As I continue to get better at building out my data models, I sometimes I think I have a good grasp and then I run into something that I need a better understanding of. I have attached below a screenshot of my data model, and two screenshots of a table.
You will see that I can do a lookup from the Customers lookup table to the Service Ledger entries fact table and it works fine. I can also do a lookup from the Customer Equipment table to the Service Ledger Entries table. Where I sometimes get confused is in building out the visual table with the correct fields.
Scenario:
from doing a lookup of the customer number table to the service ledger entries fact table, I populate the table visual with fields from the Service Ledger Entries table and it works fine. The issue is that sometimes the Service Ledger Entries fields could have missing Equipment information which is a data problem. The customer Equipment tables have all the correct information and nothing is missing. If I chose to use the fields from the Customer Equipment lookup table, I will sometimes get erroneous results on what customer numbers show up in the visual. My thought is, that I am using the wrong approach because the Customer Equipment Table is a lookup table and not a fact table. The Customers lookup table and the Customer Equipment table are lookup tables and don’t have an relationship and therefore if I pick fields from the Customer Equipment table to populate the visual table I will get bad results.
Am I on the right track?
Hi @lomorris, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.
- Use the forum search to discover if your query has been asked before by another member.
- When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.
- Use the proper category that best describes your topic
- Provide as much context to a question as possible.
- Include the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.
I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.
Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.
Hi @lomorris,
Table design and model requirements are for a large part determined by the analysis that needs to be done. There are two obvious ways you can go from here, each can have pro’s and con’s.
First only keep the Customer Equipment Key in your fact table and remove all other Customer Equipment attributes from it because you are using a Dim table. Just like you would with a Date Key or Customer Key where all attributes reside in the Dim table except for the Key that is present in both Dim and Fact table.
Second sacrifice the Customer Equipment table entirely, use it as a staging query to update any missing data in your Fact table.
Depending on the volume, number-- and type of fields, the first would be considered more optimal from a model size perspective but wether that’s the best model for your analysis - is up to you.
Another observation.
You have a large number of fields in both Service Ledger Entries and Customer Equipment table by what I could see in the screen shots. My guess is you won’t need all of them for your analysis, so remove everything that is redundend by that I mean, if you are not going to need it for a calculation or to slice and dice by, strip it from the model. Design with purpose.
I hope this is helpful.
2 Likes
Thanks Melissa,
Totally makes sense to me. I am going to go with the first approach as you suggested and dump the equipment attributes from the fact table except the Key. I will also as you suggested clean up redundant data that is not needed. Thanks again for your support on this.
1 Like
