Moving BI to the Cloud: Ditch SSAS?
12 april 2016 2016-04-12 10:30Moving BI to the Cloud: Ditch SSAS?
Some time ago I read Chris Webb blogged "Thoughts On The Power BI Announcements At The MS Data Insights Summit", where between the lines was this rather interesting point:
For the last few years my customers have asked me when MS was going to release SSAS in the cloud and I’ve always replied that Power BI is SSAS in the cloud – it’s just tightly coupled with a front-end right now.
As I'm currently planning to migrate the entire BI architecture of one of my customers to the cloud, this made me think: can we ditch SSAS as we know it already in favor of Power BI? What are the alternatives?
To study that, I've put some diagrams together to show the possibilities of moving BI to the cloud. First, I'll discuss the possible architectures, then the impossible architecture (but maybe the situation I was looking for).
One thing I should mention before diving in is the VERY valuable input on Twitter from @jrolandjones and @SQLChick, as well as a rather useful blogpost from Melissa Coates / SQL Chick about Direct Connect.
Update (15:07): It seems I've missed a crucial news update, being Power BI now having RLS (as of March 31). Sometimes it's hard to keep up. I've updated the post accordingly.
Possible Architectures
1: Power BI cloud on existing infrastructure
The first option is the current situation at my customer's. Added an Enterprise Gateway to a rather 'classic' BI infrastructure, providing Power BI in the cloud. Variations are endless here - here's the essence I think:
SSAS has several uses here:
- Semantic Model:
- Calculation (measures calculate correctly on different hierarchy levels)
- Abstraction (hiding of technical columns, providing perspectives, etc.)
- Row-level security (RLS; being not in SQL Server < 2016)
- Caching (performance achieved through local / in-mem storage; Columnstore Indexes may minimize the performance gap though)
2: Move DWH to the cloud
SQL Datawarehouse is Microsoft's cloud offering for a scalable DWH:
- Can handle Petabyte+ data
- Upscale / downscale within seconds
- separation between storage and compute
Power BI can connect directly to SQL Datawarehouse. Sounds like the architecture can be simplified, with all BI stuff at least operating on PaaS, which means less operational stuff to care about:

However, there are some drawbacks to this option (given the current offering of SQL Datawarehouse):
- Power BI has limited storage capability (10 GB in total, 250 MB per model)
- SQL Datawarehouse doesn't offer RLS
- I haven't tested this, so don't take my word for it, but I tend to think SQL Datawarehouse maybe isn't the ideal fit for interactive (BI purposed) querying[ref]It has to do with the internal structure of SQL Datawarehouse, the dataset sizes it can handle and the way Microsoft positions it. More on that in a later post.[/ref].
3: Using self-managed SSAS in a cloud infrastructure
To provide row-level security as well as ensure responsive interactive queries, you could re-introduce SSAS - either on-premises (with a gateway) or in an Azure VM:


In both cases, SSAS is used for all things it was used for in the first architecture:
- semantic model
- row-level security
- caching
Power BI connects using Direct Connect, so you won't be able to mess up and add any modeling in there.
Although every need addressed by the original infrastructure can be addressed with this new infrastructure, note that:
- The highest as-a-service level for SSAS provided is IaaS
- You're not really limited in the size of your DWH, as SQLDWH can easily store more than a Petabyte[ref]Before trying, remember you still pay per use ;-)[/ref], which means SSAS-TM (being in-memory) might not suffice when the dataset is really large. Possible solutions are using Direct Query, or using Multidimensional (SSAS-MD)[ref]SSAS-MD allows for HOLAP storage: aggregates are stored, but details are queried. On top of that, MD doesn't keep everything in-memory[/ref].
The Almost-Ideal Architecture
Reading all of above, one could argue that the best way would be to ditch SSAS: bring the semantic model to Power BI, handle RLS in SQL Database, use Direct Connect[ref]Direct Query, Direct Connect and Live Connections are essentially the same[/ref] to get the data as-needed from SQL Database, et voilà: BI platform in the cloud! Something like this:

A variation on this would be to handle the RLS in Power BI - maybe this is even better, as it makes access control more transparent:

To be honest: while studying all possibilities, this appeared to me as the solutions for a fully cloud-based infrastructure. Too bad it doesn't work out:
SQL Database (as well as SQL Datawarehouse) doesn't use AD (yet). So while SQL Database does offer RLS, Power BI queries are not executed from the user's account 🙁 . RLS as we know it from SSAS cannot be accomplished.Joey D'Antoni corrected me on this one - SQL Database does actually support Azure Active Directory federated with local AD, but I've not been able to test this one yet (my test environment doesn't have federated AD). Stefan Kirner comments below that "The authentication mode aad with password on sql azure is not available in power bi"- The Direct Connect functionality from within Power BI disables the possibilities to alter the semantic model: no more correct calculation of measures on all levels of a hierarchy
... what remains isn't really what I wanted when I said 'ditch SSAS', for one of the primary reasons to use SSAS (the semantic model) is now away:
Summary: Ready for take-off?
To wrap up, here are the four architectures discussed moving BI in the cloud. Of course, there are endless possibilities (including all kinds of weird constructions using VMs) but for clarity's sake I've zoomed in on these four: three possible, one impossible (but one that seemed a logical solution to me when studying the entire architecture)
Summarized, I conclude that:
- There still is no real "full cloud" (loosely defined as "entire BI at least PaaS") solution entirely on Power BI + Azure at this moment
- The painfully missing piece for a "full cloud" solution is the combination of RLS and a semantic model, best summarized as "SSAS" 😉
- Allowing the combination of a semantic model (maybe within Power BI) with Direct Connect
+ extending the SQL Database with AD-supportwould cover the needs. - On the other hand: SSAS (including storage) would remove the need for a Data Mart in SQL Database
- Allowing the combination of a semantic model (maybe within Power BI) with Direct Connect
- You can move to the cloud as much as possible. Here's a small decision table for possible architectures according to the needs
Analytic Model > 250 MB (compressed) |
Semantic model needed | Row-level security required |
Ways to go to the cloud |
No | No | No | #2, but substitute SQLDWH with SQL Database |
Yes | No | No | #2, use Direct Connect |
No | Yes | No | #2, build Data Model in PBI and use SQL Database instead of SQL Datawarehouse |
No | No | Yes | #2, build Data Model in PBI or use SQL Database instead of SQL Datawarehouse |
Yes | Yes | No | #3, you need SSAS for a semantic model > 250 MB |
Yes | No | Yes | #4 |
No | Yes | Yes | #3, you still need SSAS for RLS. Near future: Power BI too |
Yes | Yes | Yes | #3 for sure |
Comments (8)
Koen Verbeeck
Interesting post.
Power BI has announced to implement RLS itself in the near future, so that might be an option as well.
Gilbert Quevauvilliers
Great post. What about the new RLS in Power BI? That could potentially work. But you would still have the 10Gb limitation for the model though?
Koos van Strien
Thanks for the input! I see that RLS is available already in Power BI: https://powerbi.microsoft.com/en-us/blog/power-bi-service-march-update-part-two/. Updated the post accordingly :-).
Please note that the 10 GB is the limit for ALL models, the per-model limit is 250 MB (!)
Joey D'Antoni
One quick correction, SQL DB does support AAD which can be federated with AD.
I'm mostly of the opinion that SSAS is still needed in environments that will need to support a lot of ad-hoc querying
Koos van Strien
Great! I think I misconfigured my SQL Database then.. Will update this ASAP!
Stefan Kirner
First: Great article, a lot of well researched information. 2 questions:
Have your tried to actually use Azure AD Authentification on SQL Azure in power bi portal ?
I had such a scenario on customer site and didn't get it to work. The authentication mode aad with password on sql azure is not available in power bi.
Regarding RLS: instead of manually adding each filter to the roles is it possible to use a security fact table? This would be the way it is usually implemented in the dwh field.
Koos van Strien
Hi Stefan, thanks for your reply! To be honest, I didn't have AD Federation in place in my test environment. Based on my tests there I first reported AAD logins weren't available at all in SQL Database, but Joey D'Antoni corrected me on that one. I'll incorporate your additional input ASAP.
As for a security fact table: I'll come back on that one later. Stay tuned!
Moving BI to the cloud: Consider this… | @MSBIBLog
[…] the possibilities of moving my customer's BI infrastructure into the cloud, my primary question was how to handle the lack of cloud SSAS. But there's a lot more to keep in mind. Here's a few things that I think should be thought of from […]
Comments are closed.