Thursday, December 3, 2009

SQLSaturday Richmond 2010

Do you have a neat T-SQL trick that you'd like to show off? Is your idea of a good time figuring out Analysis Services hierarchies? Have you solved world peace using SQL Server? If so, you should submit an abstract to speak at SQLSaturday Richmond!

We are currently accepting speaker submissions. You can submit an abstract by going to the website: http://www.sqlsaturday.com/30/callforspeakers.aspx! Please submit by 12/12/2009.

If you're not interested in wowing people with your extensive SQL ninja skills, we hope you will consider attending this amazing SQLSaturday event first time ever in Richmond, VA. Obligatory marketing blurb with further information below:

Join the Richmond developer community for a free, one-day training for SQL Server professionals, developers, DBAs, and even people who don't know how to spell SQL.

When: January 30, 2010
Where: ECPI College of Technology
4305 Cox Rd
Glen Allen, VA

Admittance to this event is free; all costs are covered by donations and sponsorships. Please Register soon as seating is limited, and let friends and colleagues know about the event. For more information, visit http://www.sqlsaturday.com/ and select “SQLSaturday #30 - Richmond 2010”

I won't be speaking this time around, but I will be there to help things run smoothly! I hope to see all of you there :)

Tuesday, November 24, 2009

Installing and Configuring SSRS in SharePoint Integrated Mode

To put it bluntly, configuring SQL Server Reporting Services 2005 or 2008 to run in SharePoint integrated mode with Microsoft Office SharePoint Server 2007 is a pain in the behind.  The setup of the numerous moving pieces seems to get confused between two sets of configurations, two sets of security, and two sets of databases. (I would get confused with all those pieces too! ;) )  Let's take a look at some highlights of the installation steps and a few warnings that may help you in your environment.

Components
It makes sense that you would need Reporting Services (2005 with SP2 or 2008) and SharePoint (WSS 3.0 or MOSS 2007) in some fashion on your servers.  The secret ingredient to tie everything together is actually an extra add-in that must be installed on your SharePoint server.  This is a free download that can be found here (2005) or here (2008).

Security
Once you have installed all components, you must complete a series of screens in the SharePoint Central Administration tool.  The screens tell SharePoint where the report server resides and sets up the security so that the two pieces can talk to each other.  You need to be sure that you have a user account that has access to both server/databases to facilitate this.

Warning, warning!
Here are a few road bumps that I have run into in the past:

  • When you configure your SSRS databases, ensure that you have selected the option to create the database in SharePoint Integrated mode.  Each mode creates its own type of database and never the twain shall meet.
  • Once you get your environment set up, make sure Reporting Services reports are stored in the Default zone.  Otherwise, the reports will not render.
  • Don't mix your SharePoint and Reporting Services (2005) applications in IIS.  SharePoint should have sole ownership of its virtual, and SSRS's setup should not be disturbed.

Do as I say, not as I do
To install and configure your environment, I highly recommend that you follow the steps in the EXACT order as written in the Microsoft SQL Server Reporting Services (SSRS) Installation/Configuration Guide for SharePoint Integration Mode whitepaper.  While this document is written for SSRS 2005, it very closely aligns with the 2008 method as well.  Good luck with your setup!

Saturday, October 31, 2009

PASS Community Summit 2009

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.

Thursday, October 15, 2009

Fall 2009 Conference Odyssey

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

Thursday, October 8, 2009

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

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:

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!

Wednesday, July 8, 2009

Sitting on the beach in Pensacola

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

Tuesday, May 19, 2009

SQLTeach Vancouver 2009

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"

Register soon to get a great deal - the conference is right around the corner. I look forward to seeing you there!

Wednesday, May 13, 2009

Dimension ETL from One Source Table

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

Thursday, February 26, 2009

SSIS Insert Statement Using an OLE DB Destination

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

Thursday, February 12, 2009

SQL Down Under

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!

Monday, January 26, 2009

Analysis Services MDX Templates Exploration

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

Thursday, January 8, 2009

SSIS Designer Tip

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

Monday, January 5, 2009

Presentation References

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!