Forum Discussion
How to avoid ambiguity between 2 DIM and 1 FACT table
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
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.
4 Replies
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.
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.
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.
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.
