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

[RonSon2015]

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.

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!

[BA_Pete]

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