Forum Discussion
Problem using DateTable columns to create Matrix row hierarchy
We are looking at a modeling issue here; but without the bigger picture it is hard to come up with solutions. Is this really all you want to be showing in the Matrix? It is highly unusual to have a matrix without measures. Also, you might want to create a dimension with those IDs in the fact table.
There are other measures that would appear in the matrix but my issue isn't the values of those measures, its getting that FactTable[StartDate] column to be able to use the DateTable year and month fields as its hierarchy in the rows of a matrix. My only ideas are either add a year column and month column into the FactTable or duplicate a Date/Calendar table which Ive read is a bad idea. Just wondering if anyone else has had a Fact table with two different date columns and wanted to use the one that isn't the foreign key to the DateTable. I've seen Fact Tables with Start Date, End Date, etc and neither are used as the foreign key so are they unable to use DateTable month, year, etc columns for a matrix?
