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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors