We will next consider the Conditional Retrieval option in the Define Member dialog box, which gives you many choices in setting conditions on the data to be pulled into PowerOLAP®.
With OLAP Exchange®, you can set conditions on a field in the table from which you are pulling data for a Dimension—even if the field is not the one supplying Members for that Dimension. For example, suppose you used the Products table for the construction of the Products dimension in PowerOLAP®; and, specifically, you used ProductName as the field from which to pull the Member names. Using the Conditional Retrieval feature, you can require that the individual ProductName entries be pulled over as Members only if the entries in the SupplierID field contain an "E", signifying East Coast supplier.
The Conditional Retrieval feature is another example of how OLAP Exchange® leverages the structure of the source relational database, to give you specific choices in the construction of Dimensions and Cubes.
Let's consider some important points about Conditional Retrieval as they apply to the Measures dimension and to other Dimensions:
Notes
For the Measures dimension, Conditional Retrieval allows you to
write any SQL Where clause to determine which Fact Data to
pull into the Cube. Conditional Retrieval is also available in the creation
of other Dimensions; however, it will set conditions on which records
to exclude in the construction of the Member list, not which Fact
Data to exclude.
We will next demonstrate bringing over ProductName entries only if those Products are not discontinued. How can we even find out whether a product has been discontinued? First, we need to know that there is indeed a field in the Products table called "Discontinued". Our qualifier for including the ProductName entry as a Member is that the entry in the Discontinued field indicates, "no, this Product is not discontinued". We will construct our "conditional statement" on this basis, using features in the Conditional Retrieval option dialog box, as shown in the following steps.
To use Conditional Retrieval:
1. From the Define Members dialog
box, check the Conditional Retrieval check box option.
For this example, we need to access the Products dimension.
.png)
2. Click Next.
The Conditional Retrieval dialog box appears:
.png)
In the dialog box, all fields for the underlying table (Products) are listed on the left. An important drop-down menu is above this list, boxed in the figure. This menu will access numerical operators (the OPERATOR selection) and then the actual data entries for each of the fields in the table. Below is a detail figure of the selections from the drop-down menu encircled in the preceding figure.
.png)
3. Make a selection—e.g., Data for: ReorderLevel. The entries that appear in that field appear in a list below.
[Note that, logically, an entry value appears once, no matter how many times it appears in the field.]
.png)
4. Return to the default selection, Fields for: [Product].
Our objective is to construct a statement in the dialog box that translates into: "include the Member, which is ProductName, if the product is not discontinued." How to proceed? Logically, it would make sense to look at the table again—and we can do so via OLAP Exchange's Preview feature, which is conveniently accessible through the icon at the top center of the dialog box.
5. Click on the Preview button
.png)
.
6. By narrowing the width of the columns that aren't pertinent to our conditional statement, you will arrive at the following image, which shows that for each ProductName there is an entry in the Discontinued field—a "0" or a "1".
.png)
Assume that the "1" indicates that the product is in fact discontinued (again, this points up the importance of knowing your underlying database): this will help us construct the statement we want, to say "include the ProductName only if it equals '0'." This is shown in the following steps:
7. Close the Preview window.
8. Select Discontinued in the
Fields list on the left, then click on the Append selected item into
WHERE clause icon, .png)
,
the plus icon at the center of the dialog box.
9. From the drop-down menu, select OPERATOR.
10. Double-click on the equals sign [=] in the list box below.
11. From the drop-down menu, select Data for: Discontinued.
12. Double-click on "0" in the list box below. A detail image of how the dialog box looks is shown:
.png)
This completes the Conditional Retrieval statement we want to make.
Tip
User can also set the conditions manually by "typing in", the
conditions and operators in the right-hand pane.
13. Click Next.
14. Click Next through the successive dialog boxes to reach the Relational Data Summary message box. It will show the Conditional Retrieval statement you created:
.png)
15. Click Finish.