The Define Order Option in the Define Member dialog box also allows you to construct a statement that will determine how Detail Members are to be arranged, according to the Ascending or Descending order of a field from the table from which the Dimension is built. [Note that Aggregate Members are not reordered by this feature.] As with the Conditional Retrieval feature, the "dependent" field may be any field from the table.
We will take the Product dimension once again as an example, and create an order preference statement that lists Detail Members in descending order. Recall first, though, that earlier in our demonstration we used two Member fields—ProductID followed by ProductName—to construct the Dimension. Members, therefore, appear as a concatenation of these two fields. Let's now proceed to see results of an order preference in descending order according to ProductID (i.e, with the highest number first, then listed downward in numerical order).
To define Order:
1. From the Define Members dialog
box, check the Define Order check box option.
For this example, we need to access the Products dimension.
.png)
2. Click Next until you reach the Order Preference dialog box:
.png)
The way to proceed is to first pick the Field from the underlying table by which you want to make the order preference. Note that the familiar Preview feature is available in this dialog box, via the icon at the center.
3. Click-select ProductID in the Fields list on the left.
4. Click on the Appended selected
item to ORDER BY clause icon (the right arrow button in the center
of the dialog) or
double-click on the Field selected. Either of the two actions will bring
the ProductID to the right-hand pane.
5. Using the drop-down menu at top left, select Ascending/Descending operators, which will then appear in the list box below:
.png)
6. Double-click on DESC or type in DESC after the ProductID. (Make sure to put spaces in between words when manually typing in the operators.)
The Order Preference dialog box will appear as in the detail figure below:
.png)
7. Click Next until you reach the Relational Dimension Summary message box, which indicates your Order Preference choices:
.png)
8. Click Finish.
9. Click OK in the message box that reports updated points.
Seeing the results in PowerOLAP®
By accessing the Products dimension dialog box, you can confirm this order preference of Detail Members in the Hierarchy, in descending order (descending numerical order, according to ProductID):
.png)
Likewise, in a new Slice, the Products Detail Members will appear in descending order, across columns, as in the following figure. (Also note that if Partial Field Member name is enabled, as demonstrated earlier, you will see the 15-character length of the Customers Members.)
[We have noted that Detail Members only are affected by this Order Preference feature.]
.png)
Important
the last two examples (Conditional Retrieval and Order Preference) involved
changes to the Products dimension. Even so, the Rollup we created,
Best Mexican Restaurants—in
the Customers dimension—and
then put into the Hierarchy for the country Mexico, was removed
from that Hierarchy. Why? Because Modifying Relational Dimension actually
rebuilds the Cube. So, the important lesson is that when you rebuild one
Dimension, it may have an effect on others as well.
The ramifications of Modify Relational Dimension are discussed next. Note that some of the concepts in the matrix that follows are introduced in the next section, Process Cube (Rebuild Cube)– (for example, Mark Member as Persistent—i.e., "Persist"). In that section you will consider other circumstances when you revise—actually, update—a Cube after it has already been built.
Please see the following topics: