Oracle Business Intelligence 11g - How To Limit Data In Your Analyses? Part I

There are 2 ways to limit the data in your analyses. 

1- Using Filters: This type of limiting data limits data for a column prior to aggregation.

2- Using Selections: This limits data after aggregation. There are 2 types of selections: 

a- Using selections based on attribute columns: This limits members after aggregation doesn't effect the row gr totals.

b- Using selections based on hierarchical columns to limit members. Effects row gr totals but doesn't effect the total members aggregation.

USING FILTERS

Creating filters is same as in the previous version of OBI 10g. You can create filters in the analyses save the filters as named filters in order to access to filter definition use that filter in another analyses. You can also use "Grouping" option of filters if you've added more , 1 filter by using AND/OR operators.  Another information about the filters is using the variables to implement dynamic filters. Dynamic filters can be based 3 types of variables:

-Session variables

-Repository variables

-Presentation variables

Another option about the filters is using a Saved Analysis as a filter. You can use any saved analysis that returns a column of values to filter a selected column in the originating analysis.

Converting an existing filter to SQL is a standard feature in order to eliminate the rows by writing SQL condition.

I'm going to demonstrate these filter types also create analysis prompts. 

USING SELECTIONS

Selections are used to limit the members after the aggregation. Members are assembled from selection steps with the same order which is in the selection pane in the analysis. Another good feature of the selections is using groups. Groups are used grouping members aggregating the result based on the list of members in the groups. 

Here are some demonstrations about limiting data by using filters selections.

First one will be adding a simple filter to the analysis. I create a simple report with 3 filters. You will see that you can easily use “Grouping” feature. 2 logical operators (AND/OR) are supported.

The second example is creating filter based on another analysis. I create an analysis filtered data by year column. The column stores only 2009 2010 values. , I create another report a filter to use the result set of another report based on “Year” column.

The third example is based on a filter that includes a variable. There are 3 kinds of  variables:

• Presentation Variable: Can be declared in the analysis.
• Repository Variable: This should be created in the repository. repository variables are initiated when the service is started can be accessed until the service is down.
• Session Variables: It’s also created declared in the repository. The difference is its lifetime. It’s instances declared when the session is opened, stays active until the session is closed. 

If you want to gain benefit of variables, you’ll have to get the variable list by accessing the repository.  In my example, I use a repository variable as  filter. When the variables value is refreshed, the report will be effected. 

The last example is converting the filter to SQL in order to edit criteria by using SQL. You achieve this by selecting the checkbox “Convert this filter to SQL”.