Creating a Filter

This feature allows you to include only a specific set of Members when building a Dimension. It acts much like a conditional retrieval, where you define rules or conditions to filter the data. This is especially useful when you want to focus your analysis on a subset of data—such as only active customers, recent dates, or products in a specific category—without modifying the underlying source.

1. Open the Property Grid:
In the Table window of a relational component (where you define your dimension structure), navigate to the Property Grid.

2. Access the Filter Option:
Find the Filter setting within the Property Grid. Click the ellipsis button ( … ) next to it to open the filter editor.

4. This is where you write or build your filter statement using a combination of:

The interface provides helpful icons and tools so you don’t have to manually type everything—making it more user-friendly and less error-prone.

5. Save the Filter Statement:
Once you’ve created your filter condition, click OK. This will return you to the Table window, where the filter statement you just created will now be visible in the Property Grid under the Filter setting.

Important

At the end of the process, the system adds sorting and filtering instructions to the end of the SQL query. The Sort Order, which corresponds to the ORDER BY clause, decides how the results are ordered (like alphabetically or by date), and the Filter, which corresponds to the WHEREclause, decides which results to include (like only items over $100). These instructions need to follow the correct format used in SQL, the language the database understands.

In this example, the relational data source is the sample table dim_Regions, which contains the columns, Region, Continent and World. The goal of this exercise is to create a filter that limits the Dimension to only include regions associated with North America—specifically Mexico, USA, and Canada, as shown in the Data tab.

The following settings apply to the Region dimension attributes:

1. Set the Dimension Members to come from the Region column.

2. Define the Dimension Hierarchy as follows:

The first level (root) is the World column.
The second level (children) is the Continent column, which includes North America and South America.
The third and lowest level (leaf members) is the Region column, which contains regions like Mexico, Colombia, Venezuela, Brazil, Argentina, USA, and Canada.

3. Finally, apply a filter so that only regions from the North America continent are included in the results.

To set up the Filter Statement:

1. Define the Dimension Members

In the Design tab, select the Region column, right-click, and choose Set As Dimension Member. The values in this column will act as the Leaf Level Members—the lowest level in the hierarchy.

2. Define the Hierarchy

a. Still in the Design tab, select the World column, right-click, and choose Define As Hierarchy Level, then select At Root.

b. Select the Continent column, right-click, and choose Define As Hierarchy Level, then select Sub-Level of World.

c. Olation will automatically assign the Region column as the lowest level in the hierarchy.

3. Set up the FILTER STATEMENT.

a. Navigate to the Property Grid within the Table window.

b. Find the Filter option and click the ellipsis button (...) located on the far right side of the grid.

c. This opens the Edit Filter dialog box where you create the filter statement.

To create the filter statement that pulls Members only from the North America:

a. Click on the Measure Columns icon to return a list of all the column names that exist within the source relational table. Select the appropriate column, which is Continent.

b. Create a space by pressing the space bar.

c. Click on the Equality Operators icon. The drop-down shows the different operators that can be used for writing the filter statement. Select the equal sign (=) as operator.

d. Press the space bar to create space.

e. Enter "North America" enclosed in single quotes. When defining specific column Members in a filter statement, make sure that column Members are spelled accurately.

f. Click the OK button.

Returning to the Property Grid, you’ll see the defined Dimension attributes listed: the Dimension Member (from the Region column), the Hierarchy (based on the World and Continent columns), and the Filter Statement (Continent = 'North America') are all shown.

g. Click the Save command on the Olation ribbon.

To verify that only members from the North America continent are included in the Dimension:

a. Navigate to the Database Explorer.

b. Expand the Dimensions section.

c. Right-click on the Region dimension.

d. Select View Hierarchy from the options.