Oracle BI EE 11g – Parent-Child Hierarchies – Differing Aggregations
Mark has already explained the new Parent-Child hierarchy new feature in 11g here. Parent Child hierarchies are different from normal level based hierarchies not only in the nature of their structure but also in the way aggregations are done. For example, in a level based hierarchy, the most common type of aggregation that is done involves rolling up the numbers from the lower most level. But in the case of parent-child hierarchy, an aggregation by default enforces the roll-up across all descendant members. To understand this lets try to take this through an example.
Lets consider a sales department with the employee hierarchy shown below
Its a simple parent child employee hierarchy found in the oracle HR schema. Now the requirement for us is to model the Salary attribute of an employee in 3 ways
1. As an attribute – Show the salary of each employee as an attribute in the parent child hierarchy.
2. As a measure (All intermediary Roll-ups) – Roll-up the salary figures of all the employees who roll into the employee. For example, Scott King will have a Salary equal to sum of salaries of all the employees who report to him, including the ones who are his direct & in-direct reporting employees.
3. As a measure (Level-0 Roll-up) – Roll-up the salary figures from the lower most level till the top node For example, in the above figure, Scott King will have a Salary Roll-up figure of all the employees who are the lowermost level (not including the intermediary nodes – very similar to normal level based hierarchy roll-ups)
The above requirement is quite straightforward, but to achieve similar such aggregations in a normal level-based hierarchy, we will have introduce fragmentation & other techniques. But with parent-child hierarchy modeling now available in BI EE, all the above 3 requirements can quite easily be achieved.
Following is the employees parent child table that stores all the attributes of an employee
We start off with modeling this as a normal dimension table. As shown in Mark’s blog entry, we then create the Parent-Child table that stores all the relationships within the employee hierarchy (if you look at the script used in creating this, it will be containing a Connect By statement that will connect a root hierarchical node to every member that is part of its hierarchy).
Once this is created, we start with including the Salary as an attribute within the Employee dimension itself
This will serve our first requirement i.e show salary as an attribute of every employee. Now, to model the 2nd requirement i.e. to model salary as a measure and then make it to rollup from all the intermediary hierarchical members, we start with defining an alias on the main Employee table. Then join this table to the custom parent child employee table. The join structure of all the 3 tables is given below
We would then be using the salary column from the new employee alias table in our logical fact table as shown below
Lets now look at the report now and see what BI EE generates.
As you see, BI EE has generated the correct results with the roll-up of intermediary members as well. This is something that is quite difficult to model in BI EE 10g. If we look at the query generated (just the key one that generates the measure as BI EE now generates lots of intermediary sub-queries to support contextual drilling) you would notice that the default Parent-Child behavior is to achieve a roll-up across a hierarchy
To achieve the 3rd requirement i.e. to model a level based hierarchy kind of rollups from level-0, we create another alias in the physical layer and then model it the same way as the 2nd requirement. In the fact logical table, we then create a new measure that will map to the new physical alias as shown below
To ensure that we roll-up only from level-0 members, we then apply a filter in the fact logical table source as shown below
Lets now take a look at what BI EE generates
As you see, BI EE now generates the roll-ups only from level-0 members as against every descendant. We can have even more granular roll-ups based on custom defined attributes.
The upgrade assistant can be used to upgrade the scheduler schema, BI Publisher as well as the RPD & Web Catalogs. Lets start with the first option, i.e. upgrading RPD and Web Catalogs
In the upgrade screen, choose the 10g RPD along with the Web Catalog Path. In the Deliveries folder, choose the 10g deliveries path ({OracleBIData}/web/catalog/deliveries
In the next screen, provide the details of the weblogic server on which we will be deploying the Repository and Web Catalog to.
The upgrade utility does not seem to support remote deployments of the RPD and Web Catalog i.e. Upgrade Utility will only deploy the repository and web catalog on the machine containing the weblogic admin server.
This is just a utility. We cannot expect all the reports to start working out of the box immediately after an upgrade. Good amount of testing is required to make sure all the older queries of the most commonly used reports are working without any major issues. Also, not all the reports would get migrated seamlessly due to the significant changes across the versions which the utility cannot recognize. Care has to be taken to make sure that all the warnings logged as part of the upgrade process are properly analyzed and corrected manually. A sample upgrade log is shown below (containing 4 warnings)
Then edit the column properties of the SQL_FULLTEXT column and increase the size to 100000 (or higher) so that BI EE can display the values from CLOB.
Start with modeling a dimension and a fact in the BMM layer as shown below
Then create another logical table (same as the dimension) containing SQL_ID as the primary key. The idea is to use this primary key and show the SQL_FULLTEXT using a DENSE lookup. Mark the new logical table as a lookup table.
In the dimension logical table create a new column and use the following formula to derive the SQL_FULLTEXT as a lookup column. This will always push a separate SQL without DISTINCT/GROUP BYs on the SQL_FULLTEXT column.
Lets now include this new column in the presentation layer and use it for reporting.
As you see, we should now be able to see the CLOB data without any restrictions. If we look at the query generated, you can basically see 2 separate SQL queries, one with the group-by and the other with just the lookup.
RSS
Email