SSAS Cube, SSMS & Power Bi advice needed

[cottrera]

Hi Community,

I am not a warehouse expert but I am after some knowledge regarding Implementing SSAS Cube to our business. I currently work for a small organisation with 150-200 power bi end users and 4 power bi report developer / creators.

Our data is small, and the biggest model may reach 4 million rows. Our warehouse uses SSMS although some reports will connect to excel via SharePoint / OneDrive.   The warehouse is performing well however a handful of views are slow. Many of our tables are wide and long.

 It refreshes once per day, usually between midnight and 8 am.

Our data warehouse lead is current trying to persuade the warehouse manager to move all our data to SSAS cube. He is keen to try and aggregate many of the tables so that us report developer / creators have pre-defined measures.

All though I am not familiar with SSAS cube. I feel that this would be a good opportunity to work on our facts tables making them narrower and longer and adding better primary keys. It would also be a good opportunity to have clear dimension tables.

What I am not comfortable with if the warehouse lead aggregating and predefined measures (sums, counts, averages) to many of the tables. The reason being I can there being less flexibility with data modelling and use of DAX. Also, we will lose the granularity, for which our end users require, when drilling into their insight report pages.

I can however see a requirement to aggregate some table in SSAS cube, if they only being used for things such as K.P.Is.

Can you please advise the Pros and Cons of moving our warehouse to SSAS cube?

Thank you

Richard

[cottrera]

Hi Xiaoxin thank you for your resonse. Yes our data warehouse lead is trying to SSAS tabular cube. My argument is that I would prefere to do the modelling in power bi and add DAX functions there.  I didnt realise that transformation (power query ) was disabled on the live connection os SSAS. I can see a few business needs to use the live SSAS tabular cube. , such as K.P.Is. But the import connection to SSAS feels a better approach. However I will need to read the document on your link a few times to fully understand.

thank you

Richard😀

[Anonymous · Accepted solution]

HI @cottrera,

Did you mean the SSAS tabular cube? For that type of data source, we will use live mode to get data(the import will fix the file structure and data type) and some of the edit features have been disabled in this mode.
You can take a look at the following link to know more about the difference between power bi connection modes:

Power BI Connection Types: DirectQuery, Live, or Import? Tough Decision! - RADACAD

Regards,

Xiaoxin Sheng