SQL Saturday Minnesota (149) Recap

My customary post-SQL-Event post is a Top Ten List. I have decided that I do not wish to be bound by the limitations of the number Ten. Therefore, from here on out, my plan is to do a more free form recap.

SQL Saturday #149 just took place in Minneapolis, MN, on September 29th. There were also some Pre-cons offered on September 28th. I shall arrange my recap chronologically.

September 28th

I attend the Unlocking Insight – Be a Data Hero pre-con given by Dan English (b|t) and Brian Larson. The content was on the BI stack in SQL Server 2012, with special focus on Tabular Models and Power View. I got a lot out of it and had fun, to boot. Nice work, gents.

I had to miss the Speaker Dinner this time around. But, if you will bear with me, I will imagine some events that may or may not (likely not) have taken place:

Llama1. Jason Horner (b|t) showed up riding a llama he personally befriended while on a hiking trip through the Andes. Her name was Gertrude and her ability to juggle was astounding.

2. Jason Strate (b|t) decided to challenge Gertrude to a game of chess. Fortunately for Jason, Gertrude’s hastily constructed Sicilian Defense was no match for his knights. Well done, Jason.

3. Jes Schultz Borland (b|t) demonstrated that she can actually levitate over a stack of SQL Server books by uttering a very long, high pitched, “SQUEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE!” It was truly impressive to behold.

 

 

 

 

September 29th

I attended the POSH Eye for the BI Guy presentation given by Jason Horner (b|t). It was about using Powershell for managing objects in SSIS, SSRS, and SSAS. Jason showed great agility by doing an impromptu intro to Powershell when he learned that the vast majority of the attendees had never used it. I really enjoyed it and am looking forward to playing with Powershell to do all kinds of cool stuff with the BI tools.

I attend the Design and Implement BI Like Edison session by Bill Pearson (t). He did a great job combining fascinating facts about Thomas Edison with Business Intelligence. It was a lot of fun.

I helped Mike Donnelly (b|t) distribute lunches to the experts in the lunch breakout sessions called “Topics on a Stick,” which is a great reference to the numerous stick-bound foods one can get at the annual Minnesota State Fair. I then hung out at the PASS table with Sarah Strate (b|t) to have my lunch and do my part as a PASS Regional Mentor.

I had the honor of giving my MDX Trek: First Contact presentation. I have a blast with this one. Thanks to all who attended and for the great feedback.

I attended the session called Fast Track to Spatial Reporting by Jason Thomas (b|t). Jason did a great job explaining the use of spatial data with Reporting Services 2008 R2. As someone who has only done basic stuff with maps in SSRS, I found it really helpful.

For the final session of the day, I went to Real-Time Analytics With SSAS Tabular DirectQuery by Paul Doyle (t). It was a really interesting look at the DirectQuery option for the new Tabular models in SSAS 2012. Paul and a colleague demonstrated how to set it up, track it using Profiler, and went over some of the caveats. It was a good session on a new topic for me. Nice work, gents.

The after party was a great time. I hung out with old friends and made some new ones. There was SQLKaraoke, which is always a good time. The SQL Saturday after-parties are just awesome. Spending time with the fine folk of the SQL Community is just awesome.

That pretty much wraps it up. Huge thanks to everyone who worked so hard to make this event the great success that it was. The people who give so much of their time and energy to put on events like SQL Saturday are truly an asset to the SQL Community and deserve way more recognition than they tend to receive.

Intro To MDX Decathlon–PERIODSTODATE() Function (10/10)

For the final post in this series, we’ll take a look at the PERIODSTODATE() function. Your skills of predictive analytics should suggest that this is a handy one to use for reports needing calculations for year-to-date, month-to-date, etc.

The PERIODSTODATE() function takes two inputs:

  • [Level]: The hierarchy level defining the “Period” part of the period-to-date
  • [Ending Member]: The member that will form the end of the resulting set as well as the level at which the members will reside

It returns the set of members at the same level as the [Ending Member] going back to the beginning of the [Level] hierarchy level. That’s a fun one to try to explain in one sentence. Let’s look at an example and it will become clearer.

Let’s suppose we find ourselves on January 10th, 2011, as shown in Image 1.

Image 1

image

The MDX expression for this member of my Date Hierarchy would be:

[Date].[Date Hierarchy].[Full Date].[2011-01-10]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

If we wanted to find all of the [Full Date] members for the month of January in 2011 up to and including the [2011-01-10] member, we could use the PERIODSTODATE() function like this:

PeriodsToDate
(
  [Date].[Date Hierarchy].[Calendar Month]         -- Level Boundary
, [Date].[Date Hierarchy].[Full Date].[2011-01-10] -- Ending Member
)

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

The expression above specifies that we want to return the set of members that would defined the month-to-date starting with January 10th, 2011.

Our resulting set is shown highlighted in Image 2.

Image 2

image

An MDX expression for the resulting set would be:

{
  [Date].[Date Hierarchy].[Full Date].[2011-01-01]
, [Date].[Date Hierarchy].[Full Date].[2011-01-02]
, [Date].[Date Hierarchy].[Full Date].[2011-01-03]
, [Date].[Date Hierarchy].[Full Date].[2011-01-04]
, [Date].[Date Hierarchy].[Full Date].[2011-01-05]
, [Date].[Date Hierarchy].[Full Date].[2011-01-06]
, [Date].[Date Hierarchy].[Full Date].[2011-01-07]
, [Date].[Date Hierarchy].[Full Date].[2011-01-08]
, [Date].[Date Hierarchy].[Full Date].[2011-01-09]
, [Date].[Date Hierarchy].[Full Date].[2011-01-10]
}

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

You will note that the resulting set includes all of the members back to the beginning of January in 2011. Since are Ending Member was at the [Full Date] level of the hierarchy, the resulting members are at that level as well. And, since the Level specified was [Calendar Month], we therefore get all [Full Date] members from our Ending Member back to the very first [Full Date] within our Ending Member’s  [Calendar Month].

Note: Since you have been following this series with the utmost diligence for the last ten weeks, you will recall the explanation of the Range operator in the Intro To MDX Decathlon – Sets: A Shirt of a Different Color (4/10) post. Therefore, you will know that a more succinct expression for the above set would be:

{
  [Date].[Date Hierarchy].[Full Date].[2011-01-01]
: [Date].[Date Hierarchy].[Full Date].[2011-01-10]
}

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Let’s look at some examples from my Borg cube.

Query 1 uses the PERIODSTODATE() function to return all of the [Calendar Month] members of the 1990 [Calendar Year] up to and including the [1990-04] member, ie April.

Query 1

SELECT
      [Measures].[Episode Count] ON COLUMNS
    , PeriodsToDate
        (
          [Date].[Date Hierarchy].[Calendar Year]            -- Period to encompass
        , [Date].[Date Hierarchy].[Calendar Month].[1990-04] -- Where to start
        ) ON ROWS
FROM [Borg]
WHERE [Series].[Series Name].[Star Trek: The Next Generation]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Result 1

image

Our result is January thru April of 1990.

Query 2 uses the same ending member but this time specifies [Calendar Quarter] as the level.

Query 2

SELECT
      [Measures].[Episode Count] ON COLUMNS
    , PeriodsToDate
        (
          [Date].[Date Hierarchy].[Calendar Quarter]         -- Period to encompass
        , [Date].[Date Hierarchy].[Calendar Month].[1990-04] -- Where to start
        ) ON ROWS
FROM [Borg]
WHERE [Series].[Series Name].[Star Trek: The Next Generation]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Result 2

image

Since the [Calendar Month] member specified, [1990-04], is the first [Calendar Month] member within its [Calendar Quarter], our result is just the [1990-04] member we originally specified.

This brings my first blog series to a close. I hope I was able to show that MDX does not have to be intimidating. I encourage you to try the MDX examples you saw here or use them as a model for trying MDX in your own environment.

Intro To MDX Decathlon–PARALLELPERIOD() Function (9/10)

Like the LEAD() and LAG() functions we looked at in the previous post, the PARALLELPERIOD() function is also useful for travelling through ordered hierarchies. However, there is one really important difference. While LEAD() and LAG() use the hierarchy level of the member passed to the function to determine the hierarchy level to be traversed, the PARALLELPERIOD() function lets you specify the level as an argument. Because of this, the hierarchy level traversed can be different from the level of the member passed and the resulting member. This can be a bit confusing at first. The examples will help, though.

The PARALLELPERIOD() function takes three inputs:

  • [Level]: The hierarchy level to traverse
  • [Index]: an integer value indicating the number of members to travel
  • [Starting Member]:The starting member

It returns the member that is [Index] members of the [Level] hierarchy level previous to the [Starting Member] member.

Let’s suppose we find ourselves on March 10th, 2011, as shown in the calendar in Image 1.

Image 1

image

The MDX expression for this member of my date hierarchy would be:

[Date].[Date Hierarchy].[Full Date].[2011-03-10]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

If we wanted to travel back in time two months, we could make use of the PARALLELPERIOD() function:

ParallelPeriod(
  [Date].[Date Hierarchy].[Calendar Month]             -- Level
, 2                                                    -- Index
, [Date].[Date Hierarchy].[Full Date].[2011-03-10]     -- Starting Member
)

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Notice we are traversing the Calendar Month while passing a starting member that is a Full Date.

The resulting date is shown in Image 2.

Image 2

image

The MDX expression for the resulting member would be:

[Date].[Date Hierarchy].[Full Date].[2011-01-10]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Since we specified a level of [Calendar Month] and an index of 2, we travelled 2 calendar months back within our Date Hierarchy. Since our starting member was the tenth day of its parent month, the result was the tenth day of its month. Thus, we travel from March 10th, 2011 back exactly two months to January 10th, 2011.

Let’s take a look at my Borg cube.

In Query 1, we start on the [Calendar Month].[1990-01] member. We then travel one member back on the [Calendar Year] level of the hierarchy.

Query 1

SELECT
      [Measures].[Episode Count] ON COLUMNS
    , ParallelPeriod(
          [Date].[Date Hierarchy].[Calendar Year] -- Level to traverse
        , 1 -- How many members to go back on the Level
        , [Date].[Date Hierarchy].[Calendar Month].[1990-01] -- Where to start
        ) ON ROWS
FROM [Borg]
WHERE [Series].[Series Name].[Star Trek: The Next Generation]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Result 1

image

Since we started on the first calendar month of 1990, when we travel one calendar year back, we get to the first calendar month of 1989.

In Query 2, we once again start on the [Calendar Month].[1990-01] member. This time, instead of traversing at the [Calendar Year] level, we use the [Calendar Quarter] level.

Query 2

SELECT
      [Measures].[Episode Count] ON COLUMNS
    , ParallelPeriod(
          [Date].[Date Hierarchy].[Calendar Quarter] -- Level to traverse
        , 1 -- How many members to go back on the Level
        , [Date].[Date Hierarchy].[Calendar Month].[1990-01] -- Where to start
        ) ON ROWS
FROM [Borg]
WHERE [Series].[Series Name].[Star Trek: The Next Generation]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Result 2

image

This time, we travel back one calendar quarter. Since we started on the first [Calendar Month] of the first quarter of 1990, travelling back one quarter takes us to the first [Calendar Month] of the 4th quarter of 1989: October.

That’s really all there is to this one. It can just get a little confusing when the level being traversed can be different than the level of the starting and ending members.

Next time, in the final post in this series, we will play with the mighty PERIODSTODATE() function.

Intro To MDX Decathlon–LEAD() and LAG() Functions (8/10)

One of the many strengths of MDX is that there are many different functions you can use for travelling through time. And as a plus, none of them require you to sling-shot around the Sun at maximum Warp speed. In this post, we will introduce the simplest of these functions: LEAD() and LAG(). Although I am using these for Time, note that they will work with any ordered hierarchy. It does not HAVE to be Time related.

LEAD()

The LEAD() function takes two inputs. The first input is the expression to which you append .lead(N). The second is the integer value you place where I have the N in the parentheses, known as the index. The output is the member of the hierarchy at the same level as the member passed that is N members further in the sort order of that hierarchy. This will be easiest to show with a simple date example.

Suppose we find ourselves on January 10th, 2011, shown highlighted in Image 1.

Image 1

image

The MDX expression for this member of my Date Hierarchy would be:

[Date].[Date Hierarchy].[Full Date].[2011-01-10]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

If we want to move forward on the calendar by two days, we could append the LEAD() function with an index of 2. That expression would look like this:

[Date].[Date Hierarchy].[Full Date].[2011-01-10].lead(2)

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Our result will be the date shown highlighted in Image 2: January 12th, 2011. The resulting member expression would, therefore, be:

[Date].[Date Hierarchy].[Full Date].[2011-01-12]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Image 2

image

Since we passed a member at the [Full Date] level of the hierarchy, the LEAD() function knew we would be traversing members at that level. By passing a 2 as the index, we travelled 2 members further down the line according to the hierarchy’s configured sort order in the cube. Since this hierarchy is sorted by date, we travelled 2 [Full Date] members into the future.

My Borg cube awaits.

In Query 1, we start with my [Calendar Month] member of January of 1989, [1989-01]. When we apply the LEAD() function with an index of 3, the brilliant explanation above, in addition to your spectacular skills of predictive analytics, can likely let you determine that we will be travelling 3 calendar months into the future, arriving at April of 1989.

Query 1

SELECT
      [Measures].[Episode Count] ON COLUMNS
    , [Date].[Date Hierarchy].[Calendar Month].[1989-01].lead(3) ON ROWS
FROM [Borg]
WHERE [Series].[Series Name].[Star Trek: The Next Generation]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Result 1

image

If you want to picture Leonard Nimoy as Spock calmly standing with his hands clasped behind his back raising an eyebrow and saying, “Bam,” you go right ahead. I just did and I’m still laughing. Excuse me, I need a moment.

<<pause>>

OK. I’m better, now.

You may be wondering what would happen if you you used a negative number as the index for the LEAD() function. That merely causes the function to go in the opposite direction. So, in Query 2, we merely change the 3 in the index to a –3.

Query 2

SELECT
      [Measures].[Episode Count] ON COLUMNS
    , [Date].[Date Hierarchy].[Calendar Month].[1989-01].lead(-3) ON ROWS
FROM [Borg]
WHERE [Series].[Series Name].[Star Trek: The Next Generation]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Result 2

image

Instead of travelling 3 [Calendar Month] members forward, we travel 3 members backward.  I’m totally still giggling about the Leonard Nimoy thing.

LAG()

The LAG() function also takes two inputs. As a matter of fact, the only real difference between LEAD() and LAG() is the direction they travel along ordered hierarchies. While LEAD() travels forward with positive index values, LAG() travels backward with positive index values. And just like LEAD(), passing negative index values to LAG() causes it to change direction.

So, we can start at January 12th, 2011, as in Image 3, and apply the LAG() function with an index of 2.

[Date].[Date Hierarchy].[Full Date].[2011-01-12].lag(2)

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Image 3

image

This takes us back 2 [Full Date] members along the hierarchy and we end up at the member for January 10th, 2011 as shown in Image 4.

[Date].[Date Hierarchy].[Full Date].[2011-01-10]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Image 4

image

To the Borg cube.

Query 3 starts at the [Calendar Month] member for January of 1989 and uses the LAG() function with an index of 1 to go back 1 month.

Query 3

SELECT
      [Measures].[Episode Count] ON COLUMNS
    , [Date].[Date Hierarchy].[Calendar Month].[1989-01].lag(1) ON ROWS
FROM [Borg]
WHERE [Series].[Series Name].[Star Trek: The Next Generation]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Result 3

image

Travelling one month back from January of 1989 takes us back to December of 1988.

Just like we did with LEAD(), in Query 4 we pass a negative value as the index and travel in the opposite direction, this time going forward in time with the LAG() function.

Query 4

SELECT
      [Measures].[Episode Count] ON COLUMNS
    , [Date].[Date Hierarchy].[Calendar Month].[1989-01].lag(-1) ON ROWS
FROM [Borg]
WHERE [Series].[Series Name].[Star Trek: The Next Generation]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Result 4

image

That takes us one [Calendar Month] member forward to February of 1989.

 

These two simple functions, LEAD() and LAG(), provide solid ways of navigating back and forth within the levels of ordered hierarchies. And because each one can travel in both directions, it allows for you to dynamically change direction as needed based on other criteria in the query. I urge you to play with these functions on your own.

Next time, we get a little more advanced with the ParallelPeriod() function.

Intro To MDX Decathlon–NON EMPTY and CROSSJOIN (7/10)

Since you have been following this series with the utmost, life-changing, interest, then you have most certainly read the previous post, Intro To MDX Decathlon – Hierarchy Navigation Basics (6/10). In that post, I used the NON EMPTY keyword and mentioned I would cover it in the next post. Tada! The next post is right here. This one will also cover the CROSSJOIN function.

NON EMPTY Keyword

The NON EMPTY keyword is used on a query axis, placed just before that axis’ expression. It tells MDX to eliminate from the results any axis members that are empty for the entire axis. For example, if place on the COLUMNS axis, any columns in the result for which all of the returned cells are empty would be excluded from being returned.

My Borg cube awaits.

In Query 1, we declare a named set called The Eighties made up of all ten years that appear in that decade. We then return the overall Episode Count of episodes that first aired during each of those years.

Query 1

WITH SET TheEighties AS
{
      (
      [Date].[Date Hierarchy].[Calendar Year].[1980]
      :[Date].[Date Hierarchy].[Calendar Year].[1989]
      )
}
SELECT
      [Measures].[Episode Count] ON COLUMNS
    , TheEighties ON ROWS
FROM [Borg]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Result 1

image

You will notice in Result 1 that the cells for 1980 thru 1986 have no contents, as shown by the (null). This may be the result you want to see. However, you may just want to see the years in which there actually were Star Trek episodes. There is a very easy way to do that. Your skills of predictive analytics likely already lead you to the answer.

 

 

 

 

 

 

 

 

 

 

 

In Query 2, we use the NON EMPTY keyword in front of the ROWS axis expression to tell MDX to eliminate the (null) cells from the Rows axis or our result.

Query 2

WITH SET TheEighties AS
{
      (
      [Date].[Date Hierarchy].[Calendar Year].[1980]
      :[Date].[Date Hierarchy].[Calendar Year].[1989]
      )
}
SELECT
      [Measures].[Episode Count] ON COLUMNS
    , NON EMPTY TheEighties ON ROWS
FROM [Borg]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Result 2

image

In Result 2, the (null) cells have been beamed out of our result and we only see cells that actually have values for the Episode Count measure.

 

 

 

 

 

This is a very basic usage of NON EMPTY. You will see later on, in one of my CROSSJOIN queries, that you can still get empty cells on the axis for which you have used the NON EMPTY keyword. It only eliminates members of the axis for each every cell on the axis is empty. If there is at least one that has a value, then that member of the axis will be returned. That will make more sense a little bit later.

I also want to make a very important point here. The NON EMPTY keyword is very easy to use. But it is also very easy to misuse. Make sure you fully understand how it will affect your result before you use it. There is also a function, NONEMPTY(), that can eliminate empty cells. That function is much more flexible in that it does not pertain to an entire axis. But usage of that, in my opinion, is outside the scope of an Intro to MDX. The key point I urge you to remember is that use of the NON EMPTY keyword or the NONEMPTY() function must be carefully considered. Understanding how they work is vital to making sure the results you get from your queries is correct and valid for your purpose. My example here is just the tip of the iceberg.

CROSSJOIN Function

The CROSSJOIN function takes as arguments two or more sets. It outputs a super set that is a cross product of every member of every set with every member of every other set. The order of the tuples in the resulting set is based on the order in which you provide the sets to the function as well as the order of the tuples within those sets.

NOTE: While all of the documentation (and examples) I can find indicates that CROSSJOIN takes SETS as arguments, there are instances when it accepts members instead. Both of my CROSSJOIN example queries (Query 3 and Query 4) pass members, not exclusively sets. You may ask how that can be. I have the same question. As of the time of this writing, I don’t know that answer. My search engine skills have thus far failed me on this. If you know the answer, I implore you to post a comment, or a link, or contact me in some fashion. Once I find that answer, I will write up a post that either explains it or points you to a location that does. Until then, just bear with me that it works.

Figure 1 shows a simple illustration of a CROSSJOIN between two sets.

Figure 1

image

Since Set A was listed first, its members are in the first position of the result. Likewise, since the order of the members within Set A is X and then Y, the X member’s CROSSJOIN results appear first. Notice two that the parts of the result from Set B appear in the order they exist in Set B.

There are three different ways of using a CROSSJOIN in your MDX.

Option 1

CROSSJOIN({<<Set A>>}, {<<Set B>>})

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

The first method is to just use the word CROSSJOIN and then list each of the sets, separated by commas, within parentheses.

Option 2

({<<Set A>>},{<<Set B>>})

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

The second is to list each of the sets, separated by commas, within parentheses, leaving off the word CROSSJOIN.

Option 3

{<<Set A>>} * {<<Set B>>}

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

The third is to list out each set and separate them with an asterisk. The asterisk, used in this way, is sometimes referred to as the CROSSJOIN operator.

MVP Chris Webb (b | t) has an excellent blog post on this topic: The rather-too-many ways to crossjoin in MDX. In that post, he examines each option and settles on Option 3 as his preferred method. He lays out great reasons for his choice. I highly recommend that post as well as following Chris’s blog. Having played with the CROSSJOIN options a bit, I agree with him completely. So, for this post, I will be using Option 3, listing out the sets to be cross joined, separating them with an asterisk.

Let’s head to my Borg cube to see CROSSJOIN in action.

CROSSJOIN Example 1: Basic CROSSJOIN

In Query 3, we are cross joining the members of the [Season].[Season Name] attribute hierarchy with the [Measures].[Episode Count] measure. The NON EMPTY keyword usage in Query 3 is just to make sure I am only returning Seasons that actually have episodes. My [Season].[Season Name] dimension ranges from 1 to 15 (Why did I do that?). Since no Star Trek series went into an eighth season, we stop at Season 7. The NON EMPTY is preventing the completely empty Seasons 8 – 15 from muttering up our results.

Query 3

SELECT
      NON EMPTY [Season].[Season Name].children 
        * [Measures].[Episode Count] ON COLUMNS
    , [Series].[Series Name].children ON ROWS
FROM [Borg]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Result 3

image

Notice how short and simple Query 3 is. Then notice how cool Result 3 is. MDX and CROSSJOIN rock. While you technically CAN produce results like this using T-SQL, it is going to require far more complexity than the straightforward, compact, MDX of Query 3.

By cross joining the seasons with the episode count, you can see that each column of the episode count for a different season. Since I put the series on the rows, we get, with a very simple query, the total episode count of every (live action) Star Trek series by season. Dig it.

You will note that, despite the NON EMPTY key word on the COLUMNS axis, there are empty cells in the result. Take a close look at the Season 04 column. Since that column has at least one value that is NOT empty, that column is returned. However, there is no column for Season 08 because ALL episode counts that for that column are empty.

CROSSJOIN Example 2: CROSSJOIN And The Autoexists Concept

When you use cross join to get the cross product of members of hierarchies in the same dimension, your result will only include cells that actually exist. That is due to the Autoexists concept you can read more about here. In Query 4, we are cross joining the members of the [Calendar Month] level of the [Date Hierarchy] with the [1966] member of the [Calendar Year] attribute hierarchy. Both of these hierarchies are in the [Date] dimension. This brings the Autoexists feature into play.

Query 4

SELECT
      [Measures].[Episode Count] ON COLUMNS
    , [Date].[Date Hierarchy].[Calendar Month].members
        * [Date].[Calendar Year].[1966] ON ROWS
FROM [Borg]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Results 4

image

You can see in Result 4 that we only get [Calendar Month] members that are actually in the year 1966 returned. Using CROSSJOIN in this way can accomplish great things for you if you play around with it a bit. Just be careful since creating cross products can quickly spiral out of control.

 

 

 

 

 

 

 

 

 

 

In this post, we looked at a very simple example of using the NON EMPTY keyword to eliminate null results across an entire axis. We also took a quick look at some simple examples of using the CROSSJOIN function. In the next post, we will take a look at doing a little time travel with the LEAD and LAG functions.

Intro To MDX Decathlon–Hierarchy Navigation Basics (6/10)

Hierarchies are a great asset to cube users. They allow them to drill down into increasing levels of detail with a click. Since hierarchies are so pervasive in cubes, it is pretty much guaranteed that folks who write MDX against cubes will need to perform hierarchy navigation at some point. There are many MDX functions that allow you to move up and down or side to side in hierarchies. Since this is an Intro, we will keep things pretty simple and just describe five basic ones, as shown in Table 1. For each function, I indicate what kind of object that function takes for input (argument) and also what kind of object it outputs (object returned). I do this so that you will know how to nest them properly in order to perform more advance logic. For example, if function A takes in a member and outputs a set and Function B takes in a set, you can pass an expression with Function A as the input to Function B.

Table 1

Function

Argument

Result

Description

.children
member set Returns a set of all members of the hierarchy on the level immediately below the member passed as the argument.
.parent
member member Returns the member of the hierarchy on the level immediately above the member passed as the argument.
.siblings
member set Returns a set of all members of the hierarchy on the same level as the member passed as the argument. Note that the member passed is also included in the resulting set.
.firstchild
member member Returns the first member (according to configured hierarchy sort order in the Cube) of the hierarchy on the level immediately below the member passed as the argument.
.lastchild
member member Returns the last member (according to configured hierarchy sort order in the Cube) of the hierarchy on the level immediately below the member passed as the argument.

 

Figure 1

imageFor this post, and for this section of my MDX Trek: First Contact presentation upon which this series is based, I have used an image I found on Flickr (Creative Commons licensed content). I call it the Star Trek Family, as seen in Figure 1. Since you can see that hierarchy navigation functions in MDX are based on that of a family tree, you can also likely imagine how delighted I was when I found this image. It is perfect for demonstrating the basic of hierarchy navigation. I want to note that, when I first found this picture, its URL was this. When I checked this URL again on 9/3/2012, it no longer exists. However, I want to thank AngieK47 for posting it way back when.

Smile

 

 

 

 

 

 

 

Since you have been following this series with such gusto, you will recall from Intro To MDX Decathlon – The Trouble With Tuples (3/10) that Analysis Services has ways to deal with omitted member references. The Partial Tuple Decision Tree in Figure 2 should be a good reminder.

Figure 2

image

.children

This function takes a member expression for its argument. It returns a set  of the members of the hierarchy on the level immediately below the level of the member passed.

imageSo, using the Star Trek Family image, let’s suppose we pass in Mom as the argument.

 

 

 

 

 

 

 

 

imageOur result is the set of Mr. Blue Shirt and Mr. Yellow Shirt. Note: I do not know for sure of those are the real names for these lads. But let’s go with it, k?

 

 

 

 

 

 

 

Let’s look at some examples from my Borg cube.

Attribute Hierarchy Example

In Query 1, we use my [Series].[Series Name] attribute hierarchy.

Query 1

SELECT
      [Measures].[Episode Count] ON COLUMNS
    , [Series].[Series Name].children ON ROWS
FROM [Borg]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Result 1

image

You will notice that Result 1 does not include the [All] member. Remember that the .children function takes a member expression as its argument. Instead, Query 1 passes it an attribute hierarchy. Behind the scenes, SSAS uses the Partial Tuple Decision Tree and finds that my [Series].[Series Name] attribute hierarchy has a default member, [All], and uses that to pass to the function instead. Since .children returns a set of all members of the hierarchy on the level immediately BELOW the member in the argument passed, Result 1 is a set of all member in my [Series].[Series Name] attribute hierarchy below the [All] member. The [All] member cannot exist on the level below itself.

Just to demonstrate this further, the only difference between Query 1 and Query 2 is that Query 2 actually specifies the [All] member as the argument for the .children function.

Query 2

SELECT
      [Measures].[Episode Count] ON COLUMNS
    , [Series].[Series Name].[All].children ON ROWS
FROM [Borg]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Result 2

image

Result 1 and Result 2 are identical.

User Hierarchy Examples

Using .children with a user hierarchy isn’t really very different. In Query 3, I use my [Date].[Date Hierarchy]. Note that Query 3 also uses the NON EMPTY keyword (in conjunction with a Slicer specifying the [Series].[Series Name].[Star Trek: The Next Generation] member) to limit the results just those date hierarchy members that have Episode Counts for Star Trek: The Next Generation. I explain the use of the NON EMPTY keyword in the next post in this series. I am just using that here in order to return a result set that takes less space to display.

Query 3

SELECT
      [Measures].[Episode Count] ON COLUMNS
    , NON EMPTY [Date].[Date Hierarchy].children ON ROWS
FROM [Borg]
WHERE [Series].[Series Name].[Star Trek: The Next Generation]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }



Result 3

image

Again, since I passed a hierarchy to .children, and not a member like it requires, the Partial Tuple Decision Tree comes into play. In this case, my [Date].[Date Hierarchy] has no default member. So, SSAS looks for an [All] member, which it finds. Since the level immediately below the [All] member in my [Date].[Date Hierarchy] is the level for [Calendar Year], the .children function returns a set of years. I won’t demonstrate passing the [All] member again, but it works here, too.

In Query 4, we pass in a particular member of the [Calendar Year] level of my [Date].[Date Hierarchy].

Query 4

SELECT
      [Measures].[Episode Count] ON COLUMNS
    , NON EMPTY [Date].[Date Hierarchy].[Calendar Year].[1987].children ON ROWS
FROM [Borg]
WHERE [Series].[Series Name].[Star Trek: The Next Generation]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Result 4

image

The level in my [Date].[Date Hierarchy] immediately below [Calendar Year] is [Calendar Quarter]. So, Result 4 returns the [Calendar Quarter] members under [Calendar Year].[1987] during which Star Trek: The Next Generation aired original episodes. It first aired in September of 1987, so the first two quarters of that year are filtered from the result set.

The .children function is great for building available values lists for Reporting Services multi-value parameters. Since SSRS builds the “Select ALL” option for you, you can keep the [All] member from confusing things by using .children to return just the distinct member values within the hierarchy.

.parent

This function takes a member expression for its argument and returns the member on the hierarchy level immediately above it.

imageSo, using the Star Trek Family image again, we pass in Mr. Blue Shirt.

 

 

 

 

 

 

 

 

 

imageThe result is Mom.

 

 

 

 

 

 

 

 

 

 

Let’s see some example of the .parent function from my Borg cube.

Attribute Hierarchy Example

In Query 5, we pass the [Star Trek: The Next Generation] member as the argument.

Query 5

SELECT
      [Measures].[Episode Count] ON COLUMNS
    , [Series].[Series Name].[Star Trek: The Next Generation].parent ON ROWS
FROM [Borg]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Result 5

image

My [Series].[Series Name] attribute hierarchy has only two levels. Level 0 is the [All] member and Level 1 underneath it has the set of each Star Trek series as members. So, when passing in a member series, we go up one level to the [All] member.

User Hierarchy Example

Query 6 passes a member of the [Calendar Quarter] level of my [Date].[Date Hierarchy] as the argument.

Query 6

SELECT
      [Measures].[Episode Count] ON COLUMNS
    , [Date].[Date Hierarchy].[Calendar Quarter].[1987-Q3].parent ON ROWS
FROM [Borg]
WHERE [Series].[Series Name].[Star Trek: The Next Generation]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Result 6

image

Since the level immediately above [Calendar Quarter] in my [Date].[Date Hierarchy] is the [Calendar Year], our result is the year in which [1987-Q3] took place: 1987.

.siblings

This function takes a member as its argument and returns the set of members that exist at the same level in hierarchy, including that member.

image

So, once again using the Star Trek Family image, we can pass Mr. Blue Shirt to the .siblings function.

 

 

 

 

 

 

 

 

 

imageOur result is the set of Mr. Blue Shirt and Mr. Yellow Shirt. They are both siblings that appear at the same level in the hierarchy as Mr. Blue Shirt.

 

 

 

 

 

 

 

To the Borg cube!

Attribute Hierarchy Example

Query 7 passes the [Star Trek: The Next Generation] series member as the argument to the .siblings function.

Query 7

SELECT
      [Measures].[Episode Count] ON COLUMNS
    , [Series].[Series Name].[Star Trek: The Next Generation].siblings ON ROWS
FROM [Borg]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Result 7

image

The result is the full set of all of the members at Level 1 of the [Series].[Series Name] hierarchy. Since the [All] member is Level 0, above all of these, it is not returned.

User Hierarchy Example

Query 8 passes the [1987-Q3] member, at the [Calendar Quarter] level of the [Date].[Date Hierarchy], as the argument.

Query 8

SELECT
      [Measures].[Episode Count] ON COLUMNS
    , [Date].[Date Hierarchy].[Calendar Quarter].[1987-Q3].siblings ON ROWS
FROM [Borg]
WHERE [Series].[Series Name].[Star Trek: The Next Generation]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Result 8

image

The result is the full set of [Calendar Quarter] members that have [Calendar Year].[1987] as a parent. Note that Query 8 did not include the NON EMPTY keyword, which would have suppressed the (null) results for [1987-Q1] and [1987-Q2].

.firstchild

This function takes in a member as an argument and returns the member on the hierarchy level immediately below it that comes first according to the hierarchy’s sort order configured in the Cube.

image

So, with our Star Trek Family, we can pass Mom as the argument.

 

 

 

 

 

 

 

 

imageTake a moment to look at the children in this photo. Mr. Blue Shirt appears to be a bit taller than Mr. Yellow shirt. He also seems to have an expression suggesting he may be tired of the Star Trek Vacations this family could take each year. With this in mind, I have decided that Mr. Blue Shirt is the older, and therefore First, child in the hierarchy.

Therefore, our result is Mr. Blue Shirt.

 

 

 

 

 

Resistance to my Borg cube is futile.

Attribute Hierarchy Example

Query 9 pass the [Series].[Series Name] hierarchy as the argument. The Partial Tuple Decision Tree comes into play and, behind the scenes, SSAS passes the default member of the hierarchy, [All], to the function.

Query 9

SELECT
      [Measures].[Episode Count] ON COLUMNS
    , [Series].[Series Name].firstchild ON ROWS
FROM [Borg]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Result 9

image

I constructed the members in my series dimension chronologically. So, the first member is [Star Trek: The Original Series].

User Hierarchy Example

Query 10 passes the [1987-Q4] member of the [Calendar Quarter] level of my [Date].[Date Hierarchy] user hierarchy.

Query 10

SELECT
      [Measures].[Episode Count] ON COLUMNS
    , [Date].[Date Hierarchy].[Calendar Quarter].[1987-Q4].firstchild ON ROWS
FROM [Borg]
WHERE [Series].[Series Name].[Star Trek: The Next Generation]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Result 10

image

Since my [Date].[Date Hierarchy] is sorted chronologically by Date (yeah, I was clever), and the level immediately below [Calendar Quarter] is [Calendar Month], our result is the first month that occurs within the fourth quarter of 1987: [1987-10] which is October.

.lastchild

This function takes in a member as an argument and returns the member on the hierarchy level immediately below it that comes last according to the hierarchy’s sort order configured in the Cube.

image

So, in the fabulous Star Trek Family image, we can pass Mom as the argument.

 

 

 

 

 

 

 

 

 

image

In looking at the image, you can see that Mr. Yellow Shirt is a bit smaller than Mr. Blue Shirt. You will also notice the outright glee on his face (whether it is due to the Star Trek Vacation itself or just the fact that his brother is miserable is up for debate). Either way, I have decided that Mr. Yellow Shirt is the youngest, and therefore Last, child.

Our result is Mr. Yellow Shirt.

 

 

 

 

 

Once more to the Borg cube, my friends.

Attribute Hierarchy Example

Query 11 pass the [Series].[Series Name] hierarchy as the argument. Again, the [All] member is used behind the scenes.

Query 11

SELECT
      [Measures].[Episode Count] ON COLUMNS
    , [Series].[Series Name].lastchild ON ROWS
FROM [Borg]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Result 11

image

The result, since my series members are order according to the order in which they first aired, is Star Trek: Enterprise.

User Hierarchy Example

Query 12 passes the [1987-Q4] member, just like in Query 10 above.

Query 12

SELECT
      [Measures].[Episode Count] ON COLUMNS
    , [Date].[Date Hierarchy].[Calendar Quarter].[1987-Q4].lastchild ON ROWS
FROM [Borg]
WHERE [Series].[Series Name].[Star Trek: The Next Generation]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Result 12

image

The result is the last month in the fourth quarter of 1987: [1987-12] which is December.

Hierarchy navigation is very important when dealing with MDX. Luckily, it does not also have to complicated. The basics are pretty straightforward. Stay tuned to the next installment in this series in which was discuss the NON EMPTY keyword and the mighty CROSSJOIN function.

Intro To MDX Decathlon–.members Function (5/10)

In the previous post in this series, Intro To MDX Decathlon – Sets: A Shirt of a Different Color (4/10), we defined a Set and also discussed the rules that valid sets must follow. In this post, we will introduce a function that can provide an easy way to form a set: .members.

The .members function returns a set of the members of the dimension, hierarchy, or hierarchy level provided. We will cover each of those three instances.

[<<Dimension>>].members

You can use the .members function directly on a dimension when that dimension has one and only hierarchy. When you do, you get a set that comprises all of the members of that hierarchy, in order according to the hierarchy’s configured sort order in the cube. Query 1 shows an example of calling the .members function directly on a dimension with only one hierarchy in my Borg cube. The [Series] dimension has only one hierarchy, [Series Name].

Query 1

SELECT
      [Measures].[Episode Count] ON COLUMNS
    , [Series].members ON ROWS
FROM [Borg]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Result 1

image

Notice that the result of the .members function on my [Series].[Series Name] hierarchy includes the [All] member. In a later post in this series, we will discuss a way to exclude the [All] member and just get the [Series Name] values themselves. 

[<<Dimension>>].[<<Hierarchy>>].members

Attribute Hierarchy

The only difference between Query 2 below and Query 1 is that we specify the name of the hierarchy whose members we want in our set. Although leaving off the hierarchy name, as in Query 1, is technically allowed when the dimension has only one hierarchy, I prefer specifying the hierarchy name anyway, as in Query 2. To me, this makes my MDX more complete and easier for another person to follow.

Query 2

SELECT
      [Measures].[Episode Count] ON COLUMNS
    , [Series].[Series Name].members ON ROWS
FROM [Borg]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Result 2

image

Notice the Result 2 is identical to Result 1. They both return the exact same set of members. You notice, also, that they both dealt with the same Attribute Hierarchy. Since you have been basing your life around the release of each new post in this series, you will recall from the first post, Intro To MDX Decathlon – Cube Space: The Final Frontier (1/10), that an Attribute Hierarchy is one that contains only a single Attribute. The [Series].[Series Name] hierarchy contains only the [Series Name] attribute.

User Hierarchy

If you call the .members function on a User Hierarchy, which is composed of more than one Attribute, you still get all of the members of the hierarchy in order, but now you have more levels to deal with. Query 3 shows an example of calling the .members function on my [Date].[Date Hierarchy] user hierarchy from my Borg cube. You will note the Query 3 also has a WHERE clause as well as the NON EMPTY keyword (which we get to later in this series). I did this so that the results returned by the .members function would fit into a tighter space so that you can notice the pattern of the returned members.

Query 3

SELECT
      [Measures].[Episode Count] ON COLUMNS
    , NON EMPTY [Date].[Date Hierarchy].members ON ROWS
FROM [Borg]
WHERE [Series].[Series Name].[Star Trek: The Next Generation]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Result 3

SNAGHTML337d89

Just as a reminder, my [Date].[Date Hierarchy] user hierarchy has the following structure:

Level Attribute Hierarchy
0 [All]
1   [Calendar Year]
2     [Calendar Quarter]
3       [Calendar Month]
4         [Full Date]

Result 3 still starts with the All member. The next member you see is [Calendar Year].[1987]. This is the FIRST member immediately below the All member that that meets the criteria. We then proceed through all of the members in 1987 before getting to 1988, which would make sense since my [Date Hierarchy] is in ascending Full Date order. Each path in the hierarchy is exhausted before going to the next path. So, every member in [Calendar Quarter].[1987–Q3] appears before anything in [Calendar Quarter].[1987–Q4]. Likewise, every member in [Calendar Month].[1987-09] appears before anything in [Calendar Month].[1987-10]. And so on. You can see that the results of using .members on a User Hierarchy will travel up and down the levels in the hierarchy in order to show every member of every level while still keeping everything in the sort order configured for this hierarchy in the cube. This behavior can be fantastic for some reporting scenarios. Just keep in mind that for very deep or populous hierarchies, this can return a lot of members. Use it wisely.

[<<Dimension>>].[<<Hierarchy>>].[<<Level>>].members

With User Hierarchies, sometimes you just want to work with the members of a particular level. When you use .members to do this, you just get the members of that level. Query 4 shows an example of doing this. The only difference between Query 4 below and Query 3 is that we are adding in the [Calendar Year] level before using the .members function.

Query 4

SELECT
      [Measures].[Episode Count] ON COLUMNS
    , NON EMPTY [Date].[Date Hierarchy].[Calendar Year].members ON ROWS
FROM [Borg]
WHERE [Series].[Series Name].[Star Trek: The Next Generation]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Result 4

image

You will notice that the [All] members is not returned. This is because the [All] member is not at the [Calendar Year] level of my [Date Hierarchy]. It is the level above.

Making that one, tiny change to the query from Query 3 produced vastly different results in Query 4. That is something to keep in mind when dealing with hierarchies in MDX. Little things can have a big impact.

 

 

 

 

 

 

 

In this post, we looked at the behavior of the .members function in different scenarios. An important thing to remember is that .members outputs a Set. So, you can pass a .members expression to another function that takes a Set as input in order to nest functions. Doing so can allow you to implement some very sophisticated logic in your MDX queries.

PASS Regional Mentor–Canada

I had mentioned in my most recent PD post, Professional Development Plans: Turning Your Job Into a Career, Part 2, that I wanted to jump back into SQL Community involvement and let PASS know of my desire to become a PASS Regional Mentor (RM). I was on the Executive Board for the Minnesota chapter of PASS, PASSMN, for three years and had a great time. After taking a few years off, I am ready and anxious to get back into the game. The RM program is a huge asset to both PASS local chapters and the PASS community as a whole. The roles of an RM are, as PASS Community Evangelist Karl Landrum (b|t) noted in her post on the PASS Blog, the following:

image

I heard from PASS Director Allen Kinsel (b|t) that PASS would like to have me on board as a Regional Mentor. Hazzah!!!! It is now official.

I will be helping Chris Shaw (b|t) with the mighty Canada region. I am really excited to get this opportunity to work with Chris and serve this community I care so much about.

To my Canadian friends:

1. Having grown up in New Hampshire before moving to Minnesota, I have never lived in a US State that did not border Canada.

2. Canada happens to be the only country outside the US to which I have traveled.

3. When I was growing up, my Dad would often watch Les Habitants on the French language channel.

4. I think Mike Myers is hysterical.

5. I studied French in school for 12 years. It was mostly written, though, so my conversational French is pretty poor. But I can tell you all about pencils, pens, dogs, cats, cows, pigs, coats, boys, girls, and colors. Oh, and skirts. I don’t wear them, myself, but I remember how to say it in French. Boats, too.

6. I remember watching the Winter Olympic Games in Calgary, thinking it looked pretty cool. I was only thirteen at the time.

7. My wife and I watched a whole big lot of the Winter Olympic Games in Vancouver and it was just amazing. When I told her that being a Regional Mentor for Canada was a possibility, she immediately told me, “If you end up going to Vancouver, I AM COMING. Period.” 🙂

8. Like The Kids in the Hall, I was in a TheatreSports improvised comedy troupe for three years.

Words can’t really explain how excited I am for this new role. I am constantly telling people about the vibrant, fantastic SQL Community. It’s going to be great to be an ambassador for PASS in a more formal capacity.

Intro To MDX Decathlon–Sets: A Shirt of a Different Color (4/10)

In the previous post in this series, Intro To MDX Decathlon – The Trouble With Tuples (3/10), we discussed how to reference a particular cell in a cube by using a Tuple. Now it’s time to group them together using a concept we haven’t discussed yet: the Set. Its basic definition is deceptively simple:

Set: A collection of tuples.

The basic syntax of a set is essentially a list of Tuples, separated by commas, surrounded by curly braces {}. You can see this in Figure 1.

Figure 1

image

As a concept, we can liken the concept of a Set to the uniforms within Starfleet in the Star Trek franchise. The members of Starfleet wore uniforms of a certain color depending on their role. In Figure 2, an excellent Lego depiction of the main characters of Star Trek: The Next Generation, you see Red, Blue, and Yellow uniforms.

Figure 2

image

Image URL: http://www.flickr.com/photos/dunechaser/163959411/sizes/z/in/photostream/

The characters are wearing shirts that are designed to group them together by their job function.

Essentially, the colors follow this pattern:

Red: Command

Yellow: Tactical, Security, Engineering (possibly considered Operations)

Blue: Science, Medical

Light Blue: Well… Wesley.

This grouping only works when there are properties shared amongst members of the group. Sets in the cube space also have to have some shared properties to be valid.

Prime Directives of Sets

Sets in MDX must follow the following rules to be valid.

Hierarchality: All Tuples in a set must reference the same hierarchies. This is essentially the Apples to Apples rule. Figure 3 shows an example of a valid Set. “Hierarchality” is also a great word to throw around at parties.

Figure 3

image

The Set in Figure 3 is valid because the members are both from [Hierarchy Y]. This Set is a good example of Hierarchality.

Figure 4 shows an example of an invalid Set.

Figure 4

image

The Set in Figure 4 is invalid because one member comes from [Hierarchy Y] and the other comes from [Hierarchy X]. These members do not have Hierarchality.

The code in Query 1, below, shows a set in my Borg cube.

Query 1

SELECT
      [Measures].[Episode Count] ON COLUMNS
    , {
          [Series].[Series Name].[Star Trek: The Original Series] -- Tuple A
        , [Series].[Series Name].[Star Trek: The Next Generation] -- Tuple B
      } ON ROWS
FROM [Borg]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Result 1

image

Dimensionality: All Tuples in the Set must reference the same Dimensions in the same order. This rule comes into play when you are declaring multi-part Tuples as members of the set.

Figure 5 shows an example of a valid Set.

Figure 5

image

The Set in Figure 5 is valid because the first tuple references [Hierarchy Y] and then [Hierarchy Z]. Likewise, the second tuple references [Hierarchy Y] and then [Hierarchy Z]. These tuples have the same Dimensionality.

Figure 6 shows an example of an invalid Set.

Figure 6

image

This Set is not valid because the first Tuple references [Hierarchy Y] and then [Hierarchy Z] while the second Tuple reverses the order of the hierarchies, referencing [Hierarchy Z] and then [Hierarchy Y]. These tuples do not have the same Dimensionality.

Query 2 shows an example of a Set created from multi-part tuples in my Borg cube.

Query 2

SELECT
      [Measures].[Episode Count] ON COLUMNS
    , {
          ([Series].[Series Name].[Star Trek: The Original Series]
            , [Season].[Season Name].[Season 01]) -- Tuple A
        , ([Series].[Series Name].[Star Trek: The Next Generation]
            , [Season].[Season Name].[Season 01]) -- Tuple B
      } ON ROWS
FROM [Borg]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Result 2

image

The Named Set

For readability and organization, you can also give your sets names. You declare a Named Set using the WITH keyword. Figure 7 shows the basic syntax.

Figure 7

image

Query 3, below, will return the same result as Query 1, but is a bit more organized by using a Named Set.

Query 3

WITH SET MyFavorites AS
{
      [Series].[Series Name].[Star Trek: The Original Series] -- Tuple A
    , [Series].[Series Name].[Star Trek: The Next Generation] -- Tuple B
}
SELECT
      [Measures].[Episode Count] ON COLUMNS
    , MyFavorites ON ROWS
FROM [Borg]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Result 3

image

Result 3 is identical to Result 1.

The Range Operator “:”

The Range Operator is immensely helpful in creating Sets when dealing with ordered hierarchies, which should be just about all of them. The Range Operator takes two arguments, the Starting Member and the Ending Member.

[Starting Member]:[Ending Member]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

It returns an ordered set of all member from the Starting Member to the Ending Member, inclusive of both. So, given a list of members [A],[B],[C],[D], the expression [A]:[D] would return the entire list of members.

Query 4 shows an example of a Named Set from my Borg cube made up of all of the members of the [Calendar Year] hierarchy in the 1980s decade.

Query 4

WITH SET TheEighties AS
{
      [Date].[Date Hierarchy].[Calendar Year].[1980] 
      ,[Date].[Date Hierarchy].[Calendar Year].[1981]
      ,[Date].[Date Hierarchy].[Calendar Year].[1982]
      ,[Date].[Date Hierarchy].[Calendar Year].[1983] 
      ,[Date].[Date Hierarchy].[Calendar Year].[1984]
      ,[Date].[Date Hierarchy].[Calendar Year].[1985]
      ,[Date].[Date Hierarchy].[Calendar Year].[1986]
      ,[Date].[Date Hierarchy].[Calendar Year].[1987]
      ,[Date].[Date Hierarchy].[Calendar Year].[1988]
      ,[Date].[Date Hierarchy].[Calendar Year].[1989]
}
SELECT
      [Measures].[Episode Count] ON COLUMNS
    , TheEighties ON ROWS
FROM [Borg]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Result 4

image

Since the [Calendar Year] hierarchy is ordered by the Year values, you can also use the Range Operator instead of specifying all ten years individually. Query 5 shows the same query logic as Query 4, but uses the Range Operator, resulting in much cleaner code.

Query 5

WITH SET TheEighties AS
{
      [Date].[Date Hierarchy].[Calendar Year].[1980] -- Start
      :[Date].[Date Hierarchy].[Calendar Year].[1989] -- End
}
SELECT
      [Measures].[Episode Count] ON COLUMNS
    , TheEighties ON ROWS
FROM [Borg]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Result 5

image

Result 4 and Result 5 are identical, even though Query 5 is much easier to read and is much shorter.

In this post, we introduced the concept of the Set and explained the rules around their use. We also discussed how to create a Named Set to make your MDX a bit more organized. We closed by showing how the Range Operator can be of great use in creating sets from within ordered hierarchies.

Intro To MDX Decathlon–The Trouble With Tuples (3/10)

In this third installment of my Intro to MDX Decathlon, we cover the hugely important Tuple.

Before we get into the details, I want to make a quick note about pronunciation. There is some controversy over how to properly say the “tup” in Tuple. In one camp, there are the folks that say it must rhyme with “cup.” The other camp insists it must rhyme with “loop.” I poked around a bit online concerning this topic and found roughly equal numbers of people in each camp declaring with absolute certainty that the pronunciation they prefer is the right one. I, personally, rhyme with “cup,” but make no claim of correctness. It just feels right to me and was the first pronunciation I heard. I don’t think it really matters. In a world in which we have television shows that start with “The Real Housewives of” ANYTHING, we have much bigger problems than how to pronounce this word.

When it comes to Tuples, a key concept to cover first is that of a cube Cell.

Cell: The logical intersection of a member of the Measures dimension with a member of each attribute hierarchy in the cube

Now that we have defined a cell, let’s look at one from my first post in this series, Intro To MDX Decathlon–Cube Space: The Final Frontier (1/10). Figure 1 shows four dimensions, each named with a letter, A thru D.

Figure 1

image

The circle in the center, highlighted in blue and a bit larger than the others, can be considered a cell. It is the intersection of all of the dimensions in the figure. We can refer to this cell according to its coordinates; the member of each dimension present at that location. You can see this in Figure 2.

Figure 2

image

We can use the coordinates of this cell as its name. This is just how it works in the cube as well. We reference a particular cell in the cube by specifying the members that intersect at that location. The coordinate name for that location is a Tuple. That brings us to the definition below.

Tuple: The unique coordinates of a cell within the cube space

One key thing to keep in mind about a tuple is that it cannot include more than one member of the same hierarchy. That makes perfect sense. Think of a Calendar Year attribute hierarchy. Can there be any date that is simultaneously in both 2010 and 2011? (Hint: No.) In addition, you can only reference each dimension once in the tuple.

To specify a tuple in MDX, you separate dimension references with commas and surround the entire structure with parentheses. So, a tuple of the cell in Figure 2 would look like this:

(
       [A].[3]
     , [B].[5]
     , [C].[7]
     , [D].[4]
)

 

NOTE: The particular style of having the comma at the front of each line, the line breaks, and the indentation in this series is just my preference. I find it the most organized and readable. You need not follow my exact formatting. As long as you have the commas and parentheses in the proper location relative to the dimension references, you can format it how you like.

Now, let’s look at a tuple from my Borg cube.

Query 1

SELECT
     (
          [Series].[Series Name].[Star Trek: The Original Series]
        , [Season].[Season Name].[All]
        , [Date].[Date Hierarchy].[All]
        , [Measures].[Episode Count]
      ) ON COLUMNS
FROM [Borg]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Result 1

image

Notice in the result above that all of the labels are returned on the Columns axis since that is the only one specified in the query.

Since you have been following this series with such rapt joy, you will have noticed that the Result above, 80, has the same value as one from the previous post in this series, Intro To MDX Decathlon–The Basic MDX Query (2/10).

That previous post included this query.

Query 2

SELECT
      [Measures].[Episode Count] ON COLUMNS
    , [Series].[Series Name].[Star Trek: The Original Series] ON ROWS
FROM [Borg]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Result 2

image

You will notice that both queries return a cell with the value of 80. It is actually the same cell that is just reached using a slightly different syntax.

What we see in Query 2 is an example of what is called a Partial Tuple. A partial tuple is a feature of Analysis Services that allows you to specify the parts of the tuple you wish to while letting SSAS take care of the rest. Note that when you are using a Partial Tuple and specifying only one hierarchy, as in Query 2, you need not use parentheses to surround the Tuple.

When we just specify just [Series].[Series Name].[Star Trek: The Original Series] on the Rows axis in Query 2, SSAS follows the decision tree in Figure 3 to determine how to properly identify the cell.

Figure 3

image

When I chose not to specify either the Season or the Date dimension in the partial tuple on the Rows axis in Query 2, SSAS looked to see if either of those had a Default Member. My Season Name attribute hierarchy has a Default Member specified in my Borg cube: The [All] member. So that member is used. My Date Hierarchy does not have a Default Member. But it does an [All] member. So that member is used. If neither a Default Member nor an [All] Member are present, then the first member found (according to the hierarchy sort order defined in the cube) is used.

The benefit of Partial Tuples is that it allows for cleaner code and less typing in many cases. This allows the following two queries return the exact same cell.

Query 3 (Full Tuple)

SELECT
     (
          [Series].[Series Name].[Star Trek: The Original Series]
        , [Season].[Season Name].[All]
        , [Date].[Date Hierarchy].[All]
        , [Measures].[Episode Count]
      ) ON COLUMNS
FROM [Borg]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Result 3 (Full Tuple)

image

Query 4 (Partial Tuple)

SELECT
     (
          [Series].[Series Name].[Star Trek: The Original Series]
        , [Measures].[Episode Count]
      ) ON COLUMNS
FROM [Borg]

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Result 4 (Partial Tuple)

image

In this post we introduced the concept of the Tuple, including defining a cube Cell. We also showed the power of Partial Tuples. In the next post, we really start having some fun with Sets.