Visualize This - The Missing Bridge Between Good and Great Design

Here is a video produced by QlikView that makes me want to redo every QlikView document I have ever created.  But are these slick visualizations within a typical designer's reach?  Does QlikView make this type of functionality easy?

Great Design

The visualizations used in the various examples are pretty and effective and reinforce the idea that GOOD design is time intensive and requires a different set of skills than the backend development piece. 

People tend to spend a lot of time on the data because it is perceived to be more difficult AND it is either right, or wrong.  There is no subjectivity.

But the quality of User Interface design rests on a continuum from bad to good.  You can put in some minimal effort and get a document that functions.  But great design as exemplified in this video takes planning, a thorough understanding of design principles and technical skill.  And, I believe it is currently a scale of diminishing return.  To go from bad design to mediocre design is relatively easy.  Going from mediocre to good is a larger effort and moving toward great design requires quite a bit more (read $$$).

Can I Create These Visualizations?

The gripe I have is I wish QV actually came with easy ways to create some of these:

Many of the graphics effects you see there require photoshop or similar software, along with that skill set.  Unless you are able to "borrow" graphics from the internet that meet your exact need.  The gradient dividers and arrow shaped text boxes for example must be created or found outside of QlikView.  QlikView could use a "shapes" library to do arrow shaped text boxes and that sort of thing.  As an example, the arrow shown (4:35) does not exist in QlikView.  It must be created or obtained elsewhere and used as an image in a text box.  There should be a way to do these types of simple representations without additional software.

QlikView could use a "legend" object that could link to various objects and could have customizable display options.  Legends are extremely limited today in QlikView.  The ability to customize the graphic column of the legend or to spread the legend horizontally simply do not exist, not to mention the ability to move the legend completely off the chart to another area of the page.

Also, the last piece regarding the dimming or highlighting of objects based on selections (6:08) is tricky and requires many similar or repeated calculations at different levels within an object.  Some of this can be mitigated with variable expressions, but it would be nice if settings existed to do this kind of stuff automatically. 

So the bottom line is "Yes" I can create this quality of design.  But are clients willing to spend the extra dollars required to get design to this level?  I would generally say "No".  Maybe if QlikView can create some of the design tool improvements listed above my answer to that question could more often be "Yes"!

My Sentiments on Sentiment Analysis

My Sentiments on Sentiment Analysis

I have recently started working with sentiment analysis.  This is a new and interesting field in business intelligence and big data.  Sentiment providers promise to be able to quantify how users “feel” about your organization through the interpretation of comments, tweets or other content provided to you. 

There are a few limitations of this type of automated analysis.  Firstly, a computer algorithm has difficulty with figurative speech, sarcasm and the “lingo” of the day.  Surely, a computer will believe “sick” is negative, but what will it think of “that snowboard is sick, dude”? 

Secondly, the brevity of many of the social media forums leads to less accuracy in scores.  A computer can glean hundreds of adjectives and verbs and rate them in an essay, but when you are limited to a Tweet of 140 characters, there is not much for the computer to score.

Companies like Repustate give you tools to monitor your online sentiment from your Twitter or Facebook feed for example.  They also will allow you to access their scores through an API so that you can bring sentiment analysis into your own application.

So I did this with QlikView using the power of QVSource and Repustate.com who provides 50,000 free calls a month.  I will not get into the details of how to do this because it is actually pretty easy.  Instead I want to examine how accurate or “usable” the resulting information looks to be.

Repustate will take the text string you provide and answer back with a number.  A zero or close to zero number equates to a neutral sentiment.  A positive number obviously indicates a happy or encouraging sentiment and a negative number would be negative emotion.  Obviously, words like “amazing” and “encouraging” should trigger positive scores and phrases like “pissed off” or “horrible service” should warrant a negative score. 

So let’s first look at string length. 

1:     Horrible Service

Score = 0

2:     I received horrible service from a company that shall remain nameless.

Score = -1

If you just say “horrible service”, Repustate gives this a neutral score of zero.  But if you lengthen the string to “I received horrible service from a company that shall remain nameless”, the score comes back as -1.  So even when reducing to the presumably negative words, string length is a huge factor.

Let’s look at some other anecdotal examples.  Consider the following two tweets.  Which one is positive and which is negative?

1:     @_____ I agree that the #personalcloud will replace the PC but I am wondering how comfortable people will be with this concept. Security?

2:     @_____ I could not agree more! I am far from technical yet #QlikView allows me to create my own dashboards. #empowers

Oddly, example one received a positive 3.00, on the upper end of our sample while the second example received a negative 3.00, on the lower end of our sample.  This seems a little contrary to my judgement.  I would say the first example is slightly negative and the second is quite positive.

Consider this third example:

#BioPharma Companies make faster and smarter decisions using #BI http://conta.cc/hPG0dm

You would think this would result in a positive sentiment due to the phrase “faster and smarter” but it actually resulted in a neutral score of zero.

Tweets are limited to 140 characters and probably lots of these characters are likely rendered useless with the the hashtags, usernames and urls.  So let’s take a look at some longer strings and see if we can get some more accurate values.  Both quotes are by Thomas Jefferson.

1:     We hold these truths to be self-evident: that all men are created equal; that they are endowed by their Creator with certain unalienable rights; that among these are life, liberty, and the pursuit of happiness.

Score     =             1.00

2:     Enlighten the people generally, and tyranny and oppressions of body and mind will vanish like evil spirits at the dawn of day. 

Score     =             -1.00

So the first score makes sense, but I think a human would interpret the second example to be positive in sentiment. 

Let’s look at a slightly longer quote about happiness from Sharon Salzberg:

As I go through all kinds of feelings and experiences in my journey through life -- delight, surprise, chagrin, dismay -- I hold this question as a guiding light: "What do I really need right now to be happy?" What I come to over and over again is that only qualities as vast and deep as love, connection and kindness will really make me happy in any sort of enduring way.

Score = -.103

So the computer rates a quote exactly about happiness with a slightly negative score.  I am not sure I understand the logic there.

Try this made up text string:

This was supposed to be an amazing and beneficial seminar but I should have known better.

Score = 1.00

You can trick the algorithm fairly easily.

These examples were picked because they illustrate some issues with automatic sentiment analysis.  The seemingly odd scoring might not be indicative of the accuracy in a larger set.

But in general, I think we need to be cautious on how much value we attach to sentiment analysis.  The good news is, the algorithms will definitely improve over the next few years as more organizations begin to look at this accumulation of social media content that we can clearly call big data.  How do we analyze this data and create an understanding that will help us make decisions?  We are starting this conversation now and I am sure it will only grow in importance over the next few years.

Sometimes Fat is Better

Your data model makes a big difference in the performance of your QlikView application.  Of course, if you have been working with QlikView for any length of time that is pretty obvious.  Here is a situation you might come across I thought I would share.

The client was trying to monitor costs for production items on a per unit basis as they travelled through the various cost centers.  So there are several hundred products, and a few dozen cost centers, with each combination having a per unit cost.  Then, it should be a fairly easy exercise to find the extended costs as units are sold, “slicing and dicing” by things like customers, dates, cost centers and other groupings.

Being fairly rational, I figured out a few ways to do this:

Product	Cost Center	Cost per Unit
A	Assembly	0.0233
A	Packing	0.0324
A	Q&A	0.0133
A	Storage	0.0544
B	Assembly	0.0766
B	Packing	0.0222
B	Q&A	0.0652
B	Storage	0.0143

This is the “thin” table approach.  Load all the costs into one column and then use set analysis in your expressions to display each cost center like:  SUM({<[Cost Center]={Assembly}>} [Cost per Unit] * [Units Sold]).

Product	Assembly	Packing	Q&A	Storage
A	0.0233	0.03424	0.0133	0.0544
B	0.0766	0.0222	0.0652	0.0143

Here is the “fat” table approach.  Create a cross table, with a column for each cost center.  This results in a fairly wide fact table in your data model but no need for set analysis in your expressions as you would reference each column independently:  SUM([Cost per Unit] * [Units Sold]).

Now I have always been told as rule of thumb, “Create narrow, long fact tables” and “when you have two like facts, concatenate them together, rather than having separate tables with a link”. 

Hence, I originally thought that a “thin” table approach would be best.  This was a disaster.  The model worked but performed mind-numbingly slow.  How could it be, when I used as few columns as possible?

I finally realized the problem here is that these costs are not facts.  They are really dimensions.  Sure they are numbers that we will calculate against, but they are still dimensions that either do not change or might slowly change (with the addition of date fields).  I was so used to thinking that any column with lots of numbers we are calculating with should be a fact.  Welcome to database design 101

I developed a small example application to illustrate both models and illustrate the performance differences.  There are about 150,000 sales records, 10 cost centers and 80 products.  So there are about 800 costs that needed to be housed either in one long column or 10 columns of 80.  I ended up with this data model (so that I could create two tables each using one of the cost sets)

I then created a pivot table from the “thin” table using set analysis and a similar pivot table from the “fat” set using the same expressions without the set analysis and referring to the respective data columns.

I also created a Memory statistics file to highlight the difference in calculation time between the two objects:

You can see that the “Fat” set is almost 5 times as efficient in the example.  And look at the memory advantage in Bytes! 

So maybe next time I will take a more thoughtful look at the data before deciding on a model because what can appear as a fact can really be a dimension.  And I should always be aware that regardless of the type of data, sometimes more columns really are better.