Category Archives: SQL Server 2008

Adding Query Parameters to SQL Server 2008 Reporting Services Reports

Note, before getting started with this lesson there are some prerequisites you should know about. Please read my post Getting Started with SQL Server 2008 to ensure you have everything setup correctly, otherwise you will be missing objects required to get the code here to work correctly.

In yesterday’s post “Adding Filter Parameters to Reports”, we explored how to add a filter to the report dataset. This had the advantage of being very fast when you wanted to look through different sets of data. The disadvantage to this method is that it brings back all possible rows for the report at once. Thus it can be very slow and database intensive and unnecessary when the user only wants to look at a subset of the data.

An alternative to Filters are Query Parameters. Query Parameters are applied at the SQL statement level, before the call to the SQL Server (or other database platform) is made. This limits the number of rows brought back from the server, increasing the speed and reducing the memory footprint required on the reporting server.

To begin, let’s create a base report. We’ll do the same as steps 1 to 10 in the “Adding Filter Parameters to Reports” lab, but in case you have not worked through it yet I will repeat them here.

Step 1. Add the report

As with our other reports, right click on the Reports branch in Solution Explorer, pick Add New Report, and (if you haven’t already disabled it) click next to move past the welcome screen.

Step 2. Set the data source.

Pick the Contoso shared data source, or setup a new source to Contoso, and click Next.

Step 3. Setup the query.

In the query builder, we’ll be using one of our views. Enter this SQL statement:

SELECT [FiscalYear]
     , [ProductCategoryName]
     , [ProductSubcategory]
     , [Region]
     , [TotalAmount]
  FROM [ContosoRetailDW].[Report].[V_SubcategoryRegionTotalsByYear]

and click next.

Step 4. Set the format.

For the report type we’ll use the simple Tabular format, so just click Next.

Step 5. Determine field placement in the report.

To keep this simple we’ll not use any groups on this report, so just put all report fields into the Details section. You can do it in one easy step by clicking on the top most item (FiscalYear), holding down the shift key, and clicking the bottom item (TotalAmount). This will select all of the fields, just click the Details button to move them. Then click Next.

Step 6. Select the formatting Style

Once again we’ll go with Corporate for the style and click Next.

Step 7. Name the report.

Finally we’ll give the report a name of “Regional Sales by Subcategory” and click Finish.

Step 8. Format report columns

To make the report a little easier to read, expand the width of the columns and format the Total Amount as Currency. (See the previous labs if you don’t recall how to accomplish this.)

Previewing the report shows our data. There’s a lot of it, so let’s say we are the sales manager and we want to apply filters so we are only looking at pieces of our sales.

Step 9. Add a new dataset to act as a source for the filter parameter

There are several choices we can use for creating filters. We could allow the user to simply key a value to use into a text box. It is also possible to hard code a list of values, for example “Yes” and “No”. In most cases though, you will want to create a filter based on a set of values in the database, which is what we’ll do in this lab.

Here we’ll apply a filter for the Region. To do so we’ll first need to create a dataset to supply this list from the database. In the Report Data window (if it’s not visible pick View->Report Data from the main BIDS (aka Visual Studio) menu) right click on Contoso and pick “Add Dataset”.

clip_image002

Give the new data source a good name. Here we can use Region.

We have several choices for a data source; here take the default of Text (which means we’ll just enter a query).

In the Query area we have many choices. Even though there is a designer built in, the best way is still to use SQL Server Management Studio to create and test your query, then paste it in here.

SELECT [RegionCountryName]
  FROM [ContosoRetailDW].[Report].[V_Regions]
 ORDER BY [RegionCountryName]

Once you’ve entered the above, click OK.

Step 10. Add the Region parameter

Now that we have a new dataset, we need to add a parameter to apply to our filter. Still in the Report Data window, right click on the Parameters and pick “Add Parameter”.

Give the parameter a good name. This is the variable name you’ll use elsewhere to refer to this item. Remember it, as you’ll need it later! Make sure it has no spaces and follows other typical guidelines for naming variables. For this example we can use the word Region.

Next you want to supply a prompt. This is the message shown on the report beside the parameter selection control. For our example let’s use “Select a Region to work with:”

You should now indicate the data type for the parameter. There are only a few you can pick from, for this though the default of Text will do fine.

Users often want to see multiple items on a report, but not all, so we’ll allow them to pick more than one by checking on the “Allow multiple values” check box. Your parameter window should now look like:

clip_image004

Next we need to tell the report where to get the data from. On the Available Views area, select the “Get values from a query” option. Then pick the new dataset we created, the Region one.

Below this you will see the Value field and Label field options. Frequently when dealing with data we have primary key data, such as an INT, that is needed to link data together. But we also need a human readable value, something that the users can see and understand.

A good example is the classic Employee table. You have an EmployeeID and an EmployeeName. For the value field, you’d pick the EmployeeID, but for the Label field you use the EmployeeName.

In this particular case, we are using the same field for both, and it’s perfectly valid to do so. Just pick “RegionCountryName” for both Value and Label drop downs.

clip_image006

For the rest of this example, know that we won’t do anything with Default Values or the Advanced Area. In the Default Values we can pick or set a value to be the default, and the Advanced lets us determine how often we need to refresh the source data for our parameter.

Step 11. Adding the Region Parameter to the report dataset

Next we’ll need to do two things to add the parameter to the dataset. First, right click on the main reports dataset and go to Dataset Properties.

Since we want to apply this parameter at the database level, we’ll need to add it as a parameter to our SQL query. In the query area of the Dataset Properties window enter:

SELECT [FiscalYear] 
     , [ProductCategoryName]
     , [ProductSubcategory]
     , [Region]
     , [TotalAmount]
  FROM [ContosoRetailDW].[Report].[V_SubcategoryRegionTotalsByYear]
 WHERE [Region] IN (@pRegion)

Next, we need to tell the query where the @pRegion parameter comes from. Go to the Parameters area of the Dataset Properties dialog. If Visual Studio / BIDS has not already done so, click add to add the @pRegion. Then in the drop down pick the @Report parameter object. Click OK to save the changes.

clip_image008

Return to the Preview tab and view the report several times, using various items in the list. Everything should update fine.

Except… having the Select All can cause some issues, which are covered in the next post.

Adding Filter Parameters to SQL Server 2008 Reporting Services Reports

Note, before getting started with this lesson there are some prerequisites you should know about. Please read my post Getting Started with SQL Server 2008 to ensure you have everything setup correctly, otherwise you will be missing objects required to get the code here to work correctly.

Often when a user looks at a report, they don’t want the entire report but just subsets of the data. To assist those users we have two methods for reducing the amount of data on a report, Filter Parameters (often just called Filters) and Query Parameters.

Query Parameters are applied before the query to get data is sent to the database, and only the data the fits the criteria from the parameter is brought back. This is ideal for situations where most of the time a user is going to look at only one set of data the report can provide. A salesman, for example, who only wants to look at his sales data. Each time a query filter is applied a round trip to the database occurs to update the data in the report.

Filters, which we’ll work with in this lab, are applied after the data is returned from the server. All possible data is returned from the database, and then the filter is applied to the data being seen. This means as a user switches from one filter to another it is very fast, as all the data is already present in memory and only data being displayed is changing. A sales manager would be a good example, one who will want to see the data for all his or her sales people, but only one at a time.

Let’s start our look at Filters by creating a fairly simple report.

Step 1. Add the report

As with our other reports, right click on the Reports branch in Solution Explorer, pick Add New Report, and (if you haven’t already disabled it) click next to move past the welcome screen.

Step 2. Set the data source.

Pick the Contoso shared data source, or setup a new source to Contoso, and click Next.

Step 3. Setup the query.

In the query builder, we’ll be using one of our views. Enter this SQL statement:

SELECT [FiscalYear]
     , [ProductCategoryName]
     , [ProductSubcategory]
     , [Region]
     , [TotalAmount]
  FROM [ContosoRetailDW].[Report].[V_SubcategoryRegionTotalsByYear]

and click next.

Step 4. Set the format.

For the report type we’ll use the simple Tabular format, so just click Next.

Step 5. Determine field placement in the report.

To keep this simple we’ll not use any groups on this report, so just put all report fields into the Details section. You can do it in one easy step by clicking on the top most item (FiscalYear), holding down the shift key, and clicking the bottom item (TotalAmount). This will select all of the fields, just click the Details button to move them. Then click Next.

Step 6. Select the formatting Style

Once again we’ll go with Corporate for the style and click Next.

Step 7. Name the report.

Finally we’ll give the report a name of “Regional Sales by Subcategory” and click Finish.

Step 8. Format report columns

To make the report a little easier to read, expand the width of the columns and format the Total Amount as Currency. (See the previous labs if you don’t recall how to accomplish this.)

Previewing the report shows our data. There’s a lot of it, so let’s say we are the sales manager and we want to apply filters so we are only looking at pieces of our sales.

Step 9. Add a new dataset to act as a source for the filter parameter

There are several choices we can use for creating filters. We could allow the user to simply key a value to use into a text box. It is also possible to hard code a list of values, for example “Yes” and “No”. In most cases though, you will want to create a filter based on a set of values in the database, which is what we’ll do in this lab.

Here we’ll apply a filter for the Region. To do so we’ll first need to create a dataset to supply this list from the database. In the Report Data window (if it’s not visible pick View->Report Data from the main BIDS (aka Visual Studio) menu) right click on Contoso and pick “Add Dataset”.

clip_image002

Give the new data source a good name. Here we can use Region.

We have several choices for a data source; here take the default of Text (which means we’ll just enter a query).

In the Query area we have many choices. Even though there is a designer built in, the best way is still to use SQL Server Management Studio to create and test your query, then paste it in here.

SELECT [RegionCountryName]
  FROM [ContosoRetailDW].[Report].[V_Regions]
 ORDER BY [RegionCountryName]

Once you’ve entered the above, click OK.

Step 10. Add the Region parameter

Now that we have a new dataset, we need to add a parameter to apply to our filter. Still in the Report Data window, right click on the Parameters and pick “Add Parameter”.

Give the parameter a good name. This is the variable name you’ll use elsewhere to refer to this item. Remember it, as you’ll need it later! Make sure it has no spaces and follows other typical guidelines for naming variables. For this example we can use the word Region.

Next you want to supply a prompt. This is the message shown on the report beside the parameter selection control. For our example let’s use “Select a Region to work with:”

You should now indicate the data type for the parameter. There are only a few you can pick from, for this though the default of Text will do fine.

Users often want to see multiple items on a report, but not all, so we’ll allow them to pick more than one by checking on the “Allow multiple values” check box. Your parameter window should now look like:

clip_image004

Next we need to tell the report where to get the data from. On the Available Views area, select the “Get values from a query” option. Then pick the new dataset we created, the Region one.

Below this you will see the Value field and Label field options. Frequently when dealing with data we have primary key data, such as an INT, that is needed to link data together. But we also need a human readable value, something that the users can see and understand.

A good example is the classic Employee table. You have an EmployeeID and an EmployeeName. For the value field, you’d pick the EmployeeID, but for the Label field you use the EmployeeName.

In this particular case, we are using the same field for both, and it’s perfectly valid to do so. Just pick “RegionCountryName” for both Value and Label drop downs.

clip_image006

For the rest of this example, know that we won’t do anything with Default Values or the Advanced Area. In the Default Values we can pick or set a value to be the default, and the Advanced lets us determine how often we need to refresh the source data for our parameter.

Step 11. Bind the parameter to the main reports Dataset Filter

Now that the parameter is setup, we need to bind it to the Dataset for our main report. By default, the report wizard named it DataSet1 when it setup our report. Right click on it and pick Dataset Properties.

In “the real world” we should have already renamed the default name of DataSet1 to something more meaningful. Let’s do so now, and call it MainReportData.

Next, you might be tempted to click on the Parameters area. But in this case what we will be doing is using the Region parameter as a Filter. When we use it as a filter, Reporting Services reads in the entire dataset, and then applies the value in our parameter before displaying it. This means as we change the value of the parameter, the switch from one set of data to another goes very fast.

Let’s go down to the Filters area, and click Add. In the Expression, pick [Region] from the drop down. This is the Region field from our query, not the parameter. Since we want to allow multiple regions, change the Operator to “In”. Finally for the value, enter the name of the parameter preceded by an @ sign. [@Region] is what we’ll type in.

clip_image008

OK all done, click OK to complete your task.

Step 12. Preview your work

After you save your work, click on the Preview tab. You will now see a new area above the report with the prompt you entered.

clip_image010

Open the drop down and pick a few of the items.

clip_image012

Over on the right side you’ll see the View Report button. Click it to generate the report. As you select different countries, you’ll see the view of the report data updates very quickly.

Enhancing A SQL Server 2008 Reporting Services report: Formatting Numbers and Collapsible Groups

Note, before getting started with this lesson there are some prerequisites you should know about. Please read my post Getting Started with SQL Server 2008 to ensure you have everything setup correctly, otherwise you will be missing objects required to get the code here to work correctly.

Looking back at the report we generated in yesterdays post, there are some things we can do to further enhance its appearance. We can format the look of the total amount, and we can also hide the subcategory details. If you don’t have the report open already, open it and go to the Design tab.

Step 1. Format numeric values.

Right click on the Total Amount area of the details line, and pick “Text Box Properties” on the menu.

In the properties window, click the Number menu option on the left. Then change the Category to “Currency”. Now check on the “Use 1000 separator” option.

When we work with values as big as those that will be on the report, only dollars are typically shown. Reduce the decimal places to zero. You can also change the symbol to one appropriate to your location, put a space after it, or move it behind the dollar field. In this case we’ll just click OK.

Repeat the above steps for the SUM(TotalAmount) text boxes on the ProductCategoryName and FiscalYear lines.

Step 2. Add interactivity to hide sections of the report.

Next we can add interactivity to the report by allowing the user to essentially hide the subcategories, collapsing them into the categories.

Click on the grid, to bring up the grid row and headers. Then right click on the row selector, as pointed to by the red arrow in the following figure:

clip_image002

From the pop up menu, pick “Row Visibility”.

Click the Hide option. Then check on the “Display can be toggled by this report item” and pick “ProductCategoryName’ in the drop down, and click OK.

Step 3. Preview your work.

Save your work then click on the Preview tab. Your should now see a report where the Subcategories are rolled up into the Categories. Each category should have the + symbol to reveal the details behind it.

clip_image004

Click on one of the + signs to expand will reveal the details.

image

Creating a Table Report With Subtotals in SQL Server 2008 Reporting Services

Note, before getting started with this lesson there are some prerequisites you should know about. Please read my post Getting Started with SQL Server 2008 to ensure you have everything setup correctly, otherwise you will be missing objects required to get the code here to work correctly.

The most common type of report in existence is a simple table like report, that lists lines of data from your data source. In this lab we’ll create just such a report, and include subtotals.

Step 1. Add the report.

Start by right clicking on the Reports branch of Solution Explorer and adding a new report.

Once you click past the welcome screen, select your shared data source, or create a new one for this report. When you have done so, click the Next button.

For the query, we’ll use:

SELECT FiscalYear
     , ProductCategoryName
     , ProductSubcategory
     , TotalAmount
  FROM Report.V_SubcategoryTotalsByYear

On the next screen, leave the report type as Tabular and click next.

Place FiscalYear and ProductCategoryName in the Ggroup area, and ProductSubcategory and TotalAmount in the Details area, and click next.

On the table layout screen, you can leave it as Stepped, but put a check by the “Include subtotals” option.

Chose a table style, to be consistent with previous labs we’ll again go with Corporate. Click Next.

Give the report a good name. For this lab we can use “Subcat Totals”. Click Finish to complete the report generation.

Step 2. Format the report

In the report designer, you’ll see the columns are all narrow and on the left. Click inside the report grid and the report tables row and column sizing handles will appear. Place your mouse cursor between two of the columns and drag to expand its width.

Now preview the report and you will should see:

clip_image002

Creating a Matrix Report in SQL Server 2008 Reporting Services

Note, before getting started with this lesson there are some prerequisites you should know about. Please read my post Getting Started with SQL Server 2008 to ensure you have everything setup correctly, otherwise you will be missing objects required to get the code here to work correctly.

Matrix Reports are a special report similar to a pivot table. Matrix reports are not uncommon, and are useful for measuring trends. In this lab we’ll walk through the basic steps of creating a Matrix report.

Step 1. Add the new report.

1.1 Right Click on the Reports branch of Solution Explorer.

1.2 Select Add New Report, then click Next to go past the welcome screen.

1.3 Select your shared data source, or create a new one for this report. When you have done so, click the Next button.

Next you will need to enter the query to supply data to the report. I generally recommend using a tool like SQL Server Management Studio refined your query. For this lab enter the following query:

SELECT [FiscalYear]
     , [ProductCategoryName]
     , [ProductSubcategory]
     , [Product]
     , [TotalAmount]
  FROM [ContosoRetailDW].[Report].[V_ProductTotalsByYear]

Now the Report Wizard will ask what type of report we want. Since we are creating a matrix report select the Matrix option and click Next to continue.

It is now time to design the layout of our matrix report. Click on FiscalYear, then click on the Columns button to move it into the column area. Next click on ProductCategoryName and click the Rows button. Repeat with ProductSubcategory and Product. Finally, click TotalAmount and put it in the details area by clicking the Details button. Your screen should now look like:

clip_image002

Click Next once your screen is complete.

Now the wizard offers to format the report for you. To do so yourself later, picking generic will leave you with plain black and white, no coloring. For this example, let’s pick Corporate and click Next.

Finally we need to give this report a good name. Let’s enter “Product Total By Year Matrix” and click Finish.

 

Step 2. Preview your work.

The report will now be generated and brought into your editor. Click the Preview tab to see the result of your work. A sample of the report is shown below.

clip_image004

Getting Started with SQL Server Reporting Services 2008

I’ve been working on a new presentation on SQL Server Reporting Services. Over the next few weeks I’ll be creating posts with examples on basic techniques around SSRS. Prior to beginning the posts that will follow, there are a few basic setups you will need to do. Some assumptions are:

· You have SQL Server 2008 Developer Edition SP1 installed on your PC, and are doing the labs there.

· As part of the Developer Edition install, you have installed BIDS (Business Intelligence Development Studio, a series of add-ons for Visual Studio).

· You are familiar with using SQL Server Management Studio.

If you are still on SQL Server 2005, the majority of the techniques demonstrated here will still work. There have been some changes between 2005 and 2008 in the layouts of the BIDS SSRS user interface that you will need to adjust to, many of the screen shots would be different under 2005.

Step 1. Download and install ContosoDW

Contoso is a fictional company created by Microsoft. Similar to AdventureWorks, it provides a repository of retail data. ContosoDW is the Data Warehouse for Contoso, optimized for analysis and reporting. You can download it from:

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=868662dc-187a-4a85-b611-b7df7dc909fc

Once downloaded run the installer to create the ContosoDW database in your SQL Server.

Step 2. Create and run the custom scripts for this lab.

There are a series of scripts needed to create the custom views and tables the labs will be reporting from. The easiest way to get them is to download the complete SQL Server Management Studio project along with all the sample labs and other documentation at my Code Gallery site:

http://code.msdn.microsoft.com/getstartedSSRS2008

Step 3. Open BIDS and create a reporting services project.

Open BIDS. (Start, All Programs, Microsoft SQL Server 2008, SQL Server Business Intelligence Development Studio).

Once in BIDS, create a new project. Under project type select “Business Intelligence Projects”, then Report Server Project Wizard”.

Step 4. Setup the shared data source.

To make the transition of reports from development to test to production easier, BIDS allows you to setup a shared data source. The reports can point to this data source, then as the reports migrate among environments all that is needed is to point this shared data source at the appropriate database.

4.1 Right click on the Data Source branch in Solution Explorer and select “Add New Data Source”.

4.2 Click Next to go past the opening screen of the wizard.

4.3 On the next screen you will need to define the connection. Click New, and it will let you fill out a common dialog to create the database connection string. Do so, creating a connection to the ContosoDW database. Once it is in the Data Connections area, highlight it and click Next.

4.4 Give the data source a good name. For this lab I am simply using “Contoso”.

Ready set go!

At this point you should have the basics completed and are ready to start creating reports. Stay tuned to future posts to see a variety of techniques for SSRS.

What I learned at TechEd

Last week I was at the Microsoft TechEd conference in North America, along with over 10,000 of my closest friends. I spent a lot of time in the Microsoft floor area talking to people, and came away with some interesting info about new technologies. As I’m sharing some of these at the Steel City SQL user group tonight, I thought I’d share here too.

First up is OData, the Open Data Protocol from Microsoft. It is an ATOM feed but for data. People can publish under the OData format and be able to consume the data from either a JSON or AtomPub. You can also add security, should you wish to have data available to many consumers but only on a permission basis. You can learn more at http://www.odata.org

Next up is Microsoft’s new “Dallas” project. Dallas is the code name for a data marketplace on it’s Azure platform. Through Dallas users and vendors will be able to consume / provide data feeds. Some will be free, others will be at some cost. There is a catalog through which consumers can look at the various feeds available. This is very much in it’s infancy but there are a few feeds which you can look at and preview.

Microsoft’s SQL Server 2008 R2 Parallel Data Warehouse looked interesting, although it fits a very niche market. It’s an appliance you can purchase that is essentially a rack of SQL Servers. One is the master server, and coordinates all the child servers. As a DBA you manage what appears to be a “normal” instance of a SQL Server. Behind the scenes the controller will propagate changes to the other servers in it’s hub. Scaling can be achieved by simply adding more servers to the existing rack, or additional racks as needed. PDW becomes economical starting around 10 terabytes and scales to well over 100 terabytes of data.

The folks at Red Gate have a new tool called SQL Search that they have released for free to the community. SQL Search is an add-on for SQL Server Management Studio that does lightening fast searches of object names in your database. Just pick the database name and term to search for and SQL Search will populate a grid with all possible matches. If you double click on the row it will navigate SSMS’s Object Explorer pane to the correct spot in the navigation tree with your object. Further, if the object is a view, stored proc, etc it will even display the SQL of the object and highlight the searched item. And did I mention it’s free?

Speaking of cool, free tools the folks at Confio have created a free version of their popular Ignite tool called IgniteFree. It is a real time performance monitoring tool that will work with not just SQL Server but Oracle and DB2 as well. They have versions of the tool that run on both Windows and Unix/Linux.

PowerPivot continues to fascinate and excite me, while I was at TechEd I won a copy of “PowerPivot for Excel and SharePoint”. I had this on my “to buy” list anyway so considered myself lucky. I’m about a sixth of the way through the book and it has been really good so far. It starts with a quick tour of the Excel piece, then walks you through the SharePoint install so you can quickly get up and running in a test environment. Later chapters delve much more deeply into PowerPivot. If you are looking for a good PowerPivot book I would recommend it.

Finally, even if you couldn’t be there you can watch the sessions from this and past Tech Ed’s. Microsoft has released them to the general public at http://www.msteched.com/

 

*FTC Discloser, I am in the “Friends of Red Gate” program where I get copies of their tools in order to test and provide feedback. In this case the disclaimer probably isn’t necessary since the SQL Search tool is freely available to all, but I’d prefer to keep things above board.

SQL Server Full Text Searching at SQL Saturday #22 Pensacola FL

Today I had the opportunity to hang out with a bunch of cool people at SQL Saturday 22 in Pensacola FL. My presentation is SQL Server Full Text Searching, a Guide for Dev’s and DBAs. While I’ve done this presentation in the past, it’s been updated with new material and demos. You’ll find all the code, and the slide deck at the Code Gallery site,

http://code.msdn.microsoft.com/SqlServerFTS

Thanks for coming!

Pragmatic Works Free SQL Server Training

Just wanted to mention the Pragmatic Works company did a week of free webinars. You can view all of these which cover a wide variety of subjects. The sessions include:

  • Introduction to Managing a SQL Server Database by Jorge Segarra
  • Beginning T-SQL by Patrick LeBlanc
  • The Modern Resume: Building Your Brand by Brian Knight
  • How to Become An Exceptional DBA by Brad McGehee
  • Fundamentals of SSIS by Brian Knight
  • 0 to Cube in 60 Minutes (SSAS) by Brian Knight
  • Trouble Shooting SQL Server by Christian Bolton
  • Introduction to SQL Server Reporting Services by Devin Knight

To get access to the seminars, simply go to:

http://www.pragmaticworks.com/resources/webinars/February2010Webinar.aspx

PowerPivot for Excel 2010 at the Steel City SQL Users Group January 19, 2009

Today I am at the Steel City SQL Users Group, presenting on Microsoft’s newest BI Tool, PowerPivot.

The slide deck for this meeting can be found at:

https://arcanecode.files.wordpress.com/2010/01/powerpivot_long.pdf

To see all my posts so far on PowerPivot, you can use the link below to filter.

https://arcanecode.com/category/powerpivot/

Thanks for coming!

Introducing Microsoft PowerPivot

What is PowerPivot? Well according to Microsoft:

“PowerPivot is Microsoft Self-Service Business Intelligence”

I can see from the glazed looks you are giving your monitor that was clear as mud. So let’s step back a bit and first define what exactly is Business Intelligence.

Business Intelligence

Business Intelligence, often referred to as simply “BI”, is all about taking data you already have and making sense of it. Being able to take that information and turn it from a raw jumble of individual facts and transform it into knowledge that you can take informed actions on.

In every organization there is already someone who is doing BI, although they may not realize it. Microsoft (and many IT departments) refer to this person as “that guy”. A power user, who grabs data from anyplace he (or she) can get it, then uses tools like Excel or Access to slice it, dice it, and analyze it. This person might be an actual Business Analyst, but more often it’s someone for who BI is not their main job. Some common examples of people doing their own BI today are production managers, accountants, engineers, or sales managers, all who need information to better do their job. Let’s look at an illustration that will make it a bit clearer.

In this example, put yourself in the role of a sales manager. You have gotten IT to extract all of your sales orders for the last several years into an Excel spreadsheet. In order to determine how well your sales people are doing, you need to measure their performance. You’ve decided that the amount sold will be a good measure, and use Excel to give you totals.

IntroEx01

In BI terms, the column “Total Sales” is known as a measure, or sometimes a fact, as it measures something, in this case the sales amount. The grand total sales amount is often called an aggregation, as it totals up the individual rows of data that IT gave us. But now you might be wondering why Andy’s sales are so low? Well, now you want to dig deeper and look at sales by year.

IntroEx02

In BI terms, the names of the sales people are a dimension. Dimensions are often either a “who” (who sold stuff) or a “what” (what stuff did we sell). Places (where was it sold) and dates (when was it sold) are also common dimensions. In this case the sales dates across the top (2007, 2008, 2009) are a date dimension. When we use two or more dimensions to look at our measures, we have a pivot table.

Now we can see a picture emerging. It’s obvious that Andy must have been hired as a new salesperson in late 2008, since he shows no sales for 2007 and very small amount in 2008. But for Paul and Kimberly we can look at something called trends in the BI world. Kimberly shows a nice even trend, rising slowly over the last three years and earns a gold star as our top performer.

By being able to drill down into our data, we spot another trend that was not readily obvious when just looking at the grand totals. Paul has been trending downward so fast the speed of light looks slow. Clearly then we now have information to take action on, commonly known as actionable intelligence.

So remind me, why do we need PowerPivot?

As you can see in the above example, “that guy” in your company clearly has a need to look at this data in order to do his job. Not only does he need to review it, he also has the issue of how to share this information with his co-workers. Unfortunately in the past the tools available to “that guy” have had some drawbacks. The two main tools used by our analyst have been either Excel, or a complete BI solution involving a data warehouse and SQL Server Analysis Services.

Excel’s main limitations center around the volume of data needed to do good analysis. Excel has limits to the number of rows it can store, and for large datasets a spreadsheet can consume equally large amounts of disk space. This makes the spreadsheet difficult to share with coworkers. In addition mathematical functions like aggregations could be slow. On the good side, Excel is readily available to most workers, and a solution can be put together fairly quickly.

A full blown BI solution has some major benefits over the Excel solution. A data warehouse is created, and then SQL Server Analysis Services (often abbreviated as SSAS) is used to precalculate aggregations for every possible way an analyst might wish to look at them. The data is then very easy to share via tools like Excel and SQL Server Reporting Services. While very robust and powerful solution, it does have some drawbacks. It can take quite a bit of time to design, code, and implement both the data warehouse and the analysis services pieces of the solution. In addition it can also be expensive for IT to implement such a system.

Faster than a speeding bullet, more powerful than a locomotive, it’s PowerPivot!

PowerPivot combines the best of both worlds. In fact, it’s not one tool but two: PowerPivot for Microsoft Excel 2010, and PowerPivot for SharePoint 2010. What’s the difference you ask? Good question.

PowerPivot for Microsoft Excel 2010

PowerPivot acts as an Add-on for Excel 2010, and in many ways is quite revolutionary. First, it brings the full power of SQL Server Analysis Services right into Excel. All of the speed and power of SSAS is available right on your desktop. Second, it uses a compression technology that allows vast amounts of data to be saved in a minimal amount of space. Millions of rows of data can now be stored, sorted, and aggregated in a reasonable amount of disk space with great speed.

PowerPivot can draw its data from a wide variety of sources. As you might expect, it can pull from almost any database. Additionally it can draw data from news feeds, SQL Server Reporting Services, other Excel sheets, it can even be typed in manually if need be.

Another issue that often faces the business analyst is the freshness of the data. The information is only as good as the date it was last imported into Excel. Traditionally “that guy” only got extracts of the database as IT had time, since it was often a time consuming process. PowerPivot addresses this through its linked tables feature. PowerPivot will remember where your data came from, and with one simple button click can refresh the spreadsheet with the latest information.

Because PowerPivot sits inside Microsoft Excel, it not only can create basic pivot tables but has all the full featured functionality of Excel at its disposal. It can format pivot tables in a wide array of styles, create pivot charts and graphs, and combine these together into useful dashboards. Additionally PowerPivot has a rich set of mathematical functionally, combining the existing functions already in Excel with an additional set of functions called Data Analysis eXpressions or DAX.

PowerPivot for SharePoint 2010

PowerPivot for Excel 2010 clearly solves several issues around the issue of analysis. It allows users to quickly create spreadsheets, pivot tables, charts, and more in a compact amount of space. If you recall though, creation was only half of “that guys” problem. The other half was sharing his analysis with the rest of his organization. That’s where PowerPivot for SharePoint 2010 comes into play.

Placing a PowerPivot Excel workbook in SharePoint 2010 not only enables traditional file sharing, but also activates several additional features. First, the spreadsheet is hosted right in the web browser. Thus users who might not have made the transition to Excel 2010 can still use the PowerPivot created workbook, slicing and filtering the data to get the information they require.

Data can also be refreshed on an automated, scheduled basis. This ensures the data is always up to date when doing analysis. Dashboards can also be created from the contents of a worksheet and displayed in SharePoint. Finally these PowerPivot created worksheets can be used as data sources for such tools as SQL Server Reporting Services.

Limitations

First, let me preface this by saying as of this writing all of the components are either in CTP (Community Technology Preview, a pre-beta) or Beta state. Thus there could be some changes between now and their final release next year.

To use the PowerPivot for Excel 2010 components, all you have to have is Excel 2010 and the PowerPivot add-in. If you want to share the workbook and get all the rich functionality SharePoint has to offer, you’ll have to have SharePoint 2010, running Excel Services and PowerPivot 2010 Services. You’ll also have to have SQL Server 2008 R2 Analysis Services running on the SharePoint 2010 box. Since you’ll have to have a SQL Server instance installed to support SharePoint this is not a huge limitation, especially since SSAS comes with SQL Server at no extra cost.

One thing I wish to make clear, SharePoint 2010 itself can run using any version of SQL Server from SQL Server 2005 on. It is the PowerPivot service that requires 2008 R2 Analysis Services.

One other important item to note: at some point the load upon the SharePoint 2010 server may grow too large if especially complex analysis is being done. Fortunately SharePoint 2010 ships with several tools that allow administrators to monitor the load and plan accordingly. At the point where the load is too big, it is a clear indication it’s time to transition from a PowerPivot solution to a full BI solution using a data warehouse and SQL Server Analysis Services.

What does PowerPivot mean for business users?

For business users, and especially “that guy”, it means complex analysis tools can be created in a short amount of time. Rich functionality makes it easier to spot trends and produce meaningful charts and graphs. It also means this information can be shared with others in the organization easily, without imposing large burdens on the corporate e-mail system or local file sharing mechanisms.

No longer will users be dependent on IT for their analysis, they will have the power to create everything they need on their own, truly bringing “self service BI” to fruition.

What does PowerPivot mean for Business Intelligence IT Pros?

The first reaction many BI developers have when hearing about PowerPivot is “oh no, this is going to put me out of a job!” Far from it, I firmly believe PowerPivot will create even more work for BI Professionals like myself.

As upper management grows to rely on the information provided by PowerPivot, they will also begin to understand the true value BI can bring to an organization. Selling a new BI solution into an organization where none currently exists can be difficult, as it can be hard to visualize how such a solution would work and the value it brings. PowerPivot allows BI functionality to be brought into an organization at a low development cost, proving the value of BI with minimal investment. Thus when there is a need to implement a larger, traditional BI project those same managers will be more forthcoming with the dollars.

Second, as users pull more and more data, they are going to want that data better organized than they will find in their current transactional business systems. This will in turn spur the need to create many new data warehouses. Likewise the IT department will also want data warehouses created, to reduce the load placed on those same transactional business systems.

I also foresee PowerPivot being used by BI Pros themselves to create solutions. The database structure of many transactional database systems can be difficult to understand even for experienced IT people, much less users. BI Pros can use PowerPivot to add a layer of abstraction between the database and the users, allowing business analysts to do their job without having to learn the complexity of a database system.

BI Pros can also use PowerPivot to implement quick turnaround solutions for customers, bringing more value for the customer’s dollar. When a BI Pro can prove him (or her) self by providing rich functionality in a short time frame it’s almost always the case they are brought back in for multiple engagements.

PowerPivot also provides great value to BI Pros who are employed full time in an enterprise organization. They can create solutions much quicker than before, freeing them up to do other valuable tasks. In addition PowerPivot solutions can provide a “stop gap” solution, pushing the date at which the organization needs to spend the dollars for a full blown BI solution and allowing IT to plan better.

Finally I see great value in PowerPivot as a prototyping tool for larger BI projects. Now users can see their data, interact with it, analyze it, and ensure the required measures and dimensions are present before proceeding with the larger project.

I’ll reiterate, if anything I believe PowerPivot will create an explosion of work for the Business Intelligence Professional.

Where can I learn more?

Well right here for one. I have become quite interested in PowerPivot since seeing it at the SQL PASS 2009 Summit. I think it will be a valuable tool for both myself and my customers. This will be the first of many blog posts to come on PowerPivot. I am also beginning a series of presentations on PowerPivot for local user groups and code camp events. The first will be Saturday, November 21st 2009 at the SharePoint Saturday in Birmingham Alabama, but there will be many more to come. (If you’d like me to come speak at your group just shoot me an e-mail and we’ll see what we can arrange.)

There’s also the PowerPivot site itself:

I’ve also found a small handful of blogs on PowerPivot, listed in no particular order:

Summary

Thanks for sticking with me, I know this was a rather long blog post but PowerPivot has a lot of rich functionality to offer. While PowerPivot is still in the CTP/Beta stage as of this writing, I see more and more interest in the community, which will continue to grow as PowerPivot moves closer to release. I hope this post has set you off on the right step and you’ll continue to come back for more information.

TechMixer University – SSIS for Developers

In addition to help recruit speakers, I also had the privilege of speaking at TechMixer University 2009.

The slide deck and main demo can be found at my Code Gallery site:

https://code.msdn.microsoft.com/Release/ProjectReleases.aspx?ProjectName=SSISForDevs&ReleaseId=2883

The calling of SSIS from .Net demo can be found at:

http://code.msdn.microsoft.com/ssisfromnet

Thanks to everyone who attended TechMixer University. I look forward to seeing you next year!

SQL Saturday 25 Gainesville GA – October 10 2009

SQL Saturday 25 Logo

SQL Saturday 25 occurred in the lovely town of Gainesville GA on October 10th. At the event I did two presentations.

My first presentation of the day was Introduction to SQL Server Integration Services. The sample project, slide deck, and step by step instructions can be found at http://code.msdn.microsoft.com/introssis . In addition I also showed how to call SSIS from a .Net application. You can find that sample at http://code.msdn.microsoft.com/ssisfromnet

The second presentation is SQL Server Full Text Searching. You can find the slide deck in PDF format as well as sample code at http://code.msdn.microsoft.com/SqlServerFTS.

Thanks to Stu and the crew for a great event!

Using SSIS Package Configuration Values as Parameters to SQL Tasks

Last weekend I presented at SQL Saturday in Redmond WA. One attendee asked if it was possible to use values from the Package configuration file as parameters to a SQL Task. The answer was a definite yes, although I didn’t have a good example. This post aims to fix that.

Let’s start with the basic workflow. First you will need to create a variable, this variable will be used to pass the value from the config file to the SQL statement. Next you will need to establish a package configuration file, to hold the configured value. After that you will create an Execute SQL Task. In the input statement for the task, simply use ? for each parameter in the T-SQL statement. Finally you’ll click on the Parameter Mapping area of the SQL Task and map the variable to the position of each ?.

Now that you have a basic overview of where we’re going, let’s start this example by creating a simple table to update. Pick your favorite database, or create a test one, then run a simple script to create a table. I used my ArcaneCode database, which I use for samples, and created a table named TestParamToProcs in the dbo schema.

USE [ArcaneCode]
GO

/****** Object:  Table [dbo].[TestParamToProcs]    Script Date: 10/06/2009 21:45:19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TestParamToProcs](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [SomeText] [nvarchar](50) NULL,
CONSTRAINT [PK_TestParamToProcs] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Now let’s create a new package. I started by opening BIDS (Business Intelligence Developer Studio), creating a solution and named it ConfigFileToSqlParams. I added a Shared Data Source to connect to my database. Next I renamed the default package to ParamToProcs.dtsx. I added the shared data source to the connection managers area of the package.

OK, that takes care of the basics. Next we need to setup the package for variables and a config file. Open up the variables window (click in the Control Surface area, and in the SSIS menu pick Variables.) Create a new variable, we’ll call it MyParam. Make it a string and insert the value of “X” into it.

Now right click on the Control Flow surface (all that yellow area in the Control Flow tab) and pick Package Configurations. Click the check box to “Enable package configurations”, then click the Add button at the bottom of the screen. You are now presented with the Package Configuration Wizard. The first screen is just a welcome screen, you can click past it. The next screen is the “Select Configuration Type” screen. Make sure the default of “XML configuration file” is selected in the drop down. Next under “Specify configuration files directly” enter the path and file name for your config XML file. I named mine ParamToProcsConfig.xml and clicked Next.

The next step in the wizard is the “Select Properties to Export” screen. Expand the Variables area in the tree, then the tree for MyParam, then Properties. Now make sure the only box checked is the one for “Value”.

1_PropsToExport

Click Next once your screen looks like the one above, and you’ll be at the completion screen. Give your package config a good name, I used MyParamConfig, and clicked Finish.

Now let’s drop 3 SQL Tasks into the Control Flow surface. The first task will simply delete all the rows in the table that was just created. That way we can run the package repeatedly. For all three tasks set the Connection to the shared source you added in the Connection Managers area. Now for the SQLStatement of our first task, we’ll use Direct Input and enter this SQL statement:

DELETE FROM dbo.TestParamToProcs

For the next SQL task we’ll insert a row into our table to manipulate. Use this for the SQL Statement property.

INSERT INTO dbo.TestParamToProcs (SomeText) VALUES (‘Hello’)

OK, those were fairly easy and straight forward. Now comes the fun part. For the next SQL Task, we’ll use this statement for the SQLStatement property.

UPDATE dbo.TestParamToProcs SET SomeText = ?

Note the use of the ? (question mark) for the value of the SomeText field. This is the parameter. Now we need to supply a value to this parameter. Over on the left, click the Parameter Mapping tab. Click Add and a new row will be placed in the grid. In the first column, Variable Name, from the drop down pick “User::MyParam”. Leave the Direction as “Input”. Since this is a string variable, we’ll need to change the Data Type column to “VARCHAR”. Finally we need to set the Parameter Name, For this we’ll enter a 0 (zero). We’ll leave the Parameter Size at the default of –1.

2_Params

When your dialog resembles this one, you can click OK. If you needed to use more than one parameter, just use ? for each place where a parameter would go in your T-SQL statement. Then use the values of 0, 1, 2 etc for the Parameter Name. 0 will map to the first ?, 1 to the second, and so on. OK at this point your SSIS package should look something like:

3_Package

If you now run the package, then jump into SQL Server Management Studio and run a “SELECT * FROM dbo.TestParamToProcs”, you should see one row with the SomeText column set to X. Great! Now lets give it one more test. We should change the config file, and run the package outside of BIDS.

First, open notepad or some other text editor, then open up the ParamToProcsConfig.XML file. Move to the <ConfiguredValue> tags, and change the X to something else. I used “ArcaneCode” as it was rather spiffy, but you can use your name, or your cat’s name if you like. Save the file and you can close your text editor.

Now let’s use dtexec to run our package. Open up a command window and navigate to the folder where your package is located. I then used the dtexecui utility to create a package execution command. I’d encourage you to play with this, but so we’re all on the same page here is the dtexec command line I came up with:

dtexec /FILE "D:\Presentations\SQL Server\ConfigFileToSqlParams\ConfigFileToSqlParams\ConfigFileToSqlParams\ParamToProcs.dtsx" /CHECKPOINTING OFF  /REPORTING EW

Of course you’ll want to change the path to wherever you have the ParamToProcs.dtsx package. You should be able to run the above command line and execute your package. If you the jump back to SSMS and rerun the “SELECT * FROM dbo.TestParamToProcs”, you should now see the row with the new value from the config file.

To summarize, the basic steps are:

1. Create the variable

2. Store the variable in a package configuration XML file.

3. Create a SQL Task. Use ? for the parameters in the T-SQL statement.

4. Map the variables to the parameters.

5. Run. Be happy.

And that’s all there is to it.

SQL Saturday Redmond – October 3 2009

I am fortunate enough to be able to give three presentations at Redmond WA’s SQL Saturday event. The first session is “Introduciton to Data Warehousing / Business Intelligence”. Here is the PDF slide deck for that presentation. (Right click and save as if you want to save a copy for later reference).

The second presentation is SQL Server Full Text Searching. You can find the slide deck in PDF format as well as sample code at http://code.msdn.microsoft.com/SqlServerFTS.

The final presentation of the day was Introduction to SQL Server Integration Services. The sample project, slide deck, and step by step instructions can be found at http://code.msdn.microsoft.com/introssis . In addition I also showed how to call SSIS from a .Net application. You can find that sample at http://code.msdn.microsoft.com/ssisfromnet .