Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Managing surrogate keys for dimension tables with Power Query

Hi, I'd like to know how managing better the surrogate keys for dimension tables with Power Query. I've accomplish a proof. I've imported MyFact and MyDimension tables. MyDimension table has B_key...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi Yuliana,

     

    by creating a copy of MyDimension table, having the index from the original table, to merge with MyFact table is possible to remove the business keys in both MyFact and MyDimension.

    It should be useful to know if choosing an index as a surrogate key save memory respect to a business key like a GUID.

     

    Thanks

v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi Anonymous,

 

Did you get below error when you attempted to delete B_key_dim column?

 

As mentioned in error message, "The column 'B_Key_dim' wasn't found", that case, the new merged table is failed to be generated. Because the mapping key did not exist.

 

Based on my test, if you choose "Merge queries as new", you cannot delete the B_key_dim column in source two tables. But you can delete the repeated B_key_dim column in new merged table.

 

If you choose "Merge Queries" option, for example, you merge MyFact table to MyDimension table, at the last step, you could delete B_key_dim column in MyDimension.

 

Regards,

Yuliana Gu

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Ok, but I'd like to delete B_key_dim colums in both MyFact and MyDimension, not only in one table.

Thanks

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi Anonymous,

     

    I'm afraid this is not supported.

     

    Regards,

    Yuliana Gu

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi Yuliana,

       

      by creating a copy of MyDimension table, having the index from the original table, to merge with MyFact table is possible to remove the business keys in both MyFact and MyDimension.

      It should be useful to know if choosing an index as a surrogate key save memory respect to a business key like a GUID.

       

      Thanks

      • v-yulgu-msft's avatar
        v-yulgu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        Hi Anonymous,

         

        Thanks for your sharing.

         

        Regards,

        Yuliana Gu