Data Model Feedback

[CB13]

Hi,

I am sketching out my pass at a data model and have a question for the hive as I am relatively new to trying to figure out schemas, and I think my data is a bit of mix with transactions that have elements I need to filter by.

So far I have 3 very large (row-wise) tables + 1 4th dimension table (employee) - I'm trying to generalize terms with a crude example so it makes sense:

1. Visit table: 1 row per visit. 

2. Test table: 1 row per test; many-to-one relationship to Visit table. 

3. Item table: 1 row per item; many-to-one relationship to Test table. 

4. Employee: 1 row per employee; 1 to many relationship possible with Visit, Test, and Item tables.

Rough sketch

I'm getting stuck with how to link the employee data. If I connect to each of the 3 tables (Visit, Test, Item) on the respective owner ID I get an ambiguous reference - but I also feel I need to maintain the links from Visit to Test to Item. 

Do I need 3 copies of the Owner table - 1 to link to each table? I believe this would mean if I used Office as a filter though, it would have to pick "which" employee table to pull it from (ie: the one linked to Item) - but then it would only show the values of offices in assocated with Item records, which is not a guaruntee (even though my poor example it is the case).

Thanks in advance for any suggestions / help - I am worried I'm heading down the wrong path from the start and would rather correct early!

[v-yuta-msft]

@CB13 ,

Power bi data model doesn't allow ambiguous reference or circular reference. Could you please share the table structure and relatioship for further analysis?

Regards,

Jimmy Tao

[CB13]

@v-yuta-msft Hi Jimmy,

The picture in my original post has a fake sample of the data. Here is a little more information about my current data model and the ambiguous reference in the picture below:

I unfortunately can't post a picture of the real example (this is a far simplified example).

[tex628]

If you're using the final example it should work well if you're using a double crossfilter direction in your relationships between the facts tables. 

The other option is the merge all three tables together in the query editor. 

/ J

[CB13]

Hi,

The final example is what I am currently using - but it doesn't allow me to link on Owner ID directly to the Visit and Test tables. Since the owner's vary this is important:

It feels like I may have to create 3 "copies" of the owner table, 1 to join to each, but this feels wasteful, especially since this is a simplified example and in reality I will likely have several more tables that need this same information.

I'm not sure what you mean by join the tables together in the data model. Wouldn't this then create a lot of additional data fields with repeated data - meaning that all of the Visit data would be repeated at the Item level, over and over again? This also feels ineffecient?

[v-yuta-msft · Accepted solution]

@CB13 ,

I'm afraid in the current data model, you can't achieve this goal. The only way is as you said, to create 3 copies of owner table and build relationship between those copy table and Visit, Test and Item table.

Regards,

Jimmy Tao

[jdbuchanan71]

Hello @CB13 

Assuming that the owner of the test is the same as the owner of the visit that the test was performed on then you only need to go from Owner > Visit, Visit > Test and Test > Item.  Filtering an owner will flow down the relationship through visit, test and item so you don't need to link the owners to all three tables, just the hightest one in the chain.