SSAS Multidimensional vs Relational Database: Import Performance

[Anonymous]

Hi Everyone,

I love coming against scenarios like this! Curisosity kicks in and off we go 

Below is the scenario explained.

How am I pulling data into Power BI: Import

What Data Source is being used: SQL Server DB 

Select Tables or Write Queries: Select Tables

What data am i importing:

What transformations am i applying: Only Data Type changes

Time to load all the above data into the Power BI Model: 4 minutes 

Why use a SQL DB source instead of SSAS Multidimensional: Query Folding (Server-Side transformations)

THE QUESTION THAT IS ON MY MIND:

Why when i am importing the same Fact Table (Only Fact Table, not the dimensions) as above but instead of using SQL Server DB i am using SSAS Multidimesnional Cube as a source does it take 16 minutes?

When you think of it without too much depth, many people including myself initally thought a CUBE would be faster then a RDB, but i am starting to believe different.

Is it becuase the CUBE is not suppose to be queried at such a low level of detail due to no having pre-stored Aggregations existing?

- Apolgies, but i am no expert in SSAS, but i did this though: 

- https://blog.crossjoin.co.uk/2015/08/26/how-to-optimise-the-performance-of-mdx-queries-that-return-t...

The beauty of using SSAS as a source to Import data, is the fact that many mechanism such as Last Year measures, WTD measures, LFL measures are all available whilst they are not available in the Relational Database i am using. However, when creating solution in Power BI i am thinking of Performance continously (both memory & cpu). 

Has anyone else have a similiar scenario, whereby their SSAS Source is much slower in retuning the same data then a Relational Databse they have?

Thanks all,

Laz

[v-chuncz-msft]

@Anonymous,

You may take a look at Deep-dive into query performance with SQL Profiler and Power BI Desktop.

[Anonymous]

@v-chuncz-msft Thanks for the reply! I will be sure to read through.

Still curious to see opinions/replies from other members of the community