cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pmscorca Member
Member

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_dim as a primary key (the business key), MyFact table has B_key_dim as a foreign key for referring to MyDimension table.

For MyDimension table I've created an index column, S_key_dim (the surrogate key). Then, I've merged MyFact with MyDimension to implement an inner join and to get S_key_dim. Now, I'd like to delete B_key_dim colums in both MyFact and MyDimension, but I've an error when I delete B_key_dim column in MyDimension.

 

Any suggests to me, please? Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
pmscorca Member
Member

Re: Managing surrogate keys for dimension tables with Power Query

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

View solution in original post

5 REPLIES 5
Microsoft v-yulgu-msft
Microsoft

Re: Managing surrogate keys for dimension tables with Power Query

Hi @pmscorca,

 

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

1.PNG

 

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

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

Re: Managing surrogate keys for dimension tables with Power Query

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

Thanks

Microsoft v-yulgu-msft
Microsoft

Re: Managing surrogate keys for dimension tables with Power Query

Hi @pmscorca,

 

I'm afraid this is not supported.

 

Regards,

Yuliana Gu

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

Re: Managing surrogate keys for dimension tables with Power Query

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

View solution in original post

Microsoft v-yulgu-msft
Microsoft

Re: Managing surrogate keys for dimension tables with Power Query

Hi @pmscorca,

 

Thanks for your sharing.

 

Regards,

Yuliana Gu

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

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)