Oracle BI EE 11g  Decoding Essbase Connectivity  Part 4  Logical SQL Generation
In the last 3 posts we saw how BI EE generates the MDX and what major factors influence the MDX generated. As a recap, there are 4 main things that we noticed.

BI EE generates the most optimal MDX as long as we have all the attribute or Hierarchical columns in the RowEdge of a Pivot.

While using Selection Steps, the number of MDX generated can increase proportional to N^M where N is the number of dimensions and M is the average number of selection steps in each dimension.

While moving hierarchical columns to the Pivot Column edge and while drilling on the column edge, the number of MDX generated can increase quite a lot

Every MDX generated will have a corresponding SubLogical SQL combined to the main Logical SQL through UNION ALL
The last point above is very important as the performance of a given BI EE report going against Essbase is determined by that. We start off with decoding how and why BI EE generates so many SubLogical SQLs (for the same Case 2 we discussed in the last post)
This generates a single Logical SQL with only one SubLogical SQL. Now let's move the Market Dimension to the Column Edge of the Pivot and then look at the SQL generated. Interestingly this generates a logical SQL with 3 SubLogical SQLs. Let's look at the Logical SQL's closely. In the first Logical SQL above, we had a statement like this But after we moved one of the columns to the column edge, we notice 3 logical SQLs with the following filters As you see, the Pivot Operation introduces more SQLs. Let's try to understand why this is needed. So, to make it simple we apply a selection step on both the dimensions to basically start with predefined members as shown below. This will also produce 3 SubLogical SQLs (as we are applying only one selection step per dimension i.e. 1^1 = 1  no extra logical SQL because of selection Step). If we now look at each SubLogical SQL you can notice the following The last SQL alone would have been sufficient to generate our report. But BI EE generates 2 additional Logical SQLs which are not necessary, atleast for this report. Why does BI EE generate these 2 additional queries. The 2 additional queries look like total queries on the rowedge and columnedge. Maybe these are for the totals. To validate this, let's try adding a rowwise and columnwise total in the pivot report. The total numbers exactly match with the numbers that we got from the first 2 logical SQLs shown above. But again, if we look at the logical SQL, there will be a total of 6 SubLogical SQLs. The 3 additional ones are shown below (for the totals) One interesting thing to note here is, each total fires a separate query which is a new feature in BI EE 11g for making sure that the totals generated are correct and as expected. But still we are getting new queries generated for totals. And We haven't accounted still for the need of the first 2 sublogical sqls. Let's investigate further by removing the subtotals and then drilling down on the East member. You will notice 5 sublogical SQLs fired to generate this query. Rather than taking each and every sublogical sql, let's take the actual logical sql and fire it. As you notice, the Queries 4 and 5 are the only ones needed for generating the actual report. But the first 3 queries seem to be doing something in addition to what was actually asked for in the report. If you notice closely, the first 3 queries seem to have an integer which controls the sort order. Now, is it possible that these three additional queries are there just to enable the outline sort order. To test this, lets move the Market dimension back to the Rowedge with the East member exploded to its children as shown below Let's now take the complete Logical SQL and fire that against the BI Server. Very interesting. We now have the SORTKEY pushed into the grain of the query itself. So, what essentially is happening is, if we have the hierarchical columns in the rowedge, there is noneed for BI EE to fire another logical sql to get the sort order as that can be obtained as part of the query grain itself. But in the case of hierarchical columns on the column edge and rowedge, there will be additional queries fired. Now to find out how many such queries will get fired, lets move all the columns to the column edge as shown below. If we now look at the Logical SQL, there will be only one that too with the SortKey pushed to the grain of the query itself as shown below. So, only when we have hierarchical columns in both the rowedge and the column edge, we have separate queries fired to extract the sort order. So, given below is a matrix that shows the number of additional SubLogical SQLs In the next & the last post of this series, i will summarise the findings so far and perhaps try to come out with a set of highlevel pointers that should hopefully help people trying to implement BI EE 11g on Essbase.