Forum Discussion
When Trying to Use Nested Composite Model Facing Error or Performance Issues
Problem Statement:
We have different subject areas and datasets are published regarding 3 subject areas . These individual models are built in Composite Model ( Using Pbi aggregations we have a summary table and a detail table corresponding to each fact and every dimension is connected to both summary(Import) and detail table (direct query)) and the dimension is set to dual mode.
Now as per the latest feature released by Microsoft we are trying to combine these three models together in to one model. And we have used the below two methods. One gives error and other gives Performance Issue. Kindly let me know if there is any perfomant way to achieve this also if nesting of the composite model can be achieved using the latest feature release ( as the individual datasets are already in composite model as explained above).
Method 1:
Tried to create new joins with the existing model but getting the below error when tried to connect the same dimension to the aggregate table and detail table with in the composite model.
Method2
Tried to join the conformed dimension and drive using a single dimension to control all the facts in all subject areas but this gives performance issues.
Hi Anonymous ,
To use composite model, you should consider the data source and connection mode. For example, import mode composite, direct query composite, import and direct query composite.
Refer: Use composite models
Using composite models adds additional performance considerations. A single visual can result in sending queries to multiple sources, which often pass the results from one query across to a second source. This situation can result in the following forms of execution:
-
An SQL query that includes a large number of literal values
-
An SQL query that queries at a lower level of granularity, with the data later being aggregated locally
-
Multiple SQL queries, one per group by value
Additionally, the use of many-to-many relationships means that separate queries must be sent to the underlying source for each total or subtotal level, rather than aggregating the detailed values locally. A simple table visual with totals would send two SQL queries, rather than one.
For more about composite model performance, you can refer this ariticle: High performing Power BI using Composite models with Aggregate feature
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.-
1 Reply
Hi Anonymous ,
To use composite model, you should consider the data source and connection mode. For example, import mode composite, direct query composite, import and direct query composite.
Refer: Use composite models
Using composite models adds additional performance considerations. A single visual can result in sending queries to multiple sources, which often pass the results from one query across to a second source. This situation can result in the following forms of execution:
-
An SQL query that includes a large number of literal values
-
An SQL query that queries at a lower level of granularity, with the data later being aggregated locally
-
Multiple SQL queries, one per group by value
Additionally, the use of many-to-many relationships means that separate queries must be sent to the underlying source for each total or subtotal level, rather than aggregating the detailed values locally. A simple table visual with totals would send two SQL queries, rather than one.
For more about composite model performance, you can refer this ariticle: High performing Power BI using Composite models with Aggregate feature
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.-