1. Date dimensions and relationships in Power BI
In this demo, we'll create a year dimension, extend that dimension, and then merge columns together to allow a relationship to be created where one otherwise couldn't be.
To create a year dimension, go to the data view and select New Table. This will allow you to create a new table using DAX expressions. You should be familiar with the basics of DAX from the Power BI intro course, but note that, in the real world, date dimensions are often defined using copied and pasted code snippets. I've copied a code snippet, and will paste it here.
This DAX expression will create a table named Year. It starts with defining all dates between January 1950 and today. From this range, the CALENDAR function creates a Date field under the hood. Next, only the year is retrieved in a column named Year, using the SELECTCOLUMNS function. Finally, to get the distinct years only, all this is wrapped in a DISTINCT function.
There we have it: a table named Year, with one column named Year, with every year from 1950 up to the present, which as of the time of recording is 2021.
To extend this year dimension, for example to include the millennium of the year, right-click the table and select new column. The new column will be called Millennium.
I'm going to take the year column and subtract out the modulo division of year over 1000. This will leave 1000 for all years before 2000, and will be 2000 from there on out.
Let's go back to the data model. We have our fact table, business establishment by age, and three dimensions: establishment age code, geography and NAICS code.
The Year table is also visible, but is not yet linked to our fact table. To do that, drag the Year field of the Year dimension table to the Year field of the fact table. By default, a one-to-many relationship is defined from the dimension to the fact table, indicated by this arrow which goes from one to the asterisk. Since each row of Year is unique in the Year dimension table, Year can act as a natural key, and a one-to-many relationship is the correct choice here.
When you don't have a unique key present in a table, you can create a composite key by merging multiple columns together. Suppose we didn't have this fact table with a unique key for NAICS Code, but this fact table instead, where the NAICS Code has been split up into four columns. As a result, there is no single field in this table that could have a relationship with the NAICS Code dimension table.
Let's fix that by merging together the four columns. Right click the table to edit the query. Select Economic Sector, Subsector, Industry Group and Code by control clicking each one, and then right click to choose Merge Columns.
In this case, I don't choose a separator, and I'll call the new column NAICS Code. With the NAICS Code created, close and apply the query. Power Query will automatically create a relationship between this fact table and the NAICS Code dimension, since there is now a unique identifier column in each of these tables.
You can edit this relationship by double clicking it. Power BI automatically recognized that NAICS Code from the fact table relates with the 2017 NAICS code column, even when these columns don't have the same name. You can see here that Power BI assumed the cardinality as many-to-one. This seems correct, as there are many records in the fact table per row in NAICS Code. Perfect!
Let's do some exercises.
2. Let's practice!