Filtering cube data

Filtering enables you to limit the amount of data that is retrieved from a cube.

About this task

The Filter page lists the dimensions defined for the cube. Each dimension of a cube has one or more attributes. By selecting a check box you can choose to include or exclude the attribute's or entire dimension's data from being retrieved from a cube.

To filter the data that is retrieved from an OLAP cube:

1. Double-click a cube in your workspace or open an existing OLAP query.
2. An OLAP query has six tabs that run across the bottom. Select the Filter tab. The Filter page opens. It includes the Filter tree and the Filter Type fields.
3. The Filter tree lists the dimensions of the cube, the hierarchy levels for each dimension, and the dimension values for each hierarchy level. Click the (+) to expand the dimension.
4. To select your filtering options, select the check boxes of the dimension values that you want included in the query. Each dimension, hierarchy level, and dimension value has a check box. You can expand and collapse the dimensions and hierarchies using the plus (+) and (-) boxes. You will select or clear the check boxes to include or exclude data beginning with the lowest level of the tree structure, the dimension values. If you select all values in a level, the higher level of the tree structure will be selected. If no values in a level are selected, the higher level of the tree structure will be cleared. If you select some of the values in a level, the higher level of the tree structure will be checked but with a grayed check box. You must select at least one value for each hierarchical level.
5. If the data cube resides on a multidimensional data source uses the SQL-based API, you can vary the organization of the tree depending on your selection of filter type.
   • Select Regular Filter to display a distinct list of dimension values by simple hierarchical level. All possible values are listed regardless of their hierarchy in the dimension. For example, if you have multiple quarters within multiple years, each dimension value would be organized as follows:

     2001
     2002
     Q1
     Q2

   • Select Hierarchical Filter to organize the dimension values and display them under each of the parent hierarchy levels where they could possibly appear. This type of listing is useful when you have instances of a given value that can be included in multiple hierarchical levels. For example if you have multiple quarters within multiple years, each dimension value would be organized as follows:

     2001
     Q1
     Q2
     2002
     Q1
     Q2

6. Click the Results tab to run the OLAP query. Only the values that you checked will be included in the results.
7. To change the filtering, clear the check box next to each dimension item that you were filtering out from the OLAP query. Click the Results tab. The OLAP query is rerun and results are returned including only the values that you selected.