Designing and Querying for Multiple Facts and Conformed Dimensions
Mark Rittman
When I go and visit customers who are some way down the line with
their BI EE implementation, one of the most common questions that gets
raised by the customer is around multiple facts with conformed
dimensions (an RPD modeling issue).
To take an example; there are two or three fact tables in a RPD
subject area, that share a number of conformed dimensions. Fact one
has four dimensions, three of which are shared with fact two. Fact two
has, for example, three dimensions, two of which are shared with fact
one and three, one of which is unique to fact two. And so on.
To me this seems a simple situation. If you write a request that
includes measures from fact one and fact two, and only reference
dimensions that are common to the two facts, both measures will be
shown. If you include a measure that is only linked to one of the
facts, BI EE will "CAST AS NULL" the other measure and not display it.
The BI Server will generate two logical requests in the query plan,
one for each fact to ensure that a fan trap error isn't incurred, and
then either generate one physical SQL query or two (with the BI Server
joining the results in memory) depending on the database platform and
version.
Where it gets complicated is where the customer wishes to "trick" this
model, perhaps to still show the measure from the fact that doesn't
link a dimension when it should be cast as null (by referencing the
missing dimension at the "Total" level, and then having the measure
repeat across the values of the dimension that it doesn't link to),
and other such techniques. I try to discourage this and stick with the
standard way that BI EE handles these situations.
Others reference what appears to be the BI Apps RPD modeling approach
which is to create one large fact table per subject area, include all
the measures for all facts in this large fact table, and all the
dimensions that are used in this fact - kind of like an Essbase
"hypercube".
Does anyone else have any thoughts, or design guidelines, on creating
RPD subject areas that include multiple fact tables and multiple
dimensions, some of which are shared by each of the fac tables? Do you
have "tricks" or other workarounds that can make it easier for users
to navigate these models?
Finally, when creating the presentation subject areas for each of
these facts, do you keep to one fact table per presentation subject
area, or do you include multiple ones?
I'd be interested in people's thoughts.
Mark
SriHari
Recently, I was in this situation where in I have 3 facts and 1
dimension confirming across 2 facts tables each. If you don't set the
levels for the measures, you are right in saying that it will cast as
null and the report gets a bit messy. However if you do set the level,
the outputs are acceptable.
The reason I couldn't put in one single table is for simple reason,
the table gets too big and harder to maintain (especially when we deal
with GL_Balances).
I would be also interested if others have any better idea.
thanks
Sri
je...@brewpalace.com
I don't know if Kimball explicitly addresses this, but I would expect him to come back with: prorate them as with a multi-variate dimension. Unfortunately I don't think this is the correct solution (for either situation really). I've found that most customers are ok with either repeating the value or using NULLs and find prorating to be odd.
When it comes to presentation layers, I don't find that a single fact table is very useful - put what you need in there and beef up your Subject Area Documentation. I try to make simplified ad-hoc Subject Areas, ones that might have fewer facts in them - but you'll still need them to have multiple fact tables in there.
Jeff M.
From: Mark Rittman <mark.r...@rittmanmead.com>
To: OBIEE Enterprise Methodology Group <obiee-enterpri...@googlegroups.com>
Sent: Sun, March 28, 2010 11:45:29 PM
Subject: [OBIEE EMG] Designing and Querying for Multiple Facts and Conformed Dimensions
Michael Wilcke
should simply make no sense to ask for a measure against a dimension
that it is not linked to. What is the meaning of an inventory level of
a customer? However, comparing inventory level and sales quantity of a
product (a shared dimension of the inventory and sales cube) is
perfect. The business should take empty cells as a nice hint to
reconsider their question.
If there are cases, where it is not obvious to the business user, then
the business model might not model the business close enough.
Another point of view: When working with a subject area in answers,
the business user just sees columns from (conceptually) a single
table. This is a great reduction of complexity for him (first from
physical something to business beauty and second from there to a
simple table).
However, in our case some combinations of columns are not meaningful
and do not work out properly. There are two obvious solutions: Either
leave some brain-work to the business user or limit the presentation
catalog to the subset of columns that can be combined.
imho: trying to work around this is techie, not business
Michael
Mahesh Kumar
Regarding the number of fact tables in subject areas, we have always
implemented as multiple subject areas with one or more fact tables,
though we kept it to a max of 3. We found this to be more manageable
interms of identifying what the subject area is for.
-Mahesh
Venkat
Good question. As you say, tricking the BI Server to report against
non-conforming dimensions is not the way to go. Having said that there
are quite a few reporting possibilities when we take non-conforming
dimensions into account. I think this completely boils down what the
end users want to see. This is something that has to be raised during
the start of a project and a decision has to be arrived at earlier.
There are quite a few possibilities when we report on a non-conforming
dimension and a fact measure. For example, following are some of the
possibilities
1. Show the measure as Null when Non-Conforming dimensions are chosen
(Default for the BI Server)
2. Show the measure as the same(SUM) for every Non-Conforming
dimension attribute value - Obtained by Assigning to Total Level
3. Show the measure as an average of the number of the Non-Conforming
dimension attribute values - This is not supported currently by BI
Server - This is where a pure multi-dimensional tool like Essbase can
easily achieve this
4. Show the measure as a SUM * Multiplied by a factor depending on the
Non-Conforming dimension attribute - This is not supported by the BI
Server
5. Show the non-conforming dimension values as 0 - Extension of Point
1 - But again can be achieved easily by doing a Logical Calc of IFNULL
in the RPD or an Answers Based calc of IFNULL
Points 3 and 4 might warrant a change in the data model (like creating
a common fact view as BI Server cannot do this or might require
Essbase). Thats the reason why this needs to be raised upfront while
starting on a project. But if we know for a fact that all the business
users want to see is covered in points 1,2 or 5 then we can quite
easily achieve them in BI EE by using level Assignments. I always make
it a point to raise this first and then let the business users
decide.
With regard to the point on multiple subject areas, atleast as of
10.1.3.4.1, i always try to merge everything to a single Subject area.
Of course this can lead to subject areas being too big and difficult
to manage. I only split the subject areas only when we are completely
sure that there is no requirement(now or in future) to report on
metrics that reside across subject areas. The problem with 10.1.3.4.1
is that the only way we can bring in metrics across subject areas is
by using UNION sets or by using a custom Logical SQL. In both the
cases, we will lose the normal drill interactivity. 11g should
hopefully change this.
Thanks,
Venkat
On Mar 29, 11:45 am, Mark Rittman <mark.ritt...@rittmanmead.com>
wrote:
Christian Berg
I totally agree with Michael. This is a business issue and cannot /
should not be solved by technical means. In 99% of the cases I have
faced, the root cause for this type of issue was missing or incomplete
data.
Another classic is, if there are measures which require calculation on
non-transactional data. E.g. a typical sales force analysis where the
business wants to know "Did our coverage improve over time?" (Coverage
= No. of Visited Customers / No. of Total Customers).
If you do not have a fact which is keeping track of the customer base
over time (e.g. monthly snapshots) then the described problem will
occur. Tricking OBIEE to show always the total or "something else" for
the non-conforming dimension (in this example the time) is a nice hack
and might be appropriate for demos and quick prototyping, but - in my
opinion - should never be implemented in production. Why not? Because
in this example the historically correct number of visits (as-was)
would be mingled with the most recent customer figures (as-is). The
result will be not correct and might lead to wrong decisions. Even
worse if the sales person has a personal target which is measured by
this KPI. He might have taken monthly printouts and can proof, that
our results are wrong.
If the measure is important and has value for the business then they
will do the necessary investment and we can design and implement it
properly. If it is not important, then we should skip it. Not having
it is better than having something wrong. The users will forget very
soon, that we have told them "Yes we can do it technically BUT keep in
mind that this is just a workaround because of your missing data and
the result will not be historically correct".
Regards
Peter
Jeff McQuigg
the first place - in other words eliminating the business requirement
to do so.
However, even if that is so, you'll still encounter these scenarios.
A common kind of example is:
Show me two metrics, where you have to filter one of them using a
dimension it does not work with. For lack of a better example, a
query like: show me the # of drivers in the state, and the number of
cars in the state that are Blue. The color does not work with the #
drivers, but does with the # cars facts. In this case the filter is
the same thing as it being added to the report from an OBI perspective
- it treats it as conformed and so you'll have to come up with an RPD
config to make it happen.
Jeff M.