Forum Discussion
data warehouse and Dimensions
It is somewhat difficult to create reports in Power BI using normalised tables from OLTP databases. Therefore I am building a data warehouse now. When creating dimensions, I am not clear how to implement multiple roles for a single person. One person is a retailer, employee and customer. In this case, which will be best for dimension structure?
Option 1. create a dimension dimension.Person.
2. create several fact tables
fact.sales - contain EmployeeKey from person dimension, CustomerKey from person dimension.
fact.Payroll - contain EmployeeKey from person dimension.
Option 2. create two dimensions dimension.Employee and dimension.Customer.
2. create several fact tables
fact.sales - contain EmployeeKey from employee dimension and customerKey from customer dimension.
fact.Payroll - contain EmployeeKey from employee dimension.
another example, if DW is for school, one person can be a parent, past parents, donors, debtors as well as staff. In this case, each role should have own dimention, eg dimension.donors and dimension.parents, etc. or just one dimension.Persons?
If i was designing the database I would write out all of the fields you need for each different role: Parent, Student, Customer, Teacher, etc.
What you will notice is there are a group of common fields shared by all, such as address, phone number etc. These common fields should be your base table, which would be Person.
From here you want to create tables that act as an extension of this base table. That being that they have a Primary key link to the person table and include only the fields relevant to this new level. All common fields can be brought down by the querying app via a join. On this extended level, you might also find you still have multiple roles that can be fulfilled by commonality and that you can extend again onto another level. You just continue to repeat the process until you have a hierachy of extended tables. This format will allow you to store the smallest amount of data possible, and allow you to make updates in the least number of places.
When it comes to Power BI, you can import all of these tables and assign the appropriate relationships.
Of course there are reasons not to do this also, but the reasons are entirely on the needs of the database.
1 Reply
If i was designing the database I would write out all of the fields you need for each different role: Parent, Student, Customer, Teacher, etc.
What you will notice is there are a group of common fields shared by all, such as address, phone number etc. These common fields should be your base table, which would be Person.
From here you want to create tables that act as an extension of this base table. That being that they have a Primary key link to the person table and include only the fields relevant to this new level. All common fields can be brought down by the querying app via a join. On this extended level, you might also find you still have multiple roles that can be fulfilled by commonality and that you can extend again onto another level. You just continue to repeat the process until you have a hierachy of extended tables. This format will allow you to store the smallest amount of data possible, and allow you to make updates in the least number of places.
When it comes to Power BI, you can import all of these tables and assign the appropriate relationships.
Of course there are reasons not to do this also, but the reasons are entirely on the needs of the database.