SQLDownSouth

SQL Lunch - Introduction to Change Data Capture

2011-01-31T10:13:00.001-08:00

Join me tomorrow for a little CDC.

#47-Introduction to Change Data Capture

Speaker: Patrick LeBlanc

Meeting URL: https://www.livemeeting.com/cc/usergroups/join?id=269DRP&role=attend

Date and Time: 2/1/2011 11:30:00 AM CST
BIO: Patrick LeBlanc, SQL Server MVP, is currently a Business Intelligence Architect for Pragmatic Works. He has worked as a SQL Server DBA for the past 9 years. His experience includes working in the Educational, Advertising, Mortgage, Medical and Financial Industries. He is also the founder of TSQLScripts.com, SQLLunch.com and the President of the Baton Rouge Area SQL Server User Group. Patrick is a regular speaker at various SQL Server community events, including SQL Saturday’s, User Groups and the SQL Lunch.
Topic: #47-Introduction to Change Data Capture
In this session Patrick will introduce the basic concepts for configuring Change Data Capture (CDC). He will explain how to configure CDC at the database level and for each table within the database. In addition, Patrick will explain a few techniques that can be that can be used to load your Data Warehouse Incrementally with CDC.

Talk to you soon,

Patrick LeBlanc, MVP

Founder, www.SQLLunch.com

SQL Lunch - Building a Performance Point Dashboard

2011-01-18T10:18:00.001-08:00

Don't forget to attend the SQLLunch tomorrow.

#35-Building your first PerformancePoint Dashboard in SharePoint 2010

Speaker: Adam Jorgensen, SQL Server MVP

Add To Outlook: Add To Calendar

Date and Time: 1/19/2011 11:30:00 AM CST

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

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: #35-Building your first PerformancePoint Dashboard in SharePoint 2010
Many of you have seen the coolness that is PerformancePoint in SharePoint 2010. Come get hands on with Adam as we go through the ins and outs of creating your first dashboard. See some of the cool visualizations you can use and how they work together to create a cool user experience.

Talk to you soon,

Patrick LeBlanc, MCST, SQL Server MVP

Founder www.sqllunch.com

2011.....So (long pause), what's next for me?

2011-01-16T21:10:00.001-08:00

I've actually sat down to write this blog several times. I am never indecisive when it comes to my thoughts or my writings. Most of the time I just write what I feel. For some reason this post was a little challenging. It was hard for me to decide if I want to share my thoughts about what my goals were for the year. After a couple of attempts I decided to share.

So, what's next for Patrick? Before I delve to deep into my upcoming goals, I want to revisit 2010. Last year was a very good year for me. I accomplished everything I set out to do with the exception of one thing, record a few videos for SQL Share. I have placed this pretty high on my priority list. The main reason is because I explicitly stated it as a goal last year. I am working on my second book now, which is monopolizing a fare amount of my free time between midnight and 5 am. Therefore, most of things I mention here will not begin until I am finished with the first drafts of each chapter. I have about 4 days of writing left. I have to say, the two biggest highlights of 2010 for me was being selected as a SQL Server MVP and SQL Lunch turning 1 years old. Overall, 2010 was a great year for me.

So now, what's next for Patrick? This is where I get stuck every time on this post. What do I want to achieve or accomplish? I am not saying that I have reached the pinnacle of my career. There is still so much for me left to learn and do, but the challenge is deciding what and making sure that it will fit into my already busy schedule. Therefore, I decided to only set a few goals now and add more as priorities change and the year goes on. I plan on chronicling my progress on my blog so follow along. So here are my goals, not resolutions:

Brand Myself

I spent much of 2010 marketing and talking about the SQL Lunch that I actually forgot about Patrick. I know some of you that have attended my sessions or met me in person may be thinking, are you kidding. You talk about yourself constantly or at least your EGO does. I recently purchased a domain, which will be announced in another blog, that will be all about me. Yep, I said it all about me. I will blog, post articles, videos, etc... What's going to make it different is that I am going to share technical, professional, and personal thoughts there. I am still going to syndicate all the technical stuff, but I am going to share other thoughts and ideas also. The later will not get syndicated.

Grow Technically

I have decided to let my career (not job) and the questions I answer during presentations dictate which technical areas I will grow. I figured if I focus on those things my knowledge should grow immensely since I am a consultant and I speak on a regular basis. Now, I may decide after a couple months that these two avenues are not producing enough learning opportunities for me. At that point, I will start to work on identifying what will be beneficial to me in all aspects of my career.

More Involved

I wear many hats in my life at this time. Taking on anything new would probably not be a good idea. So instead, I decided to focus on this things that I am apart of now. For example, I run the SQL Server User Group here in Baton Rouge. Our attendance has slumped over the past couple of months. I plan on making some changes and implementing some new things to improve attendance. One thing that I have to work on is bringing in speakers. I think the low attendance can be directly attributed to the fact that we have the same four or five speakers speaking on a regular basis. Another example, would be to improve the things I do as a PASS Regional Mentor. I have some ideas that I think will help grow the User Group base in my region and I will be working with my co-Regional Mentor to get them implemented in an attempt to encourage other community members to start chapters in their area. A final example, which has been brought to my attention several times, is to be a more consistent blogger.

Physically Fit

For me, becoming physically fit is going to require a life style change. This change will require that I workout daily and change what I eat. I started this about a week ago and so far not too good. Fortunately I have someone that is pushing the process along. I have been given a workout schedule and a daily menu. Its going to take a little time for me to get this worked in to my life, but I am up to the challenge. Eating lots and lost of junk food is a big part of my life, especially when I am traveling.

Family Time

Family is really important to me. Last year, as I ramped up on Business Intelligence, my job started to take precedence over my family. No one complained, but when you come home and realize how much you have missed. Weeeeell, it kind of makes you think. As a result, I have worked with closely with my family to add their lives to my calendar before I add work. So far we have the scheduled worked out through May. As events come up I block that time on my calendar. After all, what would life be without them.

Speaking

Finally, I want to speak more. For those of you that know me, you may be thinking really. Last year I spoke more than 50 times. I plan on at least meeting that number or exceeding it. So if you are looking for a speaking for any of your events, please email me at pleblanc@pragmaticworks.com. I cannot physically attend all the meeting, but through the magic of Live Meeting I can make almost any meeting. Last year I had the opportunity to speak to the Auckland SQL Server User Group. I hope I get an opportunity to do that again this year.

PhD

Believe it or not, I am currently working on my PhD in Science and Math Education. I have completed all my course work and all that is left it to write the dissertation. Well, I will be back in school the summer working with my committee to get that completed. I have set a graduation date of May 2012. Now that it is out there I must do it. So when I am done, I am going to require that everyone refers to me as Dr. Ego :).

So that is what's next for me. I don't think these things are unattainable, just some things that I think will make me a better IT professional, father and husband.

Talk to you soon and Happy New Year to you all,

Patrick LeBlanc, SQL Server MVP

Founder www.sqllunch.com

TechEd: Session Preference Survey

2011-01-16T21:08:00.001-08:00

TechEd has listed some potential sessions for TechEd North America 2011 here: http://northamerica.msteched.com/sessionpreference?fbid=I1Ydc71JM4a. They want to know which sessions you are interested in seeing. I have one session on the list. I am not going to say which one, since TechEd did not include the names on the site. I like the idea, this way it's not a popularity contest. If you have time, go and check out what they are doing. Select your preferred sessions. I think it's a great way to find out what the community wants.

Talk to you soon,

Patrick LeBlanc, SQL Server MVP

Founder www.SQLLunch.com

January Baton Rouge SQL Server User Group

2011-01-11T10:25:00.001-08:00

The Baton Rouge SQL Server User Group will be hosting its first meeting of the new year. Our meeting place has changed to Louisiana State University in Patrick F. Taylor Hall. Both SQL Saturday's held in Baton Rouge were located here. If you haven't attended a meeting I recommend that you come out to this meeting. We have two great presentations and some really great giveaways. Wait, did I mention that I was speaking. That should definitely get you out. I will be also giving away a copy of our book, Knights Microsoft Business Intelligence 24 Hour Trainer . Still not convinced, well there is always the free Pizza and great Networking. The Baton Rouge .Net User Group will also be meeting that evening. So, if you are in the area come and hang out with us.

Topics: Introduction to SQL Replication – Transactional (Mark Verret) and Taming your SSIS Configuration Files (Patrick LeBlanc)

Location: LSU, Patrick F. Taylor Hall, Room 1118. Click here for directions.

Date and Time: 1/12/2011 5:45:00 PM
Sponsored By: Envoc

Agenda:
5:45 - 6:15 pm: Networking and Refreshments
6:15 - 7:00 pm: Introduction to Replication
7:00 - 7:45 pm: Taming your SSIS Configuration Files
7:45 - until: Open Forum for questions and Raffle

SQL Server User Group

Speaker: Mark Verret

Topic: Introduction to SQL Replication – Transactional

Description: An introduction to SQL Replication with a focus on Transactional Replication. What is SQL Replication and why would you want to use it? The presentation will involve a demonstration and discussion of usage in a production environment.

BIO: Mark Verret has been in the IT field for over 10 years. He is currently the System Administrator and DBA for the LSU Highway Safety Research Group. For the past 2 years he has taken on the DBA role involving SQL replication, warehousing, reporting, etc. Along with teaching an undergraduate business intelligence course, he has also presented at the Baton Rouge SQL user group and Baton Rouge and Dallas SQLSaturday.

Speaker: Patrick LeBlanc, SQL Server MVP

Topic: Taming Your SSIS Configuration Files

Description: Typically, you develop SSIS packages on your laptop, desktop or some client machine and then deploy them to a server. In most cases these environments are completely different. They may have different machine names, folders, connection strings, etc… To assist in easing the pain of deploying your SSIS packages, SSIS includes a range of options that help you when building your data integration solutions. These options include: Property expressions, SET options of DTEXec, and my favorite Package Configurations. In this session I will attempt to demystify the usage of Configurations files in your SSIS package with a particular focus on Child and Master package solutions.

BIO: Patrick LeBlanc, SQL Server MVP and author, is currently a Business Intelligence Architect for Pragmatic Works. He has worked as a SQL Server DBA for the past 9 years. His experience includes working in the Educational, Advertising, Mortgage, Medical and Financial Industries. He is also the founder of TSQLScripts.com, SQLLunch.com and the President of the Baton Rouge Area SQL Server User Group. Patrick is a regular speaker at various SQL Server community events, including SQL Saturday’s, User Groups and the SQL Lunch.

Patrick Leblanc, SQL Server MVP, MCTS

Founder, www.SQLLunch.com

First SQL Lunch of the Year - SSRS Tips and Tricks with Pam Shaw

2011-01-11T08:52:00.001-08:00

Last Year was a great year for the SQL Lunch. We had so many great presentations. I am really excited about this year and what we have coming up. There are three lunches scheduled for this month and we are going to be making a BIG announcement about what we are doing in May on the SQL Lunch. Stay tuned.

45-Tips & Tricks for dynamic Reporting Services Reports

Speaker: Pam Shaw

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

Add To Outlook: Add To Calendar

Date and Time: 1/13/2011 11:30:00 AM CST

Topic: #45-Tips & Tricks for dynamic Reporting Services Reports
In this session we will explore the multitude of ways that you can make your reports adapt to the needs of your users. This will include defining formats based on the data contents. We will explore how to refine layouts based on the data to be delivered. We will demonstrate how to create a parameter driven data source. We will take a look at how creating templates can give your catalog of reports a more professional look. All of this together will empower you to have more control over the reports you create.

BIO: Pam Shaw has been in IT for almost 30 years. Since 2001, Pam has been working with SQL Server, first 2000, then 2005 and now 2008. Pam is currently an independent contractor. Pam is also the Chapter Leader of the Tampa Bay SQL Users Group - a local chapter of PASS and organizer of SQL Saturday Tampa.

Patrick LeBlanc, MCTS, SQL Server MVP

Founder www.SQLLunch.com

Learning SSIS in Under 1 Hour on the SQL Lunch

2010-12-13T05:32:00.001-08:00

Don't miss the SQL Lunch tomorrow.

#44-Learning SSIS under 1 hour

Speaker: Jose Raul Chinchilla

Add To Outlook: Add To Calendar

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

Date and Time: 12/14/2010 11:30:00 AM CST
BIO: Jose Chinchilla is a Microsoft Certified Database Administrator and Business Intelligence Developer working as a BI consultant for Convergence Consulting Group, a Tampa based full service consulting firm. Jose has12+ years of experience in IT and has focused his career in OLTP and OLAP database design, development and administration and specializes in ETL/ELT, Data Warehousing and Multidimensional Analysis using SQL Server 2008 BI tools. He is also the current president of the Tampa Bay area Business Intelligence User Group and Official PASS Chapter. He is an avid twitterer under the @SQLJoe handle and blogs at http://www.sqljoe.com.

Topic: #44-Learning SSIS under 1 hour
Need to know what is the corresponding SSIS transformation or task to a specific T-SQL statement? How do you accomplish Unions, Joins, and Merges in SSIS? How do you do CASE statements in SSIS? How do you group and aggregate data in SSIS? In this session I will cover the some of the most important SSIS control flow and data flow tasks to jump start DBAs and Developers into SSIS with SQL Server 2008-R2. At the end of this interactive demo, you will be able to identify what control flow tasks and data flow transformations can be used in place of T-SQL statements and scripts. I will also cover how to accomplish in SISS: • Basic SELECT, INSERT, DELETE, UPDATE statements • Inner and Outer joins • CAST and CONVERTS • CASE statement • Sorting, Grouping and Aggregate functions (SUM, COUNT, MAX, AVG) • MERGE and Unions • Loops (WHILE)

Talk to you soon,

Patrick Leblanc, SQL Server MVP, founder SQLLunch.com

Passing SSRS T-SQL Values to SSRS MDX Parameters

2010-11-30T21:54:00.001-08:00

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:

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:

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

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

Happy Days are here again!

Talk to you soon,

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

Handling Type II Dimension with the MERGE Statement

2010-11-16T19:32:00.001-08:00

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

My SQL PASS Summit Adventures part 1

2010-11-15T18:38:00.001-08:00

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.

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.

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.

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

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

Join SQL Lunch on Tuesday

2010-11-14T06:48:00.001-08:00

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

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

A Couple of SQL Lunches this Week

2010-11-03T05:34:00.001-07:00

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

Speaker: Adam Jorgensen

Add To Outlook: Add To Calendar

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

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

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

Vote for Our SQL Rally Pre-con

2010-10-31T18:37:00.001-07:00

VOTE VOTE VOTE VOTE VOTE VOTE VOTE!!!!!!!!!!

Vote here: http://www.zoomerang.com/Survey/WEB22BD59JCQBT

Honestly, I want each of reading my blog to vote for our Pre-con(Full Day Business Intelligence Workshop). However, as with any voting process you have choices. Well....in most cases you do. We often feel or believe that our vote does not count. I really believe that this is going to be a little different. I am really excited about the SQL Rally and more excited that our Preconference seminar was selected as one of those that are now being voted upon. So, why should you vote for our Pre-con?

I have been referencing the pre-con as "Ours", that is because four individuals will be delivering the conference. The team of presenters/authors include Adam Jorgensen, Mike Davis, Devin Knight, and myself. I am not going to list the credentials for each person, but I will say that each presenter has taught full day courses in building Data Warehouses, SSIS, SSAS, and SSRS. In addition, we are all regular speakers at various SQL Server events, including SQL Saturdays, Users Groups, and PASS.

Still not convinced? Each member of this team has designed, created and delivered solutions that encompass each of the tools that will be presented during the Precon. First, we will spend time teaching you how to transform your Operational database to a data warehouse. Adam Jorgensen will take a deep dive into Dimensional Modeling and all the techniques required to create you data warehouse database. In the next three sections, which are all very demo-centric, you will learn from Devin Knight, Mike Davis and myself how to build SSIS Packages to load your data warehouse fully or incrementally, then we will show you how to create and maintain an SSAS cube and finally we will use SSRS to create visually appealing reports for your end-users.

This may seem like a lot to accomplish in one day, but we will be taking a very explicit and focused approach in regards to each technology. For example, we are not going to discuss every transform in the SSIS control and data flow toolboxes. However, we will provide detail explanations of the tasks that are vital when building and ETL solution. At the end of the day you will leave with the knowledge and tools needed to build a complete data warehouse solution. As an extra touch, we will not be using the traditional Adventure Works database for our demonstrations. We will be using some real world data that will enhance the discussions and potentially create a more engaging learning experience for all attendees.

So with that, I am not going to restate our abstract, nor am I going to talk about how good all the other sessions are. What am I going to say is that as a part of the community and a potential attendee of these Precons, go and vote so your can make a difference! Hey, I kind of sound like a politician, maybe I will run for the PASS board next year ;). See you all at the PASS Summit in two weeks and hopefully next year at our SQL Rally Preconference Seminar.

Go make it happen! There's a lot of good seminars, go read about them here:

BI Sessions: http://www.sqlpass.org/sqlrally/2011/PreConsforVotingBI.aspx

DBA Sessions: http://www.sqlpass.org/sqlrally/2011/PreConsforVotingDba.aspx

Developer Sessions: http://www.sqlpass.org/sqlrally/2011/PreConsforVotingDev.aspx

Misc Sessions: http://www.sqlpass.org/sqlrally/2011/PreConsforVotingMisc.aspx

Talk to you soon,

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

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

1st Houston Microsoft Business Intelligence User Group

2010-10-19T13:46:00.001-07:00

Tyler Chessman of Microsoft has started a Business Intelligence Users Group in Houston. The group will meet online and onsite quarterly. If you are interested in attending here are the meeting details:

Houston – Microsoft Business Intelligence User Group

We would like to invite you to the very 1^st Houston Microsoft Business Intelligence User Group. This event will take place on Wednesday October 20^th, 2010 from 11:15 – 1:00 p.m. at the Microsoft Houston Office (location and map below). LiveMeeting will also be available for remote attendees.

Learn the latest and greatest in BI - along with practical advice for getting the most out of your existing technology investments.

No RSVP needed.

Cost

This is a free event; lunch will be provided.

Who should attend

BI Practitioners from both an IT & business background should attend. We’ll have two presentations (see Topics below) of interest to both IT and business analysts/power users.

Date
Wednesday, October 20^th, 2010. 11:15 a.m. – 1:00 p.m.

Location

One Briar Lake Plaza

2000 W. Sam Houston Pkwy. S. #350

Houston, TX 77042

Phone: (832) 252-4300

See map and/or driving directions

Live Meeting Link for Remote Attendees - Join the meeting

Make sure the Office Live Meeting client is installed before the meeting:

· I am connecting from inside the Microsoft network

· I am connecting from outside the Microsoft network

Topics

· Introducing SQL Server 2008 R2 Master Data Services (MDS):

Master Data Management is a key enabler in improving the timeliness, quality and reliability of BI with the ultimate goal of improving business performance. This session will be a primer on how to get started with Master Data Management using MDS.

Speaker: Satheesh Kumar is the principal/lead consultant for Catapult Systems, a Microsoft-focused IT consulting firm. Satheesh has been developing with Microsoft technologies since 1996 and has deep experience in web applications, business intelligence and business analytics. He is currently focused on providing professional solutions using the latest technologies, including the Microsoft BI technology stack. He has an MBA from RICE University, interested in data mining research. He can be reached online at http://businessintelligence101.wordpress.com/

· Excel Tips and Tricks - Working with External Data

In this talk, Jim is going to share tips/tricks for working in Excel, specifically how to build flexible data extracts from relational databases, including passing parameters from Excel to SQL Server or Access.

Speaker: Jim Cline is a Senior Manager of Ad-Hoc Reporting at Service Corporation International, the world’s largest funeral home and cemetery consolidator; he has been with SCI for about 14 years. Jim’s department creates ad hoc reporting in Excel, Access, SQL Server, and web-based reports and applications. Jim has a strong business background and mastery of Excel, including:

• Previously with Arthur Andersen as a tax accountant and real estate appraiser (7 years).

• Degree from the University of Houston with a BBA in Accounting and Taxation.

• Founded ClineSys, an Excel and Access training system. Currently has two Excel courses (2003 and 2007) and two Access/SQL courses (2003 and 2007). www.ClineSys.com . ClineSys is approved by the National Association of State Boards of Accountancy (NASBA) and CPAs can earn 40 hours of CPE credit by taking the Excel (2003 or 2007) course.

Come out and support the new group.

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS, founder SQL Lunch

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

MDX Puzzles (New Author)

2010-10-18T16:27:00.001-07:00

Well I am sure most of you noticed that I haven't posted an MDX puzzle in some time. After much consideration I have decided to pass the torch to someone that I feel will devote the time needed to help you all continue with your learning. So with that, I would like to introduce or re-introduce you all to Dustin Ryan the new MDX Puzzle guy. Dustin is a consultant for Pragmatic Works and a regular blogger on BIDN.com. He is also very skilled with MDX.

I am not quite sure when Dustin is going to start posting the puzzles, but I am sure it is going to be soon. Thanks Dustin for stepping taking in over.

Talk to you soon,

Patrick LeBlanc (form MDX puzzler), SQL Server MVP, MCTS and founder SQL Lunch

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

SQL Lunch #40 - Mirroring: The Bear Necessities

2010-10-18T05:23:00.001-07:00

I am excited to announce a new speaker to the SQL Lunch, Ryan Adams. Ryan will be discussing Database Mirroring and how it has become an integral part of high availability and disaster recovery planning. See the following meeting details:

Speaker: Ryan Adams

Meeting URL: https://www.livemeeting.com/cc/usergroups/join?id=6J9BKQ&role=attend

Add To Outlook: Add To Calendar

Date and Time: 10/19/2010 11:30:00 AM CST
BIO: Ryan is a highly skilled Microsoft oriented administrator and has worked for a Fortune 100 company for 12 years. In his time there he has supported everything from desktop to server. Most recently he has served as a senior Active Directory Architect and Identity Management (MIIS, ILM, FIM) Consultant. He is the sole SQL Server DBA for his group, supporting both vendor databases and custom databases. His primary focus is database design and performance, but also works with SSRS report development, and SSIS ETL. He enjoys being involved in the SQL community and serves on the board of directors for the North Texas SQL Server User Group. He is well versed in many core Microsoft technologies, has implemented and supported them both nationally and internationally for a company of 250k+ users, and holds the following certifications: MCP MCSA MCSE MCDBA - SQL 2000 MCTS - SQL 2005 MCITP - SQL 2005

Topic: #40-Mirroring: The Bear Necessities
Mirroring can be an integral part of your high availability and disaster recovery planning. We’ll cover what mirroring is, how it can fit into an HA/DR plan, the rules surrounding its use, configuration via the GUI and T-SQL, as well as how to monitor mirroring. This presentation is designed to not only give you an overview of mirroring, but to also walk you through a basic implementation. At the end you will have learned what mirroring is, how it can fit into your environment, what business requirements it solves, and how to configure it.

See you at Lunch,

Patrick LeBlanc, founder SQL Lunch, SQL Server MVP, MCTS

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

Speaking at SQL Saturday #49 Orlando

2010-10-14T06:18:00.001-07:00

After making the trip last weekend to Houston for Houston Techfest with the kids I am off again, but this time alone. On Friday I will fly into Orlando for the Speaker dinner, which is always a lot of fun. On Saturday I will be presenting two sessions. The first is a mini session on Speaking and the second is on using Change Data Capture and SSIS to load a Slowly Changing Dimension. This is my second time speaking at SQL Saturday in Orlando. If you are in the area stop by. Registration is still open.

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS, Founder SQL Lunch

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

Why do I contribute to the SQL Server Community?

2010-10-12T16:09:00.001-07:00

As an active speaker and volunteer in the SQL Server community I am often asked why do I do it? Sometimes, I actually ask myself that question, but when I think about how the community as a whole operates (quick to answer questions and join discussions) any doubt is quickly removed. Think about this, in what community can you post a question on twitter or send an email to someone you met at a community event and before you know it, you have several responses to your question. I became active in the community almost three years ago and I was embraced (not physically, well maybe by a couple) by several people who I considered to be some the of the top SQL Server professionals in the community. So why not give back? If I had to list the TOP (5) reasons as to why I do it, it would hard for me to narrow it down. Fortunately, I am always up to a challenge so here are my TOP 5 reasons:

1. Contributing to the community, continually increases my knowledge about SQL Server and all of it's components.

2. Allows me to meet a bunch of really talented SQL Server professionals. Networking, Networking, Networking....

3. Can't forget about job opportunities, because I landed my current job with Pragmatic Works at a SQL Saturday event where I was speaking.

4. Allows me to travel to places that I have never been (a selfish reason) ;).

5. Honestly, I enjoy it.

So why do you contribute? Write your own blog post and share it with the community.

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS, Founder SQL Lunch

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

Speaking at South Florida SQL Server User Group

2010-10-12T04:55:00.001-07:00

I will be speaking at the South Florida SQL Server User Group on October 13 via Live Meeting. My topic is Introduction to Change Data Capture with SSIS. If you have some time logon and watch the meeting.

Meeting URL: https://www.livemeeting.com/cc/mvp/join?id=PWWZ93&role=attend&pw=47WFd%7EC%5CJ

In thisLeBlanc presentation I will Introduce Change Data Capture and show how it can be used to incrementally load a Slowly Changing Dimension. Hope to see some of you online.

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS, Founder SQL Lunch

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

October SQL Server & .Net User Group Meetings

2010-10-07T21:41:00.001-07:00

October .NET & SQL User Group Meetings

This month the Baton Rouge SQL Server and .Net user groups are holding a joint meeting. We will be meeting at our usual location. If you are around Baton Rouge on Wednesday October 13th stop by. The meeting details are as follows:

Location: Lamar Advertising, 5551 Corporate Blvd, Baton Rouge, LA

Date and Time: 10/13/2010 5:45:00 PM
Sponsored By: Sparkhound
Agenda:
5:45 - 6:15 pm: Networking and Refreshments
6:15 - 6:30 pm: Lightning Round
6:30 - 7:45 pm: (.NET) Visualize Data in a “geo way” with WP 7 and Silverlight
6:30 - 7:45 pm: (SQL) Building Spatial Data Reports with SSRS 2008 R2 and Powershell for the DBA
7:45 - until: Open Forum for questions and Raffle

Lightning Round (Lamar Kitchen)
Topic: Introduction to the Pomodoro Technique
Overview: For many of us time is an enemy. The anxiety triggered by “the ticking clock” and deadlines to be met leads to ineffective work and study habits and procrastination. In this lightning round we’ll look at how the Pomodoro Technique can help transform time into a valuable ally.

Speaker: Stacy Vicknair, VB.NET MVP, MCTS

BIO: Stacy Vicknair is a development consultant working for Sparkhound and a Microsoft MVP for two years running in Visual Basic. Stacy blogs at his website, http://www.wtfnext.com, about .NET and other general development topics. He is active in his local community as a committee member for the Baton Rouge .NET User Group (http://www.brdnug.org) and
a co-founder for the Baton Rouge Architecture Group (http://www.braglunch.com).

SQL Server User Group (3rd Floor Conference Room) Two Topics

Session One

Topic: Building Spatial Data Reports with SQL Server 2008 R2 Reporting Services Overview: Microsoft introduced spatial data types in SQL Server 2008 and since has enhanced the reporting capabilities in the following release of SQL Server 2008 R2. This session will introduce the concept of spatial data types in SQL, provide some tips and tools for getting spatial data into SQL, and show different methods for including spatial data in SQL Reporting Services Reports.

Speaker: Mark Verret

BIO: Mark Verret is the Systems Administrator for the LSU Highway Safety Research Group. He has over 10 years of experience in the IT field. He has presented at the past two SQLSaturday events in Baton Rouge. He has functioned as DBA for the past 3 years working with SQL 2000, 2005, 2008, and 2008 R2.

Session Two

Summary: Powershell for DBAs- introduction and examples of how Powershell 2.0 can make you a more versatile SQL Server Administrator

Speaker: William Assaf

Bio: William Assaf, MCITP-DD, is a Senior Development Consultant and Team Lead for Sparkhound, Inc. in Baton Rouge, LA. He blogs on SQL Server at http://sqltact.blogspot.com, where you can find the slides and code examples of this presentation.

.NET User Group (Lamar Kitchen)

Topic: Visualize Data in a “geo way” with WP 7 and Silverlight Overview: Geospatial data is being used everywhere these days, from social media, to citizen reporting 311 applications, to tracking government spending. This talk will discuss design tips and go over the requirements for creating a better user experience and how to visualize data in a “geo way” for Windows Phone 7 and Silverlight applications.

Speaker: Al Pascual
BIO: Company: ESRI
Al Pascual was born, bred and educated in Barcelona Spain,. He moved to England as a young lad to work as a developer in start-ups in the technology field. After seven years working in many small companies, Al moved to California with dreams of stock options to work for another start-up and enjoyed the world of low-level C and C++. After spending a few years working as a consultant, when .NET came out, Al helped companies be more productive. He became a pure web developer, believing that HTTP will rule the world and ASP.NET had nothing to do with classic ASP. Finally growing up and joining the corporate world
to be part of the matrix, Al now has a wife, 3 daughters, 2 dogs, 2 rats, 1 turtle and a mortgage. He’s happy to work for a big company like Esri as a senior software developer working mainly in Silverlight and ASP.NET.

See at the User Group

Patrick LeBlanc, SQL Server MVP, MCTS, SQL Server Chapter Leader

SQL Lunch #32: SSIS Design Patterns, with Andy Leonard

2010-10-07T19:10:00.001-07:00

Don't miss SQL Server MVP Andy Leonard on the SQL Lunch, Monday, October 11th.

#32-SSIS Design Patterns 1c: Variables, Events, and Automated Custom Logging Patterns

Speaker: Andy Leonard

Add To Outlook: Add To Calendar

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

Date and Time: 10/11/2010 11:30:00 AM CST
BIO: Andy Leonard is an Architect with Molina Medicaid Solutions, SQL Server database and Integration Services developer, SQL Server MVP, PASS Regional Mentor, and engineer. He is a co-author of Professional SQL Server 2005 Integration Services and SQL Server MVP Deep Dives. Andy blogs for SQLBlog.com. His background includes web application architecture and development, VB, and ASP; SQL Server Integration Services (SSIS); data warehouse development using SQL Server 2000, 2005 and 2008.

See you at Lunch

Patrick LeBlanc, SQL Server MVP, MCTS, Founder SQL Lunch

Speaking at Houston Techfest

2010-10-06T19:42:00.001-07:00

I am heading down to Houston, TX to speak at the Houston Techfest this weekend. Being a Houston native makes this trip even more exciting. This is a free event that offers a diverse set of sessions. The sessions range from What's new in Java to Introduction to Window Azure. I attended the event last year and if my memory serves me correctly there were only two SQL Sessions. Fortunately, I was able to convince the event coordinator, Michael Steinberg, to include a full SQL Track at the event. This year the event will host at least 7 SQL Sessions. I will be giving two presentations. The first is a presentation on Backup and Data Compression and in the second session I will be introducing the use of Change Data Capture with SSIS.

This event attracts approximately 1000 people from all across the state. If you are near the Houston area this weekend I recommend that you try to attend. Not only is it a great chance to learn something new, but you may even win a Trip to Las Vegas. One thing I forgot to mention was that lunch is FREE. So I hope to see you all there. Also, if you attend my second session of the day you will have a chance to win Knight's Microsoft Business Intelligence 24-Hour Trainer, which I co-authored. Hope to see you there!

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS, Founder www.sqllunch.com

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

SQL Lunch One Year Anniversary

2010-10-04T07:55:00.001-07:00

I am very excited to announce the 1 year anniversary of the SQL Lunch. Last year around this time I was getting the site up and trying to find a couple of speakers to ensure that we had a good start. I was able to convince Tim Mitchell to speak and the rest is history. Thanks to everyone that has attended all of the lunch events and thanks to all of the speakers who gave their time to make the site a success.

During the first year I gained a co-host (Adam Jorgensen) and several new friends along the way. I also hired the first SQL Lunch employee, Patrick Jr. (my son). He and I are working hard on improving the site and the notifications. Thanks again to everyone and see you at the next SQL Lunch. As always, we are looking for speakers so email us at webmaster@sqllunch.com if you are interested in speaking.

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS

Founder SQL Lunch

Maintain Change Data Capture Configuration when restoring a database

2010-10-03T14:22:00.001-07:00

I have been working with a several clients that are leveraging Change Data Capture (CDC) as a tool for incrementally loading their Data Warehouse. In most cases the clients are using database restores from various environments to create test environments. When databases with CDC enabled is restored to the same server with the same database name, CDC remains enabled and all related meta data is persisted. However, when restoring the database to another server or to the same server, but with a new database name, CDC is disabled and all related metadata is deleted. To retain the CDC configuration when restoring, simply use the KEEP_CDC option when restoring your database. The following script shows an example of how to accomplish this:

RESTORE DATABASE TestCDC

FROM DISK = 'C:\MMSQL\BACKUPS\TestCDC.bak'

WITH KEEP_CDC

The key item here is to specify KEEP_CDC as an option of the restore. As always, if you have any questions concerning this post please comment here or send an email to pleblanc@pragmaticworks.com.

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS

Founder SQL Lunch

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

SQL Saturday #57 (Houston) Call for Speakers and Registration is Open!!

2010-10-03T11:03:00.001-07:00

So many people have been waiting for a SQL Saturday in Houston and now it’s finally here. As a native of Houston I was surprised that it wasn’t one of the first SQL Saturday venues. Well the wait is over. Thanks to Nancy Wilson, the local SQL Server User Group Chapter Leader, Jonathan Gardner and Malik Al-Aminthe, the first Houston SQL Saturday (#57) will be held on January 29, 2011. Several great sessions have already been submitted, but they are definitely accepting more. In a city the size of Houston they are expecting an overwhelming number of attendees for this event. If you are a speaker that has never given a presentation at a SQL Saturday this may be a good place to get your start. Go here to sign up. If you are not quite ready to speak, but would like to attend click here to register. See you in Houston.

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS

Founder SQL Lunch

SSIS: Assign a value to Variable using Dataflow Script task

2010-09-19T16:29:00.001-07:00

I am not quite sure how many of you have tried to set or change the value of a variable using an SSIS script task, but if you have tried I am sure that you may have ran into a few road blocks. Recently I tried to do this and I quickly realized that it is not as straightforward as I thought. To do this configure your package so that you have a source of some type on the data flow design surface. Drag a script task onto the data flow and connect it to the source. Typically, you would set the variable you want to read from or write to as a ReadOnly or ReadWrite value on the Custom Properties of the script task.

This is only the case on the Control Flow. In the case of the data flow this is not required. I don't claim to be a developer, but I do know how to use the Internet to find a solution to a problem. After searching for about 10 minutes I found three posts that assisted me in solving the problem. Each post provided a little snippet of code that added to my solution. Here is the code:

   1:      public override void Input0_ProcessInputRow(Input0Buffer Row)

   2:      {

3:

   4:          IDTSVariables100 vars = null;

   5:          VariableDispenser.LockOneForWrite("intMaxSalesDetailsID", ref vars);

   6:          if(!Row.SalesDetailID_IsNull)

   7:              vars[0].Value = Row.SalesDetailID;

   8:          vars.Unlock();

   9:      }

On line 4 declare an interface that is going to allow us to access the variable. In the next line we lock the variable for writing. In line six ensure that the value that is being assigned to the variable from the Input Row is not null. Then on the next line set the value of the variable to the desired column from the buffer. Lastly, unlock the variable. That's all to it. If you have other method, preferably a simpler approach, send me an email at pleblanc@pragmaticworks.com or post it here.

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS

Founder TSQLScripts.com and SQLLunch.com.

Baton Rouge SQL Server User Group Meeting Tonight

2010-09-15T04:33:00.001-07:00

Last Night (9/14/2010) I had the opportunity to present to the Wisconsin SQL Server User Group. My topic was SSIS- Configuration Files, Deployment and a Little Performance Tuning. The presentation was well received and I hope to have the opportunity to speak to the group again on another occasion.

Tonight(9/15/2010) I will be speaking in front of my local SQL Server User Group in Baton Rouge. The topic is Loading a Data Warehouse with SSIS. In addition, I will doing a short presentation (Lightning Round) to the local .Net User group just before the SQL User group begins. If you are in and around the Baton Rouge area this even stop by. The details for both groups are as follows:

There will be great prizes supplied by Enta including:

A Nook (plays all three e-reader types), Madden 2011, Windows 7, and many more...

Date: September 15, 2010

Time: 5:45 pm

Location: Lamar Advertising, 5551 Corporate Blvd, Baton Rouge, LA 70808

Sponsored By: User Group Support Services

Agenda:

5:45 - 6:15 pm: Networking and Refreshments

6:15 - 6:30 pm: Lightning Round

6:30 - 7:45 pm: (.Net) Introduction to RIA Services and (SQL) Loading a DataWarehouse with SSIS

7:45 - until: Open Forum for questions and Raffle

Lightning Round (Lamar Kitchen)

Topic: Introduction Data-tier applications

Overview: SQL Server 2008 R2 introduced a new feature called Data-tier applications (DAC). A data-tier application defines the SQL Server Database Engine schemas and objects that are required to support an application. Join Patrick for a brief introduction to this new feature and learn how to author, build and extract the applications from an existing database using Visual Studio 2010 and SQL Server Management Studio.

Speaker: Patrick LeBlanc, SQL Server MVP, MCTS

.Net User Group (Lamar Kitchen)

Topic: Introduction to RIA Services

Overview: RIA Services and Silverlight 4.0 are recent releases and are tightly integrated with .NET 4.0 and Visual Studio 2010. RIA services is a very powerful productivity tool/framework and can be a bit overwhelming when trying to learn. This session will cover an overview of RIA services and then dive into the details using the new Silverlight Business Application Template in VS.NET 2010. We will cover some tips to modifying the code that is generated by the template to tailor it for most enterprise environments.

Speaker: Mike Huguet

BIO: Company:

Sparkhound Blog: http://geekswithblogs.com/mikehuguet

Mike Huguet is a Solutions Architect for Sparkhound. He is an active member of the community acting as co-leader of the Baton Rouge .NET User Group and committee member for the BR SQL Saturday event. He is also a member of the Microsoft SharePoint Patterns and Practices Advisory team for the recent release for SharePoint 2010. Mike has a software development background and has been working with .NET since Beta 1 of the 1.0 framework.

SQL Server User Group (3rd Floor Board Room)

Topic: Loading a DataWarehouse with SSIS

Overview: With SSIS Data Warehouse developers have a new method of loading their DW. In this session Patrick LeBlanc will show you how to use SSIS to load your Dimensions and Facts. We will discuss different methods that can be used in various environments. He will show you how to fully reload or incrementally load your Data Warehouse.

Speaker: Patrick LeBlanc, SQL Server MVP, MCTS

BIO: Patrick LeBlanc, SQL Server MVP, is currently a Business Intelligence Architect for Pragmatic Works. He has worked as a SQL Server DBA for the past 9 years. His experience includes working in the Educational, Advertising, Mortgage, Medical and Financial Industries. He is also the founder of TSQLScripts.com, SQLLunch.com and the President of the Baton Rouge Area SQL Server User Group. Patrick is a regular speaker at various SQL Server community events, including SQL Saturday’s, User Groups and the SQL Lunch.

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS

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

Where do you get your motivation and drive?

2010-09-06T12:03:00.001-07:00

I was asked by someone the other day, where do you get your drive and motivation? For a second, I was stumped. After giving it a little thought I realized that my drive has several sources. The first is my family of course. My wife has this unbridled drive for success that cannot be measured at any level. Whenever anyone in the house says I can’t, I don’t know how to…, You can’t or some variation of those sentences she immediately corrects us. Can’t is not part of our vocabulary. Next it’s my kids, they think I am Superman, Bill Gates, Jay Z, Barack Obama, a barber, a mechanic, a chef, etc.. You get the picture. It's funny, if we are watching TV or we see someone do something, one of my kids will always say, Dad you can do that. With that long list of expectations a man has to be driven to learn and succeed.

Then there is the SQL Server Community. For me the community is boundless. We learn from each other. I became active in the community about 3 years ago when I started a SQL Server User Group in Baton Rouge. I had just installed SQL Server 2005 and set up a GEO cluster. In the process I learned so many interesting things about the new features of 2005 and I wanted to share them. I talked to the local .Net user group then I spoke with PASS and the user group was born. After the first presentation I became addicted to presenting. I get a rush from sharing what I learn and for some reason not being able to answer a question. This drives me to learn more so I can share more. So I guess my addiction to presenting and the need to share information is a major contributor to my drive.

Another source and probably the biggest factor to my motivation is myself. Everyone morning when I wake up I say, this is going to be a productive day. What does productive mean? I am going to write a new presentation, I am going to get a new speaker for the SQL Lunch, I am going to help someone solve a problem, I am going to learn something new about SQL Server, I am going to read something new today, I am going to help someone start a User Group. I often end the day with a recount of the day. What did I do? Some would call me a GEEK or maybe even a NERD, but these days that equals success. Think back to your youth, and all of the geeks or nerds that you knew. Look them up. What are they doing? I actually talked to a couple of the GEEKS that went to our high recently and they are pretty successful. I am not saying that all GEEKS are successful, but there seems to be a decent trend in their favor.

Finally, there's GOD and my parents. We all know that all this that you know as PATRICK would not exist. So, I definitely have to mention that trio.

So, what drives you? What is your motivation? Share it with us.

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS

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

MDX Puzzle #7 - Solution

2010-09-03T06:20:00.001-07:00

Puzzle #7 had an interesting twist. You were required to add a column to the result set that dynamically displayed a currency type based on the existing currency code. As always, there are a couple of ways to solve this puzzle. So here is what I started with:

This query satisfies most of the requirements, but one thing you should notice here is the tuple. I started out here with only the Country and State-Province, which I thought would work. Then I added the following calculated member:

In the calculation I used the IIf function to dynamically determine whether or not the currency code was US or International. The function is used in the same way as you would use it in Excel or Reporting Services. It accepts three arguments. The second and third arguments are returned based on the evaluation of the first argument, which is the logical expression. This expression should evaluate to true or false. If it evaluates to true the second argument is returned and if false the third argument is returned. In addition, I used the CurrentMember function. This function obtains a reference to the member identified for a particular hierarchy in the current context. When I coupled the function with my initial query, I thought I was done. Unfortunately, the calculation evaluated every code to International even when it was USD.

To solve the problem I had to modify the tuple from the first query to include the Source Currency Code. After the change was made the results satisfied all of the puzzle requirements. Here is the solution:

WITH MEMBER [Measures].[Currency Type]

AS

IIF

    [Source Currency].[Source Currency Code].CurrentMember=[Source Currency].[Source Currency Code].[USD],

    "US",

    "International"

SELECT

    {[Measures].[Reseller Sales Amount],[Measures].[Currency Type]} ON COLUMNS,

    NONEMPTY(

            [Sales Territory].[Sales Territory Country].[Sales Territory Country],

            [Geography].[Geography].[State-Province],

            [Source Currency].[Source Currency Code].[Source Currency Code]

    )ON ROWS

FROM [Adventure Works]

Tell me what you think and post your solutions. Let's see how much our solutions differed. Stay tuned for Puzzle #8.

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS

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

Join me for Lunch - SQL Lunch #33 (Indicators, Sparklines and Databars)

2010-08-30T10:22:00.001-07:00

#33-SSRS 2008 R2 - Indicators, Sparklines and Databars

Speaker: Patrick LeBlanc

Add To Outlook: Add To Calendar

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

Date and Time: 9/1/2010 11:30:00 AM CST
BIO: Patrick LeBlanc, SQL Server MVP, is currently a Business Intelligence Architect for Pragmatic Works. He has worked as a SQL Server DBA for the past 9 years. His experience includes working in the Educational, Advertising, Mortgage, Medical and Financial Industries. He is also the founder of TSQLScripts.com, SQLLunch.com and the President of the Baton Rouge Area SQL Server User Group. Patrick is a regular speaker at various SQL Server community events, including SQL Saturday’s, User Groups and the SQL Lunch.
Topic: #33-SSRS 2008 R2 - Indicators, Sparklines and Databars
SQL Server Reporting Services R2 introduced several new features. In this lunch Patrick LeBlanc will provide three demonstrations that will show you how to create Indicators, Databars and Sparklines on your reports.

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS

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

MDX Puzzle #7

2010-08-29T14:38:00.001-07:00

I have presented you with a couple of easy puzzles in the last few posts. So, let's step it up a little with one that is a little more challenging. I have to be honest, this puzzle was sent to me by one of my co-workers at Pragmatic Works. Here are the requirements:

Columns: Reseller Sales Amount and using the Source Currency Code add a column to the result that displays US if the currency code is USD and International for all other currency codes.

Rows: Country and State-Province

Filters: None

Hint: You may need to use a CASE statement or the IIF function.

Your results should show Reseller Sales Amount by Country and State-Province. An additional column will also be included that displays the currency type. Be careful when designing your tuple for the rows. You may notice that I have intentionally omitted one small nugget of information in the requirements. Your results should resemble the following:

Notice the additional column that corresponds to the currency code. 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.

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.

MDX Puzzle #6 - Solution

2010-08-27T11:13:00.001-07:00

Puzzle #6 should not have presented too much of a challenge, it's intentions were to introduction you to the ORDER function that is available in MDX. Ok, so this is what I started with:

This is a very simple statement that satisfies most of the puzzle requirements. The only one that is does not satisfy is the ordering of the results. To do this I used the ORDER function. The function accepts two arguments. The first is a valid expression that returns a set. In this example it is the set of Product Subcategories. The next argument is the numeric or string expression, which will be used to determine the order. Finally, you must include the direction that your want to order by, DESC or ASC. There are a couple of more, but I am saving them for a later puzzle.

Modifying the above query to include the ORDER function yields the result to this puzzle. See the following:

Stay tuned for Puzzle #7, I promise it will be a little more challenging.

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 #6

2010-08-24T19:39:00.001-07:00

As we continue with the puzzles, I will continue to introduce new MDX functions. In this puzzle the requirements are as follows:

Columns: Reseller Sales Amount

Rows: Product Subcategories

Filters: None

Hints: This puzzle is too easy for a hint. ;)

Ahhhh, but there is one additional requirement, the resulting rows should be returned in DESC order by Reseller 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 #28 - Lessons Learned

2010-08-22T17:56:00.001-07:00

After spending a week in Tampa, FL at a client site I've had some time to unwind and reflect back on SQL Saturday #28. I have received some really positive feedback from all individuals involved. While I can't say that every aspect of the event was excellent, I can say that compared to last years event this one far exceeded any of my expectations. I have read several blog posting about the event and I have to issue one correction, most of you are reporting our attendance as 350 attendees. That is a little off. After reviewing our attendance roster our final tally was 397 signed in attendees. I know this is not a numbers game, but for little Baton Rouge, LA this is a big number. Ok, now that I have that out of the way let's talk about a few lessons we learned from the event.

1. More Signs on Campus: Our event was held on the beautiful campus LSU. If you are familiar with the campus finding the location is a easy. On the other hand, if you are not then you can get lost easily. We only had signs on the main streets into and off of campus. At the next event we need to add signs from the event hotel directly to the event. Even though the event and the hotel are located on the campus, the hotel is a bit of a hike from the event. You can easily get lost. I am sure a couple of our speakers can attest to that. In addition, we will include more signs on the the main streets and at various locations leading up to campus. This is one aspect that tends to be overlooked, but after this event I realized that this is a vital component to a successful event.

2. Physical Address: Having the event on a very large college campus can present a challenge when attempting to map out a physical address. This can be attributed to the fact that building on campuses change names, which can cause a problem as we found out. I thought that we had the correct address, but most GPS devices could not locate the address. As a result, we have decided to include Longitude and Latitude coordinates in our correspondence next year.

3. Air Conditioning: Everyone knows that it is HOT in Louisiana, so to prevent any complaints about the heat we coordinated with the facility to ensure that the AC was on all night the day before the event and all day the day of the event. Even with that, it was still warm in some of the rooms. We are currently looking for ways to solve this problem. Nothing yet, but we have about 359 days until our next event. I am sure we will come up with something.

4. Food: Now, this is a touchy topic for me, since we did not charge for Lunch. I read a blog post about SQL Saturday #28 that started out very positive, but ended really bad. The bad part of the blog was in regards to the food that we served. How could food be bad if it is FREE? For breakfast we served, donuts, muffins, coffee, water and soda. This is typical for most of the SQL Saturday's that I have attended. In addition to those items I have seen fruit, but with our humid and hot environment we voted against the fruit. This was the last thing that I thought anyone would complain about, but our breakfast was described as horrendous. Wow, such a harsh word. I will say this, if you plan on attending next year, the only thing that we will change about breakfast is to buy more of the same because there wasn't a donut or muffin left. For lunch we had a boxed Subway lunch, which is another typical item served during SQL Saturday's. Maybe not Subway, but the boxed lunch with a sandwich, chips and cookie. Everyone who was there when lunch was served received a FREE lunch. We do plan on upgrading lunch next year, but we are only upgrading the lunch provider not the type of lunch. A box lunch is convenient for our lunch team. You can easily distribute them and the garbage collection is quick. I don't recall what the post stated directly about the lunch, but I do remember a comment about the lackluster thought that was put into it. In the afternoon, we supplied a snack of cookies and crackers, which is not a typical SQL Saturday practice. Since the lunch is small, we decided to provide a little extra snack to all those that decided to stick around all day. Not sure where to complain about any of the items, but since there was a complaint it has to be labeled as a bad. We are going to discuss our food choices and there maybe some slight changes, but nothing too drastic. We are on a budget, and I believe that charging for lunch is a deterrent to potential attendees. As a result, we provide a low-cost, but quality lunch to our attendees. Two years in a row and only 1 complaint about lunch that I know of, I would say those are good numbers.

5. Too much .Net: A lot of the SQL folk stressed their concern about the 4 .Net tracks at our SQL Saturday. I have to be honest, initially I was concerned about this myself, but after the day started and I walked around and visited each room as the classes were in session I realized that there were plenty attendees to go around. I was concerned that some of the SQL sessions would have fewer attendees. I was wrong. The attendance across all sessions appeared to be evenly distributed. I also enjoyed the banter between the Devs and DBAs. Our attendance roster was almost evenly distributed between the two. I even noticed some of the developers in the SQL sessions and some the DBAs in the developers session. Maybe we can learn something from each other.

6. Schedule: I have to be honest we could have done a much better job on our Event Schedule handouts. This was probably the most commented about thing at the event, in regards to "Needs Improvement". We printed a one sheet schedule with the event diagram on one side and he schedule on the other. Since we had nine tracks it was difficult to fit them all on a regular sheet. Therefore, we decided to print the schedule on a legal sheet of paper. On top of that, we decided to fold the sheet and put it in the event bag. Big MISTAKE!. The folded sheet was lost in our event bag. Two things we are going to change. First, instead of placing the schedule in the bag, it's going to be handed to each person. Second, we are going to mimic what the guys in Dallas did. We are going to create an Event Guide that will contain a very detailed schedule of the days events.

Wow. I did not intend on writing that much about lunch, but I just couldn't stop. Please note that I enjoy constructive criticism about anything I do because it only makes me do it better the next time. Thanks to everyone that has provided really good points, we have some really good documentation and I promise that they all will be addressed. As with any event there is going to be some good and some bad. Overall, I have to say that we did pretty good. Stay tuned, I will be sharing more information about the event.

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 Reporting Services - Line Graph with a Vertical Bar

2010-08-10T18:52:00.001-07:00

So, I was recently asked if you could display a vertical bar on a line graph to denote the last day of the month. Initially, I had no clue how to solve this problem. I talked to a couple of people and searched the web, but came up empty. I decided to just dive into a report and solve the problem on my own. So I started with the following query that can be run in the AdventureWorks2008 database:

SELECT

    st.CountryRegionCode,

    CAST(sod.OrderDate AS DATE),

    SUM(sod.TotalDue) TotalDue,

    NULL Marker

FROM Sales.SalesOrderHeader sod

INNER JOIN Sales.SalesTerritory st

    ON sod.TerritoryID = st.TerritoryID

WHERE

    sod.OrderDate BETWEEN '4/28/2003' AND '5/03/2003' AND

    st.CountryRegionCode NOT IN ('US', 'CA')

Note that there is a column labeled Marker that currently has a value of NULL. It will be explained later in this post. This query was the basis for this Line Graph:

The RED vertical line you see in the graph is just an edit of the image, but this is the expected result. I have to admit my solution to this problem is somewhat of a hack, but I think it will work. The first thing i did was append a UNION to the query that would add an additional row which would be used to help create the vertical bar on the line graph. Since the initial query included four columns the UNION had to include four columns. The only two columns that required values are the OrderDate and the Marker. In this example, I hard-coded the OrderDate to the last day in April. You can modify it so that it is a parameter that is selected by the end-user. The second is a value or marker that will be used to determine the height of the vertical line. Slight modifications can be made to query to ensure that the value is consistent with the results of the query. For example, you could select the max from the result and increase it a little to further enhance the visualization. In this case, since I know the maximum is 300,000, I set it to 350,000.

What does the Union accomplish? This is done to ensure that the Marker value is visible or placed only on the last day of the month. I know what you are thinking, this is not going to work. That is what I thought. To create the vertical line I completed the following steps:

1. Added the Marker to the Values section of the Report

2. Next right-click the Marker value and selected Change Chart Type. Choose the first column Graph from the column section.

3. Now right-click on the bars on the graph and selected Series Properties. Choose Fill from the navigation pane and choose Red from the Pick color section.

4. With the bars selected on the graph open the properties windows. Then expand the Custom Attributes property. Finally change the PointWidth value to .2. This changes the sizes of the bar.

Now run the report. Your report should resemble the following:

You will see a single vertical bar whose point on the x axis is the last date of April and whose value is 350,000. This line is a bar graph that has been customized to resemble a vertical line. I am not saying that this is a perfect solution, but it appears to work. If you have any other ideas or methods that may solve this problem please post your comments here. Also, feel free to email me at pleblanc@pragmaticworks.com.

There are several ways to accomplish this, but this solutions does work. Stay tuned for Puzzle #6.

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 #5 - Solution

2010-08-10T07:43:00.001-07:00

Again I want to apologize for the slow posting of the solution to this puzzle, but I have been hard at work on SQL Saturday #28. Fortunately, I carved out some time to write it up. The solution to this puzzle could have been accomplished a couple of ways. I chose to use the SUM and YTD MDX functions, but before I discuss these functions I will start with the basic query, which satisfies these requirements:

1. Internet Sales Amount as a Column

2. Delivery Date Calendar Month as a Row

3. Applies a filter to limit the rows from the Delivery Date of January 2006 to December 2006.

SELECT

    NON EMPTY(

            [Measures].[Internet Sales Amount]

    )ON COLUMNS,

     [Delivery Date].[Calendar].[Month].&[2006]&[1]:[Delivery Date].[Calendar].[Month].&[2006]&[12]ON ROWS

FROM [Adventure Works]

Next I created a calculated member whose purpose is to return the YTD or Running Total. To accomplish this I coupled the SUM and YTD functions. See below:

WITH MEMBER Measures.[YTD Internet Sales]

AS

SUM (YTD([Delivery Date].[Calendar].CurrentMember),[Measures].[Internet Sales Amount])

Using the YTD function I was able to obtain a set of members from the same level as the given member, which in this case was the [Delivery Date].[Calendar].CurrentMember. I then used the SUM function to accurately calculate the YTD aggregations for the [Internet Sales Amount] measure. The solution to the puzzle should resemble this:

WITH MEMBER Measures.[YTD Internet Sales]

AS

SUM (YTD([Delivery Date].[Calendar].CurrentMember),[Measures].[Internet Sales Amount])

SELECT

    NON EMPTY(

            [Measures].[Internet Sales Amount],

            Measures.[YTD Internet Sales]

    )ON COLUMNS,

    [Delivery Date].[Calendar].[Month].&[2006]&[1]:[Delivery Date].[Calendar].[Month].&[2006]&[12]ON ROWS

FROM [Adventure Works]

There are several ways to accomplish this, but this solutions does work. Stay tuned for Puzzle #6.

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 lunch #28-SSIS Design Patterns 1a: Introduction to SSIS Design Patterns

2010-08-08T14:20:00.001-07:00

In this three part series SQL Server MVP Andy Leonard will discuss SSIS Design Patterns.

Speaker: Andy Leonard

Add To Outlook: Add To Calendar

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

Date and Time: 8/9/2010 11:30:00 AM CST

Topic: #28-SSIS Design Patterns 1a: Introduction to SSIS Design Patterns
This is part one of a three part series.
A brief introduction to the concept and usage of design patterns and a demonstration of the Counts Acquisition in ETL Instrumentation pattern.

BIO: Andy Leonard is an Architect with Molina Medicaid Solutions, SQL Server database and Integration Services developer, SQL Server MVP, PASS Regional Mentor, and engineer. He is a co-author of Professional SQL Server 2005 Integration Services and SQL Server MVP Deep Dives. Andy blogs for SQLBlog.com. His background includes web application architecture and development, VB, and ASP; SQL Server Integration Services (SSIS); data warehouse development using SQL Server 2000, 2005 and 2008.

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 #28 Baton Rouge Update

2010-08-06T09:46:00.001-07:00

We are just about a week away from probably the biggest FREE training event to ever take place in Baton Rouge, LA. Heck this is probably the biggest FREE technology event in Louisiana. I really don't know. Nevertheless, since it is almost here I wanted to encourage any one that has not already signed up to do so now. We are well over 500 registered attendees and that number is increasing daily. If you are not sure whether to attend or not here are a few highlights:

53 sessions, including 4 early morning beginner sessions and an all day .Net track
Ask the Experts, where you can chat with some of the top SQL Server and .Net professionals in the country
Win an MSDN subscription
Win a Netbook
Free Breakfast and Lunch
Networking, Networking, Networking
Did I forget to to mention all the FREE training

If you still are not convinced take a look at our schedule. I hope to see you all there and if you are already registered please continue to spread the word.

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 #28 Announces: Ask the Expert

2010-08-02T20:17:00.001-07:00

Do you have a challenging SQL Server or .Net issue that is causing you to have sleepless nights? Well, we have convinced 12 of our speakers (Experts) to allocate an additional hour of their day strictly for answering questions. These are some of the top SQL Server and .NET guys in the industry. So, if you have any questions bring them to SQL Saturday #28 Baton Rouge. Here is the schedule.

Time Slot	Speakers	Topics
9:00 am – 10:00 am	Chris Eargle and Chander Shekhar Dhall	Software Development, Application, Performance Tuning, Software Development Architecture
10:15 am – 11:15 am	Kevin Boles and Sean McCowen	Power Shell, Storage, Performance Tuning, SQL Server
11:30 am – 12:30 pm	Barry Ralston and Bryan Smith	SQL Server Analysis Services, MDX, Data Warehousing
1:30 pm – 2:30 pm	Denny Cherry and Steve Jones	SQL Server Storage, Performance Tuning
2:45 pm – 3:45 pm	Tim Mitchell and Steve Simon	SSIS, ETL, Data Warehousing
4:00 pm – 5:00 pm	Eli Weinstock-Herman and Drew Minkin	Data Modeling, Data Mining

These sessions will run concurrent with all of the other sessions. So plan accordingly. See you all at SQL Saturday #28 Baton Rouge.

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 #5

2010-08-01T15:13:00.001-07:00

Writing YTD totals and Running totals using T-SQL can take a little effort. However, with MDX it's not too difficult. In this puzzle I will write an MDX statement that produces a monthly YTD or Running total. Here are the requirements:

Columns: Monthly Running Total (Calculation), Internet Sales Amount

Rows: Delivery Date Calendar Month

Filters: Delivery Date from January 2006 to December 2006

Hints: You may need to create a scope calculation (WITH MEMBER), use the YTD and SUM functions

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.

MDX Puzzle #4 - Solution

2010-07-30T07:43:00.001-07:00

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

2010-07-28T07:21:00.001-07:00

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

2010-07-28T05:50:00.001-07:00

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.

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

2010-07-25T14:34:00.001-07:00

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

2010-07-23T06:15:00.001-07:00

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?

2010-07-21T19:56:00.001-07:00

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

2010-07-21T11:41:00.001-07:00

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

2010-07-18T16:57:00.001-07:00

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

2010-07-15T08:58:00.001-07:00

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)

2010-07-12T07:01:00.001-07:00

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

2010-07-08T20:15:00.001-07:00

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.

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)

2010-07-06T17:12:00.001-07:00

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

2010-07-06T05:32:00.001-07:00

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

2010-07-05T18:21:00.001-07:00

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:

Columns: Internet Sales
Rows: Calendar Years
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?

2010-07-01T09:11:00.001-07:00

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.

MDX Puzzle #2

2010-06-30T08:56:00.001-07:00

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

2010-06-29T03:58:00.001-07:00

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.

View Schedule

The SQL Saturday #28 Team

MDX Puzzle #1 Solution

2010-06-27T17:35:00.001-07:00

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

2010-06-22T06:51:00.001-07:00

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.

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

2010-06-22T04:53:00.001-07:00

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

2010-06-21T09:03:00.001-07:00

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

2010-06-13T15:47:00.001-07:00

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

2010-06-10T15:19:00.001-07:00

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

2010-06-08T12:48:00.001-07:00

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

2010-06-06T20:37:00.001-07:00

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

2010-06-01T04:55:00.001-07:00

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

Connecting to DB2 using SSIS

2010-05-26T04:23:00.001-07:00

To start, first download and install the Microsoft OLE DB Provider for DB2. Click here to download. After it is installed I suggest rebooting your machine. In some cases a reboot is not required; however I have encountered problems without rebooting.

Next open an SSIS project and create a new data source. On the Connection Manager screen select Microsoft OLE DB Provider for DB2 from the drop down list labeled Provider. In the text box labeled Server or file name enter the IP address of your server. Then select the radio button labeled Use a specific user name and password. Enter you user name and password and select the checkbox labeled allow saving password if you want to persist those values. Then enter you database name in the drop down list labeled Initial Catalog.

Now you must click the button labeled Data Links. The Data Source, User Name and Initial Catalog textboxes should be populated. You will only need to enter values for the textboxes labeled Package Collection and Default Schema. Enter your database schema in both textboxes.

Then click on the Advanced tab and check the box labeled Process binary as character. To understand why you need to do this please read, How to Retrieve IBM AS 400 /JD Edwards “for bit data” via SSIS, by Alberto Munera.

Now click on the ellipses next to the Network drop down list and enter your IP address in the textbox labeled IP Address.

Click Ok twice and you will be returned to the Connection Manager Screen. Click the button labeled Test Connection to verify that your settings are correct. Now that you have a successful connection to your DB2 database click OK and the data source is ready to use. I have to give credit to my colleagues at TBC for assisting me in putting this document together, thanks Roberto and Chad.

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.

Upcoming SQL Events

2010-05-23T16:51:00.001-07:00

I realized this morning that I will be participating in three SQL Events this week and I want to share the event information with everyone. Two of the events are available online and the other is a local user group meeting. If you have time logon onto one of the webcasts or join me at the Baton Rouge SQL Server User Group. The events are as follows:

Baton Rouge SQL Server User Group

Date and Time: 5/25/2010 5:45 PM CST

Event Location: Lamar Advertising, 5551 Corporate BLVD, Baton Rouge, LA 70808

Presenter: William Assaf

Description: What are DMV's in SQL 2005/2008, and how can you use them to improve query performance, discover indexing needs and more? Dynamic Management Views are essential tools for the SQL 2005/2008 administrator but have a wide range of applications by developers, network engineers and DBAs.

SQL PASS Appdev Presents

Date and Time: 5/25/2010 8:00 AM EST

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

Presenter: Patrick LeBlanc

Topic: Introduction to SQL Profiler

Description: The SQL Server Profiler is a powerful, but underused tool. Often developers and some DBAs don’t realize the potential the tool has to offer. In this session I will show you how to use the tool to identify poor performing queries. I will also detail steps that can be used to identify blocking and deadlocking. Finally, I will provide sample scripts and methods that can be used to proactively monitor your SQL Server for inefficient queries and stored procedures.

SQL Lunch

Date and Time: 5/25/2010 11:30 AM CST

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

Presenter: Max Trinidad

Topic: SQL Server and PowerShell Working w/Databases

Description: Looking into using SQLPS.exe how to work with databases on multiple SQL Servers. See How-To Configure your PowerShell profile to make your scripting environment more productive. This session will have a lot of samples".

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, MCTS

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

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

Report Builder 3.0 – Using Report Parts

2010-05-18T16:40:00.001-07:00

Report Builder 3.0 offers many new features to its users. One of the best features, in my opinion, is the ability to search and use Report Parts that have been published to the Report Server. If you are looking for a tool that will provide Self-Service to your end-users this is a tool that you should consider adding to your arsenal.

During many of my client engagements I am asked to create reports. I will typically spend time reviewing existing reports and then I will recreate a report or two adding additional functionality and formatting. Once the clients, specifically the business users view the reports there are comments such as: this is good, but….

can you move this table
can you move this chart
can you change the format of this column
can you change the color of the header row

Then I go back and make the requested changes and present the reports once again. Kind of a tedious process. Even worse, in some cases more changes are required. However, with the release of SQL Server Reporting Services R2, report developers can now publish parts of reports. These parts include, maps, tables, charts, images, etc… See my blog post, Publishing Report Parts, for more details on this topic.

Now instead of you, the report developer or DBA, spending your time shooting at a moving target recreating or modifying reports. You can now enable the end-user to leverage all of your hard work without spending a tremendous amount of time teaching them how to use a new tool.

So to get started, open Report Builder 3.0. There are a couple of ways to do this. You can open it from the start menu or you can use the Report Builder button on Report Manager.

Once the Report Builder is open you may need to connect to a Report Server. Once you are connected, locate the tab on the right side of the Report Builder labeled Report Gallery.

In the search box enter your search criteria or leave it blank and click the magnifying glass. The results of the search will appear directly below.

Either double-click an item in the list or drag into onto the report design surface. Any datasets are parameters that the report part depends on will automatically be added to the report.

At the point you can continue to add items to the report and format it to me your needs. Once you are done you have the ability to publish the report. Click the save icon and type the Report Server path in the drop down list labeled Look in. Select the location where you want to publish the report and click save. The report can now be viewed by any that has permissions.

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 Server Reporting Services R2 – Publishing Report Parts

2010-05-16T15:22:00.000-07:00

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.

Upcoming SQL Lunch Webcasts

2010-05-12T17:16:00.001-07:00

First, I would like to thank all that have attended and supported the SQL Lunch during our first year. We are continuing on and have some great Lunches scheduled for May and June. In May I will be presenting Configuring, Deploying and Scheduling SSIS Packages and Max Trinidad will be presenting SQL Server and PowerShell Working with Databases. In June we will have first time SQL Lunch Speaker, Stefan Bauer, presenting Warehouse Disk Layout 101.

The SQL Lunch Team has several upcoming topics on SQL Server 2008 R2, Power Pivot and Report Builder 3.0. We are always looking for new presenters and new topics. If you are interested in presenting on the SQL Lunch please email us at webmaster@sqllunch.com. No matter if you are a seasoned presenter or a first time presenter, we welcome all.

Thanks Patrick LeBlanc, MCTS

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

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

Creating Sparklines in PowerPivot

2010-05-09T08:13:00.001-07:00

Sparklines are new to Microsoft Excel 2010 and Reporting Services 2008 R2. In this post I will outline the steps required to create sparklines in an existing PowerPivot workbook. Sparklines provide you with the ability to create a trending graph inline with your data. So how do create these Sparklines.

Let’s assume that you already have a PowerPivot workbook that contains data. In this example I will be using the AdventureWorks2008 database. I have imported the following tables: DimDate, DimSalesTerritory and FactInternetSales. I am also assuming that you have already created a Pivot Table that resembles the following:

My columns in the Pivot Table are the CalendarYear from the DimDate table, the rows are the SalesTerritoryCountry from the DimSalesTerritoryTable and the SalesAmount from the FactInternetSales table is the value.

Now that I have the Pivot Table created, I decided that I would like to see a graphical depiction of the sales for each country over each year inline with the data. To accomplish this you can use sparklines. To create the sparkline, click the tab labeled Insert and click the icon labeled Line in the sparkline section of the ribbon. See the following:

When you click the icon the following dialogue box will appear:

In the textbox labeled Date Range you must specify the values that you want to trend. In my sheet I selected all the data for every country for every year, excluding the column labeled Grand Total. Similar to the following image:

In the above diagram note that all the data within the dashed lines are selected as the Data Range for my sparklines. Next you must specify where you want to place the sparklines or the Location Range. Select the empty columns to the left of the Grand Total column. Ensure that you select the same number of rows that selected for the Data Range. See the following image:

Click on the button labeled OK on the Create Sparklines dialogue box and now you have the sparklines. Your speadsheet should resemble the following:

Now you not only have data, but you can quickly see how the data is trending for each country. As always if you have any questions or comments please feel free to email me at pleblanc@pragmaticworks.com.

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 Server Performance Tuning – Webcast

2010-05-06T18:27:00.001-07:00

I recently gave a presentation on SQL Server Performance Tuning via Live Meeting. I discussed how you could use the DMVs, SQL Server Profiler and Execution Plans to solve some of your performance problems. There were over One hundred attendees in the session. I was asked several times, if I would share my presentation and the demo scripts. Since emailing each individual would be a true pain I stated that I would post all of the information in a blog post. So for all of you that attended my session and anyone that may be interested click the below link to download.

Download: SQL Server Performance Tuning.

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.

2010 1st Quarter Review

2010-05-06T18:26:00.001-07:00

I know it’s kind of late to write a review of my first quarter goals, since we are well into the second quart of 2010. However, I did state in my blog post earlier this year, 2010 1st Quarter Goals, that I would keep you all abreast of my progress. So, how did it do? Unfortunately, I failed miserably on a few goals, but, on the other hand,I exceeded my expectations on others. Here is the list from my initial post with a few comments about my progress:

1. Learn everything I can about SQL Server Analysis Server – I have been on several engagements since my posting and I have to say that my SSAS skills have definitely improved. MDX is still somewhat of a challenge, but I am working on it.

2. Two blog postings per week - Yeah right. In the first quarter I posted 22 blogs, which is pretty close to two, but there wasn’t any consistency to my posts. This is something I am really going to work hard at improving.

3. Publish two articles – I did publish two articles on www.BIDN.com. If you would like to give them a read, here they are:

Incremental Loads with Change Data Capture – Part: 2 Detecting Changes

SSRS – Creating a Master/Detail Report

4. Speak at a minimum of three events (SQLSaturday, SQLLunch, PASS Virtual Chapter, etc…)

I was able to speak at four events, speaking at 5 sessions. The sessions are as follows:

Tampa Bay SQL Saturday – SQL Server Backup and Data Compression 101

Tampa Bay SQL Saturday – Iron Chef Data Warehouse

SQL Lunch Webcast – Introduction to Partitioning

Pragmatic Works February SQL Server 101 Series – Beginning T-SQL

Charlotte SQL Saturday - SQL Server Backup and Data Compression 101

5. Schedule SQL Lunch speakers for the rest of the year (at least 2 per month) – This I found was a very optimistic goal. I have decided to scale it back to finding speakers for each quarter. So if you want to speak please send an email to webmaster@sqllunch.com.

6. Submit 2 videos to SQL Share – Unfortunately, I did not get around to recording one video for SQL Share, but I have two topics that are ready to go and I will get them submitted before the second quarter of 2010 is over.

7. Submit an abstract to SQL Server Standard (Grant Fritchey) – This item has been removed from my list until further notice. I have be asked to participate in a few larger writing commitments that really monopolize a lot of my free time. Once I have completed those obligations I will reevaluate and I try to get an abstract submitted.

As I stated in my first Goals post, my goals tend to change as the year progresses. With the exception of number 7 in the above list of the items, all of the goals will remain for the Second quarter of 2010. I have already fallen behind in regards to my blog posts. Therefore the goal is not to post twice a week, that goal has been modified to post at least once a week on a consistent basis. In addition to that modification I have added one item to the list and it is as follows:

Submit three abstracts to the PASS conference.

Maybe I will get selected. Either way I plan on attending and I look forward to meeting many of you there.

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.

R2 Road Show Recap

2010-04-23T06:23:00.001-07:00

This past week I had the opportunity to speak at three SQL Server R2 Launch events. I spoke in three cities in four days. On day one I spoke in Omaha, NE, the next day we stopped in Overland Park, KS and finally St. Louis, MO. Each event was held at a Microsoft facility. The largest crowd was in St. Louis, there were approximately 75 people in attendance. I traveled with one of our Salesmen and two Microsoft Employees.

My role in each event was to do all the demonstrations. The topics that I covered were structured completely around Self-Service Business Intelligence. In addition we also did a couple chalk talks on the new data warehousing methodologies and appliances that are available or will be available soon from Microsoft, Fast Track and Parallel Data Warehouse. Overall they were all accepted by the audience with a few questions regarding the true practicality of the features in a large organization. As expected, the Power Pivot demonstration was a big hit. However, I was surprised that several attendees in each city was excited about the Utility Control Point and Date Tier Applications.

I will be doing a SQL Lunch on each of the demonstrations in the upcoming months. The topics will include: Power Pivot, Report Builder 3.0, Utility Control Point, and Data Tier Applications. Stay tuned here and the SQL Lunch for the dates.

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.

Editing Saved SSIS Packages

2010-04-14T20:40:00.001-07:00

During my last SQLLunch, Introduction to SSIS, I was asked an interesting question by one of the attendees. He asked, how do you edit a package once it’s saved in the SQL Server package store? My response was to open your SSIS project and edit the package from Business Intelligence Development Studio (BIDS). His response was, I did not save the project. Huh, what? That’s what I thought. He tried to explain further, but I did not quite understand. He said I will be at the user group on Wednesday night, which I spoke at, and elaborate a little.

As promised he arrived on time at the user group meeting ready to explain his problem and expecting a solution. After we chatted for a little I realized the problem. He asked me to open SQL Server Management Studio (SSMS) and connect to my local SQL Server Integration Services (SSIS). Then he said expand the Stored Packages folder then the MSDB folder.

He then said, see there they go, but how do I edit them. Then I understood. When he completed a package it was saved to the SQL Server, and once he was done he closed the project without saving. As a result, the packages resided on the server, but he did not have the project that contained his packages. So he could not edit them.

I explain to edit them, open BIDs and create a new SSIS project. Then right-click on the SSIS packages folder in the Solution Explorer and select Add Existing Package and the following dialog box will appear.

Since his packages were saved on the SQL Server, I told him to accept the default, SQL Server, for the Package location. Then I told him to choose his server name, and finally click the ellipses button next to the textbox labeled Package path and the following dialog will appear:

Select the package you want to edit and it will be included in the project. Once complete double-click the new package in the solution explorer and you can begin editing the package. I explained to him to always save his SSIS projects when complete so he could always open the project for editing to avoid the extra steps.

So for all of you that don’t save your SSIS projects and you want to edit them after they have been deployed, follow these steps.

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.

Speaking Twice this Week

2010-04-11T16:40:00.001-07:00

This week I will have the opportunity to speak at two different events. The first is on Monday, April 12^th, at the SQL Lunch. I will be doing an Introduction to SSIS presentation. The presentation is online and will begin at 11:30 AM CST. If you have time during lunch, please join me for a quick presentation on SSIS.

On Wednesday, April 14^th, I will be speaking at the Baton Rouge SQL Server User group. I will be do a Backup and Data Compression presentation. If you are in or around Baton Rouge, LA, please stop by for some networking, food, COMPRESSION, and free giveaways.

If you would like to speak at either the SQLLunch or the Baton Rouge SQL Server User group feel free to email me at pleblanc@sqllunch.com. You can follow the SQL Lunch on Twitter (@sqllunch) and the User Group (@BRSSUG).

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 – Format Multiple Columns

2010-03-19T14:51:00.001-07:00

Often when designing reports you are required to apply the same formatting to multiple columns in one table or matrix. Since SSRS 2008 does not allow the developer to change the format of a column using Textbox Properties when multiple columns are selected, the developer must either:

· Change the formatting one column at a time

· Be familiar with the expression language.

Neither of the aforementioned choices are difficult, but maybe time constraints eliminates both as viable options. Therefore, I am going to share a quick tip that will help you format multiple columns quickly.

Let’s assume that you have a report that looks similar to this:

If the requirements were to change the formatting of each TotalDue column to Currency you can quickly change all of them by using the following steps:

1. Using the Textbox Properties change the format to Currency for one of the TotalDue columns.

2. Once the format has been set, right-click on the column and choose Properties.

3. In the properties window locate the Format attribute and copy the value for that property.

4. Return to the table or matrix and select all the additional columns that require formatting.

5. Open the properties window again, ensuring that all the columns remain selected.

6. Paste the format value into the Format attribute.

7. Run your report and all the columns should be formatted.

Now all of your columns are quickly formatted. If you have another method to accomplish this please share it. If you have any questions or comments about this posting please 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.

Speaking at Charlotte, NC – SQL Saturday #33

2010-03-01T17:40:00.001-08:00

If you are near Charlotte, NC, I will be speaking at their SQL Saturday on March 6th, 2010. My session,SQL Server Compression 101, is at 1:30 PM. This is the first SQL Saturday under PASS management. If you have not registered for this event do so, seats are filling up quickly. This is a free event where you can network and learn from some of the most talented SQL Server Professionals in the industry.

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 – KPIs without Analysis Services

2010-03-01T16:10:00.001-08:00

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.

Upcoming SQL Lunch Webinars

2010-02-24T04:38:00.001-08:00

If you haven’t attended a SQLLunch, I suggest that you put this upcoming lunch on your schedule. Brad McGehee, SQL Server MVP, will be presenting an Introduction to Graphical Execution Plans. Brad has over 15 years of SQL Server Experience and over 6 years of training.

Add To Outlook: Add To Calendar

Date and Time: 2/25/2010 11:30:00 AM CST

Topic: Introduction to Graphical Execution Plans

Topic: #11-Introduction to Graphical Execution Plans
One of the black arts of being a DBA is learning how to read and interpret graphical execution plans. This is an important skill, as knowing how to read and interpret graphical execution plans can help the DBA tune queries for optimal performance. Without this crucial knowledge, the DBA won’t be able identify potential query problems, nor understand how to fix them. In this short, introductory level session, which is designed for DBAs with little or no knowledge of query execution plans, you will learn why execution plans are important, how they are created, learn about the different execution plan formats, discover different ways to produce them, find out about the difference between estimated and actual execution plans, and learn the basics of how to read them. Because this is an introductory session, after attending, I highly recommend you download this free eBook to learn more about execution plans: “SQL Server Execution Plans” by Grant Fritchey. http://www.sqlservercentral.com/articles/books/65831/ This session assumes attendees have a basic foundation of DBA knowledge. After attending the session, attendees will be able to immediately put what they have learned into practice.

Upcoming Lunch

Add To Outlook: Add To Calendar

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

Topic: Zero to Cube in 30 Minutes

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: #14-Zero to Cube in 30 Minutes
Don’t think you can learn to build a cube in Analysis Services on your lunch break? Come join BI Guru Adam Jorgensen as he takes you through the ins and outs of building a simple cube in under 30 minutes! Bring your questions for Q&A afterwards.

Talk to you soon,

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

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

SSRS 2008 – Adding a Totals Row

2010-02-22T19:07:00.001-08:00

During a recent client engagement I was asked if there was a quick way to add a row containing totals for each corresponding column in the details row from a Tablix item. The tablix contained about 18 columns, and the person was entering an expression that would sum the value of the field in the corresponding detail rows. Fortunately, SSRS 2008 provides a very simple method that will perform this task.

Assuming that you have a report that contains a Tablix item you can perform the following steps to quickly add a totals row to your report.

1. In the Row Groups Panels, right-click the item labeled Details:

2. Choose Before or After, this will depend on where you want to the totals to appear in the report.

3. Once you make the selection a new row will be added to your report. It will contain an expression for each column in the details that sums the value of each column.

It’s just that simple. As always if you have any questions or concerns regarding this post, please feel free to email me pleblanc@pragmaticworks.com

Talk to you soon,

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

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

Reporting Services Switch Function

2010-02-19T14:13:00.001-08:00

Reporting Services Switch Function

Recently a client requested to create a Reporting Services expression that emulated the CASE WHEN statement from T-SQL. The expression would use the selected parameter value to dynamically set the Go to report value of an Action under the properties of a textbox. In other words, if a user chose a parameter value of Report1 the go to report value would be set to Report1, if the user chose Report2 value would be set to Report2, so on and so forth.

I looked at several varying expression methods to solve this problem including the IIF method, but I eventually settled for the SWITCH function because it offered the most flexibility. One small limitation was that the value was only updated when the report was refreshed. If a user changed the value of the parameter, he or she would be required to click the view report button to refresh the value. So how do you use the switch function? To specify the value for this example drop a text box on your report, right-click it and choose Text Box Properties. The following dialogue box will appear:

Choose Action from the list of available choices in the right pane. Ensure that the Go to report radio button is selected. Then click the expressions button next to the Specify a report drop down list. The expression dialogue window will appear.

In the Set expression for ReportName textbox you would type an expression similar to the following:

=SWITCH

(

Parameters!WhichReport.Value="Report2", "Report2",

Parameters!WhichReport.Value="Report3", "Report3",

Parameters!WhichReport.Value="Report4", "Report4"

)

The SWITCH function should be used when you have three or more conditions to test. When evaluated to true the function returns the first value in the series of available conditions.

I welcome any comments, criticisms or suggestions regarding this post. How would you have solved this problem? I have attached a small report project that simulates my approach. As always if you have any questions regarding this post please feel free to email me at pleblanc@pragmaticworks.com.

Talk to you soon,

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

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

December 2009 – Baton Rouge SQL Server User Group

2009-12-01T04:37:00.001-08:00

December SQL Server User Group Meeting

Location: At Lamar Advertising

Wednesday, December 2, 2009

6:00 PM - 8:00 PM

Linear Gauge Control: A Practical Use

2009-12-01T03:59:00.000-08:00

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

2009-11-24T09:43:00.001-08:00

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

IF EXISTS (SELECT * FROM sys.types WHERE name = 'CountryCodes' AND schema_id = SCHEMA_ID('Sales'))

DROP TYPE Sales.CountryCodes

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

IF(OBJECT_ID('Sales.GetSalesByDateAndRegion')) IS NOT NULL

DROP PROC Sales.GetSalesByDateAndRegion

CREATE PROC Sales.GetSalesByDateAndRegion

@Month varchar(20),

@Year int,

@CountryRegions Sales.CountryCodes readonly

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

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

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

Deploying Reports on Windows 7

2009-11-23T06:01:00.001-08:00

Recently I tried to deploy a report to a Report Server on my laptop, which is running Windows 7 Ultimate. Unfortunately I received the following error:

The permissions granted to user ‘MachineName\SomeUser ‘ are insufficient for performing this operations.

This confused me a bit since my account was an administrator. After digging a bit I found a very simple solution. Right-click on the Visual Studio icon or the Business Intelligence Development Studio icon and click Properties. Then go to the Compatibility tab. On that tab, in the Privilege Level section select the checkbox next to the item labeled Run this program as administrator. See the below screen shot example:

Once done, restart the application and Deploy your report.

Talk to you soon

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

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