(upbeat music)
- 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.
(upbeat music)