Sunday, 6 April 2014

Discussing Sophisticated Tricks with Oracle BI Answers

Last week we presented an article with title "Sophisticated Tricks with Oracle BI Answers" on the German user conference DOAG 2014 BI in Munich. Follow the link to get the English version of our slides. We discussed our point of view on self service BI with Oracle BI and introduced the following example for dynamic 3 month value comparison:

Shipped Quantity with fully dynamic filter criteria

Our solution is using only number presentation variables (see slides) and two short criteria columns which are easy to use and don't need any additional explanation. The same concept can be used on any other case. Thus, it is not restricted to date columns. Futhermore, it's in comparison to all other possible solutions easy to build when users request a month value as insert criteria (what they will do in such a case).

Some of you will say the same result will be returned by using Oracle BI TIMESTAMPADD function which requires a timestamp value as input. Yes, that's correct! From an usability point of view the prompt has to be as smart as the above prompt, e.g. only using one date:


Assuming the user will recognize and particularly accept the month of the day selected represents the calculation base value. Be aware that the design of the prompt has direct impact on the possibilities to apply filters on the corresponding report. Thus, we do not recommend to use the same prompt from the DOAG BI example, because your filter will get much more complex.

Value selection and filtering with Oracle BI TIMESTAMPADD follows the same logic as already shown in our first solution. Based on the date input you need to extract month and year separately. Using BETWEEN filters instead would require application of CAST or EVAL functions, because the user may insert any day value of a specific month. We don't recommend introducing these functions to end users as they suppose database know-how.

The current year block is defined as (see image "Oracle BI Filter Criteria"):
Oracle BI Filter Criteria
Month(@{Day})
Year(@{Day})
Month(TIMESTAMPADD(SQL_TSI_MONTH,-1,@{Day}))
Year(TIMESTAMPADD(SQL_TSI_MONTH,-1,@{Day}))
Month(TIMESTAMPADD(SQL_TSI_MONTH,-2,@{Day}))
Year(TIMESTAMPADD(SQL_TSI_MONTH,-2,@{Day}))


And the previous year block looks like:
Month(@{Day})
Year(@{Day})-1
Month(TIMESTAMPADD(SQL_TSI_MONTH,-13,@{Day}))
Year(TIMESTAMPADD(SQL_TSI_MONTH,-13,@{Day}))
Month(TIMESTAMPADD(SQL_TSI_MONTH,-14,@{Day}))
Year(TIMESTAMPADD(SQL_TSI_MONTH,-14,@{Day}))


Results of both queries are equal, but in our case the Oracle BI TIMESTAMPADD alternative isn't using a default for the Day presentation variable. Thus, when creating the report you cannot test it directly. You will need the prompt in your dashboard. Additionally, all charts have to be created before applying the filter criteria.

It is very tedious to insert defaults for date presentations variables. Therefore we didn't do it for this example, because we prefer the solution shown at DOAG BI. It's up to you which one is yours!

No comments:

Post a Comment