Home > Excel, Other Tools, PowerPivot > Flawless Excel in Business Intelligence

Flawless Excel in Business Intelligence


Somewhat prompted by some recent posts about the inherent error associated with Excel as a BI tool (and more importantly how some magical tool solves this problem) I thought I’d discuss Excel as a BI tool. In-fact, it’s one of my favourite pondering points “what role should Excel should play business intelligence”

As I’ve stated about it’s a discussion that’s often prompted by claims that a product improves the inherent risk associated with Excel (and to a lesser extent Access). Vendors often suggest that their Excel support mitigates the inherent risk associated with Excel because it adds a corporate security and authorised data capabilities the users trusted tool (often it is argued that Excel the only tool business users want to use or perhaps can use).

There are several flavours presented in the pitch (often several of them are presented in the same discussion) so let’s look at a few of the key selling (or scaring) points;

  1. Excel has inherent risk and is error prone. For this, I’ll take an extreme case since it suites my discussion. The nature of these stories is that Excel caused some monumental loss because of an error in a formula or there was some other error in the spreadsheet. There’s nothing like a bit of fear so make you sit up and pay attention is there? If you have not had this error yet don’t worry because you will … it’s only a matter of time (in fact we’ll even through in some statistics to show just how common this can be). It’s no big deal if you haven’t found it yet because you just haven’t looked hard enough. Infact some vendors even go so far to claim that users are arrogant (now that’s going to mend the bridge between IT and Business). But then again, there’s nothing like creating a bit of division is there – ‘oh yeah now I remember divide and conquer’
  2. The Excel (or Access System) is undocumented and the removal of the Key Person that developed the tool would cause monumental loss to the company. How could any responsible manager allow a system or some reporting tool to operate if it is reliant on a key person … this should never happen should it? If you want to be a good manager (and there’s the no back-out argument), ensure business continuality you need a system that’s IT sanctioned with proper documentation – so who wouldn’t want or need that that?
  3. Oh yes – did I mention that IT hasn’t sanctioned your Excel (or Access) work? They don’t support it and you can’t go to them with problems. This really enforces the risk associated with point 1 & 2. If there’s an issue then you are own your own. This is a very compelling argument that enforces a CYA mentality – perhaps we should rewrite the argument to the words ‘if you can’t transfer blame, you better not take responsibility for it’.
  4. End users should focus on data analysis and not data preparation. Here we are presented with the classic position that too much of time is spent in data preparation and not analysis. Could this possibly be a hint to suggest that an unproductive employee that could get so much more done if only used the tool.
  5. IT takes too long or does not do it right. This is such a common selling point that it is almost taken for granted (especially in the realm of reporting). A pitch that focuses on the ‘corporate lifecycle of reporting’ may go something like this;
    1. The business user describes the report to the IT analyst
    2. Sometime later the IT guys get around to writing the spec for the report and pass it to the dev queue where the report writer creates it, this doesn’t happen straight away though because the report writer is so busy. So say 6 months later the user gets their report.
    3. The report is inevitability wrong and the cycle begins again.
    4. By now the business has moved on and the business user needs a new report.
  6. Users need Excel interaction. If there’s been no justification presented above we need one resounding point – the final nail in the coffin (as it where). The pitch here focuses on user needs. We often hear very compelling statements along the lines that users just want to use Excel because that’s what they know or it’s just the way they work. Picture the preparation of a presentation we the analyst grabs some information from one report, creates a LOOKUP from data from another and then creates a chart to paste into Power Point. Based on this story it’s easy to see why users always are going to use Excel (well so the pitch goes).

I have listed a more than a few points here but I think they can be summarised into a few categories.

  1. Relying on Excel for the manipulation of data is a risky proposition (so you need some involvement of IT to provide corporate data to manipulate) and
  2. Users need Excel.

Now the vendor presents their tool – something to solve all the problems listed above, but how appropriate are such tools in reality? Aside from the idea that they present a very narrow perspective of what BI can do, we will now focus on how the vendors tool satisfies the gaping hole in the end users tool set.

Now (the vendor) present the age of enlightenment in their product … our (their) tool solves all these problems because it;

  1. Is managed by IT –hosted so that there will not be any issues associated with lost or deleted files.
  2. Has gone through a rigorous testing process.
  3. Can be used by anyone. The software is so intuitive that any user can pick it up and use it
  4. Delivers data to users in the format they want, that is, its accessible in Excel.

As convincing as these arguments might appear on the surface there’s a real problem with this line of thinking and we can sum this up in two ways;

Firstly to paraphrase Lyndsay Wise (W|T) – if users need Excel then the BI tool is not doing what it should. While I could go into a long winded discussion, I think the statement is succinct. Where users have to bring data out into Excel to manipulate it (you don’t tend to hear the buzz word Mash Up anymore do you?) then there’s a pretty convincing argument that the sanctioned solution just does cut it (sanctioned as in corporate structure). If we think about it for a minute longer and push the case for Excel we’d realise that what we are really saying is that our business model is so inflexible it needs an additional layer of manipulation in order to provide any real value. This may be the case but is so shouldn’t the problem be attacked at the source and not downstream, that is, when it enters the model and not when it’s spat out?

Now for the second point – one where the tool somehow removes error where there is some form of manipulation required. I am very interested to know exactly how any tool achieves this magical proposition (and have never seen a convincing argument by a vendor). At best, you’ve got a cell location (or multiple) that links back to corporate (and supposedly agreed) data. However, if we look at process of what Excel is being used for, we are faced with exactly the same issue that would be present had they not had a tool. That is, they are free to use the data how they wish. After all, that’s the reason why Excel is so popular – it allows the user (of any skill level) a programmatic interface for working with data. The tool adds no comfort than would otherwise be available from Excel.

If we would like to suggest that the availability of data in Excel is the key to Excel’s woes as a corporate BI tool (and those nasty errors that are so well published) then we are simply adding another layer for users to get wrong. After all the placement of data relies on the end user finding the right information to begin with!

Not surprisingly the argument for safety around Excel as a BI tool resolves in the user and not the tool. Once its placed in Excel, data is at the direction of the user and it is their ability that we seek mitigation against.

 

About these ads
Categories: Excel, Other Tools, PowerPivot
  1. July 11, 2014 at 2:55 am

    Thanks, Paul for the article. Related to the topic …
    Hypothesis: business solutions provided through Microsoft Access are usually accompanied with organizations planning to migrate databases out of Access into something more sustainable long term like SQL Server.
    Considering the above to be true, deservedly or not, Access ends up with a bad reputation instead of being seen as a Minimum Viable Solution. Do you see ways to prevent a similar problem to occur with Excel as an additional layer to Business Intelligence platforms?

    • July 12, 2014 at 9:28 pm

      While I understand the your hypothesis above, the scenarios that I have seen don’t play out that way. Assuming were talking about Access solutions which are developed by the business, I never see them as prototypes for more powerful engine. They are there to solve a problem. Now if there is an enterprise need for one of those ‘problems’ to be promoted to a corporate solution (because everyone raves about it), sure it can be built in the enterprises relational engine. From what I’ve seen Access is used as a step up from Excel.

      Generally, I’d say the reason why Access has a bad reputation (and this is only from an IT pov) is that IT becomes the unwilling point of contact for solutions that they did not develop and simply don’t want to maintain. They will not be held accountable for the content, accuracy and operation for something that was not developed in accordance with their mandate and hence the ‘we don’t support Access’ (even though the software is supported). From the business POV, there is less skill around the Access space (since everyone can use Excel – or so they think) and so there may be some resistance for solutions that are not understood. If we didn’t think we knew how to use Excel we’d be just as standoffish but like I said everyone can open a workbook do (at least) some rudimentary calculations and therefore thinks they can ‘use Excel’.

      But I digress – how do you stop Excel from having a bad reputation (in those situations)? I am not sure we can … media, analysts, auditors and people in general are drawn to the failure and seek to lay blame and vendors will always provide comfort through risk aversion. A practical way to mitigate risk and errors is to assign the same approach to development that are used in large scale and IT projects. Specified criteria, and independent review against that criteria – which are detailed and reproducible (ideally through an automated independent agent). Then lock it down (and test that). As the risk of error associated with failure rises, so should the degree of testing and the independence of the testing process. Can you imagine this happening?

      • July 17, 2014 at 2:38 am

        Thanks, Paul for keeping the discussion going. I will focus my reply on your last paragraph, which cover the points that you and I seem to be most interested in. While it may be impossible to avoid some people perceiving Excel as an ill-suited addition to BI solutions, I think that the approach you mentioned could work (compare to large scale and IT projects development), given the right conditions, including:

        – Business (Excel users) and IT (Data Analysts, etc.) work well together

        – The architecture of Excel and related platforms (SQL, etc.) moving forward support that approach

        I will have a closer look at the direction that Microsoft has headed, from Excel 2010 to 2013 (Figures 1 and 2 from this article show this well – http://sqlmag.com/sql-server-2012/understanding-powerpivot-and-power-view-microsoft-excel-2013) and see if I can figure out some parts of this puzzle.

        Thanks again.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 228 other followers

%d bloggers like this: