cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RonSon2015
Frequent Visitor

Data model design - how to avoid calculated column as composite key

Hello all,

In order to improve the performance of my data model in SSAS I would like to ask for your tips and tricks how solve the following issues.

Light example
I've got a fact table and three dimension tables. Two of them (customer and products) I can connect directly (key customer no and material no). However the third table requires a composite key (customer region & product type) that comes from the dimension tables.

2021-09-29_11h05_52.png

 

I.e. the customer region is originally in Dim_customer and the product type from Dim_Products. In fact table I currently use a calculated column to create the foreign key (= related(Dim_Customer[Customer Region]) & related(Dim_Products[Product type])

The performance is really low esspecially as the real case includes that problem 3 times including some cases (if then).

How can I redesign to improve it?

Thanks for your help in advance!

1 REPLY 1
BA_Pete
Super User
Super User

Hi @RonSon2015 ,

 

Without seeing an example of the actual data in your tables it's difficult to give a definite answer. However, the first thing I would do would be to try and build your third table in Power Query instead.

It appears as though you have common fields that you are using due to being able to utilise RELATED, so you should be able to create this table using merge in PQ.

This may give you the performance gains you want on an overall source -> enduser basis. Even if it doesn't, at least the processing time will be borne by your gateway during data refresh, rather than by the enduser at runtime.

 

Pete

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors