Forum Discussion
Helper VHow to avoid ambiguity between 2 DIM and 1 FACT table
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.
Solution SageAs 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.
Helper VHi 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
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.