Hi all, I have a question:
I have a fact table that has over 500 million records.
This table includes records for the last 2 years.
The table has already been aggregated according to the client’s requirements.
The table is aggregated per Day. We are trying to reach an agreement with the client to aggregate by Month. But it is not clear yet. Now the biggest issue is the performance.
We need to prepare a model that can be accessed at least by 100s of users.
I don’t know if the combination of Load (dimension) and DQ( fact) is the right solution.
Does anyone have experience with this ? Or any advice on how to approach it?
Just an experience, a suggestion, an idea
Thank you very much
While we wait for other members to share their insights, I’ve taken the liberty of using “Explain Simply,” one of the tools available within Data Mentor (you can explore it here: https://mentor.enterprisedna.co/explain-simply. It generated the following results:.
Feel free to checkout more of our Data Mentor features as you work on your report. These tools are designed to help with tasks like the one you’re working on, and they might accelerate your learning curve.
Hi @blondIT - In this situation best option according to me is to have Dimension tables in Dual mode, Fact table in Direct query mode. Add Aggregation tables in Import mode to improve the performance. For more information on Aggregation table read the below Microsoft article.
Follow Power bi best practices like keep only required columns, use Incremental refresh etc.
Also, study on Direct Query performance improvement recommendations. They are lot of articles available on this.
It depends of what you try to achieve and how often data is changing and need to be refreshed by all of 100s of users.
Regarding aggregation - I often saw usage of monthly aggregation for longer period that were needed and for last month (or two) daily aggregation depends of the need.
Hi Ankit,
THANK YOU, this was exactly what I needed to hear, just have to be sure, you are always very helpful, I’m really grateful for that
have a nice day,
blondIT
