Jessica M. Moss

Sitting on the beach in Pensacola

noreply@blogger.com (Jessica M. Moss) — Thu, 09 Jul 2009 03:12:00 +0000

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

noreply@blogger.com (Jessica M. Moss) — Tue, 19 May 2009 10:00:00 +0000

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

noreply@blogger.com (Jessica M. Moss) — Thu, 14 May 2009 05:24:00 +0000

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

noreply@blogger.com (Jessica M. Moss) — Thu, 26 Feb 2009 21:51:00 +0000

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

noreply@blogger.com (Jessica M. Moss) — Fri, 13 Feb 2009 05:14:00 +0000

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

noreply@blogger.com (Jessica M. Moss) — Tue, 27 Jan 2009 05:02:00 +0000

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

noreply@blogger.com (Jessica M. Moss) — Fri, 09 Jan 2009 00:35:00 +0000

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

noreply@blogger.com (Jessica M. Moss) — Mon, 05 Jan 2009 23:02:00 +0000

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

noreply@blogger.com (Jessica M. Moss) — Sun, 02 Nov 2008 23:23:00 +0000

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

noreply@blogger.com (Jessica M. Moss) — Tue, 21 Oct 2008 08:11:00 +0000

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

noreply@blogger.com (Jessica M. Moss) — Mon, 13 Oct 2008 05:28:00 +0000

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

noreply@blogger.com (Jessica M. Moss) — Thu, 09 Oct 2008 05:31:00 +0000

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

noreply@blogger.com (Jessica M. Moss) — Fri, 03 Oct 2008 12:28:00 +0000

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

noreply@blogger.com (Jessica M. Moss) — Mon, 29 Sep 2008 09:08:00 +0000

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

noreply@blogger.com (Jessica M. Moss) — Fri, 26 Sep 2008 05:54:00 +0000

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

noreply@blogger.com (Jessica M. Moss) — Mon, 15 Sep 2008 06:23:00 +0000

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

noreply@blogger.com (Jessica M. Moss) — Tue, 09 Sep 2008 20:06:00 +0000

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

noreply@blogger.com (Jessica M. Moss) — Mon, 18 Aug 2008 04:12:00 +0000

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

noreply@blogger.com (Jessica M. Moss) — Thu, 14 Aug 2008 07:44:00 +0000

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

noreply@blogger.com (Jessica M. Moss) — Fri, 08 Aug 2008 07:08:00 +0000

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

noreply@blogger.com (Jessica M. Moss) — Wed, 06 Aug 2008 08:45:00 +0000

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

noreply@blogger.com (Jessica M. Moss) — Thu, 31 Jul 2008 07:32:00 +0000

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

Where is my Data Tab?

noreply@blogger.com (Jessica M. Moss) — Mon, 21 Jul 2008 09:32:00 +0000

I’ve recently had the pleasure of diving into Reporting Services 2008 RC0, and I’m amazed at the difference in the design environment from SSRS 2000/2005! To give a comparison of the designer, I took a look at the BIDS Reporting Services project. Overall, I think this version is more slick and more user-friendly (once I find everything again :) ) than its predecessor.

To begin, there is no longer a Data tab in the main frame of the design environment. You can find your DataSets and Fields on the “Report Data” window on the left side of your designer. If you lose your Report Data window, click the View menu > Report Data, or just select Ctrl-Alt-D. Also joining the new Report Data family are Report Parameters, originally found on the Report menu, and Fields, originally on its own window.

A look at the new RS would be remiss without mentioning the new Tablix control. From the toolbox, you still select a Table, Matrix, or List control, but these are templates for the Tablix control that provide you with the expected layout. As soon as that control has landed on your Design window, the properties and group menus look the same. In fact, the menu option to view properties is labeled “Tablix Properties…”. It is easy to see how the rows and columns are utilized from the icons displayed to the left or top of the textboxes.

Instead of Groups being included in the properties window of each control, they are now displayed on the report itself. This option can be toggled by right-clicking on the report and selecting View > Grouping. By selecting the desired control in your design window, you can immediately see the groups for that control.

All of the property windows have been overhauled. Is it just me, or do the new property windows remind you of the Dundas Controls property windows?

I’ll be posting more thoughts and trials on Reporting Services 2008 as I dig deeper into the new system!

Version: SQL Server 2008 RC0

Software Development Meme

noreply@blogger.com (Jessica M. Moss) — Wed, 09 Jul 2008 05:05:00 +0000

I've been tagged with the Software Development meme, courtesy of Andy Leonard. And, we're off!

How old were you when you first started programming?
I was 13 years old in the eighth grade. I took an Introduction to Programming class with an amazing teacher. Thanks to Mr. Creasy, I've been hooked ever since.

How did you get started in programming?
My father was a computer programmer, so I guess I come by it naturally. I used to help my teachers work their classroom computers in elementary school, and then tried my hand at programming in middle school.

What was your first language?
BASIC. Not VB.Net. Not Visual Basic. Just BASIC. How many Millennials can say that? ;)

What was the first real program you wrote?
Hello world... Or something along those lines that was pretty simple.

What languages have you used since you started programming?
BASIC, C++, Java, C#, VB.NET, ASP.NET, T-SQL, MDX... others I'm sure...

What was your first professional programming gig?
My first job was a consultant at a custom software development firm. I was fortunate enough to dabble in all sorts of different languages, but the first project that was entirely my own was creating Reporting Services reports to analyze sales data. Do you think they knew something about where I'd end up? ;)

If you knew then what you know now, would you have started programming?
Definitely. I originally fell in love with programming because of the logic. A + B = C and such... With time, I realized that programming is more of an art. Who knows what my next revelation on programming will be?

If there is one thing you learned along the way that you would tell new developers, what would it be?
You can't know everything. Always learn and always ask questions. If you think you know everything and you have nothing left to learn, find another job.

What’s the most fun you’ve ever had ... programming?
Hmm… college all nighters with copious amounts of mountain dew and loud music… Working on teams of people where everyone just meshes and the final program is just beautiful… That final “Ah ha” moment when something just clicks… Oh, I can’t decide!

Who are you calling out?
Trying to find people on my blog list that haven't yet been tagged!

Rushabh Mehta
Wes Dumey
Andy Warren

Thoughts on Mentoring

noreply@blogger.com (Jessica M. Moss) — Tue, 24 Jun 2008 05:19:00 +0000

I just finished the book The Elements of Mentoring by W. Brad Johnson and Charles R. Ridley at the suggestion of Andy Warren in his blog series on mentoring. While I’ve been fortunate enough in my career to have many people have advised, taught, and coached me, I’ve never been involved in a formal mentor-protégé relationship. After reading all of the work involved, I’m surprised anyone would want to! That statement is partly tongue-in-cheek because it is apparent after reading this book that a successful pairing can be mutually beneficial. Here are just a few thoughts on the book.

The Elements of Mentoring starts by discussing how to be a good mentor, namely that it is important to have both the right personality and the right skill set. It struck me as I read that for most of the advice, I could replace mentor/protégé with any relationship pair, i.e. parent/child, person/spouse, etc. For example, the important of bolstering the protégé’s confidence is a pillar of good parenting. The next sections of TEoM show all phases of a mentoring relationship from picking the protégé/mentor through ending the mentorship. One recurring theme in this section is to ensure that both parties are fully aware of what they are agreeing to by entering into this relationship. I would recommend that both people start out by reading this book. It would provide a common base from which to build schedules, expectations, and timelines.

As expressed in TEoM, I think it’s important to realize that being involved in a mentoring relationship is not for everyone. Just because someone is intelligent and well-respected in their field does not mean they will make a good mentor. Just because they are a good mentor does not mean they will be compatible with every protégé’s goals and personality. Just because a mentoring relationship starts out well does not mean that it will end well. Barring the difficulties in making a successful match, if it can be done, mentoring or being mentored is definitely worth it. Getting involved in a mentoring relationship from either the protégé or mentor side can be a very fulfilling role and can create a relationship that will last long past the end of the mentorship.