Today we're going to talk about building enterprise grade semantic models for those of you who have been through Christina weights session yesterday. The Big 2 hour long session. This is somewhat of a repeat so we're talking about a lot of the same things. Of course, alot of these are very, very, very new.
Able to again talk about some of the same principles get some of the ideas again repeat it. We're going to talk about enterprise grade models. My name is constantly on I'm a principle program manager on the power BI team. Working analysis team for a long time. Yeah, still in part by what we're going to talk about.
Things.
Part of the analysis services again. It's been a topic you've probably heard time and time again here at the conference. But what does it mean and how do these things? How do they work incremental refresh again. A feature that we're pushing heavily and now exactly with all the new functionality and features coming out. It is interesting to see how this works.
Add to your advantage, then we're going to take a look at modern data warehousing on Azure with power be, I and what does that mean and what is it? What is the picture look like and how does it actually work and finally we're going to talk about calculation groups that was released yesterday. Alright let's start with power beyond analysis. Ervis is so.
Story that we have today is this we have PAR VI on one side with premium. We have enterprise by with Azure. Now service on the other hand. This is our traditional world. This is the way we've always done it reporting services analysis services. XL analysis services for those of you have been around for a long time with them.
This is how it worked, but so enterprise by models require analysis services power BI today. The model size is limited to one gig for shared and up to 10 gig for premium for larger and more complex models. You Need Azure Analysis Services, either at.
We also have a regular Animal Services and this gives you a large larger data sets up the form of gigs ability to pin to memory connectivity from everywhere. If we tools like Excel ability to use source control fine grained partitioning and access the DMV for diagnostics.
This is very normal again for the people have been around for a long time for those users who came to power by not from the Microsoft World and we have a lot of those these days. Luckily, they come to us and even our competitors say, Oh, if you go with marks of power by if you want to skill to a larger extent, you need to go by.
Service somewhere else even though it's the same thing. The same engine so they come back to say. Why do we need to have a separate service Azure analysis. That big models. We already have power. VII need to pay twice OK. We said we take this, this input and we decided to just open.
All services ports on the premium capacity becaus this. In fact premium capacity power be. I is running the same engine. It's the same thing I mean, there are some small different tweaks that we do differently between reports and data sets that are in power, BI because the user is smaller than the big models that we have sitting in there.
For now services So what we're doing is we're bringing the 2 together and we're going to give you the capability to run analysis services.
Uh.
Datasets and sizes and models. It's out of hardware premium. This does not mean that Azure Novel Service is going to go away because we still using it the same engine so we're continuing shipping this, but we're going to make it easier for you to run these workloads in power, BI Premium and what this means.
This the party I will be a unified platform for Self Service, an enterprise by so you don't have to buy these 2 separate tools again convenience and also for us internally. It is for ability to compete with other products, but it's super set becaus.
So we can give you inside of power. We are premium that is more tightly integrated with power. BI policy based incremental. Refresh schedule. Refresh today and Azure Dallas Services. There's no way for you. The scheduler rephrases know why you have to go. Ask me if download for 20 years. You have to go XML a you can.
Yeah, a scheduler and run the processing commands data. Refresh composite models. Aggregation will talk more about this more connectors. And, of course ability to create files with part of the task and there's more coming that we're going to add to this stack because once we add a feature to power by this again, the benefits.
Just once we had a feature the power VI that feature will also automatically show up in the analysis running in So what does that mean it means and this is what we call internally XML endpoints it means that when when you look at an Azure analysis, services or regular services service today.
Functionalities right you can connect to it from any tool like Excel Power BI Desktop. Of course, or third party competitors. You can manage it using SMS you can run profiler and finally you can use SDT to deploy you can do lifecycle management, the deployment wizard all of these things that.
Work with Azure Analysis services will now also start working on your premium workspace. This has a lot of benefits alot of interesting things and start to open up so let's take a quick look. Oh yeah, so not just those things. But like the table object model. We have a full fledged OB.
Cause you to create an update and modify an allosaurus models through this. API this will also be possible now against power. BI datasets, so you can go in and it's probably most interesting for ISV scenarios. I would think maybe partitioning schedules and things like this, but you can create codes.
Yes, or it, it creates changes partitions and things like this, this now. Also, maybe we will be available in the premium and the same thing goes for the tabular model scripting language. So you can create scripts to do refreshing to change the models again check in check out so if you have a data set you can get the script out and you can see.
You can check it in and see who made changes after you look at the change that are made should check in so all these things are going to be available through part, yeah, so let's go ahead and take a look at this So what does this mean it's actually pretty straightforward So what I'm a half here is a?
In power be, I and the workspace is running on premium you can see this from the little diamond. If you go in here and I click on edit workspace and I go scroll down. I click on advanced you will be able to see that this has not workspace connection.
So you can see the workspace connection here on the bottom so now it says power VI slash slash APIdepart.com and this is the workspace name. This is pretty cool because now, what I can do is I can do a few things I could copy this and I can start using it in several different places for example, I can go to SMS.
And I already did it before because it takes some time to load because you see here is the same list. Adventureworks AX demo seem adventureworks career 12 Internet sales are taxi sales. Refresh all these things are here and these are the same datasets. You see here we put them side by side you can see it.
The same names in 2 different places this means that now you can actually go ahead and connect to these models. I can change, these models. I can delete these models. I can do all kind of processing. So instead of using the why you can send processing commands. You can do anything you can do with with with power BI.
Uhm I can send queries of course I can write a tax query and send them so you can do all of these things. But the other very interesting thing is that as part of this what you can also do is connect to it with all the tools and of course, our favorite auto tool is going to be excel so again excel.
Click to run Office 365 version of of Excel it automatically updated and now I can connect to my premium workspaces. It's probably not working on your we have an internal flights of all of our tools. So Excel power be a desktop I got a little bit earlier version.
The rest of you, but it will happen before the.
Version actually will be out. So then you can go ahead and do data get data from databases analysis services. I pasted at endpoints again. Server name and I using the same trick as what I'm doing when I'm using against actual services so I go in here.
Because I'm using ad and I'm using 2 factor authentication. You have to use default this way, and go next now, what it's going to do is going to connect to my end point.
And it automatically connected on here again, you can see the same list.
Of database and again, I'm going to connect to one seem at French works. I'm going to press finish.
And create a pivot table.
And here we are talking dragon sales.
And just go ahead and start playing with it class hierarchy on rose.
And this is exactly what you would expect to work, but now the interesting thing here is of course. I'm connecting with Excel to my premium data set for those who went to Patrick session yesterday. He shows reporting services. The same thing. You can actually reporting services through a data set in part by yesterday. We did AQ&A session and I was questioning around.
We use like today, one of the biggest problems that we have in power be. I is there is no good way for you to reuse. A data set that multiple apps with this approach. You can use and I'll show you an example of this in the second because you can also connect with part of a desktop again to the same data set and then you can publish that part of the task of those reports through different workspace that you can.
So this allows you to do the things like re iterating over the other important item here change the rheumatic making is that today. When you use analyze in Excel from a data set you need to be a pro user with this feature because it's running in premium if you use the connection like this.
You don't need to have pro user.
To be able to analyze in Excel as long because, as long as it's running premium, which the XML input in this case is you can connect with Excel through. These tools you don't have to be a program pro user and regular party. I account free account is enough for you to be able to connect so that's pretty big changed a lot of people in asking for this analyzing excel, though, is.
Little bit different than this and that still will be pro.
Alright so yeah, all the same things that you're used to here so sure. This is what you can do with the XML endpoint. The XML endpoint allows you to connect 2 power BI as if and you can use all your favorite tools like that studio to debugging today. It's kind of a pain to really figure out what is going on with my premium pass it? I don't have really?
Tools through model updates. I think for those who went to Chris session just before is about dax. I walked by I saw that studio and looking at the dax queries. You can also now start connect studio to your prima capacity on your datasets there and see all the queries that are running and what's going on.
All right.
Um any questions on XM LAN Point Yeah.
So the question is, are these features also available in part by server on Prem so first of all these features are not even completely available in part by service yet so initially going to go out in in a preview version of this, where it will be read only first not read right so it will take some time for us to.
Full version of it and usually the way that we put features from power bei.com into part of our report server is they need to be general available, so it will take. I think quite some time before we fully get this into general availability and then we're going to have to look at this feature.
So the answer is maybe one rig at the GA which is quite a lawful now and then we have to take a look into how expensive it actually is to make moved over, but yeah, so maybe it's the answer.
It's not no yeah, another question in the back.
Sorry I can't hear the question.
How exactly is analyzing excel different it's actually using a different even though it sounds similar to this feature it is actually using a different internal mechanism inside of the party. I service so it's not actually opening up the XML endpoints. It is a different end points that is using so it kind of because you can make changes to it for example.
So you can make fully changes to it with the other excellent point is just read only version of it and it's a limited you cannot send MV through it, and things like this.
Well, you can send the sender limiti, OK, so incremental refresh incremental. Refresh is not a great feature that we haven't seen much usage on yet be cause. It's actually quite potentially bit scary and complicated. Now, with the new MLA endpoint and we can actually show you.
Yourself what is going on so one of the biggest problems that we of course, always half is that instead of for those who went to my session yesterday about performance. One of the biggest performance games that you can make especially if you have premium capacities and things like this.
You will be you will see whenever you look at your capacity metrics app. How many percentage of your premium capacities actually taken up by refreshing data be cause. It is a lot of people are all running. Refresh is a couple of times a day and there are couple of gigs bacon, so it.
Memory from your capacities you don't even notice because it's all running in the back. But it's all eating up. CPU memory and CPU memory that you potentially could have used for other purposes like more queries or loading reporting services or running data flows or so, instead of loading all the 5 gigs or 2 gig.
This of data that you have sitting inside of your datasets? What if we could just load a piece of it.
So incremental refresh allows you to for larger models in part by 2 faster refresh. It's more reliable because, like it is still a lot of times going over the Internet. So if your database that could be a hiccup internally when you have the gateway to the internal sites that could be something happen.
When you connecting to a database that is sitting somewhere completely different place. So it's much more reliable because it's shorter lower CPU memory usage again this will save you money, you can apply the policy and partly desktop then apply and then it actually starts to work in the service. The policy does not affect date.
So if you create incremental. Refresh policy in party desktop it will create it. But it won't actually be enforced in part of the desktops only working in the service.
So how do you configure this and II won't spend too much time with this whole zoom in a bit here actually saves no it doesn't make any difference So what? What I've done here configure it and the way you can figure it out quickly talk about it. The way you can figure out this you need to have a table. The table needs to have a date column that's it.
Column needs to be configured that in power query went on the import to have a start and end time.
At least have 2 parameters that's a start and end time and that's what it gets. The filter on as soon as you've done that. You can get set the options in this incremental. Refresh window and now you can select the table. You want to have incremental. Refresh on and in this case is doing 2 things. It is only storing the Rose for the last 10 years.
And it's who only refreshing rose in the last 5 days So what this means is let's say we're getting 2 year 11? What happens is automatically the data gets drops for Year 11 and it just gets a rolling partition that is automatically created the second thing is OK because we have a lot of day.
Yeah, I don't actually need to refresh all day to be cause. The data from last year or 2 months ago. It never changes. Maybe sometimes it does, but so I very common strategy that is used in Allah Services is to also create partitions. You have to manually create codes and write code to do it and it creates these partitions run these emerging.
With all these things and that maybe once a week, you run our full. Refresh to make sure everything is still in sync so in this case? What would happen is only the last 5 days will be refreshed so how does this work because actually quite interesting to see because now we can actually see how this works when you create something in part of it.
Grats doesn't create any prediction is crazy. One partition where all with all the data. Now, when I go and upload this to the service do scheduled data. Refresh it creates it. Of course, the same model here. So I'm going to connect to the model and connected tables. So here I have the same trip table. Let's be shown in the?
Boat trip So what I'm going to do is going to look at the partitions that oh actually the first thing that I'm going to do is I'm going to look at the the refresh policy. That's being created and for me to be able to see the refresh policy. Now that we have the XML endpoint and again. This is connecting to this Expo airport right is this database that.
Turn IR taxi, so it's a part of the data set and I want to see the refresh policy. What I can do is I can actually go ahead and look at the script, which we could never do before but this is the same script she would know for analysis services only you will see there's a few differences. There's a compatibility.
Of all that you haven't seen before. And there's a few other things in here, but I think we, we care about right now is this refresh policy. And here you can see the refresh policy. In the set up my table so in this case again same as the why but now in Cote will see refresh policy. Rolling Windows is 5 years.
I want to keep the last 5 years and I want to refresh. Only the last 10 Day 10 days of data and as soon as we run this and we start this power. VI starts to create this and fix this for us or the other interesting thing is when you run it in power BI.
How does it know what to which days to pick right what it does in power be? I always it always goes from the actual current day of today. There's no way for you in power be. I to change to say my last 10 days actually start from.
Sunday, because that's when we had the last real data, it no. It actually takes the day off. Today, the current date with an Australian is in a second, but what happens is.
When I go to my partitions and again for those who are familiar with and all services will know about partitions. But what they in fact, it's doing it's creating partitions on the covers. It takes a bit a little bit to load but here all the partition that is creating and it's creating a lot of them would you see here as partitions by year so you see 2010 2011 20?
In 2220, 15, so these are the 5 years and then the next thing. What is doing is up until the current day. It's creating different partitions and it's doing it in different granularity so it's doing it first by years, then it creates quarters. Then it creates a month, you can see 30 so.
The 7 month and then forward are discouraged month. It's creating partitions day level and the way that is doing is and why it's doing it because we want to refresh. The last 10 days and not more than this. So we can actually already see like 32. This is been refreshed. Today, 31. These haven't been refreshed you can see the refresh dates on the right and you.
10 days have been refreshed so it automatically creates this and it also automatically updates this and let me show you what it actually means so you could see it goes all the way until the 13th. So my current day my current day is 8:13. I can change this in my refreshing schedules.
So I refresh and again, you can easily get through this script. You don't have to write it. You can go to process table here and just like you can do analysis process table and scripted out and it will generate this accept these 2 things because they didn't really exist yet.
The existing Ford purpose of this demo for it actually says is OK for the refresh schedule. Apply the refresh policy that we have an we because we're running. This script can actually supply. The date that it needs to start from so in this case start from 8:13. You just saw it. Let me update it and show you what happens.
So I'm going to make it into 814. I'm going to execute the script hope we don't get a timeout.
It's executing the script.
Executed query it takes a couple of seconds but now again under Corpus is doing a few things is loading the data in this case. It's fake data so there's not much to load but it's also fixing up. The partition strategy because now we've set. I have a new date that I need to start my rolling window periods from so.
Into the partitions die dialogue.
Again, it takes a few seconds to load go back in here.
You see. Now we have a new partition added on the bottom 814. We didn't have this before, and also you will see that will go back and see all the dates like we only refresh from 84.
It all has the same, like we rather just now. The other one. I ran yesterday. So you can see it automatically does the right thing it only refresh the last 10 days of data and then automatically updates this for us? We don't have to worry about anything. We don't have to manage maintain this, who's done this in analysis service.
Yeah, it's it's a lot of work. It's allowed hassle object models running code. This is much more elegant and simple. Of course, we partitions. You can do much more other things. This is of course. A limited scenario. You need to have dates, but still as long as you have a date this works great, so hope.
I.
A show of how this works. Today I think Chris you mentioned yesterday in this talk today. This only works in.
Premium capacities what we do want to do at one point is also bring this to pro. But it is interesting work. So I don't know exactly what that when the time is then we will do this. But yeah, it is definitely the goal to be able to have this in pro as well.
All right any questions on incremental refresh.
Here question there.
Ah so the question was like let's say you have 5 days and someone came in and you said, OK, actually something changed like 5 days ago, so just a few things you can do you can change the policy to say OK? I'm going to run the last 6 days that's easiest way there other ways. You can actually go in because these are regular I'll show you.
Partitions.
So I can go in here partitions and say, I can actually update just that one partition manually. If I want so I can go in here and say OK. I want to pick one of these and I want to process their own partition. So I can actually go in and manually. Refresh it as well.
Policy or refresh manually OK will do more questions at the end.
OK, another really interesting topic of course, is the model data warehousing on Azure and power by this is something that we're investing heavily in Microsoft as you can imagine one of the big things that we see a lot is data. Lakes pulling data in Azure. Yotei lots of data gets generated and we want to get make sure that this last mile.
Visualize this data, and make it insightful for your business. Users is actually going to work. So you've probably seen this demo by now. I'm going to show it again be cause. It's pretty awesome. What we've created is a feature that allows you to really connect to these big datasets.
Big data sets and not just in a way.
That you would traditionally think about this because big datasets and spark and these things are traditionally. They're pretty fast, but not as fast as we are used to it in the VR world be cause.
Our tabular models are multidimensional models is there instant. It's instant gratification. They just drag stuff on the canvas and they see visualization. Individuals this for business users want if they have to wait for more than a couple of seconds are going to start calling you and saying it's too slow. I can't use this. I'm going back to Excel so.
To make sure obviously we're going to give you the capability to give them the instant gratification. Even on those big datasets, but of course, the problem with these big datasets? Is there big we cannot load everything into memory that is too expensive like of course, we would like you to pay I don't know how much to load everything in memory.
Biggest baddest virtual machines out there, but it's just not economic it doesn't make a lot of sense to look all. This data in So what we are going to allow you to do is combine the best of 2 worlds. Let me show you the demo. So here I have a power BI Desktop file part of a desktop file.
Data for a Courier service in the US and the couriers drive around and every time they drive around and they do something. I lat long and a timestamp are going to get sent to a big data cluster or if I'll share what we're going to do is.
How much data is in in here So what I have here is a regular tax measure it just says counts rose at the table going to drag this in.
And it's going to show me the number that I need.
So it's actually 1,000,000,000,000 rows of data so we're doing my account throws index over this table and it's one trillion rows of data? What we have sitting here in the background. There's a quarter of a petabyte of data in Indiana. Databricks spark cluster that it's running and we have 1,000,000,000,000 rows of data and you saw how fast this, it is.
And if So what I'm going to do is I'm going to actually show you So what I'm going to do is location count. I'm going to drag in my.
Distance traveled give me one second distance reveled yeah.
Distance traveled dragon, you see again. It's it's already interactive but the really gets interesting when we start, adding more fields through it like activity date.
Make it to bar chart and I want to see it by.
Miles by job where is the fields scroll down miles per chop and add it on the legend.
And you can see we're actually working on a quarter. A petabyte of data. It is a trillion rose. And it's it's instantaneous. I'm just playing with it. I'm I'm Dragons roping if I want to now. See for example, figure out the drivers were left, I can create a filter it's automatically slicing it.
I have only 13,000,000,000 rows left, I'm going to make it bigger and now we want to actually go figure out so I'm going to filter out by date.
So December 23rd and now I actually want to go in and look at the drivers so I'm going to drag location counts and by driver name.
And here we can see the individual driver name so now we went from the 1,000,000,000,000 rose to all the way down to the individual details. So now we have actually driver names and with this is that they drove on that particular day. So we went from 1,000,000,000,000 rows completely aggregated data. All the way down to the individual drivers and the people who are.
Driving so now, what we actually want to see is where did she go because this is where the really interesting part starts becaus? How can we actually make use of that quarter of Pi divided data? How can we actually can get insights So what I can do is I can go in here? Do right mouse click drill true and I go to my.
This is now running a spark job data brake job on the back end, so let's go ahead and take a look at this, it takes a bit longer because we're running this over quarter petabyte of data so I'm going to do is I'm going to go back in here, I'm going to my data.
I'm going to wait for a second because usually takes a bit to show up.
OK let's scroll down so you can see here. The active active job and see this is the query does being sent. We scroll down you can see here. The driver names Abigail Johnson is the one we picked and also in the day, but it's actually running this query right now on my data bricks cluster on the covers.
An it takes a few seconds but let's see if they already finished it's still loading.
Let's see how how how long it's going to take.
And now it's done so now would ran from the verti. Paq engine that was sitting on military in a second thought we are. This is done by the way that works now we can actually see individual trips, so from the quarter petabyte of data that is sitting on a summer in Azure. We can now look at the aggregated data. It's just super fast, it is.
Performance, but still if they want to get through individual granularity, the lowest level to see details like this that is also possible.
OK so how does this work so?
What we've introduced as a new feature?
One of my favourites.
Is called aggregations because this story. The situation that we have today? You have big models if you need this? Is the The The thing you need to think about when you start creating another service model or power. BI model when you have a big model is it going to fit in memory. It's not going to fit in memory. I need to be in dark very modes.
Probably throughout this event and a lot of other events. Directory is interesting. If you do use it for everything. So it's probably not a good idea, so you're in trouble or you want today to be super fresh right. I have IOT data. It comes in all the time. I need my data to be fresh. You need dark remote be cause the alternative.
Fastest query performance is importing data into desertification importing data our costs time.
You need to do it on processing schedule, and cost time and cost a CPU and memory so this was the situation. We had before now. We've actually brought these 2 into a single model so we have directory and import into the same model.
So again if you look at a model like this either, direct remote all tables are in dark promote or table. All tables are important and we see if geography. Customer sales date reseller sales product employee regular schema and again.
Or.
We started introducing one feature misquote composite models in power, BI Desktop Composite models allow you to change how tables are stored in your party. I so one sided is stored in memory and the auto tables are stored in our query mode. So when I get the sales and always goes to our query.
There are a memory this is interesting, but this is not really the trick that we used to show you the demo that we did before what we do. Instead is we also introduced a new feature called aggregations and what aggregations allows you to do is create.
Kate it's table in memory you load in 30 pack that has most of the same columns as the sales table, but not everything for example, in our scenario that we just had an in demo. The Aggregated table does not contain that long it does not have latitude along dudes in that table, but that brings.
A quarter of a petabyte of data and once rillian rose to 500000000 rows, which is much better because it is easily. We can handle that so the aggregation stable only has 500000000 rows you can only refresh data. You can get the interactive analysis that you want. But as soon as you want to get to the details reported actually.
The other table so the engine is smart enough to recognize how this works for example, if I do a query like this like we probably wouldn't write a query like this we will just probably dragon visuals would drag in a year and the city and the dragon sales amount. The engine would automatically recognize OK all of this can be got on him.
From the aggregated table, so I can get this from the Aggregation.
Um Christian in his in his session here 2 hours. I have 50 minutes, so for me, sweetheart of show you all of this in the newest version of that studio, you can actually see if aggregations get hits by the queries that you sent so it's allows you to debug a little bit more deeper so this hits the memory cache.
But if we do something like this summarize columns date customer name. Some of sales amount. The engine again recognizes I can't use the aggregation table. I need to use the dark query table. So it automatically does this. The other thing that is. I think the most important here is I don't know if you've noticed it in the previous demo data.
Not use 2 tables. I used one table the same thing here your end user is using the sales table. It's not using even going to go back here, he dragged in sales amount from the sales table, not from the aggregation stable the engine was.
To figure this out, so we only have to give the end user one table. We take care of it in the back and say OK. This aggregation table. Here's data. You can get you don't have to expose this to your end user so for them. It is as if they're working with one table and they don't have to know that we're getting from aggregation table, not will take care of it.
OK let me quickly show you what it looks like we go back in here.
For going here and I go to the diagram. View there's a few things you can see this just 2 tables and they are pretty much similar in nature.
The only biggest difference is the aggregation table does not have that long.
Yeah, we remove some of the lower ground actually tables, but that's it. And now the way that you can do this as you can even in this case. There's no relationships. No nothing what I can do is I can go in here? Do right mouse click or here and do right mouse click I think.
Manage aggregations.
This opening up the aggregations when the dialogue and the aggregations dialogue allows us to say what we need to go do like for example, when I did the account. Rose xpression that we did before so it's smart, so we wrote. It expressionists count rows of the table counts rose the table is something.
So the position count the count of the rows of data that is created is actually already stored in the aggregated table. We aggregated this we automatically know the aggregations functionality knows that when you do counts rose at the table. It needs to get the data from the driver activity table needs to count the table of rose from the driver activity.
The table and the same thing. I want to do movement only want to do this some off the movement column in a driver activity table. So it automatically match. These 2 things together and then figures out where these together from.
I think this is really an interesting scenario, especially in this big data case. We start hearing them more and more and more people want to have access to larger amounts of data the biggest problem is sometimes. We also hear this to say OK why do? Why do we even need power BI we're just going to go run directly on top of spark or whatever data bricks.
These scenarios, but first of all the experience not the same you can do directory mode in power. Be I but whenever you do directory mode in power be. I it's not going to give you the same performance. You saw when I drag and drop stuff. It was instant. When we actually went to had to go to the spark cluster to do aggregation and look at some of the data, it took.
I don't know what is 30 seconds or something like this 30 seconds or so you can potentially scale your database cluster to a larger extent than actually is instantaneous, but it's very, very, very, very expensive. So you have to pay a lot of money to scale this up and second most of these big data clue.
Are not really designed for concurrency because in power be? I you want to have 50 users. Others users play with this report. 50, 100, Jews were going to send concurrently queries to your data bricks cluster is going to be very interesting and very, very, very costly.
So the analysis engine, the power be, I in this case is going to take the bronze like the 80% cases off those hits in its in memory. Angel was built for analytics is built to handle these concurrent needs and then if someone like the 20% case. If someone does have to go and look at the underlying details.
5 into unconnected database cluster and they don't have to know this of course, database is just one example. It can be anything and let me show you a quick picture of this.
So this is kind of like a topology diagram that we've created and let me quickly walk you through it, it kind of topology diagram. If you created for party in Azure. Of course, is not D Topology Becaus as usual. There are 50 different services for everything so you can pick and choose which ones you need but the scenario is the.
You load data you freshen ingest data through all these tools initial states ADF when your professional it organization low data stream. Analytics storm or when you're a business user. You can use data flows to load data. But the underpinnings of everything in.
The data store is Azure Data Lake Gentoo. All data gets loaded to Azure. They like Gentoo on top of the data in ash day like Gentoo, you can create a common data model. The common data model is nothing more than a text file that describes the data in your Azure Data Lake.
Folder that text file that describes it now makes it possible to connect to other tools like dynamics or power apps so you can actually make changes to that data interact with the data through these tools directly into the data. Lake we are also working on allowing you to can.
They like the same CDM folders through data brakes native integrations and spark and we can train the models. You can really say OK. I'm loading in data through ADF. These are my transactional data. I want to Market Basket analysis. I want to do something machine learning and you can add these things now.
Those already directly or you can load them connect machine learning directly to the CNCDM folder and let it writes the output of the machine learning algorithms that you've written directly added to the city model and now that output can be used by power apps or dynamics and of course, when you are the data.
Which one did we be analyzed you can load the data?
And model and serve it so probably either through SQL DW and download it into a data. Mart potentially or of course, partly previous datasets, slash analysis services. The aggregations feature that we just saw. And then from there on you would give it into the party. I reports or when you're part of a desktop user you can.
Through the CDM so your business. Users can connect directly to the CDM that corrected data directly connect directly to SQL DW. It is just how you set it up so this is kind of the ID the one thing that I do want to mention as well about aggregations. We just talked about relatively straightforward scenario, one in memory database.
Through the data bricks you can actually also Cascade So what you can also say is OK. I want my 80% case, sitting inside of a inside a 30 pack. I want 10% of my queries being returned from SQL. DW any other 10% get in return from Azure data brakes so I can create an?
Tables and they can all connect to different data sources, so you can cascade it or a SQL SQL database or anything like this so you can go in further than what I showed you just now. I think yeah, we're just starting to play with this so it's going to be really interesting to see what people will be actually doing with the aggregations feature we've already some big customers were playing with it.
The pretty interesting OK, finally finally we're going to talk about one of our favorite topics.
So I've been on the analysis team for more than 10 years. When I started. We just started with tabular. I was from nature. A multidimensional developer. I build multidimensional models and in the reporting services reports on top red all Christmas books.
But the tabular model, the design goal was we want to make things easier.
If you want to create multidimensional models. You have to understand as relationships. And when you have to understand Aspies relationships for a lot of people. The lights would just go off. It was the end of it. Tabular we wanted to make it simpler, but simpler also sometimes makes it much harder to build so it took us a long time to get to a lot of the same functionality.
As we have for multidimensional recently released a bunch of important features detail rose object level. Security ragged hierarchies aggregation. Such composite models. So now we're getting, it through that shape that we are getting close to all the functionality of multidimensional the biggest one that we have missed so far.
Ability to do something like this today in tabular when you have measures and maybe you have like 50 measures in your model and for every measure. You need to have something like this right here. Today Multidata quarter date 3 months average 12 months average previous year year of year so 50, * 10 different times variations gives you immediately.
Right that is very painful in multidimensional was very elegant just run that I'm intelligent wizard and make some tweaks to it is how I used to do it make some tweaks to it, and then we will be done and it will have to write 50 or 500 measures, it just works so we're going to bring that same functionality.
All the calculation groups to tablet model as well so calculation group will allow you to reuse tax calculations to reduce complexity. It will ship in an hour services 20:19. Today it ships. The CB 2.3. So you can download it today and play with it is also going to ship in ash.
This is also going to ship in part, B premium of course, when we have the XML endpoint enabled it does require a gnu compatibility level completely level 1470. We just saw it when we looked into the incremental refresh. It also has the same compatibility level.
OK, So what is calculation group look like it looks like this you will create a table animal. Unfortunately, I cannot show you a demo the demo.
Died so I can show it to you, Unfortunately, but we can talk about what it actually does and what we have here is the calculation group that is defined in the model and what the key trick here is what allows us to do is it says OK create their calculation item called current that gives me the current measurement, so in this.
Sales I wanna have sales. I don't want to sales. By month date support of the data here. Today's previous year. And it doesn't matter which measure you use on top of this so you can use the sales. You can do the the distinct count, you can do the transactions. You can do the returns all these measures.
This can be applied to for all these measures you can get previous year previously amount. Today previous year quarter days. Previous year year. Today's your vehicle rose year over year percentage so this now significantly reduces get you back to the multidimensional land, where you just have a measure and the measure will allow you to.
Create these and then the calculation group on top of it for the life you create all these time talents calculation or whatever. You want to do like already Marco Russo created an interesting blog post on this right. He's already thought about all kinds of interesting scenarios, other than the one we showed you here where you can use it for.
So, in order for us to make this work. We introduced a couple of new tax measures adapts functions selected measure again. It returns a reference to the measuring currency currently contacts there was a question yesterday. The calculation groups. You can use all these measures also outside of calculation groups you can use them in any dax expression.
Measure second measure name is selected measure our new functionality that we added so you can do something like this is selected measure expense ratio L or expense ratio to then so the selected measure otherwise divide selected measured by the number of rows in the date column.
To play around with measure, which measure is selected.
So what you will see when you start playing with this and CDP 2.3. Some things are not working yet and the export. It's coming soon. Dynamic format strings. This is probably the most important one dynamic format strings. Coming soon because that's how you write it calculated.
Some cases, you want to get a percentage out of ice. We want to get a currency and you want to be able to show the right numbers there. Obviously Apple security rollup security implicit measure also doesn't work. This is kind of like a quick show you how it works, you can drag it the salesman column.
Um.
And this was an impressive measures are in place measures you need to turn off so you cannot use implicit measures when you connect to the model so this is something that we might want to work on adding later but right now. You cannot use a business. You need to actually create measures for every calculation. You want to do people cannot just drag.
Yeah, the sales amount of column anymore. That's the downside of being able to use this.
OK, the last slide and we are out of time. SQL server at all services 2019. What are the new features that are shipping in that modeling analytics calculation groups? We just talked about this many to many relationships. This also shipping is a feature that is in power be. I super decks MD. This important for those of you who are using multidimensional model.
This is going to get your performance improvements for multidimensional models connected to power be. I resource governments data connectivity. So we want to add new connectivity anew. Dax functions and with that. We are out of time. Thank you for attention. I'll be here for questions don't forget to fill out the evaluation forms. Thank you.