This discussion is archived
3 Replies Latest reply: Jan 3, 2013 12:48 PM by Srini VEERAVALLI RSS

Designing snowflake in OBIEE repository.

Chiwatel Newbie
Currently Being Moderated
Hi everybody,

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 ?

Many thanks !

Chiwatel.
  • 1. Re: Designing snowflake in OBIEE repository.
    Srini VEERAVALLI Guru
    Currently Being Moderated
    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)

    Its all depends on your data.

    Hope this helps, if helps pls mark
  • 2. Re: Designing snowflake in OBIEE repository.
    Chiwatel Newbie
    Currently Being Moderated
    Hi Sirini,

    Thanks for your reply.

    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).

    Any thoughts ?

    Thanks,

    Chiwatel.
  • 3. Re: Designing snowflake in OBIEE repository.
    Srini VEERAVALLI Guru
    Currently Being Moderated
    I've no idea.. i doubt on cache... ;)

    If my last message is helpful pls mark.

    Appreciate if you mark on my message too

    Edited by: Srini VEERAVALLI on Jan 2, 2013 5:24 PM

    Edited by: Srini VEERAVALLI on Jan 3, 2013 2:48 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points