Filter based on Analysis' time level

Question

Hello,

I have the following Time dim hierarchy in the RPD:

Total

Year

Week

Date

If I create an analysis displaying Sales (fact) by Week, how can I detect in the back end that my report is ran at the Week Hierarchy Level? I want to be able to filter on my logical table in the RPD depending on the level the user is querying in OBIEE.

Thanks

Joao

Gianni Ceresa · Answer

Ok, so as you say you just don't want to filter your analysis the way it's designed to be and want to add a filter in your LTS instead.

Well ... strange requirement

As you say you have many fact tables why don't you add the filters by default to all of them? Then by setting the content level on your LTS you get what you look for: for a sales by week OBIEE will hit the fact table by week (which is filtered at the week level), if the user choose a sales and year column OBIEE will hit the fact table by year etc.

So correct content level settings on your LTS will manage it by itself.

PS: did you consider the option of creating 3 saved filters? One for Date, one for Week and one for Year? Your users building an analysis all the need to do is to add the saved filter to their analysis to get the right result (and any use able to add a sales column and a week column will understand he need to use the week filter).

2981486 · Answer

Yes, let me clarify.

If the user creates a report Sales by Week, in the RPD I want to limit the result set using a WHERE statement in the LTS fact table by Week, aggregated at Week Level < sysdate.

If the user creates a report Sales by Year, in the RPD I want to limit the result set using a WHERE statement in the LTS fact table by Year, aggregated at Week Level < sysdate.

Notice that the analysis has no filter in the OBIEE criteria tab. I want to dynamically filter my data in the back end depending on what level (year, day, quarter, etc) the user selects in his analysis.

Joao

Gianni Ceresa · Answer

Hi,

Not really sure to know what you are asking...

If your analysis is sales by week there is nothing to detect as your analysis is at the week level, so you know it because you built it like that and you add the good filter to be at the week level...

What kind of filter do you want to do in the RPD?