Give me the master keys

Posted on May 21, 2013 by Lee Hawthorn

For the past several years I’ve been a keen proponent of the OLAP approach to deliver Business Intelligence.

Cube

We have different models to work from but I can sum it up in terms of :

  • Extract
  • Transform
  • Load
  • Query

By pulling the data from the core system we can be assured that we won’t impact it beyond the initial extraction (which is often scheduled off-peak).

The next phase to transform the data is sometimes essential.  The data in the source systems is difficult to query directly.  The complexity can be reduced in the transformation phase.  At this point the data resides in a Data Warehouse (DW).

The transformation can include turning some of the DW into a number of multi-dimensional cubes.

We can use the cube to carry out our own analysis without having to involve IT.   Queries are fast and reliable  All good so far?  Well, yes and no.

If you get this far it’s likely you’ll soon have multiple cubes.  Don’t get me wrong, I use cubes every day in my analytic role.  They are very powerful.

The difficulty comes when we need to join 2 cubes.   Cubes are often designed along functional lines e.g Sales, Finance, Procurement, Marketing.

The value of the data can be increased on a huge scale if the data within the functional areas is linked.   Think about some of these basic questions:

  • How does the marketing effort affect Sales?   How effective are our campaigns?
  • Why is the stock so high?
  • Why are the lead-times increasing?
  • Which products will our customers likely buy next?  Which customers will churn?
  • What’s eating free cash-flow, how will it impact on EBITDA next quarter?

To answer these questions we often have to link the data.   It’s not easy to link cubes due to their multi-dimensional aspects.  Instead we have to query the DW or worst case, the source system.

When I talk to BI developers about this conundrum they say the cube needs to be remodeled.   Sometimes this is correct but many times the cube shouldn’t be changed at all.   The next worst thing from having no cube is having a cube that is difficult to use.

With the latest in-memory tools from QlikView, JasperSoft, Microsoft and many others it’s possible for non IT workers to write cross functional queries on the DW that are just as fast as those queries run against cubes.

To really benefit from these cutting edge tools we need the Master Keys.

We use keys to join the data.   Think of a key as the look-up value in VLOOKUP.

I once read that the customer is king in business.   This naturally means lots of analysis is centered around the customer.   I find it odd that in many organisations the customer exists in many different parts of the business each with a record that is different to the other.

We see the customer in the Sales cube.   In the Marketing cube we see Leads.    Some of these Leads will become Customers but as the Customer isn’t present in the Marketing cube it’s almost impossible to link the 2 two objects.    We go to the DW and find the customer key isn’t present in the table.   This means we have to infer a fuzzy key using the address and possibly name.   Far from ideal.

What we really need is the Master Customer Key that represents a single view of customer that can be shared across the different areas.

Master Key

At the time when source systems are designed it’s often the case that no one is thinking about analytics such as this nor master sources.     Data modelers need to be asking questions such as :

  • How do leads relate to customers?
  • Is a lead created for an existing customer?
  • How does Procurement relate to Finance?
  • How does the Customer relate to Finance?

When you have the Master Keys your analytical capability will be boosted.

Until then have a read of my post on Fuzzy Lookups.

As always, please feel free to comment or share your experience.

Posted in Analytics | Tagged , , | Leave a comment

Performance management levers with CRM

Posted on January 21, 2013 by Lee Hawthorn

Why CRM on a performance management blog I hear you say! Well, think about the performance management of a sales force. We frequently find this area of the business has little solid facts available other than those captured on phones and often scribbled down in a paper diary.

paper diary

CRM (Customer Relationship Management) is a wide subject. It can be summed up as the software and processes that allows the sales and biz dev teams to manage their activity such as selling to customers, managing leads and managing relationships.  CRM software can be integrated with existing back office systems to avoid the need to re-process data, thereby, increasing efficiency.

From my perspective the benefit of CRM comes from the facts that are provided, we can analyse leads, conversion rates, targets, and sales fulfilment. These facts can be used to evaluate performance and aid decision making. facts

CRM & Planning is like bread & butter

Financial pro’s have a habit of talking to the business in our Financial language. For instance, we ask for the £ Revenue forecast. In some companies the £ Revenue can be a difficult figure to calculate due to recognition rules or inter-co sharing.

Would it be better to talk the language the Sales team understands? Ask for the Conversion rates, Opportunity levels and their Temperature or other revenue drivers that are applicable. It’s harder for managers to hide behind speculative or biased forecasts when the £ amount is represented along with the revenue drivers.

With CRM it’s possible to back calculate the £ Actual to the originating measures. You can use this calculation to build business rules that connect the CRM measures with the Planning system.  Each time you need to update the forecast just refresh the data from the CRM system.  No need to open Excel.

Variance Analysis

Let’s move on to the task all Financial pro’s perform at some point in their lives - Variance Analysis.

Have you ever felt that you don’t have the full picture?  I’ve seen time after time a variance explanation that explains the movements (the what) but doesn’t tell the user the reasons (the why). “10% up on last month due to higher sales activity”

Would it be better to say:

“10% up on last month due to increase of £100K opportunities at stage 4 with a 2% increase in conversion rates. Looks to be a 1 off as stage 1 opps are static.”

Biggest winners are the sales team

CRM can have a huge impact on the sales team. If CRM is delivered in the right way the sales people will be spending less time on administration and more time selling. Leads can be managed in smart ways that maximises the chance of conversion.  Contacts can be searched, called, managed, information can be shared, collaboration is easier, business rules can replace the intervention of team leaders that is often required to close deals.

Challenges

The challenge with CRM depends on your sales force make-up. The requisite skills for selling don’t always include technical or analytical aspects. This doesn’t bode well for CRM as it can result in resistance to change with the users not seeing the benefits up front.

Often the sales team believe that their sales performance (and bonus!) will drop if they adopt the CRM system. Under these conditions, CRM is still possible, but more time needs to be spent capturing requirements and involving users early on.  Identify evangelical users !

This brings me on to a second challenge, active user participation. In the CRM context, this means bringing sales people out of service. If you have 100 sales people, this isn’t an issue, but if you have a team of 30, it can be a problem.

Biggest Success Factors

One of the biggest success factors for CRM is user adoption. Based on this the system delivered should be aimed at the sales teams and not the office. The User Interface (UI) is one of the most important aspects, followed closely by the quality of data. With modern CRM packages it’s possible to cover all user requirements in a way that doesn’t give a complex user interface.

CRM Packages

There are hundreds of different CRM packages. You’ve probably heard of the most popular one, Salesforce, but there are many others. The experience I have is with Microsoft Dynamics CRM. From an analytical perspective I like Dynamics as I can easily get at the data using tools I’m already familiar with, such as SSRS, SQL Server, Excel. The other benefit of Dynamics is that it can be purchased in the form of SAAS (software as a service) or you can deploy an on-premise version, the latter being more suitable for large deployments with more complex requirements.

Here are some links to CRM sites if you want to learn more about CRM.

Feel free to ask any questions or comment on your experiences.

Posted in Analytics, CRM, Performance Management | Tagged , , | Leave a comment

Analysing sales drivers with Python

Posted on December 1, 2012 by Lee Hawthorn

Since I started my journey 18 months ago to learn about how statistics can be applied in the business context I’ve come across various platforms and applications that make this easier.  Despite this I’ve not yet found a clean way of integrating our Data Warehouse data to statistical apps.  I’m still searching but in the meantime let me introduce you to Python.   Python is a very popular, easy to use, and open source programming language that is used all over the Internet.

This post covers a common business scenario where there is a need to understand how various sales drivers impact on the level of sales.

Continue reading →

Posted in Analytics, Coding | Tagged , , | 2 Comments

Self Service DI Fuzzy lookup

Posted on September 9, 2012 by Lee Hawthorn

It’s an unfortunate fact of life that dealing with data from third-parties is not always straightforward.

Take the example where you have a list of customers and a third-party is selling you a list of potential sales leads. Before you pass these leads into the CRM system you want to ensure that non of these leads are customers already.

This would be straightforward if the way customer details were stored was standard.  As we know, it isn’t standard.

For example, in the customer record we have a customer :

Bill’s Builders Limited, Arlington Ind. Park, Chester, Cheshire, CH2 3RT

This customer is also present in the lead data :

Bill’s Builders Ltd, Arlington Industrial Park, Chester, CH23RT

Without eyeballing each sales lead it’s difficult match these records.  Thanks to our resident SQL Dev, I was introduced to SQL Server Integration Services (SSIS) a while ago.

SSIS is a data integration tool bundled with SQL Server. Within SSIS we have a tool called Fuzzy Lookup,   Fuzzy Lookup is like Excel’s VLOOKUP on steroids.  With Fuzzy Lookup we can specifiy similarity levels and other advanced attributes to match across many columns. I’ve been using SSIS quite a bit lately to clean data, however, it’s more of a developer tool hence many of you may find it unwieldy.

Fear not, the folks at Microsoft Research have developed an Excel add-in that gives you the features of SSIS Fuzzy lookup right on the Excel worksheeet.

You can download the add-in here :

http://www.microsoft.com/en-us/download/details.aspx?id=15011

If you want to read about the direction Microsoft is going with data cleaning in general you can find a wealth of material here :

http://research.microsoft.com/en-us/projects/datacleaning/default.aspx

There’s good documentation with the download so I won’t repeat the instructions here.

This really works.  A few months ago I heard that our Biz Dev people had access to 10K leads but many of these leads were customers already. I offered to de-dup the records to help them out.  Within 1 hour the job was done including training up the marketing manager to use the Excel Add-in in the future.

Is this a sign of Self Service DI arrriving?  or is it a defensive strategy to protect the Office cashcow from Google Docs?

Posted in Excel | Tagged , | Leave a comment

Self Service BI, data utopia or same old dashboard?

Posted on September 8, 2012 by Lee Hawthorn

I had an interesting discussion with the #BIWisdom tribe on twitter last week. The discussion was on SSBI (Self Service BI).

I agree with the principle of SSBI being available to everyone, however, the truth is there are different types of users with differing needs and technical ability.

In my experience, I see analytical users, the people who want the detail. They have some skills from varying degrees to slice/dice/mash-up etc. but the over-riding characteristic is that they have an inquisitive mind. These people have been line managers, VP, Directors. The grade doesn’t really come into it. I think these people will benefit most from SSBI. These people are in short supply.

The second type of user is the person that wants the information on a plate. They don’t see the value hidden in their data or at least they don’t have the will to do it themselves. They usually want someone else to give them the insight. These are 2 extremes of course, many people lie somewhere in between and I think it’s these users that will benefit from the plug and play types of dashboards from QlikView and Microsoft’s PowerView.

I had an interesting discussion with a Sales Director recently. I asked about her requirements for management information for her and her team of business development/sales managers. I gave her a choice of reports, dashboards, pivot tables. In the end what she wanted was the key performance facts delivered to her Blackberry. She also wanted her staff to receive the same personalised facts on their Blackberry. She specifically didn’t want any of her staff to be diverted from the main task of selling/account management.

You could argue this sales mentality is out of date and the sales team should be spending time analysing the customer records/account facts. My view is that it depends on many factors, such as the widgets being sold and the level of relationship management, as to how much fact based analysis is performed. However, I can never see a sales manager creating a DAX based Power Pivot model. Although, I can see them navigating a dashboard and slicing data.

How do we get the data from the database table to a Dashboard? It is this activity that is under-flux.  The nature of data source is also changing, we have local data, unstructured local data, big data, cloud data, external data.

It is far from perfect. For instance, PowerPivot/QlikView are great tools if you have good quality data sources. Of all the organisations I’ve worked with, the data sources are not usually of a high quality in the context of management information.  We even see this with modern tools such as DynamicsCRM.

The issue is that IT see the data source as being top quality for the purpose it was designed for i.e. transactional processing. I don’t think they appreciate someone like me telling them the data is poor for analysis. So the answer to this problem lies with ETL/Data integration.

At the moment ETL/DI is not self-service. I want a self-service ETL tool, although, I think many things need to change in the minds and world of IT for this to happen. Even if I had a tool today I don’t envisage the IT director letting me poke around the live OLTP system and I’d probably agree.

SSBI is a deep subject. There are many stakeholders at different levels. One thing is certain, the tools are getting better and easier to use to such an extent that brings them closer to the end-user and enables fact based decision-making at levels never seen before.

How we join the dots from dashboard to source data has some way to go before we have data utopia.

What do you think?

Posted in Uncategorized | Tagged | 2 Comments

Understanding data

Posted on September 8, 2012 by Lee Hawthorn

This year I’ve been developing my statistical skill set in order to add another tool to my box of analysis tricks.

I decided to apply my new skills recently in the area of time series forecasting.

Now I recall that the 1st step before carrying out any statistics modelling is to visualise the data.  So I fired up Excel and created a quick chart.

Weekly Ticket Data

The trend line was going to be difficult to extrapolate at the weekly level.   There seems to be some kind of alternating pattern though.  To explore this further, I grouped the weeks to months and created a new chart.

Data grouped to month

Bingo.  We can see the pattern in the chart and it’s easier to see a pattern in the numbers at this aggregate level.   All I needed to do was to extend the pattern.  No statistics were required.   Additionally, this alternating pattern illustrated a problem with un-balanced processes.    By balancing out the process we can increase overall throughput.

So in summary, before you dive into the statistics tool box apply your business knowledge and visualise the data set from different perspectives.

If you want to follow me on my statistical journey check out Udacity.

Posted in Uncategorized | Tagged | Leave a comment

Fact Based Management – Projects

Posted on July 22, 2012 by Lee Hawthorn

Many things need to occur to ensure any project is successful.

In this post I’ll discuss two things that are particularly important.

1.  Describe the measures of success

When setting up the project, objectives are usually set to describe the problems the project will solve or to describe the benefits it will provide.   Compliment these objectives with measures of success.  On a BI project this might be : Reduction in hours spent producing reports or for a change management project try and describe numerically what is being changed such as : Hours of process time.

The benefit of doing this is two-fold.

First, it will help the sponsor and approver decide if the project is worth the investment.  I’ve seen some instances when a popular sponsor/executive has promised some benefit/improvement to their boss which leads to a project that is hard to resist.   When expectations are built without good facts to base them on it can be difficult to back down.  With a clear mind be sure to check the objectives and measures of success will deliver the expected benefit. Don’t assume the benefits will occur just because the executive wants it.  It’s easier to challenge a project with a good solid argument if this is done as early as possible.

Additionally, during a project when the inevitable scope creep occurs, before accepting the scope creep you can check how this impacts the objectives and measures of success.   I’ve seen many instances where scope creep has been accepted and the sponsor/stakeholders don’t realise until the end of the project that a key deliverable is no longer in scope.

2. Subject Matter Experts

If the project is to change an existing business area.   Nothing can be more important than bringing in someone from the area that has expert knowledge of the area and the existing process. These people have usually been around for a while and are well known in the team as other people often go to this person for support.   This person is a key change agent.  Without their support it will be harder to make the change in the wider area.

For all the love in the world the project manager won’t have the knowledge to make decisions without the support of the subject matter expert.

Likewise, if a new IT system is being introduced you need someone with expert knowledge of the system and how it will interact with the business process.

If the system expert can only be found outside the organisation don’t be tempted to skim the time of the consultant too low. You might save cost in the short run but worse case the project can be put at risk or more usually key objectives may not be delivered satisfactorily.

I hope you’ve found this post useful.  Please share any experiences you have on projects.

Lee

Posted in Projects | Tagged | Leave a comment

Time management (off topic)

Posted on July 16, 2012 by Lee Hawthorn

I’ve been on an organisational journey since reading the book Getting Things Done.

I thought I’d give an update of the things I’ve changed that has yielded the biggest benefit to my personal time management.

Email

Previously email had been causing real disruption to my day.  The effect of working in different areas of the business meant I had 3 in-boxes.   I tried filtering the messages into different folders depending on the topic.  I also tried the single folder approach but this didn’t work too well either.   My inbox resembled the twitter stream at times.   Something had to change.

I settled on the Inbox Zero system described by Merlin Mann.

When email arrives, if I’m around I manually filter it into 3 folders.

Do
Emails in this folder are the ones that are high priority and easy to do now without too much disruption to the day.  This folder forms a to-do list for the day.

Defer
These emails are the ones that require action and need to be be planned to avoid causing disruption to whatever is being done now.

Delegate
These emails are the ones that need action from others before completion.

Any other emails are deleted.

The benefit of this system is that when my boss says “What are you doing and what have you got planned next week?”  I can take a quick look at the folders before promising my time away.

Another benefit is that it’s so simple that it works for most situations.  Once you’ve mastered your own organisational performance push it out to the team and boss and before you know it, you’ll be fostering a high performing team.

Desk Drawers

My desk drawers we’re a mess.  Full of old process notes and reports that had become junk with the passage of time.   I was far too busy to organise my drawers so the solution was to get rid of them.  Instead, I opened up Microsoft OneNote that is bundled with Office.  This is a great pieice of software that enables me to categorise information and data from any source.   It even OCR’s text so that it can searched upon.

Since making these changes  I’m able to get the lists of things out of my mind.    This enables me to focus on getting things done and keeping some spare capacity in my brain for  thinking.

Posted in Uncategorized | Tagged | Leave a comment

Don’t forget about SSRS

Posted on June 3, 2012 by Lee Hawthorn

Do you know about the bundled reporting tool that comes with SQL Server? SSRS (SQL Server Reporting Services).

Well, I’ve been using this lately more than Power Pivot. The reason? Quite simply, the data being queried is already present in an Analysis Services cube and there has been no need to mash-up the data with anything else. As much as I like working with DAX there is simply no point of moving the data from the cube when we simply query it in place via SSRS.

In my place of work I have a very demanding sales director who naturally wants to spend his time managing the sales team. He is not interested in clicking slicers or analysing data. He wants the report delivered on a plate.

In SQL Server 2008 R2, SSRS is a very rich report authoring tool and it allows me to send the report directly to the sales directors’ inbox just as he wanted.

There are some limitations when using Analysis Services as a source. The primary one being that measures must be placed in columns and attributes in rows.  This means you lose the benefit of multi-dimensional cross tab reports that Analaysis Services provides.

It’s normally possible to work around this problem by re-structuring the data source of the report but it is here that you have to get your hands dirty with MDX. Personally, I don’t mind this as once the report is created you can kind of set it and forget it.

In the past I’ve been spoilt by building reports in Excel and querying Analysis Services using the ADO cube set object. The cube set object doesn’t have this limitation and with VBA it’s possible to manipulate data in any way you want.

This situation got me thinking. If I have a table of data, let’s say it’s a classification table of accounts (A) that summarise the detailed accounts in the accounting system. It’s relatively easy to move this data into SQL Server. Then, as long as we’re querying the account system via SQL we can simply join this data to table A.

Now of course this leaves the question about how table A is administered. Enter Lightswitch. Lightswitch is a development tool from Microsoft that is aimed at business users and enables users to rapidly create business applications. I bet it’s possible to create an admin tool very quickly that allows updates to be made to the SQL table.

In conclusion, the more I work with SQL Server and PowerPivot I’m beginning to think in some cases the traditional BI approach is dead.

Why spend 6 months working on a BI project when you can work in a more proactive way to support the business with facts when they need them? Now I know I’ve oversimplified ETL in this discussion, however, even with ETL I still think this type of approach is more agile.

What do you think?

Posted in Analysis Services | Tagged | Leave a comment

Rank and STDEV in Power Pivot 2012

Posted on April 29, 2012 by Lee Hawthorn

I was prompted to write this post based on a question in the SQL forums.

There is a single table of data containing thousands of transactions by Customer, Year, Origin, Type.  The measure is Sales.

The output needs to show the Rank of each Customer  based on Sales for the subset of data defined by the Year, Origin & Type.

Without Power Pivot this would be quite painful to create but with Power Pivot 2012 we have the new RANKX function. Continue reading →

Posted in Power Pivot | Tagged , | Leave a comment