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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jan 3, 2005

MDX Essentials: String / Numeric Functions: More on the IIF() Function - Page 3

By William Pearson

The IIF() statement that comprises the definition of the ApproxSqFootage calculated member uses the numeric, versus string, option for returned values. (We discussed these options in String / Numeric Functions: Introducing the IIF() Function). Because member properties, by definition, are strings (even when storing numeric information), the string must be converted to a number for the function to allow us the "0" in the second return value expression in the function.

The purpose of the second WITH MEMBER section of the query is to create a calculated member to provide SalesPerSqFt for each Store location. This value is simply defined as Store Invoice divided by the new ApproxSqFootage value.

12.  Insert the following syntax, between [Measures].[Store Invoice] and the closing bracket of the ON COLUMNS line (first line under the SELECT keyword of the query:

,[Measures].[ApproxSqFootage],[Measures].[SalesPerSqFt]

NOTE: Be sure to insert the comma after [Measures].[Store Invoice], and before the added syntax, so that a comma separates the three measures. Placement of commas is important within later steps as well, where one appears in a similar placement to that in Step 12 above.

We are simply adding the two new calculated members to the SELECT clause to facilitate their retrieval in our query. Our modified query appears, with the modified sections in red rectangles, as shown in Illustration 3.


Illustration 3: Modified Query in Query Pane (with Modifications in Rectangles)

13.  Execute the query by clicking the Run Query button in the toolbar.

Analysis Services populates the Results pane, and the DataSet depicted in Illustration 4 appears.


Illustration 4: Results DataSet with New Calculated Members

While it appears that the columns we wish to present have materialized, our calculated members do not appear to be presenting the expected data in every case. First, we see the dreaded 1.#INF in several of the SalesPerSqFt fields, an indicator, in this case, of a "divide by zero" condition.

Next, we see that some of the ApproxSqFootage fields are populated by zeros - not that our function is not working: it certainly is, or these would be nulls. However, the zeros indicate "no square footage," for whatever reason, captured for the associated Stores, and our instructions from the information consumers clearly stated that they were interested in presenting "only operating store locations, which had invoice activity / area values for year 1998." (One of the locations in our current DataSet, HQ, is obviously an "overhead account" included in the Stores dimension for some other reason - a reason that, while perfectly appropriate within another context, does not apply from the perspective of our immediate criteria.)

Finally, it is obvious that the Stores are not ordered by SalesPerSqFt in descending order, another request of the intended audience. In cases like this, I usually consider sorting, as well as formatting, in a later step anyway, because I like to get the population of the DataSet correct first. We will remedy the other issues at this point, and then manage the ordering and formatting next.

Let's resolve the "holes" in the data first. We can do this quite easily with the highly useful FILTER() function, as we shall see in the next steps.

14.  Select File -> Save to resave file MDX27-2 in the location chosen earlier. Leave the file open for the next step.

15.  Replace the comment line with the following:

-- MDX27-3 Internal Sales / Store Area Analysis: Step 3: Filter Empties  
   and Zeros

NOTE: Be sure to comment out the second line if the above text "wraps" in the Sample Application. Mine did not, in this or later steps, as we can see in the associated illustrations; the "wrap" in the text above is a function of page margins for this article.

16.  Save the file as MDX27-3, to protect MDX27-2.

17.  Insert the following syntax, immediately within the left curly brace ("{") on the ON ROWS line of the SELECT clause,

FILTER(

between the brace and [Store].[Store Name].Members .

18.  Insert the following syntax, immediately to the right of [Store].[Store Name].Members, within the ON ROWS line of the SELECT clause,

, ([Measures].[ApproxSqFootage] > 0))

We are placing the FILTER() function within the curly braces, and placing [Store].[Store Name].Members into the FILTER() function, as the set, or first entry, which is followed by the search condition ([Measures].[ApproxSqFootage] > 0)).

NOTE: For a detailed introduction to the FILTER() function, see my Database Journal article MDX Essentials: Basic Set Functions: The Filter() Function.

Our modified query appears, with the modified sections in red rectangles, as shown in Illustration 5.


Illustration 5: Modified Query in Query Pane (with Modifications in Rectangles)

19.  Execute the query by clicking the Run Query button in the toolbar.

The Results pane is populated with the DataSet depicted in Illustration 6.


Illustration 6: Results DataSet with FILTER() in Place

As we can see in the illustration above, our filter seems to have done the trick. The population of the DataSet appears to fit the business requirements. All that remains is ordering, and a couple of cosmetic touches, to please the intended audience.

20.  Select File -> Save to resave file MDX27-3 in the location chosen earlier. Leave the file open for the next step.

21.  Replace the comment line with the following:


MDX27-4 Internal Sales / Store Area Analysis: Step 4: Order DataSet / 
     Formats

22.  Save the file as MDX27-4, to protect MDX27-3.

23.  Insert the following syntax, immediately within the left curly brace ("{") on the ON ROWS line of the SELECT clause,

ORDER(

between the brace and FILTER (inserted in the last set of modifications).

24.  Insert the following syntax, immediately to the right of ([Measures].[ApproxSqFootage] > 0)), which we added as part of the FILTER() function in the last set of modifications, within the ON ROWS line of the SELECT clause,

 , ([Measures].[SalesPerSqFt]), BDESC)


MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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


















Thanks for your registration, follow us on our social networks to keep up-to-date