Using Composite Key in Power BI
Have you used a Composite key in SQL/Power BI?
Most of you would be familiar with the concepts of Primary key and Foreign key.
Then, what is a Composite key?
A composite key is a combination of two or more columns used to identify the unique rows of a table.
Let me explain this with a simple use case.
Imagine a company has multiple units and each unit has multiple departments.
A lookup/dimension table containing all the information of each unit and department is available. Let’s say this table contains unit_id and dept_id columns, along with all the other information pertaining to each line item.
The dept_id column contains duplicates since the same values are assigned for different units. Also, the unit_id column will contain duplicates since there are different departments in it causing the repetition.
You have a sales table (fact table) containing the records of all the sales happening within the company by unit and department. Each record will have its own unit_id and dept_id values, based on the corresponding unit and department in which the sale is happening.
Now, you have a requirement in Power BI where you need to filter the sales table by both unit name and department name. How will you establish a relationship between these two tables to achieve this requirement?
If you try to link unit_id columns, you will get a many-many relationship and the same thing will happen if you link dept_id columns using a passive relationship.
Though you can merge these tables in Power Query, the workaround is to use the Composite key and solve this problem in the data model itself.
How can it be done?
1) Concatenate unit_id and dept_id columns to obtain a unique primary key column (Composite key) in the lookup/dimension table called unit_dept_id.
2) Repeat this step to obtain a foreign key column in the sales (fact) table.
3) Now you can establish a many-one relationship between these tables which allows you to filter the sales table by unit and department names.
Hope this quick tip helps you.
Follow Raghavan P for more such content.
