SSAS architecture for Power BI

[anandav]

Hi,

I am developing an architecture for Power BI and would like to know what is the best practice for using the below scenario.

1] Create "views" from RDBMS for SSAS to connect to

2] Connect SSAS to "views" using Direct Query

3] Connect to SSAS from Power BI using Connect Live

In the above scenario since all interactions are query based and data is only physically available at RDBMS, will this slow down the Power BI dashboard / reports?

I can do simple PBI reports and the response is acceptable. But will the above architecture scale well for several 100 millions of records - assuming news records gets added to the RDBMS/DW two or  three times a day?

What is the best practice?

e.g. materialised views in SQL, load data into SSAS rather than DirectQuery, etc.

Any adivce would be greatly appreciated.

[JosefPrakljacic · Accepted solution]

Hello @anandav ,

this depends on many factors but the following applies:

A materialized view (apparently you come from the Oracle world, because in SQL Server the views are called Indexed Views and are usually worthless) takes load off the RDBMS and is often used as a performance improvement. You just have to make sure that the refresh interval is correct.

Loading into the SSAS Cube is always better and faster than a direct query. It also depends on whether you need the SSAS Cube at all. Please read this article.

In general, persisting data is always faster and better than direct access - no matter at what level in your architecture. But the problem is that it leads to data latency and you have to pay attention to the refresh intervals.

Maybe this Whitepaper is also of interest for you. Planning a Power BI Enterprise Deployment 

BR,

Josef