Home > Articles > Data > SQL Server

  • Print
  • + Share This
Like this article? We recommend

Member Properties

A member property is a characteristic of a dimension member that can be exposed to provide additional information to data warehouse users. For example, suppose that your users want to see customer occupation along with total sales per customer on their reports. You can edit the customer dimension and expose the occupation as the member property pointing to the Occupation column within the customer table. To do this, click Shared Dimensions within the Analysis Manager, right-click the customer dimension and choose Edit. Next, navigate to the name level within the customer dimension, right-click the Member Properties folder and choose New Member Property. From the dialog box that opens, simply choose Occupation. The dialog box you'll see is identical to the one I showed you when adding a measure, so I won't include another screenshot here.

Notice that any time you add a member property you must process the dimension because you are changing its structure. Any cubes that contain the changed dimension also have to be fully processed.

Process the customer's dimension and sales cube (this can be accomplished by right-clicking the cube and choosing Process). Then get back into the Cube Editor and add a calculated member called customer_occupation using the following formula:


Please consult the calculated members section of this article for detailed instructions for adding calculated members. After you add the occupation calculated member, you must save the cube before this member is available for MDX queries. However, you don't have to reprocesses the cube again; this happens because you're simply adding a calculated member—you're not changing the structure of the cube.

Next, run the following statement within the MDX sample application:

SELECT {[measures].members, measures.[customer_occupation] } ON COLUMNS,
FILTER({[customers].[name].members}, ISEMPTY(measures.[unit sales]) = FALSE) ON ROWS
FROM sales
WHERE ([store].[store name].[store 11])

The results will contain all measures as well as the customer occupation for each customer who has shopped in store 11. The abbreviated results are presented in the next figure.

Figure 12Figure 12

This output might allow a marketing manager to see the opportunities to target a particular occupation with more aggressive sales or adjust the current promotional campaign. So member properties allow you to add value to the reports and make them more meaningful.

  • + Share This
  • 🔖 Save To Your Account