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 Row-Edge 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 Sub-Logical 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 Sub-Logical SQLs (for the same Case 2 we discussed in the last post)
This generates a single Logical SQL with only one Sub-Logical 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 Sub-Logical 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 Sub-Logical 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 Sub-Logical 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 row-edge and column-edge. Maybe these are for the totals. To validate this, let's try adding a row-wise and column-wise 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 Sub-Logical 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 sub-logical sqls. Let's investigate further by removing the sub-totals and then drilling down on the East member. You will notice 5 sub-logical SQLs fired to generate this query.
Rather than taking each and every sub-logical 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 Row-edge 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 row-edge, there is no-need 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 row-edge, 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 row-edge 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 Sub-Logical 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 high-level pointers that should hopefully help people trying to implement BI EE 11g on Essbase.