MDX Puzzle #2

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

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

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

 

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

Talk to you soon,

Patrick LeBlanc

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

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

SQL Saturday #28 (Baton Rouge) – All about User Groups

This year at SQL Saturday #28 we will be featuring a User Group Booth alongside the Sponsor booths. The User Group booth will provide information about various User Groups that host meetings. If you are attending SQL Saturday and are a part of a group that would like to be involved you can either provide a handout that can be left at the booth for other attendees or contact Stacy Vicknair at stacy.vicknair@sparkhound.com with your group information including the group name, website, description and a primary contact. 

No matter the size of your User Group, how often you all meet or whether your group virtual, we will be happy to have you as part of this event.  Also, if you are interested in starting a new User Group we suggest that you send your information and we definitely place it in the booth.  This is our community so let’s grow it together.

Register Here

View Schedule 

The SQL Saturday #28 Team

MDX Puzzle #1 Solution

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

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

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

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

SELECT *

FROM [Adventure Works]

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

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

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

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

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

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

Download Script

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

MDX Puzzle #1

Recently, I embarked on a mission to become proficient at writing MDX queries.  This is a challenge, as many of you may already know.  MDX, though a query language, is very different from T-SQL as I quickly found out.  Each week I will share with you a puzzle and then a couple of days later I will share my solution.   Please don’t post your solutions on this blog post.  Only post them on the Solution post, which will contain Solution in the title. Each solution post will contain a section on terminology and concepts if any new ones are introduced and the MDX query that is the solution to the puzzle.  In this first posting there will be a few.  The puzzles will start out very basic, but will move to more advanced MDX techniques as time progresses and my skills improve.  I will be using the Adventure Works 2008 R2 cube, which can be downloaded from codeplex.  So, let’s get started.

My first puzzle was to write what I thought was a very simple query.  Here is the puzzle:

Show:  Product Internet Sales and Order Quantity Measures Rows:  Product Categories and SubCategories Columns:  Ship Years Filter:  Only Show Men from Customer Dimension and Exclude NULL values Hint:  You will need a tuple for year and measures and for the rows.

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

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

Talk to you soon,

Patrick LeBlanc

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

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

SQL Saturday #28 (Baton Rouge) Announcements

Register:  Click Here

Submitted Sessions:  Click Here

Twitter:  @SQLSatBR

On August 14, 2010 at Louisiana State University SQL Saturday #28 will be held.  If you have yet to attend a SQL Saturday you should try to make this event.  Now I know what you are thinking, Baton Rouge in August?  Well, before you make a quick decision about not attending take a look at everything that will take place.

Breakfast Tracks - SQL and Beignets (All Novice or Beginning Level)

At 7:30 AM we will have three sessions.  All three sessions will be introduction, novice or 100 level.  If you are new to SQL Server or just getting started or trying to get started then these sessions are for you.  You can learn from the best in the industry two SQL Server MVPs and a Business Intelligence Expert.  Here are the three sessions:

Presenter	Session Title
Steve Jones	Basic SQL Server
Andy Leonard	Build Your First SSIS Package
Barry Ralston	Breakfast Basics, SSAS Cube Creation

So if you want to learn about SQL Server, SSIS or SSAS join us for some coffee or juice, beignets and three great presentations.

Keynote by Steve Jones

Do you want a chance to meet one of the co-founders of SQLServerCentral.com and SQL Saturday? Then here is your chance.  Join us for a great keynote by Steve Jones, MVP (Steve’s talks are always entertaining and this one shouldn’t be any different). 

Four SQL Tracks and Three .Net Tracks

We have worked hard on finding some of the brightest SQL and .Net speakers.  Fortunately for us, it wasn’t very difficult.  We have over 80 sessions submitted.  Now that we have closed the call for speakers we will be spending time putting together a great schedule.  With so many sessions we have decided to have a minimum of seven tracks with six sessions each.  That will give us at least 56 sessions plus the 3 breakfast beginner sessions.  We are considering adding two more tracks, so stay tuned to the SQL Saturday site for updates.

Host of Microsoft MVPs

Finally, we have commitments from 13 Microsoft MVPs for this event.  Not taking anything from our non-MVP speakers because all of our speakers are extraordinary, but we are excited to have so many MVPs at this SQL Saturday.  Each track (SQL and .Net) will include MVP speakers. 

Microsoft .NET Framework from Scratch

Come see this session that Keith Elder did in a pre-TechEd conference.  The cost of this session was $400-$500. Space is limited so please sign up ASAP.  This session is a part of our 2010 Baton Rouge .NET and SQL Saturday. You will need to register in two places if you want to attend this session, SQL Saturday #28  and Event Brite SQL Saturday Baton Rouge.

This seminar is for anyone who is starting at ground zero with .NET and wants a deep dive into the platform starting from scratch. It is designed for developers experienced in at least one other language, and starts with the basics of . NET and covers Microsoft Visual Studio, writing code in C#, and how to build applications in various technologies of the platform such as Windows, Web, Microsoft Silverlight, and Windows Mobile.

So if you have the time, come out and network with some of the smartest SQL Server and .Net professionals in the country.  I have had the opportunity to attend sessions from most of our speakers and I have to say you will definitely learn something if you attend. 

Talk to you soon,

Patrick LeBlanc

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

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

SSRS R2 - Naming Excel Worksheet Tabs

I was recently asked if it was possible to name an excel tab based on a page in a reporting service report.  Honestly, I did not know if it was possible.  My initial response was, I don’t think so.  Not being 100% sure, I started searching the web and I stumbled across the What’s New (Reporting Services) site.  The site provides a great list of all the new features in SQL Server Reporting Services (SSRS) R2.  Not too far down the list I saw the subtitle, Naming Excel Worksheet Tabs.  So, how do you do it?

In this example (download my example), I used a report that has a grouping on Countries and Stores.  Each country will represent the title for each tab in the Excel spreadsheet. 

To start, right click on the Country grouping in the Row Groups section of the Report Designer.

Select Group Properties from the dialogue box that appears and the following screen will open.

Select Page Breaks from the left navigation section and on the Page Break Options screen check the box labeled Between each instance of a group.  Then on the Business Intelligence Development Studio (BIDS) menu bar choose View –> Properties.  The properties window will open in your development environment.  Back in the Row Group section click Country, then in the properties window expand the Group Property. 

In the PageName property you will type the expression of the value that will represents the tab names.  In this example I have decided to use the Country field as the name for each tab.  Click the drop down in the PageName property textbox and choose expression.  In the Expression window select Fields from the Category Section and double click Country in the Values section.  You screen should resemble the following screenshot:

Now preview your report and export it to excel.  Once you open it you will notice that you have a tab for each country.

Download Sample Project

As always if you have any questions or concerns regarding this post please feel free to email me at pleblanc@pragmaticworks.com. 

Talk to you soon,

Patrick LeBlanc

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

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

Parameterized Report using a Stored Procedure

Download Sample Project: Parameterized Report

In a recent forum post on BIDN.com a person asked how to filter the result on the report based on a stored procedure parameter. Since a short blog post without screen shots wouldn’t fully explain how to accomplish this, I decided to write a quick post to assist the person. I did a quick search on the web, and I found a few results but nothing that I thought was a good beginner posting. So here is my version.

The Stored Procedure

First you start with a couple of stored procedures. One stored procedure (Script 1-1) will provide the data set for the report. The second stored procedure (Script 1-2) will the act as the source for the parameter. The second stored procedure is not required, but at think it adds to the usability of the report.

USE AdventureWorks2008 GO IF(OBJECT_ID('dbo.GetStoreSales')) IS NOT NULL DROP PROC dbo.GetStoreSales GO CREATE PROC dbo.GetStoreSales @StoreID int AS SELECT s.Name StoreName, soh.OrderDate, soh.TotalDue, soh.PurchaseOrderNumber FROM Sales.SalesOrderHeader soh INNER JOIN Sales.Customer c ON soh.CustomerID = c.CustomerID INNER JOIN Sales.Store s ON c.StoreID = s.BusinessEntityID WHERE c.StoreID = @StoreID

Script (1-1)

USE AdventureWorks2008 GO IF(OBJECT_ID('dbo.GetStores')) IS NOT NULL DROP PROC dbo.GetStores GO CREATE PROC dbo.GetStores AS SELECT BusinessEntityID, Name FROM Sales.Store

Script (2-1)

The Report

Now that the stored procedures are created let’s create the report. Using Business Intelligence Development Studio (BIDS) create new report project. Add a share data source to the project. In this example the data source will connect to the AdventureWorks2008 database. Next add a new report to the project. The solution explorer should look similar to Figure 1-1.

Figure 1-1

Now you must create two data sets. First, on the Report Data tab add a new data source using the Share Data Source that was created earlier. Then right-click on the new data source and select Add Dataset. The Dataset Properties window will appear. Name the data set Stores, select the radio button labeled stored procedure and choose the GetStores stored procedure from the drop down list. Your Dataset Properties screen should resemble Figure 1-2.

Figure 1-2

Repeat the above steps that were used to create the Stores data set to create the second data set. The only difference is that you will choose GetStoreSales from the stored procedure drop down list and name the dataset GetStoreSales. Since the stored procedure contains a parameter a parameter is automatically added to your report. Expand the Parameters folder and you will notice a StoreID parameter in the listing (Figure 1-3).

Figure 1-3

Double click the parameter and the Report Parameter Properties window will appear (Figure 1-4)

Figure 1-4

Change the value in the textbox labeled Prompt to Choose Store. Click Available Values in the left navigation pane. Select the radio button labeled Get values from a query. Then select Stores from Dataset drop down list, select BusinessEntityID from the Value field drop down list and select Name from the Label fields drop down list. Click OK. The screen should resemble Figure 1-5.

Figure 1-5

Now click the Toolbox tab and drag a table onto the Report design surface. Click back on the Report Data tab drag the columns from the StoreSales dataset onto the table that you want to include in your report. Once you have added all the columns Preview your report. You will notice a drop down list at the top of the report labeled Choose Store. Select the store that you want to filter the report by and click the button labeled View Report. The table will contain only data for stores that match the selected value in the drop down list (Figure 1-6).

Figure 1-6

I know that this may be obvious or common place for most of us. However, for those of you just getting started with reporting service I hope this helps you out. As always, if you have any questions or comments about this posting please feel free to email me at pleblanc@pragmaticworks.com.

Talk to you soon.

Patrick LeBlanc, MCTS

Founder http://www.tsqlscripts.com/ and http://www.sqllunch.com/.

Visit http://www.bidn.com/, Bring Business Intelligence to your company.

SQL Lunch Events

We are scheduling more and more events daily on the SQL Lunch.  Just as a reminder here are a few of the upcoming events that will be broadcasted soon on the SQL Lunch.

SQL Lunch #22

Date and Time: 6/04/2010 11:30 AM CST

Event URL:  https://www.livemeeting.com/cc/usergroups/join?id=GR2N9J&role=attend

Presenter:  Thomas LeBlanc

Topic:  Adding Columns to a Large Table with Transactional Replication and Database Mirroring

Description:  SQL Server 2005 updated replication with the ability to have schema changes replicated to subscriptions. This seemed to be a great solution to custom software releases. You could ALTER a table and the changes would magically appear on the subscriber. Life was great. Also in 2005, database mirroring came along to help with High Availability to a Disaster Recovery site. Life got even better. The problem is with large tables and Tlog heavy changes. This is a step by step process we use to release software to these large tables.

SQL Lunch #23

Date and Time: 7/12/2010 11:30 AM CST

Event URL: https://www.livemeeting.com/cc/usergroups/join?id=TR5QJM&role=attend

Presenter:  Patrick LeBlanc

Topic:  SSRS R2 – Shared DataSets and Report Parts

Description:  SQL Server Reporting Services 2008 R2 introduced two new capabilities. Developers now have the ability to publish Data Sets and Report Parts (tables, charts, etc) as items that can be used by individuals building reports using Report Builder 3.0. In today’s session Patrick LeBlanc will show you how to create Shared Data Sets and Report Parts. He will then explain how to publish these items out to a Report Server. Using Report Builder 3.0, he will also show you how your end-users can use these items when building their own reports.

If you have time, try to attend these events.  If you are interested in speaking at any of these events please email me at pleblanc@sqllunch.com. 

Talk to you soon,

Patrick LeBlanc

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

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

SQL Saturday #22 Pensacola Recap

Saturday I drove up to Pensacola for SQL Saturday #22.  Me and several others from Baton Rouge, including my two kids left Baton Rouge around 3AM. 

We arrived at SQLSat22 around 7:30AM.  We registered and had a few donuts.  Then Patrick Jr. (9), Kalyn (5) and myself headed to Steve Jones’s 7:30 am Session, Basic SQL Server.  After that, I realized that the image for my 10:15 session,Introduction to SharePoint 2010 BI and PowerPivot, was not working.  As a result, I needed to find Karla Landrum (SQLSat22 fearless leader) to let here know that I needed to change the session. 

So instead of attending a session I went through a few of my presentations and narrowed the list to two.  I allowed the room of attendees to vote between Introduction to SQL Profiler or SQL Server Compression.  It was unanimous, the Profiler.  I had given the presentation a couple of times so it went off without a hitch.  Well at least until my little girl, Kalyn, decided that she wanted to co-present.  Good times for all, that’s all I can say.

Next I attended, Chris Skorlinski, session Introduction to Change Data Capture.  This was a great session.  I was able to take information from his session to improve my second session.  I did not have time to make the changes before my session, but the changes will be added before I give it again.  After that it was time for a vendor session and lunch.  I attended the FusionIO session, and it was very interesting. 

Finally, it was time for the best session of the day, CDC+SSIS=SCD.  I quote my good friend Tim Mitchell, “the most entertaining hour of the day”.  To my surprise there were more speakers in the session than attendees.  They waited for me to fail, but to their disappointment it was a complete success.  I have to say thanks to Tim Mitchell for saving the day, when my little girl interrupted again,“Dad my tooth fell out”.  He helped me by helping her rinse it out and wrap it in some tissue for the Tooth Fairy. I am going to make the presentation even better in the weeks to come.  If you did not have a chance to join us, I have submitted it to PASS and hopefully it will be selected.

This was my second time Speaking at SQL Saturday in Pensacola and it was a great event.  Karla and her team did a great job.  There were several well known speakers at the event, Brad McGehee, Kevin Kline, Steve Jones, Trevor Barkhouse, Joe Healy, Troy Gallant, Devin Knight, Barry Ralston, Tim Mitchell, Joe Webb and many more.  This was a great event for our Technology Community in Baton Rouge, We had four speakers (William Assaf, Cody Gros, Brian Rigsby, and myself) that made the trip to Pensacola.  Unfortunately, I had to leave when my session was over, but I am sure the remainder of the day was awesome. 

Talk to you soon,

Patrick LeBlanc

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

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

Inventory SQL Servers with CLR Function

In an enterprise with several SQL Server instances it is pivotal that you keep track of each instance and its corresponding properties. Properties such as, name, version, service pack level, is it clustered, etc… I have implemented and read about several approaches that will assist in collecting this information. My first implementation involved creating a DTS package, yes DTS Package, in which I created a connection to every instance and imported the information into a centralized repository. Each time a new server was added I would create new connection and import the data from that server

When SSIS was introduced I converted my DTS package to an SSIS package. The process was exactly the same. The package contained a data flow that connected to every instance and similar to the DTS package the data was imported into a centralized repository. The following image depicts a data flow layout similar to my SSIS package:

As the environment grew, it became very cumbersome to add a new connection every time a new server was added. Therefore, I modified the package to utilize a dynamic connection. This removed the need to add a connection each time a new server was added. I created a table that contained a list of instance names. Using a Foreach Loop container, I iterated over the list and imported the data for each instance. Instead of adding a connection I simply inserted the instance name into the table.

This approach worked well until I decided I wanted to learn how to write a CLR table-valued function. I have to admit, this blog post started out as an article, but after trying to explain the details of the C# code I deferred to writing a short blog post. The function accepts one parameter, which is the SQL Server instance. The instance is used to create a dynamic connection, which can be seen below in the code snippet from the function:

Then, using the above-mentioned connection a query is run that will pull the required information from the server. The query uses the SERVERPROPERTY scalar function to retrieve the information. See the following code snippet:

If you are interested in viewing the code in its entirety, feel free to download load it. You can deploy the function directly from the project. Before you deploy, ensure that you have enabled the CLR and set the database to trustworthy. Use the following script to enable the settings:

use master go exec sp_configure 'clr enabled','1' go reconfigure go alter database <databasename> set trustworthy on go

Finally, before deploying right-click on the project and select Properties. When the Properties tab is activated click on Database and click the browse button next to the Connection String text box. Either select an existing database or create a connection to the database where you want to deploy the function. Then select External from the drop down list labeled Permission Level. See the following for an example:

Now that everything is configured deploy the function and begin using it. To test the function use the following script:

SELECT *FROM dbo.SQLServerProperties('SQL INSTANCE NAME')

I created a stored procedure that uses a MERGE statement to ensure that the data remains consistent each time the query is run, which is included in the download file. Lastly, I scheduled a job that runs the stored procedure on a nightly basis to capture the information from each server. One thing to note is that the account running the SQL Server where the function is deployed must have permission on the SQL Servers where it is pulling information.

I am far from a C# coder so after downloading the sample project, if you find ways to improve my code please do so. All I ask is that you send me the updated code. If you have any questions or concerns regarding this document please feel free to email me at pleblanc@pragmaticworks.com.

Download: ServerInventory

Talk to you soon,

Patrick LeBlanc, MCTS

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

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

SQL Lunch Call for Speakers – Quarter 3

One of my quarterly goals is to get speakers for the SQL Lunch webcasts.  Q1 and Q2 of 2010 are in the books, so its time to start working on Q3.  To all of you out there who want to share your knowledge with all of us, please send an email to webmaster@sqllunch.com if you are interested in speaking on the SQL Lunch.  Include a short bio, the topic that you want to discuss and a brief description of the topic.  Please attach a head shot so I can share your picture with the world.  Remember the SQL Lunch webcasts are 30 minutes or less.   It does not matter if you are  a seasoned speaker or a new speaker, we at the SQL Lunch welcome you with open arms. 

Talk to you soon,

Patrick LeBlanc, MCTS

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

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

twitter:  @sqllunch, @patrickdba