Oracle BI EE 10.1.3.3.3/2 – CPM Analytics and EPF – Handling Ragged Hierarchies in Relational Data Sources & Action Links
Posted by Venkatakrishnan J on June 23, 2008
The last couple of weeks have been really very hectic for me as i am currently leading a very interesting project for a client involving EPF, Essbase, BI EE, Informatica and HFR. Though the project details would never see the light of the day, there have been lots of key take aways both from a technology standpoint as well as from an architecture standpoint. As part of the project, i had an opportunity to interact with one of the PM’s for a product called as CPM Analytics. I am not sure how many of you have actually heard of this product. For those who have not heard about this product, it is very similar to the Analytic Applications (OBIA) like HR Analytics, SCM analytics etc. But the major difference between this and the other applications is that this is a pure reporting analytics i.e there is no warehouse or ETL part to this application. CPM analytics is nothing but a repository and a set of reports bundled by the Oracle Development team for direct reporting on Oracle Enterprise Performance Foundation. Now, why am i blogging about this? As i said earlier, i had a very fruitful discussion with the PM of this application and there were 3 key take aways from that meeting which i shall be sharing here. As you might probably know, EPF or Enterprise Performance Foundation provides an out of the box capability to consolidate financial as well as non-financial data from Oracle E-Business Suite. One of the major advantages of EPF is that it structures the data in such a fashion that it is very easy to report on. It structures the data in the form of dimensions and facts(though not a direct star since the grain can vary). Our discussion centered around 3 main topics. They were
1. Handling Ragged and value based hierarchies in EPF
2. Performance considerations
3. Drill through to Oracle GL using Action links
I will explain each of the above in detail.
Handling Ragged and Value based Hierarchies:
This was my first question to the PM since BI EE does not have the capability to handle value based hierarchies out of the box. Most of the hierarchies in EPF are value based (Parent-Child). So, in order to circumvent that drawback, the CPM analytics uses a two way flattening. Lets understand this in detail. For example, lets take a simple hierarchy as shown below
In EPF, this hierarchy would exist in the Parent-Child form as shown below
Now, in order to leverage this hierarchy in BI EE, the first step is to flatten the above hierarchy as shown below
Now, if you notice, though we have flattened the hierarchy, we still have raggedness in the hierarchy. Hence while making the join with the fact table, we would be losing out on all the values that are null in the lowermost level. In order to circumvent this, we would have to do additional flattening which will replicate the lowest child to all the levels (where it is null) as shown below
So, if you look at the CPM analytics repository, you would notice 20 levels for all the dimensions. The reason for having that is to handle the value based hierarchy and also to ensure that we are handling the raggedness in the hierarchy.
This is a very nice way of handling ragged hierarchies. But the major drawback with this approach is that you would have a lot of unnecessary duplicates when you drill down and also we need to ensure that the hierarchy depth does not cross the limit specified. Still a very handy approach.
The second part of our discussion was towards the performance of CPM analytics. EPF contains huge amounts of data even after consolidation from Oracle GL. So, one would have to ensure optimal performance for all the users irrespective of the kind of queries that they fire. In order to achieve that, CPM analytics uses 2 approaches
1. Enable caching for all queries – The usual caching approach. But the major drawback with this is that one cannot determine what kind of queries an end user will fire and hence it would not be possible to cache everything. Also, there is a physical limitation to caching.
2. Intelligent use of MV’s – This is an approach very unique to CPM analytics. In order to ensure optimal performance, they have created Materialized views for each dimension at its granularity. For example, Natural Accounts is one of the important dimensions in EPF. So, you would find a MV called as FEM_NAT_ACCTS_MV which will store the aggregated data at the natural account level. Also, in order to ensure that this MV is always used by the optimizer you would find the below statement being fired for all the sql’s.
ALTER SESSION SET QUERY_REWRITE_INTEGRITY = stale_tolerated
The above ensures that the optimizer would pick up the data from the MV even if the statistics are stale.
Drill through to Oracle GL using Action Links:
This is again one of the very unique features of CPM analytics. It provides out of the box capability to drill through to Oracle GL. Lets first try to understand what Action links are. Action Links, but for its name, are nothing but columns exposed as URL’s. There is nothing extra that one would have to do on the BI EE side to enable this. Action links enable end users to drill from a BI EE report to an Oracle GL form. Typically these are called as Action Links since the links are dynamic and they are obtained directly from Ebusiness Suite. In CPM analytics (the same concept was used in Fusion Intelligence before), the action links have been implemented using a simple sql shown below (just an example)
SELECT B.* ,OBJ.OBJECT_TYPE_CODE AS CREATED_BY_OBJECT_TYPE_CODE ,CASE WHEN ( OBJ.OBJECT_TYPE_CODE IN ('OGL_INTG_BAL_RULE') OR B.CREATED_BY_OBJECT_ID IN (1702,1703,1704,1705) ) THEN <b>FND_RUN_FUNCTION.GET_RUN_FUNCTION_URL ( CASE WHEN OBJ.OBJECT_TYPE_CODE IN ('OGL_INTG_BAL_RULE') THEN FND_FUNCTION.GET_FUNCTION_ID('FEM_INTG_DRILLDOWN') WHEN B.CREATED_BY_OBJECT_ID IN (1702,1703,1704,1705) THEN FND_FUNCTION.GET_FUNCTION_ID('FCH_DATASUB_DD_FORWARD') END ,FND_GLOBAL.RESP_APPL_ID ,FND_GLOBAL.RESP_ID ,FND_GLOBAL.SECURITY_GROUP_ID ,'&DatasetCode='||B.DATASET_CODE|| '&CalPeriodId='||B.CAL_PERIOD_ID|| '&LedgerId='||B.LEDGER_ID|| '&CurrencyCode='||B.CURRENCY_CODE|| -- '&CreationRowSequence='||B.CREATION_ROW_SEQUENCE|| DECODE(DATASET_ATTR.DIM_ATTRIBUTE_VARCHAR_MEMBER,NULL,NULL,'&DataTypeCode='||DATASET_ATTR.DIM_ATTRIBUTE_VARCHAR_MEMBER)|| DECODE(B.COMPANY_COST_CENTER_ORG_ID,NULL,NULL,'&CompanyCostCenterOrgId='||B.COMPANY_COST_CENTER_ORG_ID)|| DECODE(B.PRODUCT_ID,NULL,NULL,'&ProductId='||B.PRODUCT_ID)|| DECODE(B.LINE_ITEM_ID,NULL,NULL,'&LineItemId='||B.LINE_ITEM_ID)|| DECODE(B.NATURAL_ACCOUNT_ID,NULL,NULL,'&NaturalAccountId='||B.NATURAL_ACCOUNT_ID)|| DECODE(B.ENTITY_ID,NULL,NULL,'&EntityId='||B.ENTITY_ID)|| DECODE(B.FINANCIAL_ELEM_ID,NULL,NULL,'&FinancialElemId='||B.FINANCIAL_ELEM_ID)|| DECODE(B.CHANNEL_ID,NULL,NULL,'&ChannelId='||B.CHANNEL_ID)|| DECODE(B.CUSTOMER_ID,NULL,NULL,'&CustomerId='||B.CUSTOMER_ID)|| DECODE(B.PROJECT_ID,NULL,NULL,'&ProjectId='||B.PROJECT_ID)|| DECODE(B.INTERCOMPANY_ID,NULL,NULL,'&IntercompanyId='||B.INTERCOMPANY_ID)|| DECODE(B.TASK_ID,NULL,NULL,'&TaskId='||B.TASK_ID)|| DECODE(B.USER_DIM1_ID,NULL,NULL,'&UserDim1Id='||B.USER_DIM1_ID)|| DECODE(B.USER_DIM2_ID,NULL,NULL,'&UserDim2Id='||B.USER_DIM2_ID)|| DECODE(B.USER_DIM3_ID,NULL,NULL,'&UserDim3Id='||B.USER_DIM3_ID)|| DECODE(B.USER_DIM4_ID,NULL,NULL,'&UserDim4Id='||B.USER_DIM4_ID)|| DECODE(B.USER_DIM5_ID,NULL,NULL,'&UserDim5Id='||B.USER_DIM5_ID)|| DECODE(B.USER_DIM6_ID,NULL,NULL,'&UserDim6Id='||B.USER_DIM6_ID)|| DECODE(B.USER_DIM7_ID,NULL,NULL,'&UserDim7Id='||B.USER_DIM7_ID)|| DECODE(B.USER_DIM8_ID,NULL,NULL,'&UserDim8Id='||B.USER_DIM8_ID)|| DECODE(B.USER_DIM9_ID,NULL,NULL,'&UserDim9Id='||B.USER_DIM9_ID)|| DECODE(B.USER_DIM10_ID,NULL,NULL,'&UserDim10Id='||B.USER_DIM10_ID)|| '&PerformDrilldownQuery=Y&addBreadCrumb=Y&retainAM=Y' ) END AS ENTERED_BALANCE_URL </b>,(SELECT ENTITY_ATTR.DIM_ATTRIBUTE_VARCHAR_MEMBER FROM FEM_DIMENSIONS_B DIM ,FEM_DIM_ATTRIBUTES_VL ATTR ,FEM_DIM_ATTR_VERSIONS_VL VER ,FEM_ENTITIES_ATTR ENTITY_ATTR WHERE DIM.DIMENSION_VARCHAR_LABEL = 'ENTITY' AND ATTR.DIMENSION_ID = DIM.DIMENSION_ID AND ATTR.ATTRIBUTE_VARCHAR_LABEL = 'ENTITY_TYPE_CODE' AND VER.ATTRIBUTE_ID = ATTR.ATTRIBUTE_ID AND VER.DEFAULT_VERSION_FLAG = 'Y' AND ENTITY_ATTR.ATTRIBUTE_ID = ATTR.ATTRIBUTE_ID AND ENTITY_ATTR.VERSION_ID = VER.VERSION_ID AND ENTITY_ATTR.ENTITY_ID = B.ENTITY_ID AND ENTITY_ATTR.VALUE_SET_ID = B.ENTITY_VS_ID ) AS ENTITY_TYPE_CODE ,(SELECT COUNT(*) FROM FEM_CAL_PERIODS_HIER HIER WHERE HIER.HIERARCHY_OBJ_DEF_ID = LEDGER_ATTR.DIM_ATTRIBUTE_NUMERIC_MEMBER AND HIER.PARENT_ID = B.CAL_PERIOD_ID ) AS PARENT_CAL_PERIOD_COUNT FROM FEM_BALANCES_V B ,FEM_OBJECT_CATALOG_B OBJ ,FEM_DIMENSIONS_B DIM_LEDGER ,FEM_DIM_ATTRIBUTES_VL ATTR_LEDGER ,FEM_DIM_ATTR_VERSIONS_VL VER_LEDGER ,FEM_LEDGERS_ATTR LEDGER_ATTR ,FEM_DIMENSIONS_B DIM_DATASET ,FEM_DIM_ATTRIBUTES_VL ATTR_DATASET ,FEM_DIM_ATTR_VERSIONS_VL VER_DATASET ,FEM_DATASETS_ATTR DATASET_ATTR WHERE OBJ.OBJECT_ID = B.CREATED_BY_OBJECT_ID AND DIM_LEDGER.DIMENSION_VARCHAR_LABEL = 'LEDGER' AND ATTR_LEDGER.DIMENSION_ID = DIM_LEDGER.DIMENSION_ID AND ATTR_LEDGER.ATTRIBUTE_VARCHAR_LABEL = 'CAL_PERIOD_HIER_OBJ_DEF_ID' AND VER_LEDGER.ATTRIBUTE_ID = ATTR_LEDGER.ATTRIBUTE_ID AND VER_LEDGER.DEFAULT_VERSION_FLAG = 'Y' AND LEDGER_ATTR.ATTRIBUTE_ID = ATTR_LEDGER.ATTRIBUTE_ID AND LEDGER_ATTR.VERSION_ID = VER_LEDGER.VERSION_ID AND LEDGER_ATTR.LEDGER_ID = B.LEDGER_ID AND DIM_DATASET.DIMENSION_VARCHAR_LABEL = 'DATASET' AND ATTR_DATASET.DIMENSION_ID = DIM_DATASET.DIMENSION_ID AND ATTR_DATASET.ATTRIBUTE_VARCHAR_LABEL = 'DATASET_BALANCE_TYPE_CODE' AND VER_DATASET.ATTRIBUTE_ID = ATTR_DATASET.ATTRIBUTE_ID AND VER_DATASET.DEFAULT_VERSION_FLAG = 'Y' AND DATASET_ATTR.ATTRIBUTE_ID = ATTR_DATASET.ATTRIBUTE_ID AND DATASET_ATTR.VERSION_ID = VER_DATASET.VERSION_ID AND DATASET_ATTR.DATASET_CODE = B.DATASET_CODE
As you see above in the sample sql, the part highlighted in bold would provide us the URL. So, typically when we are in an Answers report the idea is to pick up that column and make it as a URL. Hence, whenever someone clicks on that link it will automatically take them to the GL page by passing the appropriate parameters. The above was just to give an idea. There can be lots of different pages in EBS that we can redirect to and hence it is pretty tedious and complex to implement. But on the whole the idea is the same. But the major drawback with this approach is that this has a specific granularity. So, whenever someone creates a report with this link, it will open up a report at the URL grain rather than the report grain. This is something one would have to be aware of while implementing action links.
On the whole, the 3 approaches above can be re-used in relevant implementations.