Analytics are for everyone !

Analytics are for everyone! Well, not building analytics, no. That needs a high level of expertise in statistics, machine-learning, optimization, programming, database skills, a healthy does of domain knowledge for the problem being addressed and a pretty wide masochistic streak too.

Using analytics, now that is for everyone, or at least it should be. We all use analytics, and, I think, the best examples, we use without thinking about just how complex it is.

Is there anyone out there that hasn't used an electronic mapping service (GPS) for directions? Even ignoring the electronics, these are remarkable pieces of engineering! An extensive, detailed database of road systems and advanced routing analytics to help you find the best route from A to B without sending you backwards down one-way roads or across half-finished bridges.

Perhaps you're thinking it's not that hard? Could you build it? What if I got the data for you? No? But you can use it right? They are not perfect, mostly I think because of data cleanliness problems, but they are close enough that I don't travel far from home without one.

More examples. Anyone used a search engine? How about an on-line weather forecast? How about web-sites that predict house-values? Recommendation engines like those used by Amazon and Netflix? All heavy analytic cores wrapped in an easy to consume, highly usable front-end.

These are, I think, among the exceptions in analytic applications - good analytics AND good delivery.

I talked about pseudo-analytics in a recent post: shams with no basis in science wrapped in a User Interface with the hope that nobody asks too many questions about what's under the hood. This is not good analytics.

Unfortunately even good analytic tools get under-used if they have not been made accessible to the poor people that have to use them. Spreadsheet tools probably top the list for unusable analytic applications: unusable that is by anyone except the person that wrote them. Sadly though, I have seen many examples both in reporting and applications where so little effort was put in to User Experience that any good analytics is almost completely obscured.

Building new analytic capability is a highly skilled job. Delivering analytic results in an easy to consume format so that it gets used is also a highly skilled and, frankly, often forgotten step in the process. After all we do build analytic tools so that they get used. Don't we? Sometime I wonder.

Next Generation DSRs - Analytic power !

To handle real Analytics (see my recent post Reporting is NOT Analytics) you need real Analytic power. BI tools are based on the language they use to interrogate the database (typically SQL) and with no library of analytic tools - it's not nearly enough.

We use SQL (Structured Query Language) to query relational databases like SQLServer, Oracle, MySQL and Access. SQL is a great tool for handling large quantities of data, joining tables, filtering results and aggregating data. However, SQL's math library is only sufficient for accounting (sum, product, division, count) and while I do know it can do a few more things, it's not enough to be useful for Analytics. Even getting it to calculate a simple correlation-coefficient is a big challenge. Want to build a simple regression model? That's just not going to happen in base SQL, we need something designed for the task.

R, SAS, SPSS, Statistica, and a good number of others, are the real deal and the difference between any of them and what you can do in SQL (or Excel) is vast! With these tools it's no longer a question of "can you build a regression model?" now it's "which particular flavor of regression do you need?". What! There's more than one? Oh yeah!

I'm not getting into which analytic tool is the best. I use R, and that's what I'll talk to, but I have good friends, analytic-powerhouses who insist on using SAS or SPSS. These tools have different strengths and weaknesses and within the analytic community a lot of time, blog posts and misinformation go into arguing the relative merits of one vs. another. My take is that for most business-analytic purposes any of them will get the job done. The one you choose should be driven most heavily by your ability to get the analytic tool working against your data.

The problem is that these analytic tools do not generally reside in the same space as your database or BI tool, so you spend a lot of time interfacing data between systems. It's slow, sometimes very slow, and requires replication in your resources.

In recent years many database and BI tools have started offering integration with statistical tools (Oracle, SAP Hana, Tableau, Spotfire, MicroStrategy). The ideal here is in-database analytics where we run the complex stats in-tandem, indeed in the same memory space as the database. That is very attractive but I would look very carefully at the depth of integration offered before getting too excited. In some cases, I think, vendors have done just enough to tick the box without making it truly useful. As examples:

• One vendor limits the transfer of data between database and R to simple table structures. Now, imagine running a regression model. What goes into the regression is very likely a simple table - check! What comes out is anything but: it's a complex object combining multiple tables of different dimensionality and named values (like r-sq). We need this data to determine the validity of the model and make future predictions. Force me to return just one table structure and I must throw most of the information and capability away. Before anyone asks, no, this is not unique to regression models.
• Another vendor has integrated R into the reporting layer. This is relatively functional as long as the data you want to work with can be generated in a report. If you need very large amounts of input data you may well exceed reporting limits. If you want to build a separate model for each product in your database, you may have to run the report separately for each one.
• Standard R was not originally designed for parallel execution (though you can get around this with a little coding help). Current processors (CPUs) even on low-level laptops are multi-cored. Servers routinely run more cores per CPU, more CPUs per server and we want to scale-out across multiple servers. A BI offering that only offers single core R execution is wasting your resources and time.

Bottom line, to do real Analytics, you need real Analytic tools. But even the best tools must be able to get at the data to be useful. Choose carefully,

Next Generation DSRs - Reporting is NOT analytics

I've written a number of posts now on the next generation of Demand Signal Repositories. DSRs are the specialized database and reporting tools primarily used by CPGs for retail Point of Sale data.

So far, I've looked at the challenges (and big opportunities) around handling the large quantities of data involved: better database technologies, scale-out platforms, true multi-retailer environments, effective data blending and dramatic simplification of data structures.

Taken as a whole this get's the necessary data into one place where it is relatively simple to overlay it with the BI or analytic tools of your choice and still get good performance. This is the starting point.

Now, we can get to the fun stuff, Analytics. Let's start by addressing a widespread misunderstanding

Reporting is NOT analytics

I've blogged on this before, actually one of my very first blog posts, but it bears repeating and extending from the original

Reporting is about "what happened"; Analytics is concerned with "why?", "what if?"and "what's best?".

You need reports. Hopefully they are well constructed, with appropriate metrics, good visualization and exception highlighting. Perhaps they are also interactive so you can drill-down, pivot and filter. These are useful tools for exploratory "what happened" work, but, almost exclusively, reports leave it up to the reader to construct the "why".

Great reporting can pull together facts that you think are related for visual inspection (e.g. weekly temperature and ice-cream sales by region). Perhaps you can see a pattern, sort of, but reports will not quantify or test the validity of the pattern that's up to you, the reader, to guess at.

Even great reports can't help you much with more complex relationships. In reality, ice-cream sales are also dependent on rainfall, pricing, promotions, competitor activity etc. Who knew? Well we all did of course, but there is no reasonable way to visualize this in a standard report. Want to predict sales next week given weather, price and promo data for all products in all regions? Your going to need some good analytics.

You need Analytics too. In some cases, basic, high-school, math is all you need. In most, it doesn't even get you close to the 80% solution beloved of business managers. "Winging it" in Excel, Access, PowerPivot etc. can give you very bad answers that are seriously dangerous to your success and/or employment.

Want to understand and predict the impact to sales of promotions, pricing or weather events? You need Analytics for that.

Wan't to know where you can safely reduce inventory in your supply chain while increasing service level? You need Analytics.

Wan't to alert when sales of your product are abnormally low? Analytics!

Want to know how rationalizing products across retailers would impact your supply chain? Yep, Analytics.

Want to know which shopper demographics are most predictive of sales velocity? I think you get it...

If your business question is something other than "what happened" you need Analytics.

Visualizing Forecast Accuracy. When not to use the "start at zero" rule ?

I recently joined a discussion on Kaiser Fung's blog Junk Charts , When to use the start-at-zero rule concerning when charts should force a 0 into the Y-axis.  BTW - If you have not done so, add his blog to your RSS feed, it's superb and I have become a frequent visitor.

On this particular post, I would completely agree with his thoughts was it not for this one metric I have problems visualizing, Forecast Accuracy.

Forecast Accuracy is a very, very widely used sales-forecasting metric that is based on a statistical one, so let's start there.  

The statistical metric (Mean Absolute Percentage Error) looks at the average absolute forecast error as a percentage of actual sales.    Some of the errors will be positive and some negative but by taking the absolute value we lose the sign and just look at the magnitude of error.  (We handle optimism or pessimism in the forecast with a different "bias" metric).  

There is occasionally heated discussion in the sales forecasting community about exactly how this should be calculated but let's save that for another day as all forms I am familiar with have the same properties with regard to plotting results.

• perfect forecasts would have no error and return 0% MAPE, this is our base.
• there is no effective upper bound on the metric

If we were to look at this across a range of product groups (A thru K) it might look something like this.  The Y-axis is forced to start at 0 and the length of the bars have meaning, Product D really does have almost twice the error rate of product A.  This plots out very nicely, it's hard to misunderstand  and the start-at-zero rule certainly does apply.

Now convert MAPE into a Forecast Accuracy with this simple calculation.

Forecast Accuracy = 1 - MAPE

I can only assume this metric was created in the sense of "bigger numbers are better".  It's in widespread use, it's part of the business forecasting language, and no, I can't change it.  As you can see below, perfect forecasts are now at 100% and there is no lower bound on the metric, it can easily be negative.

MAPE	Forecast Accuracy
0%	100%
20%	80%
40%	60%
60%	40%
80%	20%
100%	0%
120%	-20%
140%	-40%
160%	-60%

This causes me a problem.  Check out the chart below: this is the same data as before but now expressed as Forecast Accuracy rather than MAPE in a standard Excel chart.  Excel is trying to help (bless it) and put the 0 value in without my help.  Work in supply-chain and you will see a lot of these.

The zero value has no special meaning on this metric, so starting at 0 is very misleading:  80% accuracy (20% MAPE) is not twice as good as 40% accuracy  (60% MAPE).

Allowing the minimum of the y-axis to float  does not solve this either (below)

I really don't know what this is trying to tell me... some product groups are better than others perhaps ?  Certainly, relative size is meaningless.

"Abandon it" you say  "go to a line chart".  Line charts often have floating axes and yes they do not emphasize relative size nearly as much as a bar-chart does (below).

Perhaps it's less confusing/misleading than the previous charts but I still don't like it. because there is data I want to compare relative sizes for (the MAPE) and line-charts seem most useful when trying to show patterns.  I have no reason to expect a useful pattern to form from product categories: I just sorted then alphabetically.

My thanks to the contributors on Junk Charts for helping me clarify my thinking on this.  I don't know that there is a great answer but as it's one I run into all the time I do want to find a better solution.  (FYI - It's just hit me that there are another set of supply-chain metrics for order fill-rates than have the exact same problem)

The best I have been able to do with it so far is shown below, by forcing the upper limit on the Y-axis to 100% and letting the lower limit float, I am trying to emphasize the negative space between the top of the bar and 100%, essentially the error rate.

I'm not entirely happy though, those heavy bars do draw the eye, how about a dot-plot instead ?

You would still have to learn how to read it properly ...

Or how about this?  Inspiration or desperation?  I'm now plotting the bars down from the 100% mark, emphasizing MAPE while still using the Forecast Accuracy scale.  I'm not entirely sure yet, but I think I like it and if I generalize the "start at 0" idea to "start at base" it may even fit the rule.

What do you think?  Which version best handles the compromise between a user's desire to see the metric they know and my desire to show them relative error rates?  Have you a better idea?  I would love to hear it - this one really bugs me !  Can you think of any other examples of metrics where 0 is meaningless?

Recommended Reading: The Definitive Guide To Inventory Management

A little over 15 years go now, I was set the task to model how much inventory was needed for all of our, 3000 or so, products at every distribution center.  Prior to this point, inventory targets had been set at aggregate level based off experience and my management felt it was likely we had too much inventory in total and what we did have was probably not where it was most needed. (BTW - they were absolutely right and we were ultimately able to make substantial cuts in inventory while raising service levels).

I came to the project with a math degree, some programming expertise, practical experience simulating production lines, optimizing distribution networks, analyzing investments and with no real idea of how to get the job done.  The books I managed to get my hands on gave you some idea how to use such a system but no real idea how to build it.  They left out all the hard/useful bits I think.  So, I set about to work it out for myself with a lot of simulation models to validate that the outputs made sense.

I still work occasionally in inventory modeling and I'll be teaching some components this fall, so I have been eagerly awaiting this new book : The Definitive Guide to Inventory Management: Principles and Strategies for the Efficient Flow of Inventory across... by CSCMP, Waller, Matthew A. and Esper, Terry L. (Mar 19, 2014)

Full disclosure here: one of the authors, Dr Matt Waller is a friend and colleague of mine.  He brings an astonishing level of expertise to many areas of supply-chain management and inventory modeling is clearly no exception.  Together Matt and Terry Esper have produced a book that (had I possessed it 15 years before it was published) could have short-cut my inventory modeling project by approximately 6 months.

This is not a long book, not quite 200 pages in fact, but it is no lightweight.  If you just want an overview of the topic you could skip the math, but my guess is that if you do that, you will never really understand.  The math is not particularly hard and it's presented in a sort of hybrid math/Excel fashion that I find easy to follow.  I'll also say that I hit my first "ahah!" moment before I got to page 20.  I won't embarrass myself by telling you what it was but something that had bothered me for years suddenly clicked into place.

Unlike most discussion on this topic, this book looks at inventory modeling from the manufacturer's or supplier's point of view right through to the retail shelf.  They also provide a number of means to estimate components of inventory from historical data so you can assess how well your planning and execution system are tracking to plan: something I was aware of but had never really thought through how useful it could be.  Details on how to conduct your own simulation studies in Excel and an overview to the most commonly used forecasting approaches that feed the inventory models round it out.

It's all here, what you need to understand (and if you so wish, build) a system to optimize your inventory  holding.  I highly recommended it.

Data Visualization - are pie-charts evil ?

I'll be speaking next week at the Supply Chain Management Conference at the University of Arkansas on how data-visualization enables action.   

Good visualization is fairly easy, unfortunately, building bad visualizations that are hard to use, easy to misunderstand and that obscure and distort the data you are trying to present is even easier - many analysts can do it without trying to.



In honor of the event, I'm resurrecting a post I created a couple of years ago "Are pie charts evil or just misunderstood".  I wrote this around  the time I was moving away from a trial and error approach  (and 20 years of trial and error effort does get you cleaner visuals) to attempting to understand why some visuals so clearly work better than others.  

It turns out that there are some great frameworks to help in building better visuals.  Join me next week and we'll talk about human graphical perception, chart junk and non-data ink.

Enjoy !

The right tools for (structured) BIG DATA handling

Here's the scenario: you are a business analyst charged with providing reporting and basic analytics on more data than you know how to handle - and you need to do it without the combined resources of your IT department being placed at your disposal.  Sounds familiar?

Let's use Point of Sale data as an example as POS data can easily  generates more data-volume than the ERP system.  The data is simple and easily organized in conventional relational database tables -  you have a number of "facts" (sales-revenue, sales-units, inventory,  etc.) defined by product, store and day going back a few years and then some additional information about products, stores and time stored in master ("dimension") tables,

The problem is that you have thousands of stores, thousands of products and hundreds (if not thousands) of days - this can very quickly feel like "big data".    Use the right tools and my rough benchmarks suggests you can not only handle the data but see a huge increase in speed.


Let's see just how big this data could be:

If on each day, you collect 10 facts for 1,000 products at 1,000 stores that would be 10 million facts every day (10 x 1000 x 1000) .  Look at it annually , that's 3.65 billion facts every year.  

Is it big compared to an index of the world-wide-web? No it's tiny, but in comparison to the data a business analyst normally encounters it's not just "big" its "enormous".  Just handling basic data manipulation (joins, filters, aggregation etc,) is a problem.  Trying to handle this in desktop tools like Excel, or Access is completely impossible.

As usual, there are better tools and worse tools - you must use a database, but even with a conventional server-based database like Microsoft's SQL*Server, you may have problems with speed.   I wanted to see how speed is impacted, firstly by upgrading the hard-drive and second by using two varieties of column-store databases.  

A couple of relatively simple changes and bench-marking shows a 4100% increase in speed.  If a 4100% increase does not indicate to you that there may be a better tool for the job, I don't know what will.

Running analytics against this data (once delivered from a tool that has joined, filtered and aggregated) appropriately is another challenge that we will get to in a later post.

First a little disclosure: I am first and foremost an analyst: my technologies of choice are statistics, mathematics, data-mining, predictive-modeling, operations-research,... NOT databases and NOT hardware-engineering. To feed my need for data I have become adept in a number of programming languages and relational database systems. I'm most comfortable in SQL Server just because I'm more familiar with that tool though I have used other databases too. Bottom line, I'm a lot better than "competent" but I am not "expert".

Test environment

I built a test database in SQL Server 2012 with 4 tables in a simple "star schema": 1 "fact" table with 10 facts per record and 3 associated "dimension" tables as follows:

The data itself is junk I generated randomly in SQLServer with appropriate keys and indexes defined.  

This represents approximately 8 GB of data.  Not enormous (and as you will see later) perhaps not big enough to test one of the options fully, but big enough to get started and much bigger than many analysts ever see.

I'm testing this on a mid-range laptop, quad-core AMD CPU, with 8 GB of RAM running Windows 7 (64 bit) that cost substantially less than $1000 new. You probably have something very like it sat on your desk.

I then wanted to see how long it would take to take to perform a simple aggregation. My test SQL (below) joins the fact table to both the product and period dimension tables then adds each fact (1 thru 10) for each year and brand. Not very exciting perhaps but a very common question "what did I sell by brand by year".

SELECT Item.Category, Period.Year, SUM(POSFacts.Fact1) AS Fact1, SUM(POSFacts.Fact2) AS Fact2, SUM(POSFacts.Fact3) AS Fact3, SUM(POSFacts.Fact4) AS Fact4, SUM(POSFacts.Fact5) AS Fact5, SUM(POSFacts.Fact6) AS Fact6, SUM(POSFacts.Fact7) AS Fact7, SUM(POSFacts.Fact8) AS Fact8, SUM(POSFacts.Fact9) AS Fact9, SUM(POSFacts.Fact10) AS Fact10 FROM Item INNER JOIN POSFacts ON Item.ItemID = POSFacts.ItemID INNER JOIN Period ON POSFacts.PeriodID = Period.PeriodID GROUP BY Item.Category, Period.Year

Each run was repeated 5 times and the elapsed time for each run averaged to get the results shown below.  While there was variation in run times, this was typically within about 10% of the average for each test.

Baseline

This is my starting point: SQL Server 2012 in its regular row storage mode.   

Faster Storage

This database query is going to need a lot of data from the hard-disk; actually almost all the data in the database.  The hard-drive that came with my laptop was not especially slow but it was clear that it was a bottleneck on my system.  While running this query the standard disk could not deliver data fast enough to keep the CPU busy - in fact the CPU was rarely operating at even 50% capacity.   An option to upgrade the hard-drive seemed to be in order.  ($350 for a 480 GB Solid State Disk).

SQL Server 2012 ColumnStore Indexes

SQL 2012 has a new feature called a Columnstore Index.  Per the Microsoft website:

 "An xVelocity memory optimized columnstore index, groups and stores data for each column and then joins all the columns to complete the whole index. This differs from traditional indexes which group and store data for each row and then join all the rows to complete the whole index. For some types of queries, the SQL Server query processor can take advantage of the columnstore layout to significantly improve query execution times... Columnstore indexes can transform the data warehousing experience for users by enabling faster performance for common data warehousing queries such as filtering, aggregating, grouping, and star-join queries."

To put that in plainer English - for data warehousing applications (like reporting and analytics) a columnar database structure can pull its data with fewer trips to the disk - and that's faster, potentially a LOT faster.  (By the way if you want your database to support a transactional system where you will repeatedly be hitting it with a handful of new records or record changes - this could be an excellent way to slow it down  )

Now adding a ColumnStoreIndex does take a while but it's not exactly difficult.  It's just a SQL statement that you run once:

CREATE NONCLUSTERED COLUMNSTORE INDEX [ColIndex_POSFacts] ON [dbo].[POSFacts] ([Fact1],[Fact2],[Fact3],[Fact4],[Fact5],[Fact6],[Fact7],[Fact8],[Fact9],[Fact10])WITH (DROP_EXISTING = OFF) ON [PRIMARY].

Note: Once the ColumnStoreIndex is applied the SQL Server table is effectively read-only unless you do some clever things with partitioning.  For one-off projects this doesn't matter at all of course.  For routine reporting projects you may need a DBA to help out.

InfiniDB columnar database

Columnar databases are not really "new" of course, just new to SQL Server so I also wanted to test against a "best of breed", purpose-built columnar database.  

Why Infinidb?  From my minimal research it seems to test very well against other columnar databases, it's open source (based on MySQL), will run on Windows and comes with a free community edition.  I actually found the learning curve relatively simple, in fact, as Infinidb handles it's own indexing needs it's perhaps even simpler than SQL Server .  Frankly, the hardest part was remembering how to export 40 million records neatly from SQL Server so they could easily be read into InfiniDB using their (very fast) data importer.

The Results

Here are the (average) elapsed times to run this query under each disk and database configuration.  

So the basic SQL Server 2012 configuration on a regular hard-drive took... 1,535 seconds to run my query.  That's over 25 minutes.  I can drink a lot of coffee in 25 minutes.

Upgrade to the Solid State Disk (SSD) and it runs 460% faster in 5 minutes and 32 seconds.  Now understand that my laptop does not use the fastest connection to this SSD, it's spec says it can handle 2.5 Gb per second.  I believe newer laptops run at 6 Gbps.  That being said at least now the quad-core CPU was being kept busy.

If instead of upgrading the disk we add a ColumnStoreIndex to the fact table, we do even better reducing from 1,535 seconds to 126 - that's over 1200% faster !

So which option should we use?   Both of course!  I can now run a query that used to take 25 minutes in 37 seconds.  That's 4100 % faster than when I started.

Now let's take a look at that InfiniDb number.  (I did not test with InfiniDB before swapping out the hard-drive so I only have data for it on the SSD.)   Surprisingly it was not quite as good as the SQL Server speed with the Columnstore Index .  I talked to the folks at Calpont that develop InfiniDB and they kindly explained that a key part of their optimization splits large chunks of data into smaller ones for processing.  Sadly my 41 million record table was not even big enough to be worth splitting into 2 "small" chunks so this particular feature never engaged in the test.  Still it's almost 3000% faster than base SQL even on this "small" dataset and the community edition is free.  

Based on the success of this test I think it's time to scale up the test data by a factor of 10 - watch this space.

(Check out the following update post for more details.)

Conclusions

My test-bed for this  benchmark was a mid-range laptop with a few nice extras  (more RAM, solid state disk and 64 bit OS) but certainly not an expensive piece of equipment and it managed to handle an enormous amount of data with very little effort.  This opens up possibilities for analyzing and reporting on much more data than was possible previously on your desk.

The implications are not just relevant to desktop tools though or to tools we think of as databases.  Numerous other tools now claim to handle data storage in columnar form (see Tableau and PowerPivot for Excel).

Is this the best tool for the job?  Perhaps, perhaps not: there is an enormous amount of activity and innovation in the database space right now and many. many other software providers.  It's certainly a lot faster for this specific purpose and a major step forward over more traditional approaches.

Look hard at columnar databases to speed up your raw data processing and don't spend any longer waiting on slow hard-drives.   

Business Analytics - The Right Tools For The Job

Whether your analytic tool of choice is Excel or R or Access or SQL Server or ... whatever,  if you've worked a reasonable range of analytic problems I will guarantee that at some point you have tried to make your preferred tool do a job it is not intended for or that it is ill-suited for.  The end result is an error-prone, maintenance nightmare and there is a better way.

We all know when we are pushing it too far - the system starts to "creak".  Symptoms vary but include at least some of the following:

• Model calculations run slowly (if they complete at all).
• Model calculations give, apparently, inconsistent results.
• Making minor changes becomes a major headache
• Errors ("bugs") are routine.  When you need to do a demo, you pray first.
• You have learned to avoid certain operations because the likelihood of success is slim.
• If you must come back to your model/application after 6 months to update it, you feel physically ill.
• and perhaps most importantly, you are far from sure that using your model generates the right results.

This isn't just an academic issue or one of preference.  Poor models can cost real money in lost opportunity or bad decisions that get implemented.  (See this post for a few examples)

So why do we do this ?  I think it's because for many analysts their tool box looks like this.

Why is their toolbox so empty?  In some cases, corporate IT restrictions may make it very difficult to  acquire/install the right tools;  I've been there, it's a real challenge.

For many people though, it just feels easier to take a tool they know well and try and "make it work" than to learn something new.  That's rather like thinking  "I need to chop down a tree... I'll sharpen my hammer" 

Last week, I gave you my nominations for "The Worst use of Excel Ever!"  I could easily cite similar abuses for other tools and over the next few months I will.   

This blog post is the first in a series around using "The Right Tool For The Job".   I'm going to encourage you to add a few more tools to your analytic tool-box and learn to wield them effectively. (Or, alternatively, to at least recognize when you need someone who can do that for you.)  Do you need: 

• A application programming environment ? 
• A database (of varying capability, Access, SQL or perhaps a newer column storage databases) ?
• A reporting tool ?
• An optimization modeling language ?
• A visualization tool ?
• A statistics or data-mining package ?
• A simulation package ?

Which tools do you think are the most mis-used?  What skills/tools should a business analyst consider adding to their toolbox ?  

Business Analytics - The Worst Use of Excel ever ?

Excel is a great tool and I use it a lot.  It's available on almost every business user's desktop and it's highly extensible (with some sensible design) through add-ins and programming but it can't do everything; push it too far and the results can be nasty.  

Here are my nominations for "The Worst Use of Excel ever" awards.

1. Entire applications built in Excel/VBA.  I'll admit it, I have done this: it's expedient and for prototyping it can work effectively, but the more you try to lock down Excel to behave as an application (rather than a general purpose spreadsheet) the more problems you encounter.  At some point you need to crank up a real programming environment with purpose built components, even if it's only to build an Excel add-in (like XLReportGrids)
2. Surveys conducted through Excel/Email.   Build a survey template, email it out to 200 folks and get back...junk you can't use unless you manually sift through each response.  (Yes, I know you can try to lock down the survey spreadsheet, but you can't stop stupidity.  People will copy it, change it, enter incomplete records and it will never be a good substitute for direct entry to a database through a form that handles proper validation.)
3. Trying to join multiple "tables" by extensive use of VLOOKUP functions.  Judicious use of VLOOKUPs actually extends your capability substantially and can help maintain data integrity rather than duplicate data... but, Excel is not a database.  VLOOKUP is very slow compared to a database join and do you really want to check that the right function is defined in every row?   What happens when I need to add a few records?  Can you make sure that the calculation copies down correctly?
4. Using Excel to edit database tables.  Pull some data from your database into Excel, let someone "edit" it and then try to upload the changes.  It's always particularly (un)helpful if they color-code what changed, added new records or added/deleted a few fields.  
5. Excel as a project management tool.  I may get some flack from this one as I know it's really popular but it seems to me that Excel is used just as a grid to layout tasks and timelines.  I can do that with a whiteboard.  Typically there is no calculation at all and if you want to tie tasks to resources or visualize slippage in tasks across time, this is not the place to start.
6. Using Excel's "analytic" capabilities when you need something industrial strength.  I'm not a purist, you can use Excel's Solver and Data-Analysis tools quite effectively for smaller/simpler problems.  As size and complexity increase you may be able to use more sophisticated add-ins but at some point you will need to upgrade to a purpose-built tool to work effectively..
7. Repeating the same "analysis" or "reporting" once per tab for 40 different brands (or factories or products or managers,...).  Seriously, there is no way you can stop errors creeping in.  You need a reporting or analytic tool that will generate these for you.
8. Of course there are also the folks that use Excel as a word-processor, a presentation tool or even a grid to hold the numbers they produced on a calculator but that's really not a fault of the system is it?

My own personal favorite for the top spot is #2 at least until I see another example for one of the others :-)  

Which ones resonate for you?  Any other nominations?

Coming soon, a new series of posts around using the "Right Tool for the Job".

Better Business Analytics - 2013 New Year's Resolutions

10 resolutions for Better Business Analytics

Firstly - thank you Santa for reading my Christmas list. I love the T-shirt - "Statistics means never having to say you're certain".  With the holiday season coming to a close my thoughts are turning to the New Year and even a certain excitement about getting back to work.  Time for some new year's resolutions !

1. Remember that reporting is NOT analytics

One of the key misunderstandings of analytics in the business world and perhaps part of the reason good analytics is not well recognized for the value it creates is that managers think reporting IS analytics.

Per my previous post "Reporting is about what happened; Analytics is about answering your questions ... well-built Analytics or Predictive Models can find insights and opportunities that you will never find by any other means."

Do good analytics, shout about your success, spread the word.

2. Never, ever build to a manager's spec.

If when you visit your doctor he simply writes the prescriptions you ask for, I suggest you go find a new one, quickly. Unless you possess this expertise yourself, you must rely on your physician to diagnose your symptoms and prescribe action. To do otherwise would be very foolish.

When you (the analytic expert) are asked to build reports or models without explanation as to what it is for - don't do it. Business managers can help you enormously to understand the business context and the killer-questions that need to be answered, but unless they are also skilled in analytics you must bring that expertise to decide how to provide an effective solution.

3. Make sure your project is worth doing

This is really easy and yet so often overlooked. If you are looking for a million-dollar savings opportunity you are unlikely to find it in part of your operation that totals $2 million in cost. However analytically interesting a project may be, business analysts are paid because they generate a good return on investment. Do some simple estimation to see how big an opportunity could be before unpacking the big-guns. See some examples here

4. Use the right tools for the job (or everything looks like a nail)

To recount the old adage: if all you have is a hammer, everything looks like a nail. You must have met people like this, an expert in one particular technological or analytic approach, every single project they undertake is somehow ideally suited for that approach. I've known a few: Excel-experts, database-divas, simulation-specialists, statistics-sages, optimization-??,...

Do you build everything in Excel or in SQL or your favorite BI tool? Are you writing database code in your favorite (non-database) programming language? Has it been a while since you acquired the skill to wield a new tool effectively. Perhaps it's time to extend your skill-set.

5. Learn your craft (or know when to call for an expert)

The difference in skill-level between an analytic-expert and an amateur is huge. In my experience that skill-gap does not result in +10% incremental return on investment, it's the difference between a successful project and a relative failure.

Know when you are getting beyond your skill-level and either set yourself to learning quickly or call in the cavalry.

6. Remember that you are first and foremost in sales/change-management

In all honesty, the "analytics" may be the easy part of business-analytics. Even the most technically-adept analytics fail from a business standpoint unless action is enabled. Knowing that implementation of your project could save 20% in cost may give you a nice warm-fuzzy but unless it is implemented you wasted your time and the company's money. Do that repeatedly and you should brush-up your resume.

To be implemented, a result must be repeatedly and effectively sold-in to an organization. Take the time to present your results as a simple compelling argument for change and deliver that message consistently and often.

Analytics is a lot of fun, but leave a little space for other things in life

7.     Lose that 20 lbs
8.     Exercise more
9.     Eat healthy
10.   Have fun !!

What do you think should be on the list ?


Wishing you an Analytic New Year

Better Point of Sale Reports with Variance Analysis (update)

I've just revised and updated one of the most popular posts on this blog adding more detailed descriptions,  a graphical view to the output and more clearly showing path to action based on these reports.  Follow the link below to the updated post.

Better Point of Sale Reports with "Variance Analysis": Velocity, Distribution and Pricing.. oh my !

Routine, weekly point-of-sale reports tend to look very similar.  For various time buckets (Last week, last 4 weeks, year to date) we total sales in both currency and units then compare to prior year.  Add in a few more measures to look at retail pricing, inventory,  or service level metrics and you may struggle to make it fit on a page.   And from a CPG standpoint, POS  reporting is only half of the story: a CPG's sales targets are not based on POS, they are based on shipments to the retailer.  How can you get a good overview of POS and reconcile that with Shipments all in one report?

Better Business Analytics - Christmas list

It's that time of year again: my kids have written, re-written and re-re-written their Christmas lists now so we all hope Santa will read them carefully and take notice.

With just a few days left before the holiday season hits I wanted to do something a little more fun, so I've pulled together a list of things that I think every Business Analyst should want.  Some are free to acquire, just costing your time to learn, others you may wish to ask Santa (or your CFO) to provide.

64 bit Operating system, applications and more memory

32 bit operating systems cannot recognize more than 4 GB of computer memory (RAM), regardless of how much you load onto your hardware.  Forget "Big Data" for a moment - you can fill 4GB relatively easy with desktop tools - if you want to do any serious data processing on your laptop/desktop environment you will need more than that.  RAM is cheap, easy to upgrade and most modern laptops will take up to 8GB without any problem. Max out your RAM

Note: 8GB of RAM for the laptop I am writing this on is currently $41.99 at www.crucial.com.

Hard-drive upgrade

Solid State Disks (SSD) provide huge speed improvements over their spinning, hard-drive counterparts.  If you are are crunching numbers from file or a local database the hard-drive may well be slowing you down.  A modest investment (the 480 GB Sandisk SSD is currently available on Amazon for $360) can save you a lot of time.

Serious computing power

If you have access to a high powered server this may be of less use, but I was surprised recently to find out just how much computing power you can now get in a desktop workstation.  This beast with 2 CPUs (32 cores), 128 GB of RAM and 2TB of super-fast SSD hard-drive costs a little over $6000.  That's an awful lot of power (more than most servers I've worked with)  for the price of 2-3 good laptops.

Excel 2010 with PowerPivot.

Excel is a superb tool for interacting with your data for prototyping and occasionally for delivering models and results.  It can't do everything and for many problems you will need to turn to more specialized tools but any analyst that tells you they don't use it at all is probably not a very effective analyst.  With the ability to write VBA, to customize and embed your own calculations, it can be a very powerful modeling tool indeed.

With Excel 2010 and the free PowerPivot  add-in, Excel now has the capability to embed  powerful BI capabilities too.  Integrate data from different sources, load enormous amounts of data, vastly more than the worksheet limit of about 1 million records and define more complex measures and functions to apply to this data with DAX (the Data Analytic eXpressions language).  If you are not already there upgrade now to 2010 - make sure its the 64 bit version to blow past the 4GB memory limitation.

Note: Office 2013 may be just around the corner, but with Microsoft's latest offer if you buy now (Oct'12 thru April '13), you get Office 2013 for free when it's released.   Microsoft's Office Pre-Launch Offer.

Time to learn R for statistical computing

OK - I know - those of you who are heavily invested in another package (SAS, SPSS, Statistica, ...) do not get this.  Those are great tools and why should you change?  Well, I'm not suggesting you swap one package for another - I'm suggesting that there is room in your head for more than one tool and R has a lot going for it.

Pros

• According to Revolution Analytics R is now available as an embedded, in-database analytics engine for Oracle,  SAP HANA., Neteeza, TeraData and others.  This is a very exciting development allowing advanced statistical capabilities to be called from within the database from SQL. Handle routine analytics in-line and on-demand.
• It's free - really - free.  R is open source tool  you can download from http://www.r-project.org/ 
• It has packages to do an enormous variety of statistical analyses, you will probably not need or use even 90% of them.
• It has great graphical capabilities for visualization.
• It's callable from .NET languages too via RDotNet.  The interface is not well documented, but it does work.

Cons

• The user interface is "ugly" unless you love command line applications - I don't.  RStudio does help with this.
• If you are not used to vector based programming languages it may take you a while to grasp.  (try "The Art of R Programming" for help.)
• There are a lot of commands to remember, I use my cheat sheet a lot.

Time to learn SQL

 In reality the time to learn SQL was probably about 20 years ago, but if you have not yet done so, catch up quickly. 

SQL (structured query language) is the programming language for relational databases.  Whenever you interact with a database it is some dialect of SQL that interacts with the database to select update or insert your data efficiently.

Most of your data will now come to you from a database of some form.  It is a common requirement to integrate data from multiple sources (without losing any), filter, aggregate and sort as a precursor to more advances analytic routines.  This is what the databases does superbly well - if you're doing this in Excel or indeed any non-SQL environment, you are working to hard.

While you are at it, get a good grasp of database design principles and normalization too.

2013 - the year for Column Oriented Databases ?

Column storage is not a new idea in relational databases, but perhaps it's an idea that is about to mature.  Regular, relational databases physically store rows of data together which is very efficient for transactional databases that update and retrieve a few rows at a time.

A column-oriented database stores entire columns of data together.  This is very efficient for systems where aggregates are computed over large numbers of similar rows - such as in data warehouse reporting and analytics

Notably, SQL Server 2012 released its first direct support for column storage this year.  It's not perfect, particularly as the implementation limits how you can write to the database, but it is fast.  My own testing on a large database showed a 10-fold increase in speed on large aggregation queries and almost linear scaling as the number of fields chosen (and hence the number of columns that must be accessed) changed.

I've also had excellent performance from InfiniDb available as a free "community edition" though I suspect I have not fully tested it's capability with tables that only have a few hundred million records :-)

Column Storage is exceptionally fast and could allow for significant changes in system architecture for reporting and analytic tools.  How about POS analytics?  Straight from database to reports without the need for any custom-architecture, pre-aggregation, cubes or waiting on batch processing?  Want to update a few Item attributes and re-run your reports now?  No problem !  (Embed in-database analytic capability so you can do more than add/subtract/multiply/divide and you have a serious number-crushing platform).

The #1 item for your list - a (much) bigger dry erase board.

At times, it seems I can't think clearly without a dry-erase board and pen.  My own workhorse is a 8'x4' board that sees heavy daily use, but bigger ideas need more space.  Amazon has a dry-erase paint on offer that I may have to try - enough for about 30 linear feet of wall for $500.

What do you think?

So what do you think should be on every good Business Analyst's Christmas list?   Am I missing this year's top gift?  What should a "naughty" analyst receive - more bad data?  Let me know in the comments section.

P.S.  Santa - if you're reading this, I rather like this T-shirt from CafePress.com too.

Better Business Reporting in Excel - XLReportGrids 1.0 released

XLReportGrids 1.0 released

XLReportGrids is a FREE, Excel add-in that builds grids of visual reports, from a template, sized to fit the printed page. 

Templates are just a range of cells in a worksheet that are driven by a pivot-table. Build templates with: charts, formulas, images, pivot-tables, text boxes, anything that can be added to a worksheet.

Find out more, download the add-in and see sample results here

Better Business Reporting in Excel - XLReportGrids beta released

In my last Blog entry, I talked about reporting in Excel, some of the reasons I choose to make heavy use of  them, and some of the issues that stop me using them even more. (see Better Business Reporting in Excel)

• Pivot-tables can only show you data (although lots of it)
• Pivot-charts show you a chart, but only 1 per pivot-table
• If you want the same report for multiple grouping (e.g months, years, brands or locations) you add these groups to your pivot-table and select them one at a time to print.

XLReportGrids is a free Excel add-in, now available in beta test, that builds grids of reports with multiple copies of a template sized to fit the page.   Templates are just a range of cells in a worksheet that are driven by a pivot-table.  Build templates with: charts, formulas, images, pivot-tables, text boxes, anything that can be added to a worksheet.

Features 

• Lightweight, Microsoft-Excel add-in (2007, 2010) 
• Easy to install and easy to use.
• Templates are just a range of cells in a worksheet.  
• Build templates with:  charts, formulas, images, pivot-tables, text boxes, anything that can be added to a worksheet.
• Layout tools make it easy to build clean, professional templates.
• Select the number of rows and columns for your
• Automatically scale to fit the page-size and orientation.
• Generate multiple 'Grids in the same workbook
• Work with multiple page-fields so the rows and columns have meaning
• Optionally add outlines, border gaps and shadows
• Refreshes in seconds, on demand.
• Write the chart legend to a header section, printed on each page
• Lock value axes so they all show the same range
• Easy to automate

Download the beta test version now from our site:  tools.crabtreeanalytics.com 

and help us build a better tool.  The beta version is intended for testing, you will find bugs and usability issues that we have not.  Please report them to us in as much detail as you can so  we can fix them.

The beta version will be active though the end of October.  We will probably make frequent updates to it during the next 3 months so be sure to check back here often.

Have fun and when you build something you like, please send us a copy to include in our gallery.