Slow reports - to cube/SQL Server or not

I have found many irregularities in the data that complicates the design of a good model. Some tables have data that seem to be missing and no one can explain what and why it is missing or what to do about it.

So, to make sure I can develop a good model I feel that merging all related data would be the only realistic option to avoid too much DAX chaos. After merging everything, there are around 40 columns and half a million rows, with around a dozen dimensions. So, of course the single fact table with so many columns has produced the challenge of slowing reports down, even though most of the columns are numerical ids. Nearly all the DAX measures would be counts and a few date calculations.

Would it be a good idea to build a SSAS cube so that all the data merging and heavy calculations can be done by SQL Server? The challenge with this idea is that it might take a while to set up and this introduces so many more steps before data can be analyzed.

Or, is there another way to deal with this challenge? I was thinking of doing the merges in SQL Server and then using DirectQuery to load the data into Power BI thus eliminating the process time for merges, but there’s still the problem of so many columns for DAX to deal with.

The next obvious step would be to break the single fact table into multiple fact tables, but this would bring me back to the issue of missing data and the unknown of how this affects the rest of the data. So it might be tricky knowing how to break the single fact table up into many fact tables.

Hope you can help!

Bumping this post for more visibility.

Hi @powerbideveloper

Power BI is primarily a Reporting and Analytics tool with option of doing ETL using Power Query. Power BI works really well if data is already harmonised i.e. in ready to consume form with minimum transformations.

It’s always preferable to do any resource heavy work like Merging at Backend using an ETL tool like SQL or in an Analytics Server like SSAS,AAS.

You can go with both ways i.e. creating a Tabular model using tools SSAS,AAS or doing merging etc in SQL. Can check advantages of both online.

Breaking in multiple Fact table is not a recommended approach.

Thanks
Ankit J

1 Like

We hope @ankit’s answer above helped you @powerbideveloper.

If not, how far did you get and what kind of help you need further?

If yes, kindly mark as solution the answer that solved your query.

Hi @powerbideveloper, due to inactivity, a response on this post has been tagged as “Solution”. If you have any concern related to this topic, you can create a new thread.

Hi @ankit!

Just a quick question before I close, what do you mean by “Breaking in multiple Fact table is not a recommended approach”?

Are you saying that breaking up a single merged table into more than one fact table is not a good idea, or what exactly do you mean?

Thanks!

Yes, that’s what I exactly mean. Ideal scenario is to have Star Schema i.e one Fact table surrounded by Dimension/Lookup tables. If that not possible, we shall still try to keep Fact tables as minimum as possible

1 Like

Hi, did Ankit’s response satisfy your question? If so, kindly tag the post as SOLVED.

Thanks