SQL Server Analysis Services is a powerful analytics engine & Azure Analysis Services builds on it’s strengths & adds exciting new capabilities. Out of the box Scale up/out, high availability, a powerful semantic layer & now support for bi-modal analytics means that Azure Analysis Services may just have become a game changer.
So, full transparency, I’ve got a long history with Analysis Services from Microsoft. I actually worked with the 1st Beta more years ago than I care to remember and stuck with it off and on for several years.
Later in my career when Powerpoint became my tool of choice I was more hands off but still kept a fond eye on SSAS. A victim of its own success, it would invariably outgrow the server it shared with the underlying SQL Server DBMS and would need moving to its own dedicated server. Scale up obviously meant procuring & migrating to a new, larger server and when the limits of scale up were reached the alternative of scale out (across multiple servers) was not difficult but again not supported out of the box.
The advised approach required a separate, dedicated OLAP processing server – this would build the cubes which would then be copied (via a bespoke process) to one or more “Presentation” layer servers. Add in high availability and a full enterprise level cube infrastructure might end up with 5 or 10 servers, and, obviously, all of these servers needed a license and patching/support. Finally of course, the environment had to be sized for peak query times.
Of course none of these issues were confined to Analysis Services, all pre cloud applications have the same scalability & managability concerns once the limits of a particular server are reached. However, as all analytic environments tend to increase in scope and usage there would always be the pressure to do more in the environment. Keeping up with demand (especially on a 3 year hardware refresh cycle) wasn’t trivial.
Another issue with MOLAP (multidimensional OLAP) was its agility, or arguable lack of, as developing cubes was pretty much an IT only activity. SQL MDX, the SQL dialect for OLAP, is a powerful language and can build complex metrics in a few lines however (and I say this as a fan) it can be a little arcane. Its focus on tuples & sets can be hard to grasp for a newcomer and it was difficult to teach or to fully understand.
This, and the fact that all of the tooling was in Visual Studio or similar meant that the business had to come to IT to add new content. The rich semantic layer provided by Analysis Services hugely simplified “slice & dice”/”drag & drop” type analysis however the business user was always limited to the hierarchies, metrics, KPIs and overall structure defined by IT. Anything, even a new measure, would have to be requested, prioritised, scheduled and moved through the change management process.
Finally there was the usual frustration of an evolving product. Each release brought new, interesting features however because of the rigours needed for production systems (regression testing, down time etc) & the tyranny of the standard release cycle it could be 2 years from 1st announcement to implementation on the production OLAP systems of these features.
Although Analysis Services provided in-memory class performance before in-memory was in fashion & a rich semantic layer that provided a well formed business friendly abstraction on complex data, not every company had the in-house expertise to exploit it fully. Data Discovery tools came along which could do similar things (if not quite as well) as part of their overall data visualisation paradigm and often Analysis Services was implemented for point solutions in a siloed fashion (and owned in many cases by “Shadow IT”).
Step forward Azure Analysis Services….
Azure Analysis Services (AAS) potentially changes almost all of the above. AAS is the PaaS (Platform as a Service) implementation of the Tabular model mode from SQL Server.
Note: Analysis Services has 2 modes, MOLAP & Tabular. MOLAP is the traditional Multidimensional cube approach as discussed in the previous section, whereas Tabular is a newer, in memory approach which more closely aligns with the underlying tables on the sources. See this post for more details on the 2 modes.
From a development & consumption point of view AAS is feature complete with Tabular mode in standard SQL Server.
Azure Analysis Services – Manageability & Scalability
Whereas an on premise SQL Server Analysis Services instance requires considerable set up an AAS instance can be instantiated in a few minutes in the Azure Portal. During that creation the instance size (in terms of memory) can be defined at anything from 3 GB cache to 400 GB – compressed.
What that means in terms of uncompressed depends of course on your data however compression ratios of between 6 and 10 are achievable, meaning that theoretically up to 4 TB uncompressed data could be loaded into a tabular model if it was mostly numeric and “compression friendly”.
400GB is probably larger than almost all on premise Analysis Services instances so that pretty much deals with scale up, but what about scale out? Well, as announced in this Ignite 2017 presentation – “Delivering Enterprise BI with Azure Analysis Services” – about 46 minutes in, AAS will scale out across up to 8 servers.
Via the Portal or via code the server admin will be able to scale from 1 to 8 replicas of a given server to respond to concurrency requirements. The original server can be isolated as a build server if needs be (to prioritise model processing) then introduced back into the presentation layer when processing has finished.
Out of the box, Azure Analysis Services guarantees 99.9% availability. With AAS being a PaaS solution all patching of the Tabular model servers happens transparently and as regards new features there is no formal “2 year release cycle”, features are announced, released on preview and then moved into general availability – the above scale out feature for example will make its way out to all Azure Analysis Services servers automatically.
So in terms of manageability we have:
- 99.9% availability baked in
- Scale up/out controlled on demand via essentially 2 sliders
- No patching
- Automatic regular feature roll out
This still however leaves the issue of Analysis Services being an IT led activity with limited agility. How has that changed?
Azure Analysis Services – Agile Development
There’s really 4 aspects that have arguably made Azure Analysis Services far more “user friendly” and agile.
DAX (Data Access eXpressions) is the favoured query dialect for the tabular model. It was designed to be simpler than MDX and whilst this can be arguable when one gets into very complex metrics the fact remains that DAX is available in SSAS, AAS, Power BI & Excel. As such it’s more likely to be familiar to a business user than MDX and it’s certainly more “table” like than the multidimensional MDX dialect.
Portal based Model Designer
As mentioned previously the standard tooling for building Tabular models is SQL Server Data Tools within Visual Studio or SQL Server Management Studio.
However, available in preview is a new, wholly web based AAS designer. This tool is evolving rapidly and whilst it doesn’t provide all the functionality of the mainstream tools it certainly provides a model development capability for non IT people. In particular it enables the business user to develop new metrics and rename existing ones using business friendly terms (as well as organising those attributes into appropriate folders).
This provides the capability to provision a small scale “sandbox” Analysis Services Server for the business teams which can then be used to develop new capabilities & insights. Once the design is hardened the tabular model can be scaled up or moved to a “production” environment as required.
Data Catalog – herding the data silos
Flexibility & agility can however create an explosion in metrics & KPIs across multiple tabular models:
- Is this the enterprise level “single version of the truth” version of “% Market Share” or a local or business unit definition?
- How complete is the data in a given Tabular model?
- When is this data refreshed and from where?
One option is to use Azure Data Catalog which is able to gather & host additional metadata on multiple data sources, including KPIs, dimensions & measures.
This searchable resource can reach out to enterprise and localised AAS Servers and used to record the data provenance of each. This in turn can be used as and when tabular models are being consolidated, which takes us onto the most important item for agility.
Merging Power BI & Azure Analysis Services Models
It’s beyond the scope of this post however a major challenge in modern analytics is the balance between centralization & self serve. To summarise, too much centralisation leads to stagnation and lack of agility, too little leads to duplicated effort, multiple versions of the truth & high cost, siloed solutions. As mentioned above, Azure Data Catalog can go some way towards identifying these silos but in the ideal world those silos (which represent valuable analytics assets) are “promoted” into the mainstream, allowing them to be shared across a wider audience.
Tabular AAS goes some significant way towards solving this as it’s engine is essentially the same as the Microsoft Data Discovery tool, Power BI. This ignite 2017 session, “Creating Enterprise Grade BI Models with Azure Analysis Services”, covers this in a lot more detail from about 36 minutes in but in essence there are 2 major capabilities to consider.
Firstly, a Power BI model can be imported into an AAS server to create an Azure based instance of the model. Secondly, an open source tool, BISM Normaliser, can be used to merge that model with an existing one.
This is a fairly unique feature in the market. Enterprise level reporting and localised data discovery are distinct areas, migrating a dashboard or report from one to the other is generally fairly painful and requires a lot of rework. However, as Power BI and AAS tabular are essentially the same technology under the covers one can be “upsized” and deployed to the other. Furthermore, all changes can be generated as script which means this merge & development process can be included in a DevOps style move to production cadence.
This post opened up with the benefits of Microsoft Analysis Services. A powerful and responsive technology it nevertheless needed complex infrastructure to scale out & was essentially an IT led centralized Analytics environment.
What we now have is a PaaS solution that is feature complete with SSAS Tabular but adds exciting new capabilities. AAS can be scaled up and out in minutes, and paused completely when not needed. It has built in high availability and essentially looks after itself in terms of patches, new features & backups. AAS can also deploy very large scale models which in turn can be merged with localised models thus promoting bi-modal development and agility.
In terms of authoring it has a full powered and mature set of tooling yet it also has a simplified user friendly web based interface which allows for “sandbox” type development or, as previously mentioned, a Power BI mashup can be imported and promoted to full model status.
And we haven’t even mentioned the main features which it shares with SSAS. Because it is wholly in memory a Tabular model performs with sub-second response times even over very large data sets. Its semantic layer abstracts away from complex data structures into business friendly dimensions, hierarchies, measures and KPIs and its “perspectives” feature allows for the publishing of focused “data mart” style data sets at the logical level.
Finally, it can act as a data virtualisation layer, reaching out across multiple distinct data sources to create a single image in memory of up to 400GB in compressed data, without formal data movement, data staging, synchronisation issues or ETL processes. It’s not magic of course and if you are pulling several billion rows every day from 3 different sources it will take some time to build the in memory model but it can certainly act as an integration/presentation layer which can rapidly deliver combined analytics across different systems.
Given the state of AAS today, it’s possible that it could become one of the core components of your Bi-modal, self serve, Enterprise Analytics strategy and a game changer for your BI stack.
The 3 presentations in this blog post inspired the above and give an excellent grounding on where AAS is right now and where it’s heading.