Forum Discussion

aashritsingh's avatar
aashritsingh
Icon for Helper I rankHelper I
2 years ago

Calculated Column - Conditional Attribute between unrelated tables

We have a snowflake model where we have to write a case statement based on one dimension table into another without any direct relationship between each. The only link is through fact tables with very high data volume which makes merge query solution very inefficient.

Screenshot below is a similar example where we need a column with a logic - IF(PROMO_EXEMPT ) = Y, THEN PROMOTION_DESC is BLANK ELSE PRMOTION_DESC.

 

 

As you will see in the Screenshot, the only links between SECTOR_DIM and PROMOTION_DIM is through TRANSACTION_FACT and RETURN_FACT.

 

 

We are using DirectQuery in our live scenario as these fact tables have over 300 million rows.

 

Please suggest the best solution to accomplish this.

Need Help
Show and Tell
Tips and Tricks

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi aashritsingh ,

    1. Please try to create a measure, do not use calculated column

    2. Optimize the model, do not passing relationships through the fact table

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • aashritsingh's avatar
      aashritsingh
      Icon for Helper I rankHelper I
      2 years ago

      Hi Anonymous,

       

      Can you please elaborate on Optimizing the model and not have a relationship through fact table? As there is no link between Promotion Dim to Sector Dim without going through Fact table. I wanted to avoid merging in Transform query as it impacts performance significantly due to data volume in Direct Query.

       

      Thanks,

      Aashrit