I am a beginner and I have a question about designing snowflake in the OBIEE repository.
I have a Fact table and 6 dimension tables
The joins are something like this
Dim B --> Fact A
Dim C --> Fact A
Dim D --> Fact A
Dim E --> Fact A
Dim F --> Dim E
So the Dim E is snowflake to Dim F. The joins are pretty straighforward.
I would like to know the best way to design these tables in the OBIEE repository. My first idea is to create a view in my Oracle database of Dim F and Dim E in order to link these two tables directly to Fact A. Would it be a good idea performance wise ? If not, what would be a better design ?
We need to consider snowflake schema in BMM layer not in Physical layer and its rare in BMM layer.
Go with joins as mentioned for Physical layer and coming to BMM for logical table Dim E try to add Dim F as 2nd logical source else
physically map to the Dim E (Using Properties and then use Add button)
However, I am still confused. I tried with creating an Oracle view and join it directly with my fact table and it worked fine. I also tried to create logical tables with my joins when you suggested. The thing is the view seems to be quicker when I try to retreive my data than the logical tables.
I would thought that the view would be much slower as we cannot create indexes between a view and an Oracle table (here my view is my dimension and the Oracle table is my fact table).