Month: October 2012

SQL Saturday Nashua (146) Recap

FenwayGateBOn October 20th, I had the pleasure to attend SQL Saturday 146 in my hometown of Nashua, NH. To speak in the vocabulary of my youth, it was wicked good. I have decided to have parts of this post be in Bostonian, the dialect of English that is common in a pretty wide radius around the fine city of Boston, MA. I will note the Bostonian sections by using the <Go Sawx!> tag at the beginning and the ending tag of </Go Sawx!>.

I know that Fenway Park is actually in Boston, but the majority of people in southern NH are Red Sox fans. In the interest of full disclosure, I have to come clean: I married a Yankees fan.

 

<Go Sawx!>

The Speakah Dinnah was on Friday night, and it was a wicked pissah. Meetin’ Mike Walsh (blahg|Twittah) and Jack Corbet (blahg|Twittah) for the first time was awesome. It is so much fun meetin’ people you have only chatted with on Twittah and stuff. I also got a meet a ton of new people and have a really good Seafood Samplah. It was a sweet time. And the SQL Satuhday hoodie all the speakahs got is spectaculah.

</Go Sawx!>

Saturday, I showed up early to help set up the PASS table and staff it with mighty Joe D’Antoni (blog|Twitter). As two PASS Regional Mentors, part of our job is to help out with the PASS table at events like SQL Saturdays. I spent pretty much all of my time there when I wasn’t in a session (or the bathroom, but we won’t go into that). I had a great time chatting with the attendees about PASS. The overwhelming majority of the attendees said it was their first SQL Saturday. It was fantastic to see the enthusiasm of people so new to SQL events.

I gave my MDX Trek: First Contact presentation during the first session of the day. It went well and I had good questions asked. It was really fun and I got some great feedback. Thanks to those who attended and endured my Star Trek nerdery.

I then sat in on Matt Masson’s (blog|Twitter) EIM – Bringing Together SSIS, DQS, and MDS session. I had not seen much of Data Quality Services before. That was pretty cool and great to see those tools put together to great effect.

I spent the third session hanging in the speaker ready room with Bill Pearson (Twitter), Andy Roberts (blog|Twitter) and Slava Kokaev (blog|Twitter). It was great to just sit and relax a bit and chat about Business Intelligence.

The fourth session of the day for me was with Bill Pearson in his Overcoming Barriers and Avoiding Mistakes With BI presentation. It was actually more of a group discussion format. That was cool and a refreshing change from the typical presentation format.

<Go Sawx!>

The aftah pahty was at a restarant called Mahtha’s Exchange (Martha’s, actually). I hung out with some really cool people and had killah appatizahs. It was low key and just really fun. Any chance to spend time with othah SQL people is a great time. Oh, and that Peanut Buttah Pie was frickin awesome, guys.

</Go Sawx!>

Mike and Jack put together one great SQL Saturday. The feedback from attendees and presenters alike was overwhelmingly positive. With the help of awesome volunteers like David Taylor (blog|Twitter) and so many others, SQL Saturday 146 in Nashua a great success.

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.