Lots of detail here - for a succinct question, skip to the end.
I have an application that has several fact tables. They share some dimensions, like quarter and year and country. When the app was built, there was no tab or object that displayed information from more than fact table at a time, so an easy solution was for each one to have it's one dimension fields. For example, Fact1Country and Fact2Country are separate fields that show up as selections in different parts of the application. It's worth noting that the country lists share a lot of crossover here, but are ultimately different.
So of course, now the requirement is changed to have a unified view of all the data in the application. So on this screen, the list box needs to have all the countries from both Fact1Country AND Fact2Country (and Fact3Country, etc.). There also needs to be a quarter and year selection which needs to filter correctly across all of those fields (Fact1Year, Fact2Year, etc.).
My initial thought was to just concatenate the different fact tables, and make sure there is one master location dimension table which would include all countries that exist across the various country fields right now. Unfortunately, some fact tables use a country code to associate, others use literal country name. I don't want to use country name across the application nor do I want to somehow generate/populate country codes for those that don't have it, so I don't think the simple concatenation solution will work.
If I just change all of the Country field names to Country (Fact1Country becomes Country, Fact2Country becomes Country, etc.) it works in the sense that I then can get every country in one listbox, but since I can still only choose 1 set of Quarter and Year fields to make selections on, it excludes too many. For example, if I select Q1 2012 in the Fact1Quarter and Fact1Year fields, then country which used to exist ONLY in the Fact2Country field is now excluded.
I can't just make a master calendar and link all the fact tables to it along with a master country list, because then we get a circular reference.
So I guess the basic question is if you have two or more fact tables that need to reference the same dimension tables, and concatenate isn't an option, what's the best solution?
.png "Former Employee"){kind=icon}
