Defining Member Options

Defining Member Options

We can now explore the options available in the Define Member dialog, which appear within the dialog as follows:

definememberdialogoptions.png

Figure 1.    Define Member Dialog Options

If you are using these options in the initial creation of a cube—rather than in the process of Modifying a Relational Dimension—you should ignore the instructions to first access the Modify Relational Dimension feature. In other words, go to Enabling duplicates.

To define member options:

  1. From the Data ribbon's Xchange Group, click Modify Relational Dimension.

  2. Dismiss the messages by clicking OK.

  3. From the Modify Dimension from Relational Source dialog, select Customers and then click OK.

  4. Click OK in the message box to proceed with modifying the dimension.

Enabling duplicates

By selecting Allow Duplicates, you ensure that Xchange will permit cube-building to proceed when it detects two identical Member values in a table. Typically, this would occur when you are building a Dimension other than Measures from your Measures table.

On the other hand, keep in mind that tables such as Product Master or Customer Master used as the basis for building Dimensions should contain unique names—e.g., there should not be two different products with the same name in a Product Master table. If this occurs, you can concatenate the Product Code with Product Name to differentiate the two. Otherwise, if Allow Duplicates is used, this will create a single product for which data for both products would be aggregated.

Let’s consider an example of when you might want to use Allow Duplicates. In the Measures table you might find a field that records the time when transactions occur—suppose month is the only thing specified. It is very likely that more than one transaction will occur per month; thus, if you are building a Months dimension from this table you must account for the fact that January, for example, will be “duplicated” in the field. By selecting Allow Duplicates, you ensure that you can proceed with the creation of a Monthsdimension, where each month will be listed as a Member.

By deselecting Allow Duplicates in this case, you will not be allowed to proceed with cube creation, and in fact you will receive a message like the one following (where the Table value ALFKI appears more than once).

duplicatetablesmessage.png

Figure 2.    Multiple Tables Message

Some features of Xchange, including the Allow Duplicates option (provided it is deselected) will clue you in to issues and anomalies in your underlying database. For more information, refer to Preparing Relational Data for Use with Xchange and Error File Reporting.

Defining rollups/drilling through with rollups

The Define Rollups option allows you to create your own self-defined roll-up Member(s) that aggregates values from Members in the underlying database so that they appear in proCube under a new Rollup Member name.

A Rollup is entirely different from a Hierarchy. Hierarchies result from fields in Table records. A Rollup is a detail member that a user creates, and which may (or may not) be placed into a Hierarchy within proCube. This is demonstrated in the following steps.

We will proceed with a demonstration of this option using the sample Cube, involving the Customers dimension:

  1. To enable the Define Rollups option, select Define Rollups.

  1. Click Next.

  2. Click Next in the Define Aliases dialog.

  3. Click Next in the Define Key Alias Group dialog.

  4. Click Next in the Define Properties dialog .

  5. Click Next in the Define Hierarchy dialog . The Define Rollups dialog box opens:

definerollupsdialog.png

Figure 3.    Define Rollups Dialog

All the Members of the Customers dimension are listed in the left-hand pane. At the top right, in the Rollups text box, you will enter a name for your own defined Rollup, then Group the Members you want within the Rollup in the list box below. We will demonstrate this with an example. First, though, recall that the Customers hierarchy for the country Mexicopresently is constructed as follows:

customershierarchymexico.png

Figure 4.    Customer Hierarchy - Mexico

We will now define a Rollup named BestMexico Restaurants that includes the Members Antonio Moreno Taqueria and Tortuga Restaurante, which are the second and last Members in the Hierarchy, shown in the figure above:

  1. In the Define Rollups dialog's Rollups text field, enter BestMexico Restaurants.

  2. Click Add. The Rollup name (BestMexico Restaurants) now appears with the Rollup icon beside it.

  3. From the list of Members on the left select Antonio Moreno Taqueria.

  4. Select the BestMexico Restaurants rollup on the right, then click the Add field to rollup icon in the middle of the dialog.

  5. Scroll down the list of Members and select Tortuga Restaurante.

  6. Since BestMexico Restaurants is already selected as the Rollup, click on the Add field to rollup icon. The right-hand side of the Define Rollups dialog appears as follows:

bestmexicorestaurantsrollup.png

Figure 5.    BestMexico Restaurants Rollup

  1. Click Next to proceed to the Relational Dimension Summary message. Note that at the bottom there is information about the Rollup you just created:

reldimsummarybestmexicorestaurantsrollup.png

Figure 6.    Relational Dimension Summary - BestMexico Restaurant  Rollup

  1. Click Finish to rebuild the Customers dimension with the new Rollup.

  2. Click OK in the message box that reports updated points.

  1. Return to the ‘Customers’ Hierarchy dialog and expand the Hierarchy for Mexico.

customershierarchybestmexicorestaurants.png

Figure 7.    'Customers' Hierarchy Dialog

There are several important things to note here: first,the members Antonio Moreno Taqueria and Tortuga Restaurante are no longer included in the Hierarchy; nor is the Rollup you just created. In fact, the two Members are no longer listed on the left-side Member list at all—they are “rolled up” into the Member BestMexico Restaurants. The new Rollup Member BestMexico Restaurants is listed as a Detail member (indicated by the pound sign) at the top of the left-hand pane.

Assuming you want to add the new Rollup Member to the Mexico hierarchy, you must do so within this Hierarchy dialog. Why is this the case? The reason is, when you create a Rollup, you might conceivably use Members from several different hierarchies. For example, you could have added a third Member, Berglunds snabbkop—from the Swedenhierarchy—to the BestMexico Restaurants Rollup. It is not obvious which Hierarchy the Rollup to belongs to—and, in fact, you might not want it included in a Hierarchy at all. That is, you might want the new Rollup to exist on its own, outside defined hierarchies, but still, theoretically, within the overall aggregation for the Dimension, Total Customers.

For present demonstration purposes, put the Rollup BestMexico Restaurants into the Mexico hierarchy:

  1. In the ‘Customer’ Hierarchy dialog, select BestMexico Restaurants from the Members list box on the left and drag it just above Ana Trujilo Emparedados y helados into theMexico hierarchy on the right, so that it appears as follows:

mexicohierarchywithbestmexicorestaurants.png

Figure 8.    Mexico Hierarchy - BestMexico Restaurants

  1. Click OK (green check mark) in the Hierarchy dialog, then OK in the Dimensions dialog.

  2. In the Server View, right-click the SalesOrder cube and from the right-click menu, select Browse to open a slice.

  3. Press F9 to populate the slice.

The new Rollup Member, BestMexico Restaurants, appears first in the Product row. The Quantity of 17Alice Mutton ordered is 36.

  1. Scroll down the rows to Mexico, then right-click on the cell at the intersection of Mexico and 17Alice Mutton.

  2. From the menu, click Drill Through to open the Drill Through Dialog.

  3. Select Drill Through to Relational Source (proCube automatically selects Express Drill Through)

  4. Select Show Summed Relational Values so that the Drill Through Dialog appears as follows:

drillthroughdialogmexico17alicemuttondrillthrough.png

Figure 9.    Drill Through Dialog - Top Three Options Selected

  1. Click OK. The following slice Sales Order: Drill Through opens:

salesorderdrillthroughslice17alicemutton.png

Figure 10.           Drill Through Slice - Mexico and 17 Alice Mutton

You can see that the Drill Through feature will, as the name implies, “drill through” to display Rollup-derived data in the underlying relational source database. In other words, both the data for Antonio Moreno Taqueria (one entry, value 18.0) and for Tortuga Restaurante (2 entries, values 10.0 and 8.0)) are shown, as is the Rollup value (for BestMexico Restaurant, value 36.0) within the Hierarchy for Mexico.

Keep in mind that if you are using a Rollup, you will have to place it back in a Hierarchy if you proceed to use Modify a Relational Dimension. For more information, refer to Ramifications of Modifying Relational Dimensions.

  1. To return to a demonstration of further options in the Define Member dialog, close the slices you were working in.

Assigning a partial field member name

Provided there is only one item in the Members list, you have the option in the Define Member dialog to use a “partial field member name” (you can not use this feature if there is a concatenation of field entries). As a result, you can edit the name of the item appearing in the underlying table in order to make it appear in proCube as you wish: for example, beginning with the fifth character and ending with the tenth character. One practical example of this is to remove unwanted preceding characters from Member names—e.g., from the Date entry “2009_01” one might want only the “01”, to signify January.

  1. Reopen the Define Members dialog for the Customers dimension. Make sure that ContactName is the only item in the Members list.

  2. Select Partial Members and make sure no other options are selected.

  1. Click Next. The Partial Fields dialog opens.

partialfieldsdialogcontactnamemember.png

Figure 11.           Partial Fields Dialog - ContactName

  1. On the left, the Initial Values are listed—meaning the Member entries as they appear in the underlying relational table. By manipulating the Lower Value index and Higher value index boxes, you can change the appearance of the Members as reflected in the Partial Values list on the right.

  2. For example, enter 10 into the Lower value index. Note the Members’ appearance in the Partial Values list. For this example, reset the Lower value index to 1.

  3. Use the Higher Value index scroll bar to reach 15. The following figure shows the Members of the Customers dimension with Lower value index set to 1 and Higher value index set to 15.

  4. Click Next through the successive dialog boxes to reach the Relational Dimension Summary dialog. You will see that the summary shows 'Partial Fields is On'.

  5. Click Cancel to exit from the Modify Relational Dimension routine.

By changing the Member name, which is what selecting Partial Members accomplishes, you may affect other “Define Member” choices you have made, particularly if you are Modifying a Relational Dimension. For example, if you use Partial Field Names you will have to re-create a Rollup so that it is composed of the Members as they now appear in ”partial” format. Failure to make Member name changes throughout the Cube will result in an error log.

If, for example, you applied the Partial Members option, you would be able to confirm the new appearance of the Members within proCube by opening a new slice that shows Members of the Dimension in columns or rows.

Naming Blank Members as [Undefined]

There may be blank values in a Table field that you are using for Members of a Dimension. By selecting Name Blank Members in the Define Member dialog, you will ensure, first, that you can continue with Cube creation; and, Xchange will create a Member, named Undefined (or whatever you enter into the text box), for the values associated with this “blank” Member. Data from all records that are blank will be added under this Undefined Member.

Assigning Conditional Retrieval

With Xchange, 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 proCube; and, specifically, you used ProductName as the field from which to pull the Member names. By selecting Conditional Retrieval, you can require that the individual ProductName entries get pulled over as Members only if the entries in the SupplierID field contain an “E”, signifying East Coast supplier.

The Conditional Retrieval option is another example of how Xchange 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. 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, as shown in the following steps.

  1. Access the Define Member dialog for the Products dimension, then select the Conditional Retrieval option.

  1. Click Next to open the Conditional Retrieval dialog.

conditionalretrievaldialogproducts.png

Figure 12.           Conditional Retrieval Dialog - Products

All fields for the underlying table (Products) are listed on the left. A drop-down menu above the list  contains numerical operators (the OPERATOR selection) and actual data entries for each of the fields in the table.

  1. From the drop-down, select Data for: ReorderLevel. Entries appear directly below the list. Logically, an entry value appears once, no matter how many times it appears in the field.

dataforreorderlevelconditionalretrievaldialog.png

Figure 13.           Data for: Reorder Level

  1. Select Fields for: [Product].

Our objective is to construct a statement that translates, in plain English, 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 Xchange’s Preview feature, which is conveniently accessible through the icon at the top center of the dialog.

  1. Click Preview the dimension table to open the Preview Table Products dialog. 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”.

previewtableproductsdialog.png

Figure 14.           Preview Table - Products

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.

  1. Click Close.

  2. Select Discontinued in the Fields list on the left, then click the SQL icon to insert 'Discontinued' into the WHERE: list.

  3. From the drop-down menu, select OPERATOR and double-click = to insert it directly after 'Discontinued'.

  4. From the drop-down menu, select Data for: Discontinued and double-click 0 to insert it directly after the =. Your Conditional Retrieval statement should read:

Discontinued = 0

  1. This completes the Conditional Retrieval statement we want to make. Click Next through the successive dialog boxes to reach the Relational Data Summary message. It will show the Conditional Retrieval statement you created:

Conditional SQL: WHERE Discontinued = 0

  1. Click Finish to close the summary message. Messages appear to let you know that proCube has processed the statement. Click through those messages to return to proCube Management Studio.

Defining a drill through

We will next consider the Define Drill Through option in the Define Member dialog.  This option has the ability to select additional User defined fields when performing a Drill Through. The ability to drill through back to the relational source is inherited in Xchange, while the ability to use additional fields is configured through Define Drill Through. This feature allows you to select additional fields from the measures dimensions table and any of the other dimensions tables that make up the Cube.

This extended drill through gives users the ability to see additional data located in the relational database. For example, let’s say you are creating a Financial Data Cube from the General Ledger Transaction Detail Table. Data from this table is brought into the Cube via Xchange at the monthly level. While analyzing the data, it’s determined that additional supporting information is necessary for one of the expense accounts. Selecting the data value and then selecting Drill Through presents the User with a choice to drill through into the relational source. The Users can select additional fields to view, i.e., the Transaction ID for each record. When the requested data is returned, all detailed transactions that made up the data value in the Cube are included along with the additional information selected, i.e., the Transaction ID.

Configuration of Extended Drill Through is done through Xchange’s Member dialog. It can be configured during Cube creation or through the Modify Relational Dimension dialog.

To configure Define Drill Through:

  1. Open the Define Member dialog for the Measures dimension.

  1. In the Fields list, double-click OrderID to send it to the Members list on the right.

  2. Select Define Drill Through.

definemembersdialogorderiddefinedrillthrough.png

Figure 15.           Define Members - Define Drill Through

  1. Click Next to open the Drill Through Dialog.

  2. From the Drill Through Dialog, double-click OrderID to send it to the Drill Through Columns list on the right. Click Next to open a summary.

  3. Click Finish.

  4. Click through the messages to complete the process.

To use the Drill Through feature, create a new slice in the SalesOrder Cube. This slice shows OrderID for Products (columns) and Customers (rows).

  1. Right-click in the cell at the intersection of 1 Chai and Mexico;and from the menu, select Drill Through to open the Drill Through dialog.

  2. Select Drill Through to Relational Source (which by default selects Express Drill Through), and select Show Define Drill Through Values.

  3. Select all dimensions from the left side and move them to the right side so that the dialog appears as follows:

drillthroughdialog1chaicustomers.png

Figure 16.           Drill Through Dialog

  1. Click OK to create the following:

salesorderdrillthroughsliceorderid.png

Figure 17.           Drill Through Slice - OrderID

Here we can see the customers and order IDs for the Chai product.

Setting order preference

The Order Preference option in the Define Member dialog 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. As with the Conditional Retrieval feature, the “dependent” field may be any field from the table.

Aggregate Members are not reordered by the Order Preference option.

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).

  1. Open the Define Member dialog for the Products dimension.

  2. Double-click ProductID and ProductName to move them over to the Members: list.

  1. Select Define Order and then click Next to open the Order Preference dialog.

orderpreferencedialogproductiddesc.png

Figure 18.           Order Preference Dialog - ProductID DESC Statement

The way to proceed is to first pick the Field from the underlying table by which you want to make the Define Order. Note that the familiar Preview feature is available in this dialog, via the icon at the center top.

  1. Double-click ProductID to move it into the ORDER BY: list.

  2. Using the drop-down menu at top left, select Ascending/Descending. These values will then appear in the list below.

  3. Double-click on DESC to move it over to the ORDER BY: list. Your statement should appear as shown in the above Order Preference Dialog.

  4. Click Next until you reach the Relational Dimension Summary message, which displays your Define Order statement.

  5. Click Finish.

  6. Click OK in the message box that reports updated points.

By accessing the ‘Products’ Dimension dialog, you can confirm this Define Order of Detail Members in the Hierarchy, in descending order (descending numerical order, according to ProductID):

productshierarchyorderedbyproductid.png

Figure 19.           Product Hierarchy - Ordered by ProductID

Likewise, in a new slice, the Products Detail Members will appear in descending order, across columns, as in the following figure. (Also note the 15-character length of theCustomers Members, according to the Use Partial Field Member name selection, demonstrated earlier.)

We have noted that Detail Members only are affected by this Define Order feature. If you are working with the demonstration, there are no Aggregate Members, so you will not see the fact that Aggregate Members are not ordered according to the Define Order.

sliceorderedbyproductid.png

Figure 20.           Ordered by ProductID Slice

Defining member options summary

The Conditional Retrieval and Order Preference examples involved changes to the Products dimension. Even so, the Rollup we created, BestMexico Restaurants—in the Customersdimension—and then put into the Hierarchy for the country Mexico, was removed from that Hierarchy. Why? Because choosing 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.

Previously, you learned how to use the feature Modify Relational Dimension, to Add/Delete Members in Measures and other Dimensions. Also, you learned about the features in the Define Members dialog—which you will likely use when you initially create a Cube.

Ramifications of modifying relational dimensions

The table below shows how each of the modification options affects your cube.

Option

Ramification

Clears and Reloads Fact Data

YES

Adds New Metadata

YES

“Recognizes” deleted Table Member (i.e., it no longer appears)

YES

“Gets rid of” Member created in proCube

YES [If Persist* is used, Member is saved, but Fact Data is zeroed]

“Tosses out” Member from proCube-created Hierarchy

YES

Zeroes out “Transfer Cube” data in 2nd Cube

YES

Zeroes out “Formula” data in 2nd Cube

NO

*  The Mark Member as Persistent option (= “Persist”) enables you to “save from deletion” a proCube-created Member. DO NOT use Persist feature on a Member that has been generated from underlying relational tables. The above-bracketed statements are valid when that “Multidimensional Editing Allowed” option is enabled.

Restoring the measures dimension

The Measures Dimension currently filters for OrderID. Before continuing on to Rebuilding Cubes, you will change the Measures dimension back to filter for Quantity as it did prior toDefining a Drill Through.

To restore the Measures dimension to filter for Quantity:

  1. Close any open slices (if applicable).

  2. From the Data ribbon's Xchange Group, click Modify Relational Dimension to open the Modify Dimension from Relational Source Dialog.

  3. Select the Members Dimension and click OK. Click through the messages until the Define Members dialog opens. OrderID is the lone item in the Members: list.

  4. Double-click OrderID to return in to the Fields list.

  5. Double-click Quantity to move it to the Members: list. None of the options in the dialog should be selected.

  6. Click Next.

  7. From the Summary, click Finish.

  8. Open a new slice from the SalesOrder cube to confirm the change.

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk