Data model issue

[prathmeshb27]

https://drive.google.com/drive/folders/1wekC76qUasF5u9YofANlMNXYu09-nmuK 

This link contains folder with excel data source and mockup and requirements so you can understand better.

Sales is fact tables and others are dimension tables. I am unable to connect customer table, reseller table and sales table. Which relationship and cardinality to use? There are no related keys in sales fact table for customer table. Please provide solution. If you have any question ask me.

[Irwan · Accepted solution]

hello @prathmeshb27 

i would do something like below

all relationship are one-to-many with single filter

Sales - Customer using CustomerKey

Sales - Reseller using ResellerKey

the relationship between date and sales is quite tricky since you have three datekey in sales table.

i use OrderDateKey in sales table to connect DateKey in date table (however, this depends on your outcome result).

Hope this will help.

Thank you.

[Irwan · Accepted solution]

hello @prathmeshb27 

i might be misunderstood your question but i believe it should be fine.

as far as my knowledge, relationship takes same value to connect two tables.

so -1 will be connected to -1 as well.

Also since your reseller is a dim table, the relationship will be one-to-many then it will connect -1 in reseller to all -1 in sales.

Hope this will help.

Thank you.

[Irwan · Accepted solution]

hello @prathmeshb27 

i would do something like below

all relationship are one-to-many with single filter

Sales - Customer using CustomerKey

Sales - Reseller using ResellerKey

the relationship between date and sales is quite tricky since you have three datekey in sales table.

i use OrderDateKey in sales table to connect DateKey in date table (however, this depends on your outcome result).

Hope this will help.

Thank you.

[prathmeshb27]

Please check this once https://drive.google.com/drive/folders/1wekC76qUasF5u9YofANlMNXYu09-nmuK 

[Irwan · Accepted solution]

hello @prathmeshb27 

i might be misunderstood your question but i believe it should be fine.

as far as my knowledge, relationship takes same value to connect two tables.

so -1 will be connected to -1 as well.

Also since your reseller is a dim table, the relationship will be one-to-many then it will connect -1 in reseller to all -1 in sales.

Hope this will help.

Thank you.

[prathmeshb27]

Is it ok if we delete -1 row from both dimension tables and use? Because -1 row in dimension tables looks bad with no data,but at the same time there are many rows related to -1 in fact table. Which will be big data loss

What should we do at this situation?

1. Delete -1 row in both dimension tables?

2. keep everything as it is?

[Irwan · Accepted solution]

hello @prathmeshb27 

that is depend on your need.

have those -1 data reviewed, if you dont need those data, then you can remove it (either remove it in PQ or visual filter in PBI). Removing in PQ will make the data disappear in PBI while visual filter only filter out those data from visualization.

Hope this will help.

Thank you.

[prathmeshb27]

Reseller

Customer

Sales Table

1. Your logic is correct but what can we do about -1 in all tables? Customerkey & resellerkey