Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Nov 25, 2003

MDX in Analysis Services: Calculated Members: Leveraging Member Properties - Page 4

By William Pearson

In the results set, we note that the new calculated member, Store Size, appears, with single zeros filling the fields for which no square footage is defined in the cube (the "nulls" are converted to zeros). We can see that the text strings that occupy the Store SqFt member property have been converted to numerical values, as the nulls that exist in the cube for certain members have become zeros instead.

12.  Save the query with changes as MXAS09-2.

Next, let's change the SELECT clause to retrieve and display not only the Store Names (the lowest level of the Store dimension), but to retrieve and display the "rollup" levels in the hierarchy of Store City, Store State, and Store Country.

13.  Modify the comment line in the query to read as follows:

-- MXAS09 - 3:  Tutorial Query - Step 3

Within the SELECT clause of the query, immediately following the {[Measures].[Store Profit], [Measures].[Store Size]} ON COLUMNS, line, modify the next existing line as follows:

Step 2 Affected Line:

{[Store].[Store Name].Members} ON ROWS

Step 3 Modified Line:

{[Store].Members} ON ROWS 

The modification above changes the SELECT clause to retrieve the members of the Store dimension in general, which will include all members - not just Store Names. This will mean that the individual stores, as well as the level members to which they belong, will be retrieved.

The modified query appears in the Query pane, in its entirety, as depicted in Illustration 6:

Illustration 6: Step 3, with Modification Circled, as Displayed in the Query Pane

14.  Execute the Step 3 query by clicking the Run button.

The result dataset appears partially displayed in Illustration 7 below:

Illustration 7: Partial Results Dataset, Query Step 3

We notice that, at the rollup levels, we have #ERR appearing as the result in the affected fields. This occurs because, although we have converted the Store SqFt member property to a numeric value, we have yet to make provision for an aggregation to occur; the square footage values are held at the member level, and not at the various dimension levels to which the stores belong in our cube. We will handle this with the next step, where we will create a calculated member, based upon the Store dimension, to sum the individual store square footages at the appropriate rollup levels.

We will create a calculated member that correctly handles the summing, while leaving our original calculated member in place for comparison purposes.

15.  Save the query with changes as MXAS09-3.

16.  Modify the comment line in the query to the following:

-- MXAS09 - 4:  Tutorial Query - Step 4

17.  Within the WITH statement of the query, immediately following the 'Val(Store.CurrentMember.Properties("Store SqFt"))', line, add the following syntax to create a new calculated member for Total Store Area:

MEMBER[Measures].[Total Store Area] AS 
   'SUM(Descendants([Store].CurrentMember, [Store Name]) , [Store Size])' 

18.  Within the SELECT statement of the query, in the first line, modify the syntax to include selection of the new calculated member, Total Store Area:

Step 3 Affected Line:

{[Measures].[Store Sales], [Measures].[Store Size]} ON COLUMNS,

Step 4 Modified Line:

{[Measures].[Store Sales], [Measures].[Store Size], 

    [Measures].[Total Store Area]} ON COLUMNS,

The modified query appears in the Query pane, as shown in Illustration 8.

Illustration 8: Step 4, with Modifications Circled, as it Appears in the Query Pane

19.  Execute the Step 4 query by clicking the Run button.

The results dataset appears as partially depicted in Illustration 9.

Illustration 9: Step 4, Partial Results

The "parallel calculated member," Total Store Area, appears alongside its predecessor, and appears to have overcome the limitations of the Store Size calculated member. We see rollups occurring accurately, in that the individual stores' square footages now add correctly at the corresponding Store City, Store State, and Store Country levels.

20.  Save the query with changes as MXAS09-4.

Were this destined to support a "real world" reporting platform for a client, I would perform formatting at this stage, after removing the Store Size calculated member from the SELECT statement, so as not to display it. We will leave the query at this stage, however, with an invitation to all readers to play with the layout further, as time is available.

With this example, we have advanced another step within our examination of calculated members, retrieving a member property and returning it, after a conversion with the val function, in a way that allows it to deliver more value to the information consumers. We have taken steps to leverage the property beyond its standard capability to store information for the member that contains it, and to provide rollup values through the SUM function. This demonstrates additional possible uses for the aggregation of values using a non-measure calculated member.

Summary and Conclusion ...

In this lesson, we extended our growing familiarity with calculated members, to include the selection and retrieval of member properties. We briefly discussed the nature of member properties, providing illustrations of how they can be used to help us to meet our business needs. We then focused upon the selection of member properties within our calculated members, performing a practice example in which we used member properties to provide square footage of our store locations, after converting the values concerned to a usable datatype, to further demonstrate possible ways that we might add value to the projects we develop for information consumers.

» See All Articles by Columnist William E. Pearson, III

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM