Oracle BI EE 10.1.3.4.1 – Solutions – Puzzles 1 & 2

Venkatakrishnan J

Feb 20, 2010 • 7 min read

Well its been almost a week since i blogged the first 2 puzzles in this puzzle series. I guess it is time for me to blog about the solutions. I have been amazed by the response so far. Its been really positive, so more puzzles to come during this week and the next.

Puzzle 1 Solution1:

Well, this was a very easy puzzle and lot of people got it right. There are a couple of possible solutions. The most obvious and the easiest solution for this is to create a universal security group in the repository and then in the filters area assign the column level filters (vary the filter depending on the column) as shown below

I had to put this as a puzzle since this is one feature that i feel is the most under-utilized in BI EE. The drawback with this is the fact that this solution cannot be applied for Administrator user directly. But that is one user that is rarely exposed directly to end users.

People who got it right: Nicolas Gerard, Fiston and Harish Gopalan

Puzzle 1 Solution 2:

The other solution is to use a combination of multiple logical table sources in the dimension logical table. This is again another solution that directly affects the modeling we do in the Business Model and Mapping layer. Though it requires some work compared with solution 1, this can prove to be quite useful when the requirement is rephrased a bit like this. The report columns and the filters for the rephrased question is given below

“CHANNEL_CLASS & AMOUNT_SOLD” – Filter required is CHANNEL_CLASS = 'Puzzle1 Test'

“CHANNEL_DESC & AMOUNT_SOLD” – Filter required is CHANNEL_DESC = 'Puzzle Test2'

“CHANNEL_CLASS, CHANNEL_DESC & AMOUNT_SOLD” – Filter required is CHANNEL_DESC = 'Puzzle Test3' and CHANNEL_CLASS = 'Puzzle Test4'

In such a case, solution 1 cannot be used. Only solution 2 can be used. Screenshots of the BMM is given below for this

Each LTS will have a separate filter. Ordering of the LTS is important to push the predicate filters properly.

People who got it right: Christian Berg, Nicholas Gerard and Ilmari Alto

Puzzle 2:

Puzzle 2 was a bit trickier as there are quite a few possible solutions. But lot of people answered it correctly.

Puzzle 2 Solution 1:

Everyone who answered this correctly, have used the solution 1. This basically alters 2 Database Features. They are given below. The idea is to basically enable both the options below

PERF_PREFER_MINIMAL_WITH_USAGE
PERF_PREFER_INTERNAL_STITCH_JOIN

Or the other option is to just switch between 10gR1 and 11g/10gR2 as the database in the database type (this basically does the same thing as above – enables both the options above)

The drawback with this solution is, altering the database features can start affecting other queries. WITH clause provides such an advantage and sometimes even in a normal model(without conforming dimensions), the queries might get pushed to the BI Server. I generally do not recommend altering the database features unless its told by Oracle support. For example there have been instances in older versions of BI EE where PERF_PREFER_INTERNAL_STITCH_JOIN was asked to be enabled/disabled by Oracle support (due to bypass some 10gR1/10gR2 database bug). I generally prefer a solution where everything is always pushed to the database but if due to some reason we want to enable the BI Server join then we should be in a position to do that. For example, there have been instances(couple of bugs in BI EE which even exists in 10.1.3.4.1) where BI EE generates wrong sub-totals when pushed to the database. In many cases, this happens when conforming dimension joins happen at the database and we start getting nulls in the results. To check whether sub-total query is wrong we might want to push everything (even the conforming dimension join to the BI Server). The following 2 solutions basically demonstrate that. One other potential issue is, if you have more than one fact table from the same database then, even those conforming dimensions will be pushed to the BI Server layer. The other drawback is, i can still make BI EE to generate the database joins. Want to know how? Well, lets enable the above 2 settings in the physical database features. And lets create a report. In the Advanced SQL of the report, enter the below logical SQL

SELECT A.CHANNEL_CLASS saw_0,
 A.QUANTITY_SOLD saw_1,
 B.UNIT_COST saw_2
 FROM
 (
 SELECT Channel.CHANNEL_CLASS,
 Sales.QUANTITY_SOLD
 FROM "SH - Conforming Dimension)
 A FULL OUTER JOIN
 (
 SELECT Channel.CHANNEL_CLASS,
 Costs.UNIT_COST
 FROM "SH - Conforming Dimension") B
 ON A.CHANNEL_CLASS = B.CHANNEL_CLASS
 ORDER BY saw_0

If you look at the report now, you will notice that this join has actually been pushed into the database (even after disabling the WITH clause and STITCH join features)

But from a Puzzle standpoint, i was expecting any one of these solutions. So thanks for everyone who answered this correctly.

People who got it right: Vinod Jaganathan, Harish Gopalan, Somasekhar

Puzzle 2 Solution 2:

This involves adding more metadata to the repository and altering the BMM layer. But this provides you complete control on how the joins will be generated and you can be sure about the structure of the SQL Query. Basically the idea is to replicate the dimension and the fact table in 2 separate databases. For example, CHANNELS and SALES in one database & CHANNELS and COSTS in the other database as shown below

And your BMM dimension will have 2 sources (same CHANNELS source coming from 2 different Physical Databases as shown below

If you think logically, when we model the same set of tables in different databases, from a BI EE standpoint, these 2 are completely different. So, if you think from a SQL standpoint, the only way we can enforce the conforming dimension join in the database is by using Database links. Since we do not have them here, only way BI EE can join them is by stitching them in its memory. Remember, if you create aliases within the same physical database, even then BI EE will force the SQL to the database as, from a BI EE perspective, all the tables still reside in the same database. The issue with this approach is, it will become very difficult to manage the BMM layer especially when you have a physical source like BI Applications. But this is something to keep in mind while doing any Repository Modeling.

People Who got it right: Scott Powell (was close in mentioning the alias solution but i give the benefit of the doubt as this was the only comment suggested that was in line with this solution)

Puzzle 2 Solution 3:

This is something that is used rarely. Now that 11g is around the corner where everything will be based on View Objects etc, it becomes even more important to consider this. Understanding Logical SQL is very important as in many cases we would be using BI Server and some other front end instead of presentation services. The solution is to use a logical SQL that basically combines both the facts in separate sub-queries. This is not a straight forward solution. This requires introducing just a single CHANNELS table in another physical database (instead of all the tables,including the facts as described in the above solution) and creating a dummy Presentation Subject area containing the table.

Then use logical SQL method to push the join to the BI Server layer (introduce the new presentation layer object Channels)

A simple logical SQL that i used is given below

SELECT
 A.CHANNEL_CLASS saw_0, A.QUANTITY_SOLD saw_1, B.UNIT_COST saw_2
 FROM (
 SELECT
 Channel.CHANNEL_CLASS,
 Sales.QUANTITY_SOLD
 FROM "SH - Conforming Dimension"
 ) A FULL OUTER JOIN (
 SELECT
 Channel.CHANNEL_CLASS
 FROM
 "SH - Conforming Dimension#1"
 ) B ON A.CHANNEL_CLASS = B.CHANNEL_CLASS FULL OUTER JOIN (
 SELECT
 Channel.CHANNEL_CLASS,
 Costs.UNIT_COST
 FROM
 "SH - Conforming Dimension"
) C ON A.CHANNEL_CLASS = C.CHANNEL_CLASS
 ORDER BY saw_0

As you see i basically fired 3 separate sub-queries and then joined them together in the logical SQL. When you enter this in Answers, you will notice that the join is pushed to the BI Server irrespective of how you have modeled the repository. A sub-query join in BI Server means a join at the BI Server layer.

If you look at the SQL, you will notice that there are 3 SQLs that get fired simultaneously and all of them are joined in the BI Server memory. Not exactly the solution i was looking for but this pushes the join to the BI Server layer. This technique has a lot of drawbacks as well like you lose the drills in the front-end etc. But i generally always like the logical SQL approach not for implementation but for visualizing how your BMM layer has to be structured.

There is one more solution as which also uses an extra new physical database table. But i will leave that for now as it more or less follows what i have discussed in the above 2.

There can be other possible solutions as well. If you can think of any feel free to add them in the comments. As i have said this before BI Server is one of the best tools out there that can effectively model most reporting scenarios. There can be more than one possible way of arriving at a solution. Of course, there will be advantages and disadvantages associated with each approach. The idea of the Puzzle series is to basically highlight as many solutions along with their drawbacks etc.

Sign up for more like this.