Help with Aggregations and Composite models.

[Anonymous]

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 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!!

[v-piga-msft]

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

[Anonymous · Accepted solution]

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!

[v-piga-msft]

Hi @Anonymous ,

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

Best Regards,

Cherry