A huge dimension table as direct query while fact is implemented as aggregation

[Anonymous]

Hi all,

I have a few small size dimensions that serve as dual connected to direct query on fact and has its own aggregated fact table. KPI can be retrieve pretty fast since aggregation is used. 

However, now i have a huge dimension table and only 2 to 3 category fields i would need it as a filter. This slows down a lot. Any way that i can integrate it as part of existing aggregated table (which has no dimension id of the huge dimension table). I cant put this as part of aggregated table as it will defeat the purpose of having aggregate. Any way that i can retrieve the 2 to 3 fields into aggregated table without bringing in the dimension id? 

Also i wont be able to change the dimension design already. I tried merge but it will import the table and loading is very slow and hits memory error too. Any idea/trick to go about this? By the way, database is redshift. I cant do advance sql query too. Need to schedule refresh so ODBC method dont work as well.

Regards

CS

[Anonymous]

Was wondering if anyone faces same problem? Any idea how to go about it?

[amitchandak]

@Anonymous , can these two to three category can connect to the fact/s directly.

Then you can create a table in Power BI (This will become mixed/composite mode)  and join with Facts and use it.

I created a date table in Power BI in direct query mode and used it.

[Anonymous]

Hi @amitchandak , unfortunately no( two to three category fields cant be join to the fact, only thru the unique dimension id), else it will be easy for me to put in aggregated table.

[amitchandak]

@Anonymous , Can you share the data model, the column should be visible and highlight the column we want to have some action

[Anonymous]

thanks @amitchandak for reverting.

I quickily mock something up, but same concept.

Assume dim_marketing is the huge dimension here and i need marketing name and marketing category to be part of fact_sales_agg so that filtering on those fields would be fast. Any idea without add the market segment and market category into the fact table in database solution? i am not sure if power bi can do such thing. I would avoid merging a its pretty slow.