One of the important points to consider when
moving from BI EE 10g to 11g is in understanding the BI Server behavior
changes. Though there are quite a lot of changes in the underlying code
for performance improvement etc, introduction of new features in 11g can
potentially alter your generated queries in 11g. In this blog entry i
will basically cover 2 main important changes that have been introduced
in 11g. There are more actually, but that is something we will try to
cover in our training days event.
1. Level Based Measures at Detail Level:
One significant change in 11g is the handling of
level based measures at the detail level. For example, lets consider a
measure AMOUNT_SOLD that is assigned to the detail day level of the date
dimension.
In
10g, whenever we bring this column for reporting, we would basically
see something as shown below i.e. the level will always be enforced.
In the case of 11g, there are 2 types of behavior
a. If the report contains normal attribute
columns and when the query is at a grain higher than the measure grain
(say the report is at the Year level), then normal aggregation will be
applied and the level will not be enforced like in 10g. An example
screenshot is given below
b.
If the report contains hierarchical columns, then if the query is at
higher grain than the measure, the measure values will become null. The
values will appear at that grain or below. An example screenshot is
given below
2. Priority Groups & Order of LTS:
In 11g, there is a new concept called Priority
Groups. In 10g, the LTS that determines the final query is dependent on
the ordering of the LTS in the dimension logical table(in most cases –
but there are cases where we cannot be always be sure to guarantee this
behavior). But in 11g, this is determined by the priority order set in
each and every LTS.
For example, lets consider a case where there are 2 aliases for the same dimension CHANNELS as shown below
In
the business model and mapping layer, lets assume that both the LTS are
mapped to all the columns. Both the LTS are at the same detail level.
Now, lets assign a priority group of 0 for the Channel Alias – B LTS and
a priority group of 1 for the Channel Alias – A LTS. Also the SALES LTS
of the Fact table will also be assigned to the priority group 0. In
11g, the LTS with the least number is considered as the one that has to
be chosen for querying (highest priority)
Now, lets create a report containing columns from Channels dimension and the Sales fact.
If you look at the query of the report, you will notice that Channels Alias – B will be chosen as it is the one having the highest priority.
Lets now go back to the repository and reassign the highest the priority to Channels Alias – A as shown below
For
the same report, if you look at the query now, you will notice that
Channels Alias – A will be chosen instead of the B alias.
No comments:
Post a Comment