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).

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:

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) querying1.

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:

SSAS on-prem as semantic & security layer

SSAS in VM as semantic & security layer

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 Petabyte2, 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)3.

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 Connect4 to get the data as-needed from SQL Database, et voilà: BI platform in the cloud! Something like this:

The Impossible Architecture: RLS in SQL Database, Semantic Model in Power BI

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:

Variation on the Impossible Architecture: Semantic Model & RLS in Power BI, Storage in SQL Database

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-support would cover the needs.

On the other hand: SSAS (including storage) would remove the need for a Data Mart in SQL Database

You can move to the cloud as much as possible. Here's a small decision table for possible architectures according to the needs

Share story

Founder of this blog. Business Intelligence consultant, developer, coach, trainer and speaker at events. Currently working at Dura Vermeer. Loves to explain things, providing insight in complex issues. Watches the ongoing development of the Microsoft Business Intelligence stack closely. Keeping an eye on Big Data, Data Science and IoT.

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.

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!