tim laqua dot com

A few days ago, one of our SSIS packages that maintained a Type 1 Slowly Changing Dimension (SCD) of about 1 million rows crept up to 15 minutes of runtime. Now this doesn’t sound too bad, but this is part of our hourly batches, so 15 minutes is 25% of our entire processing window. The package was using the Slowly Changing Dimension Wizard transformation – we were doing the standard OLEDB Source (which basically represented how the SCD “should” look) and then sending it to the SCD transform and letting it figure out what needed to be inserted and updated. One option was to switch to lookups instead of the SCD wizard to speed things up, maybe even some fancy checksum voodoo for the updates (see http://blog.stevienova.com/2008/11/22/ssis-slowly-changing-dimensions-with-checksum/ for an example). Then after thinking about it a little more – why are we sending a million rows down the pipeline every hour? We know only a small percentage of these are new – and another small percentage needs to be updated. Well, we can just write a quick SQL query to get us just those sets and the package would be much more efficient!

Wait a tick – why would we give the rows to SSIS if all it is going to do insert one set and update the other? Let’s just do it all in T-SQL: (continue reading…)

First of all – I know, don’t do this. The application that put the URL in the column in the first place is MUCH better at handling URLs and ideally, you would just add columns for the GET variables you’re after and have the application put those in. Parsing them out after the fact from a VARCHAR field is insane.

So now we’re here and we need to do that thing that we said we shouldn’t do. My approach is to use a Table Valued Function that only returns one column – the value of the variable or NULL if it can’t find the variable in the querystring. (continue reading…)

I have always felt that the traditional approach to getting Agent Job status by creating a table and populating it with output from xp_sqlagent_enum_jobs was incredibly crude. So I poked around a bit and came up with a solution using a Table Valued Function that queries sysjobactivity to determine the status of a given job. The reason I use a Table Valued Function is because we have a few tables that contain a list of jobs that need to be started at certain points in a given process (and they may or may not already be running) – with the TVF, you can simply CROSS APPLY the function and bob’s your uncle. (continue reading…)

I run this one pretty frequently when we need to figure out what procs are killing a complex ETL process and what exactly about them is making the server cry. So basically, if it’s on the development server, I’ll do a DBCC FREEPROCCACHE and a DBCC DROPCLEANBUFFERS, run the entire set of ETLs, then run this query and then dig deeper in to the query plans that look suspect (high *Scan counts usually, sometimes lots of Hash Matches or Merge Joins). On a production server, the clearing of the proc cache and dropcleanbuffers can be problematic so I’ll often just run the query after a scheduled ETL run. If you want to see the query plans mapped out visually, click on the query_plan value and SSMS will open up the XML. Then save that XML file as a .sqlplan file. Once you have that, close the XML and then open the .sqlplan file. (continue reading…)

First of all, I’m a high handicapper. Currently I float around 20-25. I do NOT count penalty strokes when my tee shot goes out of bounds when practicing (I count all other penalties, just not that one). I don’t count those because they inflate my handicap. When practicing, I’ve got my driver out every hole except for par 3 holes simply because I can – not because it’s the smart play. If you slice 50% of the time and the right side of the teebox is out of bounds (and the subsequent 250 yards are also out of bounds to the right) – the intelligent thing to do is take out your 4 or 5 iron and put it on the fairway. That’s exactly what you would do during competition play. But when practicing, every time, without fail, I’ll sacrafice 2-3 balls to the right lateral hazard. Technically, that’s 4 strokes – tee out of bounds, drop, tee out of bounds, drop, etc. But that shouldn’t be factored in to my handicap. Basically, if you take a risk during practice that you wouldn’t take during competitive play – I see no reason to rack up stroke and distance penalties. You’re better off (in my opinion) just saying to yourself “lesson learned – don’t do that,” dropping another ball and practicing the safe and logical play.
(continue reading…)

While working with some SSIS logging, it occured to me that there’s really no good way to globally apply the same logging to all packages on your server. For our production SSIS server, it would make sense to log all OnError events to the Application Event log. Then, whatever event log monitoring app you use can notify you of the package error (with details!). Thus, this app was born – an app to remove pre-existing LogProviders and add in the intended LogProvider and options.

This is really just an example of the implementation that worked for my purposes – I’m sure some of you would prefer to log elsewhere. This should be enough to get you going. Please drop a line in the Comments if you post alternative approaches.

Usage

SSISForcedLogging.Console.exe "Z:\SSIS Packages"
or
SSISForcedLogging.Console.Exe "Z:\SSIS Packages\Package1.dtsx"

(continue reading…)

In most businesses, the name of the game is giving the customer what they want when they want it. Putting the right opportunities in front of them at the right time. At the golf course (the elitist ones), the game changes quite a bit. Take the Pro Shop for example – everything is overpriced, at least 20% above what you could get it for at TGW or GolfGalaxy. Why? Because if you’re in need of something before a round and you remember just as you’re standing in the pro shop, you’re certainly not going to hop in your car and head over to GolfGalaxy – you just buy it there, you’re trapped more or less. Don’t even bother trying to work with the pro shop guys on the price either, they could care less. Which brings me to my second point – the staff.
(continue reading…)

Originally, we were charged with figuring out how to display SSAS cube measure descriptions via ToolTip in Excel 2007. If that’s your plan, forget it – after some reading up on the interwebs, it appears that Excel doesn’t even request the Description property. Additionally, if you want to add a description to Calculated Members, you have to hack it in (yuck).

So we went with a simple, albeit relatively crude (but effective), alternative – implementing a URL action for Cells so users can easily link out to a definition of the measure they’re looking at.

Create a new action in your cube (Open up the cube definition, Actions tab) and configure similar to this:

Name: View Member Definition
 
Action Target
    Target Type: Cells
    Target object: All cells
 
Action Content
    Type: URL
    Action expression: "http://i.domain.com/doc/Defs.aspx#" 
                       + [Measures].CurrentMember.Name
 
Additional Properties
    Invocation: Interactive
    Description: View Member Definition
    "View Definition Of " + [Measures].CurrentMember.Name + "..."
    Caption is MDX: True

When you’re finished, it should look something like this:

For a possible way to implement the aforementioned Definitions.aspx, check out http://timlaqua.com/2009/03/scrolling-to-and-highlighting-anchor-target-via-javascript/ – which describes an early endpoint we used for this project.

I implemented a SSAS cube action to link to a SharePoint page (URL Action) with the name of the measure contained in the cell the user fired the action from. The theory here is to have a page that contains a list of definitions for all measures (both real and calculated) in the cube. As we were flushing out this implementation, it was suggested that the page should scroll to the specified measure and highlight it in some way. The implementation of this using the CSS :target pseudo class is pretty straightforward – however we’re a Microsoft shop and we absolutely have to support Internet Explorer 7 and Internet Explorer 8, so that’s out.
(continue reading…)

So, you have a bunch of errors from some application between 2:00 AM and 3:00 AM on the day that Daylight Savings Time occured, huh? This year, it was between 2009-03-08 02:00:00 and 2009-03-08 02:59:59 – and we had one of those applications. As Urchin 6 was trying to parse IIS logs from 2:00 AM to 3:00 AM this past Sunday, it failed and continued to fail over and over again with the error “Incorrect datetime value” when it tried to insert the non-existant date in to the MySQL 5 (5.0.6.7 to be exact) database. MySQL was configured with a timezone of SYSTEM:

SELECT @@time_zone

Running under Windows Server 2003 Web Edition, MySQL used the correct timezone and appropriately skipped ahead at 2AM to 3AM – those times don’t exist here. Unfortunately, one of the IIS servers wasn’t configured to adjust the time for Daylight Savings and made a big pile of log entries between 2:00 AM and 3:00 AM (again, a non-existant time).

ERROR: (...) Failed to query the database
DETAIL: Incorrect datetime value: '2009-03-08 02:56:15'
        for column '...' at row 1

First, properly configure IIS so it records time the same way the database expects it to. The only way to get around this situation where you have essentially corrupt IIS log files is to fix the time entries in the log file and associate them with a valid time (i.e. an hour before or an hour after). Technically, you also need to shift all of the subsequent logs forward one hour as well, as IIS was recording the wrong time (-1 hour). Then let ‘er rip.