MDX Puzzle #4 - Solution

This puzzle may not have been much of a challenge, but it introduced two new MDX concepts.  I have to admit, one of my colleagues and I chatted about this puzzle, and I realized that my requirements were a little unrealistic.  As a result, I changed the requirements a little.  Instead of returning data between the Calendar Years of 2006 and 2008, the data should be filtered by Ship Date Calendar Year.

So, here is what I started with:

SELECT 

    NON EMPTY

    (

        [Ship Date].[Calendar Year].Children,

        [Measures].[Internet Sales Amount] 

    )ON COLUMNS,

        [Product].[SubCategory].Members ON ROWS

FROM [Adventure Works]

This satisfied all of the requirements except the filters, which are the reason for the post.  My first try at limiting the result set to date between Ship Date Calendar Year 2006 and 2008 resembled this query:

This query did work.  It only returned the years from my requirements, but I was sure there was a better way to accomplish this.  Notice that I included a set that implicitly limited the query for years 2006, 2007, and 2008.  What if I needed a larger range, say between 2000 and 2010?  No way am I typing all of that.  After a little digging I figured out how to perform the range operations (BETWEEN) in MDX.  Here is the query:

Using the ranger operator (:), which returns an ordered set based on the two specified member endpoints, I was able to return all members from the Ship Date Calendar Year between 2006 and 2008.  Next I needed to limit the result to only Product Categories that had sales greater than $1,000,000.  Thinking like a T-SQL programmer I went directly to a where clause using a greater than operator (>).  See the query below:

That did not quite work out for reasons that will be explained in later postings.  So, how did I get it to work.  I had to use the FILTER function to limit the result set.  The FILTER function returns a filtered set based on a search condition.  It accepts two arguments.  The first is a Set_Expression, which is an MDX expression that returns a set.  The second i a Logical_Expression that evaluates to true or false, which could be considered the WHERE clause.  Here is the final query:

The FILTER function is used on the Rows axis in the query to return on the Product Subcategories where Internet Sales Amount is greater than $1,000,000.  As I mentioned earlier, I modified the requirements a little because my initial set did not make logical sense.  Stay tuned for Puzzle #5.

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS

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

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

2010 3rd Quarter Goals

Now that we are into the third quarter of 2010 I decided to post an update of my 2nd quarter goals and post goals for the 3rd quarter.  If you all have been following my blog or me on twitter (patrickdba) you know that I was selected as a SQL Server MVP for the first time this year and that the SQL Lunch is doing great.  I am really excited about both and I can't wait for you all to see the great Lunches that are coming up in the next few months.  So now onto my goals. 

In the second quarter I met all of my goals with the exception of Submitting 2 videos to SQL Share.  I have decided to push that to the top of my list of priorities and I have also increased the number of videos to 6.  This is to make up for my lack luster performance regarding this goal in the first two quarters.  So goal number 1 is:

1.  Submit 6 videos to SQL Share.  This may seem like a big commitment, but I already have the topics ready to go.  I think I can do it.

Since I have been working for Pragmatic Works a goal of mine is to learn everything I can about SSAS.  A big part of SSAS is MDX.  Again if you have been following my blog you will know that I have a series of MDX puzzles.  So goal number 2 is:

2.  Learn MDX, Learn MDX, Learn MDX.

The first two will take up a large part of this quarter.  In the last quarter a lot of my time was spent writing a book(Knight's Microsoft Business Intelligence 24 Hour Trainer) and speaking.  I always set a goal to speak at least three times a quarter. Well in the second quarter of this year I spoke 13 times.  When I went through the list I could not believe it, but I did.  This quarter I have given five presentations.  I have about 3 more scheduled, and I will be speaking on the SQL Lunch in August.  Not quite sure I should make this a goal since I have already met it, but I will add it anyway:

3.  Speak at least three times.

Now, back to the book.  The book was a challenge, not really a goal, but definitely an accomplishment.  I hope to write another one real soon.  I am a co-author along with Brian Knight, Devin, Knight, Mike Davis and Adam Jorgensen.  If you are getting started with Business Intelligence check it out.  Again, back to the goals.  It was a challenge to write a couple of articles while writing the book at the same time, but I did get it completed.  I wrote exactly two articles, which was the goal for the 2nd quarter.  I have removed the article writing as a goal.  I am working on a few other things that will take up some time and I felt this could take a break for now.

The final three goals are as follows:

4.  Schedule SQL Lunches for the remainder of the quarter, which I have done.  I will be posting them within the next couple of days.

5.  Blog at least twice a week, which thanks to Steven Jones, I have been doing.  Every we talk he talks about consistency.  I have actually given a short presentation on speaking and blogging.  I am working on making it a full hour presentation.

6.  Submit three abstracts to the PASS conference.  I did submit three and two were declined and one was selected as an alternate.

All three of these were goals for quarter number two and I succeeded.  This was a really good quarter for me.  For the third quarter I have added a few new goals:

7.  Network and meet as many people as a I can.  So, if you plan on attending PASS or any of the SQL Saturdays in my region you may just happen to run into me shaking hands like a politician.  :)

8.  Add more functionality to the SQL Lunch website. (Searching, Speaker Pages, etc...)

8.  Become a more active PASS member by volunteering more.

The second half of this year is going to be busy with SQL Saturday #28 in Baton Rouge, PASS and a couple of more surprises that I will announce later in the month.  Stay tuned.

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS

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

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

MDX Puzzle #4

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

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

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

\

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

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS

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

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

SQL Saturday Wiki

This past weekend I had the opportunity to hangout with some of the brightest upcoming SQL Server Professionals and a couple of old-timers.  We were gathered in Orlando, FL by Andy Warren to work on the SQL Saturday Wiki.  Essentially, the plans were to improve on the information that he had collected about organizing and running successful SQL Saturday events.  We were locked in a room all day Saturday and our brains were drained of any information we had obtained from running our own events. 

Overall I thought this was an excellent idea, not only because I had the opportunity to travel to Orlando, but anytime you place smart people together good things are bound to happen.  We arrived at Andy's office around 8:30 am and we started working around 8:32 am, no kidding.  We discussed almost every aspect of coordinating a SQL Saturday.  At times we veered off the path, but these were very short excursions because Andy and Jessica Moss would get us quickly back on track.  Including Jessica and myself, the team also consisted of Scott Gleason, Jack Corbett, Greg Larsen, Jorge Segarra, and Sri Sridharan.  We spent most of the day discussing and documenting our events and sharing any ideas, thoughts, best practices, and challenges that were faced during the event planning process.  All of this information is available at http://wiki.sqlsaturday.com/. 

So, if you are planning on coordinating a SQL Saturday event in the near future check out the wiki.  We will be updating it on a regular basis.  If you find any problems with the content or have any thoughts to add  please feel free to email me at pleblanc@pragmaticworks.com.  Remember, this is a work in progress so be patient as we improve and expand upon the site. 

Patrick LeBlanc, SQL Server MVP, MCTS

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

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

SQL Server 2008 R2 for the DBA Recording and PowerPoint

I recently had the opportunity to do two presentation on SQL Server R2 for the DBA.  In the first session there were approximately 150 attendees and at the second there were approximately 400 attendees.  The recording is available here and you can download the slide deck here.  I would like to correct something I said about the Utility Control Point and Data-Tier applications.  They are only available in SQL Server 2008 with Service Pack 2, SQL Azure and SQL Server 2008 R2. Service Pack 2 for SQL Server 2008 CTP is available.  There are some limited capabilities in SQL Server 2005 and SQL Server 2008 pre service pack 2.

Thanks to all that attended the sessions and if you have any questions regarding the presentation please feel free to email me at pleblanc@pragmaticworks.com.  You can also follow me on twitter, @patrickdba.

Here are a few good references:

http://msdn.microsoft.com/en-us/library/aa833292.aspx

http://msdn.microsoft.com/en-us/library/ee210548.aspx

http://msdn.microsoft.com/en-us/library/ee240739.aspx

Patrick LeBlanc, SQL Server MVP, MCTS

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

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

.NET Speaker Idol, Guess who won?

Last night (July 21, 2010) I had the honor of being a speaker at the Baton Rouge .Net User Group.  This wasn't their normal meeting.  It was their 3rd Annual Speaker Idol.  The rules are as follows:

1.  There are three judges that get to ask questions and settle tie breakers.

2.  Each speaker has 15 minutes to speak.

3.  The audience is not allowed to ask questions.

4.  At the end of all the presentations the audience votes for the winner.

The prizes were an MSDN subscription and a $75 gift card.  When I arrived I found out that I was the last speaker of the night.  I really wanted to be first so I tried to convince them to change the order, but they would not.  Maybe they were saving the best for last.  There were over 40 people at the meeting and only two from our local SQL user group.  So the card were stacked against me.

After listening to the first four presentations,

Al Manint: End of the Free Lunch - Parallelism options in 4.0

Neil Martin: Resharper the TAO of the Code Ninja

Cody Gros: Powershell for Total Morons

Wayne Losavio: Getting Started with Sketchflow

I thought, there was no way I had a chance at winning this thing.  Nevertheless, I was game.  My presentation was titled, Speaking and Blogging.  I decided to break the monotony a little by delivering a non-technical presentation.  I almost changed it to a short technical presentation on Data-tier applications, but I decided against it and rolled on.

At the end of my presentation I still didn't think I had a chance, because the other 4 presenters gave really good technical sessions that delivered some good "food for thought"  in the time allotted.  We were all called to the front after I completed my session, then two people were asked to sit down.  To my surprise, I was still standing.  Then we were told that there was a tie for second place.  I thought, cool I came in second place, but I was wrong.  There was tie between the two remaining .Net presenters and I had WON!

While I was a excited by the fact that my presentation beat out the more technical ones, I found out that I won because I delivered my presentation in a way that was more engaging.  So it really wasn't about was I was saying, but more about how I said it.  Regardless of how I won, I won.  In the end, I graciously rescinded my victory and I gave my prize back to the community.  I have decided to expand this presentation and make it into an hour presentation.  I submitted a session to SQL Saturday #49 in Orlando, FL.  Hopefully it will get accepted.

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS

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

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

SQL Server 2008 R2 for the DBA Live Meeting

Tomorrow at 11:00 AM EST I will be giving a talk on SQL Server 2008 R2 for the DBA.  Here is the description:

Microsoft SQL Server 2008 provides a number of enhancements and new functionality, building on previous versions. Administration, database maintenance, manageability, availability, security, and performance, among others, all fall into the roles and responsibilities of the database administrator.  Patrick Leblanc will go over the new features and functionality that will impact the DBA.  If you have some time drop by the session, you can register here:

https://www1.gotomeeting.com/register/347832137

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS

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

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

Practice Your PASS Session on the SQL Lunch

We at the SQL Lunch would like to extend an invitation to all of the PASS speakers.  If you are looking for a place to practice your PASS session please email us at webmaster@sqllunch.com.  Don’t worry, you can have up to 90 minutes or you can break your session into small 30 minute sessions.  It’s completely up to you.  This is our way of saying thanks to all of you for giving your time and knowledge to all of us in the SQL Community.

Also, it is completely up to you if the sessions are recorded and if any materials are shared.  Thanks again.

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS

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

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

MDX Puzzle #3 Solution

Sorry that it’s taken me so long to write this post, but I have been a little busy with the SQL Lunch.  I am back now, so let’s solve this puzzle.  This puzzle introduced a new keyword, WITH, that can be used to create a calculated member that is only available for a single MDX query.  Note that, after the query is finished executing the calculated member no longer exists.  Also, introduced are three functions ROOT, AGGREGATE, and TOPCOUNT, which will all be explain later in this posting. 

So, here is what I started with.  This query satisfies these requirements:

1.  Internet Order Quantity

2.  Product

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

One thing that is new is the use of TOPCOUNT in the Rows section of the query.  TOPCOUNT behaves similar to TSQLs TOP(), but it requires three arguments instead of one.  It requires a Set Expression, which is a valid MDX expression that returns a set.  In this example, the set is all the Children of the product dimension.  The next argument is the Count, which is the number that specifies how many tuples will be returned.  Finally, it expects a Numeric Expression, which is the value that will be used to determine which set is returned.  This value is typically an MDX expression that returns a value.  The TOPCOUNT sorts the set in descending order and returns the specified number of elements (the Count argument) with the highest values (the Numeric Expression).

Now let’s create the calculated member.  Here is the query that was used:

You will notice the use of the WITH keyword that was explained earlier and the MEMBER clause.  Two calculated members are created in the above query.  The first uses the AGGREGATE() and ROOT() functions to calculate the total of all Internet Orders.  The second performs simple division, dividing Internet Order Quantity by the calculated total from the first member to obtain the Percentage of Total Orders.

The AGGREGATE and ROOT() are two new functions in this series.  The AGGREGATE function accepts two arguments.  The first is a Set_Expression, which in this example is ROOT().  The second argument, which is optional, is a Numeric_Expression.  The Numeric_Expression is typically an MDX expression that returns a numbers.  For this example, the Internet Order Quantity measure was used.  the ROOT() function was for the Set_Expression because it returns ALL member from each attribute hierarchy in the cube.  As a result, the total Order Quantity for the entire cube will be returned.  One thing to note about the ROOT() function is that you can limit its results by passing either a Dimension or Tuple Expression as an argument. 

Now to complete the puzzle, take the calculated members MDX query and paste it directly above the SELECT statement.  Then add the Percentage of Total calculation to the ON COLUMNS section of the SELECT statement.  Finally, you will have the solution.  See the query below:

One thing that I have realized is that, just like T-SQL, there are several ways to solve a query with MDX.  Once I have finished the journey of mastering the art of writing MDX, I will begin down the path of performance tuning and writing efficient MDX queries.

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS

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

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

Speaking at Greater New Orleans .Net User Group (GNO.NET)

This week I will speaking at the Greater New Orleans .Net User Group.  My topic is Introduction to the SQL Server Profiler.  If you want to learn some tips and tricks that you can use when trying to identify performance problems with your SQL Server stop by the meeting.  Also, I will be giving away an MSDN subscription at the end of my presentation.  Here are the meeting details.

Meeting URL:  http://tinyurl.com/0710-gno-net

Time:  6:30 PM CST

Location:  New Horizons, 2800 Veterans Memorial, BLVD, Metairie, LA 7002

Hope to see you all there. 

P.S.  I will be giving away a free MSDN subscription to one lucky participant.

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS

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

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

Free Data Warehouse Training – Let's Get Dimensional

Join Adam Jorgensen, tomorrow on the SQL Lunch to learn about Dimensional Modeling.  Go to SQL Lunch and add this event to your calendar or use the link in this posting.  To receive notifications about upcoming SQL Lunches please go here.  Every week this month we will be hosting a lunch time meeting. 

Title:  #27 - Let's Get Dimensional

Add to Outlook:  Add to Calendar

Date:  7/22/2010

Speaker:  Adam Jorgensen

Join Meeting:  https://www.livemeeting.com/cc/usergroups/join?id=Z9N87J&role=attend

Description: Learn the basics or dimensional modeling, the foundation for any good data warehouse, with Adam Jorgensen on SQLLunch. Adam will take us through why how dimensional modeling is different from your typical applications database, why it’s different, and some mistakes to avoid. We will review some existing models and take your questions on you modeling challenges. We’ll review common scenarios and discuss why they are the best approach.

BIO:  Adam Jorgensen , MBA, MCDBA, MCITP: BI has over a decade of experience leading organizations around the world in developing and implementing enterprise solutions. His passion is finding new and innovative avenues for clients and the community to embrace business intelligence and lower barriers to implementation. Adam is also very involved in the community as a featured author on SQLServerCentral, SQLShare, as well as a regular contributor to the SQLPASS Virtual User Groups for Business Intelligence and other organizations. He regularly speaks at industry group events, major conferences, Code Camps, and SQLSaturday events on strategic and technical topics.

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS

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

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

Free SSIS Training – SQL Lunch #24 (Looping in SSIS)

Join Brad Schacht this week on the SQL Lunch to learn about Looping in SSIS.  Go to SQL Lunch and add this event to your calendar or use the link in this posting.  To receive notifications about upcoming SQL Lunches please go here.  Every week this month we will be hosting a lunch time meeting. 

Title:  #24 Looping in SSIS

Add to Outlook:  Add to Calendar

Speaker:  Brad Schacht

Join Meeting:  https://www.livemeeting.com/cc/usergroups/join?id=SBMB3K&role=attend

Description:  In this session Brad will walk you through the loops available in SQL Server Integration Services. Topics to be covered include the ForLoop and the value it provides, as well as the most common uses for the ForEach Loop; such as looping over files. Setup and configuration will be discussed along with when a loop should be used. We will also discuss how to use these loops to dynamically name a file for archiving after it is done being used inside the package.

BIO:  Brad is a BI Consultant and Trainer for Pragmatic Works. His experience on the Microsoft BI platform includes DTS, SSIS, Reporting, and migrations and conversions. His background in creating custom solutions for clients and partners provides great experience for delivering real-world value through his courses. Brad uses this experience to make the topics real for those he’s working with and teaching. Brad also participates as a speaker at events such as SQL Saturday and is an active member of the Jacksonville SQL Server User’s..

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS

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

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

MDX Puzzle #3

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

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

Rows: Product

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

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

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

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

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS

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

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

MDX Puzzle #2 Solution

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

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

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

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

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

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

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

CREATE MEMBER [Adventure Works].Measures.YearlyGrowth 

AS 

'

CASE 

When IsEmpty

(

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

)

Then Null

When IsEmpty

(

    [Internet Sales Amount]

)

Then Null

 ELSE

(

    (

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

    )

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

)

END',

FORMAT_STRING='Percent';

SELECT 

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

    } ON COLUMNS,

    NON EMPTY(

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

FROM [Adventure Works]

WHERE

    (

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

        [Product].[Category].[Bikes]

    );

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

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS

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

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

SQL Server MVP - Are you kidding me?

I received an email today stating that I had been selected as a Microsoft SQL Server MVP for 2010.  This is my first MVP award and I am elated to become part of such a distinguished group.  What a great community!!!!

Thanks to all those that nominated me and thanks to the MVP team!

Talk to you soon,

Patrick LeBlanc, SQL Server MVP

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

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