Azure Analysis Services Tutorial | Scale Power BI reports into hundreds of GBs

if you are building reporting solutions for power bi or excel that span across tens or even hundreds of gigabytes of data or maybe you're building a common data model for your company I got a perfect solution for you this is Adam and today we're gonna get introduction into our analysis services stay tuned so let's talk about what this our analysis services our on the services is first of all you're in memory database but also your analytical data processing engine and everything that in a cloud as a service you usually will see analysis services being by the end of your architectures just before your BI reporting solution in other in most of the time that will be part bi and you will see analysis services as a back-end analytical engine so what is this analytical engine that I'm talking about if you have for instance in your analysis services a table a sales table it contains country products and sales for that products it's a simplified data model with just one table and you have your bi reporting solution in this card case power bi if you drag a country column from your table into the power bi what would happen a matrix table would be created and another services would find the first value a unique value for that table for that column country and pull it in and it would do the same for each row so by grabbing this column you're summarizing it by country and you're getting two values but that's not where the analytical part comes in what concern is that you can actually define a sales amount measure and the measure is dynamic calculation of your column value in this case you're using something called ducks it's called data analysis expression and you can define for instance that you want to summarize a sales from your sales table what will happen is that whenever you pull this into your report in case in our matrix if we're pulling that sales amount measure what another server we'll need to do is we need to evaluate every single row of our temporary matrix to calculate the value in this case for Poland it will find the corresponding rows that were used to create this bro and for then it will grab your measure check what this measure is calculating in this case it's grabbing the sales column it will find what are the values calculate the sum and now put it into the row and it will do exactly the same thing for each row of the table finding corresponding rows grubbing the values and calculating the measure that's the first part what those dynamic measures are amazing about but additionally it's not only about creating dynamic measures it's about this context as you already seen it calculated based on the country even though we never defined country in a measure and you can do the same in other cases if you have a table by product it will do pretty much the same thing so by pulling the same measure it will grab context of each row and calculate the value again so you are reusing your measures within your model and it doesn't have to even be metrics you can actually use any kind of visual because that can be even a bar chart and still pulling the same measure always give you the same values that's the analytical part and what is also amazing here another services is really fast at it because it's your in-memory database originally another services was a part of SQL Server family but it was also migrated to Azure and now you have utter another services one important distinction if you're moving from on-premises to Azure you need to know that it's not exactly the same it's a little bit different than on premises version first of all you don't have multi-dimensional models so no cubes for you you can only use tabular if you don't know what tabular is think of it simply like your relational database and the second important thing is only models above 1200 compatibility levels are supported so it's SQL Server 2016 up and above but of course I recommend always to use the latest because bunch of features that you can use if you've been working with power bi everything that I'm selling saying right now might sound familiar and there's a reason for that because underneath power bi there's analysis services it's actually tied together with power query in a browser process if I would explain those very quickly then I would say power query is your ETL tool so it's responsible for extracting transforming and loading data into analysis services but unless Services itself it's that in-memory database responsible for query caching managing your data model relationship security enrolls so you can create this role level security those dynamic measures that I was talking about and all that analytical processing that is happening so what is left only in power bi is just that browser process is just simulated edge window with JavaScript some visuals and it's just visualizing your data but when moving to a guru you get a bunch of additional features because this is no longer SQL Server on-premise so you get up and down scaling capabilities up to 400 gigs of memory that's the maximum you can actually fit there considering you can actually compress that from five to hundred times but my experience is about ten fifteen that means you can fit there about two maybe two and a half terabytes of data of course I would not recommend that much but I would go for a few hundred gigs is pretty much perfect that's as sweet spot you have also scale out and scale in with replicas so you can have up to eight small Tonya's replicas of your model that you can use to scale out your reports if you have many users additional you can pause and resume so you can actually stop your development environments if you're not using them or maybe even production in case of very rare scenarios for reporting during certain periods of the month additionally you get automation capabilities so there's a lot of additional tools in Azure that allows you to automate things in your analysis services and bunch of bunch of other features so natasha is really good idea what are the coma scenarios as already said large datasets because it can fit those models in Part VI of tens and hundreds of gigs of memory but it's not the only tool that can actually fit this much data but what is great here is that it can fit this much data but still have very fast response times and they're just like ten hundred two hundred millisecond response times chickens should actually hook up those reports and live reports and it will still respond very fast and lastly it's also very commonly used for something called common data model imagine a scenario where you have a sales database and you are actually consuming this data into multiple reports what's commonly happens in your organization when delivering those reports to a certain group of users you define the KPIs that they want to see so for each report you define KPIs but the problem is that defining KPIs on a reporting solution makes a little bit of disadvantages first of all you very often do that a couple of times you might have a mistake you can actually redo the work instead of actually copy pasting this into another report there's a lot of disadvantages here and well why to do that well you can use analyst services to actually get it in the middle between your sales database and your reports and actually put those KPIs there making it at common data model that you will use across your reports of course you can either grab all the measures there or just grab the most common ones and let the reports define the report specific KPIs still on the report level that's the common data model the demos so I have a bunch of demos for you today we're gonna start with service creation then I'm gonna create analysis services project in Visual Studio I'm gonna be loading some data I will actually show you a nice comparison between power bi and analysis services then we're gonna deploy a model to either I'm gonna connect from power bi to another services we're gonna create some measures and redeploy our models so the demo for today is very simple one a blob storage with some movie analysis services and a power bi so let's jump straight to it let's start with creation of the service in utter portal as you seen we need two services we need a blob storage so let's start with creating it blob storage of course if we're gonna type yours quickly gonna find also a storage account because that's the name of the service so let's hit create from the drop-down I'm gonna select my research group I'm gonna call this am is demo storage I'm gonna choose the region that's gonna be north Europe as always for me I'm gonna leave everything else as default maybe I'm gonna choose the lower replication because I can get it faster so let's hit create and hit create so while our start account is deploying I can start deploying another services so let's hit create resource search for unlisted services hit create and I'm gonna call it a mas demo I'm gonna choose the resource group the same reasons group from our another services example pricing tier here's the first choice study need to make pricing tier is defined by tiers its development basic and a standard development as the name suggests should only be used for development because it's only 20 query processing units query processing units are Microsoft way of measuring throughput and performance of analysis services a d1 is 20 query processing units if you're gonna jump to documentation you're gonna see that actually d1 is 10 so I'm not hundred percent sure what is the actual kippy's that you get it should probably check it but only 3 gigs of memory so it's very small so development is just to test all those features and develop your models but as soon as you go into bigger models you will actually need to upgrade probably to add smallest one standard as 0 with 10 gigs of memory for almost $1 an hour so I'm gonna choose d1 development purposes I'm gonna select the administrator that's gonna be myself so let me actually select myself administrator is a person who can actually do all the administrative tasks on the analysis services including model deployment so we definitely need that in order to be able to deploy our models hit create and we just need to wait since our storage provisioned in the meanwhile I will go to our resource group open it and go to the storage account containers I'm gonna create a container this is gonna be called an input container is where I will actually put our movie data so I will hit OK go to that container and upload a movie data set so I'm gonna hit upload select and in the Downloads I have a movies CSV so I'm gonna hit upload and I've got it I will quickly show you how this movie sees we look like so you will actually get a good idea of it as you see it's very simple movie ID title and genres all right so let's go to power bi I will quickly connect to this to show you the experience and the difference between the experience from power bi and analysis services so I'm gonna go to more find a blob storage in the other section blob storage hit connect I need to provide a name so I need the name of our storage account this is this one I'll paste it here I also need to provide an account key you can I get account key from this section let's go here paste it in hit connect once you do that you will actually see your data of course you need to select the storage container hit transform data app our query will pop up you hit on binary content and you get your data so let's close that for now so let's go back to the portal and let's see that our analyst services already has been provisioned so let's go to the resource and see what's there as you see first of all the two important things to notice first of all the status is active that means the model and the service is currently running if you're gonna hit pause it's gonna be in status post you also have a server name and management server name general recommendation here is that for server name this is what you pasted into reports because this is a read-only connectivity whereas management server is read and write while for single instance this is not important which one you're gonna use but when you're gonna be using a replicas this is very important so you always grab management server name if you want to work with models and deployment so I'm gonna copy that and I will actually go into visual studio this time so individuals today I'm gonna create a new project file new project and if you have SQL Server data tools with another services projects installed you're gonna find under the services section and an analysis services tabular project I'm gonna call it movies model hit OK and here another important choice you have two options integrated workspace think of it like that internet grated workspace is that simulated analysis services on your machine so it's Visual Studio gonna be soon lighting running another services instance underneath workspace server allows you to build model locally but deploy that model and work directly with analyst services somewhere it can be their own premises or in the cloud because our model is small I'm gonna use integrated because it's gonna work faster and the compatibility level already said this is the version of the SQL Server and analysis services that you're supporting my recommendation always go with the lightest especially go above 1400 because this one has the power query included so I'm gonna go 1500 hit okay and what I will want to do is the same as in Part VI so I will want to connect to our data so this is the workspace that you're gonna get as soon as you connect to feelings to notice you have a solution Explorer those are the files within your project and you have a tabular model Explorer this is where you actually build your model in case you would close this notice that tabular model Explorer disappeared and to get it back simply go to model boom and double-click it will open the model Explorer so right now we don't have anything so what we need to do is we need to import a new table and to do that we right click on data sources and new data source or directly import the table and create data source in one step so I'm gonna hit it and notice this this is the same pop-up that you've got in power bi because this is the power query that is also included in analysis services so I'm gonna pick a storage account hit connect same pop-up so let's go back to the portal let's go to the resource group and let's grab our storage account name and the key and hit connect and as you will see right now we're gonna get also the same pop-up as last time this is the still per quarter experience so you can hit this container called click transform data get the pop-up from the power query hit binary and you're gonna get power query experience and a transformation the power query allows you to create a transformation here as steps say in case I would want to let's say transform my genre column I could simply go here split column by delimiter use the default one which is the pipe hit OK and then as you see I got multiple columns when my genres split and then I can go and unpick up these columns and remove this one very quickly remove rename this to genre as you see I am pivoted this list together each title with its genre as a separate browse and I can hint simply hit click import and this is your ETL for run of the services and again recommendation here if those are super simple transformation do it in Park wearing if they're hard or combining multiple sources try to do them on the database level or in a different tool because well it scales nicely on a small level if you're transforming tens or hundreds of kicks of data this will crush it will run and timeout it will cause the problems don't do complicated part query transformations on very large data sets so as you see 20,000 roles were transferred we can hit ok and this our table once we have this we can actually start deploying these tunnels services before I start creating measures I want to show you the deployment experience in order to deploy this to assure you need to go to solution Explorer right-click on your project go to properties and specify the deployment server notice by default its localhost but because we're deploying to Archer we need to grab error address for the under services so in analysis services grab the RW copy the clipboard paste it here hit OK once you do that you can right click and hit deploy this will prompt you to log into Azure because it will need to verify if your administrator on the server and you can deploy models what will happen right now is this entire model is being first metadata is being sent to the server and then server locally needs to be able to access your data source to pull in the data so it's deploying right now let's give it a couple of seconds so the deployment finished as you see it succeeded you can verify that by going to another services and simply refreshing this page once you refresh this you will see your movies model deployed the compatibility level and the deployment date if this is done correctly you can copy to clipboard the server address and go to power bi but this time you can actually get data from analytics services so let's hit it but notice it's actually selected import this is because I already imported some data even though I didn't save so I need to actually create new report in order to be able to connect live to analyst services so let's create a new report ok and now we click get data analyst services we paste the URL and notice we have an option selected connect life that means we can connect live so that our power bi will be just executing the queries and analytic services will be calculating them so let's hit OK it will ask me to log in again this time it checks if I have any roles either an administrator or just regular user roles on analytic services in order to be able to actually read data once it do once it does that it will actually pull the model into analyst into power bi when it does you can actually select which model this is the pop up in case you have an many models and a single analysis services it will allow you to select which one and if you very nicely design your model you can actually put even the definitions here and the description of your model can hit ok and this on the right hand side will pop up our table which is currently called input not the best name for the table but we can start working with that model so we can click on genrih to get a list of genres a unique list of genres on a report so if we want to work with another services we can go back and start defining some measures and while this might be not intuitive on the first time in order to define a measure you actually don't go here and right click on the measures because nothing happens what you need to do is actually go here this grid underneath your table is where you define measures I think the Microsoft idea was here to get this excel like feeling for people working with Excel who are switching to another services so you right click here and you can start typing your measure for instance movie count double dot equals and type for instance some of movie ID but some is not the best so let's do a distinct count of our movie ID let's hit enter as you see we have nine thousand one hundred twenty five unique movies in our table and if you define that measure it's as simple as simply going to your solution Explorer right hitting deploy and it will deploy the latest model version once the deployment is finished you can hit Klaus go back to part bi and simply hit refresh to get the your latest model version and you can start using your newest measures in your model this is the great part about another services because you can actually instead of doing matrix just change the visualization type to for instance a bar chart or use a pie chart but hold on that's not all there's one additional amazing thing that I want to show you in Visual Studio there's a really cool extension that you can install if you go to tools extension and updates there's something called base normalizer can simply install it for free and use it but it allows you to do by going to tools there's new option called new tabular model comparison so let's do a quick demo of that because let's point what is the issue with the current deployment model if I will actually introduced a new measure let's call it row count and it's gonna be simple row count I'm sorry count rows over our input table it's called and I somehow I messed this measure up and I added one so it's not correct but no one would notice that because I made that miss mistake and I would deploy this to the server if I'm gonna right click here to deploy he will deploy and overwrite the current model without showing me what changed and without letting me know that I actually made that mistake so the great thing about peace normalizer allows you to actually do a comparison between your current model in your visual studio and a model on another services so you simply hit compare you go here you hit on the compare button and you compare between your local model and another services you hit OK it will create a new comparison something here with the tool is the slide zoom incorrect but don't worry about this and notice very cool stuff here it actually tells me that on this measure there's an update so once I deploy the model movie count will be updated with the new part where the +1 was changed and I could I can actually review it between the original and the target that I'm deploying and you can also do for the row count as you see this has the status of create so once I do that I will actually create a new measure and overwrite the old one but additional great thing about this is that you can actually change this update to skip which will skip the changes on this particular measure if you don't want to deploy it yet next you hit validate selection it will tell you what based on your selection on the action column what will happen and simply run update are you sure you want to update target database and this is of course everything that happened here is based on this log at the bottom yes I am hit ok I need to provide the account key again you every now and then will need to do that because it doesn't cache and the key for that long so let me go to blob storage go to access keys crop it again put it here hit OK and it deploys the current model right now it very quickly deployed it hit close do you want to refresh comparison yes I do and currently the differences you see is only on the movie count which I of course decided to skip for now and that is amazing way to do enterprise level deployment ensuring that you don't overwrite your colleagues changes and impact their project so let's finish by going to the portal and showing you what are the other options available for another services on the left hand side panel you have analytic services admins so this is the list of users who can actually deploy manage models but also manage permission and roles and security on another services if you're integrating with on-premises you have section on-premise data gateway you can also backup your another service to the storage account if you want to you have connection strings if you're connecting from other tools using Emmas all of you can actually get the connection strings from here you can firewall etc etc important thing about firewall is that if you hit it alone you will actually need to decide what are the IPS allow it to connect on all these services so be careful about this because you're gonna set it like this enable firewall on and disallow Part VI service your PI bi reports might stop working and there's not many more options here available because this is as easy as it gets of course you can also do scaling here if you want so you can change pricing tiers later on you can change how many replicas do you want but of course I would need to upgrade to standards to get replicas and things like this and on the closing note if you go to overview and grab your management server you can also connect from management studio and to do that simply hit connect analyst services and paste your URL here select active directory of MFA hit connect it will prompt you to login I need to specify the possible sign in and you're connected to another services as that that easily in database of course you're gonna find your models that you deployed your tables roles management studio is first of all free and second of all allows you to manage a lot of staying things about annan services that you cannot manage from portal so it's one of the crucial things when working with another services for the cloud as you see Building Solutions for other analysis services it's not that difficult while it has its downsides it definitely allows you to schedule reports into much larger data sets and provide additional benefits to your organization that's it for today if you liked the video hit thumbs up leave a comment and subscribe if you want to see more and see you next time [Music]

Channel: Adam Marczak - Azure for Everyone

Views: 48,268

Rating: 4.9721012 out of 5

Keywords: Azure, Analysis Services, AAS, Power BI, BI, Business Intelligence, power bi

Id: 4Fv6cHdL8S0

Length: 29min 53sec (1793 seconds)

Published: Thu Dec 05 2019

Reddit Comments

[u/JFancke]

I'm such a big fan of AAS. Plus combining SSDT /BISM-Normalizer with Tabular Editor and DAX Studio makes development pretty bearable for consolidated models.

[u/SLeepdepD]

The lack of flexibility with Live Connection has lead us to focus on dataflows for sharing data with users.