cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
anandav
Continued Contributor
Continued Contributor

SSAS architecture for Power BI

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

Annotation 2019-12-20 134144.png

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.

1 ACCEPTED SOLUTION
JosefPrakljacic
Solution Sage
Solution Sage

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

View solution in original post

1 REPLY 1
JosefPrakljacic
Solution Sage
Solution Sage

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

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors