A roadmap for successful business intelligence

The need for structured, robust, and reliable business intelligence has mushroomed in recent years. As an increasing number of businesses grapple with the issue, Paul Cullen  explains the critical elements for implementation success.

Data volumes within businesses have increased dramatically in recent years, primarily driven by cloud-based data solutions. Many companies struggle to harness this data in a way that enables them to focus on the key drivers of their success and to know if the strategies they have executed are having the desired results.
Proper and well-planned implementation of a business intelligence (BI) solution can give management the real-time information they need to maximise commercial opportunities and ensure organisational coherence to deliver on agreed performance metrics.

Why Excel just doesn’t cut it for BI

Accountants have always loved Excel, and it still has a pivotal role as an analytics tool. However, when it comes to flexible reporting and giving end-users the ability to dive beyond the headline numbers to get to the ‘why’, Excel falls short in several key areas:

• Model maintenance headaches: in a 50-tab reporting workbook, any change to the layout can be very time-consuming (and often error-prone). I frequently encounter client reporting workbooks riddled with errors because one sheet has a misaligned row, which results in an incorrect aggregated summary.
• The dreaded invisible F2 edit: how many times have you spent hours pouring over an Excel workbook trying to figure out why the individual tabs don’t agree with the summary, only to eventually discover that someone has keyed in a manual F2 edit in a cell?
• Distributabilty: so you have built this all-singing, all-dancing Excel reporting pack, but it’s 70MB and cannot be shared via email. You also realise that some information needs to be segmented so that specific users can only see select slices of the data. These issues usually mean that multiple Excel models must be maintained, amplifying the risk of error and potentially compromising data integrity.
• Limits on row numbers: Excel’s sheet row limit has increased to one million in recent years. While this sounds more than adequate, you can easily exceed this limit if you include transactional data. Housing data in this way within Excel will usually result in slow, large file-size models.
• Usually dependant on one key user: there is typically only one key person who knows how to run and maintain a reporting model. Therefore, reporting quality, outputs, and cycle time rely to a worryingly large degree on one individual.

The need for structured, robust, and reliable BI has mushroomed in recent years. As a result, dedicated BI platforms like PowerBI, Tableau, Qlik and ZapBI have evolved to address these shortcomings and provide analytics visualisations and end-user self-service reporting that goes far beyond Excel’s capabilities.

Key obstacles to getting good BI Master data

Many finance professionals underestimate just how unstructured their data is. I often hear clients say: “Yes, but we use NAV/Dynamics 365, so our data is really good”. They often fail to understand the inconsistencies across the company in how transactions are coded or recorded by staff.

These inconsistencies make life difficult when you need to connect transactions across different platforms. For example, say you want to connect salary data for an employee from an HR system with data in a time-recording system. The employee ID is, say, PCULLEN250 on the HR system but CULLP on the time-recording system. This is just one example of the data-mapping tasks that must be undertaken for BI to succeed.
I have seen this to varying degrees in every BI project I have delivered because, for many years, siloed teams have had their own ways of doing things. They simply didn’t realise that there would be a future requirement to bring all this data together at a transactionally-connected level.

Historical processes or ways of working

The ways in which your teams have historically coded transactions on source systems will almost certainly present challenges in initially setting up your new BI platform.
I once worked with a ship management group with 1,000 ships under their control. Management wanted to get to ‘vessel profitability’, and we knew that cost allocation would be a challenge due to the complexity of the company’s operating structures. However, we were surprised to find that revenue for each vessel wasn’t available from the ledgers because the company issued just one monthly invoice to each carrier, even though some had more than 50 vessels under management. Furthermore, payroll costs for vessel crews were recorded by office location, not by vessel. Both of these historical processes gave rise to significant re-analysis work and new process design to enable the required analyses.

Similarly, one healthcare client wanted to understand their profitability by treatment type. They believed that everyone across the more than 100 clinics they owned used roughly the same few hundred treatment type codes. In fact, there were over 6,000 live treatment codes in use and in some instances, clinics could even create their own codes at will. So expect to change some of your ways of working as a result of embarking on a BI implementation.

How far back to go?

Once it becomes clear to key stakeholders just how much insight a good BI implementation will bring, there is typically a desire to have as much history loaded into the model as possible. This is often the case where the company is private equity-owned, or a sale is planned.

My advice here is the old 80/20 rule. Yes, it might be nice to see this new level of insight going back five years. But if your company is one of those where a lot of re-analysis will be required, you have to ask: is it worth it?

I instead recommend that older historicals should, where possible, only be incorporated in aggregate. You should then ensure that the new data processes are designed
and implemented so that future analytics are both robust and reliable.

How often is too often?

When implementing a BI platform, the next consideration is how often the data and outputs should be refreshed. It’s tempting to think: “Great, I can see what the sales team are doing every morning and then follow-up to discuss what’s going on”. However, this approach can quickly create a situation where staff have to spend time each day figuring out what just happened. And this, of course, can lead to ‘paralysis by analysis’.

Be judicious about how often BI data should form the basis of a trading or operations conversation, and otherwise use it to indicate the company’s direction of travel.

Introducing a new performance management BI tool will initially strain your executives and managers as they sift through a deluge of new and revealing information. This takes me to the following consideration: the need for culture change if a BI solution is to work correctly.

Warning! Culture change approaching

Imagine you are a sales or production manager, and you wake up to a new, live, web-based BI portal that shows everyone in your organisation where things might not be going so well on your patch. Senior management must avoid using the BI solution to shame or berate colleagues. Instead, it should be seen as a tool to identify opportunities and enhance performance across the business. Tread carefully here and avoid the ‘big bang’ approach of rolling out BI. You want your teams to embrace this new way of working, not run away from it or, worse still, seek to discredit it.

With all this new performance management data at your fingertips, you may wish to consider redesigning your legacy compensation and bonus systems to ensure that these insights drive the right behaviours across the organisation. Embedding a robust BI solution in your organisation can be the catalyst for undoing the traditional silo mentality that can arise when different functions perform to their own narrow targets.

Factors affecting implementation speed

The following four issues will affect the length of time it takes to build and roll out your new BI platform.

1. Poor data mapping: it is critical to understand how different naming conventions are used across your systems. You should conduct a thorough data-mapping audit to ensure that independent systems can be bridged on common field names (by employee ID, customer ID, or product ID, for example). Doing this during the development of the BI solution is time-consuming, but products like Caragon Flex can make the process much more manageable.
2. Organisational readiness: prepare your team for the effort required to clean up your data and, more importantly, how this information will be distributed and reviewed once it is live. Having a new suite of detailed analytics can be overwhelming for data consumers if it is not clearly understood what it will be used for. Also, inform your colleagues that they are not expected to understand every data point that surfaces in the reports.
3. Absence of a project champion: projects that should take weeks often take months due to the lack of an internal project champion. It is vital to appoint one and empower them to ‘herd the cats’ to ensure the project is delivered on time.
4. Unclear output requirements/moving targets: consider what you want to get out of the new BI platform and be ruthless in identifying the key reports and key performance indicators you will need at the outset. Solution providers will typically build a proof-of-concept model to illustrate the art of the possible. This is a good time to agree on the minimum requirements for Phase 1 – but don’t bite off more than you can chew.

Some processes must change

As the earlier examples show, digging deep on data to build robust processes across multiple systems will invariably highlight process weaknesses that, if not remedied, will compromise the integrity of any BI platform. Therefore, it is essential to understand at the outset that go-live and the ultimate success of the project will be contingent on staff being adequately trained in the new ways of working.

This might, for example, mean retraining payroll staff on payroll coding so that the correct costs are tagged to the relevant activity. Similarly, invoicing processes may need to change to ensure that revenue can be appropriately tagged to achieve the desired level of reporting granularity.

You should also introduce tighter controls on crucial data fields across your systems (customer codes, product codes or employee IDs, for example). In my experience, this is best achieved by having a data governance standing group, to which all data changes (or new data field creations) must go for approval and communication to other potentially affected users.

In conclusion

A BI implementation is an exciting journey for a company. To get the most from it, here are my top four tips:

1. Appoint a data champion and BI steering committee to ensure the project both gains and sustains momentum, and the business is prepared for what’s coming.
2. Take the time to fine-tune your data mapping processes.
3. Phase your BI roll-out in bite-size chunks to avoid overwhelming the organisation.
4. Create a sense of ‘new frontiers’ within the business as it embarks on its data-empowered journey.