Oracle BI EE 10.1.3.4.1 – Puzzlers – Puzzle 1

If you had noticed the news last week, Oracle’s BI EE has achieved a leader status in the Gartner’s BI Magic Quadrant(i think 3rd year in a row). This can only mean one thing. More and more customers have started to implement BI EE and are happy with it as well. I have been planning to put together a small series of articles that are more like Repository Modeling puzzlers which should hopefully pique the interest of all the folks who are currently using BI EE. Repository Modeling is one of those areas that is least documented but it is the probably the biggest area that Oracle BI EE is extremely good at when compared with other reporting tools. And also it is sort of a subjective area where different people can have different techniques to achieve the same functionality. In the forthcoming articles (including this one), i would basically blog a small series of repository modeling puzzlers which everyone can participate in. All you have got to do is to, go through the blog entry, understand the question and come out with a RPD which can solve that particular use case. If you feel that the puzzle is very easy to achieve/solve, then you can  put your solution as a comment to the blog entry. For every puzzler blog, i will come out with a solution blog where i will give the credit to the folks who have come out with the correct solution for that particular use case. If you have a RPD to send, do send them to [email protected].

We are hoping to do a similar sort of Puzzler series (with an interesting twist & completely different set of questions :-)) in our BI Forum later this year. I will start with a very simple one today. But this is something that anyone rarely uses but that is extremely powerful. The puzzle is

“How do we make BI EE to generate different filters for every column(within a dimension) chosen from Answers?”

For example, lets take the SH Schema in the Oracle Database. Listed below are all the attributes of the CHANNEL dimension that have been exposed to Answers.

When someone creates a report containing CHANNEL_CLASS attribute and a measure, the SQL fired should automatically apply a filter on the CHANNEL_CLASS column. A sample SQL is shown below

select T1776.CHANNEL_CLASS as c1,
     sum(T1929.AMOUNT_SOLD) as c2,
     T1776.CHANNEL_CLASS_ID as c3
from
     CHANNELS T1776,
     SALES T1929
where  ( T1776.CHANNEL_CLASS = 'Puzzle1 Test' and T1776.CHANNEL_ID = T1929.CHANNEL_ID )
group by T1776.CHANNEL_CLASS, T1776.CHANNEL_CLASS_ID

If you notice in the above SQL, there is a filter T1776.CHANNEL_CLASS = ‘Puzzle1 Test’ which gets automatically applied (without any filter in the Answers).

Now, when someone removes CHANNEL_CLASS and replaces it with CHANNEL_DESC column, then the SQL fired should have a CHANNEL_DESC (not the older filter that we applied above) specific filter as shown below

select T1776.CHANNEL_DESC as c1,
     sum(T1929.AMOUNT_SOLD) as c2,
     T1776.CHANNEL_ID as c3
from
     CHANNELS T1776,
     SALES T1929
where  ( T1776.CHANNEL_DESC = 'Puzzle Test2' and T1776.CHANNEL_ID = T1929.CHANNEL_ID )
group by T1776.CHANNEL_DESC, T1776.CHANNEL_ID

If you notice, the above SQL has a CHANNEL_DESC specific filter. The question is how do you model your RPD to achieve this. Or if there is any other easy solution without going into modeling, feel free to put them in comments. This is a very simple puzzle(not exactly a puzzle but more of an in-built feature) but something that is extremely powerful as i had mentioned before. The next puzzle will be a little bit more challenging, i promise!!!