How to avoid ambiguity between 2 DIM and 1 FACT table

[IvanS]

Hi guys,

I have created simplified schema of my dataset below. I would like to visualize:

1. Transactions per date

2. Customers per registration date

With current set up, I am unable to create relationships due to ambiguity. This model is very simple, in reality I have dozen of DIM tables (such as Vendors, Employees etc.) with other colums. Currently I have created copy of DIM Customer table however this is significantly increasing dataset size when doing the same for each DIM table. For some calculations, I used USERELATIONSHIP function but I would need to create dozen of measures which is definately not user friendly.

Is there any other way how to handle such relationships in dataset as described?

Thank you for any help
IvanS

[CoreyP]

As you currently have dates in your DIM Customer table, I believe it technically makes it a FACT table. I think the proper star schema would be to have a separate DIM Customer table with only a distinct list of customer id's. Then you'll have a relationship from Date to FACT Transactions and to FACT Customer Dates.

[IvanS]

Hi @CoreyP ,

the Customer table is dimensional as Customer ID is unique there and there is only one registered date assigned to unique Customer ID - example:

Customer ID	Name	Language	Registration date	City	Country
1	Johnny	en	1.1.2024	New York	US
2	Simon	fr	20.12.2023	Paris	France

So creating distinct list of customer ID's will lead into having duplicate table of the same size (which is actually that I am doing right now) but I am looking for ways to reduce size of dataset.

[CoreyP · Accepted solution]

Yeah, for sure. However, as you're wanting to aggregate customers by registration date.. that technically means it should be considered a fact table in a star schema. 

So, you are correct in your OP. Your two options are to restructure your model, or add USERELATIONSHIP to your measures.

[amustafa]

In your Customers dataset, make sure to have a unique row for each customer by let's say a Customer_ID and then you can have all other demograhic infomrmation about a customer in this table.

For Transactions table you can have a Customer_ID colun with all transaction details.

Join the two table on Customer_ID with 1 to many relationship.

Hope this helps.