Introduction

This page discusses the way that Styles are applied to cells in SpreadsheetML, it is correct at the time of writing and is correct in accordance with ECMA 376 – Second Edition Part 1 which relates to the Microsoft Office Open XML schemas.

Initially I wrote this document just for me, to explain to myself how my code needed to work, but I decided it might be universally useful.

In terms of the actual XML code in this, its as produced by Excel 2010, therefore where people spot additional  attributes that seem pointless/irregular this should be directed at the Excel team not at me.

Style Anatomy – How Excel Does This

In terms of open office xml, a workbook loosely speaking has this structure (intentionally reduced for readability), I have bolded the areas I will be discussing in the document.

• Workbook
  • Styles
    • Fonts
    • Fills
    • Borders
    • CellStyleFormats (cellStyleXfs)
    • CellFormats (cellXfs)
    • CellStyles (cellStyles)
    • Differential Formats
    • Table Styles
    • StylesheetExtensionList
• Theme
• Worksheet (multiple)
  • SheetData
    • Row (multiple)
      • Cell (multiple)

The Way Styles are Looked Up

The Cell (c)

Each cell in a row can have a StyleIndex (s) attribute, this attribute is a number starting from 0. It represents a lookup into the CellFormats (cellXfs) by position (this is a zero index based lookup).

For example, you have a cell defined as:

<x:c r="C4" s="2" xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
	<x:v>123</x:v>
</x:c>

This is telling Excel, this cell (C4) uses CellFormat (cellXfs) index 2. The actually CellFormat xf record at that position looks like this:

The CellFormat (xf) for the cell

<x:xf numFmtId="10" fontId="3" fillId="0" borderId="0" xfId="1" applyNumberFormat="1" xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
	<x:alignment horizontal="center" vertical="top" />
</x:xf>

Inside this CellFormat record there are various format specifiers but the one to note is the FormatID (xfId) this  is saying its another lookup, this time into the CellStyleFormats (cellStyleXfs).

The CellFormat for the “style” inside CellStyleFormats

The CellFormat above contains an xfIf (FormatID) of 1, this is a reference to the item at index 1 in the CellStyleFormats (cellStyleXfs), in my workbook this looks as follows:

<x:xf numFmtId="0" fontId="3" fillId="0" borderId="0" xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
	<x:alignment horizontal="center" vertical="top" />
</x:xf>

Note that this format record does not contain a FormatID (xfID) and it should not do that according to documentation, although this would make a nice future extension in terms of style inheritance in Excel if Microsoft ever chooses to implement that.

The Relationship between CellStyleFormats and CellFormats

In programming terms the best way I can come up with to describe the difference between the cellStyleXfs and the cellXfs records is like this:

Logically the CellFormat inside the CellStyleFormats acts as an abstract base class to the CellFormat in the CellFormats, as such you cannot bind a cell directly to one as it cannot be instantiated directly. The CellFormat which lives inside CellFormats effectively inherits this base class and can be instantiated and if needed override one or more of the Properties that exist on it in order to customize the appearance applied to one or more cells.

CellStyles (cellStyles)

Cell styles are not directly related to the actual formatting of cells, instead they primarily exist for the Excel UI to  show the user a style so that it can be modified etc. in essence they are like Labels for CellStyleFormats. My CellStyle for the style we’ve been talking about so far looks very simply like this:

<x:cellStyle name="CellValue" xfId="1" xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main" />

Some Working Examples

Bold a single cell within a workbook, the logical process.

For the purposes of the example, assume there are many cells in a workbook which share a style. And you want to just bold one of these. This is what you have to do in order to make that happen:

1. You have your Cell (c) it is currently set up to use a style xfId=1.
2. You go to your StyleSheet.CellFormats (styleSheet/cellXfs) and find the element
   at index 1 (the 2nd child). This is your CellFormat (xf) for the cell.
   1. If your xf for the cell has ApplyFont (the applyFont) attribute set, then get the
      value of that attribute and use that as the id of the font that is being used from
      the Font (fontID) attribute.
   2. If your xf does not have ApplyFont set, then gets its FormatId (xfId) attribute
      (lets say this is set to 1 for now)
      1. Go to your StyleSheet.CellStyleFormats (styleSheet/cellStyleXfs) and get the child
         at index 1
      2. read the Font ID from the Font (fontId) attribute of this CellFormat (xf)
3. Assuming a fontID of 1. Go to StyleSheet.Fonts (/styleSheet/fonts) and get the Font
   (font) child element at index 1.
4. Verify that the Fonts collection does not already contain an identical font with
   Bold=true (b=1) set already
   • if one exists use its position as the font index.
   • if one does not, Copy the font element, then add a value Bold=true (b=1) to it, then append this
     newly cloned font specification back to StyleSheet.Fonts. The increment the value of Fonts.Count by 1. take the position of the newly added font definition as the font index.
5. Assuming a font index of the newly added/found font of 6. Create a copy of your CellFormat (xf) for your cell (the one that lives in CellFormats (cellXfs))
6. On your newly copied CellFormat, set the attribute Font=6 (font=”6″), along with the attribute ApplyFont=true (applyFont=”1″).
7. Verify that you do not already have an identical CellFormat to the one you just made in your CellFormats (cellXfs)
8. Assuming that you do not have an identical CellFormat already, append your newly created CellFormat (xf) to CellFormats(cellXfs). Then increment the value of CellFormat.Count
9. Take the index of the newly added/created CellFormat within CellFormats (assume 19), set this value to the StyleIndex attribute of the Cell e.g. Cell.StyleIndex=19 (s=”19)

Although the above is quite a lot of steps, repeating for another cell once you know that styleID=19 is the one to use is very simple.

In terms of being totally correct, you should really check that your old CellFormat for the cell you updated, is still in use by some other cells, then remove it if it has become Orphaned by the process, but be careful doing this as in doing so you are decrementing the index of any higher CellFormats in the workbook so effectively if you remove a CellFormat, you really may need to perform an update on every cell in the workbook. These are the areas that SpreadsheetML becomes really tricky and having good wrapper code becomes pretty important.

General OpenXml Good Practises with Style elements

1. When Appending children onto a collection, always increment the Count.
2. Never remove any children from a collection unless you are prepared to do the work of finding all of the references to that collection
   and decrementing ID’s where possible.
3. Do not blindly add children to a collection without checking that there is not already an appropriate child the same as the one you are adding as it can lead to massive file size bloating.

This post looks into using a pretty cool visualization technique called a Tree Map to represent the data that would traditionally be looked at in a Cross Tab.

TreeMaps from the Beginning

Tree Maps are an Area oriented way of showing data, for instance if you had some Top-Line responses to a question as follows:

It’s TreeMap respresentation would be as follows:

This provides a very instant read for the viewer who can immediately tell not only which the largest and smallest segments of their sample are but also what the real proportions are. Therefore it’s doing the job of a sorted table that has percentages. In essence its an Analog view of the data much like comparing an Analog to a Digital watch.

why Treemaps suit Tables

The tables we get all the time in market research put a lot of information in front of the person who’s trying to make gain insight. This kind of table is really common:

There’s really  a lot of information for the brain to consume in order to build an insight, its all very digital and it takes a long time to process. The Analog view of this is very different:

But Oh! There’s More

Tree map’s have 2 main visual indicators available to them, the first being Area, the second being Color, so far the examples have had both driven by proportion of responses. Here’s an example where the Color is being driven by something different, in this case, the Color on is being driven by  Mean score on another question (the proportion is still being driven from the sample size).

Not only can you easily tell that the 55-65 age group is rating a product pretty high but you also have the visual warning that the insight might be based on a fairly small sub-group.

Wow, how did you do this/How can I do this?

There’s no cheating, we built an app platform quite a while ago now for feeding on survey results which we use for all kinds of visualizations. Then we built a demo app on the top of that which presents data from regular/real surveys into a TreeMap.

The demo app is built using the Microsoft Silverlight Toolkit, and is reading data from our insightMart data warehousing system (the data is sample data and didn’t come from a real market research study in this case).

We can build this kind of visualization and many others for people’s dashboards or presentations very quickly, the data doesn’t even have to come from survey’s.

I’ve changed the colors here but the essence of the problem was that I had a scale which looked something like this (actual colors changed):

In other words a gradient brush scale, such as:

<LinearGradientBrush x:Key="MyTestBrush" StartPoint="0.05,0" EndPoint="0.95,0">
   <GradientStop Offset="0" Color="Red"/>
   <GradientStop Offset="0.5" Color="Green"/>
   <GradientStop Offset="1" Color="Blue"/>
</LinearGradientBrush>

In my case the brush had far more colors than this in its scale and the particular offsetting of the start and the end of the gradient was important.

I’d previously stumbled on this post about making custom interpolators for the Silverlight Data Visualization Toolkit.

So I set about the task of making a LinearGradientBrushInterpolator trying to re-use as much as possible from the toolkit itself.

First off I looked at the way the SolidColorBrushInterpolator worked, it already did a big part of what I needed to do by interpolating a range between 2 different colors.

[sourcecode language='csharp'  padlinenumbers='true' light='false' autolink='false' gutter='false']
namespace ColorPicker
{
    public class LinearGradientBrushInterpolator : Interpolator
    {
        System.Collections.Generic.List interpolators = new System.Collections.Generic.List();
        LinearGradientBrush templateBrush;

        public LinearGradientBrush TemplateBrush
        {
            get
            {
                return templateBrush;
            }
            set
            {
                if (value != templateBrush)
                {
                    templateBrush = value;
                    interpolators.Clear();
                }
            }
        }                                                                                                                   

    }
}
[/sourcecode]

In other words I decided to use an array of SolidBrushInterpolators to do this. To set these up I wrote this:

[sourcecode language='csharp'  htmlscript='false' padlinenumbers='true']
 private void InitializeBrush(LinearGradientBrush br)
        {
            if (br == null)
                throw new ArgumentNullException("br");
            //set start and end interpolators
            double start = br.StartPoint.X;
            double end = br.EndPoint.X;

            if (br.GradientStops.Count < 2)
            {
                throw new Exception("2 Gradiant Stops must exist");
            }

            SolidColorBrushInterpolator startInterpolator = new SolidColorBrushInterpolator();
            startInterpolator.DataMinimum = start;
            startInterpolator.DataMaximum = br.GradientStops[1].Offset;
            startInterpolator.From = br.GradientStops[0].Color;
            startInterpolator.To = br.GradientStops[1].Color;
            interpolators.Add(startInterpolator);

            for (int i = 1; i < br.GradientStops.Count - 1; i++)
            {
                SolidColorBrushInterpolator inter = new SolidColorBrushInterpolator();
                inter.DataMinimum = br.GradientStops[i].Offset;
                inter.DataMaximum = br.GradientStops[i + 1].Offset;
                inter.From = br.GradientStops[i].Color;
                inter.To = br.GradientStops[i + 1].Color;
                interpolators.Add(inter);
            }

        }
[/sourcecode]

Because of my Gradients not starting or ending on 0 or 1 I had to add special handling of these beginning and end cases.

Finally I had to implement the Interpolate method itself:

[sourcecode language='csharp' ]
public override object Interpolate(double value)
        {
            if (interpolators.Count == 0)
            {
                InitializeBrush(templateBrush);
                if (interpolators.Count == 0)
                {
                    throw new InvalidOperationException("TemplateBrush is not optional");
                }
            }
            double min = 0;
            double max = 0;
            if (double.IsNaN(DataMinimum))
            {
                min = ActualDataMinimum;
            }
            if (double.IsNaN(DataMinimum))
            {
                max = ActualDataMaximum;
            }

            if (value < min)
            {
                value = min;
            }
            if (value > max)
            {
                value = max;
            }

            //calculate relative interpolation
            double offset = (value - min) / (max - min);

            foreach (SolidColorBrushInterpolator inter in interpolators)
            {
                if (offset < inter.DataMaximum)
                {
                    if (offset < inter.DataMinimum)
                        offset = inter.DataMinimum;
                    return inter.Interpolate(offset);
                }
            }

            return interpolators[interpolators.Count - 1].Interpolate(interpolators[interpolators.Count - 1].DataMaximum);

        }
[/sourcecode]

The end result worked nicely and I put together this TreeMap to prove it to myself:

The live version of this sample (which randomizes each time you refresh is here).

Full Source code including the sample application is here.

PASW sure got some eye’s rolling, although it was suggested to be an acronym of Predictive Analytics Software, my favourite theory is that it was an available 4 letter domain name which could be made to fit.

Anyway with the PASW change, all the products SPSS sold were renamed. Therefore well known products like SPSS Base became PASW Statistics. Which brings me on to the products that other products for survey research that had previously been under the banner SPSS Dimensions with product names such as mrInterview and so on, Tim Macer said some fairly inspired words on it here http://bit.ly/155Ll3.

Because the aforementioned trademark dispute was settled another rename has come, and PASW is to become IBM SPSS (http://bit.ly/10qexj).

Best Effort At Decoding the product Names

My Opinion

If it matters, and I have no idea whether it does although I am an SPSS Customer and I do spend a good deal of my time working with this line of products for my various customers.

This is diluting your brand IBM SPSS, I know you are going for enterprise sales and it makes is easier to sell the concept of IBM SPSS Data Collection as an end to end solution (which it is at one level). However:

The tools still exist and the product names just stink of a category dump which may make it easier for you to internally manage things but are completely divorced from any real concept of branding.

its the same as Heinz deciding to rename Heinz Baked Beans to Heinz  Canned Foods Tomato With Beans and running an ad campaign on it.

This may make it easier for Heinz to explain its product at an enterprise level when its talking to Walmart but it doesn’t mean it makes any good marketing sense.

In terms of what happens in opinion research, the last couple of blogs have really been about fermentation and bottling; its all been about warehousing data. Little has been said about getting the data in or the process which we call Harvesting.

What are the Grapes?

While I am pressing (excuse pun) on with this analogy, a grape is an opinion; opinions about your business or one of its brands are not concrete things. There are potential grapevines all over the internet, internal information systems and there’s still of course surveys; either survey’s that you commission or industry reports that you are savvy to.

These sources of information vary wildly in quality, structure from a raw data point of view. Somehow you want to bring all or some of these sources to paint a full picture, one that can drive some action and deliver improvement.

HArvesting

At this point I could paint a rather blissful picture of the process of harvesting which looks like this:

To combat the disconnection between the simplicity of this diagram and the actual complexity involved in shuffling through data and getting it to sit nicely together in a warehouse. We developed the concept of Harvesters which quite literally take a stream of data and “do stuff” to it in order to make it sit nicely in a standard location for analysis and reporting.

There’s no such thing as a free Opinion

Structured data like that coming from surveys or CRM systems is relatively easy to deal with but rarely standardized. It’s also of relatively known quality, but there’s no surprise that you pay to get it (you either pay your call centre, web site, survey systems/vendors). Therefore this is the cheapest data to harvest.

Syndicated research (e.g. industry reports/omnibus data) data sits slightly in the middle of the spectrum, its got a structure (usually quite a variable one) but its aggregated so its limited, and some historic data also fits into this part of the spectrum.

Conversely unstructured data like facebook, twitter and blogs are almost without structure, generally need some categorization which can also mean a partly human process therefore the harvester is potentially a process in its own right.

Whichever way you look at it, you either pay more to grow the grapes and less to harvest them or the other way around.

Keep focussed on the Barolo and You will Pick the Right Grapes

Throughout the process of building a warehouse, you have to stay focussed on the wine you are trying to make at the end. The un-harvested grapevines will still be there. There’s been too many attempts to build warehouses without a clear vision of what’s got to come out of the other end.

“lets grab all the data we possibly can and see if there’s anything there”

This is NOT the idea, you will never get a good glass of wine from a bottle of random grapes without huge amounts of luck.

If you are interested in finding out more about what we do, or what value it can bring visit www.forgetdata.com or message me personally on twitter @samwinstanley

“survey data is simple, you should try building an analysis database from our customer records…my life is hell”

I shrug this stuff off, its easy to pick on the little guy who isn’t building the systems that have 7 figure budgets and 100 strong programming teams.

Thinking Backwards…

Researcher’s actually think backwards, they know they are digging for information about how things perform against each-other, they come up with the instrument to measure this data, possibly in terms of questions and answers, possibly in terms of qualitative analysis, web mining etc.. however the start is the business problem. Often this transpires into some kind of interview script which has modules and questions and sample variables etc… what just happened was that something very business specific into something deeply generic (questions and answers). This is what I call “downgrading”.

For years and years software vendors have been working very hard to build systems for collecting and analysing data for surveys, and all of them have focussed on ease of creating surveys. In other words all of them help convert business minded ideas into generic questions and answers.

Ease of Authoring Might be a BAD thing..

You see the truth is that ease of authoring is a pseudonym for data-hell, it means that a relative novice can create an immense survey with no thought whatsoever to the real structure of the data that’s coming out.. an entire DP industry and countless DP tools have been founded on creating order from the chaos that is surveys.

We Revolve Around Cross-Tab Reports

I refuse to do an extensive industry study to establish how many cross tabulation tools exist for market research vs. the rest of industry. Whatever the hard numbers I’m certain its extremely disproportionate to the market size.

It’s clear why the market research and survey research industry loves this kind of technology so much though. It’s allowing primitive information like questions and answers to be “upgraded” back into a business context so that it can be analysed.

So are cross tabs a bad thing?

Cross tabs have a role, they are an analysis tool more than a reporting tool, its a fairly neat way to compare X with Y in quite an ad-hoc fashion it enables exploration of results.

However cross tabs bring baggage with them when you use them as a reporting platform in a couple of ways:

• Cross tab reports are nearly always showing a single source of data and that data is questionnaire structured not business structured. This means every piece of information you put into this data source is getting “downgraded” and its loosing richness and its context.
• Cross tabs naturally limit analysis, we “the techies who provide to the MR industry” try to force statistical models into these reports in a very un-natural way… for instance nobody else, anywhere outside surveys has really made use of a Column Proportions test which compares column A with B/C/D/E, that doesn’t mean that nobody else in the world ever does that kind of analysis it just means is a pretty absurd way to implement the result of a statistical test.

Preaching to the converted

Much of the survey research world has moved on from cross tabs at least at some level, and I’m not out to teach people to suck eggs. But I see so much evidence that traditional DP and interactive tab  packages still provide lots of underpinnings for the industry.

The industry is bound in chains but contains many smart people who do understand that if you think up-front about the way to arrange data, you can provide a much more valuable analysis and reporting experience on the back of it. What we lack is the 7 figure budgets that make it so easy for banks and blue-chips to roll elegant warehouse systems and dynamic business dashboards on the back of it.

Back to You Mr Database Guru

At the start of the post, I stated my poor treatment by my peers in the banking world. The truth is they live in a world of luxury, if they thought they had a hard life now whatever would they do if 100’s of people in the organisation were able to add a new data field into a customer records.. but that’s virtually the same thing as adding a new question to a questionnaire. Seriously, those guys would suck at surveys in every way…

At forgetdata we spend a lot more time thinking about and designing the strategy for managing and storing survey data than most people. We understand that perfect standardisation of questionnaires isn’t a perfect world its a rare luxury and that even 6 figure data architecture budgets are even rarer.

Our passion is helping the industry solve this kind of problem without having to re-invent every wheel its based on, a sensible business based highly controlled kind of chaos.

When analysing markets or business performance, analysts and researchers are faced with this kind of task all the time. Forced into a position to be able to give a certain figure for whatever the KPI in question is, this number I will call 5.6 just for consistency, it matters little what 5.6 is it could be a measure of anything. For instance “on a scale of 1.7 the average overall customer satisfaction was 5.6 for the last quarter” it can be a pretty important number this 5.6….

If the Answer was 5.6 what was the question…..

For a long time in the world of survey research arriving at such a number was a simple task relatively speaking.. you would run a survey, perform some data processing and somewhere probably in a cross tab report clearly stated there was the Overall Satisfaction question with a Mean score of 5.6.

However things are changing, the source of 5.6 is getting spread out, here are some of the things that could be considered now as evidence that “on a scale of 1.7 the average overall customer satisfaction was 5.6 for the last quarter” based on the world as it looks now (and this list is not exhaustive):

• The responses to several surveys quite possibly surveys that don’t exactly match some from in-house research, some bought in from outside sources.
• Research from actual performance in CRM and mining what was said in emails and conversations between the company and its customers. For instance if based on 1000 respondents the overall satisfaction was 5.6 but based on 10,000 calls to customer service, 85% of people said “I hate this company and I never want to do business with it again” its hard for an analyst to ignore this data if its available when arriving at 5.6.
• What is said on the internet about a business in social networks and online communities can and more frequently is being listened to as evidence of 5.6.

Where’s My Data?

Given that 5.6 is considered important, possibly even life changing from the point of view of an analyst or researcher, its pretty important the the evidence supporting it is very solid. So solid that if somebody different were asked to reproduce the number 3 years down the line after the life changing decision they can do.

Relating back to the expense claim, its traditional to write down all the line items which amount to your number in a spreadsheet, and sometimes even scan all the evidence supporting those numbers and attach it.

How to Know Where your Data is and PRofit?

An initial part of any kind of analysis to arrive at 5.6 usually involves getting all of your data into 1 place and somehow normalising it to make it comparable. If 5.6 was the only answer you needed then probably making a spreadsheet and doing some analysis in Microsoft Excel would be quite okay. Unfortunately 1 number is never enough and 1,000’s 5.6’s are usually needed.

To produce this kind of information quickly and efficiently either on demand via a Dashboard or online report, or distributed in a periodic report you have to build a process to support it along with the technology to support that. You need a common repository or warehouse to put all of your data in to enable you to get it out in a structured way.

At forgetdata we’re pioneering a type of data warehouse that allows data from diverse sources to co-exist, that enables these worlds to meet and we’re building the tools around that to support the getting the data in, analysing it and reporting from it.

There’s lots of general benefits to having a warehousing system in place that I’ll try to discuss in future articles, but its clear that making wide use of this kind of warehouse system enables lots of new possibilities both in terms of analysis and efficiency.

Honestly, I’m not a serial ranter most people who know me would say I’m a fairly laid back kind of person, and they would be right until this chain of events…

• On 4th September I received a couple of letters in the post from BT One letter said “Welcome to BT, <fluffy saying how great it was I’d opened an account with them”, the other letter said “please complete and sign the following Direct Debit mandate for £22.50/month” or words to that effect.
  • I was confused I had a BT account which had remained intact for at least 5 years I had never changed it and indeed I checked my bill and the Account number on these letters did not match the account number on my bill, but the telephone number did.
  • My new service would start of 4th September which was the same day I got the letters so I thought I should check this out pretty fast or risk getting sucked into a “mystery contract” from BT.
• I phoned up customer service, they didn’t understand what had happened at all, however they thought it was all likely some glitch and promptly shut down the “mystery account number”. I made a complaint I wanted to know what had happened.
• The next day a very jolly lady phoned me up, she was clearly a kind of “anti-churn CRM master” of some kind, she was so deeply apologetic and explained to me that my account transitioned between different internal BT systems and that usually no letters would be sent out and I would see no change except my next bill would have a new account number on it. However something went wrong. She reassured me that everything would be okay that my old account number was still working just fine and not to worry.

At this point I’d spend 2 hours on the phone to BT but at least felt that something had been achieved.

On Monday 7th…

the Phone went dead..

A voice in the back of my head was telling me that this would happen before it actually did, the unstoppable ball had already started rolling in BT, the hasty Customer Service rep I had initially talked to had shut down my account, he didn’t realize he was doing it he thought he was shutting down a strange ghost account but no.. he shut down my account.

My satisfaction at this moment in time was somewhere between Highly Dissatisfied and Completely Dissatisfied, I was scoring about 1.4 on BT’s own Sat scale.

However I thought, this might not be too bad, I’ll phone up tell them “I’ve been disconnected in error, please sort it out”. After several hours on the phone with several people the information I’d been getting back from BT had moved from “Actually sir you have never had a phone account with us, your line is with another provider we just do your broadband” to “Okay sir we understand what’s happened, we’ll transfer you to sales because only they can reactivate your account” reluctantly I agreed to accept my 5th transfer of the evening… On came the sales rep and we reached this kind of point:

Sir we can sort this out, you will need to pay a £5.95 connection fee, sign up to a new 12 month contract and within the next 5 working days your line will be active again

Well I declined this kind offer, and explained the circumstances again very clearly and asked to be connected to a person who could reconnect me without a fee and without a minimum term contract. I discovered that Sales people dislike this line of action, predominantly because 2 different people CUT ME OFF when I uttered these words.. each time after I was cut off customer service would call me back and repeat the process of connect me to sales, each time I said those words the sales person (who is only scored on revenue generation) cut me off. Since then the Customer Service team have evidently given up as no word has been heard from any of the large quantity of people who all appear to be called Michelle (I’ve decided the name is culturally unlikely given what has become very wide spread research now) have attempted to contact me.

But all stories have a happy end…

With Dell it was HP who they lost their business to, with BT I’m really happy to say it was thephone.coop given I was beginning to conclude that the wheels of BT were actually unable to turn in a way that would reach a satisfactory end because the computer was saying NO regardless of the number of cheery people of sub-continental people called Michelle were thrown at it. I phoned up thephone.coop, I use this supplier for all my office telephone/internet needs. Within 20 minutes I’d started a new contract which was cheaper, on my old number (which BT wholesale still have to reconnect and will still take up to 5 days whatever happens).

But there’s more to my choice than that, thephone.coop just shines at customer service, every time I’ve had any cause to contact them, 1 person has been able to see or find out the whole picture. They are a company with values and it shows down to every single thing they do. Ethically they are great, they don’t just say they are different, they actually are different right from the core and for me at least it works. So if nothing else they deserve the viral marketing so here it is, my token effort.

Installing the new disks into my trusty HP server was easy, it can hold 4 disks and it has support for Hardware RAID. I have a spare slot still and I will put the RMA’d failed disk into that when it arrives and use it as a backup device.

The net result of all this thinking was that some thinking and re-arranging of stuff was going to leave me with new drive letters for my Hyper-V area, as a result some configuration changes are needed to pull this off. On the net I’ve seen several somewhat hacky ways to do this but I wanted to do it what I think is “the right way”.

This is the approach I took:

1. Export each of the VM’s to the new disk using the Hyper-V manager’s built in Export feature which I would say 2 things about:
      – is rather slow to be polite. I am certain it would be faster if I paid an exhorbitant amount of money for expensive disks but still this is SATA-300 to SATA-300 on the same machine and exporting 120GB took somewhere over an hour.
      – in general there should be an optimized way to just “move a VM” along with all its stuff. Exporting is more made for moving between machines.
2. Delete the VMs in the Hyper-V manager (gulp)… gotta hope that export was successful.
3. Change the Overall hyper-v settings to point at the new mirrored drive/paths.
4. Import each of the virtual machines. This is very much faster than exporting thankfully.

After my last blog post about using auto-categorization in Desktop Reporter, I was asked the question:

“How do I analyze multiple response data which is coming from Excel”

Here’s a walkthrough of 1 way that you can handle it.

The Data

I started with data which is actually a profile extraction of the Museum database that I exported to Microsoft Excel which looks as follows (in Excel):

Museums_Visited is a multiple response which is comma separated.

Splitting out the categories.

Before we can really work on the data in Reporter we need to make a couple of tweaks to it in excel.

Inside Excel – Select the Museums_Visited Column then click on the “Text to Columns” button:

This starts the conversion Wizard.

The end result of this is a brand new column or 2, actually 5 new columns in my case, there is one column for each response to Museums_Visited and the most responses anybody made to that question is 5. (This is what SPSS Base users will commonly call a “Category Set”).

Now the data is in this shape we can take the rest to Reporter.

Opening up in Reporter

We open up the Excel file in Reporter and it recognises each column to be Text, Text is not great for doing tabulations! it needs some work…

To make the Text variables usable for tabulations we need to categorize them, and we do that using the new Categorize feature, as follows:

Note I have unchecked the “Other” option and very importantly Not asked (NULL) is checked FOR the first category only this point is very important for a reason I will mention later, the second variable should be categorized with these options:

Note that for this one the User Missing Category is checked.

And repeat this for all of the 5 new variables until we have along with the other categorical variables in the data until we have (note that there are new variables and these have new icons):

The final step is to make a new variable that combines our Museums_1-5 into a multiple response.

Hit the select button which opens the Expression builder… We want our new variable to add together all the individual responses to the Museums_1-6 variables to make one new multiple response variable.

Click okay, Desktop Reporter does some work in the background and this screen now has responses in it.

Click Save and close to save this new variable into the data.

Then Export the data to a new format as follows:

Then open the newly exported data.

Note here that I am opening an MDD file after exporting a DDF file.

Drum roll Please

There we have excel multi-response data tabulated in Desktop Reporter.

Some final notes

1. Why did you set up the first Museum variable to use NULL and the others to not use Null.
   I knew I was going to make the multiple response variable which had the expression Museums_1+Museums_2+Museums_3 etc.. I also knew that Museums_1 was the real base of the variable, (e.g. people with a blank to Museums_1 actually did not answer that question). The Dimensions DataModel has special ways of dealing with the NULL value (this is called the ANSI SQL 92 way to handle nulls). As a result 1+NULL=NULL…. In our case if Museums_1 had a value and Musuems_5 did not then the end result of Museums_1+….+Museums_5 would always be null which is definitely not what was expected.
2. Why so many steps.. it must be possible to make it simpler.
   All of these steps can be automated if you are comfortable with mrStudio and particularly with TOM scripting. In the end Excel isn’t a great data format, especially when you talk about multiple response data and I think Reporter is doing a good job of getting around some of those weaknesses. This is a slightly specialized case because in the end if the data you have in excel is really simple like this you can achieve a good result just using excel itself.
3. Why the export to DDF, why not just analyse in the Excel format.
   There’s definitely a performance reason why it’s a good idea, but I also did find some problems with making tables before I did the export. I suspect there were ways to workaround these but I’d already decided I was going to export the data to something that could natively hold multiple response data.