Jessica M. Moss

PASS Community Summit 2009

2009-10-31T15:13:00.001-07:00

One of my favorite times of the year is the PASS Community Summit. It's five jam-packed days of SQL Server sessions by Microsoft employees, MVPs, and the people that you read about in technical books. It's pre-conference and post-conference workshops on all different levels of topics of SQL Server. It's a way to meet new people who love to do what you love to do and connect with old friends who you only see once a year. It's a way to get a job or sell a job. It's all of this and so much more that I can't even begin to describe.

I'm excited to attend for the third year starting tomorrow. If you are attending the Summit, I hope you'll come and introduce yourself. You can find me at one of these locations/times:

Monday, November 2, 2009
4:30pm-6:30pm
Networking Pre-Con - I'm really excited to learn about the best ways to meet people and meet other people who are interested in the same thing!

Tuesday, November 3, 2009
11:45am-12:45pm
Birds of a Feather Lunch (Expo Hall 4B) - I am hosting a table during lunch on “Is Business Intelligence an Oxymoron?”. I'm looking forward to hearing your thoughts on how we can incorporate the business into our data warehouse and delivery design.

Wednesday, November 4, 2009
11:30am-1:30pm
Women in Technology Luncheon and Panel Discussion (Room 6BC) - I am one of the panelists discussing “Energizing the Next Generation: Encouraging and Inspiring Young Women to Choose Tech Careers”.

3:00pm-4:15pm
“Adding SSRS Report Bells and Whistles” (Room 612 (165) ) – I am presenting on Reporting Services 2008 design best practices and guidance.

Thursday, November 5, 2009
12:00pm - 12:30pm
Book Signing (South Lobby, across from Summit Bookstore) – I’ll be hanging out by the bookstore as one of the authors of Microsoft SQL Server 2008 Integration Services Problem-Design-Solution. Definitely come by if you have any question about the book!

Of course, I will also be attending sessions and some evening events, so please come and talk to me there instead. I’m looking forward to attending the best Summit yet.

Fall 2009 Conference Odyssey

2009-10-15T22:10:00.001-07:00

As promised, I wanted to let you know about a few events where I'll be in the upcoming weeks. This Saturday (10/17), I'm making the trek up to East Iowa for their SQLSaturday event. They pulled in quite a few great speakers, and they've also invited me to speak on my favorite BI products: SSRS, SSIS, and SSAS! It looks like registration is still open, so if you're in the area, you should definitely sign up, come by, and say hi.

The following week, I am pleased to be speaking at the Charlotte SQL Server User Group on Extract, Transform, and Load your Data Warehouse. I'll be making the drive down (audio book suggestions, anyone?) on Thursday, October 22 to speak that evening. I'm looking forward to meeting new people and seeing some old friends in the area. I'll also be staying over for a soon-to-be-announced event on Friday.

The first week of November, I'll be at the PASS Community Summit. These is my third year attending the conference, second year speaking, and first time as a panelist on the WIT luncheon. This conference is so big and has so much going on that I will give it its own post closer to that time.

The following week is the SharePoint Connections conference. My friend and colleague, Kevin Israel, invited me to deliver a joint preconference workshop entitled SharePoint BI - Building Dazzling Dashboards and Sizzling Scorecards in SharePoint. Can you tell which section will be my part? *coughBIcough* ;) The all-day workshop is on November 9, so I'll be taking the rest of the week to attend some sessions and talk shop with attendees. Please do look me up and introduce yourself!

Rounding out the list is the SharePoint Saturday event in Richmond on November 21. I'm not sure if I'm volunteering or speaking yet, but either way, I will be there! The SharePoint Saturday events have taken off like a rocket, and I'm excited to participate.

That wraps up the current list. I hope to see you at one of the events.

Update: Fixed SQLSaturday date

I'm an Author and Other Sort-Of-But-Not-Completely-Related News

2009-10-08T10:54:00.001-07:00

Over the years, I've read several posts similar to this one, and now it's my turn! At the beginning of this year, I was asked to participate in writing a new SSIS book with co-authors: Erik Veerman, Brian Knight, and Jay Hackney. After many late nights and just a few cans of Mountain Dew, I'm proud to say that the book has been completed and will be published at the beginning of November. Please check out: Microsoft SQL Server 2008 Integration Services Problem-Design-Solution.

Along with that exciting news comes an apology for not blogging as much as I would have liked. Here's a quick recap from the past several months:

David Giard invited me to do a short video on Integrations Services on his Technology and friends series.
I had a great conversation with Richard Campbell and Greg Hughes on Reporting Services on RunAs Radio.
The PASS BI Virtual Chapter had me speak a few times. I definitely recommend you check out the line up of great speakers/presentations that they have coming up as well!
I flew to Nashville to participate in the devLink conference with two presentations on SSRS and SSIS.
I am honored a second time to be re-awarded a Microsoft MVP Award on October 1. Thank you all for allowing me to come speak at events and share the exciting things I've learned with you.

Also, if you follow me on Twitter, you probably already know a lot of this. And if you don't follow me on Twitter, why not?

More posts to follow on future events and, of course, some technical content as well!

Sitting on the beach in Pensacola

2009-07-08T20:12:00.001-07:00

The SQLSaturday phenomenon is taking over the nation! I was honored to be selected to give a presentation at the Pensacola event on June 6, 2009 on PerformancePoint Server 2007 M&A (I'm still waiting to hear more information about next version incorporated with SharePoint :)) I had a great time seeing the SQLSaturday crew, old friends, and meeting some new friends!

While I was down in beautiful Pensacola, I was also thrilled to participate in the third episode of the It's All About The Tools TV Show! I gave a demonstration on the SSIS 2008 data profiling tools - you can check it out here: http://channel9.msdn.com/shows/toolshed/Show-Episode-3-Its-All-About-The-Tools-TV-Show/. Russ Fustino and Stan Schultes put on a great show that I'm sure you're going to enjoy!

I really enjoy speaking at events and sharing information on data warehousing and the Microsoft business intelligence suite. I’ll warn you in advance that I prioritize engagements based on the shortest distance to water, sunshine, and community -- and not necessarily in that order :)

SQLTeach Vancouver 2009

2009-05-19T03:00:00.000-07:00

On June 8-12, I'll be attending and speaking for the DevTeach/SQLTeach Vancouver conference. I've written about SQLTeach before, and I have to say that it's one of my favorite conferences. It's a small group of attendees with speakers who are some of the biggest names in the field. You really get a great opportunity to learn from and hobnob with speakers such as Peter DeBetta, Bill Graziano, Kevin Kline, Joe Webb, and more! I know I'm looking forward to picking their brains on a few things.

A few highlights of the conference:

Monday night: Party with Vancouver IT community DevTeach Kickoff party
Weekend after: Alt.NET event - DevTeach attendees receive a special registration code
Preconferences: 3 preconferences on Silverlight, F#, and Agile Development
Keynote: Tim Huckaby will discuss "Your Development Happy Place"

Dimension ETL from One Source Table

2009-05-13T22:24:00.001-07:00

In an ideal world, the source system of your data warehouse has the exact information that you need to populate all of the fields in your dimensions. In a less than ideal world (also known as the real world), we need to cobble pieces of data together. You may come across one scenario where all of your dimension and fact information is in one large table. How do we handle this ETL in SQL Server Integration Services?

It is possible to load new records into your dimensions while loading your fact. One way would be to use a Lookup transformation to check for existence, and if the business key doesn't yet exist, insert that value, return the surrogate key, and go along your merry way. On the other hand, if you need to use those dimensions for other fact tables, you may decide to load only your dimensions first.

To load all dimensions from one table, we can utilize the Aggregate transformation in SSIS, which provides the capability to perform aggregations on columns in your data flow. The aggregations that are available to you include: Count, Count distinct, Sum, Average, Minimum, and Maximum. You also include a column to group the data. For the output that you're setting up, you can add as many columns to aggregate or group by that you need. For those of you familiar with T-SQL, this concept should be very similar to the GROUP BY clause.

When we're working with aggregations in SSIS, there are a few other things we should mention. The aggregate transformation will not pass through all input columns, only the columns that you specify in your settings. You can set comparison flags on your grouping columns on how to group the data together. I also use the "Ignore case" option to help get a list of case insensitive distinct values from the column.

Initially opening up the Aggregate transformation allows you to set up the columns you want aggregated and what you want to group by. In this scenario, we will use the Group By function for each column to get a distinct list of values; however, if we put all of them on this screen, we will still end up with duplicate values. Instead, we want to create multiple outputs that each contain a single Group By on the appropriate column.

There's a sneaky little button at the top of the designer window that toggles between Advanced and Basic modes. It defaults to Basic, which is why you only see one list right now. Push that button to toggle to Advanced and create a different output for each dimension you need to populate. Once you have a different output, you can perform a lookup against that dimension and only insert records that do not exist. We end up with a package that looks similar to this to load all of our dimensions:

The aggregate transformation has a little bit of a bad reputation, as well it should. It is an asynchronous component, so it creates a new buffer set when it runs, uses a huge amount of memory, and slows down your package execution time. See Kirk's great article about performance tuning SSIS (the aggregate transformation notes are applicable to both 2005 and 2008) for more information: http://www.simple-talk.com/sql/sql-server-2005/sql-server-2005-ssis-tuning-the-dataflow-task/.

This method may not be the best for your situation. It will load the source data twice and doesn’t take into account any slowly changing attributes. This should only be used if the situation calls for it. Hopefully, this will help you if you do fall into that situation!

Version used: SSIS 2008 SP1

SSIS Insert Statement Using an OLE DB Destination

2009-02-26T13:51:00.001-08:00

When building a SQL Server Integration Services package, many business scenarios call for inserting a record into a table if it doesn't already exist. The most commonly used database destination, the OLE DB destination, looks as though it can handle this through the SQL command Data access mode. Unfortunately, SSIS appearances can be deceiving...

In the OLE DB Destination, setting the Data access mode to SQL command causes a SQL command text window to appear. You can perform typical SSIS SQL statement actions, such as building the query through the Graphical Query Designer, importing the SQL from an external file, or parsing the query. Note that there is no option to specify query parameters on this display. You will not need to set query parameters because the OLE DB Destination uses this SQL statement to find the metadata of the desired insertion table. Even adding a WHERE clause to filter the data will not change the outcome of the result. All rows passed through the Data Flow pipeline are inserted into the destination table.

The SSIS OLE DB Destination uses the OLE DB Provider specified in the connection manager associated with that destination. The resulting SQL statements from the OLE DB Provider set to use the SQL command resemble the SQL statements from the Table or view Data access mode. I created an SSIS packages that inserts data into the AdventureWorksDW DimProductCategory table using both destination data access modes. The insert portions (taken from SQL Profiler) both match this code:

exec sp_cursor 180150003,4,0,N'[DimProductCategory]',@ProductCategoryAlternateKey=9,@EnglishProductCategoryName=N'Bikes',@SpanishProductCategoryName=N'Bicicleta',@FrenchProductCategoryName=N'Vélo'
go
exec sp_cursor 180150003,4,0,N'[DimProductCategory]',@ProductCategoryAlternateKey=10,@EnglishProductCategoryName=N'Components',@SpanishProductCategoryName=N'Componente',@FrenchProductCategoryName=N'Composant'
go

Note that sp_cursor, an internal API server cursor call that the OLE DB Provider uses, performs the insert.

The difference in these two methods is in the set up of the initial cursor. The SQL command method uses two statements:

declare @p1 int
set @p1=1073741825
declare @p5 int
set @p5=229378
declare @p6 int
set @p6=294916
exec sp_cursorprepare @p1 output,NULL,N'SELECT  ProductCategoryKey
    ,ProductCategoryAlternateKey
    ,EnglishProductCategoryName
    ,SpanishProductCategoryName
    ,FrenchProductCategoryName
FROM    DimProductCategory
WHERE   ProductCategoryKey <> 1',1,@p5 output,@p6 output
select @p1, @p5, @p6
go
declare @p2 int
set @p2=180150003
declare @p3 int
set @p3=2
declare @p4 int
set @p4=4
declare @p5 int
set @p5=-1
exec sp_cursorexecute 1073741825,@p2 output,@p3 output,@p4 output,@p5 output
select @p2, @p3, @p4, @p5
go

While the Table or view method uses just one:

declare @p1 int
set @p1=180150003
declare @p3 int
set @p3=2
declare @p4 int
set @p4=4
declare @p5 int
set @p5=-1
exec sp_cursoropen @p1 output,N'select * from [dbo].[DimProductCategory]',@p3 output,@p4 output,@p5 output
select @p1, @p3, @p4, @p5

You can solve the original business problem of only inserting records if it doesn't already exist by performing a lookup against your destination table, redirecting the rows that do not match, and inserting those rows using the OLE DB Destination. Depending on your particular scenario, other methods also exist that do not use a lookup. Whichever method you use, know that it will not entail a hand-written insert statement from an OLE DB Destination.

Version used: SQL Server 2005

SQL Down Under

2009-02-12T21:14:00.001-08:00

I had the honor of being invited to join Greg Low, fellow Solid Quality mentor, on an episode of SQL Down Under. On last week’s episode, we discussed the new features of SQL Server Reporting Services 2008 and best practices for all versions of Reporting Services. You can check out the episode here.

During the episode, Greg and I talked about reports that can be used to monitor Reporting Services. You can find these reports on CodePlex. Also, the design book that I mentioned is called Information Dashboard Design. I hope you enjoy the episode and these resources!

Analysis Services MDX Templates Exploration

2009-01-26T21:02:00.001-08:00

While looking in SQL Server Management Studio for something else entirely, I stumbled upon the Template Explorer. This window provides Analysis Services templates for querying data mining structures (DMX), querying cubes (MDX), and performing DDL (XMLA). I took a deeper look into the “MDX Queries” templates.

To view the Template Explorer, select the View menu > Template Explorer option. To see the Analysis Services template, select the Analysis Services cube option at the top of the window. Double-clicking any of the templates listed will then open a new query window containing the selected query. For example, the Basic Query will show the following:

Select <row_axis, mdx_set,> on Columns,
<column_axis, mdx_set,> on Rows

From <from_clause, mdx_name,>
Where <where_clause, mdx_set,>

You have two choices at this point in time: selecting the Query menu > Specify Values for Template Parameters option or replacing the phrases enclosed by angle brackets manually. The former choice opens a dialog box with all parameters listed to allow you to fill in the correct value; however, you will need to type in the full hierarchical structure by hand. If you’re anything like me, this is bound to cause a typo and a few frustrating minutes of letter-by-letter comparison. I prefer to modify the query directly by dragging the measure or dimensional attribute/hierarchy to my query window. Then I don’t need to worry about mistyping anything.

While these templates will not teach you how to write MDX, they are an easy way to save yourself some typing or looking up a particular keyword that you have forgotten. Looking over the XMLA queries, they appear to be more useful, as I am forever looking up the exact syntax for a particular XMLA query.

Versions: SQL Server 2005/2008

SSIS Designer Tip

2009-01-08T16:35:00.000-08:00

When designing a SQL Server Integration Services package, it can seem tedious to drag over each and every task and component from the toolbox to your Control Flow and Data Flow and connect all of the precedence constraints and pipelines. You can alleviate some of this by modifying the default Business Intelligence options within Business Intelligence Development Studio (BIDS).

Under the default settings, you can double-click any toolbox item and it will show up in your package designer with no connectors or specific place. To improve this, open up the Tools > Options... menu in BIDS. Then expand Business Intelligence Designers and Integration Services Designers. You will see Control Flow Auto Connect and Data Flow Auto Connect. If you check the option to "Connect a new shape to the selected shape by default", the drop down lists for specifying connector type and location are enabled in each menu. I prefer to use a Success constraint and add the new shape to the right of the selected shape, but you have a few options based on your design predilection.

Once these options are checked, double-clicking a toolbox item will add that item to the designer, using the options specified in the drop downs. You can of course change the type of constraint or move the item once it has been generated for you. Hopefully, this will save you a little bit of time when designing!

[Hat tip] I read about this option from Donald Farmer's great SSIS Scripting book.

Versions: Visual Studio 2005, Visual Studio 2008

Presentation References

2009-01-05T15:02:00.000-08:00

Over the past two months, I had the honor of presenting at many user groups and conferences. I wanted to put together some information for those who attended (and for those who were unable to attend!).

If you saw either "Building Reports in SQL Server Reporting Services 2008" or "New Features of SQL Server Integration Services 2008" and are excited to get your hands on a SQL Server 2008 instance, but your company won't upgrade... you can download a free trial/evaluation version from Microsoft. Maybe you can even show your boss some of the things you learned from my presentation!

@AndyLeonard: “You got your SSIS in my Twitter!”
@JessicaMMoss: “You got your Twitter in my SSIS!”
[Previous dialogue shamelessly stolen from Brent Ozar's blog because it's a perfect lead-in to...]

For those of you who are not familiar with Twitter, it's a micro-blogging tool / social network platform that has completely taken off over the past year. Andy Leonard invited me to join in his "once-a-decade" great idea, using SSIS to write to Twitter! You can download the current version of the SSISTwitterSuite from CodePlex.

PASS puts on the largest SQL Server and Business Intelligence conference. If you attended, you can download the slides for both "SSIS Scripting" and "Building an SSIS Management Framework" (which I co-presented with Rushabh Mehta) by logging into the summit site. As an attendee, you can also purchase a DVD of all of the sessions. If you are interested in the SSIS Framework discussed during the presentation, contact Solid Quality Mentors.

This was my second time speaking at the DevTeach/SQLTeach conference in Canada, and I was thrilled to speak on SSIS, SSRS, and Data Mining in Office 2007! If you attended the conference, you can sign in and download the slides and custom component code. If you have an inkling to work with the data mining tools, check out http://www.sqlserverdatamining.com/.

Finally, the SQLPASS BI SIG Data Mining webcast was recorded. Once it is available, you should be able to listen to it at:
http://www.sqlpass.org/Community/SIGs/BusinessIntelligenceSIG/tabid/82/Default.aspx.

I look forward to speaking at more events in the future!

Jessica's BI Tour

2008-11-02T15:23:00.000-08:00

I'll be flying up and down the east coast over the next two weeks, sharing information about the SQL Server Business Intelligence suite. I do hope you can make it to one of the sessions!

11/3 - Tampa Bay SQL Server Business Intelligence User Group - SSRS 2008
11/6 - Sarasota SQL Server Users Group - SSIS 2008
11/11 - Orlando PASS - SSRS 2008
11/13 - Richmond SQL Server Users Group - SSRS 2008
11/15 - Raleigh Code Camp - SSRS 2008

Manipulating Excel Spreadsheets in SSIS

2008-10-21T01:11:00.000-07:00

Tom, an attendee at last weekend’s SQLSaturday Olympia, asked me how to refresh a spreadsheet from within SQL Server Integration Services. My first thought was to turn on the connection’s “Refresh data when opening the file” option in the spreadsheet itself and avoid the situation entirely; however, this may not always be a viable solution. Here are the steps to perform the refresh from within an SSIS package.

First, ensure that Microsoft.Office.Interop.Excel is registered in the GAC. If not, install the 2007 Microsoft Office system Primary Interop Assemblies. This will need to be done on any machine where you plan on running this package.

Next, create a script task in your SSIS package that contains the following code (include your spreadsheet name):

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.Office.Interop.Excel

Public Class ScriptMain

Public Sub Main()
Dts.TaskResult = Dts.Results.Success

Dim excel As New Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook

wb = excel.Workbooks.Open("C:\\TestExcelSS.xlsx")
wb.RefreshAll()
wb.Save()
wb.Close()

excel.Quit()
Runtime.InteropServices.Marshal.ReleaseComObject(excel)

End Sub

End Class

You'll see error squiggles, but don't worry about them because they will disappear in just a minute. Save and close your package. In your Solution Explorer, right click on the package and select ‘View Code’.

In the resulting XML, change the Build Settings ReferencePath property to:
"C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.ScriptTask\9.0.242.0__89845dcd8080cc91\;C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.ManagedDTS\9.0.242.0__89845dcd8080cc91\;C:\Windows\assembly\GAC\Microsoft.Office.Interop.Excel\12.0.0.0__71e9bce111e9429c\"

Also change the Build References to include:
<Reference
Name = "Microsoft.Office.Interop.Excel"
AssemblyName = "Microsoft.Office.Interop.Excel"
/>

Save the XML, and reopen the package. Open the script task and select ‘Save’. This will compile the code, and now you can run your package.

When working with COM references, you can use the script task GUI to add the reference by adding the desired component to the .NET framework folder. I could not find Microsoft.Office.Interop.Excel.dll on my machine to move to the framework folder, which is why we added the reference through the XML.

As Douglas Laudenschlager notes, writing server-side code to access client-side Office is unsupported. Please take these possible problems under advisement and code as necessary. You have been warned. :)

Update (11/12/08): Added last two lines to code to stop Excel process.

Versions: Microsoft Office 2007, SQL Server 2005 SP2

IIS 7.0 Role Service SSRS Requirements

2008-10-12T22:28:00.000-07:00

Using the default IIS 7.0 installation on Windows Server 2008 does not include all of the role services necessary to install SQL Server Reporting Services 2005. I found this command in an online forum when looking for a list of all the needed role services. (By the way, I apologize, but I can no longer find the post to link back to. If it was you who wrote it, please post a comment with a link back to that post!)

Here is the command. I hope it helps you as much as it helped me!

ServerManagerCmd.exe -i Web-Server Web-Asp-Net Web-Http-Redirect Web-Windows-Auth Web-Metabase Web-WMI

Version: SQL Server 2005

Microsoft MVP

2008-10-08T22:31:00.000-07:00

On October 1, 2008, I received notification that I was awarded as a Microsoft Most Valuable Professional. According to Microsoft, MVPs are "exceptional technical community leaders from around the world who are awarded for voluntarily sharing their high quality, real world expertise in offline and online technical communities" and quite a few other comments that make me blush. It's always nice to be rewarded for doing what you love to do.

MVPs are awarded for their past contributions, but the benefits are applied to the upcoming year. These benefits include access to technical resources and the opportunity to learn from and offer thoughts to Microsoft. I believe the information I provide will be best if it comes from everyone, so I ask you to let me know if you're having any issues or concerns with SQL Server. On a final note, if you have a user group that would enjoy learning about SQL Server BI, or if you have a technical question, or if you just feel like geeking out about SQL Server, please contact me at jmoss at solidq dot com.

Thank you.

Richmond and Olympia

2008-10-03T05:28:00.000-07:00

That would be Richmond in Virginia and Olympia in Washington. :)

I'll be speaking at the Richmond Code Camp 2008.2 on October 4 about custom code in Reporting Services. Richmond is my local user community, and I can't say enough good things about the crew that organizes and speaks in that area. I hope to see some familiar faces during the day.

The following Saturday (October 11), I'll be presenting at Olympia SQLSaturday about Reporting Services 2008. I enjoy SQLSaturday events because they are all about SQL Server! This one includes talks on Business Intelligence, TSQL, and internals.

Both events have a great speaker lineup and should be a lot of fun. Hope to see you there!

MDX Query Returns Duplicate Values

2008-09-29T02:08:00.000-07:00

When someone is querying a new Analysis Services cube, there is one mistake that I often see made: trying to use a dimension that does not relate to the desired measure group. For example, when looking at the Adventure Works cube, if you try to use any of the Reseller measures with the Customer dimension, you will receive the same value across the Customer members. Because there is no relationship defined in the cube, the MDX query will use the default member for that particular attribute, which, in this case, is the ‘All’ member. It would look something like this:

This type of situation typically goes away once a user has learned the new data model, but Management Studio 2008 provides a neat dropdown list to help initially avoid the rogue query. On the Metadata tab, under the label Measure Group, you can select the measure group you are targeting. The GUI refreshes to only show the dimensions and measures that are associated with that measure group. If we select the ‘Reseller Sales’ measure group, we can see the way the new display looks, including no Customer dimension!

A user can still use the inappropriate dimension, but hopefully this will prevent the issue right from the start. Happy querying!

Version: SQL Server 2008 RTM

PASS Community Summit 2008

2008-09-25T22:54:00.000-07:00

This will be my first year attending the PASS Summit as a speaker rather than an attendee. I will be joining Rushabh Mehta to present a spotlight session entitled "Building an SSIS Management Framework". The PASS Summit is a great conference that focuses on SQL Server and Business Intelligence. Because it is in Seattle, there will be a ton of people from the SQL Server Developer Team and SQLCAT. If you have any questions about SQL Server, this is the place to be.

You still have a few weeks left to sign up. Solid Quality Mentors is also offering a discount code applicable to the cost of the conference. You can get that code from here: http://www.solidq.com/na/pass.aspx. So now you have no excuse left to not sign up!

Display Top N Rows - Alternate Method

2008-09-14T23:23:00.000-07:00

In a previous post, I showed how to dynamically limit the number of rows shown in a table or matrix. This methodology used a filter on the table to only show the desired number of rows. The disadvantage with this method is that all rows are returned, even if only a small subset of rows is actually desired.

To alleviate this problem, you can use a query parameter to restrict the number of rows returned at the query level. This does introduce a different problem. Every time the report is run with a different Top N parameter, the query will be rerun to bring back the correct number of rows. You will need to determine which method is optimal for your situation.

Here are the steps to create a sample report to show this new method.

1. Add a report parameter named “NumberPpl” of data type integer.

2. Create a DataSource connecting to the AdventureWorksDW database and a DataSet with the following query:
SELECT TOP(cast(@TopN as int)) DimEmployee.FirstName, DimEmployee.LastName, SUM(FactResellerSales.SalesAmount) AS SalesAmount
FROM DimEmployee INNER JOIN
FactResellerSales ON DimEmployee.EmployeeKey = FactResellerSales.EmployeeKey
WHERE (FactResellerSales.OrderDateKey = 20011001)
GROUP BY DimEmployee.FirstName, DimEmployee.LastName
ORDER BY SUM(FactResellerSales.SalesAmount) DESC
Assign the @TopN query parameter to use the @NumberPpl report parameter.

Note that this differs from the original query in two ways:
A. Includes the TOP clause to restrict the rows
B. Includes a descending order by for the SalesAmount to ensure the highest SalesAmounts are shown

Also note that the TopN query parameter is cast to an integer. This is because all query parameters are created as string and the TOP clause expects an integer.

3. Add a table to the body of the report with the data fields of FirstName, LastName, and SalesAmount. Sort by the column SalesAmount from ‘Z to A’. This sort is now only for display purposes and is not necessary to return the correct data.

4. Preview the report, modifying the value for the parameter NumberPpl to see the number of rows change. It should look similar to the report below and the reports using the first method.

Version: SQL Server 2008 RTM

Upcoming Events

2008-09-09T13:06:00.000-07:00

I have an exciting week coming up! On Wednesday evening, I will be speaking at the Rockville .NET User Group on SSIS Tips & Tricks for both SQL Server 2005 and 2008. If you're in the area, I hope you'll stop by.

On Friday night, the Northern Virginia Girl Geek Dinner is having their inaugural dinner. Hosting by Viget Labs, sponsorship by Microsoft, and a presentation by Dr. Joanne McGrath Cohoon will make for a great evening! I will be there, helping to ensure everything runs smoothly. The sign up deadline was just extended through Wednesday, so sign up now at http://novagirlgeekdinner.eventbrite.com/!

SnippetCompiler

2008-08-17T21:12:00.000-07:00

Extensibility in SQL Server Integration Services and SQL Server Reporting Services is achieved by writing custom code inside your package or report. SSIS provides a great interface by using Visual Studio for Applications (2005) or Visual Studio Tools for Applications (2008), lightweight versions of the Visual Studio IDE. When you create a script task or component, you can write code using intellisense and error squiggles. SSRS, on the other hand, does not provide any IDE for writing custom code. If you want to create VB functions, you have a notepad-like window without colors, intellisense, or any error handling.

In comes SnippetCompiler, an application that allows you to write and compile snippets of code. You can download the executable here: http://www.sliver.com/dotnet/SnippetCompiler/. The current version allows code in both VB.NET 2.0 and C# 2.0. A .NET 3.5 version is in Alpha release and can be downloaded from the same location. Keep in mind that if you minimize the application, it becomes an icon in the system tray instead of showing up on the task bar.

I use this application when I write all of my custom code, especially in Reporting Services. It allows me to ensure my syntax is correct, as well as keep consistent formatting before copying the code into my package or report. I hope you find this tool useful as well!

Display Top N Rows

2008-08-14T00:44:00.000-07:00

In SQL Server Reporting Services, you can dynamically limit the number of rows that are displayed in a table or a matrix. Here are the steps to create a sample report showing this.

1. Create a DataSource connecting to the AdventureWorksDW database and a DataSet with the following query:
SELECT DimEmployee.FirstName, DimEmployee.LastName, SUM(FactResellerSales.SalesAmount) AS SalesAmount
FROM DimEmployee INNER JOIN
FactResellerSales ON DimEmployee.EmployeeKey = FactResellerSales.EmployeeKey
WHERE (FactResellerSales.OrderDateKey = 20011001)
GROUP BY DimEmployee.FirstName, DimEmployee.LastName

2. Add a report parameter named “NumberPpl” of data type integer.

3. Add a table to the body of the report with the data fields of firstName, lastName, and salesAmount. Sort by the column salesAmount from ‘Z to A’.

4. Set the Filters option of the table as shown below. (This is where the real work is happening!)

5. Preview the report, modifying the value for the parameter NumberPpl to see the number of rows change. It should look similar to the reports below.

Version: SQL Server 2008 RC0

WorldMaps

2008-08-08T00:08:00.000-07:00

Those of you who visit my blog directly may notice a new image labeled "WorldMaps" on my right-hand side bar. WorldMaps, introduced to me by Andrew Duthie, stores statistics about visitors to any website. By signing up for an account and adding the tracking device on your page, you can keep track of the number of hits, different IP addresses, visitor locations, and more. For an example of what WorldMaps can provide, click on my world image, which will link to a page with my statistics.

For other statistics of interest, I also use Feedburner. While I'm happy with the information they've provided, there is something about seeing my visitors’ locations visually that speaks to my Business Intelligence side ;) I definitely recommend you take a look at WorldMaps!

Overlapping SSRS Report Items

2008-08-06T01:45:00.000-07:00

After converting your SQL Server Reporting Services reports from 2005 to 2008, you may notice a difference in the way the report looks. One possible reason for this difference is the change in the way overlapping report items are rendered.

In both SSRS versions, the message in the Output window is similar to: "[rsOverlappingReportItems] The text box ‘Textbox2’ and the text box ‘Textbox1’ overlap. Overlapping report items are not supported in all renderers." In Reporting Services 2005, these textboxes would display in an overlapped fashion, often causing the text within both textboxes to be unreadable. In Reporting Services 2008, however, the second textbox shifts, so that it appears the textboxes are vertically next to each other. This allows you to always read the text in both textboxes.

The new overlap rendering functionality is included in the "Soft Page-Break Renderers", including the Report Preview window, HTML, MHTML, Word, and Excel. Taking a look at the report in the Report Preview pane will give you a pretty good idea of how the report will look in HTML. I did not find this to be the case for the "Hard Page-Break Renderers", including Acrobat (PDF) format.

If you do have overlapping report items, the item to the "front" of the report will be rendered above the item to the "back" of the report. You can take advantage of this by right-clicking on the item and selecting either the option "Bring to Front" or "Send to Back" for the desired result.

For more information on how rendering works in SSRS 2008, I would recommend visiting this page: http://technet.microsoft.com/en-us/library/bb677573(SQL.100).aspx

Versions: SQL Server 2005 SP2, SQL Server 2008 RC0

Reporting Services 2008 Configuration Mistake

2008-07-31T00:32:00.000-07:00

To start working with the management side of SQL Server Reporting Services 2008, I decided to set up a report server and report manager. Unfortunately, I made a mistake while setting up my configuration that left me a little perplexed. Here are the steps I took to cause, track down, and solve the issue.

Problem:
I began by opening the Reporting Services Configuration Manager from the Start Menu. I clicked through each of the menu options and accepted the defaults for any question with a warning symbol, since warning symbol typically designate an action item. After two minutes, all of the warning symbols had disappeared, and I was ready to begin managing my report server. Unfortunately, opening up a browser and trying to open up the report manager resulted in the dreaded "The report server has encountered a configuration error. (rsServerConfigurationError)" message.

Sherlock-ing it:
I put on my sleuthing hat and went to the log file directory: C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\LogFiles. Under the latest ReportServerService*.log, there was the following error: "Message: No DSN present in configuration file". Looking at the file: C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\rsreportserver.config proved the error message true, as right at the top of my log file was the empty Dsn tag.

Looking in Books Online under the RSReportServer Configuration article, I discovered that the Dsn property contains a connection to the report server database. That's odd, I don't remember creating that connection...

Solution:
I returned to the scene of the crime, the Configuration Manager. Sure enough, the third warning-less menu option allows you to set up the connection to either an existing report server database or create a new one for this instance. I filled in the appropriate information, and took a look back at the configuration file. This time, the Dsn tag contains a beautifully encrypted blob of information. The report manager works, and I am ready to manage my reports! The moral of this story is to read the directions before clicking through configuration managers, don't trust warning icons or lack thereof, and don't forget that Reporting Services needs to know what database to use. :)

Version: SQL Server 2008 RC0