I saw a peculiar requirement being discussed in a pentaho forum(i was actually browsing for some unique Pentaho features and stumbled upon the requirement) this week wherein end users wanted to get the sub-totals automatically while drilling on a dimension. In normal cases, i would have left it as a unique requirement and would not have tried to replicate in BI EE. But when i thought about this requirement further, it somehow seemed like a very common requirement to me. Whenever i look at my phone or internet bills, i always look for sub-totals. Without them a reporting tool would be incomplete. And BI EE does offer some good sub-total features out of the box. But what is not available is an automated Sub-Totaling feature after drilling down on a dimension in a dashboard. Many might argue that when we do the drill we are actually drilling from a Total to its individual values. But again sometimes end users would like to have this feature (atleast i would love to have this in BI EE).

There are couple of options to enable this. Both of them require some good amount of work (for a small feature) in the repository. I would recommend to use either of the solutions only for a dimension or 2 with not more than 4 to 5 levels. It is possible to use this for more than 3 dimensions as well but the repository can grow in size pretty quickly. We shall be seeing a technique that leverages the Conforming dimensions concept of BI EE. Conforming dimensions can be explained easily by the diagram below

As you see, whenever 2 different dimensions form 2 logical table sources for the same dimension and we have 2 different fact tables, then the dimension and fact combination would produce a union of the dimension members. The basic premise behind conforming dimensions is the fact that there is no linkage between the dimensions nor the fact themselves. That is Dim1 should be related to Fact1 alone and Dim2 should be related to Fact2 alone. If there is any relation between Dim1 and Fact2 or vice-versa, BI Server would not treat them as conforming dimensions.

With that background lets move on to the actual requirement. Producing dynamic sub-totals. The idea is very straight-forward. We make every member that we drill on to be a member in the column that comes up after drill (in addition to the child members of the parent member that we drilled on). For example, consider the report below

As you see, while drilling on Channel total member, we get the children of that member in addition to the member itself which simulates a sub-total. If we have n levels in a dimension when we model it we would have to model it as having n+1 levels. For example, in our example the Channel Dimension has 3 levels. Channel Total –> Channel Class –> Channel Desc. So, when we model in our repository, we need to basically have 4 levels as shown below.

According to the conforming dimension concept explained above, in order to ensure that BI EE fires different queries for every fact table, we need to create 3 aliases for the Channels dimension and 3 aliases for the fact table.

Include all the 3 channel aliases as logical table sources for the channel dimension. Now, ensure that mappings of the sources follow this table.

Also create 3 separate fact tables with the same aggregation for the amount measure. Each fact table will be sourced by a Fact alias

Now, for normal reports that do not require this capability, just pull in the dimension and one of the fact alias tables. Wherever this sub-total drill is required one would have to ensure that all the fact measures from all the fact tables are chosen. This will ensure that conforming dimension join kicks in as shown below.

From here on, its just a matter of combining all the facts into a single fact using the formula below

IFNULL(SALES.AMOUNT_SOLD,IFNULL("SALES1".AMOUNT_SOLD,0)+IFNULL("SALES2".AMOUNT_SOLD,0))

The position of the sub-total can be easily  controlled by adding one more sort order column in all the Logical table sources.

As with any reporting tool BI EE works on the premise of converting the front-end column selection to the corresponding physical queries. But one of the advantages that makes BI EE stand apart from other reporting toolsets is its ability to do certain joins/sorting etc in-memory without pushing them down on to the database. This feature has multi-fold advantages. Especially in BI Applications this feature is put into good use by having a single repository that can report across multiple ERP modules using conforming dimensions. One other advantage of this is the fact that it can help in generating reports that produce all dimension members even if those members do not have a transaction.

For example, whenever a dimension and a fact measure are pulled into a report, BI EE will convert them into a physical join between the dimension and the fact measure. So, in effect only those dimension members that have a corresponding fact transaction would come out in the report. In most cases, end users might want to see all the dimension members. This is illustrated better by the screenshots below

As you see, even though we have 5 different Channels, since our fact table had only transactions for 3 channels, our report produces data only for 3 Channel Members. But what if the end users want to see all the 5 members in the report (non-transactional channel members would be shown as Null in the measure). The most common resolution for this is to use outer joins in the physical layer. But this cannot be done in all the cases as when one applies any filter on the fact measures, the outer join effect is negated. Also, there are possible performance impacts in using outer joins. So, lets look at a modeling technique that will negate the use of outer joins.

The basic idea is to simulate a Cartesian join across dimensions and then make the BI Server to do the conforming dimensions join using a separate dummy fact table. So, to start with create a Dummy fact table in the Physical layer which will be based on a Select (Select 1 FROM DUAL or something like that)

Now make every dimension in the warehouse to have a Cartesian join with this dummy fact table.

Now create a new Logical Fact table source containing this fact table and the Dummy Fact column. Ensure that the fact column has an aggregation set (same aggregation as the other measures in the original fact table)

Now, include this fact along with other facts whenever you want an outer join but with filters on the outer joined fact table. Just hide the dummy from display.

Now, sometimes end users might want to browse multiple dimensions simultaneously. Since there is no join across dimensions, this would result in not producing all dimension member combinations. This is illustrated below

As you see, we only get those channels for which we have transactions on the fact table. In many cases we do not want dimension member values to be dependent on the fact table. To negate this, we would have to use the same dummy fact table that we used above and put that as the implicit fact table as shown below.

Now, if you view the same report again

This is because, the Implicit Fact column makes the BI Server to choose the Dummy Fact as the fact table for joining the dimensions and hence would result in a Cartesian product across the dimensions.

One common issue that gets highlighted when someone uses BI EE is its inability to leverage Parent Child Hierarchies out of the box. 11g release scheduled sometime later next year is supposed to address this seamlessly. Having said that, being consultants ourselves most of the times we find ourselves in situations wherein we would have to use the parent child hierarchies. I would be covering a technique today that can basically address hierarchical reporting on parent-child hierarchies, in the current release itself.

It is possible to mimic value based hierarchical reporting in BI EE in a number of ways. I will provide 3 high level options here. Though there can be other possibilities they would only be slight variations of the 3 listed below

1. The first option is to flatten the hierarchies and convert the parent-child into a level based hierarchy. This is the approach that Oracle’s BI Applications product called CPM Analytics uses. This is a straight forward approach wherein you have a pl/sql or a sql program which basically converts the hierarchy into level based wherein the number of the levels is pre-determined.

2. The second option is to mimic everything using a combination of Answers UI navigation and Repository based drills.

3. The third option is to provide out of the box drills from the repository using a combination of BI Server Federation and Oracle database hierarchical operators. Here there is no need for setting up custom navigation in Answers.

I would be covering the 3rd option which requires fair bit of understanding of Federation, how BI Server uses the logical table sources and the issues inherent with a parent-child hierarchy. I would try to address them in a simplified manner here. Lets take the Global Schema. I will be using a parent child hierarchy table for the Products dimension (shown below)

As you see, it is a simple parent child hierarchical table. The idea behind the technique is to have one Logical table source for every parent-child level. This can be best illustrated by the diagram below

So effectively we would have n-1 logical table sources for n levels. For drilling from Level 1 to Level 2, we would use the Parent to Child Relation. From Level 2 to Level 3, we make the BI Server to switch to another logical table source wherein Level 2 would now be the parent and Level 3 would be the child. But when we drill, the values of Level 1 would have to be propagated across all the table sources. This is where the crux of the technique lies. So the first step in our technique is to alter the above parent child hierarchy table to contain the Level information as well as Level-0 product information i.e every record in the parent-child table should have the Level Number and should have all the Level-0 products that roll into the parent.

In Oracle 10g, this can be done easily through the sql below. We are doing this to ensure that we get the rolled up numbers for every parent. There can be multiple variations of the script below. But all we need is a level number and all the level-0 children of every record in the parent-child table. All the sub-queries below are not actually necessary.

SELECT LEVEL_1, LEVEL_2, PRODUCT_NAME, PRODUCT_ID, LEVEL_NUM
FROM
(
SELECT LEVEL_1,
CASE WHEN LEVEL_1 = LEVEL_2 THEN LEVEL_3 ELSE LEVEL_2 END LEVEL_2,
LEVEL_3 PRODUCT_NAME, LEVEL_3_ID PRODUCT_ID
FROM
(SELECT LEVEL_1,
CASE WHEN INSTR(PATH,'|',1,2) = 0 THEN LEVEL_1
WHEN INSTR(PATH,'|',1,3) = 0
THEN SUBSTR(PATH,INSTR(PATH,'|',1,2) + 1,LENGTH(PATH))
ELSE SUBSTR(PATH,INSTR(PATH,'|',1,2)+1,INSTR(PATH,'|',1,3)-INSTR(PATH,'|',1,2)-1)
END LEVEL_2,
LEVEL_3 LEVEL_0,
PATH LEVEL_1_ID, LEVEL_3_ID, LEVEL_3, LEAF
FROM
(
SELECT SYS_CONNECT_BY_PATH(PARENT_DSC,'|') PATH,
CONNECT_BY_ROOT PARENT_ID LEVEL_1_ID,
PRODUCT_ID LEVEL_3_ID,
CONNECT_BY_ROOT PARENT_DSC LEVEL_1,
PRODUCT_DSC LEVEL_3,
CONNECT_BY_ISLEAF LEAF,
LEVEL
FROM
(SELECT TO_NUMBER(A.PRODUCT_ID) PRODUCT_ID,
TO_NUMBER(A.PARENT_ID) PARENT_ID,
A.PRODUCT_DSC PRODUCT_DSC,
B.PRODUCT_DSC PARENT_DSC
FROM PRODUCT_CHILD_PARENT A,
PRODUCT_CHILD_PARENT B WHERE B.PRODUCT_ID(+) = A.PARENT_ID)
CONNECT BY PRIOR PRODUCT_ID = PARENT_ID) A
WHERE LEAF = 1)) A,
(SELECT DISTINCT PARENT_DSC, LEVEL LEVEL_NUM FROM
(SELECT TO_NUMBER(A.PRODUCT_ID) PRODUCT_ID,
TO_NUMBER(A.PARENT_ID) PARENT_ID,
A.PRODUCT_DSC PRODUCT_DSC,
B.PRODUCT_DSC PARENT_DSC
FROM PRODUCT_CHILD_PARENT A,
PRODUCT_CHILD_PARENT B WHERE B.PRODUCT_ID(+) = A.PARENT_ID)
CONNECT BY PRIOR PRODUCT_ID = PARENT_ID
START WITH PARENT_DSC = 'Total Product') B
WHERE A.LEVEL_1 = B.PARENT_DSC

The idea is to create a table which would hold the output of the above script. Now import this table into the physical layer. Since Product id would no more be unique, make a complex join with the product_id in this table and the fact.

All this gives us is the information required for the first logical table source. Remember when we jump from one logical table source to another we would still be having the filter for Level 1, Level 2 etc. If we leave those columns to be mapped to Parent or child columns, the drills would not work. So, for each additional logical table source we would need an additional Select view within the physical layer. This select view will be a Cartesian product of the filterable columns. Do not worry about the performance much as the Cartesian product would still be producing only valid combinations as we would be filtering them while drilling(but this filter in effect actually does not filter anything from a reporting standpoint). This Cartesian product select view is only for disabling the filters while making the jump. So, for the 2nd logical table source, create a select view in the physical layer using the sql below.

SELECT
B.LEVEL_1 LEVEL_1,
A.LEVEL_1 LEVEL_2,
A.LEVEL_2 LEVEL_3,
A.PRODUCT_ID PRODUCT_ID,
A.PRODUCT_NAME PRODUCT_NAME,
A.LEVEL_NUM LEVEL_NUM
FROM
(SELECT DISTINCT LEVEL_1 FROM PRODUCT_PARENT_CHILD_TBL1) B,
PRODUCT_PARENT_CHILD_TBL1 A

In the same way for the 3rd logical table source we would now need a Cartesian product of 2 tables, one with Level1, Level 2 columns and the other with the source of the first logical table source.

SELECT
B.LEVEL_1 LEVEL_1,
B.LEVEL_2 LEVEL_2,
A.LEVEL_1 LEVEL_3,
A.LEVEL_2 LEVEL_4,
A.PRODUCT_ID PRODUCT_ID,
A.PRODUCT_NAME PRODUCT_NAME,
A.LEVEL_NUM LEVEL_NUM
FROM
PRODUCT_PARENT_CHILD_TBL1 A,
(SELECT DISTINCT LEVEL_1, LEVEL_2 FROM PRODUCT_PARENT_CHILD_TBL1) B

Now make a physical complex join between these 2 aliases with the fact table. In the example above, i have stopped with 4 levels. Depending on your requirement and the depth of the hierarchy, the same procedure above can be repeated for more levels.

Once we have the physical sources ready, the next step is to design the Business Model Layer. As discussed above, we would have 3 logical table sources contributing to the product dimension. And each source would have a mapping as shown below

The ordering of the Logical table sources is very important. Generally whenever there are 2 or more dimensional logical table sources that contribute to the same set of columns, the first logical table source would chosen for querying(not always true – It is dependent on the measure as well). So, when we create a report containing LEVEL1 and a fact measure we would be hitting the parent child table (And not the Cartesian Product views). And the other important aspect to this is the filtering on Level Number. When we move from one logical table source to another, as we are negating the effect of the drill filter using Cartesian product, we would have to explicitly apply the level filters for each logical table source as shown below(For level 1 lts the filter would be equated for Number 1, level 2 to number 2 and so on.

Once this is done, just create the hierarchy as shown below.

This will ensure that our BMM is parent-child hierarchy aware for the product dimension. The drills would be seamless for an end user

There can be many more improvements to above technique. For example, instead of the Cartesian Product select view we could just have another table with repeating Level 1/ Level 2 values. But the idea would remain the same.