Tuesday, November 30, 2010

Passing SSRS T-SQL Values to SSRS MDX Parameters

At a recent client engagement we built a SQL Server Reporting Services (SSRS) report with a T-SQL based data source. A requirement of the report was to create an action that drilled-through to another report. No big deal, right? This is SSRS 101. Well, that was until I realized that the parameters that had to be set on the drill-through report were based on MDX queries. I still didn’t think that it would pose a problem and proceeded to configure the textbox Action to go to another report. After I completed the configurations, I ran the report and it DID NOT WORK! Hmmm, what’s wrong?

The first step was to take a look at the parameters on the MDX based report, which revealed very little. I needed to see the source query that provided the data for the parameter. Initially I did not see the data set for the query. Then I realized that when you created parameters using the MDX query builder in SSRS it hid the corresponding data sets. To show them, right-click on the data sets folder and click Show Hidden DataSets. With the data set now visible, I was able to run the query in the designer. The designer revealed a little Golden Nugget of information. There was a column labeled “ParameterValue” as shown in the following figure:

image

This was interesting. It revealed a fully qualified MDX value as the Parameter Value. Therefore, using the traditional method of only selecting the column from the data set as the value to be passed would not work. So I decided to build an expression that would dynamically create values equal to those values in the ParameterValue column of the MDX query result set. The expression can be seen in the following figure:

image

I re-ran the report and clicked the textbox with the action:

image

This directed me to the MDX based report and the parameter was set correctly, which can be seen in the following figure:

image

Happy Days are here again!

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS, founder SQLLunch.com.

Tuesday, November 16, 2010

Handling Type II Dimension with the MERGE Statement

Microsoft attempted to make our lives easier with the Slowly Changing Dimension task in SSIS.  However, as we all know the task has some major performance issues.  So they introduced the MERGE statement.  When I initially started using the MERGE statement I considered it as a great solution to handle Type I dimensions, but I did not consider it as a solution for Type II.  Recently, I was chatting with Brian Knight and he said that the MERGE statement could handle Type II dimensions.  After spending some time with Books Online I realized that you could SELECT the OUTPUT data from the MERGE Statement.  This set up the perfect mechanism for handling a Type II dimension.  This is how I did it.

Running the examples requires the AdventureWorks2008R2 database.  To get started with the explanation run the following script:

USE tempdb


GO


IF(OBJECT_ID('dbo.Product')) IS NOT NULL


    DROP TABLE dbo.Product


GO


CREATE TABLE dbo.Product


(


    ProductID int identity(1,1),


    ProductNumber varchar(50),


    ProductName varchar(100),


    Color varchar(30),


    ListPrice decimal(12,2)


)


GO


 


INSERT INTO dbo.Product


SELECT 


    ProductNumber,


    Name,


    Color,


    ListPrice


FROM AdventureWorks2008R2.Production.Product


 


GO


 


IF(OBJECT_ID('dbo.DimProduct')) IS NOT NULL


    DROP TABLE dbo.DimProduct


GO


CREATE TABLE dbo.DimProduct


(


    ProductSK int identity(1,1),


    ProductAK varchar(50),


    ProductName varchar(100),


    Color varchar(30),


    ListPrice decimal(12,2),


    EffectiveDate datetime,


    ExpirationDate datetime


)


GO


 


INSERT INTO dbo.DimProduct(ProductAK, ProductName, Color, ListPrice, EffectiveDate)


SELECT 


    ProductNumber,


    ProductName,


    Color,


    ListPrice,


    '1/1/1900'


FROM dbo.Product


GO






The above scripts creates an operational Product table and initially loads the data into the DimProduct table.  Now let's assume that ListPrice is a Type II attribute.  In other words, if the ListPrice changes, the current effective row should be expired and a new row should be inserted into the dimension as the effective row.  This new row will contain the changed data.  To simulate a Type II change run the following script:




UPDATE dbo.Product


SET 


    ListPrice = 5.00


WHERE


    ProductNumber IN ('AR-5381','BA-8327')


GO




The script will change the ListPrice for two products in the operational table.  After the updates have been executed, run the following script:




   1:  INSERT INTO dbo.DimProduct(ProductAK, ListPrice, Color, ProductName, EffectiveDate)


   2:  SELECT ProductNumber, ListPrice, Color, ProductName, EffectiveDate


   3:  FROM 


   4:  (


   5:      MERGE dbo.DimProduct dp


   6:      USING dbo.Product p


   7:          ON dp.ProductAK = p.ProductNumber


   8:      WHEN NOT MATCHED THEN


   9:          INSERT (ProductAK, ProductName, Color, ListPrice, EffectiveDate)


  10:          VALUES (p.ProductNumber, p.ProductName, p.Color, p.ListPrice, '1/1/1900')


  11:      WHEN MATCHED 


  12:          AND ExpirationDate IS NULL 


  13:          AND (dp.ListPrice <> p.ListPrice) THEN


  14:          UPDATE


  15:              SET


  16:                  dp.ExpirationDate = convert(datetime, getdate(), 101)


  17:      OUTPUT $Action MergeAction, p.ProductNumber, p.ListPrice, p.Color, p.ProductName, convert(datetime, getdate(), 101) EffectiveDate


  18:  ) MergeOutput


  19:  WHERE


  20:      MergeAction = 'Update';



Let's start the explanation in the inside of the query.  On line 12 the current effective row is identified.  You may need to change this depending on your Type II dimension design patterns.  Your dimensions may use a specific date as the expiration date and you may also include a ActiveFlag bit column.  Then on line 13 I verify that the column specified as a Type II attribute has actually changed.  Finally, on line 16 the row is expired.  Now you may be thinking how are you going to add the new current row for the corresponding expired rows.  That was the same thing I was thinking.



On line 17 I output any of the Inserts or Updates.  In addition, I added a column that acts as the Effective Date.  The thing that I did not know was that you can SELECT the OUTPUT data from the MERGE statement and INSERT it into a table.  This is accomplished in lines 1 and 2.  Essentially, I derived a table from the OUTPUT of the MERGE statement.  Lastly, I included a WHERE clause to ensure that I only inserted the data that was UPDATED.  That pretty much did it.  To verify the changes run the following query:




SELECT *


FROM dbo.DimProduct


WHERE


    ProductAK IN ('AR-5381','BA-8327')




Talk to you soon,



Patrick LeBlanc, SQL Server MPV, MCTS

Monday, November 15, 2010

My SQL PASS Summit Adventures part 1

I started my PASS Summit a little early this year.  I arrived in Seattle on Friday night, November 5th,  around 10:30 PM.  I met Brian Knight and Tim Moolic (Pragmatic Works COO) and we drove about 1.5 hours to Ashford, WA.  We settled in to our cabin on the Creek for a few hours of sleep before we began our BIG adventure.  About a month before PASS Brian gave me a call and asked if I would like to join he and Tim on a hike up Mt. Rainier.  I hesitated at first, but then quickly decided to do it.  So here I was, about to start a hike up the mountain in what we were told was some very unfavorable weather conditions.

IMG_1064 As you can see from the background, the weather was not very kind.  It was snowing, raining, and sleeting all at once.  If that is possible, but we were brave and had already rented all the gear so we did not really have a choice.  If you take a close look at my face in the picture you realize that I was the only one not smiling.  If you don't know I currently live in Louisiana and I was born in Houston, Tx.  Snow is not a common occurrence in either of these places.  To be honest I almost backed out, but my EGO wouldn't let me, besides these two guys would have never let me live it down.  So on to the hike.

I thought we were going to have a slow start to the hike, boy was I wrong.  Tim took off like he was running a 40 yard dash.  By the way, Tim lives in Boston so he is very familiar with snow and how to get around in it.  After about the first 200 feet I was done.  I wanted to turn back and find a nice warm place with a hot cup of coffee.  Again, my EGO took over and forced me to continue.

We hiked up for about 3 hours or 4000 feet, stopping a few times along the way for a little water, snacks and sight seeting.  At one point I found myself nearly waste deep in snow. Since I am not very familiar with snow, I did not realize that I should have been wearing the Snow Shoes that we rented.  Tim quickly brought that to my attention after I trudged through the snow for about 100 or so feet.
 SANY0024
The visibility worsened as we hiked higher and we decided to call it quits after we reached about 8000 feet.  We did not want to take the chance of hiking down in the dark and getting lost.

IMG_1072

This was the first time that I had ever experienced so much snow in my life.  Would I do it again?  Yep, without a doubt.  Since the PASS Summit is in October next year, the weather should be better and we should be able to hike a little higher.  At the end of the day we were exhausted, exhilarated, and ready for a drink.  We ended our night at a the only restaurant in Ashford.  We enjoyed a nice dinner with a few drinks some food and a little fun (Karaoke). 

IMG_1136 IMG_1148

All SMILES!!!!!  This was just day one of my PASS adventures.  Stay tuned for more adventures.

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS, Mountain Man

Sunday, November 14, 2010

Join SQL Lunch on Tuesday

What a long week at PASS (stay tuned for more details about my trip).  Now that it's over back to work.  Join me and Cade Roux for a SQL Lunch on Tuesday.  Cade is lives in the rocking city of New Orleans, LA.
#41-Get a Lever and Pick Any Turtle: Lifting with Metadata

Lunch Image

Speaker: Cade Roux

Add To Outlook: Add To Calendar

Date and Time: 11/16/2010 11:30:00 AM CST

Live Meeting URL:  https://www.livemeeting.com/cc/usergroups/join?id=68R4BF&role=attend


BIO: Cade Roux, is the recent founder of Rose Crescent, a software and systems consultancy in New Orleans. He has been a professional programmer since 1992, and a user of SQL Server since 1997, managing IT operations at all levels of organizations. He has broad experience in several lines of business, including Route Accounting, Medical and Banking and technical exposure ranging from C/C++ to T-SQL, from the Windows API to Data Warehousing. He loves building great systems and great teams.


Topic: #41-Get a Lever and Pick Any Turtle: Lifting with Metadata

Integrated metadata is a required element of all RDBMSs. In addition to ANSI INFORAMTION_SCHEMA and metadata on implementation-specific features like indexes, SQL Server also offers a very powerful extended properties feature. In this talk, I cover using the basic metadata combined with extended properties and demonstrate practical usage to organize and document a system's design and monitor the health of a system's compliance with design rules.

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS, founder SQLLunch.com

Wednesday, November 3, 2010

A Couple of SQL Lunches this Week

I want to apologize for posting this so late, but there is a SQL Lunch today and one tomorrow.  Hope you all can make it.

#37-Zero to Dashboard

Lunch Image

Speaker: Adam Jorgensen

Add To Outlook: Add To Calendar

Date and Time: 11/3/2010 11:30:00 AM CST


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.


Topic: #37-Zero to Dashboard
Come have some fun seeing how we can build your first dashboard in PerformancePoint Services Together. Join Adam and Go from Zero to Dashboard and see some of the cool reporting techniques you can do with Performance Point without writing any code!

#42-Auditing Database Permissions in SQL Server

Lunch Image

Speaker: K. Brian Kelley

Add To Outlook: Add To Calendar

Date and Time: 11/4/2010 11:30:00 AM CST


BIO: Brian is a SQL Server author, columnist, and Microsoft MVP focusing primarily on SQL Server security. He is a contributing author for How to Cheat at Securing SQL Server 2005 (Syngress) and Professional SQL Server 2008 Administration (Wrox). Brian currently serves as a database administrator / architect for AgFirst Farm Credit Bank where he can concentrate on his passion: SQL Server. He previously was a systems and security architect for AgFirst Farm Credit Bank where he worked on Active Directory, Windows security, VMware, and Citrix. In the technical community, Brian is president of the Midlands PASS Chapter, an official chapter of PASS. Brian is also a junior high youth minister at Spears Creek Baptist Church in Elgin, SC.
Topic: #42-Auditing Database Permissions in SQL Server


Invariably, the dreaded auditors will come knocking at your door wanting to know who can do what in a database. Knee deep in other initiatives, the last thing you want to do is parcel out the time required to answer their requests and the follow-on questions they're likely to have. However, you know that this these are requests you can't refuse and you resign yourself to your fate. In this presentation, Certified Information Systems Auditor (CISA) and Microsoft SQL Server MVP K. Brian Kelley will cover what to audit, how to audit it quickly and efficiently, and the gotchas to look out for when it comes to compiling and reporting database permissions in SQL Server. Coverage of the security catalog views to use in SQL Server 2005/2008 as well as capturing implicit permissions will be focused on.

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, founder SQL Lunch.com