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 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.

SQL Server Reporting Services R2 – Publishing Report Parts

SQL Server Reporting Services R2 (SSRS) allows report developers to not only publish reports and data sources to the report server, but now they have the ability to publish report parts.  You can publish parts of a report, such as charts, tables, logos, etc…  In addition to the report part, any data set and/or parameter that the part depends on accompanies it as metadata.   End-users will have the ability, using Report Builder 3.0, to create reports based on the published Report Parts.  So, how do you publish the report parts?

First, create  a report that contains a few charts, tables and maybe an image.  Similar to the following:

Each item on the report, the five charts and the logo, can be published to the report server as a report part.  Ensure that you give each part that you plan on publishing a descriptive and meaningful name.  Once that is done, select Report from the menu bar and select Publish Report Parts.  The following screen will appear:

Then select the checkbox next to each item that you want to publish as a report part and click OK.  The next time you deploy your report project each selected item will be published to the report server.  Finally, prior to deploying the report parts, you can specify the location where these items will be deployed.  Right-click on the project in the solution explorer and select Properties:

 

Under the deployment section in the row labeled TargetReportPartFolder, you can specify the location where you want to deploy the report parts.  Your end-users can now connect to the report server and use these report parts in their reports.  I will explain how they do this in my next blog post.

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.

SSRS – KPIs without Analysis Services

During a recent project I was asked if it was possible to simulate Key Performance Indicator (KPI) images in SQL Server Reporting Services (SSRS) without Analysis Services. Off the top of my head I thought of a couple of things, but I decided to do a little searching before I embarked on my latest challenge. After one quick search I found a great video posted on SQL Share by SQL Server MVP, Jessica Moss, Designing a KPI in Reporting Services.

Using her example, with a few modifications I was able to produce exactly what the client needed. See the following screenshot for an example:

So how did I do it?

To start, I had a quick meeting with the client to determine the calculation for the GOAL of the KPI. It was simply a percentage based calculation between current YTD sales and the prior YTD sales ((CurrentYTD/PriorYTD)*100). The value indicators would be determined by the results of the calculations. As seen in the above screenshot, if the calculation was greater than 100 a green arrow should be shown, if it was greater than or equal to 90 and less than 100 a yellow arrow should be show and if the value was less than 90 a red arrow should be shown.

After all the requirements were gathered, the next step was to develop a method to dynamically show the arrows based on the KPI Range Values. First I created a calculated column that used the aforementioned calculation. To do this, add a calculated field to your existing dataset by right-clicking the dataset and select Add Calculated Field. The Dataset Properties window will appear:

Click the Add button and insert your calculation. You can also return the calculation as part of your result set and ignore this step.

Next add a column to an existing Table item on the report. Then add an Image into the column. See the right-most column in the following screenshot:

Next, right-click the image and select Image Properties and the following window will appear:

=SWITCH ( Fields!KPI.Value > 100, "GreenUpArrow", Fields!KPI.Value >= 90 AND Fields!KPI.Value < 100, "YellowAngleArrow", Fields!KPI.Value < 90, "RedDownArrow" )

Then click the expression button next to the Use this image drop down list. In the expression textbox enter the following or something similar to the following:

The SWITCH function will allow you to specify the image that should be displayed based on your KPI values. If you are not familiar with the SWITCH function read my blog posting, Reporting Services SWITCH Function , for more details. In the function I specify the image to display based on the calculated KPI Value.

Next click on the Size in the right pane of the Image Properties box:

Ensure that the radio button labeled Clip is selected and you can also adjust the padding on the images just in case you want to change the alignment of the image. Click ok and run your report. You now have KPI type images in your Reporting Services report.

Downloads

KPIExample

AdventureWorks2008DW

As always, if you have any questions, comments or concerns regarding this posting 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.

December 2009 – Baton Rouge SQL Server User Group

December SQL Server User Group Meeting

Location: At Lamar Advertising

Wednesday, December 2, 2009

6:00 PM - 8:00 PM

Sponsored by: EMC

Topic

An Overview of Business Intelligence

Presenter

Brandon McMillon

Brandon McMillon is a Senior Solutions Principal at EMC.  He has 10 years of experience at Microsoft, working on the Visual Studio & Windows development teams.  He was also a Chief Software Architect for a Microsoft Gold Partner before coming to EMC.

Abstract:

This talk will first examine the current marketplace for Business Intelligence, and how it has gotten there.  We’ll look at how EMC is leveraging some of the newer tools and products to lower the costs and effort for implementing BI, in the context of a real-world EMC BI project.  We’ll also share best practices & lessons we’ve learned in these projects.  Finally, we’ll look at the next generation of BI tools and products, and talk about how they will impact the market and what problems they’re attempting to solve.

Agenda

5:45 pm - 6:00 pm:	General Introduction/Food and Drinks
6:00 pm - 7:30 pm:	Overview of Business Intelligence
7:30 pm - until:	Open forum for questions

Talk to you soon

Patrick LeBlanc, founder www.tsqlscripts.com and www.sqllunch.com

Linear Gauge Control: A Practical Use

SQL Server Reporting Services 2008 introduced several new features. One key feature was the inclusion of the Gauge Controls. I was recently asked to provide a real-world example of the Linear Gauge Control. Since I am always up for a challenge, I delve head deep into the gauge controls. After doing a little reading and testing of the linear control I chose my path. I started with the following report:

The data in the report can be reproduced using the stored procedure provided at the end of the posting. As you can see the report contains Sales data from each country. Looking at the report I noticed, after a little time, that the United States has the largest amount of sales. In the past I would have added a parameter that allowed dynamic sorts based on the Total Sales column, which is not a bad solution. This helps the end-user to quickly identify the BIG winners and losers in regards to sales. However, what about all the countries that fall in between? As a result, I decided to add a linear gauge control that would graphically depict sales as a percentage.

When you initially add the Linear Gauge control to the report, there are several items (ranges, pointers and labels) that are unnecessary.

Therefore before attempting to associate data with the control, I remove three things:

1. LinearPointer2

2. Two right-most Ranges, which are shaded in the lighter two shades of gray

I also hide the labels and any tick marks, which can be done by selecting the entire scale and un-checking the items labeled Show Labels and Show Major Tick Marks.

In then end you are left with a control that looks like this:

The next thing I did was to set the End range at scale value for the remaining Range to 100. Since I am attempting to graphically depict sales as a percentage, comparing the values to 100 should provide a meaningful representation of the data. To calculate the percentages I used the following expression:

=(Fields!TotalSales.Value/SUM(Fields!TotalSales.Value,"Tablix1"))*100

In the expression I divide the Sales for each country by the total sales for all countries. Note that the sum for all countries is calculated by using the sum function, including the scope value. Finally the value is multiplied by 100 to ensure that the result is between 0 and 100, which is the start and end values of the controls only range (the remaining dark grey bar in the control).

Now the only thing left is to assign the calculated value to LiinearPointer1. To do this, select the orange part of the control only. Then right click, the following dialogue box will appear:

Click Pointer Properties, then the Linear Pointer Properties dialogue box will appear. Choose the expression button next to the textbox labeled value and insert the above calculation. Once complete run the report and you will be presented with a result similar to the following:

As you can see from the above image, an end user can easily identify which country is selling and which is not. If you have any questions or comments concerning this topic please feel free to email me at pleblanc@pragmaticworks.com.

Talk to you soon

Patrick LeBlanc, founder www.sqlscripts.com and www.sqllunch.com

SQL Server 2008: Table-valued parameters

A new feature of SQL Server 2008 is Table-valued parameters (TVP).  This feature will allow Developers and DBAs to pass tables as parameters to stored procedures.  You cannot pass a variable table or temp table, you can only pass a Table Type, which is an alias data type or a user-defined type.  So how do you use it?  The first step is to create  a Table Type.  See the following script:

 

USE AdventureWorks2008 GO IF EXISTS (SELECT * FROM sys.types WHERE name = 'CountryCodes' AND schema_id = SCHEMA_ID('Sales')) DROP TYPE Sales.CountryCodes GO CREATE TYPE Sales.CountryCodes AS TABLE ( CountryCode nvarchar(3) )

 

The next step is to create a Stored Procedure that will include a variable of the aforementioned Table Type. The following Stored Procedure uses the AdventureWorks2008 database to select Sales by Date and Country Region Code, using the TVP to limit the result to specified Country Region Codes:

USE AdventureWorks2008 GO IF(OBJECT_ID('Sales.GetSalesByDateAndRegion')) IS NOT NULL DROP PROC Sales.GetSalesByDateAndRegion GO CREATE PROC Sales.GetSalesByDateAndRegion @Month varchar(20), @Year int, @CountryRegions Sales.CountryCodes readonly AS SET NOCOUNT OFF SELECT    cr.Name,    SUM(TotalDue) TotalDue FROM Sales.SalesOrderHeader sod INNER JOIN Sales.SalesTerritory st    ON sod.TerritoryID = st.TerritoryID INNER JOIN Person.CountryRegion cr    ON st.CountryRegionCode = cr.CountryRegionCode INNER JOIN @CountryRegions c    ON cr.CountryRegionCode = c.CountryRegionCode WHERE    DATENAME(MONTH,sod.OrderDate) = @Month AND    YEAR(sod.OrderDate) = @Year GROUP BY     cr.Name,     DATENAME(MONTH,sod.OrderDate),     YEAR(sod.OrderDate) SET NOCOUNT ON GO

 

In the variable declaration of in the above stored procedure, the last variable is declared as the Table Type (Sales.CountryCodes) created in the first script.  Then the Table-valued Parameter (TVP) is used in the last JOIN of the query to limit the result to the items or Country Region Codes contained with the TVP.  On thing to be aware of is that the Table Type is read only.  The contents of the table cannot be modified.  Now that all of the formalities are out of the way, how do you use this in T-SQL?  The following example is a script of how to call a stored procedure that has a TVP as a parameter:

USE AdventureWorks2008 GO DECLARE @StartDate datetime, @EndDate datetime, @CountryRegions Sales.CountryCodes SELECT @StartDate = '4/1/2002', @EndDate = '4/30/2002' INSERT INTO @CountryRegions VALUES('US'), ('CA') EXEC Sales.GetSalesByDateAndRegion @StartDate, @EndDate, @CountryRegions

 

As you can see in the above script, you will populate the TVP the same way that any other table is populated.  Once it is populated it can be passed to a stored procedure. 

Talk to you soon,

Patrick LeBlanc, Founder www.tsqlscripts.com and www.sqllunch.com

How to Create a Reporting Services 2005/2008 Template

 

At most large companies one business requirement is that all reports have the same look and feel.  This may vary by department, but there is typically some level of standardization amongst the business entities.  In most cases there is a header and footer template that needs to be seen on all reports.  Often developers I have seen developers start from scratch or copy and paste and existing report.  Those days are gone.  For all of you still using Reporting Services 2005, don’t worry this method is available to you also.  Here are the steps:

1.  Create a template report, maybe and .rdl that contains only the header and footer information.  These are items that are typically used throughout a company or department.

2.  Copy the .rdl file to one of the following directories

(SSRS 2005) - C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

(SSRS 2008) - C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

The next time you create a report, right click on the Reports folder in the Solution Explorer of your Reporting Services Project.  Then choose Add –> New Item.  A dialogue box will open, similar to the one below:

In the above screen shot, my template is named Dashboard Template.  Choose that item and click Add.  When the report is added to you project it will look exactly like the template you created.  Happy Report Writing!

Talk to you soon

Patrick LeBlanc, founder www.TSQLScripts.com and www.SQLLunch.com

Article of the Week – Kimberly L. Tripp (Partitioned Tables and Indexes)

When partitioning was first introduced in SQL Server 2005, I thought what a great idea.  However, when I started reading Books Online I thought, this is going to be a big Pain in the you know what.  After reading Kimberly’s article, Partitioned Tables and Indexes, I was convinced of how easy it would be to implement Partitioning and how it would help manage and maintain Very Large Databases, specifically warehouses.  The article is a little dated, but if you have any questions about Partitioning from a management and maintainability stand point this article is a must read.  She discusses topics that include, planning, defining, steps to create, etc…  This article should definitely help jump start your design and implementation of partitioning in your SQL Server environment.

To read the article in its entirety click here.

Talk to you soon

Patrick LeBlanc, Founder www.tsqlscripts.com and www.sqllunch.com

August 2009 Baton Rouge SQL Server User Group – Near Real-time Data Warehousing 1.0

Fresh off of my presentation of the SQL Profiler at SQL Saturday #17 in Baton Rouge this past Saturday, I am now preparing to speak at our next user group. The topic is Near Real-time Data Warehousing. I gave this talk at the SQL Saturday in Pensacola it was well received. However, before I could really get started presenting in Pensacola one of the participants asked, “Why do you need a Near Real-time Data Warehouse”. I was prepared to answer that question, but instead of using the rehearsed version of the answer I decided to traverse down another path. My answer went like this:

Have you ever actually tried to find a “Needle in a hay stack”? Of course trying to find that needle would by very difficult and time consuming and everyone agreed. I went back to my slide deck and revealed a query that consisted of THIRTY-FIVE joins. The results of that query was simply a detailed customer listing, which included columns like first name, last name, address, phone number, city, state, etc.. I continued by explaining why the query was so long and complicated. Our developers designed somewhat of an Object-Oriented database. As a result, we have a database that is at a level of normalization that does not exist (smile). Therefore, our team was posed with the task of de-normalizing the database to make it more report friendly.

The project was initially tasked as flattening the operational tables into a less normalized state, which was to include lots and lots of T-SQL and SSIS packages. However, as the project progressed someone suggested that we just make it a warehouse and try to populate it at certain intervals though out the day. I had built some very small warehouses, but nothing of this magnitude and visibility. As a result, I accepted the task. The result of the project is six FACT table and 23 dimensions that are built in intervals of 15 minutes using the following technologies:
1. Transactional Replication
2. Triggers
3. SQL CLR
4. Message Queuing
5. SSIS
6. A Custom ETL application.

If you want to find out more and you can’t make it to our local SQL Server Group (go to http://batonrouge.sqlpass.org to get information about our local group) send me an email at pleblanc@tsqlscripts.com to receive and invitation to a LIVE MEETING broadcast of our use group. If you can’t make either don’t worry, I am writing an article on the entire process that hopefully will be published somewhere soon. Please note that this is version 1.0, version 2.0 does not rely on items 2,4, and 6 of the aforementioned list. Instead we have incorporated Change Data Capture, which makes life so much easier.

Talk soon Patrick LeBlanc,
SQL Down South.