Forum Discussion
How to create a dimension table from a fact table using DAX?
Hi guys,
I'm working on my data model, so I'm trying to create a dim table from my fact table. I don't want to do it in Power Query because I only got one source of data, so I don't want to keep duplicating my source whenever I need a dim table, I already tried that and it triplicates the size of my file, slowing down the performance.
In my fact table I've got an "Assignment Group" column, which tell us the group that is assigned to the record. This column has repeated values, as several records can belong to a same group.
My goal is to create an ID for the "Assignment Group" column so it can behave as a Foreign Key in the fact table. Then, create a dim table that has the ID as the Primary Key and a second column with the Assignment Group, removing duplicates (Create a catalog). Remove the Assignment Group column from the fact table and create a relationship between the fact and dim tables through the IDs.
Could someone advise in how to do this in DAX please?
Thanks!!
OV
6 Replies
Anonymous
You can create a dim table for the "Assignment Group" using:
Dim Assignment group = DISTINCT(Fact_Table [Assignment group])You can then join the common fields in a one-to-many relationship (you don't need the numerical key for this). If you need a column to establish a sorting order, add a calculated column to the Dim table using RANKX or a SWITCH function.
PaulDBrown thanks for the input!
I read the storage is lower if you have a numeric ID in your fact table and your actual data in the dim table. Actually the reason I'm doing this is to optimize the storage and have a better performance while slicing by assignment group.
If I just create a relationship between my fact and dim table with the actual assignment group, am I still getting any enhancement here?
OV
Anonymous
The thing is in order to have a numeric key in your fact table you are going to have to create a calculated column to assign the number for each value anyway (adding columns should be avoided since they hinder performance).
Alternatively you could do it in Power Query (best practices actually generally recommend doing table transformations/creations as early as you can in the process (so at the source -> if not PQ -> if not Dax).
I don't think you will notice any significant delay between using a numeric key and a text key anyway, but try it out.
PS. BTW, did you try "referencing" your fact table to create the DIM table (instead of duplicating it)?
