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,\ [Meas...
Read More
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 a...
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...
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. Ove...
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 S...
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. Whe...
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 ...
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 ...
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 a...
Download Script 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 po...
Join SQL Server MVP, Andy Leonard, tomorrow on the SQL Lunch to learn some snappy tips and tricks on Transact-SQL development and deployment . 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: #25 – Transact-SQL Development and Deployment Add to Outlook : Add to Calendar Date : 7/14/2010 Speaker : Andy Leonard Join Meeting :...
With all the MSDN give away contests, I decided to add another. Here are the official rules for the twitter referral registration contest for SQL Saturday #28 in Baton Rouge, LA on August 14, 2010 on the campus of Louisiana State University. In case you haven't heard, SQL Saturday Baton Rouge has over 400 registrants as of early July. We're in Taylor Hall (formerly known as CEBA) on LSU campus. This isn't no community college, all of the rooms for SQL Saturday #28 feature stadium seating and mou...
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, M...
Join SQL Server MVP, Patrick LeBlanc, next week on the SQL Lunch to learn about Share Datasets and Report Parts in SSRS R2. 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: #23 – SSRS R2 (Shared Datasets and Report Parts) Date: 7/12/2010 Time: 11:30 AM CST Add to Outlook : Add to Calendar Speaker : Patrick LeBlanc, SQL Serv...
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 ...
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 sol...
MDX Puzzle #2 Solution Download Script 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...
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.