January « 2013 « tim laqua dot com

tim laqua dot com Thoughts and Code from Tim Laqua


Hiding SSAS Attributes With Powershell

The challenge here was that we have a cube with some great data as well as some low level detail (customer detail, think PII) that we want to expose to a larger audience. The problem is that the low level detail is not something that the new audience needs (or in some cases, is allowed) to have access to. The obvious answer here is Dimension security - why not use that? Two reasons. First, Dimension security is slow. Second, even if it wasn't slow, to hide all the data in many (30+) attributes is tedious to setup, and when new attributes were added we would have to make sure and disable access to those as well. To be clear, we're not just hiding attributes here, we're creating an entire copy of the existing SSAS database and hiding attributes in the copy.

# Import Required Libraries
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") >$NULL

# Connect to server
$server = New-Object Microsoft.AnalysisServices.Server

# Make a metadata copy of the existing cube
$newdb = $server.Databases.GetByName('POS Data').Clone()

# Change ID and Name of SSAS DB
$newdb.Name = 'POS Data - Limited'
$newdb.ID = 'POS Data - Limited'

# Drop Existing SSAS DB if it exists
$server.Databases['POS Data - Limited'].Drop()

# Add the new copy to the server

# Sync our copy of the new database with the server's copy

# Grab the cube we want to work with from the new database
$cube = $newdb.Cubes.GetByName('POS Data')

# Hide the Location Dimension
$cube.Dimensions.GetByName('Location').Visible = $false

# Hide all attributes and hierarchies in the Customer dimension
$cube.Dimensions.GetByName('Customer').Attributes |  %{$_.AttributeHierarchyEnabled=$false; $_.AttributeHierarchyVisible=$false;}
$cube.Dimensions.GetByName('Customer').Hierarchies | %{$_.Visible=$false; $_.Enabled=$false;}

# Enable the key attribute in the customer dimension - it won't work if the key isn't enabled
$cube.Dimensions.GetByName('Customer').Attributes['Dim Customer'].AttributeHierarchyEnabled=$true

# Enable the Market attribute in the customer dimension
$cube.Dimensions.GetByName('Customer').Attributes['Market'] | %{$_.AttributeHierarchyEnabled=$true; $_.AttributeHierarchyVisible=$true;}

# Hide the Location Database attribute in the Product dimension
$cube.Dimensions.GetByName('Product').Attributes['Location Database'] | %{$_.AttributeHierarchyEnabled=$false; $_.AttributeHierarchyVisible=$false;}

# Add a new member to the role granting Read Only permissions in the cube
$newMember = new-object Microsoft.AnalysisServices.RoleMember("domain\tlaqua")
$newdb.Roles['Role 1'].Members.Add($newMember)

# Push our updates to the server

# Process the new database

# Disconnect from the server

This approach has two notable downfalls. First, you have to think up a different database name because the original db with the low level detail still exists on the server. Second, you have to ProcessFull the clone that you made. It doesn't close the data, it clones the metadata. All in all, works great for us, this particular cube is only processed once a week and the 20 minutes we lose processing data in to the clone is more than acceptable (looks really clean to the users as well).


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:

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

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

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

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 garage door repair service and they helped me 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 a flexible heating element 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

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:



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.


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.