Composite Model - Is this a bug or I'm missing something?

[Anonymous]

Edit: I just made again the model starting from our monthly version and it worked again. Do you guys know if any changes related to composite models were made in the latests versions of PBI? Thanks!

Hello guys, I need some help!

I have some problems dealing with Composite Models. I don't know if i am missing something or this is some kind of bug.

This model it's been working for a couple of months but it isn't now. It has daily data, we have another one with monthly data which is pretty much the same like this one but with some tables added (nothing less, just more tables, dimensions and measures).
Happened the same than this one, it was working for a couple months and then it went terribly slow. I managed to make it work just going back to our firs version which is exactly the same than the daily version (but different source table, groupping data monthly) and then added the changes again in certain order and avoiding the creation of a measure table which caused every query to go Direct Query. Really weird.

Screenshot of our model:

First let me explain the model and then what it's failing.
Light Blue: dimensions. Each joins with Detail and Aggregated fact table except for two, I explain that later. DUAL
Dark blue: grouped dimensions. DUAL
Fact table with aggregated data: IMPORT
Fact table with detail data: DIRECTQUERY

Extra (Don't think they affect this example)
Yellow: Salesforce tables. IMPORT (only used with the detail table)
Purple: Calendars. -
Orange: Extra tables to store measures and others, just not connected to the model. -

Ok, so we had a lot of data and realized that pretty much of the rows came because of the granularity caused from two dimensions (light blue inside red circle). Aside from that, we where pretty much using groups of data from this dimension and the lower level just for details.
Example:
adunit (space where ads are added in a website) DIM_ADUNIT
section (group of adunits) DIM_ADUNIT_GROUP
site (website, group of sectios) DIM_ADUNIT_GROUP

So we had an adunit dimension with all the adunits linked to the detail table and then another dimension with adunit groups linked to both adunit dimension and aggregated facts table. Everything was working like a charm, when using any dimension except for the two linked only to the detail table it used imported data, even the two grouped dimensions (dark blue), when using those two only linked to the detail table then PowerBi threw direct queries.
Somehow this isn't working anymore, it's really weird because like I said, the same model with monthly data it's working (the old version is just like this one, only data changes, the previous version which was working it's just like that one, just different table sources).

The problem is that when we use any of our two dimension group tables (dark blue), every single query goes DirectQuery, even the slicers that only have data from every other dimension so it's making it really slow.

Like I said, what it's even weirder is that in our Monthly model that it's working, if I create a new table to store measures (just to have them organized), once I create the blank table without any links to the rest of the tables or any measure it makes all the queries go DirectQuery again.
For me this doesn't makes any sense and sound like a bug to me, but maybe I'm missing something.

If I didn't explain myself just tell me.

Thank you!!

[v-xicai]

Hi @Anonymous  ,

Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[Anonymous]

Hello @v-xicai 

I ended up redoing the model from the previous version that was working. The model ended up being the same that the one that wasn't working properly except for a measure table wich I said it was causing problems and worked fine.

After that I made a new version with more tables and dimensions and worked fine until I made another measure table, again the same problem.

To me it doesn't make any sense but without making measure tables everything works so...

Thanks.

[v-xicai]

Hi @Anonymous ,

You can find the changes of Power BI Desktop latest version from the document What's new in the latest Power BI Desktop update?.

When the refresh is in fact slow or crash, it can be due to several reasons:

• Insufficient CPU (refresh can be very CPU-intensive).
• Insufficient memory, resulting in refresh pausing (which requires the refresh to start over when conditions are favorable to recommence).
• Non-capacity reasons, including data source system responsiveness, network latency, invalid permissions or gateway throughput. You may increase the capacity for workspaces to increase the model refresh parallelism frequency.
• Data volume - a good reason to configure incremental refresh, Incremental refresh can significantly reduce data refresh duration, especially for large model tables. See more: Incremental refresh in Power BI Premium .

See more details: https://docs.microsoft.com/en-us/power-bi/whitepaper-powerbi-premium-deployment#why-are-refreshes-slow.

You can also use some tips to improve the performance:

   Enable Row-Level Security (RLS) where applicable.

   Use Microsoft AppSource certified custom visuals where applicable.

   Do not use hierarchical filters.

   Provide data categorization for Power BI reports (HBI, MBI, LBI).

   Use the On-premises data gateway instead of Personal Gateway.

   Use slicers sparingly.

   Remove unused tables or columns, where possible.

   For more information on optimizing data sources for DirectQuery, see DirectQuery in SQL Server 2016 Analysis Services.

You can refer to the links to see more performance tips:

https://docs.microsoft.com/en-us/power-bi/power-bi-reports-performance,

https://blog.pragmaticworks.com/power-bi-performance-tips-and-techniques,

https://maqsoftware.com/expertise/powerbi/power-bi-best-practices.

(Please understand that this link is provided “AS IS” with no warranties or guarantees of content changes, and confers no rights.

If you still have this issue for Power BI, you'd better create a support ticket in Power BI Support , Scroll down and click "CREATE SUPPORT TICKET", to get further help.

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.