I have following tables in physical layer - which are at different levels.
Dim1: Year, State ,State id 10 records
Dim2: Year, State, County , County id 90 records
Dim3: Year, State, County, City, City id 150 records
Fact table in Physical and bmm layer holds: Year, State, county, City but not id's.
Can i create only Dim table in Bmm layer with columns Year, State, County, City. And, make sure Year column holds logical mapping for all Dim1, Dim2, Dim3.
And, State for all 3 Dim's.
And, county for only Dim2, Dim3.
And, city for only Dim3.
I was able to do this one but i'm having problems while generating reports.
Some, times my report throwing errors - in regards to logical joins and detailed level...blah.. blah.. What might be the reason ?
In, busin. layer i have Dim1 mapped to Fact intially, then drag and dropped Dim2 and Dim3 into Dim1. So, it creates all the columns. And, if the column exists in all the dim's such as year, state present in all Dim's so those two columns are mapped to all Dim's L.T.S
Where as County is present in only 2 Dim's so it is mapped for only 2 Dim's as per L.T.S