Forum Discussion
Best practice for Data Model
Hello,
I am a little stuck with deciding on the data model.
Till now what I have done is created a master table, say MTable containg values as below:
| Measure | Category |
| M1 | C01 |
| M2 | C01 |
| M3 | C01 |
| M4 | C01 |
| M5 | C01 |
| M6 | C01 |
| M1 | C02 |
| M2 | C02 |
| M3 | C02 |
| M4 | C02 |
| M5 | C02 |
| M6 | C02 |
| M1 | C03 |
| M2 | C03 |
| M3 | C03 |
| M4 | C03 |
| M5 | C03 |
| M6 | C03 |
This is to address a purpose for creating a matrix like:
| C01 | C02 | C03 | |
| M1 | Values | ||
| M2 | Values | ||
| M3 | Values | ||
| M4 | Values | ||
| M5 | Values | ||
| M6 | Values |
I have connected the MTable with other 6 Tables from which these Measures(M1-M6) are coming.
Now I want this matrix to get filtered with another column say Sub Category:
| Sub Category |
| Tableau |
| Power BI |
| Python |
| AWS |
| Qlik |
The sub-category is present in all the 6 tables and so whenever I choose filter from table1, the other Measures( M2-M6), stop updating.
Please let me knwo what will be the best way to get it fixed?
As there is one to many relationship between the tables , follow the below steps and try and update the model:
1 Merge all the Tables 1:6 in Mtable (merge using category)and only bring the associated sub category (only consider sub category column) while merging.
2. the above step will help you bring Category and Sub Category in Mtable
3. create a key for Category and sub category
4. As we have merged all the six tables there might be a many to many relation with those tables.
5. To overcome the many to many relationship create a bridge table with distinct values of Key (Category and Sub Category)Please update the ticket if you need any further clarification.
4 Replies
PC2790 , you need to share the full data model.
Becasue of the criticality of the data, I am not able to share the exact model.
Though I have tried to replicate the same as below:
Now, I want to filter the matrix using the Sub Category
This is where I am not getting the desired result.
As there is one to many relationship between the tables , follow the below steps and try and update the model:
1 Merge all the Tables 1:6 in Mtable (merge using category)and only bring the associated sub category (only consider sub category column) while merging.
2. the above step will help you bring Category and Sub Category in Mtable
3. create a key for Category and sub category
4. As we have merged all the six tables there might be a many to many relation with those tables.
5. To overcome the many to many relationship create a bridge table with distinct values of Key (Category and Sub Category)Please update the ticket if you need any further clarification.
