Database Reference
In-Depth Information
Advanced modeling with many-to-many
relationships
Many-to-many dimension relationships can be leveraged to present data in ways
that are not feasible with a traditional star schema. This opens a brand new world of
opportunities that transcends the limits of traditional OLAP and enables advanced
data analysis by using pivot tables without writing queries.
One common scenario where many-to-many relationships can be used is a survey
consisting of questions that have predefined answers with both simple and multiple
choices. The typical star schema model (one fact table with answers joined with a
questions/answers dimension and a case dimension) is fully queryable using SQL.
However, while it is very simple to compare different answers to the same question,
it could be very difficult to relate answers to more than one question. For example,
if we have a question asking the respondent which sports they played (multiple
choices) and another one asking what job they did, we would probably like to know
what relationships exists between those two attributes. The normal way to solve it is
to have two different attributes (or dimensions) that users can combine on rows and
columns of a pivot table. Unfortunately, having an attribute for each question is not
very flexible; more importantly, we have to change our star schema to accommodate
having a single row into the fact table for each case. This makes it very difficult to
handle any multiple choice questions.
Instead, we can change our perspective and leverage many-to-many relationships. We
can build a finite number (as many as we want) of question/answer dimensions, using
role-playing dimensions to duplicate an original dimension and providing to the user
with a number of "ilter" dimensions that can be nested in a pivot table or can be used
filter data that, for each case, satisfies defined conditions for different questions.
Another scenario is the one where a snapshot fact table contains the state of data
over time, recording "snapshots" of that state. If, for example, we register the
credit rating of a customer each month, an OLAP cube is pretty good at returning
the number of customers for each rating rank in each month. However, it is usually
much harder to analyze the flow of changes. For example, if we had 30 customers
rated AAA and 20 rated AAB in January, then we see that we have 20 customer
rated AAA and 35 rated AAB in June, what does it mean? It could be that we had
10 customers previously-rated AAA that have been downgraded to AAB and we
acquired 5 new customer AAB. But, it could also be that we lost 5 AAA customers
and 2 AAB customers, we retained 5 AAA downgraded to AAB and got 12 new
customer rated AAB.
Search WWH ::
Custom Search