Convert snowflake schema to star

Question

Hi Experts

I'm looking for guidance and help in converting a snowflake type physical schema to Dimensional Logical schema. from my reading and actual experience having snow flake logical it's not supported to use snowflake in BMM layer, i'm getting errors like nQSError: 15048 in the admin tool

in the physical schema i have a fact table Fact_A have relation to say Dim_A and Dim_B dim tables . these Dimension tables are fact tables with other Dimensional (e.g Dim_A (is now fact table) for Dim_C and Dim_D , ... and so on)

I'm new to OBIEE so appreciate more details and elaborations if possible

some thing i tried

1- in one blog (Linking Dimensions to Fact Tables in a Snowflake Schema Design | CodeIT – An OBIEE Blog )i read that we can have multiple  star schemas (by creating different alias for the same table that plays different roles like fact and dimension) . i tried this but didn't work for me . i got error when i ran a simple analysis that join the data from the 2 star schemas i created . so i s this supported and should work?

2- i started creating a logical fact table with multiple logical table sources, but half way in i found all my tables became LTS in the this fact logical table and got confusing and complex for me at least, so i decided to stop and ask for more guidance

i want the simplest and most effective approach if possible

Robert Angel · Answer

Hi,

did we answer your question or do you need further explanation on my explanation?

[Deleted User] · Answer

+1 to Robert. Best solution is to already do it in the ETL.

As for the logical modelling approach it's totally possible but before going any further: Do you have SampleApp running somewhere? http://www.oracle.com/technetwork/middleware/bi-foundation/obiee-samples-167534.html

Robert Angel · Answer

Hi,

the most performant way to use OBIEE is to solve the snowflake issue by not building a snowflake with the ETLs that populate the data warehouse. Star is best 10 times out of 10.

In a less performant environment, which can be as a consequence of real time requirements requiring 3NF modelling, or can be as a consequence of 11th hour requirements being met by OBIEE sub-optimal solutions rather than data warehouse fixes for timeliness / cost then the approach is that you model the business model layer as though the solution was a star schema.

Simple example; -

Physical; -

Fact Table: F1

Dimension Table: D1

Snowflake Table (sic): D2

So in physical layer joins (=>) are; -

F1 => D1 => D2

In the physical layer you model F1 first, then D1 with logical join ->

F1 -> D1

Then you open the open the logical table source (LTS) of D1 and drag D2 onto it from the physical layer.

Then examine the join D1 => D2 in the LTS and ensure the type fits your needs

Note F -> D1 remains as it was.

Finally delete the columns you do not need from D2 and rename what you do need to business appropriate terms.

Note if your snowflake requirement is only 1 small table then you might want to look into lookup tables also.

but - @Christian Berg is 100% correct, get some decent training, I have seen too many god awful subject areas with horrible performance and no standards applied in their build to think that learning on the job is a good way to go on a project, and indeed have spent many a long month employed to rebuild the same....