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.
Is this right approach ?
Please, let me know your feedback.
Edited by: GRK on Jan 30, 2013 8:29 AM
Edited by: GRK on Jan 30, 2013 8:30 AM
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 ?
That might be the reason for LTS.. try to map those tables to Dim1.
If you still have same issue let me know the message
You havent mentioned how they mapped to the fact
If it is like
then you may go for diff logical tables for Dims
if not you have to Map them to Dim1
Edited by: Srini VEERAVALLI on Jan 30, 2013 10:47 AM
In physical layer it was mapped as
Fact - Dim1
Fact - Dim2
Fact - Dim3
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