Single-column dimension table

[hm13]

I just started PowerBI a couple weeks ago. I've been looking at explanations and videos for star schema, but I haven't seen any example where they use single-column dimension tables. It is usually multiple attributes in one dimension table instead of just one.

To simply explain my situation, I have a fact table with the following columns: Date, Time, Name, Process, Shift, Location, and Description. I would like my model to have slicers that can filter the fact table by the date, name, process, shift and location. I've heard star schema is the most optimal way to model the report, so I've been trying to resemble this in my report. What I've tried to do is creating a dimension table for each of the date, name, process, shift and location slicers. To make the dimension tables, I would reference the fact table, and then remove all the other columns and finally remove duplicates and any null values. Then the slicers would be hooked up to these dimension tables, and then relationships would be added between the dimension tables and the fact table.

However, my question is how this would be helping efficiency at all. To me it seems that it is just extra, unneeded data/dimension tables. Why couldn't I just hook up the slicers to the date, name, process, shift and location columns in the fact table? I feel like this would get the same thing done, but just without the extra dimension tables.

Also could you explain when to add your own index keys in dimension tables vs. just having the value itself to filter the fact table?
 

[mlsx4 · Accepted solution]

Hi @hm13 

You just need only two tables: one calendar table (to manage correctly dates) and your fact table will be this one ( Date, Time, Name, Process, Shift, Location, and Description).

Now you relate Date column with Calendar table (date). The relationship should be 1 to * (* this in the part of the Table with your data). Your model should be something close to this:

And now, in the slicers you just need to put the column needed. 

[hm13]

What would be the difference if the calendar table is created separately like you mentioned vs. just using the date column and making a date hierarchy for this column?

[mlsx4]

When you only have one table, it won't make any difficulty. However, if you add more tables (which probably includes another column of type date), you will face problem to link every database. Therefore, a Calendar Table will overcome this problem

[ray_aramburo]

If your data model will only consist of one single table, then there is no point in creating a star schema. If you'll have multiple tables, then yes, star schema is a good idea.