Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Help with Aggregations and Composite models.

Hello guys,

 

I'm starting to play with aggregations and composite models on PowerBI with Redshift as a datasource.

 

To make things simple I have one import table with aggregated data and another table with detail and DirectQuery.

Then I have a dimension with a lot of cardinality, something like this:


ID, SITE, ID_SITE_GROUP, SITE_GROUP
1, www.1.com, 1, MAIN

2, www.2.com, 1, MAIN

3, www.3.com, 1, MAIN

4, www.4.com, 2, SECONDARY

5, www.5.com, 2, SECONDARY

and so on...


I tryied two different approaches without the expected result.
First, I connected this dimesion to both aggregated and detail table, like this:
Sample ModelSample Model

What I was expectig is that when I used dim_site.site_group in any visual, PBI used imported data and if I used dim_site.site then make a DirectQuery over Redshift. It didn't work because PBI always throws a DirectQuery when using this dimension.

 

Does this make sense? I mean, is this the correct approach for what I'm trying to achieve?

 

The second approach I tryied was to have this site_dimension separated, more like an snowflake model. It didn't work either.

Anything will help, thank you!!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @v-piga-msft 

 

My imported table whas the one with aggregated data, same for all the dimensions.

The detail table was the one I was trying to DirectQuery only when needed.

 

Anyway I finally found the solution. It was a mix of things, first I needed to use the snowflake approach and then use DUAL Storage for the dimensions.


Now it's working fine.

 

Thank you for your response!

View solution in original post

3 REPLIES 3
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @Anonymous ,

I still have a little confused about your requirement.

What I was expectig is that when I used dim_site.site_group in any visual, PBI used imported data and if I used dim_site.site then make a DirectQuery over Redshift. It didn't work because PBI always throws a DirectQuery when using this dimension.


What do you mean "use imported data" and "throws a DirectQuery "? Could you explain your desired in more details?

In addition, which table is your impoted table?

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi @v-piga-msft 

 

My imported table whas the one with aggregated data, same for all the dimensions.

The detail table was the one I was trying to DirectQuery only when needed.

 

Anyway I finally found the solution. It was a mix of things, first I needed to use the snowflake approach and then use DUAL Storage for the dimensions.


Now it's working fine.

 

Thank you for your response!

Hi @Anonymous ,

Glad to hear the issue is solved. I will mark your reply as an answer to close this thread.

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.