Best practice for where to put calculated columns?

[richard-powerbi]

I struggle with deciding where to put calculated columns in a data model. Is it better to put them in fact tables, or in dimension tables? And what if I don't feel like having a table where it belongs to? Make a new blank query with just 1 id column and build the calculated columns in there? I fear your answer will be, "put them in the table where they belong", but sometimes they can 'logically' belong in several tables, or in none. Does anyone know what's best practice?

[JosefPrakljacic]

Hey @richard-powerbi,

as always it depends. But I would place the calculated column in a small dimension rather than a fact table.

Since you are not providing us any examples its hard to give you a recommondation.

According to Kimball your column could form a junk dimension....

If we don't get an adequate description from you, this will just be a big guessing game.

BR,

Josef

[richard-powerbi]

Let's say I have a table1 with product ID's, product name etc.
I have another table2 with dates that describe when this product is made.
Then I have another table3 with dates that describe when the product should be made.
Now I want to make a conclusion column that tells me how many days too late or too early the product is made.
It's tempting to just add this column to table1, but there are more calculated columns and I feel the table is getting too wide.
Is it better to make a 'new' blank query with one referenced id column and then build calculated columns from there? For example a conclusion table? And do I need to make a blank query first or can I create a new table with only dax columns?

[JosefPrakljacic]

Hey @richard-powerbi ,

are table2 and table3 transaction tables? For me it seems right at the moment that you should combine table 1,2,3 because they are forming a product dimension.

If table2 and 3 are transaction tables I would create a dax "fact table" with the product id and all the other measures/calculated columns that are referecing this product dimension.

BR,

Josef

[richard-powerbi]

---

@JosefPrakljacic wrote:

are table2 and table3 transaction tables? For me it seems right at the moment that you should combine table 1,2,3 because they are forming a product dimension.

---

Yes I would call table2 and table3 transaction tables.

So you're basically saying combine as much transaction tables as possible into 1 transaction table?

I always try to avoid wide tables, I prefer to categorize things in seperate tables to keep a better overview of what's happening. Is this bad practice?

Is it better for performance to have several transaction tables which are note very wide or is it better to have 1 wide transaction table like you're saying? 

---

@JosefPrakljacic wrote:

If table2 and 3 are transaction tables I would create a dax "fact table" with the product id and all the other measures/calculated columns that are referecing this product dimension.

---

I've always had the 'feeling' that a fact table needs to 'exist' (I mean in chronological order of creation) before a dimension table. Is this not true? Can I make a fact table from a dimension table?

Not sure if I understand you correctly, but I do not understand why I should put all the measures/calculated columns in the fact table. Or do you mean this: have 1 fact table with just the ID, another combined dimenstion table with all the dates, then another dimension dax table with the measures/calculated columns?

[JosefPrakljacic]

Hey @richard-powerbi,

I've expressed myself misleadingly. I hoped that table2 and table3 were dimension tables.

Now that all three are fact tables, it makes the whole thing a little more difficult.

There is a rule of thumb that says, each process should have its own fact table.

Are these three different processes that fill these transactional tables 1,2,3?

If so, then you have three fact tables and you put your calculated column into the dimension table needed by all three.

If all three transaction tables serve one overall process, then I would have started to slim down the transaction tables by putting their attributes into dimensions and having only IDs and aggregates.

@Richard you have to understand that this is all a big guessing game, because without a data model I can't make any qualified statement that would help you.

I just feel like I'm looking for the needle in the hay stack.

BR,

Josef

[richard-powerbi]

I thought that there would be best practices for this, so I didn't put an example. But I'll try to come up with an example soon.