Search results matching tags 'SQL 2008' and 'SSRS'

Here Comes the FY11 Earmarks Database

Mike C — Sun, 19 Dec 2010 04:51:00 GMT

I'm really interested in politics (don't worry, I'm not going to start bashing politicians and hammering you with political rage). The point is when the U.S. FY11 Omnibus Spending Bill (the bill to fund the U.S. Government for another year) was announced it piqued my interest. I'm fascinated by "earmarks" (also affectionally known as "pork"). For those who aren't familiar with U.S. politics, "earmark" is a slang term for "Congressionally Directed Spending". It's basically the set of provisions added to bills by Senators and Representatives in the U.S. Congress, directing money to be spent on certain programs or projects without all the hassle of introducing a separate bill, sending it through umpteen committees, and getting enough supporters in both chambers to vote it up. Most of the time Senators and Representatives introduce earmarks that send money back to their home states (see this guy for details).

Now it looks like the Omnibus Bill is dead for now, but the record of the earmarks is still available at http://appropriations.senate.gov/news.cfm in PDF format (no, you don't have to visit Wikileaks). Just look for the "Earmarks" links at the bottom of the page under "All News Items". So what I have been doing the past few days, since the bill was announced, is loading the earmarks into a SQL database. It turned out to be a little more complex than it should have been. There was the issue of extracting the data out of the PDF files, normalizing it, ETLing it into SQL Server, cleaning it up (a *lot* of manual cleanup required), adding and cleaning up reference data, and finally linking the earmarks to the reference data.

You can download the resulting SQL 2008 database backup file from this post and restore it to your test server to play around with. Here are some sample queries I ran:

SELECT SUM(e.Amount)
FROM dbo.Earmark e;

Result:
8313820025

(That's $8.3 Billion dollars in earmarks -- note that some earmarks did not specify $ amounts, so this number is low)

Want to see total earmarks requested by party affiliation?

WITH CTE
AS
(
SELECT DISTINCT e.EarmarkID, e.Amount, c.Party
FROM dbo.Earmark e
INNER JOIN dbo.EarmarkCongress ec
ON e.EarmarkID = ec.EarmarkID
INNER JOIN dbo.Congress c
ON ec.CongressID = c.CongressID
)
SELECT Party, SUM(Amount)
FROM CTE
GROUP BY Party;

Results:
D, 6779032457
R, 2933829318
I, 216149500

This one actually double-counts some items since the same earmark can be sponsored by a Democrat (D), a Republican (R), and/or an Independent (I) all at the same time.

And if you want to see how much was requested by your Senators and Representatives:

SELECT c.Chamber,
c.LastName,
c.FirstName,
c.Party,
SUM(e.Amount)
FROM dbo.Earmark e
INNER JOIN dbo.EarmarkCongress ec
ON e.EarmarkID = ec.EarmarkID
INNER JOIN dbo.Congress c
ON ec.CongressID = c.CongressID
WHERE c.State = 'NJ'
GROUP BY c.Chamber,
c.LastName,
c.FirstName,
c.Party;

Results:
House, Adler, John D, 23848125
House, Andrews, Rob D, 27710000
House, Holt, Rush D, 103953000
House, Pallone, Frank D, 21810000
House, Pascrell, Bill D, 15143000
House, Payne, Donald D, 52908000
House, Rothman, Steven D, 107153000
House, Sires, Albio D, 120141000
Senate, Lautenberg, Frank D, 239061125
Senate, Menendez, Bob D, 243982125

Again there can be double-counting since an earmark can have multiple sponsors (very likely when you're talking about Senators and Representatives from the same state).

Also note that I had to add in the non-voting Congressional delegates from U.S. territories (Puerto Rico, U.S. Virgin Islands, Guam, etc.) and Washington, D.C. to the reference data. They don't vote, but they're allowed to attach earmarks to bills to bring some cash back home.

I'd love to see what others are able to do with this data. Some ideas -- use Reporting Services to map earmarks across the country; come up with clever queries to allocate earmarks equally across sponsors to avoid the double-counting issues; some Top n style queries by amounts, counts, etc.; combine it with spatial data and other reference data out there; compare things like # of sponsors for an earmark vs. the amount of the earmark, or seniority of sponsors vs. the amount of the earmark; maybe do something clever with it in PowerPivot. If you find this type of data interesting and you download the database to play with, let us know the results and what you come up with.

T-SQL Tuesday #005: Creating SSMS Custom Reports

Mike C — Tue, 13 Apr 2010 00:14:00 GMT

This is my contribution to the T-SQL Tuesday blog party, started by Adam Machanic and hosted this month by Aaron Nelson. Aaron announced this month's topic is "reporting" so I figured I'd throw a blog up on a reporting topic I've been interested in for a while -- namely creating custom reports in SSMS.

Creating SSMS custom reports isn't difficult, but like most technical work it's very detailed with a lot of little steps involved. So this post is a little longer than usual and includes a lot of screenshots. There's also a downloadable ZIP file with the projects from this article included.

SSMS Custom Reports

SQL Server 2008 and 2005 both offer custom report functionality in Management Studio. With a little bit of work you can create custom SSMS reports just like the standard reports that Microsoft ships with Management Studio. The Disk Usage Report shown below is just one of the many standard reports that comes with SSMS.

Creating a Report Project

SSMS uses the SQL Server Reporting Services (SSRS) 2005 client/viewer control to render both standard and custom reports. This is true for both SSMS 2005 and SSMS 2008. So the first step to building a custom SSMS report is to fire up Visual Studio 2005 and create a new Report Server Project as shown below. For this example we'll create a custom report that lists missing indexes, so give the project the name Missing Index.

Once you create the Report Server Project right-click on Reports in the Solution Explorer and choose Add > New Item...

When the Add New Item box appears, choose the Report template and give the report a name. For this example I named the report Missing Index.rdl - the .rdl extension stands for "Report Definition Language", and is the standard extension for SSRS report definition files.

Defining the Report Dataset

Once the report is added to your project you have to add a new dataset to the report. The dataset defines the structure and content of the source data that will populate your report. Choose <New Dataset...> from the Dataset: dropdown.

Visual Studio will respond with a Data Source box. Just make sure the Type: dropdown is set to the default Microsoft SQL Server and put Data Source=. in the Connection string: box. This is all that's required since the SSMS custom report you're creating will use connections created and managed by SSMS's Object Explorer.

After you define the data source, you can define the SQL query that will populate your report. Just put the query in the dataset window as shown below.

I borrowed (and slightly modified) the following query from Brent Ozar. He originally published it at SQLServerPedia. This particular query uses SQL Server's missing index Dynamic Management Views (DMVs) to identify missing indexes.

-- Begin missing index query

WITH cte
AS
(
    SELECT mid.object_id AS object_id,
        QUOTENAME(OBJECT_SCHEMA_NAME(mid.object_id)) AS table_schema,
        QUOTENAME(OBJECT_NAME(mid.object_id)) AS table_name,
        QUOTENAME('IX_' + OBJECT_SCHEMA_NAME(mid.object_id) + '_' +
            LEFT(CAST(NEWID() AS CHAR(36)), 8)) AS index_name,
        mid.index_handle
    FROM sys.dm_db_missing_index_details mid
)
SELECT
    DENSE_RANK() OVER
    (
        ORDER BY cte.table_schema, cte.table_name
    ) AS table_color,
    ROW_NUMBER() OVER
    (
        PARTITION BY cte.table_schema, cte.table_name
        ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC
    ) AS index_color,
    cte.table_schema,
    cte.table_name,
    cte.index_name AS index_name,
    (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) AS calc_impact,
    'CREATE NONCLUSTERED INDEX ' + cte.index_name + ' ON ' +
    cte.table_schema + '.' + cte.table_name +
    ' (' + COALESCE(mid.equality_columns, '') +
        CASE WHEN mid.inequality_columns IS NULL
            THEN ''
            ELSE CASE WHEN mid.equality_columns IS NULL
                THEN ''
                ELSE ','
                END + mid.inequality_columns
            END +
    ') ' + CASE WHEN mid.included_columns IS NULL
            THEN ''
            ELSE 'INCLUDE (' + mid.included_columns + ')'
            END +
    ';' AS create_stmt,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig
    ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
    ON mig.index_handle = mid.index_handle
        AND mid.database_id = DB_ID()
INNER JOIN cte
    ON cte.index_handle = mid.index_handle
WHERE migs.group_handle IN
(
    SELECT group_handle
    FROM sys.dm_db_missing_index_group_stats WITH (NOLOCK)
)
ORDER BY calc_impact DESC;

-- End missing index query

There are a couple of caveats to keep in mind about the missing index DMVs that this query relies on:

They're not designed to be exhaustive. If you want to get a serious analysis of useful indexes in your databases, you definitely want to use a tool that will perform a more extensive analysis.
The DMVs only persist their information since the last time the SQL Server service was restarted. If you've recently restarted the service you'll get very little information back. These DMVs are best to use when your server has been running under normal load for a while.
The suggestions returned by the DMVs aren't always the best way to go. These DMVs often recommend suggested indexes with lots of overlap (lots of columns in common, often in similar order).

To put it another way, don't take the raw information returned by these DMVs at face value. The information they return is definitely useful for determining where indexes might provide benefit if your system has been running under normal load for a while, and the redundant index suggestions tend to indicate which indexes might be most useful. But these DMVs just amount to a starting point for analyzing your indexing needs. I'd recommend against creating dozens of redundant indexes based on the raw output of these DMVs.

Visual Studio will normally populate the dataset fields. If for some reason it doesn't, you can manually edit the fields by hitting the Edit Selected Dataset button to pull up the Dataset window. You can then enter the field names on the Fields tab if they aren't already populated.

Building the Report

So far most of what we've done is just setup. With this done, it's time to design and build the actual report. For this, click on the Visual Studio Layout tab to get to the report designer surface. You can drag and drop text boxes, charts, tables, images and any other controls from the Toolbox onto the designer surface. To keep it simple we'll just drag a text box and a table onto the designer.

Put the title of the report in the text box (in this case "Missing Index Report") and format it to your liking. The table we dragged onto the designer surface has three columns by default. For this example we want six columns total. To add more columns right-click on the top border of the table and choose Insert Column to the Left to add a new column. Repeat two more times.

In the Header row of the table we'll type in the headers for each column like the following:

In the Detail row we'll put in the formulas to populate the database fields like this:

SSRS formulas begin with the equal sign (=). Fields from the dataset are referenced directly using the format Fields!field_name.Value.

Deploying and Running the Report

At this point use Visual Studio to build the project. Once it builds without error, navigate to the project directory in Windows Explorer and copy the Missing Index.rdl file to the SSMS Custom Reports directory (on my computer this directory is located at C:\Users\Michael\Documents\SQL Server Management Studio\Custom Reports, it'll be different on yours).

You can now bring up this report in SSMS by right-clicking on a database in the Object Explorer and selecting Reports > Custom Reports... to select the custom report.

Choose your custom report from the file selection box and click Open.

When you run a custom report in SSMS you'll get a warning like the following:

Just choose Run. You might also want to check the box that says "Please don't show this warning again" to keep the box from popping up every time you run a custom report. The simple custom report you've created looks like the one shown below.

You can use images, color and other formatting techniques to make the report easier to read and use, and more flexible for your users. Consider the image below, which is a screenshot of a reformatted version of the Missing Index report. This one includes more information, color and collapsible sections. Both reports are included in the attached ZIP file under the Missing Index and Missing Index Color directories.

You can play around with the source files included in the attached ZIP file.

It's Official - SQLSaturday is Coming to NYC!

Mike C — Sat, 06 Feb 2010 19:59:00 GMT

New Jersey SQL Server User Group (NJSQL) is bringing SQLSaturday #39 to NYC on April 24, 2010! The free all-day training event will be hosted by Microsoft at their Midtown Manhattan offices. The speaker line-up is growing fast—if you'd like to present, visit the event's open call for speakers. This is a free full-day training event, but registration is required to attend. Seating is limited.

Registration, speaker, and sponsorship details are posted at http://www.sqlsaturday.com/39/eventhome.aspx.