Filtering Table data using "Request Variable" and Session varaible/OBIEE- Request Variable

When you want to filter the table data based upon the selected value in prompt

For example ,I have a table called SRC_ORDER_LINES it will show Sales amount and Order quantity based on order_id.My requirement is in dashboard i want to filter whole table data based on the VALUE(Order quantity) selected in Prompt

1) Create a session variable initialization block with following query

2) Next,variable creation and assign the above initialization block to this newly created variable

3) Double click on Variable qty,check in "Enable any user to set the value"

4) We need to add this session variable to the "BMM layer Content Level Filter" in order to filter the table


5) Double click on LTS(Logical Table Source) of SRC_ORDER_LINES click on content tab we need to give the condition under "Using where condition to filter out row counts"

6) Coming to answers create a prompt and declare a request variable with same name as session variable "Qty"

7) Add the prompt to dashboard and its respective report .when you select value of quantity as 1500 then the SRC_ORDER_LINES will limit the rows like where qty >1500,it will retrieves only those records

Below figure shows 

Quantity >1341 records

Quantity >1881 records

Dates, Timestamp and Oracle BI Answers Filters

I was working with a customer today who had brought in a new Oracle source into their OBIEE physical layer. The source had several tables with date columns that used the DATE datatype, and these tables were then used to create a simple dimensional model, like the one below.

Everything worked well, until they created a request with a filter on the date column. Remember that the column in Oracle is a DATE datatype, yet the filter added by Answers below adds a TIMESTAMP to the predicate.

Now the reason that this is an issue, is if you’ve got an index defined on the date column. If you let Answers generate the query as it is, you end up with SQL looking like this:

-------------------- Logical Request (before navigation):

RqList  distinct
    Items.Order ID as c1 GB,
    Items.Order Date as c2 GB
DetailFilter: Items.Order Date = TIMESTAMP '2007-04-12 00:00:00.000'
OrderBy: c1 asc, c2 asc

+++Administrator:480000:48000b:----2009/03/06 21:27:26

-------------------- Sending query to database named orcl2 (id: <<309826>>):

select distinct T6622.ORDID as c1,
     T6631.ORDERDATE as c2
from
     ITEMS T6622,
     ORDERS T6631
where  ( T6622.ORDID = T6631.ORDID and T6631.ORDERDATE = TIMESTAMP '2007-04-12 00:00:00' )
order by c1, c2

which will mean that your index won’t get used, unless you create a corresponding function-based index on the date column. It also screws up partition elimination as well, which can seriously impact the response time of your query.

The answer to this problem is to go back to the physical model in Oracle BI Administrator, and change the DATETIME datatype that OBIEE assigns to Oracle DATE datatypes by default, to an OBIEE DATE datatype, like this:

This altered datatype then propagates through to the business model and mapping layer, and then the presentation layer of the model. After restarting the Oracle BI Server just to be on the safe side, reloading the server metadata in Oracle BI Answers, I go to create the same report. To my surprise though, the filter prompt still adds a timestamp to my choice of date.

Cue several hours of fiddling with column formulas in Answers, wrapping the filter value in a CAST function and so on, all of which only partially solved the problem and usually ended up with either the predicate or the table column being wrapped in a function call, which again cancelled out the index. In the end, we just tried dropping the column from the business model and mapping layer, and then adding it back in again from the physical layer.

Now, after adding the new column to the presentation layer, saving the repository and then reloading the server metadata in Answers, when I create a new request in Answers the filter prompt just uses the date on its own.

Then, if I take a look at the SQL in the query log, we can see that the predicate is just a date. Checking the execution plan afterwards shows that the index is indeed being used.

-------------------- Logical Request (before navigation):

RqList  distinct
    Items.Order ID as c1 GB,
    Items.ORDERDATE as c2 GB
DetailFilter: Items.ORDERDATE = DATE '2007-04-12'
OrderBy: c1 asc, c2 asc

+++Administrator:300000:300005:----2009/03/06 21:46:19

-------------------- Sending query to database named orcl2 (id: <<12826>>):

select distinct T6622.ORDID as c1,
     T6631.ORDERDATE as c2
from
     ITEMS T6622,
     ORDERS T6631
where  ( T6622.ORDID = T6631.ORDID and T6631.ORDERDATE = TO_DATE('2007-04-12' , 'YYYY-MM-DD') )
order by c1, c2

So the moral of the story there is, if you’re bringing Oracle data in and it’s got DATE datatypes, make sure you correct the DATETIME datatypes that the import process assigns to them in the physical model, otherwise you’ll hit this same problem around superfluous TIMESTAMPs. If you’ve already built your logical model before you find this issue, make sure you delete and the re-add the date columns after you correct the physical model datatype, otherwise Answers will still go on using TIMESTAMPs even if you change the repository model and even refresh the Answers metadata. Simple once you know how, but it took us a good few hours to sort out.

The story didn’t end there though. The customer also set up a session variable that held, for each user, yesterday’s date. They populated this within an initialization block that selected against the SYSDATE pseudo-column, truncating it and taking 1 off to return yesterday’s date.

As you can see, again by default OBIEE assigns it a DATETIME datatype, which will cause the same problem as before if we try and use it in a filter. No amount of TO_DATE or CAST as DATE around the SQL would turn it into a plain old DATE datatype, so in the end we wrapped a TO_CHAR around it and converted it into the correct CHAR format for our database’s NLS_DATE settings, like this:

Now, if we include the session variable in a filter, like this:

The resulting SQL generated by the BI Server includes this CHAR version of the date directly in the query, which Oracle then implicitly converts to a date datatype because we’re using the correct date format for the database’s NLS_DATE setting.

-------------------- Logical Request (before navigation):

RqList  distinct
    Items.Order ID as c1 GB,
    Items.ORDERDATE as c2 GB
DetailFilter: Items.ORDERDATE = '06-MAR-2009'
OrderBy: c1 asc, c2 asc

+++Administrator:300000:300006:----2009/03/06 21:58:47

-------------------- Sending query to database named orcl2 (id: <<12934>>):

select distinct T6622.ORDID as c1,
     T6631.ORDERDATE as c2
from
     ITEMS T6622,
     ORDERS T6631
where  ( T6622.ORDID = T6631.ORDID and T6631.ORDERDATE = '06-MAR-2009' )
order by c1, c2

Checking the execution plan for this new query, it is indeed using the index we created. Now obviously, this is a “dangerous thing” as we’re making the assumption that our database, or more correctly database client software, will always keep using this same NLS_LANG setting, but given that I can’t see any other way of getting the BI Server to store the date session variable as a date rather than date and time, it’s a working solution that solved the immediate problem.

So, a day of fiddling around with dates, and thanks to John Minkjan, Venkat and Christian Berg who chipped in with some advice. If anyone else faces the same issue, hopefully this posting will be of use.

i

Combining Measure with Filter on an Unrelated Dimension

Sometimes we need to combine measures from two fact tables in the same report and apply a filter to one of them. If the filter is on a dimension shared by both fact tables; that’s great. However, if the dimension is only related to one of the facts, then we have a problem: Oracle BI would show null values for a measure that’s not related to the dimension.

Imagine that we have two fact tables: Revenue and Budget. Revenue is recorded for Product but also contains information about General Ledger (GL) Account. Budget is defined for Product but not for GL Account so there’s no relationship between them. Our business model is shown in the image below. Of course in a normal repository there would be more dimensions and facts but they have been left out to clarify the example.

Sample dimensions and facts

We can show Revenue and Budgeted Revenue in the same report without any issues (as shown in the screenshot below). OBIEE simply queries both fact tables. The product dimension is not a problem as it’s shared between Revenue and Budget facts.

Report without filters

But if we need to apply a filter on the GL Account we won’t be able to see any values for Budgeted Revenue. That’s because OBI recognizes that Budget fact is not related to GL Account dimension and therefore shows null values (empty cells) as in the screenshot below. You can see that Revenue has been filtered.

Budgeted Revenue with null values

Proposed Solution

To overcome this we should define the measures in the Budget fact on the grand total level in the GL Account dimension. We can do this in the OBI repository in the properties of a measure. In the properties of a column just go to the last tab Levels. Be aware that we’re changing the properties of the measure Budgeted Revenue – not the properties of the logical table source.

Have a look on the image below. We’re setting Budgeted Revenue measure on the grand total level in the dimension GL Account. If you think about it, defining a measure on the total level of a dimension is like saying the measure is not defined at all in that dimension. It does however make a difference in OBI because now this measure will show up even if GL Account dimension is included in the report.

Defining Budgeted Revenue measure on GL Account dimension

After the change we are able to see the Budgeted Revenue because we’ve defined it on the grand total level of GL Account dimension. The filter in the product dimension was never an issue since both facts are related to that dimension. See how Revenue now has a filter? Budgeted Revenue appears in the request but isn’t affected by the filter. Defining the measures on the grand total level solves the issue of having a filter on a dimension that’s not related to the measure. If you try this, let me know how it goes!

Final results of combining measure with filter on GL Account dimension

Before closing, I should mention that having an attribute from a dimension that’s not related doesn’t make that much sense. For example if we added GL Account Number in the report we’d see the same Budgeted Revenue repeated for each GL account number. It would also sum up incorrectly as you can see in the screenshot.

Report with GL Account Number added

This can be solved with aggregation based on dimensions – but clearly that deserves a separate blog entry!

Issue with Filters while Navigation

Sorry for not posting for a long time. I was stuck and was little busy with things. But have just taken out some time to write and share with the things i encountered.

Today, I came acros a wierd scenerio wherein the filters didnt worked properly when we navigate from one report to another on Dashboard.

I had a chart report on the Dashboard and it was having a navigation to another Pie report. Now when I tried to navigate to another report, what i found was the other report was not getting filtered properly. I tried running that report from the saved location but from there it ran properly with all the filters in place. I also checked in the destination report for IS PROMPTED filter. The filter was in place too. 

Then after analyzing the things i came to know that sometimes this happens in OBIEE and to tackle this we should Protect the filter. We need to make the filters Protected in the destination report to which the navigation will lead to. The filters other than IS PROMPTED should be Protected.

We can protect a filter by just clicking on the filter options and check the Protect Filter option as shown below.

One thing to note here. We can’t protect the filter group containing individual filters. We need to individually protect the filters in the filter group and then save it again.

I hope this can be helpful to you.

OBIEE Protect Filter – A precautionary step

The feature was already there from Siebel Analytics 7.9 versions or probably from its earlier releases . We need to take some extra precautions before applying this in request filters which can be adopted as best practice in report development .

This ensures that the filter used in the request is not lost or overwritten by another filter or dashboard prompt that may supersede the request.
This option is only available if a value has been specified in the filter. If the filter item is set to “is prompted” then the Protect Filter option if not available.

To apply it on the filter in the Answers Request, select Filter Options button > Protect Filter.  See below :

"in between" filters for MDX sources

Fiddling around with some more functional options we've all come to know, like and use frequently, I found that "in between" filters in answers requests going against MDX sources (Essbase, MS Analysis server etc) don't work as expected.

To showcase this, I have created a simple request. Planning and Current numbers by Fiscal Month within Europe. And I'm interested in all months between Q1 2009 and Q4 2009:




Checking the results tab we see that it's not necessarily what we'd expect:




Doing a full data scroll reveals that basically all Fiscal Month members are being pulled up.

Here's the MDX from the log:

With 
set [Geo3] as 'Filter([Geo].Generations(3).members, (([Geo].CurrentMember.MEMBER_ALIAS = "Europe" OR [Geo].CurrentMember.MEMBER_Name = "Europe")))'
set [Time Period3] as 'Filter([Time Period].Generations(3).members, ( NOT (([Time Period].CurrentMember.MEMBER_ALIAS < "Q1-09" OR [Time Period].CurrentMember.MEMBER_Name < "Q1-09"))) AND ( NOT (("Q4-09" < [Time Period].CurrentMember.MEMBER_ALIAS OR "Q4-09" < [Time Period].CurrentMember.MEMBER_Name))))'
set [Time Period4] as 'Generate({[Time Period3]}, Descendants([Time Period].currentmember, [Time Period].Generations(4),SELF), ALL)'
member [Scenario].[MS1] as 'AGGREGATE(crossjoin {[Geo3]},Scenario.[Current])', SOLVE_ORDER = 100
member [Scenario].[MS2] as 'AGGREGATE(crossjoin {[Geo3]},Scenario.[Planned])', SOLVE_ORDER = 100
select 
{ [Scenario].[MS1],
[Scenario].[MS2],
[Scenario].[MS3]
} on columns,
NON EMPTY {{[Time Period4]}} properties ANCESTOR_NAMES, GEN_NUMBER on rows 
from [Sample.Sample]


And here's the important bit:

set [Time Period3] as 'Filter([Time Period].Generations(3).members, ( NOT (([Time Period].CurrentMember.MEMBER_ALIAS < "Q1-09" OR [Time Period].CurrentMember.MEMBER_Name < "Q1-09"))) AND ( NOT (("Q4-09" < [Time Period].CurrentMember.MEMBER_ALIAS OR "Q4-09" < [Time Period].CurrentMember.MEMBER_Name))))'

I've opened an SR for this since there's no mentioning on metalink, the release notes or the new features guides.

Dynamic multi-level filtering with MDX

Following up on my Essbase ranking post and a question from OTN, I’d like to elaborate on the ranking and filtering of the rank results.

Again, we start with a basic report showing the top10 products by actual sales. We’re not using OBIEE functions due to the performance constraints mentioned in the first article.



The core MDX function retrieving the dimension members being:
EVALUATE('TOPCOUNT(%1.members,10,[Actual])',Product."Gen6,Product")



Business now wants to implement filters on Gen2 through Gen5 of the product hierarchy in order to be able to more specifically choose their top10 products. This normally means four “is prompted” filters and a dashboard prompt containing Gen2 through 5 as hierarchically constrained prompt column. Let’s keep it simple and try one of the generations as a fixed filter before creating the prompts and testing in a dashboard context.





As already stated, the grain of the query changes to the one of the filter and renders the whole request useless.

MDX without filter:
With
set [Account3] as 'Filter([Account].Generations(3).members, (([Account].CurrentMember.MEMBER_ALIAS = "Net Sales" OR [Account].CurrentMember.MEMBER_Name = "Net Sales")))'
set [Market3] as 'Filter([Market].Generations(3).members, (([Market].CurrentMember.MEMBER_ALIAS = "Europe" OR [Market].CurrentMember.MEMBER_Name = "Europe")))'
set [Time Period3] as 'Filter([Time Period].Generations(3).members, (([Time Period].CurrentMember.MEMBER_ALIAS = "Q4-09" OR [Time Period].CurrentMember.MEMBER_Name = "Q4-09")))'
set [Evaluate0] as '{TOPCOUNT([Product].Generations(6).members,10,[Actual]) }'
member [Scenario].[MS1] as 'AGGREGATE(crossjoin ({[Account3]},crossjoin ({[Market3]},{[Time Period3]})),Scenario.[Actual LY])', SOLVE_ORDER = 100
member [Scenario].[MS2] as 'AGGREGATE(crossjoin ({[Account3]},crossjoin ({[Market3]},{[Time Period3]})),Scenario.[Actual])', SOLVE_ORDER = 100
select
{ [Scenario].[MS1],
[Scenario].[MS2]
} on columns,
NON EMPTY {{[Evaluate0]}} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [MyCube_A.MyCube]

MDX with filter:
With
set [Account3] as 'Filter([Account].Generations(3).members, (([Account].CurrentMember.MEMBER_ALIAS = "Net Sales" OR [Account].CurrentMember.MEMBER_Name = "Net Sales")))'
set [Market3] as 'Filter([Market].Generations(3).members, (([Market].CurrentMember.MEMBER_ALIAS = "Europe" OR [Market].CurrentMember.MEMBER_Name = "Europe")))'
set [Product4] as 'Filter([Product].Generations(4).members, (([Product].CurrentMember.MEMBER_ALIAS = "SMU PF - Business Products" OR [Product].CurrentMember.MEMBER_Name = "SMU PF - Business Products")))'
set [Time Period3] as 'Filter([Time Period].Generations(3).members, (([Time Period].CurrentMember.MEMBER_ALIAS = "Q4-09" OR [Time Period].CurrentMember.MEMBER_Name = "Q4-09")))'
set [Evaluate0] as '{TOPCOUNT([Product].Generations(6).members,10,[Actual]) }'
member [Scenario].[MS1] as 'AGGREGATE(crossjoin ({[Account3]},crossjoin ({[Market3]},crossjoin ({[Product4]},{[Time Period3]}))),Scenario.[Actual LY])', SOLVE_ORDER = 100
member [Scenario].[MS2] as 'AGGREGATE(crossjoin ({[Account3]},crossjoin ({[Market3]},crossjoin ({[Product4]},{[Time Period3]}))),Scenario.[Actual])', SOLVE_ORDER = 100
select
{ [Scenario].[MS1],
[Scenario].[MS2]
} on columns,
NON EMPTY {{[Evaluate0]}} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [MyCube_A.MyCube]

Lets' forget about OBIEE filtering. This means that we have to do the filtering within the MDX and make that filter respond to the prompts. For this we will use presentation variables and a new MDX statement:



EVALUATE('TOPCOUNT(Intersect(%1.dimension.members,Descendants([@{vPromptedProdHier}{SMU}],[Product].[Gen6,Product])),10,[Actual])',Product."Gen6,Product")

And here's the prompt to go with it:



The multiple usage of the same presentation variable ensures that we will always have the lowest level currently chosen populated in the hierarchy, which will nicely adapt our MDX statement to constrain the results to the descendants of the chosen dimension member.

Family:



With
set [Account3] as 'Filter([Account].Generations(3).members, (([Account].CurrentMember.MEMBER_ALIAS = "Net Sales" OR [Account].CurrentMember.MEMBER_Name = "Net Sales")))'
set [Market3] as 'Filter([Market].Generations(3).members, (([Market].CurrentMember.MEMBER_ALIAS = "Europe" OR [Market].CurrentMember.MEMBER_Name = "Europe")))'
set [Time Period3] as 'Filter([Time Period].Generations(3).members, (([Time Period].CurrentMember.MEMBER_ALIAS = "Q4-09" OR [Time Period].CurrentMember.MEMBER_Name = "Q4-09")))'
set [Evaluate0] as '{TOPCOUNT(Intersect([Product].Generations(6).dimension.members,Descendants([SMU PF - Business Products],[Product].[Gen6,Product])),10,[Actual]) }'
member [Scenario].[MS1] as 'AGGREGATE(crossjoin ({[Account3]},crossjoin ({[Market3]},{[Time Period3]})),Scenario.[Actual LY])', SOLVE_ORDER = 100
member [Scenario].[MS2] as 'AGGREGATE(crossjoin ({[Account3]},crossjoin ({[Market3]},{[Time Period3]})),Scenario.[Actual])', SOLVE_ORDER = 100
select
{ [Scenario].[MS1],
[Scenario].[MS2]
} on columns,
NON EMPTY {{[Evaluate0]}} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [MyCube_A.MyCube]

Group:



With
set [Account3] as 'Filter([Account].Generations(3).members, (([Account].CurrentMember.MEMBER_ALIAS = "Net Sales" OR [Account].CurrentMember.MEMBER_Name = "Net Sales")))'
set [Market3] as 'Filter([Market].Generations(3).members, (([Market].CurrentMember.MEMBER_ALIAS = "Europe" OR [Market].CurrentMember.MEMBER_Name = "Europe")))'
set [Time Period3] as 'Filter([Time Period].Generations(3).members, (([Time Period].CurrentMember.MEMBER_ALIAS = "Q4-09" OR [Time Period].CurrentMember.MEMBER_Name = "Q4-09")))'
set [Evaluate0] as '{TOPCOUNT(Intersect([Product].Generations(6).dimension.members,Descendants([SMU PG - BP Video],[Product].[Gen6,Product])),10,[Actual]) }'
member [Scenario].[MS1] as 'AGGREGATE(crossjoin ({[Account3]},crossjoin ({[Market3]},{[Time Period3]})),Scenario.[Actual LY])', SOLVE_ORDER = 100
member [Scenario].[MS2] as 'AGGREGATE(crossjoin ({[Account3]},crossjoin ({[Market3]},{[Time Period3]})),Scenario.[Actual])', SOLVE_ORDER = 100
select
{ [Scenario].[MS1],
[Scenario].[MS2]
} on columns,
NON EMPTY {{[Evaluate0]}} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [MyCube_A.MyCube]

End of line.

Oracle BI EE 10.1.3.3/2 – Rolling Year,Month and Date filters – Moving Window Filters

I have received this question a couple of times before and this also came up in the forums today. Lets look at the question first (modified version of the original question to make it more clearer). “I have a dashboard prompt wherein the user can choose any year (using a dropdown). I also have a report which shows sales across all the years (containing a lot of historical data). Now, based on the year that was chosen in the dashboard prompt i would like to restrict the report to only the previous 4 years of the selected date“. Though this question is pretty straight forward, i thought i would blog about it to serve as a point of reference for future questions like this. Just to make it a bit more complex lets also make the number 4 above dynamic. Lets start first with creating a dashboard prompt on 2 columns. One prompt would extract the year from a date field and the other would just let the user enter some numeric data. Make both the columns to set presentation variables promptdate and promptnumber. The first column would have a formula to extract the year from the date.

YEAR(TIMES.CALENDAR_MONTH_START_DATE)

And the second one would just let the user to enter any value. So, in order to achieve that enter the below formula

CASE WHEN 1=0 THEN CHANNELS.TOTAL_NAME ELSE ‘Test’ END

      

Once this is done, lets create a simple report containing 2 columns. CALENDAR_MONTH_START_DATE and its corresponding SALES.

      

Now, create a filter on any of the columns and convert that filter to SQL.

      

Now, in the SQL box enter the below filter formula.

TIMES.CALENDAR_MONTH_START_DATE >= TIMESTAMPADD(SQL_TSI_YEAR, – @{promptnumber}{2}, TIMESTAMP ‘@{promptdate}{2003}-01-01 00:00:00′)

Similarly, create another filter and enter the below formula after converting it to SQL.

TIMES.CALENDAR_MONTH_START_DATE <= TIMESTAMP ‘@{promptdate}{2003}-12-31 00:00:00′

So, what the above filter does is it dynamically varies the window of the filter that is being applied.Notice that even this SQL box can accept presentation variables.The other important thing to note in the formula is the use of negative year values in TIMESTAMPADD function. This will move the year backwards.

      

Now, if you go to the dashboard prompt and enter 1 and 2006 in the prompts we would get a report containing the dates from 2005. Similarly, if you enter 2 and 2005, it would get a report containing dates from 2003 till 2005.

      

      

Again, very simple but can be useful in a lot of situations.

Oracle BI EE 10.1.3.3/2 – Presentation Variables in TOP filter

I got an email in one of our help lists today stating that Answers does not seem to accept presentation variables in TOP filter. If you are not sure what i mean, look at the screenshot below.

      

So, basically the user wanted to have a filter like “Sales is in TOP N” where the N would be populated by a presentation variable from a dashboard prompt. Lets look at a workaround today to achieve the same. Lets start with creating a simple report containing 3 columns Country, City and its corresponding Sales.

      

Now, lets create another column with the below formula

TOPN(SALES1.Sales,@{test}{5})

      

What this does is it creates a TOPN aggregation on the column. test is our presentation variable and 5 is the default value of the presentation variable.

Now, create a filter on the newly created column as shown below

      

So, basically your final filter will look like the one shown below

      

This filter is equivalent to the one that we tried first (where we got the error). Now create a dashboard prompt with a column formula as shown below

CASE WHEN 1=0 THEN GEOGRAPHY.COUNTRY_NAME ELSE ‘Test’ END

Make this prompt to set the presentation variable test

      

Now, you should be able to pass the presentation variables to the report.