May 30, 2013

Toolbox Interview to Jim King: Legendary Career Transition of a BI Blogger


Check the full content below:

In 2012, Toolbox gained a new business intelligence blogger,datakeyworld. He offers insight into all sorts of topics pertaining to data in his blog Data Analytics. Don’t miss out on the valuable content he provides or a sneak peek into his life…

I’m a BI execution consultant and also a father. Many people say that I do a better job for the latter one.

I was a programmer before 2001. I participated in my first BI project to establish decision-making support system for one of Fortune Global 500 enterprises in 2001. I was the team leader at that time and this project was very successful. From then on, I focus on BI industry. Time is flying; I have worked in this field for 12 years. Every New Year, I’ll have a party with the NCR \Cognos\ICBC colleagues who participated that project. Even, I successfully escaped from the prediction of Mayans (Joking).

At present, I provide BI consulting service for several enterprises and provide solutions specifically for large-scale BI projects. But currently, my interest is gradually shifting to “Desktop BI” and the main customer is Raqsoft. Since their concept is the same with mine: business man does the business intelligence.

What’s your favorite part of blogging for Toolbox?

Discussing the topics I’m interested with real BI experts is the driving force of my blogging for Toolbox. I found many real BI experts with profound thoughts on Toolbox. They help me to improve my views continuously or tartly note my mistakes. Sometimes, especially the latter found the flaws from my “perfect contention”, which surprised me. Just as the saying goes, birds of a feather flock together.

How do you develop the ideas for your Data Analytics blog posts?

I find inspiration from my work and then I ponder over, decompose, test and verify my ideas,   disrupt and overthrow them. When I feel “It’s great and all logics point toward it”, I’ll write it down in one breath. My most commonly considered questions include “what kind of BI tools could integrate cost and efficiency and what parameters influence these tools?”

Are you a night owl or early bird?

I might be a standard night owl. May be this is not good for health. But in my dictionary, health is the compound word of mind, body and sociality, in which the mind has higher weightings. I believe in broadening idea. Obviously, ideas can be widely broadened at midnight.

What is the most challenging part of your career?

I remember that I took my first job in 1999. I ambitiously decided to earn more money for the customers with advanced information technology. But the company I worked for at that time made the customer paid for numbers of price to establish the BI project, which almost didn’t bring any effects. I think this is unfair. But the boss gave me two options: make money or go away. I chose the former.

If you could live anywhere in the world where would it be and why?

New Zealand! After living in the Northern Hemisphere for 30 years, I want to try the feeling of reversed season. Keeping warm around the fire in August must give me the feeling of “coming to the earth for the first time”. In addition, the air and beach of New Zealand may be the best in the world.

Don't miss out on an insightful blog, Data Analytics!

Desktop BI Helps to Meet Instant Business Analytics Demands


Data computing & analytics software (DCAS for short) is used for processing and studying on various data to get the valuable result. For example, according to the order details, calculate and find the goods whose sales growth rate in the recent 3 years is greater than 20%.

The data source of DCAS is usually the structured data, such as, database, txt file, and spreadsheet. The calculation methods include filtering, grouping, summarizing, sorting, comparison, and discovering the correlation. Similar to ERP, CRM, Reporting tools, Dashboard, OLAP, and ETL, DCAS is also a type of BI.


Desktop BI refers to the BI tools running on the desktop environment, almost without any supports of server. They usually only provides the core BI functions and requires less dependencies on the technical environments. There is an interesting phenomenon: most DCAS tools belong to the Desktop BI, including Excel which holds the largest market shares in the sector of commercial BI tools, R project which ranks the first in the open source software market. Similar examples also include StataCorp Stata, Raqsoft ES series, IBM SPSS, and MathWorks MATLAB, etc.

Is this a coincidence? Compare their features and you can clearly understand the root cause of this phenomenon.

If you ever read the article of What Role Desktop BI Plays, you should know that Desktop BI is characterized by the below features:

l  Lightweight BI tools: Desktop BI neither explores much about the business details directly nor provides a great number of modules to give the ready-to-use answer. Usually, a work process is required to solve a problem.
l  Quicker problem solving: Focusing on BI, the Desktop BI does not require the technical assistance and is ideal for solving the complex problems quickly.
l  Most Desktop BI users are business-oriented, such as the accountants, banking account manager, business analyst, and stock analyst.
l  Self-service and Independence: Desktop BI is usually used by users to complete the BI task independently.
l  Low hardware requirement: Desktop BI is a desktop application with low hardware requirements.

Then, let’s check the features of DCAS:

To address the temporary needs

DCAS is usually used to address the temporary needs, such as the RStudio or esProc computation: For those clients accounting for top 50% of the total sales last year, whose ranks increased this year? The clients’ sales are usually already stored in the business systems and may have been ranked, because these data is frequently used. But for the data not for daily use and only be used in specific occasions, such as “clients accounting for top 50% of the total sales” and “year-on-year comparison based on rankings”, they are usually not available.

The data to be frequently-used can usually be predicated in the early stage of BI system development. The ready-to-use module can be built with Solution BI tools such as the Report Tools, Dashboard, and OLAP. For example, the Dashboard of QlikView is quite fit for the above-mentioned client sales ranking or even the sales ranking.

For the data that is seldom used, since it is usually hard to predicate and less possible to use, the cost is quite high to build all means to get these data into the ready-to-use modules. Therefore, we need to conduct the temporary computation. The Desktop BI refers to the lightweight tools that do not explore into the business details. Although Desktop BI tools do not provide the ready-to-use module to get the answer, they can be used to address these temporary needs via calculation easily. It can be seen clearly that DCAS is characterized by these features of Desktop BI.
        

To meet the sudden demands

DCAS is often used to address the sudden needs. For example, find the product whose sales values are rising in 5 consecutive weeks through rapid calculation in Excel or esCalc, so as to launch the marketing campaign aimfully. Such needs are pressing since the correct results must be calculated out in limited time. In order to achieve the goal of rapid calculation, DCAS shall allow for the full control by users, especially the Business experts must be capable to act independently, and the DCAS functions must focus on the BI sectors. These are just the Desktop BI features.

On the contrary, Solution BI like SAS or SAP usually requires the collaboration between business personnel, DBA, SQL composer, Web administer, programmer, report script developer, and experts in several areas. In addition, they also need going through a series of work processes like the requirement management, departmental approval, resource provision, developing, and responding. The timeline is completely not guaranteed at all, and thus it is not fit for addressing the sudden needs.

What-if method

It is always easier to solve the BI problem with clear computational goal. However, the complex problems are always abstract and ambiguous. To address them, DCAS requires the what-if analysis method. For example, you can resort to RStudio to find the reason for the current climbing complaint rate. To solve such ambiguous problem, we need some reasonable assumptions. For example, the new product debut gives rise to the laggard after-sales, product quality drawback, and after-sale platform failure. These assumptions are the decomposition of goal, that is, decomposing the ambiguous and great target into several simple and clear small goals. Through validating and calculating several simple and clear goals, the complex, ambiguous and great goal can be solved.

The learned and experienced business expert is the key to what-if analysis in determining: what factors are related to the goal? Of these factors, which factors cover all possibilities and do not overlap mutually? Which factors can be verified explicitly? Which factors can be further divided? What are the weights of these factors? Which are highly possible and which are relatively easier to verify? To make the correct judgment on these questions, you may need the in-depth business understanding. Therefore, DCAS tools must be business-oriented, such as esProc. Being business-oriented is just a feature of Desktop BI.

Individual creative work

The labor can be divided into two types of the repetitive work and the creative work. In BI sector, the repetitive work refers to those problems that can be solved through teamwork or collaboration between multiple persons, for example, the commonly-used reports in enterprise, OLAP model tailored for specific industry, and classic correlation analysis. They are in the scope of Solution BI conventionally. But the creative work is quite another thing. For example, use RStudio or esProc to find the new product with the greatest market potential.

For the creative work, no standardized and existing solution. The creative work requires the rich expertise of business experts and computation, and DCAS is really good at such computation. Different experts may see from different perspectives and be in different positions, take different analytical methods, and reach different conclusions. Therefore, their respective process cannot be reproduced. Such calculation is soaked with the strong personal style, being related to the individual background, work experiences, and business preferences of business experts. It is the typical creative work by individuals. The collaboration will backfire and hinder the user creativity.

Therefore, DCAS is usually adopted by users independently as a type of typical Desktop BI.

Ability of expressing the business

Ability of expressing the business is the ability to convert the business jargons into the computer languages. Unlike other BI tools, DCAS users are usually required to analyze the complex goal, which demands the creative work on the basis of a strong business background. In view of this, we can conclude that the core ability of DCAS is to express the business ideas and plans efficiently and cost-effectively, which is an important criterion to discriminate the good DCAS tools from the bad ones. This core ability includes providing the friendly UI, the business-oriented syntax rule, the intuitive and easy-to-understand formulas, and the free analytical style. For example, with esCalc, merge the basic salary, performance, attendance, and multiple spreadsheets into a practical salary sheet according to the No. of employee.

So, we can say that performance is not the top priority for DCAS. The core features of Solution BI like multi-core parallel computing, cloud, and cluster computing can boost the performance only, but not the ability of expressing the business. These features may backfire, distracting users from reaching the business result and even bringing about a bad impact on the correctness of computational results.

In addition, the normal PC can offer the more than enough computational capability. Even the CPU released 5 years ago - Intel i7 - can support more than 8GB memory and are still powerful enough for running almost all DCAS. In fact, not having to rely on servers, most computation and analysis problems can be solved on PCs that are believed to provide only the relatively low performance nowadays. The vast majority of DCAS belongs to the Desktop BI. In case any computational problems requiring a higher PC performance are encountered, DCAS tools, for example R and esProc, can also handle them well with its advanced features, though it seldom happens.

Through the above analyses, we’ve found that many features of DCAS are up to the Desktop BI standard. So, we can call it the typical Desktop BI.

May 26, 2013

How to Compute the Link Relative Ratio of Automobile Sales


The business spreadsheet software is widely welcomed by business users for its simpleness and ease of use. However, there are some common calculations which are still tough for spreadsheets to solve, such as the year-on-year basis and link relative ratio.

Take the sales details from a Volkswagen 4S shop in the below table as an example, they are purchase records of customers in various periods.


We need to calculate the ratio of sales volume in the current month to that of its previous month (Supposing the current month is December of 2012), and the year-on-year basis of sales volume of each month. Detail data needs to be kept for other computing. The result will be like this:


The section in the red enclosed box can be implemented easily that users only need to filter, sort, summarize in groups, and fold the data. But the link relative ratio calculation (i.e. column LRR) is a quite different matter. Visually, it seems that you should write =C458/C4 in the F458 cell, drag or copy it to the column F or other cells. In fact, it is not correct since the formula in the cell F890 will change to” =C890/C436” instead of the expected “=C890/C458”. This is because that the common spreadsheets only mechanically calculate the offset when copying the formula. For example, 458 - 4 = 454, the 436 is the result if the offset of 890 is 454. To have a correct computation, you must enter each formula manually. Needless to say, when there is huge data, the workload will be great.

In addition, the meaningless formula will definitely appear in the detail cells like F1324, because the common business spreadsheet software cannot differentiate the summary section and the detail section. If dragging formula in the summary section, these formulas will be copied and pasted to the detail section automatically. Such “automation” is obviously not expected, and we have no choice but input the formula manually.

When calculating the Year-on-Year (i.e. YOY) column, we will be in the similar situation: undistinguishable summary and detail, incorrect formula paste, and faulty formula appearing in the section of details data.

However, esCalc is more efficient to solve the problems alike. It is the business spreadsheet software with the “homocell” functions. In the Summary section, any formula entered will be copied and pasted to the cell with the same business status (i.e. other summary sections), without any impact on the detailed data. Just input the formula for once, and other homocells will be adjusted according to the business logics automatically. For example, write “=C458/C4” in the cell F458, and “=C890/C458” will appear automatically in the cell F890. Therefore, with esCalc, only two formulas are needed to be entered to solve such kind of problems, of which the formula for link relative ratio is:


The year-on-year basis is:



May 23, 2013

Why Plug-and-Use Desktop BI Software Is So Powerful?

The basic function of a calculator is computing, which can be as simple as the four arithmetic operations and also as complex as the calculation for the next move in chess with Deep Blue. Among these, esCalc is a desktop data calculation tool for the business users to handle the occasional, complex, and business-related data computation. Such tools are named as the desktop BI software.

For example, a stock analyst is required to recommend some stocks to the clients urgently. Among all calculations involved, the analyst needs to find shares from more than 20 daily trading stocks which had risen on 5 consecutive days in previous month. He opens this desktop BI software and imports the daily data of the more than 20 stocks. He continuously monitors and analyzes the data, works out the outline of algorithms, and then groups, summarizes, sorts, filters and takes other possible operations to calculate the results with some simple formulas. At last, he gets the result and makes the recommendation successfully.   

The similar calculations also include:

  • For all clients of the insurance company, what’s the average insurance price of those who bought the basic insurance first and then bought the additional insurance?
  • In the 3 months with the most client complaints, find out the top 3 products with the highest defective rate.
  • For the top N sales persons who achieved the 50% of the total sales for the company, what are their respective sales proportions to that of their respective sales team?

esCalc is the plug-and-use desktop BI software with the powerful computational capability for business users to grasp easily, owing to the following advantages:

Typical Desktop Application

The installer of esCalc is only dozens of MB. The installation procedure only requires a few clicks and can be run immediately after installation. As a JAVA application running on the Windows desktop, esCalc can run on most office computers independently without having to deploy the extra server additionally. 

esCalc resembles Excel in UIs that is easy to learn and grasp. The overall interface is shown as the below figure:


The Data Computation section is shown below:


esCalc is especially designed for the business users without technical background. It can be installed in a common working environment easily, and be used once installed.

Various Data sources Support

esCalc supports various databases, including MSSQL, Oracle, Access, MySQL, DB2, Sybase, and other mainstream databases. In facts, esCalc supports any databases with JDBC and ODBC drivers.

Besides the access to the database in the LAN, esCalc also supports the access to the local data file, such as txt, log, tab, other text files, and the Excel 97~2010 spreadsheets.

esCalc also supports the interactive calculations between various data sources, for example, to store the basic information like the company name, the contact information, and the company industry in the database, and to store the follow-up visit to some clients by sales persons in another Excel spread sheet.  With esCalc, you can merge the two pieces of data easily to form a follow-up visit log classified by the company industry. Even if their Number of clients and the Sort Criteria differ to each other, esCalc can handle it with easy.

The office environment of business personnel is sometimes rather complex, such as the CRM, MIS, ERP, DSS or performance environments, supply chain management, and other application systems. esCalc supports various data sources and interactive calculations and is capable to handle the complex office environment.

Step-by-step Calculation

The step-by-step calculation can decompose the complex computational goal into several simple steps and complete a seemingly complicated great goal by solving each simple small problem.

Still with the above example, to calculate the stock rising for consecutive 5 days, you can group by stock first, and then calculate the daily increment of the stock. An increment greater than 0 indicates the stock is rising. Based on this judgment, the consecutive rising days of each stock can be calculated out. Lastly, the longest consecutive rising days of each stock can be calculated through filtering and sorting. The details are shown below:

Step 1: Import the stock data from txt file, as shown in the below figure: 


Step 2: Filter out the data in the previous month by date. Suppose it is June in 2011, as shown in the below figure:


Step 3: Set the level by the stock code:


In the above figure, the newly-built level is in the red block on the left, and in the red circle on the right is a stock. This row is the summery row.

Step 4: Sort the transaction data of each stock by Date in ascending order.


Step 5: Calculate the daily growth rate of each stock. In this step, a calculation column D needs to be added, and a formula =(C4[A2]-C3[A2])/C3[A2] is entered to the D4. The formula will be pasted to the related cells automatically, as shown in the below figure:


Step 6: Compute the consecutive rising days. In this step, a computation column E needs to be added, and then the formula in E4: =if(D4>0,E3+1) to be entered. The result is shown in the below figure:


Step 7: Calculate the longest days of a certain stock rising consecutively by inputting the formula like max({E4}) in E2. The result is shown below:


Step 8: Fold the summary row by clicking on the level number 1 on the left, as shown in below figure:


Step 9: Sort by the longest consecutive rising days, as shown in below figure:


In the above figure, 5 stocks keep rising for 5 consecutive days in June 2011, which are American Express Co., The Boeing Co., Citigroup, Inc., General Motors Corp, and Coca-Cola Co. It is certain that the data can also be filtered by the longest consecutive rising days, as shown in the below figure:


To solve the relatively complex computational goal in this case, we decompose the computational goal into 9 steps of simple operation or formula computing.

The step-by-step computation allows users to decompose, simplify, and ultimately solve problems in a rather visual train of thoughts. Owing to this, business users can also solve some complex data computing problems by themselves.

Adequate data computational capability

esCalc is powerful enough to handle the various computational task in the daily office work.

Function as SQL in every aspect

With the same computational capability as SQL, esCalc can be used to filter, group, sort, and perform the distinct, union, join, and other equivalent actions of SQL.  Please refer to the menu shown in the below figure:


No technical background required

SQL can only be grasped by the professional technician, while esCalc represents SQL functions with graphics and decomposes it into several steps so that even the business users without technical experiences can handle it easily. For example, in the step 3 of “building levels by stock code”, esCalc does not require any complex coding, and users only need to set it up in the menu easily.


Alternatively, the default shortcut menu can also be used, as shown in below figure:


Because SQL does not support the step-by-step calculation, SQL solution to this case will be lengthy, error-prone, and hard-to-understand statements. Obviously, it is hard for the business users to grasp.

Computational capability beyond Excel

The Excel and other tools alike do not provide the auto copy and intelligent adjustment functions. The similar functions can only be implemented with a great amount of manual operations. For example, the longest consecutive rising days of each stock, esCalc users can enter the formula =max({E4}) in E2 directly, as shown in the below figure:


After entering the formula in E2, all homocells of E2 will be populated with the formula, for example, E94. By comparison, Excel does not provide the auto-copy function, and you will have to conduct it manually. In addition to the auto-copy, esCalc also supports the intelligent adjustment function, for example, the formula in E94 will be adjusted to =max({E96}) to meet the business logics, as shown in the below figure:


 In addition, the homo-cell model maintains the business relations between esCalc cells, so that the true grouping is implemented, and the grouped data can be further processed. This is hard to be implemented with the Excel and other tools alike, for example, in the step 4 of the above case, “sort the dealing data of each stock by Date ascendingly”. For Excel, ungrouping is required to sort the data by stock & date and then group. However, with esCalc, you can sort directly to implement it.

Multiple computational functions

esCalc can be used to perform the complex computations related to sequence numbers. For example, still in the above example, calculate the rankings of closing price of the end of previous month on the basis of the result in step 8.

Firstly, calculate the closing price in the end of month, then append a new column F, and enter the new formula in F2: ={C3}.m(-1), as shown in the below figure:


Then calculate the rankings. Append the new column G, and input the formula ={F2}.ranki(F2) in G2, as shown in the below figure:


As can be seen from above, 9N12584 (i.e. IBM) has the highest closing prices among these shares of more than 20.

esCalc can also perform the intersection, union, compliment, and other set operations, for example, compute the stocks that are among the last 10th (cheaper) by closing price and rising for consecutive 4 days. esCalc can also be used to perform the inter-row computations such as monthly year-on-year comparison and the link relative ratio comparison, for example, compute the stock price moving averages in the 5 days.

In conclusion, esCalc is a typical desktop application which is able to support multiple data sources and step-by-step computation with sufficient data computational capability. It is the data computation tool for business users to handle the various computational problems in the daily office work easily. 

May 22, 2013

What role desktop BI plays?


All modern Information Technologies which are capable of improving the enterprise competitiveness fall in the scope of BI, such as ERP, CRM, Reporting tools, Data Computing, Statistical Analysis, Data Mining, OLAP, and ETL, etc. They can be divided into 2 categories: Desktop BI and Solution BI.

 Desktop BI runs on the desktop environment, and almost does not need the support of the servers. Usually, such tools only provide the core BI function with a relatively low requirement on the technical environments, for example, Microsoft Excel, StataCorp Stata, Raqsoft ES series, IBM SPSS, and RStudio.

On the contrary, solution BI cannot work without the support of dedicated server. It’s usually the integrate solution or multi-module platform. Besides the core BI functions, there are also some external functions like the authority management, resources sharing, and collaboration among various jobs. Such software includes the SAS, Spotfire, Tableau, Qlikview, and SAP BI.

Although there are obvious differences, these two types have many advocates. In the following sections, we will discuss their features respectively from 4 aspects, including software structure, function feature, technical requirements, and target users.

Software Structure

Basic Structure: The Desktop BI is a typical desktop application that can be installed on an average PC. With it, you can access the external database via network and analyze the local files like RStudio if network is unavailable. The Solution BI is usually the B/S or C/S architecture with its own dedicated server. Owing to this, Solution BI requires a high performance server, a complete network environment, and a disaster recovery system like Spotfire. Therefore, everyone can use Desktop BI easily because it does not require much on its environment for use. On the contrary, the solution BI has high environment requirements and can only be used in the enterprise environment.

Installation: Solution BI software can be roughly divided into 2 types: server software and client software. The IT department usually will be responsible for the server end. Although it is very complex, the end users need not to care for it. Most clients are browsers capable to run with zero installation like QlikView. Even for the fat clients, the Java WebStart and the relevant technologies can be leveraged to reduce the complexity of installation and future upgrade. By comparison, Desktop BI requires users to download and install the software and sometimes various plug-ins like RStudio, which adds difficulty to the upgrade and update. It is far less convenient than Solution BI.

Performance: The differences of basic structure result in the great differences of their performance. Desktop BI is operated on the common PC, which means that the computation involving great data volume, high I/O and performance cannot be performed on the desktop. On the contrary, Solution BI usually completes the computation on the high performance server end, so that Solution BI has its advantages in the massive data processing and the operation efficiency. Sometimes, Desktop BI adopts cloud computing, server cluster, multi-core-based parallel computing, etc. as the supplementary means to enhance the performance, such as SPSS. This is certainly not the typical application of SPSS.

Maintenance: Their maintenance overheads are different. Desktop BI is zero maintenance cost and ready to use once installed nearly without any assistance of IT department. By comparison, Solution BI requires a data center, a network administer, and a server administer with a relatively high maintenance overhead. In addition, Solution BI generally requires a set of corresponding supporting mechanism. The administrators and users are also required to go through the professional training at high expense, for example, SAS.

Functions and Features

The functions of Desktop BI usually are specific and focused on a single-purpose, while Solution BI functions are diverse and complete.

BI-specific vs. miscellaneous functions: DeskTop BI only provides the core BI function, excluding the functions like access control, resource assignment, collaboration, and other non-BI functions – even if these functions are available in some software, for example Excel, users almost do not use them. However, the Spotfire, Tableau, and other Solution BI software provides a complete set of non-BI functions such as ACL, logon, logging, performance monitoring, mobile and share, and other non-BI functions. In addition, according to module, the user types can also be divided into system administer, database administer, reporting template designer, common business user, business manager, and other roles.

Single purpose vs. multiple purposes: This means the single usage of Desktop BI. For example, Excel and esCalc mainly focus on the spreadsheet function, while their reporting and ETL functions are relatively weak; RStudio and esProc are mainly for data analysis without any spreadsheet functions. Solution BI integrates a great number of BI modules. For instance, the SAP BI not only provides the ETL and Data Warehouse functions, but also the Reporting and OLAP functions.

In this case, we can find it is a typical example of 80-20 rule/Pareto Law that Desktop BI only offers 20% functions of Solution BI but is capable to achieve 80% BI goal. It is certainly not to deny the value of Solution BI. In fact, a secure, robust, and highly expandable system is a worthy investment.

Study curve:  Due to single-purpose and highly specific functions, Desktop BI is easy and fast for users to learn. Its study curve is smooth and gentle. The learning cost is very low, and users can fully grasp it after a few days of learning. Excel is even assigned as the learning contents for the primary school students. On the contrary, the study curve of Solution BI is quite high because Solution BI software consists of many modules, and provides many complex functions. The robustness and collaboration factors of Solution BI also add the cost and difficulty to learn.

Applicable scenarios: The complex access control and cooperation based on division of jobs determines that Solution BI is only fit for the job involving thousands of documents, rigid work, and routines. For example, for the jobs of reporting, Dashboard, ETL, OLAP, software of Spotfire, Tableau, Qlikview, and SAP BI all belong to this type. For this reason, it always takes quite a long time to complete a Solution BI job. For example, the OLAP analysis with SAP BI requires the multi-step and various jobs collaboration on model design, data acquisition, and task scheduling. Even the apparently simple job of report preparing requires the database administer to compose the stored procedures, the business expert to decide the business logics, the art designer to design the style, and the report designer to write the report scripts.

Instead, Desktop BI is a tool for users to work creatively. Desktop BI requires users to deliver the reliable results before the commercial opportunities slip away. For example, use R to analyze the reason for the sharp increase of website visits. Considering this, its main concern is not the access control, robustness, and cooperation but the agility and convenience.

Technical Requirements

The technical requirements of Desktop BI are lower than that of Solution BI.

Self-independence vs. Teamwork: Desktop BI usually allows users to complete the data computing and statistical analysis independently. The computational goal is highly related to the business, and thus the technical assistance becomes unnecessary. By comparison, Solution BI requires the strong technical assistance to complete the server deployment, report scripts and SQL statements, secondary development, and other IT-related work.

Work Cycle: Because of the lower technical requirements, the work cycle of Desktop software is relatively short. Solution BI software instead requires the cooperation of experts and coordination between various departments, and thus the duration of the cycle is relatively long.

The deviation of computational goal: The relatively low technical requirements can ensure that the business experts can fully grasp the Desktop BI all by themselves. As we know, the “B” of “BI” stands for “Business”. The whole computational goal of BI is to ultimately serve the purpose of business. Therefore, facing the business requirements, to get a result of the most practical and actual to business conditions, and the closest to the computational goal, it is compulsory to enable the business experts to use the tools by themselves, analyze from the business perspective, and turn their business expertise into algorithm.

By comparison, Solution BI instead requires the technical assistance from IT experts. A lot of core algorithms are even proposed by the technical experts, such as the modeling procedure of Qlikview. In the process of implementation, the IT experts usually play the role of constructing the underlying infrastructure on which the business experts will carry out their work. As we know, a solid foundation determines the lifecycle of the superstructure of a building. Therefore, the infrastructure laid by the IT experts will have great impact on the computation procedure of business experts. The ultimate computation results may easily deviate from the computational goal. Every business expert may ever hear these words: No way. You cannot drill like this because we did not consider this in designing; No. The desired data is hard to obtain through SQL and please consider to choose other data; No. we need stored procedure to achieve this, and we will have to discuss it with the DBA and IT manager; No. The cost is too high for IT department spending a few months on it. We need to change the computational goal.

Of course, the high technical requirement is not always a bad thing. For the BI work of relatively huge data volume and clear standard like reporting, the high technical requirement and cooperation based on division of work usually leads to the high quality of output. That is to say, the report is more refined, the algorithm is more efficient, and the model is more complete, just to name a few.

Target Users

According to the above analysis, we can conclude the most suitable user groups for these two types of BI software.

Data computing oriented vs. Data management and presentation oriented: Users of Desktop BI software all focus on the data computing, such as the Microsoft Excel, Raqsoft ES series, RStudio. These types of users usually have to perform the complex procession on data to get a certain valuable computational results. For example, find the product whose sales value has ever been rising for 3 consecutive months. Users of Solution BI are usually data management and presentation oriented. For example, use Dashboard to present the performances of major departments dynamically.

Business-oriented vs. Technology-oriented: Users of Desktop BI are mostly business-oriented. For example, the accountants, account manager for banking, business analyst, and stock analyst. Users of Solution BI are mostly technology-oriented, for example, the report designer, enterprise portal designer, and data warehouse administer. Of course, such division is not always clear, for example, the stock analyst may very likely the expert in both mathematics and finances.The report designers not only understand the reporting scripts, but also have in-depth knowledge in business.

Temporary and sudden demand vs. Daily routine requirement: Users of Desktop BI usually have to confront to the temporary and sudden requirements. Such requirements are often in short of the existing and ready model to use. Instead, it depends on the concrete analysis according to the specific factors at that point. For example: find the main reason for the sharp increase of complaints in last week. Or, of the clients accounting for the top 50% of the sales values, find the ones whose ranks of this year rise again. By comparison, the results of Solution BI are mostly typical and daily common ones. For example, the monthly balance sheet and cash flow report. Or, the 3 dimensions of client, network of dealers, and product, and the 2 metrics of sales value and sales volume for OLAP analysis.

No technical support vs. Available technical support: Desktop BI is fit for those users who can hardly get any support and assistance from IT teams, for example, the sales assistant who travels a lot, business analysts who work at home, and the stock analyst as freelancer. Solution BI is fit for those users who can easily get the support and assistance from IT teams,  for example, the telephone sales assistant, and the banking report designer.

After all, we also must notice that the same users in different occasions will use different tools. For example, an accountant will login on to the enterprise portal to view the routine report, and this falls into the scope of Solution BI. Sometimes, they will use Excel to solve the temporary data computing task arranged by the finance administer, and this falls into the scope of Desktop BI. For another example, the business analyst can use esProc to handle the complex data computing independently if no support is available from the IT teams, and use SAS to perform the massive data computing if the support is available from IT teams.


May 16, 2013

Could Business Computing Be Done by Users without Technical Experience?


The business computing always occurs in enterprises, which plays an important role in enterprises operations, and mainly handled by the business users. The key business computing is usually characterized with timeliness and weak predication. There is no ready answer in the ERP, CRM, and other business systems. For example, find out the materials whose inventory level is below the warning limit for 3 consecutive days in the recent 2 weeks. Among those materials, which are used to produce the top 5 Halloween best-sellers? In another example, find out the agents contributing to the 60% of the total sales. Assign those agents in proper order to the salespersons whose performances rank ahead of others in the year-end sales promotion

Most business users have no IT experience. They can only rely on the business spreadsheet software to complete such computing. However, the traditional business spreadsheet software is weak in calculation, which will bring about errors in the calculation process.

Considering the above examples, you must filter or sort the summary values and detail values. But, the traditional business spreadsheet software is only fit for the 2D sheet computing, and lacks the further processing capability on the grouped data. The manual operations are usually required to implement computing with such spreadsheet and the process is error-prone.

In order to solve the above problems, the set operation capability to some degree is also required. But the business spreadsheet software has no concept of sets, and no business association between cells is available. The set operation can only be handled via the complicated search & find formula, which adds to the computing difficulty virtually, and thus a higher technical competence is required.

In this case, the sheets from multiple data sources need to be merged. But in the traditional business spreadsheet software, users can only copy the data, and is unable to merge the data at various levels or in the uncertain orders according to a certain relation. This is usually achieved through manual operation or complex formula.

esCalc, the desktop BI software, introduces the concept of “homocell”. It is the ideal spreadsheet to solve the above-mentioned problems. The core concept of esCalc is to treat the cells of various business statuses differently, and treat the cell of the same business status uniformly. In esCalc, filtering the summary value will not affect the detail data. The formula entered in the detail data section will be copied and pasted to other homocells automatically, while not the summary section. The homocell relationship between cells ensure both of them being in a same set, so users can seek the intersection set and union set conveniently; Multiple sheets can be merged through the join or union operation according to the homocell relation, even for the data at various levels and in the uncertain orders.