Multiple star or snow flake schema for universe

[Former Member]

Hi,

I would like to know following things

1. Can we use more than one star or snow flake schema for an universe? and how to do this?

2. Using multiple shemas for one universe is it good practice or not?

Regards,

Manjunath

[Former Member · Accepted solution]

Here you go...

1. Can we use more than one star or snow flake schema for an universe? and how to do this?

Yes, we can include multiple star or snow flake schemas in a single universe.

How to do this? You can just insert all the tables in the universe and make joins as per your data modeling. Once you have done that, create one context for each star or snowflake schema. That is the general practice which we follow when we have mutlipe star schemas. Assuming that, we create reports based on a single star schema.

If you want to create reports by the combination of objects from multiple star schemas, probably you can define context with set of joins from mutlipe star schemas.

Generally we go for mutli data providers in report level, if we want to combine data from multiple star or snow flake schemas

2. Using multiple shemas for one universe is it good practice or not?

You can include multiple star schemas. There is no point of creating one universe for one star schema.

Again, it all depends on requirement. If you have set of dimensions which are common for both star schemas, its better to define those schemas in a single universe.

Regards

Gowtham

[Former Member · Accepted solution]

Gowtham, I have the same requirement as you replied above:

"If you want to create reports by the combination of objects from multiple star schemas, probably you can define context with set of joins from mutlipe star schemas. "

Can you elaborate more on "Set of Joins"?? Not sure I understood that part of it..

Thanks

[amitrathi239 · Accepted solution]

Hi,

Context is basically group of joins.So "Set of Joins" means include joins in the one context which define the path between tables.

In the multiple star schema you have multiple Fact tables.Create one context for one fact table include associate joins for dimensions and fact table.It means context set the path between the tables.

For for information how context will work. Go to the below link.

http://www.dagira.com/2008/03/10/what-is-a-context-anyway/

Thanks,

Amit

[Former Member]

Manjunath,

This is exactly where BusinessObjects excels.

When dealing with multiple fact tables, you use contexts.

Contexts are very simple to understand and you must take your time to do so if you are going to successfully develop universes based on more than one fact table. No matter what universe you build, the rule for contexts is always the same; there are no different circumstances based on, say, industry.

Each context starts with a base table, typically a fact table, where all its joins are at the many end of the relationship. The joins are then followed out through the joined tables up other joins where they in turn are at the many end, as in a snowflake schema.

For example, consider the very basic schema below:

D1 -< D2 -< F1 >- D3 -< F2 >- D4

There are two tables that only have many joins attached to them - F1 and F2.

Starting with F1, I can move to D2. I can also move from there to D1. In the other direction, I can move from F1 to D3. However, I cannot move from D3 to F2 because the join cardinalities are in the wrong direction. So, I've found all the joins that belong in the first context. They are D1-D2, D2-F1 and F1-D3. By the same process, I will get to the second context containing joins D3-F2 and F2-D4.

It doesn't work well with many-to-many joins, but you really shouldn't be facing these in a well-designed multi-star.

As for one-to-one joins, set the cardinality as one-to-many in the direction that you know the relationship should be in for the ownership to work out correctly..

The process that I've described above is essentially how the Detect Contexts algorithm works.

The only remaining thing for you to read up on is the SQL parameters but essentially you need ot be able to select multiple contexts and generate multiple sql statements for each context. Otherwise, what's the point in defining them?!!

Hope that clears it up for you.

Regards,

Mark

[amitrathi239]

Hi,

Yes, you can use the multiple star or snowflake schema in the universe.Universe design is depend on the requirement and universe can not restrict to use start or snowflake schema. More is first under stand the requirement and tables joins.look on the loops and traps.