tim laqua dot com Thoughts and Code from Tim Laqua

10Feb/130

Hanging The TV

One morning as we were eating breakfast, Lindy (my wife) looked over to the other room where we had a TV sitting on a sofa table and commented that it looked "terrible" because of all the wires all over the place. This is basically license to knock down the entire wall and do whatever you like as long as she can't see those wires when you're done. I stopped short of that and just pulled a little part of the wall off.

Here's what it looked like originally (the terrible wires photo):
1-Original

3Feb/130

The Garage Heater: Natural Gas

Now after all this running around with sensors and propane heaters - there's really no possible way we could NOT install a natural gas heater. Browsing the Menards ad for the week yielded a 75,000 BTU Beacon Morris unit on sale for about $450. Figuring that's a pretty good deal, we headed over there and gathered the supplies we needed. Oh the supplies... B-vent to get out the roof, flashing, crazy angle pipe things, a cap, storm collar, black iron pipe for a drip leg on the gas line, 60' of 1/2" copper flex, some flare nuts, some 14/2 wire, a thermostat, some 18/5 thermostat wire, hangars, and various other random things - probably some beef jerky since you always buy beef jerky at Menards. All in - a whopping $750 bill at the checkout. Seems like a lot, but it's much less than the $2,200 quote I received from a local HVAC shop for a 45,000 btu heater. Fast forward a few hours and we've got ourselves cheap(er) heat!

Scenario 1: 20 degree day, maintain 50 degrees

NaturalGas-50degrees

On for about 8 minutes, off for about 56 - that's a total cycle of 64 minutes and it's on for 12.5% of the time. It's a 75K BTU heater, meaning it will consume 75K * 12.5% or 9,375 btu per hour. 24 hours * 9,375 btu / 100,000 * $0.67 = $1.50. That's $1.50 per day for a 30 degree temp differential.

Scenario 2: 20 degree day, maintain 70 degrees

NaturalGas-70degrees

On for about 7 minutes, off for 11. Cycle of 18 minutes, on for 39% of the time. 75K * 39% = 29,250 btu. 24 hours * 29,250 / 100,000 * $0.67 = $4.70. $4.70 per day for a 50 degree temp differential.

Scenario 3: Back to 50 degrees

NaturalGas-70to50

I had some crazy idea like i could figure out the heat loss of the garage with data like this - but there are really just a ton of variables in addition to the heat loss. The biggest issue I see is the heat that moves from the objects in the room (that have been heated up to 70) in to the air around them as the air cools. Look at the difference in how long it takes to drop from 59 to 49 the first time, and then again the 2nd time after the air is heated again.

Here's what it looked like installed:
Installed

And the thermostat:
Thermostat

And my car in the middle of being waxed - since I had to keep the garage at 70 for a while to gather data, I figured I'd make use of the warm weather. That laptop in the background is gathering the data:
WaxingTheCar

Filed under: Thoughts No Comments
20Jan/130

The Garage Heater: Propane Torpedo

As a first followup to my article that looked in to the economics of heating my garage (The Garage Heater), I ordered myself a TEMPer USB temperature sensor to do some fact checking. Note - I use the term fact pretty loosely... more like "numbers I made up using formulas I found on the internet."

The software I'm using to record the data is called ThermoHID (http://www.thermohid.co.uk) - a freeware app which is far superior to the terrible software that comes with the sensor. For this test, I went out on a 5 degree day, bought a bunch of propane, and did my best to keep the garage 60 and 85 degrees for three hours or so:
PropaneTorpedoHeater

The first thing I would like to point out in the data is the pause in the initial heat up. It's darn cold out - the garage started around 38, which it seems to be able to maintain with just heat leaking in from the house (there's one finished wall w/ a house on the other side), so when we tried to heat it up to 85 the first time, the propane tank froze up and we had to switch to a warm one. That's just what happens when you use a 106,000 BTU propane heater with a 20lb tank. It simply can't support that rate (something about vaporization rates).
PropaneTorpedoHeater-TankChange

There's also an interesting drop in there after a bit - that's when we opened the garage to vent the the stinky air out (propane stinks).
PropaneTorpedoHeater-OpenedGarage

And finally - it's interesting that as time goes by, it takes less time to heat the garage back up and it takes more time to lose the heat. I'm pretty sure this is due to all the "stuff" in the garage(cars, garage slab, some engines, refrigerators, tools, workbenches, etc). Heating up air is one thing, but heating up stuff - that takes more energy and it has its own rate of heat loss. But when your "stuff" loses heat because the air around it is cooler, the heat goes in to the air - the air we're trying to keep hot. Just an interesting observation. I'm also fairly sure that 106K BTU heater is putting out nowhere near that hooked up to a 20lb tank, it simply couldn't have been based on how fast that much energy should be able to heat the garage. Now to move on to the inevitable - the Natural Gas heater.

Filed under: Thoughts No Comments
13Jan/134

The Garage Heater

Being a life long Minnesota resident, running propane heaters in the winter to keep the garage warm is incredibly common... and incredibly smelly. Eventually your eyes start burning from all the stuff in the air. Add in some degreaser and it gets really enjoyable in there. This age-old problem led me to try and figure out how to keep it warm in there without the trusty propane heater (or kerosene - that's equally terrible). The only two viable options are Electric and Natural Gas. I don't really care which - I just want to pick the most cost effective one. If you ask anyone, the answer is always natural gas. But why? How much more efficient is natural gas? I care because natural gas requires a gas line, while electric would just require another (big) circuit (the natural gas heaters require a circuit as well, just way more logically sized). Ever wonder how those electric heaters rated in watts translate to the BTU rated natural gas heaters? I did. I also wondered how much all this propane is costing... and why I have to run the heater so frequently.

First things first, I looked at my gas bill and it's billed in therms. That's kind of the standard unit i'll use, a therm is 100,000 btu (and wherever I say BTU, i really mean BTU per hour). How's that translate to electricity? Well, 1 watt = 3.414 btu so one therm 29,291 watts. Then I went and looked at my electric bill and it's billed in Kilowatt hours so now I can just multiply by my per kWh cost ($0.10).

Electric costs roughly $2.92 per therm
Natural Gas cost roughly $0.67 per therm

That's a difference of $2.25 per therm consumed. Let's say I think I need a 75K BTU heater. That's 75% of a therm, so for every hour I run it, it would cost $1.69 more for electric. Now let's assume the Natural Gas setup cost $1,000 more to install - it would take 591 hours to eat up the cost difference.

Ok, got that sorted out, now what about this propane stuff, what's that cost? First we have to sort out how much is in the tank (just using 20lb tanks) and divide by the fill cost. Turns out one gallon of propane has 91,600 BTU. But I buy propane in pounds, so one gallon is 4.24 lbs - meaning there's 4.6 pounds required per therm. On the high side, i'm paying about a buck a pound (if they actually filled the tanks up to 20lbs).

Propane costs roughly $4.60 per therm

That's a whole bunch - definitely need to stop using propane. Now let's try to figure out how much heat I actually need rather than just using the 'ol "bigger is better" logic. It takes 0.018 btu per cubic feet to raise the air 1 degree fahrenheit and my garage is 34' x 22' x 9' = 6,732 cubic feet. If I did the math right, it would take 121.176 BTU per degree we want to heat it.

Now for the heat loss part...

Filed under: Thoughts Continue reading
6Jan/131

Our Life Fitness X5 Elliptical: Part 1

I want to start off by saying that this is the greatest home elliptical I've ever used. I'm 6' and my wife is 5' 4" - the adjustable stride really goes a long ways (pun intended) towards allowing both of us to enjoy this piece of equipment. I'm not familiar with the Basic console, but I can attest that the Advanced console's auto-adjusting workouts are very handy at 6AM when you're really not interested in pressing buttons - or really thinking at all. This unit gets used almost daily (by Lindy - I fell off the wagon some years ago) and we've owned it for about 6 years now. Here are the stats on this mamma jamma:

DSCN0069[1]

DSCN0068[1]

We haven't had a SINGLE problem with it... until now. After our most recent move, to be exact. Now - we've been in our new place for about 4 months and this is the first time I noticed it was having an issue. Wait - but didn't I say it was used daily? Oh, it is. But Lindy is they type that doesn't really care about the flashing lights on the screen, she actually finds them to be distracting. So what she does is turns the unit on, hits all the buttons to get it in to manual mode, and lays a DVD case across the entire screen, cause she really doesn't care what it says. Well, I decided I should jump back on the wagon for some strange reason earlier today and my little auto adjusting program wasn't working. The Level area of the display simply says "OFF" and no amount of button pressing will wake it up.

DSCN0070[1]

So the only good news here is I get to give Lindy crap for always using the elliptical on Level 1 and being a slacker. Bad news, I gotta fix it since the warranty ran out like 3 years ago.

12Jun/120

Upgrading from SQL 2008 R2 Enterprise to SQL 2012 Business Intelligence

With all the new licensing considerations in SQL 2012, everyone's busy trying to figure out exactly what the cheapest license they can get by with is. For one of our instances, we decided rather than Enterprise, we could run the Business Intelligence edition to save some cash. That upgrade path is explicitly listed in the Supported Version and Edition Upgrades documentation.

What happened?

We clicked through the upgrade wizard as usual and it got through about 85% and then prompted an error:

Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes.

We clicked ok, then it finished and the results showed that the SQL engine and a few other features failed the upgrade. Great.

27Feb/124

Slowly Changing Dimensions with MD5 Hashes in SSIS

We recently moved away from the 3rd party Checksum component (and all 3rd party components) in SSIS and I wanted to share the pattern we settled on for maintaining our Type 1 Slowly Changing Dimensions (SCDs). There are two things we wanted to address with our new pattern. First, our previous implementation wasn't performing as well as we needed it to or generating reliable checksums. The second was that we wanted to get away from dependencies on custom assemblies in general. To illustrate the pattern, we're going to build a SCD package off the Adventure Works DW DimCustomer table and skip over the actual source of the business keys and attributes by selecting directly from the completed dimension for now.

First, we assume that our dimension already exists (and we were using some other checksum or MERGE to maintain it). We have to add a column to store the MD5 hash:

ALTER TABLE dbo.DimCustomer ADD
	MD5 VARCHAR(34) NOT NULL DEFAULT ''

Second, we need a staging table to store updated/changed rows. Script out the current dimension as a CREATE, remove all unneeded constraints and indexes, and create a staging table as a heap:

CREATE TABLE [dbo].[Staging_DimCustomer_UpdatedRows](
	[CustomerKey] [int] NOT NULL,
	[GeographyKey] [int] NULL,
	[CustomerAlternateKey] [nvarchar](15) NOT NULL,
	[Title] [nvarchar](8) NULL,
	[FirstName] [nvarchar](50) NULL,
	[MiddleName] [nvarchar](50) NULL,
	[LastName] [nvarchar](50) NULL,
	[NameStyle] [bit] NULL,
	[BirthDate] [datetime] NULL,
	[MaritalStatus] [nchar](1) NULL,
	[Suffix] [nvarchar](10) NULL,
	[Gender] [nvarchar](1) NULL,
	[EmailAddress] [nvarchar](50) NULL,
	[YearlyIncome] [money] NULL,
	[TotalChildren] [tinyint] NULL,
	[NumberChildrenAtHome] [tinyint] NULL,
	[EnglishEducation] [nvarchar](40) NULL,
	[SpanishEducation] [nvarchar](40) NULL,
	[FrenchEducation] [nvarchar](40) NULL,
	[EnglishOccupation] [nvarchar](100) NULL,
	[SpanishOccupation] [nvarchar](100) NULL,
	[FrenchOccupation] [nvarchar](100) NULL,
	[HouseOwnerFlag] [nchar](1) NULL,
	[NumberCarsOwned] [tinyint] NULL,
	[AddressLine1] [nvarchar](120) NULL,
	[AddressLine2] [nvarchar](120) NULL,
	[Phone] [nvarchar](20) NULL,
	[DateFirstPurchase] [datetime] NULL,
	[CommuteDistance] [nvarchar](15) NULL,
	[MD5] [varchar](34) NOT NULL)

Now in to SSIS - We will be building:

  1. Execute SQL Task to Truncate our Staging table(s)
  2. Data Flow Task to Insert new rows and Stage updated rows
  1. OLE DB Source to retrieve our source data
  2. Script Component to Generate Row Numbers
  3. Conditional Split to Evenly Distribute Rows
  4. Script Component to Generate MD5 Hashes
  5. Union All to Squish it all back together
  6. Lookup to get the existing MD5 Hash (if it exists)
  7. Conditional Split to separate Unchanged and Changed rows
  8. RowCount Transformation
  9. OLE DB Destination for Changed rows
  10. OLE DB Destination for New rows
  • Execute SQL Task to Update changed rows
  • Completed Control Flow

    Completed Data Flow

    2Feb/123

    Uncivil UNIONs

    If you asked any SQL Server Developer or DBA what UNION does, they would tell you it combines two result sets. Then if you were to ask them if they knew it removed duplicates without the ALL argument, they would say yes. That's academic, but I dare you to go look at their and your code and see how often UNION without the ALL argument is used. UNION is evil. Why do I say that? Because DISTINCT is evil and blocking operations are evil so it follows that UNION is evil.

    Would you be OK with the following query as the source for a fact table?

    SELECT DISTINCT *
    FROM
    (
      SELECT DISTINCT [c1],[c2] FROM [t1]
      UNION ALL
      SELECT DISTINCT [c1],[c2] FROM [t2]
    ) a

    No? Then why are you OK with this?

    SELECT [c1],[c2] FROM [t1]
    UNION
    SELECT [c1],[c2] FROM [t2]

    Personally, I tend to use UNION to combine data from two different sources. For example, if I wanted to combine the sales transactions from two JDE instances - the query might look like:

    SELECT [c1],[c2] FROM [JDE1].[dbo].[F42119]
    UNION
    SELECT [c1],[c2] FROM [JDE2].[dbo].[F42119]

    Each line here in a sales detail represents a line on an order, so assuming you selected all the PK columns in your query, a UNION won't remove any rows within either set, but when combined - it's possible the same PK could exist in both systems. Now you could split hairs and say it's highly unlikely the rest of the row would be identical, but that's not the point - it's wrong to ever DISTINCT a set of transactions intended for creating a fact table. Sure, in this particular case you could add a "SourceSystem" column that would stop that, but then you still have to accept that the UNION is blocking and expensive. Just because a DISTINCT doesn't modify results doesn't mean it didn't do any work - it actually did a great deal of work. Now consider that we're usually not selecting 2 columns, we're selecting 10-20. Do you want to wait for it to figure out all the rows are distinct when you already knew that? Further, if they're not distinct you probably screwed up a JOIN somewhere.

    As far as coding standards regarding UNION go, I would suggest never using UNION without the ALL clause and doing your DISTINCT elsewhere if that's actually what you intended. Does that seem silly? Yes - but I would wager you won't ever use the 2nd one outside of building Dimensions.

    SELECT [c1],[c2] FROM [JDE1].[dbo].[F42119]
    UNION ALL
    SELECT [c1],[c2] FROM [JDE2].[dbo].[F42119]
     
    --OR
     
    SELECT DISTINCT * FROM
    (
      SELECT [c1],[c2] FROM [JDE1].[dbo].[F42119]
      UNION ALL
      SELECT [c1],[c2] FROM [JDE2].[dbo].[F42119]
    )

    And don't get me started on unioning subqueries with GROUP BY's - Why don't you just throw half your processers in the garbage, light tempdb on fire and put all your data in heaps.

    Tagged as: , 3 Comments
    19Oct/114

    Pattern for Conditionally Sending SSRS Subscriptions

    This is a pretty common request - send the report if there's an issue, don't send it if there's not an issue. This is a simple pattern for doing that using Data Driven Subscriptions. Here, we're not using the data driven subscription to dynamically populate recipient and parameter information, we're just using it to suppress the sending of the report if the report has no value.

    To do this, we only need one thing - a query that returns one row if the report should be sent and zero rows if the report should not be sent. Here is an example - Let's say I want to send a report out if a JDE Address Book record with an AT1 of 'DC' has been updated after a certain julian date.

    First, write a query to return rows meeting that criteria:

    Tagged as: , Continue reading
    24Jul/1122

    Troubleshooting: 1998 Jeep Grand Cherokee (ZJ) Doesn’t Start – Cranks, No Fuel, No Spark

    This has been one of the most frustrating repairs we have come across. One day the Jeep just plain refused to start anymore. We have had prolonged starting times for a while now where we will let fuel pump prime at least twice before it would start, but it would always start. Nothing really precluded the no start condition, we just turned it off one day and it didn't want to start again. The symptoms don't really point to any particular component (other than the PCM) and without some fancy tools, you'll do exactly what we did - replace the PCM... and it won't fix anything. So we took out our fancy tool, looked up some diagnostic diagrams on alldatadiy.com, and got to work.

    A few things to check for right away to see if you're having the same issue we were:

    • Check to see if the fuel pump runs during cranking - ours didn't
    • Check for spark during cranking - we didn't have any
    • Check to see if your check engine light is on (ours WASN'T)
    • Check your voltage and fuel gauges with the ignition on - ours didn't register anything
    • Check to see if your low fuel light is on - our was