Data model optimization - too much combinations

[timalbers]

Hi all,

first of all: I really appreciate every single idea about this topic, even if it's just brainstorming - I am sure it will help me.

So, basically my customer has a wide portfolio of products which are for rent. There are approximately 20,000 items.

The main objective of the dataset that I am trying to create is to give information about the occupancy rate of each item.

This rate should be calculated on a daily basis. So for every day in my calendar I need to know wheter the item was in rent or not.

The calender window is set to the current year plus the three previous years. So, the number of days is always between 1,095 at the least and 1,461 at the most. In my first attempt I created a calculated table which generated a row for every possible calendar[date] - portfolio[item] combination using SUMMARIZECOLUMNS(). In the end - depending on the number of days in the current year - this table contains between 22 and 29 million rows.

Generating this combinations wasn't really a problem for performance. But after that I needed to perform quite a few calculations, like: was the item at the given date in possession? if it was in possession, was it in rent? for how long the product was in possession? etc. And with every calculated column and every measure I am adding, the performance of the pbix file gets weaker and weaker. Right now I am at a point, where adding a new measure takes about 3 minutes to open the formula bar and another 3 minutes to apply the changes when pressing enter.

For a better development experience, I started to temporarily reduce the size of the calendar to only a few days every time I am editing the model. This trick works well, the performance is way better. So my conclusion is, that the main pain point in my model is the vast amount of combinations I have created.

This leads me to my question: Is there any approach, with which I can reduce the number of rows in my model without losing information. Like using aggregated data or something like this? Or would it be possible to calculate the values all in measures and not generating a calculated table at all? Those are all things that came to my mind, but I just cannot imagine how to get it to work.

Maybe one of you has faced a similar problem and is able to help me with this

Thanks in avance

Tim

[foodd]

There will obviously be a number of brainstorming and whiteboarding sessions for this.  Capture everything.    Are the customer requirements fully defined....

A general recommendation would be to look over to data-goblins.com for their Power BI Checklists which are very helpful and follow best practices.

[Idrissshatila]

Hello @timalbers ,

one thing I could tell you is build your data Model as a star schema data Model, it will be a better performance for your report.

learn about star schema data Model https://learn.microsoft.com/en-us/power-bi/guidance/star-schema

Also, only load the columns needed for the analysis and don't just load all the table.

one more thing, if you want to do transformation, if it is applicable do it on the datasource side and not on the power bi side, it will reduce the load on Power BI.

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin