Oracle BI EE 10.1.3.4.1 – Solutions – Puzzle 3

After almost a week of me giving out the Puzzle 3, there was hardly any interest for this one (just couple of odd replies requesting clarification of the Puzzle). Probably this is because this one has no direct practical usage and also there are lots of possibilities. But to me this is one very important Puzzle as in many cases when we are called in for repository tuning, the first question that we normally get in such situations is, why does BI EE generate such a big query when the same report can be solved by a very simple query. In such cases, we need to know where to look at and also understand what can cause BI EE to generate long SQLs. Remember, there is no theoretical limit to the length of the SQL generated (we can make it to generate as big a SQL as we want). The puzzle was meant primarily to know the possibilities of what can make BI EE to generate long SQLs. I always try to visualize a repository by looking at the SQL. That generally helps in doing further analysis on a pre-built repository.

Solution 1 – Conforming Dimensions:

This is probably the easiest and the most common reason why BI EE generates a lot of sub-queries. Always conforming dimensions should be used only when absolutely necessary as that will start generating sub-queries for every fact. For example, if you look at the repository below, it shows a very simple Business Model and Mapping layer containing one dimension with one Logical table source pointing to the physical CHANNELS table.It also contains 3 Facts each having a count metric (Mapped to 1 in the BMM layer for all the 3 columns)

There is also one more Logical Column that basically adds all the 3 columns together using a logical calculation

Now, when you generate a report using the CHANNEL_TOTAL and the logical calculated column, you will notice that BI EE will generate 3 sub-queries and then will bring them together as shown below

So the solution is you can create n number of conforming dimensions like this to make BI EE to produce n sub-queries thereby making the SQL very long. In this case there is no need for actually using conforming dimensions. The same SQL can actually be converted into a single SQL with all the counts (without the sub-queries). This basically demonstrates a bad use of Conforming Dimensions.

Solution 2 – Fragmentation:

This is another way of generating big SQLs. Same source can be made to appear as part of UNION ALL queries using Fragmentation. For example, if you look at the repository below

there are basically 3 logical table sources that contribute to the Fact Count. Each logical table source is modeled in a way such that all of them contribute to the Count and follow Parallel Fragmentation as shown below

And if you look at the SQL, you will notice that BI EE will fire 3 UNION ALLs to generate the count. You can make this query as big as you want by adding more and more logical table sources.

Solution 3 – Level Based Measures:

This is another possible solution where incorrect use of Level-Based aggregation can start generating pretty complex queries. In your queries, if you start noticing Partition By using ROW_NUMBER or SUM() OVER functions then that means level based measures are being used somewhere (not in all cases but in most of them). For example, lets look at the repository below

As you notice we basically have have 3 count columns each dependent on the other. Count1 is a normal measure assigned to a constant 1 and to the lowermost level in the Channel Dimension. Count 1 is a measure which is equal to measure Count(logically calculated) but assigned to the Channel Class level. Count3 is equal to measure Count2(logically calculated) but assigned to the Total level.

As you see, this basically demonstrates why logically calculated measures can have different level assignments than their base members. If you look at the SQL generated,

you will notice a number of sub-queries which will equal to the number of level assignments for each dependent measure.

In all the cases above, the queries were generated using a single Physical table and from just using 2 report attributes. Much more complex queries are possible using other methods but most of them will be a variation of the 3 listed above. Puzzle 4 to follow tomorrow.