This write up assumes the reader is familiar with general Azure concepts, Analysis Services and OLAP technology, and development environments.
Please refer to my own presentation for some introductory information as a supplement to this post.
This post will cover the benefit of using such a product within an organization, migration practices if moving from on-premises, considerations when comparing to on-premises implementations, automation tools to optimize an organization's Azure Analysis Services build, and considerations for return on investment.
Business intelligence structures are ultimately striving for transparency, efficiency, and usefulness of meaningful data to run a business. Azure Analysis Services allows for this through informative and fast reporting underpinnings.
For example, here is a Power BI map that is plugged into an Azure Analysis Services instance:
This is showing sales by cities in a sample data set, Wide World Importers.
It can provide insight as to where the company is selling the most product and possibly why it is not selling to cities that report lower profits, all with the backend power and speed of Azure's massive resource pool.
A standard, albeit very simplified, architecture of data flow in an organization typically looks like this:
In this model, data sources are flowing into a pool or data lake in its raw format, after which data gets transformed into "business objects", or tables that represent business entities properly. Those entities are staged for models in the Azure Analysis Services engine, before being read by reporting mechanisms.
This is much more easily said than done. For the purposes of this post, it is assumed that this is all working properly for use of the sample data set in the Azure Analysis Services layer.
A company using this model can retrieve insights and trends, save some space and time, have reports on portable devices, and explore data outside of the organization relatively easily.
Azure Analysis Services works with a variety of reporting tools, each with their own flavor of presentation, such as SharePoint, Power BI, and other third-party applications.
This is a strong case to have Analysis Services within a business intelligence architecture, in general.
When implementing an on-premises solution for Analysis Services, or with any physical infrastructure, there are several opportunity, time, and monetary costs to take into consideration.
- Time for software installations
- Specialists for physical implementation
- Networking and cabling
- Aging hardware and software
- Physical drives
- Physical CPUs
- Real estate for a data center
- Backups and redundancies
- Disaster recovery plan(s)
- Security roles and access matrices
- Regulatory and audit measures
These are items to weigh against a decision to move to Azure Analysis Services when considering implementation options.
Where: The AAS module can be found in Azure under Intelligence + Analytics
How: A new instance can be added easily with the "+Add" button
- Resource Group to add the instance to
- Location to place the instance in.
*Not all locations support AAS at this time
- Pricing Tier to charge for usage and determine how much of Azure resources the instance uses.
*Pricing Tier analysis is covered in the below sub-section
- Backup location using Azure Containers
An AAS instance can be paused and started up again in cases when it should not be used. This is a useful feature as AAS instances only charge Azure subscriptions when running. While they are paused, no charges are applied.
- Access Control allows for Azure Active Directory user access by role or by Azure Virtual Machine
- Firewall whitelisting of IP address ranges
- Troubleshooting by sending a request to Microsoft
- Metrics view to chart out usage and reporting for the instance
- Backup Azure Container location in case of a needed change
- Automated Deployment Scripts for quick re-deployment or deployment to different environments (impacts the entire Resource Group)
- Scaling resources based on pricing tier, which can be changed by clicking a button
The pricing model uses the concept of QPUs, or Query Processing Units. About 20 QPUs of computational power is approximately equal to one virtual CPU. At the highest pricing tier in the North Central US location, it estimates about 20 3.5 Ghz processors running model queries for reports, with up to 100 GB of in-memory cache, which is a significant amount of power.
AAS only charges for usage. If the instance is not being used or is off, there will be no charge. If there are queries running every second of every day while it's on, the full estimated amount will be charged.
A more in-depth pricing calculator for lots of Azure services can be found here.
Azure Analysis Services allows for administrators to change an instance's pricing tier to scale the power of the instance.
There are three groups of tiers in order of power: D (Developer), B (Basic), and S (Standard).
Scaling can be moved up from a lower group to a higher group, but cannot be scaled back down to a lower group. The scale can be moved around freely within a group however.
When changing the instance scale, all connections are dropped until the new scale is stood up. This only takes a few minutes.
An AAS pricing tier overview can be found here.
For the purposes of analysis, the highest pricing tier will be analyzed within the North Central US region.
Azure estimates this cost to be $6,033.84 per month, which allows 200 QPUs and 100 GB of in-memory cache for the instance to use.
This cost assumes that the instance will be used at full capacity every second of every day in the month. Realistically and pragmatically, this will not be the case in an organization.
Given that $6K price tag as a constant usage value, we arrive at ~$8 per hour assuming a 30-day month and a 24-hour day.
The first consideration to apply would be an 8-hour work day. If the instance were to be started at 9 AM and paused at 5 PM, the cost reduces to ~$2K per month. This is still in the assumption that when this instance is on, all 200 QPUs and 100 GB of cache is being used.
It is easy to see the ways to reduce cost just by knowing when the business absolutely needs the instance and when it does not.
A more robust Azure cost calculator can be found here.
Also consider this cost against purchasing a 20-core CPU alone, which can cost up to $20K when looking at 3 to 4 GHz models.
SQL Server Data Tools/Visual Studio
Azure Analysis Services models can be developed per normal as long as they are in the Tabular format as well as compatibility level 1200 or above.
Deployment to Azure is supported in Visual Studio 2016 and above.
Once an AAS instance is up and running, its connection string can be placed in the deployment location in the Visual Studio project.
The connection string can be found in the overview of the AAS instance and can be copied directly in Visual Studio in the deployment properties.
The instance must be on (not paused) for the deployment to connect successfully.
If the model's data source is a local database, an On-Premises Gateway will need to be installed and configured on both ends.
SQL Server Management Studio
SSMS supports connections to Azure Analysis Services instances in versions 2016 and above.
The connection is similar to logging into an on-premises server, but using the AAS instance connection string and Multi-factor Authentication (MFA).
It will prompt for a password via browser window, after which SSMS will display the AAS instance and any models available.
If a developer is accustomed to viewing Analysis Services in this manner, the only thing that has changed is the connection. All other SSMS features are available once connected.
Again, the instance must not be paused for SSMS to connect to it.
The web interface is in public preview at the time of this post, but it is an online tool to develop already deployed models as well as create new ones in an AAS instance.
Opening the web interface for editing models or creating new ones is found in the AAS instance overview.
Developing a new model using the web interface has a limitation at the time of this writing in that it does not allow for a connection locally via a gateway. New model creation only allows for Azure SQL or Power BI data sources.
A new model directly in the web interface will create a Tabular model in the 1400 compatibility level.
If editing an existing model, on the other hand (by clicking either the 'eye' icon or model graph icon in the 'Models' pane), it will keep its connection structure even if there is an On-Premises Gateway set up. Editing within the web interface also retains the model's compatibility level.
Once in the model, the development environment is fairly straightforward.
- Visual modeler: Automatic relationship builder. Visual editor of measures, relationships, and hierarchies
- Model script view
- Model view through Visual Studio, Power BI, and Excel
- DAX and visual query editor against model
- Deployment back to the instance
Microsoft has an introduction to the designer here.
SSAS to Azure
With the above information in hand, an organization can view Azure Analysis Services as a viable cloud option for reporting back-ends.
Given the standard architecture model introduced earlier in this writing, it is assumed that an organization will have several dedicated transactional and warehousing servers that support their Analysis Services models, if already implemented on-premises.
Using the deployment tools above, transitioning models from on-premises to Azure is a relatively non-exhaustive task to under take, making it the layer of the architecture to port to Azure first, if looking to move to an all-cloud solution.
Most enterprise architectures do not hold the framework to be completely cloud-based. This is especially true for transactional systems reading from application, mainframe, and user-generated data. Porting these systems to the Azure platform would not be advisable in the same timeframe that Analysis Services models are being ported.
Instead, the On-Premises Gateway is recommended to connect the ported models back to an organization's source data, creating a hybrid solution.
Keep in mind the following overhead tasks when migrating to the Azure Analysis Services platform:
- Models are to be in the Tabular format only.
- Any Multidimensional models would have to be either converted to Tabular, or they maintain their on-premises locations.
- Tabular models are to be in 1200 or above compatibility levels.
- On-Premises Gateway should be set up in the instance.
Assuming the overhead tasks are being implemented, there is likely more than one model being ported to Azure for an organization to report from.
Each model should be prioritized and scheduled for migration appropriately by organizational need. Having a development and testing environment in Azure via deployment scripts becomes helpful here.
Upon deployment, a testing plan should be put together based on the order of importance of the reports coming from the model.
If several models and reports match in prioritization, consider possibly tasking parallel work teams or members to handle the migrations with this approach.
Consider the ideal scenario presented in this diagram, which can be used as a migration project starter. It assumes parallel streams of work and is scalable to the amount of work needed for migration.
The models and reports being migrated and tested would first be deployed to a development or testing environment in Azure, using a lesser pricing tier than needed for production. Upon organizational approval of each report, the production data could go live with a higher pricing tier if needed. The development or testing environment then could be cleansed of anything that is approved for production already, reducing usage.
Tools and Usage
As mentioned earlier in this post, AAS only charges for usage of an instance. When it comes to automating, then, the context becomes how to optimize usage to reduce cost and still effectively provide timely reporting.
There are three stand out pieces of an instance that can be optimized for usage:
- Availability: Whether the instance is paused or not
- Scaling: What pricing tier the instance is in
- Service: Processing models (full refresh and/or incrementally)
The tools available for automation are:
- Azure Runbooks (covered below)
- Azure Function Apps (covered below)
- Custom .NET or PowerShell
- Classic SSIS/SSMS
Azure Runbooks and Function Apps
Runbooks can be used for quite a variety of things within Azure and are their own robust subject matter. Getting started with Runbooks can be done here.
To demonstrate a use for Runbooks to automate an AAS instance, one could be set up on an hourly schedule that calls a PowerShell script.
The script could check the day of the week and the hour of the day. Based on the day and hour, the scale of the instance could be changed, and the instance could be paused and restarted.
For example, this Runbook would see that it is 8 A.M. Monday morning, start the instance, and scale it to S2 for early morning operations. Then, around 1 P.M., the scale can be turned down to S1. At 5 P.M., the instance could be paused until the next day.
Holidays and weekends could be implemented in this script as well.
The schedules and scales needed would be determined by business need and could be changed easily. Several scripts could even be written if different schedule scenarios are foreseen, such as a holiday rush for a retail company.
As with Runbooks, Function Apps are another integrated module within Azure and really are their own subject. Read more about Function Apps here.
Function Apps are technically web applications hosted by Azure, and are constantly running unless turned off. This is slightly different than the idea of a scheduled Runbook when it comes to automation.
This makes Function Apps a good candidate, then, for determining when to process a model.
Creating a 'Timer' application (using the CRON timing format) to process models applies here.
The application could run a C# script using AAS libraries that processes the instance's models in full at 7 A.M. to ready them for the day's reporting, and every hour incrementally process any changes to keep the data up to date.
Again, in a real world application, the timing and sizing of model processing would be determined by business need.
Reading through the above material gives rise to an abundance of thoughts surrounding AAS and its potential impact to an organization.
I have compiled some of my own analysis on the matter below.
Advantages to AAS
- No physical space needed for servers
- One cloud platform for development
- AAS is a beneficial tool to learn as Microsoft transitions to Azure
- Scaling and automating is quick
- Integration with already-used tools
- Immense processing power is available from Azure
- Administration of instances is streamlined
- Built-in redundancy of the cloud, save for a location failure
- Simple and forecasted pricing model for managing cost
Disadvantages to AAS
There is a potential learning curve and acceptance for an organization with any new technology, and AAS is not exempt. Buy-in, research, and new skills are required.
Once models are ported to Azure, any workers with SSAS experience may not be able to use said experience any longer. Again, this comes with the territory of an ever-evolving technological landscape, but it is a possibility this is a replacement of SSAS administration on a resume.
Organizational Movement Track
- Initial cost: Ramp up -> Migration -> Automation -> Production
- Downstream savings: Space -> Physical CPUs and discs -> Maintenance -> Time
Deeper Dive Subjects
- What is the best automation scenario using Runbooks, Function Apps, query replicas, or other techniques?
- What is the true implementation cost considering down time, buy-in, and learning curve?
- What might a full migration plan look like once documented?
- What are the best incremental testing techniques once models are deployed?
- What are the best practices for maintaining an instance and applying developer resources once models are in production?
- What does the development cycle look like once an organization is using such a hybrid solution as described in this post?
AAS is a robust cloud solution for reporting structures. It features simple scalability, automation, and time and cost savings for an organization. There is a clear benefit to at least understanding its potential, and there is a lot more to explore.
Future features for AAS, from Microsoft:
- Multidimensional model support
- SSRS, Excel Online, and Power BI Embedded support
- Schema comparison for deployment
- Azure Data Lake usage as a data source for models
If you have any comments or questions, please feel free to email email@example.com, and thank you for reading.