Forum Discussion
Building relationship between several FACT tables and date table
I have 4 fact tables.
1. Accounting actuals
2. Budget
3. Outlook
4. Previous Outlook
The only fact table that has a defined date is accounting actuals, the rest only show data by month (name and #), there is no year, day or date. I am having trouble building a relationship as I want to compare tables (1vs2, 1vs3 and 3vs4). I want to add a slicer by month, but it only slices the accounting actuals data. I created a date table that has Date, days in month, start of month, end of month, month name, month #, Year and YYYYMM. How can I use the date table to slice fact tables 2-4 when Period name or number is not unique as it wont let me create a many to many relationship as it will create ambiguity between the other relationships I have.
Hi,
In the Accounting Actuals table, create a calculated column to extract Month name. Do not create any relationships between the 4 tables. Create a Month table with 12 rows. Create a relationship (Many to One and Single) from the Month name column of each of the 4 fact tables to the Month table.
Hope this helps.
1 Reply
- Ashish_Mathur
Super User
Hi,
In the Accounting Actuals table, create a calculated column to extract Month name. Do not create any relationships between the 4 tables. Create a Month table with 12 rows. Create a relationship (Many to One and Single) from the Month name column of each of the 4 fact tables to the Month table.
Hope this helps.