Datamarts is a new self-service capability included with Power BI Premium that reduces the time it takes to do custom departmental reporting, from weeks to minutes.
In just a few clicks, both business users and analysts can easily create and access their own mix of data from dozens of the most common data types and services, all backed by Azure SQL that brings elastic scale and ad-hoc analysis to Power BI. Charles Webb, from the data team at Microsoft, joins @JeremyChapmanMSFT to show you Datamarts in action.
Ingest and transform data from dozens of services.
Get ad-hoc analysis in just a few clicks with Power BI Premium. Select from dozens of connecting options like files, databases, Power Platform, Azure, and other onl...
Now Power BI has SQL integration.
Microsoft’s new self-service Datamarts brings the SQL hookup to Power BI Premium. See how you can write custom queries using T-SQL for ad hoc analysis.
Reduce reporting bottlenecks.
Perform advanced or specific reporting across a mix of data from a variety of sources without IT involvement. Save time with a low-code, self-service experience.
QUICK LINKS:
00:00 — Introduction
00:43 — What changes with Datamarts?
02:21 — See Datamarts in action
05:34 — Analyst experience
06:32 — Complex requests
09:24 — Pull Data with T-SQL
12:01 — Where to get started
Link References:
For more guidance, check out https://aka.ms/DatamartDocs
Unfamiliar with Microsoft Mechanics?
We are Microsoft’s official video series for IT. You can watch and share valuable content and demos of current and upcoming tech from the people who build it at Microsoft.
- Subscribe to our YouTube: https://www.youtube.com/c/MicrosoftMechanicsSeries?sub_confirmation=1
- Join us on the Microsoft Tech Community: https://techcommunity.microsoft.com/t5/microsoft-mechanics-blog/bg-p/MicrosoftMechanicsBlog
- Watch or listen via podcast here: https://microsoftmechanics.libsyn.com/website
Keep getting this insider knowledge, join us on social:
- Follow us on Twitter: https://twitter.com/MSFTMechanics
- Follow us on LinkedIn: https://www.linkedin.com/company/microsoft-mechanics/
- Enjoy us on Instagram: https://www.instagram.com/microsoftmechanics
- Loosen up with us on TikTok: https://www.tiktok.com/@msftmechanics
Video Transcript:
- Up next, we look at the new self-service Datamarts capability included with Power BI Premium that reduces the time it takes to do custom departmental reporting from weeks to minutes. And we're going to show you how in just a few clicks both business users and analysts can easily create and access their own mix of data from dozens of the most common data types and services, all backed by Azure SQL, that brings elastic scale and ad hoc analysis to Power BI and much more. And to walk us through all this, I'm joined today by Charles Webb, from the Data team at Microsoft. Welcome to Mechanics.
- Thanks, Jeremy, it's great to be on the show.
- And thanks so much for joining us today. I think a lot of people watching are probably aware of Power BI for creating their own reports and visualizations. So what changes now with Datamarts?
- You're right, most of us know Power BI for its ability to create charts and graphs and visualizations against a shared set of data. We've heard from a lot of you that you're spending a ton of time building out SQL databases or Datamarts and data warehouses to make your data more accessible today. And that work is typically done by your data engineer. And this takes a ton of time. Today, we're changing that and giving you more power by delivering SQL and a relational database to Power BI, and this solves a few things. For example, if you want to do more advanced or specific reporting across a mix of data across different sources, you typically would have to wait for your data engineer to free up before they can help. And they'd have to build out some pipelines for ETL and then provision and build out a central place to store all the data, among other things. And then they'd add a bunch of code and business logic in SQL. Now with Datamarts and Power BI, you can self-serve and bring in the data you want. You don't need to worry about building out your own databases or instances or servers. We provision Azure SQL in the backend for you, and you can design and query this database with no code or SQL experiences.
- And this is really going to reduce a lot of the bottlenecks, especially for things like self-service reporting, and also departmental analytics.
- Yes, it really does. And by having Azure SQL on the backend, you're freeing up local compute because it's running in the cloud, and it's elastic. So this isn't just about the analytics experience for an individual. It's about teams and meeting the most demanding needs of performance, security, and governance. You can even host data volumes of up to a terabyte.
- And a terabyte's not a small amount of data, especially when you think about it, this is going to be structured and text-based data only. So can we see Datamarts in action?
- Sounds good. What I'll do is I'll start in Power BI and I'll show you an existing Datamart. So this is a report for sales pipeline insights, and there's a lot of data behind this report. And if we go backstage to look at what's powering this from the data model, it's different to what you normally see behind Power BI, but familiar for an analyst. So we're bringing together both Power BI experiences and relational databases for the SQL world. These tables are in fact coming from multiple sources, and we added business logic here to model and define facts and dimensions so that business users know how to slice and dice their data. And by the way, SQL integration has been the number one request from Power BI users, and now it's here. So you can write custom queries using T-SQL for ad hoc analysis. Here I'll do a simple select statement, and you'll see how it instantly returns the results. And if you don't know SQL, you can also write custom queries using the Visual Query Editor, as you can see here. Also, as a business user, in the data view, even though you're seeing tables from a SQL database, you can still do simple filtering and sorting similar to Excel. So I'll go ahead and filter this state or province column by selecting Arizona, California, and Colorado. And I'll sort this column of data for account names by ascending. And you can see this is a no-code experience.
- Right, and just to put this into context, to generate a solution like this, it might require multiple people, different roles, multiple tools, and all that could take weeks or months to build out.
- You're right, and with Datamarts, this just takes a few minutes. In fact, let me show you what it takes to build one yourself. To create a Datamart in a premium workspace, I'll select New and then Datamart. And this triggers the creation of an Azure SQL database in the backend. Next, we need to give it a name, and I can do that from the top bar here. Now we need to bring data into the Datamart. We have our three most popular options shown here, but if I choose Get data from another source, I can select from dozens of different options, using our connectors for files, databases, Power Platform, Azure, online services, and more. Now, if this looks familiar, it's because we're using Power Query to connect, transform, and load data. So for this demo, I'll pull in just a simple Excel file, but of course, there's a lot of options to bring in bigger enterprise data sources. For the Connection settings, I need to point to the file I want, which is stored in OneDrive, in my case. I'll paste in the file path I want, hit Next, and that will load the Excel file. And now you can do the transforms you'd normally do with Power Query. To prep this data, I'll walk you through some of those steps. I'll go to the Account_Data table, when I look at this data, and I can see that it needs some cleanup. First, I'll rename it to Accounts, and next, because the data sort of looks grouped between two columns, I'll select the Attribute and the Value columns. And then, from the Transform tab, I'll click on Pivot columns, and then I'll click okay. Next, I'll remove the null row from the table by deselecting it. And now, in just a couple of steps, we have a clean accounts table. Once I save this, this will load the data into my Datamart. And of course, I can watch the progress here as it loads, but because the service is running online and loading data in the background, this will run whether I leave this tab open or close it.
- Okay, so now you have your Datamart populated with data. What's the experience then for all the analysts who are watching?
- So now that the data is loaded, I can explore those tables directly as an analyst. So I'm in the data view, and this shows me data from each table. And I can do additional sorting and filtering here if I want to. I don't need to know SQL or write select statements. Now, what if I have a business question, like who are all of my customers in Washington? Now, traditionally, if this was just a raw database, you might need to submit a ticket. And then depending on what you're asking for, or how much is in the IT department's queue, this might take days. Now with Datamarts, I can explore the data myself and self-serve to see how many accounts I have in Washington state. So I'll load in all the states here, and then deselect everything. Now in the search bar, I'll enter 'wa' for Washington, and select and confirm it. And now I can see the filtered results. So again, you don't need to know SQL, query syntax, it really is as simple as filtering like in Excel.
- Right, so what if you have then a more complex request across maybe a couple of tables? What does that process look like?
- So like I showed you before with that SQL query, you now have the option of using T-SQL or the no-code Visual Query Editor directly within Power BI. So let's say I wanted to drill into the accounts table I explored earlier and understand my top accounts by opportunity value, but I still don't know SQL. Here I'm in the design view where I can use the Visual Query Editor. All I need to do is drag and drop the tables I want onto the canvas. So I'll drag in the opportunities and the accounts tables, and next, I can look across the data. Now from here, I can merge queries as a new table. And when I do this dialog, a menu pops up that helps me do a join. So I'll keep what's there for the join type and the left table, and now I'll choose the right table for merge opportunities. I can go ahead and relate these two keys, and it looks like these tables are related by the Account Sequence field. And I can see the number of matching rows, so I can validate that the join is what I want. Now, once I confirm this, there's a new table of data, and I can choose which columns to include as part of the join. Now, remember, to do this before would've required a complex SQL statement as well as writing and executing commands iteratively to see the data and work through those results. So I've added a few more columns, and now I'll keep adding to my query. I only want to see deals that are close to landing in my Pipeline field, So I'll just choose the ones here that are in the final stage of the sales pipeline. Next, I see data in the format I want, but each has multiple rows for account opportunities and their values. So I need to group these accounts by the opportunity value. I'll click on Group by and with the dialog, I'll name my column Opportunity Value. I'll use a sum operation, and then I'll select the Value column to summarize. And that's it. Now to answer my business question of who is the top account by opportunity value, I just need to sort this column in descending order. And of course, I can open the query in Excel and do further analysis there if I'd like. And this list represents my top accounts with deals close to landing.
- Got it, that said, though, for all the analysts watching who might be a bit more fluent in T-SQL, could I pull the same data with code?
- That's right, you absolutely can. Notice this top result for O'Keefe and Sons, with the sum value of around 481,000. I'm going to show you another way of getting this exact same result, but using the integrated SQL Editor. To get to the SQL Editor, you click on the SQL view in the bottom left of the screen. I'm just going to paste in the entire query I just built, but this time as a raw SQL statement, and now I'll run it. And there's my same list of opportunities from before. It's literally the same query against the same Datamart. And because this is all SQL behind the scenes, you can use whatever SQL IDE you want, like SSMS or Azure Data Studio.
- And so if you do know T-SQL, then as an analyst, it's going to be a lot more efficient. So that said, though, why don't we go back to what's more comfortable maybe for the Power BI users who are watching.
- So the great thing about this is even though we have a SQL database now in Power BI, we still do generate a Power BI dataset for your downstream reporting. You can use the model view I showed you before to set relationships using drag and drop, and you can add measures to standardize business metrics and more. So as a Power BI user, it's super easy to work with your data in a dataset, just like you normally would.
- Okay, so now, since this is running SQL underneath it, does that mean that we can also give access to people who are outside of our domain?
- Absolutely. Let me quickly show you how. You can share the Datamarts you build internally or externally, just like in Office. In fact, I'll share this one with you, and by the way, we use Azure Active Directory for access management.
- Okay, so it looks like I have what I need here. Since my preferred tool, in this case, is actually SQL Server Management Studio, I can even connect to your Datamart from here. So I'm going to go ahead and paste in the server name string with the port details that you gave me, and I've already got my credentials inputted here just to save a little bit of time. As you can see, not only does this work with Azure Active Directory, like you mentioned, but also in my case, it's working with a Hotmail account. So now I'll go ahead and click Connect, and I'm in. So just to prove that this all works, I'm going to paste in that long query that you authored before, then I'll run it. And there's our descending list of customers by opportunity value. Okay, so beyond the controls that we just saw, what are some of the other productions that are built in to Datamarts?
- So we built in a ton of controls to keep Datamarts secure and make them easier to govern. From the workspace, you can lock down data further with workspace roles, as you can see with the different role types here on the right. Moving on to the Datamart Settings, you'll see controls for Scheduled refresh. Then in the Sensitivity labels, I have all of my organization's data classifications. In fact, if I change this one to confidential and apply it, I can go right to my impacted content, and you'll even see the data lineage view. Now I'll hover over this lock, and you'll see that the sensitivity label has been applied to that downstream content. And if I go back to Settings, just like with datasets, you can also configure endorsement and discovery, as well as request access settings. Then in the Datamart itself, because this is using SQL under the covers, we inherit additional controls, like row-level security, as you see here. We even have View As Roles, so that you can quickly validate what those roles will see, like this view for central accounts, with access in Illinois, Ohio, Minnesota, and Michigan accounts. And I'm really just scratching the surface with these controls. There's a lot more to come.
- Okay, so now you can really easily build out Datamarts that are all backed by Azure SQL. It just takes a few minutes, all from within Power BI, and with a complete web authoring experience. So for anyone who's watching right now, looking to get started, what do you recommend?
- So if you're a Power BI Premium user, you can try Datamarts now, it's in preview. And you can check out aka.ms/Datamartdocs for even more guidance.
- Thanks so much, Charles for joining us today and also giving us a first look at Datamarts and what they can do. Of course, keep checking back to Microsoft Mechanics for all the latest tech updates. Subscribe to our channel if you haven't already. And thank you for watching.