Question on symmetry in Star schema

[dm_modeler]

Hi
I read Kimball's Data warehouse toolkit and I came across a point that one
of the advantages of having star schema is - BI / reporting tools in the
market can leverage the symmetrical nature of the star schema.
From what I know and I could understand is
"any star schema would have a central fact surrounded by set of dimensions
and hence if a table is identified as fact then all surrounding dimensions
linked to it would have a one-to-many relationship with the fact."
I am wondering how exactly this information is helpful to BI tools and how
their internal make use of the star schema.
I am not very clear on this, it would be great if more explanation is
provided on this point.
Thanks in advance

[ngalemmo]

Tables in a star schema have clearly defined roles and relationships that BI tools can take advantage of in terms of deciding join paths to resolve a query. This is not the case in an ER model where all tables are peers, so in tools that are schema agnositic (such as Business Objects), there is a lot more definition work for a developer to do to clearly lay out how tables interact and how queries need to be handled.

BI tools designed specifically for star schema are easier to develop because they require less information about the schema. Each star is an independent unit with its fact table and associated dimensions. A query against a star is not complicated by potential alternate join paths that may exist in a normalized model. Where bridges are used, again, it is a single line join path from the fact to the dimension.

Where queries use more than one star, the process to combine facts is well defined and invariant. The tool applies the same query template regardless of the stars involved.

[caderoux]

Star schema models are already decomposed into areas, unlike 3NF models which are more like an entity network.

I'm not sure what you mean by symmetry - there certainly is far greater consistency in dimensional models than in 3NF models. And most dimension tables are just like any other, and the joins are all very simple.

In a star model, because the relationships are consistently simple, there is a relatively easy way to represent the fact and attached dimensions and turn this into code. The star can easily be flattened with a view with no loss of data or semantics. This is only true in 3NF models to the extent that a view could be created that is a decomposed projection of the 3NF system which exposes that subject area. The work to create such a view in a 3NF model is obviously more significant - it's effectively the same as the dimensional modelling job - partitioning the fact data into a subject area, choosing a grain, assigning dimensions, ensuring a fact lives in only one place.

In a 3NF model, while all your facts _might_ be in one place for a subject area, the dimensions are very likely to be far away through several joins. In addition, the potential for many-to-many relationships or multiple joins causes the possibility that generating single facts without choosing a bad query or path and doubling or tripling the facts through a join arises. This problem will not appear in a single star model. When you combine stars, of course, you would potentially have to address this.

[Sponsored content]