Hi all,
I have two queries with different granularity, the first have two dimensions (ie Country and State) and a measure (ie income) and the second a dimension and a detail (ie Country and abbreviation of the country).
The two queries share a dimension (country) but have diffent granularity, and the relation is not 1:1.
I need a table with all the dimensions and the measure, like this:
Country, abbreviation of the country, State, Measure
I tryed merging the two Country dimensions, but the relation is not 1:1 and it doesn’t work (abbreviation is always blank).
You can merge the two country dimensions, but there are rules around usage, such as you cannot use unmerged dimensions from both sides.
First thing I would suggest is to merge the country dimension and insert all three versions of country into one table (the merged dimension and the country dimension from each query).
The 1:1 issue is around state - a country has more than one state, therefore state is not a detail of country.
However, you could make country abbreviation as a detail of the merged dimension and add that to the table because a country has only one abbreviation and an abbreviation only has one country, i.e. do not use the abbreviation dimension itself in your table.