Forum Discussion
Best Practice for table relationship
Hi there,
I have an EmployeeDim and SalesFact with 3 EmployeeID like CreatedID, ProcessID and ShippedID
What is the best way for table relationship?
1. To create 3 Employees table (derived table like tsql view) or
2. Create USERRELATION function in measure for the other two column
That's because you can have one active relationship and all other are inactive
And the slicer will not perform as expected
What is the best practice for that? Space, Memory ext.. Prons and cons please.
Thanks,
Oded Dror
Hey Anonymous,
basically it's not that easy to define a best practice.
Personally I try to understand the main analytical purpose of the underlying datamodel. If this purpose to explore the interactions between the different roles, than I to tend create separate tables.
Aksing for memory consumption, the more tables (columns) are materialized, the more space is needed.
Asking for speed, nothing beats natural realtionships. From a performance point of view, these natural relationships are followed by "activated" relationships (USERELATIONSHIP), followed by virtual relationships (TREATAS).
Without more details about the purpose, number of rows (employees) and number of columns, and number of rows in your facttabel, it's difficult to come up with a general solution.
Maybe you will find this book
interesting, the title may be a little bit misleading, it's one of the rare books that provides inside into the data modeling of Power BI data models.
Hopefully this gets you started.
Regards
Tom
2 Replies
Hey Anonymous,
basically it's not that easy to define a best practice.
Personally I try to understand the main analytical purpose of the underlying datamodel. If this purpose to explore the interactions between the different roles, than I to tend create separate tables.
Aksing for memory consumption, the more tables (columns) are materialized, the more space is needed.
Asking for speed, nothing beats natural realtionships. From a performance point of view, these natural relationships are followed by "activated" relationships (USERELATIONSHIP), followed by virtual relationships (TREATAS).
Without more details about the purpose, number of rows (employees) and number of columns, and number of rows in your facttabel, it's difficult to come up with a general solution.
Maybe you will find this book
interesting, the title may be a little bit misleading, it's one of the rare books that provides inside into the data modeling of Power BI data models.
Hopefully this gets you started.
Regards
Tom