Moving BI to the Cloud: Ditch SSAS?

 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:

To get in depth knowledge on Microsoft business intelligence, enrich your skills on Msbi online training

2. All on-prem except PBI

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:

3. BI in the cloud - Power BI acting as SSAS

BI in the cloud - Power BI acting as SSAS


However, there are some drawbacks to this option (given the current offering of SQL Datawarehouse): Learn more from Msbi online course

  • 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) 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:

4a. SSAS on-prem as semantic & security layer

SSAS on-prem as semantic & security layer


SSAS in VM 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 Petabyte, 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).

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:

5. Ditch SSAS

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: RLS in Power BI, Storage in SQL Database

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.

Configure the SSAS cube project with proper security and encryption of passwords. It needs to set the password first before executing the files. Thus, to learn more about cubes and their deployment practically, go through MSBI Online Training Hyderabad.

Comments

Post a Comment

Popular posts from this blog

What is Power BI: Architecture and Features Explained

Image
We all know that Power BI is one of the best BI tools, and many organizations are using this tool to generate reports and dashboards to make effective business decisions. Before using the Power BI services and features, you should know about the Architecture of Power BI. Because, you should know how the Power BI services, components are being used to transform the data, create the reports and dashboards. To get in-Depth knowledge on Power BI you can enroll for a live demo on   Power BI online training Microsoft Power BI Architecture: Power BI is a business platform that includes several technologies to work together. It delivers outstanding business intelligence solutions. Power BI Architecture contains four steps.  Let us discuss these four steps giving insightful information about each one of them. Data Integration Data Transforming  Report & Publish Creating and Dashboard 1. Data Integration:  Data is extracted from different sources wh...
Read more

Fundamentals of Blockchain Network Security

Image
Blockchain is a revolutionary technology, yet its security is often over-exaggerated. While its design does help protect against a variety of different security threats, the technology is vulnerable to attack. In this post, we discuss three of the fundamental security assumptions of blockchain and how they are violated. (Mostly) Benign Participants Blockchain technology is designed to create a secure, decentralized, and distributed record of the history of the blockchain system. The way that this is accomplished is by relying on the network of blockchain participants to (mostly) act in their own best interests and in that of the system as a whole. To get in depth knowledge on  blockchain  , enrich your skills on  Blockchain online training  professionals Blockchain wants to make sure that users participate in block creation, so that the system remains decentralized. It does this by providing incentives for creating these blocks in the form of block reward...
Read more

Explain about Informatica session properties

Image
Session property is a series of instructions on how and when to transfer data from source to target.Just like other tasks you create in workflow manager, a session property is a job. Any session you create must have a corresponding mapping. A session may have one mapping at a time, and can not be changed once assigned. This must be added to a workflow to perform a session mission. To get in-Depth knowledge on Informatica you can enroll for a live demo on  Informatica online training parameter in document lsi present focus Informatica kw density 1k-100k fkw density 1 flesch 60 plagiarism unique  A session may be an object which can be reused or not reused. If you create a task creator session, it can be reused, but if you create a workflow designer session, it is non-reusable. The multiple workflows can be added to a shared session. Session Properties in Informatica Using the session property you can configure various session features such as pre- and post SQL scripts, log file...
Read more