Dual storage table in aggregation model

[Anonymous]

Hi all,

I've been trying to get my head around this problem for ages.

I have a simple model set up where I am testing composite model and aggregations features. I have a sales fact table in direct query mode which is about 300m records. I have an aggregated sales fact table of about 30m records which is in import mode, and I have a product dimension table which has about 500k records which is in dual mode.

I have set up the aggregations and everything works fine in desktop.

As soon as I refresh the model in the service I have issues where not all of the records are being matched against the dimensions from the product dimension  table.

I am wondering whether it is due to the volume of data, but I thought that was the point about being able to use aggregations with large volumes of data.

Hope someone can help or shed some light. I've attached some screenshots for reference.

Thanks

Mike

Model in desktop - most of the net value assigned to a departmentModel in service - most of the net value IS NOT assigned to a department

[venal]

@Anonymous 

Please find the below links.

Direct Query Limitations - https://docs.microsoft.com/en-us/power-bi/desktop-use-directquery#limitations-of-directquery

Import Query Limitations - https://docs.microsoft.com/en-us/power-bi/service-admin-manage-your-data-storage-in-power-bi#dataset-limits

I would like to suggest you to reduce the data and try to re-pro the issue.

If you have any issues, please let us know.

If this post helps, then please consider Accept it as the solution to help the other members find it more
If this post was helpful may I ask you to mark it as solution and click on thumb symbol?

[Anonymous]

Thanks @venal 

I am still having the same problem when the dataset is much smaller.

The direct query fact table is now 8m rows and the imported aggregate fact table is now 50,000 rows. The product dimension table is still 500k rows. My simple model is shown below. Both fact tables join to the product table using the article_no field.

Regards

Mike

[venal]

@Anonymous 

If you have any error message, please share it with us.

If no error message and thinking that there was a data discrepency, please submit a support ticket to investigate.

Please share the support ticket here for reference.

If you have any other queries, please let us know.

If this post helps, then please consider Accept it as the solution to help the other members find it more
If this post was helpful may I ask you to mark it as solution and click on thumb symbol?