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:

1. There still is no real "full cloud" (loosely defined as "entire BI at least PaaS") solution entirely on Power BI + Azure at this moment
2. 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
3. You can move to the cloud as much as possible. Here's a small decision table for possible architectures according to the needs