32 Bit vs 64 Bit

I want to put a quick post in here because I released yesterday that I had been being a dense idiot with this. I’ve been around IT long enough to know how it works but I still fell into the trap so please pay attention to this.

32 Bit Office means it can only access the first 2GB of RAM on the system NOT up to 2GB per 32 Bit process. I spent over a week tuning my powerpivot sheet for Excel 32 Bit thinking “keep the RAM requirements under 1GB for launch and you’ll be fine”. Launch day came and tuning had kept requirements to 950MB (engaging smug mode) HOWEVER I started getting reports of max RAM errors. I looked into it and went back to my older school days of doing technical support and realised that it was things like Outlook (300 MB of RAM), Lync 2013 (250MB), other applications (500 MB). Suddenly it dawned on me, I had been suckered into thinking of that 2GB limit as almost portal.

The lesson is clear, if you have a hankering to use big spreadsheets and that then move to 64 Bit office. The old issues of incompatible plug-ins and macros may well still exist, but the difference of being able to use the 4+GB of RAM your system has make it worth it.

I’m actually amazed that we default to 64Bit OS these days and most systems have 4+GB of RAM yet we (and Microsoft) still default to 32 Bit Office… makes you wonder

Too many Dates?

Real world item often have more than one critical date, Orders will have an Ordered Date, Dispatched Date, Delivered Date etc… In Incident Management the core dates to begin with are Created Date and Resolved Date, but there may also be Status transisiton dates i.e. On-Hold/Off-Hold/Assignment Changes etc… I struggled trying to work out how to fit this into a “Narrow your data” mentality. In the end I tried a completly different solution in 2.0. I pivoted the dates in my attempts at PowerPivot godness I probably went a little too far – I couldn’t get the original idea to work.

Pivoting the dates wasn’t as bad for me as it may be for you, I was able to do it on the SQL server, I created a view from the original source of the tables, that view pivoted the data and then PowerQuery imports the view, drops the mic and walks off like a pro

SQL Source – I wrapped mine in a create view statement

SELECT [Incident GUID], DateSwitch.DateValue, DateSwitch.DateType
FROM TblAll_Incidents
UNPIVOT (DateValue for DateType in ([Created Date], [Resolved Date], [Closed Date])) as DateSwitch

I’ll break it down because I know I promised not to get overly technical. I’ll also share a secret – GUID, if you talk to DBA’s or System Architects long enough you’ll come across this term and they’ll invariably try and look down their nose at you if you ask what it means so I’ll tell you. GUID stands for Global Unique IDentifier. Simply put a GUID should be unique within the system – if not the whole wide world. They are 16 digit strings of numbers and are semi-randomly generated (I’m not 100% on the science of their generation) basically though when someone says they’re going to use GUID’s it means they’re going to set the Database up properly, numeric 1,2,3,4… indexes not only repeat for each table they also commit the biggest sin in database terms… the change width, 1, 10, 100, 1000.

For those old enough to remember it that was the worry about the Millennium bug “How will a computer cope if it has to put 100 (three digits) into the space for 99 (two digits)?” Millions if not Billions was spent so we wouldn’t find out the hard way.

The UNPIVOT section is going to get me into trouble, basically it’s going to turn a column into a row in SQL PIVOT would turn a row into a column, in essense though I’m sticking to what I said, I pivoted the dates using the UNPIVOT command in SQL. So we’re turning a column into a row, remember though at any given point in the query we’re only looking at one row. That means our single row will be slit into the number of rows you mention in the the UNPIVOT. Our Tickets have three dates [Created Date], [Resolved Date] and [Closed Date] so our unpivot turns our one row into up to three (not all tickets have been resolved or closed remember.


Suddenly there was a way of really pulling the column counts down for the imports. The second step occurs in PowerQuery. When importing that DateValue column change the datatype to just be Date – you lose the time but the reality is for most reports you won’t use it. Retaining the time in the SQL though means that IF or in my case more likely WHEN senior management say “We want to know what time tickets were created” you can handle it

I got too keen when I first hit upon this and tried to combine all the dates from all the processes I needed to report – they’re still like that in the SQL, however PowerPivot (or more likely my skills) let me down. If you work out how to combine the dates from a single FACT table into a single “Date Pivot” please let me know!

Another day another village?

I am starting to really gain an understanding for how Dr Frankenstein must have felt, that terrible mix of pride at what he’d created and then horror as he watched it lurch off towards another village.

Today I recieve a Typical Request, this is the sort of thing I get used to, I’m so used to it now that that as you can tell I generally butcher my notes, be kind, normally there are only for me to read.

As a “Doer” (note I give myself a capital), I’m used to getting tasks like this, although if I’m honest I’ve done a pretty good job of collating this into something meaningful and in this particular case the customer had actually mocked something up… very unusual! This is however a great one to show you how it goes typically when I receive a request like this.

Step 1: Meet with customer
Step 2: Panic – What am I doing, what have I agreed to, why am I speaking to myself?
Step 3: Refer to Data Model, does it have the core data I need
Step 4: Create additional Measures as required
Step 5: Start setting out reports
Step 6: Meet with customer(s) and present results
Step 7: Rework as required (never ever underestimate this step, your first pass will invariably miss the point and not through anyone’s fault)

Step 7 is by far the hardest step for people to understand, this goes back to my time in Consultancy but is a hard lesson to learn and can be a bitter pill to swallow. Emergent Behaviour is not the same as people doing a “Lou” (See Lou and Andy), the reality is much more complex while at the same time being simpler – I’ll jack all this in at some point and become a motivational speaker – EMERGENCE. Emergent Behaviours are mathematically unpredictable without going through the steps. A great example of this is Langton’s Ant. So when you present to your customer/manager/trained/untrained imp a report and they say “couldn’t it do…” or “but I wanted…” generally what they really mean is “Holy Cow that’s blown my socks off, that reports shows me <Insert point here>, I wish I could dig deeper”. The Emergent Behaviour is that if you didn’t do the first draft/pass they wouldn’t know what they wanted. There is no way to predict the outcome without following the steps. Knowing that and releasing the stress of being asked to make changes is a vital step. Likewise, the frustration often felt by the requestor needs to be tempered (I find however reminding them off this rarely moves things forwards) hopefully one will read this an have that “Holy rusty metal Batman” moments (other super heros are availalbe!) and realise that in fact that first pass was necessary to help distil what it is they are looking for and to drive the key thing they actually want – An Invaluable Report rather than worthless jibber jabber.

Good hunting Starbuck!