Seven analysis tools: R language, esProc, SQL, SAS, SPSS, Matlab and Excel


Interactive analysis is a cycle analysis procedure of assumption, validation, and adjustment to achieve the fuzzy computation goal.

The interactive analysis is the real on-line analysis to solve the complex computation problem in the real world, and it is one of the key points in the business computation.

Example of Case

Let us explain the interactive analysis with a common example in the business activities.

Step 1 Set the goal

Why the sales volume this month greatly exceeds that of the previous month?

Obviously, this is a fuzzy computation goal with several possible answers. You cannot get the result directly using any analysis mode.

Step 2 Guess the possible branch

Since there are several possibilities to give rise to the sales volume increase, the analyzer has to check every possibility, such as:

l  Orders numbers increase

l  Appearance of large orders

l  Intensive consumption of specific customer base, for example the intensive screening the movies of children in the summer holiday

l  Improvement of process

l  Launching a marketing campaign


Obviously, a certain level of business knowledge is required to make these assumptions and the keen sense of smell to the circumstances inside and outside the enterprise. This is a relatively personalized effort.

Step 3 Branch validation

Based on the possibility and characteristics of data, the analyzer will choose a branch to start the analysis, such as Increase of Orders. If the number of orders does not increase through the calculating for validation, then it indicates that this assumption is not correct. You need to validate the next assumption to carry on the cyclic analysis.

For example, by going through the validation on this branch of Appearance of Large Order, the analyzer finds this is correct, and thus this branch can be justified.

Step 4 In-depth exploration and mining

These possibilities are usually the apparent cause instead of the root cause. To really settle the problem, you will have to drill down step by step to reach the core. For example, the appearance of large order may result from:

l  The new salesmen is highly capable

l  The new sales policy of the company boosts the large order

l  Intensive procurement of clients from a certain sector


It is obvious that the process of drill-down is a cyclic procedure. The analyzer must judge on the characteristics of data at that specific point to choose the branch of the highest possibility, so as to progress level by level, until the problem is solved.

Step 5 Solve problem

The procedure of exploration and mining does not require the unlimited drilling down. The whole procedure can put an end once a clear answer enough to make a decision is found. For example, through the validation, the Centralized Procurement in a Certain Sector is determined just the root cause. Then, this is enough for analyzer to make a decision: The sales volume can keep rising by simply beefing up the sales forces and efforts in this sector since the recent sales rise is the result of centralized procurement by the clients in this sector.

Step 6  More computations

To this step, the computation goal is achieved. However, we can realize more business values through more computation on the basis of the existing results, such as:

l  Find the list of customers in this sector

l  Find the list of salesman which are good at this sector

l  Find the reason why the client in this sector increase the procurement quantities abruptly

l  Find the abnormal actions in the sector related to this sector and the downstream/upstream sector


As we can see from the above examples, the real world is far more complex than the theory. The commercial opportunity changes unpredictably and comes and goes in a moment of doze. In fact, the computation on the business activities is usually fuzzy. There are few model algorithms from textbook that can be used to solve the real situation. The analysis computation is to solve the problem in the real world. They are characterized with the following points:

Fixed algorithm as bottom layer

Interactive analysis can be always resolved to the fixed algorithm. For example, ranking algorithm is usually used to compute the “Appearance of Large Order”; grouping algorithm is usually used to compute the “which sector sees the intensive procurement by clients”.

Focus on the interactive procedure

The bottom layer of interactive analysis is the fixed algorithm though, the human intervention is necessary. How to break down the target? How to set the priority of branches? Whether to carry on the mining or not? Is the existing result enough to support the decision-making? Is the further computation necessary? Theoretically speaking, the power enough computer programs can implement the above network-like branches, and thus turn it into the fixed algorithm. However, before the The Matrix and Neo born, the analyzers will have to take great effort in it.

Focus on the business expert

Interactive analysis is to solve the problem in the real world. The assumption will have to make on the basis of business status, and the next step computation will be decided on the current data and business experiences. To do this, the abundant business knowledge is required. The qualified analyzer is usually the business expert. The database administer and programmer are more fit to seek the solutions to the fixed algorithm and they are able to provide the assistance in computation but hard to make the most important business decision.

Take massive structural data as the primary goal

The massive structural data is the data capable to be represented with a 2-dimention structure. Of the massive structural data, the typical examples are the data from database and spreadsheet, and text file. In the business activities of real world, these data are the most common and fundamental, acting as the base of business calculation.

Requirements on analysis tool

Characteristics of interactive analysis determine its requirements on computation tool:

l  Abundant library function or fixed algorithm

l  Provide a convenient interactive procedure

l  Business expert can handle

l  Support massive structural data

Common tools for interactive analysis

Based on the requirements on the interactive analysis tool, we can list some common tools, just name a few:

n  Excel. Please refer to:

n  R language. Please refer to:

n  esProc. Please refer to:

n  SQL. Please refer to:

n  SAS. Please refer to:

n  SPSS. Please refer to:

n  Matlab. Please refer to:

There are many other tools for interactive analysis. However, one point to note is that none of them is perfect on all aspects. For example, the analyst may find that it is easy to grasp Excel but hard to compose SQL statements; esProc lacks of the non-linear model, but it can provide a convenient interactive process; SPSS boasts the abundant fixed algorithms but is not as convenient as SQL in relation query.

The tool suitable for your needs is the correct one. Please refer to the Comparison between Interactive Analysis Tools to join us on choosing the analysis tool suitable for your needs.

As you may find that the interactive analysis is similar to OLAP in some respects. Please refer to another article: interactive analysis and OLAP.

more in my blog:



About datathinker

a technical consultant on Database performance optimization, Database storage expansion, Off-database computation. personal blog at: datakeywrod, website: raqsoft
This entry was posted in Data Analytics, FAQ, Program Language and tagged , , , , , , . Bookmark the permalink.

One Response to Seven analysis tools: R language, esProc, SQL, SAS, SPSS, Matlab and Excel

  1. Marco says:

    I like your step-by-step case study. It’s good for the beginners. Are there real-world data sets available for students to practice?

Leave a Reply

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

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s