As a purist I would like every reporting database to have a perfect star, with the Fact tables containing all the relevant dimensions for reporting.  I would also like to see stars that contain everything you need for your report.

However, as a realist I am seeing many implementations of OBIEE / Siebel Analytics that have not set up as I want.  Short of re-writing the entire system from ETL to Reports I often have to find ways around ‘issues’.

There are three main issues that keep occurring:
1. Fields not available in the Subject Area
2. Dimension Attributes causing reporting problems.
3. Dimensional Facts causing reporting problems.

The first one is simple to explain, you need to report on Customers who have Call Activity, but you want to see this in your report from the Finance Star.

The second may need explaining, unless you are faced with this issue right now!

You have a list of Doctors, with Facts relating to call Activity.  Now you have a variable set of attributes which may be applicable to some of the doctors, such as, “Has Grey Hair - Yes”, “Private Patients - 6″, etc.  These can be listed in your application as ‘Specialities’, or ‘Categories’ or simply ‘Attributes’.

Now, if you want to report Doctor based numeric facts, but include some of the attributes in the filter, there is a likelyhood that the facts can be double or triple counted.

The third problem can appear in many ways, but my main example is:

“Give me a list of Hospitals where the Doctors there have prescribed Product A more than 6 times.”

Another example is:

“Give me all the customers that buy high value products”.

The solution to all of these issue could be the use of a sub query.

I’ll build a very simple example to show you how a sub query works, using the Sample Sales Subject Area.

Example is:

Give me a list of all Customers that have ordered High Value Products in the last three months.

This is made up of three queries.

A.  list of Customers
B.  list of High Value Products
C.  list of recent sales

I will start with B - List of High Value Products

This is a very simple design, and I have used a filter on the value so we can change the Value threshold.

Now for C. a list of sales.

Another simple list report, but this time I have added a filter, which is an advanced filter based upon another request

For the final report I have just added a filter to base the customers on those from report C.

and the final result on theh dashboard with a prompt is:

and you can see changes to the prompts too

Word of Warning - There are limitations to the number of elements in an IN CLAUSE.  Check what it is for your environment.

Note to the wise.
I have seen some recommendations that you should denormalise the attributes in the warehouse, DO NOT DO THIS.  Any new attribute will take months to implement in the reporting.  If your consultancy recommends this they are lining work for years to come! Come to us and we’ll show you how to make your reports without big Db changes.

As a footnote to this.  In the recent BI Forum we were shown a reporting system based upon a fully relational database at the table level.  These tables were then used in Materialised queries to create ’stars’.  These materialised queries can then be used in the physical layer in the rpd.  I actually think this approach looks good from a flexibility approach (you can add into you star very quickly).  I suspect that for most companies using Oracle that approach would be suitable, but for others, particularly where the fact tables grow to more than 100 million records it may not sustainable.  I look forward to an update at next years Forum.