Using OBIEE for Reporting Directly Against Operational Systems

[This is a guest blog from Steve Devine from Hitachi Consulting, who we've known for a while and who attended our BI Forum in Brighton last May. Steve, Adrian, Venkat and I were discussing the issue of reporting against non-star schema data models and in particular the issues around reporting against HR data, which seems to bring out all the issues around reporting against OLTP data sets. Steve kindly agreed to write up his question as a blog post, and would welcome feedback on the questions he poses. Does OBIEE represent a good solution for non-star schema reporting, and how else has anyone reported against HR data? - MR]

"Since Oracle's strategic direction for all ad-hoc reporting is OBIEE, I wanted to understand how feasible this is and ensure that our customers migrating from Discoverer would not also have to invest in building data marts or warehouses. Whilst researching this I found few blog resources on the subject suggesting that there is very little real world experience of implementing OBIEE against an OLTP schema. The OBIEE documentation does not include any case studies either.

So the purpose of this blog is to get some feedback in order to understand what OLTP modelling has been undertaken to date and to start to build a library of 3NF model patterns and their OBIEE implementations, something which I am more than happy to be the custodian of.

The broader question of how suitable OBIEE is for reporting against Operational Systems came out of a project that I've recently been working on which involved the migration of one of our Discoverer products to OBIEE. The real challenge has been how to create a Business Model against a 3NF OLTP database based on Oracle HR. Having since experimented with modelling more straight forward domains such as an ERP Order -> Despatch -> Invoice process it is clear to me that HR data has some characteristics that are not found in other domains, and are not easily modelled in OBIEE. HR data is unique for the following reasons:

• Full history is tracked in the operational system. For example, Oracle HR maintains a historical record of an Employee's addresses and their salary which they call Date Tracking. In BI terms every attribute is SCD Type 2
• It is predominantly verbose and textual in nature. There are no measures.

Here is a cut-down version of the physical model (the complete model contains seven more detail tables containing other Employee information such as Salary Payment Method, NI Class and Benefits etc).

A typical requirement would be to report current Employee details such as their job, address and salary for all Employees who currently live in London. We'll use this requirement to test each of the solution options later.

Against the physical schema the query would look like:

select employee.job, salary.salary, address.address
from employee,
salary,
address
where employee.employee_id = salary.employee_Id
and emplyoee.employee_id = address.employee_id
and CURRENT_DATE between employee.start_date and employee.end_date
and CURRENT_DATE between salary.start_date and salary.end_date
and CURRENT_DATE between address.start_date and address.end_date
and address.city = 'London'

As well as allowing the user to write a Request across multiple physical tables, we gave ourselves the objective of creating a Business Model so that they would only have to enter a single condition (filter) for the date they wish to report for (as the above query shows in SQL or Discoverer the user would have to enter one per table. In the actual application there are around ten detail tables and the Employee table has another three pairs of dates so this can get cumbersome!)

Option 1

The Business Model was created as follows

3. Employee contains a single attribute – the Employee Id which is the Employee PK.

4. Salary and Address contained the same attributes as their physical sources.

5. For each fact it is important to set the 'Logical Level' for each dimension in the source content, otherwise you may get Consistency warnings and certain queries will error.

Conformed Dimensions + Verbose fact attributes that are functionally dependant on a Conformed Dimension PK + Fact measures

For example, if you have a dimension whose PK is Order Id, Item Id, an Orders Fact with an Order Qty measure, a Despatches Fact with Despatch Status and a Despatch Qty measure and Despatch Status is functionally dependant on the Order PK i.e. One Order Item can have only one Despatch Status then it is perfectly valid to create a query with all of these attributes. OBIEE however will not query the Orders fact if Despatch Status is included in the Request.

I guess this all goes back to the point that OBIEE does not like you to include non measure attributes as facts. Will this change in 11g I wonder?

So the workaround I implemented is to trick OBIEE into thinking these verbose attributes are measures by applying the 'min' aggregate function (it could be any aggregate function that works with text). As long as you include the Employee PK (it is important that this is defined in the Employee dimension as described in 3 above) in each Request the results are correct because you are effectively aggregating only one record per Employee. OBIEE generates individual queries for each fact included in the query and uses an in memory stitch join to generate the final result set which is obviously not the most efficient method. Whilst this workaround produces the correct results, when you start to include these attributes in filters the SQL generated resolves the filter using a having clause which again is not the most efficient method i.e. if these attributes did not have an aggregate function defined against them a where clause would be the most efficient method.

Actual SQL:

select dim.Employee PK, min(Address) from Employee dim, Address fact
where dim.Employee PK = fact.Employee PK
group by Employee PK
having min(City) = 'London'

Preferred Performant SQL:

select dim.Employee PK, min(Address) from Employee dim, Address fact

where dim.Employee PK = fact.Employee PK

and City = 'London'

group by Employee PK

There is a further issue when you try and include the 'Start Date' or 'End Date' in the Request Criteria (rather than just as a filter). For example if you wanted to report the full salary history for each Employee. The Request Criteria is shown below:

What about if you only want to see the Salary periods? The only way to get this to work is to model the Employee Name in the Employee dimension instead of the dummy fact. Unfortunately this approach will cause the test requirement to generate invalid results as the Start/End Date filter will not be applied to the Employee data as we would not be including any attributes from the Employee dummy fact. It seems undesirable to include the Employee Name in both the dimension and dummy fact as this will be confusing to the user.

One more point about conformed dimensions. If you just create a Request with Start Date it will only return data from the first source defined for the dimension in the Business Model. This is a problem as ideally the results should contain the distinct set of values from all sources. I'm also currently having problems creating a multi level dimension hierarchy, but that is a WIP!

Option 2

This option is a useful reference as it demonstrates some OBIEE tricks and things you will certainly not find documented. I have Venkat to thank for outlining this solution (for those of you that don't know his blog it is always a good place to start when trying to understand anything OBIEE http://oraclebizint.wordpress.com and now http://www.rittmanmead.com/blog).

The Business Model was created as follows:

1. The Employee, Salary and Address physical tables were denormalised to create a single logical dimension called Employee. This was achieved by dragging the physical Employee table across to the Business Model and then mapping this source to the Salary and Address detail tables.

2. Because each Logical table must join to at least one other table, I created a dummy fact which contained a single measure that counted the Employee PK. I don't understand why OBIEE does not allow standalone tables in the Business Model, there are many reasons why you would want to present a standalone table to the user and having to create these dummy tables just clutters up the model.

3. Now the really clever bit. As you can see from point 1), the source is called 'Employee – Current'. That is because there are actually two sources defined for the Employee physical table, the other being called 'Employee – History'. You create this second source by simply copying and pasting the initial source. The difference between these sources is that 'Employee – Current' has a filter defined against it to only return the current information i.e. the filter includes each CURRENT_DATE predicate from the test requirement query in the opening section, whereas 'Employee – History' has no filter.

Two logical attributes are then created:

• 'Current Indicator' which is only mapped to the 'Employee – Current' source.

• 'History Indicator' which is only mapped to the 'Employee – History' source.

And what about our test requirement? The main benefit this solution has over the first is that because the verbose attributes are modelled as dimensions predicates are applied as where clauses rather than having clauses and are therefore more performant.

Also a single query is used to return the results which is again more efficient. This is as a result of mapping the sources as described in 1) above. This metadata definition ensures that OBIEE will query the source tables together using a single query rather than Option 1 which issues separate queries for each source.

The main disadvantage with this solution is that to report details at any point in time, the user must define a filter for each dataset they wish to include. One workaround for this would be to set a session variable from a Dashboard Prompt that is referenced in the filter for the 'Employee – History' source, but this would not be possible from a Request. However, having separate pairs of dates does allow the user to perform more flexible analysis as it gives them the capability to query the Employee data at different points in time. For example if they were trying to see if there was a correlation between people that attended a specific course in Q1 and whether the course contributed to them receiving an end of year pay rise.

I would of course be interested to hear from anyone that has other solutions or answers to any of the outstanding limitations and problems I've outlined in this blog."

[Steve can be contacted at [email protected], and you can leave a comment on this blog post - MR]