Azure Analysis Services - Data Model to Dashboard
Analysis Services: Past, Present, and Future
Over the years, Microsoft Analysis Services has taken a few forms. Starting out as an OLAP service, in 1996, it's grown to be the backbone of modern analytics. The original SSAS models were all hulking, multidimensional beasts, scary to the average developer. But that all changed in 2009 when SSAS got a facelift in the form of Tabular SSAS. With the release of Tabular SSAS came a wave of self serve BI- PowerPivot, PowerBI, and DAX all exist because the Tabular models were accessible and easy to use. After nearly 10 years in the trenches, the On-Prem Tabular model has nearly run its course and the new age of "Big Data" and "Cloud data" is upon us. Azure Analysis Services is the Genetically Modified Twin of the old SSAS Tabular Models.
Same language, faster responses.
Similar connectivity, more options.
Familiar structure, new face.
Excel made spaghetti solutions too easy
Order, not spaghetti
Everything in your office revolves around data: purchase orders, project ID's, employee clock-in times, account numbers, stock levels- even Credit Card purchases. Sometimes the data is unrelated, but often we need complementing datasets to make sense of other datasets. It's common to see Excel power users harnessing the VLOOKUP function to link two or more tables- but eventually, it's just too complex...
Analysis Services offers a saving grace. Something dependable. Every time you access the Model you can expect the same Data, the same Aggregations, and the same relationships. SSAS is always Predictable, never again will you consume something broken. With publish options, making sure the current version is stable easy.
Scalable HorsePower is now...
Moore's law says we should see the power of our machines double every 2 years. But in the BI world, it seldom feels that way. Once we commit to a system we can get locked into the hardware or software for the next 10 years! Talk about stifling innovation. SSAS changes that. Long gone are the days of your data model being limited by the power of your machine. Analysis Services generates a model on a remote machine that is scalable and secure.
A wild Star Schema in its natural habitat
What's the catch?
Analysis Services requires someone who cares. Good reporting is a natural progression of beautiful data. Beautiful data only comes from careful curation and thoughtful planning. Choosing your datasets well AND making sure they answer important Business questions is not a 5-minute exercise. Although you could speed through the creation of an Analysis Services model- it takes weeks to make a great model.
A Brief Overview of AAS Features:
Solution Accessibility
AAS give us connectivity to any modern analitical program- and when packaged with PowerBI, it's downright dangerous. AAS is primarily used with PowerBI, but can be packaged to work with on prem excel or .NET mobile packages.
Data Connectivity
AAS uses the On-Premises Data Gateway to keep local data up-to-date and accessible on the "Cloud" models. Run on a schedule usually, the On-Premises Data Gateway is the Microsofts bet on the new interconnective world of Azure.
Fast Solution Deployment
Microsoft Advertises that you can set up AAS "in seconds". They're not too far off either. A fully developed model can be published to and ready to use in less than 10 seconds, no matter the size of the userbase.
Scalability
With AAS's scalible pricing, we can choose when to pay for more power. If it's the middle of the day, we can increase our BTU. Is it midnight? Scale it down to the developwer version. All in the name of conserving your Azure Credits.
Row Level Security
AAS natively allows dynamic filtering on users when RLS is enabled. RLS enables you to store data for many users in a single database and table, while at the same time restricting row-level access based on a user's identity, role, or execution context. RLS centralizes access logic within the database itself, which simplifies and reduces the risk of error in your application code.
Cross Filtering
AAS is tabular in nature- this means that relationships are key to creating contextual data. If you've got your relationships set up properly, cross-filtering comes naturally. This makes finding new data across tables easy.
Stack Integration
AAS works flawlessly with the existing Microsoft Data Stack. SSIS, SSRS, SQL Server, PowerBI, and all the new Azure offerings have inputs and outputs for AAS.
Solution Re-Usability
We can redeploy, alter, add new features, take away features- and it's mindblowingly easy. It can sometimes be as simple as drag and drop.
Deployment: Easy Process, Hard to Get Right
Let's check out an incredibly basic implementation of Azure Analysis Services. We'll go over the Data Gateway, Model creation and connection inside PowerBI.
A few pre-requisites to follow along:
Visual Studio 2017 ( SSDT )
An active Azure environment
PowerBI Desktop
In the following steps, we want to demonstrate how simple the deployment to consumption cycle for an Azure Analysis Services model CAN be:
Step-by-Step Instructions
1) Install and Configure a Gateway
Install a gateway locally. Be sure to configure it with the same account that you'l be using in Azure Later. Be descriptive with the name of your Gateway, otherwise confusion may ensue. Take note of the Location ( West US in this example ), you'll need this info later..
2) Create a Gateway Connection in Azure
Head to Portal.Azure.Com to create a Gateway connection in Azure. Assign the same location as your gateway to this connection.
3) Create a Blank AAS Instance
Inside Portal.Azure.com, create a 'blank' AAS instance. Assign it the same location as your Gateway and Gateway connection. Again, be descriptive about your resource names.
4) Link your Gateway Connection to the New AAS Instance
Once the AAS instance is setup, the last step inside Portal.Azure.com is to connect your AAS instance to your Data Gateway connection. Again, choose the correct location.
5) Create and Name the AAS Instance
Back on your local machine, you need to open Visual Studio SSDT and create a new 'Analysis Services Tabular Project'. This local template is where we'll design how the AAS model works.
Note: The SSDT version of Visual Studio is different from the standard version of Visual Studio.
6) Set the Instance Properties
Choose 'Integrated Workspace' and set the Compatibility level to at least 1400. This ensures the model will be compatible with AAS.
7) Add a Data Source(s)
Once the model spins up, you can add data sources by right clicking on the 'Data Sources' folder and clicking New Data Source. This will open the default Power Query window, follow the prompts and choose the source you want to connect to.
8) Import Data Sets
Once you've chosen a source, the next step is to import data from that source. Right click on your new source in the sources folder and choose Import Data. At this point you can choose to transform your data furhter, or consume it raw.
9) Add Relationships
Click the icon in the bottom right to open up the Relationships view. Click and drag related fields from differnt tables to each other to create new relationships.
10) Edit the Deployment Properties
To edit the database name and upload location, edit the properties of the solution. Head to the 'Solution Explorer' and right click on the name of your project.
11) Edit the Properties Cont.
Inside the Solution Properties, you can edit the server location ( upload URL ), Change the Edition type and change the Database name for you AAS instance.
12) Deploy the Model To AAS
Once you're ready to deploy the model, hit the green start button, or, right click on the Solution and click Deploy.
13) Deployment Cont.
If you get all green check-circles, you're ready to consume your datamodel!
14) Deployment Cont. ( hint )
If you get an error on deployment, once place to check first that your gateway service is running on the machine you installed the gateway on.
15) Consume your AAS Model
Open PowerBI or Excel and use the get data function to open up the AAS dialogue. Key in or paste the AAS URL and the follow the prompts.
16) Consume your AAS Model Cont.
Once you see the Navigator, you're successful! Navigate to the model and click 'OK'. Once it's loaded you can start using your model for report construction or Advanced analytics.
What Value Can Analysis Services Add to my Business?
In addition to all of the topics briefly covered earlier, Analysis Services can open doors and add value to your business in the following ways:
TIME: Data models built without Analysis Services are impossible to manage and even harder to depend on. Time is saved when both those factors are obliterated via dependable, easily deployable models managed by Azure Analysis Services.
COST: In addition to reducing cost as a consequence of saving time, leveraging the scalable nature of AAS allows for dynamic resource appropriation. No confusing hardware depreciation. No bizzare license costs. Expected costs, managible loads and quick scaling are King in the Azure world.
ANALYTICS AVAILABLE: The very nature of Analysis Services is analytics. It started as a data mining tool nearly 30 years ago, and today it is a leader in the modern analytics world. Whether by it's perfect integration to PBI, or the pure horsepower accessible, there's no denying the flexibility AAS allows our reporting.
Our Latest Blogs
About FreshBI
Based in USA and Canada we have helped hundreds of businesses achieve excellence & success through business intelligence.
Power BI runs in our blood, and we are ready to take your business to next level.