MDX Puzzle #5 - Solution

Again I want to apologize for the slow posting of the solution to this puzzle, but I have been hard at work on SQL Saturday #28.  Fortunately, I carved out some time to write it up.  The solution to this puzzle could have been accomplished a couple of ways.  I chose to use the SUM and YTD MDX functions, but before I discuss these functions I will start with the basic query, which satisfies these requirements:

1.  Internet Sales Amount as a Column

2.  Delivery Date Calendar Month as a Row

3.  Applies a filter to limit the rows from the Delivery Date of January 2006 to December 2006.

SELECT

    NON EMPTY(

        {

            [Measures].[Internet Sales Amount]

        }

    )ON COLUMNS,

     [Delivery Date].[Calendar].[Month].&[2006]&[1]:[Delivery Date].[Calendar].[Month].&[2006]&[12]ON ROWS

FROM [Adventure Works]

Next I created a calculated member whose purpose is to return the YTD or Running Total.  To accomplish this I coupled the SUM and YTD functions.  See below:

WITH MEMBER Measures.[YTD Internet Sales]

AS

SUM (YTD([Delivery Date].[Calendar].CurrentMember),[Measures].[Internet Sales Amount])

Using the YTD function I was able to obtain a set of members from the same level as the given member, which in this case was the [Delivery Date].[Calendar].CurrentMember.  I then used the SUM function to accurately calculate the YTD aggregations for the [Internet Sales Amount] measure.  The solution to the puzzle should resemble this:

WITH MEMBER Measures.[YTD Internet Sales]

AS

SUM (YTD([Delivery Date].[Calendar].CurrentMember),[Measures].[Internet Sales Amount])

SELECT

    NON EMPTY(

        {

            [Measures].[Internet Sales Amount],

            Measures.[YTD Internet Sales]

        }

    )ON COLUMNS,

    [Delivery Date].[Calendar].[Month].&[2006]&[1]:[Delivery Date].[Calendar].[Month].&[2006]&[12]ON ROWS

FROM [Adventure Works]

There are several ways to accomplish this, but this solutions does work.  Stay tuned for Puzzle #6.

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS

Founder www.TSQLScripts.com and www.SQLLunch.com.

Visit www.BIDN.com, Bring Business Intelligence to your company.

MDX Puzzle #5

Writing YTD totals and Running totals using T-SQL can take a little effort.  However, with MDX it's not too difficult.  In this puzzle I will write an MDX statement that produces a monthly YTD or Running total.  Here are the requirements:

Columns:  Monthly Running Total (Calculation), Internet Sales Amount

Rows:  Delivery Date Calendar Month

Filters:  Delivery Date from January 2006 to December 2006

Hints:  You may need to create a scope calculation (WITH MEMBER), use the YTD and SUM functions

Remember, don't post your solution here.  Save them for my solution post.  I will post it along with the steps that was taken to solve this puzzle in a couple of days.  Here is a screen shot of what the results should look like:

Don’t forget to check back in a couple days for the solution.

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS

Founder www.TSQLScripts.com and www.SQLLunch.com.

Visit www.BIDN.com, Bring Business Intelligence to your company.

MDX Puzzle #4

This puzzle is rather simple, but it does introduce a few new things. Using T-SQL you typically filter queries with a WHERE clause.  You can also do the same with MDX, but just like T-SQL there are a few ways to accomplish this.  In this puzzle you will see an example.  Here are the requirements:

Show: Internet Sales Amount Columns:  Ship Date Calendar Year Rows:  Product SubCategory Filter:  Only return Product SubCategories that have sales greater than 1,000,000 and only return data between Calendar Year 2006 and 2008. Hint:  Use the FILTER function to limit the rows by Sales Amount.

Remember, don't post your solution here.  Save them for my solution post.  I will post it along with the steps that was taken to solve this puzzle in a couple of days.  Here is a screen shot of what the results should look like:

\

Don’t forget to check back in a couple days for the solution.

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS

Founder www.TSQLScripts.com and www.SQLLunch.com.

Visit www.BIDN.com, Bring Business Intelligence to your company.

MDX Puzzle #3

The next puzzle comes from a BIDN.com forum post.  Here are the requirements:

Columns:  Internet Order Quantity and Percentage Of Total (Calculation)

Rows: Product

Filter:  Return only the TOP 10 Products based on Internet Order Quantity.

Hint:  Use the WITH MEMBER statement to perform the calculation.  In addition, you will need to you the ROOT function or [ALL Products] to get the Total Quantity Ordered for the calculation.  Your result set should resemble the following:

Remember, don’t post you solutions here.  Save them for my solution post.  I will post it along with the steps that was taken to solve the puzzle in a couple of days.  

Don’t forget to check back in a couple days for the solution.

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS

Founder www.TSQLScripts.com and www.SQLLunch.com.

Visit www.BIDN.com, Bring Business Intelligence to your company.

MDX Puzzle #2 Solution

This puzzle was a little more challenging than the first, but it was definitely fun and a good learning experience.  It introduced a new method that allows you to create session scoped calculation that can be used in your MDX query.  You can actually take the code used in the CREATE MEMBER statement (that will be explained in later puzzles) and add it to your cube as a CALCULATED MEMBER.

First, let’s address the simple parts of the requirements.  The following query satisfies the following:

1. Columns:  Internet Sales
2. Rows:  Calendar Years
3. Filter:  Only Show United States Bike Sales

Now, above this statement in your query window you can use the CREATE MEMBER or WITH MEMBER statement to create a calculated member to be used in the query.  In this puzzle I will be using the CREATE MEMBER statement.  I will use the WITH MEMBER statement in later puzzles.  The CREATE MEMBER statement defines a calculated member that is available throughout the session and can be used by multiple queries within the session.  This statement will be used to create the YearlyGrowth calculated member, which will be added to the above query.  Here is the statement:

You should note that a CASE statement is used in the calculation.  There are three conditions in the case.  The first condition will ensure that the query avoids a divide-by-zero error by checking the previous years sales.  If the previous years sales is empty then the calculation is not performed and the value retuned is NULL.  The second condition checks to see if the sales for the current year is empty.  As with the previous year, if the current year is empty a NULL value is returned.  Finally, if neither condition is met the calculation is performed. 

There are also a few new functions that must be explained.  The first is IsEmpty.  The IsEmpty function evaluates whether or not a cell value is empty.  The next function is PrevMember.  The PrevMember function returns the previous member in the level that contains the specified member.  In the example, I use it to return the previous years sales.  Finally, FORMAT_STRING, which not a function, but an option for the CREATE MEMBER statement, is used to specify how the returned value should be formatted.

If you couple these two statements in the same query window, running the create member statement first then the query, you will completely satisfy the requirements of the query.  Here is the solution:

CREATE MEMBER [Adventure Works].Measures.YearlyGrowth 

AS 

'

CASE 

When IsEmpty

(

    ([Internet Sales Amount], [Date].[Calendar Year].PrevMember)

)

Then Null

When IsEmpty

(

    [Internet Sales Amount]

)

Then Null

 ELSE

(

    (

        ([Internet Sales Amount])-([Internet Sales Amount], [Date].[Calendar Year].PrevMember)

    )

    /([Internet Sales Amount], [Date].[Calendar Year].PrevMember)

)

END',

FORMAT_STRING='Percent';

SELECT 

    NON EMPTY{[Measures].[Internet Sales Amount], YearlyGrowth

    } ON COLUMNS,

    NON EMPTY(

         [Date].[Calendar].[Calendar Year].Members) ON ROWS

FROM [Adventure Works]

WHERE

    (

        [Sales Territory].[Sales Territory].[Country].&[United States],

        [Product].[Category].[Bikes]

    );

In the above query I added the calculated member, Yearly Growth, to the list of columns.  If you need to update the calculated member, simply change the CREATE to an UPDATE.  Then run the statement and finally rerun your query.  Unlike T-SQL, the two MDX statements must be run separately.  Stay tuned for Puzzle #3. 

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS

Founder www.TSQLScripts.com and www.SQLLunch.com.

Visit www.BIDN.com, Bring Business Intelligence to your company.

MDX Puzzle #2

Now that we have our feet wet, let’s get started with the next puzzle.  In this puzzle we will be writing a query that returns Percentage Growth Year over Year.  For example, if sales were $258,056 in 2006 and $389,456 in 2007, then the percentage growth would be (2007 sales – 2006 sales)/2006 sales.  Here are the requirements:

Columns:  Internet Sales Amount and Percentage Growth (calculated measure) Rows:  Calendar Years Filter:  Only Show United States Bike Sales Hint:  You can use the CREATE MEMBER statement to perform the calculation and use it in your query.   You may also need to use a CASE Statement to address a Divide By Zero error.

Remember, don’t post you solutions here.  Save them for my solution post.  I will post it along with the steps that was taken to solve the puzzle in a couple of days.   Here is a screen shot of what the results should look like:

 

Don’t forget to check back in a couple days for the solution.

Talk to you soon,

Patrick LeBlanc

Founder www.TSQLScripts.com and www.SQLLunch.com.

Visit www.BIDN.com, Bring Business Intelligence to your company.

MDX Puzzle #1 Solution

OK, are you ready to start the MDX journey.  In MDX Puzzle #1 posting I presented you with the challenge of writing an MDX query based on the requirements that I outlined in that posting.  In this solution and all subsequent solutions I will walk you through the process of writing the MDX restating each requirement along the way.  Also, as I write the query I will define and explain any new concepts and functions that are introduced.  Enough with the formalities let’s go.

The first thing that you will need to do is get connected to a SQL Server Analysis Server (SSAS).  If you are like me, a long-time DBA, you are probably thinking, how in the heck do I connect to SSAS.  I have been connecting to SQL Servers for more than ten years, now they want me to connect to an Analysis Server.  Well don’t worry it’s just like connecting to a SQL Server.  First, open SQL Server Management Studio (SSMS), yes I said SSMS.  Select Analysis Services from the Server Type drop down list, type your server name and click Connect.

Now that you are connected, you will need to ensure that you are querying the correct database.  In the menu bar there is a drop down list that contains a list of all the available databases.  Choose the Adventure Works DW 2008R2 database from the drop down list then click the New Query button.  I was getting a little excited, because these steps are very similar to connecting to and writing a query against a SQL Server Database.

When you click the New Query button a new tab becomes available.  On this tab you will select the cube (Adventure Works) and the Measure Group (Internet Sales), which you will by querying.  Finally, I am ready to start writing this MDX query.  Here is the first query that I wrote against the cube:

SELECT *

FROM [Adventure Works]

I wanted to see all the columns (hahahahahaha) in the cube.  Was I wrong.  This is a cube (Multi-Dimensions) not a single table.  When selecting from the cube you select Measures and Attributes.  What do I do?  Well looking at the new tab in SSMS I expanded Measures then expanded the Internet Sales folder.  The first requirement from the puzzle was to return Internet Sales Amount and Internet Order Quantity.  I performed a drag and drop on the Internet Sales Amount measure, replacing the asterisk.  I repeated the steps for the Internet Order Quantity Measure.  Separating the two with a comma and arrived at the next query that I ran:

This one did not run either.  I was already beginning to not like this MDX stuff.  Not only was it very specific, but I noticed that everything has to be fully qualified.  To get it to run I had to add two things to my query.  First, curly braces ({ }) were place around both measures.  Then the ON COLUMNS keywords were added immediately after the second brace.  The braces creates the set of measures and the ON COLUMNS keywords specifies which axis to place the result set.  There are several variations to this, as time goes on we will discuss them all.

Finally, my first executable MDX query.  The next requirement was to show Product Categories and SubCategories as ROWS in the result set.  In my earlier research, I found the solution for this.  You will need to add a Tuple to your query.  A tuple is a combination of dimension members or attributes.  In this puzzle we are required to return two members from the same dimension, note that a tuple can contain members from different dimensions.  My second working MDX query:

In addition to the tuple, the ON ROWS keywords were included in the query.  As with the ON COLUMNS, ON ROWS specify which axis to place the data in the result set.  Maybe this MDX stuff isn’t too bad after all.  Let’s move on.  I did get a little stuck on the next requirement, which was to add Ship Years to the Columns axis.  After a little searching I came up with the solution.  I had to create two tuples within the set of measures.  The tuples contained the Ship Year coupled with each measure.

I also suffixed each Ship Year and both the Category and Subcategory with the Children function.  This function returns a set of children for a specified member.  You may notice several null values in your result set.  How do you get rid of them?  You can’t just add WHERE <some column> IS NOT NULL to your query.  This brings me to our last requirement, which was to filter the result set to Exclude NULL values and only show Men from the Customer Dimension.  This to me was kind of simple.  A quick search and I found the NON EMPTY keyword, which returns the set of tuples that are not empty (does not contain NULL values).

Wow, a lot of work, but this is finally starting to make a little sense to me.  Finally, we need to filter the result set so that it only returns men from the Customer Dimension.  In MDX you will use a WHERE clause to filter the data, but instead of filtering it is considered Slicing.  To Slice our result set to meet our requirements add one final line of syntax to our query, which is our SOLUTION!

Download Script

Done.  What a task, but I learned so many things from this.  I can’t wait for the next challenge.  Please post your comments and solutions here.  As always, if you have any questions please feel free to email me at pleblanc@pragmaticworks.com.