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.

What Agile Desktop BI Tool Really Means?

BI (Business Intelligence) refers to the intelligence and ability to enhance the enterprises competitiveness, involving report presentation, reporting result calculation, OLAP analysis, business data calculation, data mining and predication. Among these, there are both the technician-oriented high-level systems, and the business user-oriented agile desktop BI tools. In this post, we only talk about agile desktop BI tools. But what is agile desktop BI tool and what are their standards?

Agile desktop BI software should have the following features:

Common BI features support:

The agile desktop BI software should fit for those business personnel to prepare the static report by themselves, even if they are inexperienced in IT. The friendly reporting design interface is necessary. It also supports the rapid reporting, the business personnel-oriented data processing, and high fidelity report preparation. In other words, it is capable to keep reports consistent in the stages of design, preview, pagination & printing, and export.

For example, make a product sales situation report for specific products to present the monthly sales of 3 products, and their link relative ratio, and monthly year-on-year growth.

The agile desktop BI software supports the calculation on the result of common reporting tools. Excel and the plain txt are the export formats supported by most reporting tools. It can import and calculate them directly; supports the data pasting from the report result directly on the clipboard; provides the calculator-style operation for business personnel; capable to conduct any process on data and the calculation between steps can be transited smoothly. 

For example, the reporting tools generate the below report result:

Find the big clients who account for 60% of the total sales of the company based on the above data. The result is as follows:

The agile desktop BI tools support the true OLAP analysis. It is capable to perform the interactive analysis arbitrarily and intuitively, decompose and simplify the obscure analysis goal. It provides the basic analysis methods that are both simple and easy to use. Then, lots of advanced analyses can be implemented through the free combination. esCalc is such kind of desktop BI software. With an Excel-style, esCalc becomes relatively easier to understand and learn. Moreover, esCalc also provides a range of powerful advanced functions to solve the complex problems regarding OLAP.

Agile installation deployment:

The size of the agile BI tool should be very small and easy to install and uninstall. For example, esCalc installer is only dozens of MB and only requires a few clicks to install and run, which is the typical example of agile desktop BI software. Agile desktop BI tools are capable to run on most desktop computers independently, not having to deploy the additional server:

Agile desktop BI software supports various mainstream databases, like esCalc which can manipulate data from different databases, including MSSQL, Oracle, Access, MySQL, DB2, and Sybase. This desktop BI software also supports the local data files, for example, Txt, Log, tab, and other text files; Excel 97, Excel 2010, and the Excel of other versions. It also supports the interactive calculation between various data sources, such as the calculation between Oracle and Excel.

Agile formula functions:

Agile BI software also provides the agile formulas and functions, so that the business personnel can easily represent the relatively complex calculations, such as comparison on year-on-year basis, link relative ratio, set operations, ranking and row number calculations.

For instance, calculate the players whose rankings are among the top 5 in every game. We take esCalc, the agile desktop BI software as an example. The data available is as follows:

Simply input "={A3}" in E2, and the top 5 players of each game will be calculated out in E2, E8, and E14. Input "={E2}.isect ()" in E1, then the players whose rankings are among the top 5 in every game will be calculated automatically. In which, the function "isect" is to calculate the intersection of sets, and "{E2}" is a set to indicate "cells shares the same meaning with E2 regarding business” (homocell by name), that is, E2, E8, and E14.

It also has the similar function like:

diff(): Calculate the difference set of a group of data; for example, calculate the employee who made a full attendance in this quarter. You can calculate through the formulas like [set of employees, employee who ever absent in the 1st month, employee who ever absent in the 2nd month, employee who ever absent in the 3rd month].diff() to calculate.

There are also other advanced functions available, such as the sum(~*~) to calculate the sum of squares of a certain group of data; cumulate() to calculate the cumulative value of a group of data, ord() to calculate the relative row number in the calculation hierarchy, and ranki() to calculate the ranking of a certain number in a group of numbers.

All in all, agile desktop BI software supports the common BI functions, and is business personnel-oriented in the respect of installation deployment, and formula function. 

Broadening the Reach of Self-Service BI

By Gadi Yedwab, founder of Explore Analytics.

(Note: I'm  glad that Explore Analytics and Raqsoft are making efforts to self-service applications.I show high respect and agreement with the opinion in this article.)


The necessity of self-service is obvious once you realize that traditional BI has limited reach within user communities. For example, BI dashboards are typically tailored to the needs of decision makers and leave out a broader group of analytically-minded users who could leverage data to innovate and make improvements. For small companies and for teams with limited budgets, self-service is often the only viable option because current BI approaches require people’s time and expertise to setup.
The proliferation of spreadsheets as tools for data analysis is a proof that that existing needs are unmet.
The current approaches to self-service often suffer from the same problems that limit the reach of BI to broader user communities. This article focuses on these problems and discusses a new approach that can significantly broaden the reach of BI.

The Problems with Current Approaches to Self-Service BI

• IT organizations often concentrate their efforts on the most strategic data, while leaving a lot of useful data outside the scope.
• For performance reasons, IT often opts for data warehousing. This approach is expensive and therefore has limited reach. Small companies lack resources and find this approach to be cost prohibitive.
• Providing self-service by periodically delivering data sets for analysis in spreadsheets or desktop tools does not satisfy the need for real-time data. Latency of information is often cited by users as the major drawback of their BI solution.
• Desktop BI tools and spreadsheet downloads can be a security risk when users keep data on laptops, or send it via email. This approach also makes it hard to share and collaborate in the analysis.

The Spreadsheet as a Self-Service BI Tool
Let’s admit it: the number one tool for self-service BI is the spreadsheet. It’s been that way since the invention of the spreadsheet, and it still is. The most typical scenario is exporting data from an application and then analyzing it in Excel. The main drawback of this approach is that it’s outside the skill-set of most users.
Sure, having the data in a spreadsheet is better than having nothing, but using Excel for BI has serious limitations. Most users do not have the necessary skills to analyze data in Excel, especially if the data resides in more than one table. Even for users who are skilled in Excel, the data quickly becomes stale and there is no good way of collaborating with other users in the analysis.

A New Approach to Self-Service
The new approach minimizes the need for data warehousing thereby reducing costs and providing real-time data. It uses cloud-based solutions to facilitating collaboration and sharing. Moreover, cloud-based tools can bring the required expertise and cost down to within the means of small companies and teams inside large companies.
The premise is simple: if a solution can be useful to small companies with limited resources, then it can be very useful for all the under-served constituencies inside large companies. The spreadsheet already proved that, but we can do much better than that.

Reducing the Need for Data Warehousing
For more than two decades the common wisdom has been to keep ad-hoc query away from production systems. This is generally still a good idea. However, there are good reasons to reconsider that widely accepted notion.
A good self-service BI tool can control and prevent runaway queries.
Explore Analytics, for example:

• Only joins tables on the primary key
• Puts a limit on every query to prevent it from returning too many rows
• Pushes all the filtering and aggregation to the data source thus eliminating the need to pull large query results
• Controls the number of queries that concurrently execute against a data source

Modern database servers eliminate three reasons why a bad query would previously bring a database down to its knees.

• Having multiple CPU cores, the database performs well even if several cores are momentarily tied up.
• Large portions of the database reside in memory and a full-table scan can be done without noticeable impact to other transaction.
• Liberally creating indexes doesn’t come with the performance penalty that it had a decade ago.

While “Big Data” is an important category, a lot of useful data reside in tables with less than a few million rows. Running a query to summarize data across a million rows can complete in a few seconds. That wasn’t the case a decade ago.

Using the Cloud
Having a centralized web-based self-service BI solution allows users to share and publish their analysis. It allows teams to leverage the diverse strengths of individuals and review the analysis to increase its accuracy. Analytically-minded people can create data analysis and share it with the rest of the team.
By keeping data sets and reports securely in the cloud, companies can avoid distributing data to laptops, desktops, and passing it around in email attachments.
If you’re thinking that the same can be accomplished using an in-house web-based solution, you may be right, but you should consider the cost and expertise that’s required to build and support this solution. A cloud solution can greatly reduce the expertise that’s needed as well as the direct costs of the service. It then becomes feasible even for small companies or teams.

IT Call to Action
IT organizations should identify data sources for real-time access. For other data sources, consider publishing data sets to the Cloud. Then provide a cloud-base tool such as Explore Analytics to deliver self-service analysis to users and unleash their creativity.

Application Vendor Call to Action
Application vendors should enable real-time data access by providing web-services APIs that allow ad-hoc query including joining data, filtering and aggregation. Remember that if you allow tools to push the filtering and aggregation to your application, then they’d have no need to pull large results in real time.

40+ Free Data Analysis and Visualization Tools 2013

Original posted on computerworld by Sharon Machlis.
This article list 30+ tools for data visualization and analysis, including desktop BI tools, tools for data analysis, visualization and presentation, JavaScript libraries for maps, charts and other data visualizations.

Skill levels are represented as numbers from easiest to most difficult to learn and use:

1. Users who are comfortable with basic spreadsheet tasks

2. Users who are technically proficient enough not to be frightened off by spending a couple of hours learning a new application

3. Power users

4. Users with coding experience or specialized knowledge in a field like GIS or network analysis.

Data visualization and analysis tools

Tool	Category	Multi purpose visualization	Mapping	Platform	Skill level	Data stored or processed	Designed for Web publishing?
R Project	Statistical analysis	Yes	With plugin	Linux, Mac OS X, Unix, Windows XP or later	4	Local	No
Data Wrangler	Data cleaning	No	No	Browser	2	External server	No
OpenRefine (formerly Google Refine)	Data cleaning	No	No	Browser	2	Local	No
Google Fusion Tables	Visualization app/service	Yes	Yes	Browser	1	External server	Yes
Impure	Visualization app/service	Yes	No	Browser	3	Varies	Yes
Many Eyes	Visualization app/service	Yes	Limited	Browser	1	Public external server	Yes
Tableau Public	Visualization app/service	Yes	Yes	Windows	3	Public external server	Yes
VIDI	Visualization app/service	Yes	Yes	Browser	1	External server	Yes
Zoho Reports	Visualization app/service	Yes	No	Browser	2	External server	Yes
PowerPivot**	Analysis and charting	Yes	No	Excel 2010 and some 2013 versions on Windows	3	Local	No
Choosel	Framework	Yes	Yes	Chrome, Firefox, Safari	4	Local or external server	Not yet
Exhibit	Library	Yes	Yes	Code editor and browser	4	Local or external server	Yes
Google Chart Tools	Library and Visualization app/service	Yes	Yes	Code editor and browser	2	Local or external server	Yes
JavaScript InfoVis Toolkit	Library	Yes	No	Code editor and browser	4	Local or external server	Yes
D3	Library	Yes	Yes	Code editor and browser	4	Local or external server	Yes
Quantum GIS (QGIS)	GIS/mapping: Desktop	No	Yes	Linux, Unix, Mac OS X, Windows	4	Local	With plugin
OpenHeatMap	GIS/mapping: Web	No	Yes	Browser	1	External server	Yes
OpenLayers	GIS/mapping: Web, Library	No	Yes	Code editor and browser	4	local or external server	Yes
OpenStreetMap	GIS/mapping: Web	No	Yes	Browser or desktops running Java	3	Local or external server	Yes
TimeFlow	Temporal data analysis	No	No	Desktops running Java	1	Local	No
IBM Word-Cloud Generator	Word clouds	No	No	Desktops running Java	2	Local	As image
Gephi	Network analysis	No	No	Desktops running Java	4	Local	As image
NodeXL	Network analysis	No	No	Excel 2007 and 2010 on Windows	4	Local	As image
CSVKit	CSV file analysis	No	No	Linux, Mac OS X or Linux with Python installed	3	Local	No
DataTables	Create sortable, searchable tables	No	No	Code editor and browser	3	Local or external server	Yes
FreeDive	Create sortable, searchable tables	No	No	Browser	2	External server	Yes
Highcharts*	Library	Yes	No	Code editor and browser	3	Local or external server	Yes
Mr. Data Converter	Data reformatting	No	No	Browser	1	Local or external server	No
Panda Project	Create searchable tables	No	No	Browser with Amazon EC2 or Ubuntu Linux	2	Local or external server	No
Weave	Visualization app/service	Yes	Yes	Flash-enabled browsers; Linux server on backend	4	Local or external server	Yes
Statwing	Visualization app/service	Yes	No	Browser	1	External server	Not yet
Infogr.am	Visualization app/service	Yes	Limited	Browser	1	External server	Yes
Datawrapper	Visualization app/service	Yes	No	Browser	1	Local or external server	Yes
Cascading Tree Sheets	Library	Yes	Yes	Browser	1	Local or external server	Yes
Dataset	Library	No	No	Browser	4	Local or external server	Yes
Leaflet	Library	No	Yes	Browser	4	Local or external server	Yes
Searchable Fusion Table Map Template	Library	No	Yes	Browser	3	Local or external server	Yes
Tabletop	Library	No	No	Browser	3	Local or external server	Yes
Data Explorer**	Data acquisition, data reformatting	No	No	Excel 2010 and 2013 on Windows	2	Local	No

But i want to add 2 desktop bi tools, esProc for statistical computing and esCalc for business analysis and reporting. Both are under skill level 2.

Empower SQL Ability to Spreadsheet

The spreadsheet can implement the visualized calculation to some extent, and the nontechnical people can perform some rather complex calculations without having to learn the SQL. However, as the core of SQL, the relational query is unable to be implemented through common business spreadsheet software, which adds complexity to the apparently simple problems of multi-table join.

For example, the Finance department needs to calculate the salary, and the relevant data is stored in ”standard sheet”, ” Absence sheet”, and ” performance sheet”, as shown in the below figure:

 If these three sheets can be joined, then you can compute it easily via the standardWages*(1+Evaluation-Absence/40)+Bouns, as shown below:

 

However, the common business spreadsheet software like Excel is usually quite inconvenient for such Union and Join actions. The manual data copying is error-prone, and it will be even more exhausting if the data volume is huge. Considering these factors, composing formula is a great method, for example, in D2, E2, and F2, respectively compose the 3 formulas:

=IFERROR(INDIRECT("'Absence'!"&ADDRESS(MATCH(A2,'Absence'!$A:$A,0),2)),0)

=IFERROR(INDIRECT("'Performance'!"&ADDRESS(MATCH(A2,'Performance'!$A:$A,0),2)),0)

=IFERROR(INDIRECT("'Performance'!"&ADDRESS(MATCH(A2,'Performance'!$A:$A,0),3)),0)

The above-mentioned formula requires the strong technical competence and rich user experience with business spreadsheet software. In fact, the qualified capable people would rather import the data to the database and use a simple statement of “relation query” to solve the problem, because this formula is hard to understand and error-prone.

Isn’t there any better business spreadsheet software or Excel alternative? Actually, we’ve got one – esCalc, a good Excel alternative and an innovative desktop BI tool which is capable for the relation query. To join the Absence sheet with the standard sheet, simply use the Join function, as shown in the below figure:

 

Similarly, you are only allowed to perform the Join action for once for the Performance sheet. The ultimate result is the just what we have expected:

 

        

The “perform for once” even includes the formula to calculate the salary. In G2, just enter the formula for once and the formula will be automatically copied to G3, G4, and other cells sharing the common business sense. We call such cells Homocell.

The Join action is dependent on the homocell to some degree. The advantage of group table at multi-levels is to join the data correctly, even those data at various levels. Similarly, in the grouping table at multi-levels, the formula will be copied and pasted to the homocells. For example, the formula in the summary section will be copied and pasted to other summary section, and the data in the details section will not be affected. Therefore, for the huge amount of workload needing adjust before in the business spreadsheet software has been automated in esCalc, the smart desktop BI tool.

The Spreadsheet is Still the King of the Business Intelligence World

"BI is not an upgrade to the spreadsheet", "Stop trying to use spreadsheet as the BI tool" - BI vendors are frequently heard to make such comments. People may thus assume that the spreadsheet is on the decline, and feel embarrassed by their Microsoft Excel, RaqSoft esCalc, LibreOffice Calc, and OpenOffice Calc tools because they may have a wrong impression that these tools are not intelligent enough to cross the BI threshold.

The technology consulting firm Gartner Group Inc. once precisely predicated that BI would be the hottest technology in 2012. The year of 2012 witnesses the sharp and substantial increase of BI. Unexpectedly, spreadsheet turns up to be the one developed and welcomed most, instead of the SAP BusinessObjects, IBM Cognos, QlikTech Qlikview, MicroStrateg, or TIBCO Spotfire. In facts, no matter it is in the aspect of total sales, customer base, or the increment, the spreadsheet is straight the top one.

Why the spreadsheet is still ruling the BI world?

One of the key factors is its low technical requirement. The spreadsheet does not require a complex installation and deployment procedure or the expertise in SQL/MDX/Java script/.Net, let alone a great many of advisory agents and technical experts to give you command. The spreadsheet is so simple and easy-to-learn that even the "100 Compulsory Things to Do for U.S. Pupils in Summer Holiday" includes the homework of presenting the data in Excel to their parents. These are just the drawbacks of the non-spreadsheet BI tools - their technical requirements are usually too high.

Being intuitive, free, and flexible are factors in another aspect. The simple actions like query, filtering, grouping, sorting, formula composing, and judging are the routine work common and understandable to everyone. The free combination of these operations can bring about numerous algorithms. However, the non-spreadsheet BI tools are all hard to understand, and restrictive to your innovative ideas. Just to name a few, you can only calculate in the model of the respective BI tool and must have an idea of the rotation of dimensions.

Most importantly, the spreadsheet is business-personnel-oriented. What is the purpose of BI? BI is aimed to enhance enterprise competiveness through business computing. This objective requires the business personnel to carry out the analysis from the business perspective when confronting to the business opportunities and risks. However, the non-spreadsheet BI tools are designed for the IT experts. They may know the SQL and linear regression, and are capable to solve the 20% BI problems, but they may not necessarily know the business.

It is the low technical requirement, intuitive and flexible calculation capability, and business-expert-oriented easy solution to the 80% BI problems that makes the spreadsheet still rule the BI world.

Despite the gap that still remains, the non-spreadsheet BI tools are more and more close to the spreadsheet, for example, the QlikView which is reputed for its agility. Some spreadsheet applications have remedied the drawbacks of traditional spreadsheet calculation, for example, the esCalc known of its great computing capability.

Related Articles:

How Well Do You Know Spreadsheets?
SQL Visualization in the Spreadsheet
Self-service BI, the Next Big Thing or Disaster Waiting to Happen?

Can Users Really Do BI from the Desktop Tools?

To identify and respond quickly to trends and patterns in business data, users or a normal business staff in the company need self-service BI tools. I think the  self-service desktop BI tools should be their first option. The faster the reports or results can be produced, the better. They don't have to depend on IT department.The user aid can be a point which i think deserve most attention from the self-service desktop BI tools.

This is an article from techtarget technology reporter beth stackpol,self-service BI needs flexibility, governance, user Aid. Self-service BI tools, to free IT workers from having to be hands-on in the data analysis process, that is a thing the BI vendors have been doing since 20 years ago. See this article below:

While the self-service business intelligence moniker implies that business users will be able to effortlessly partake in BI activities, implementing tools that are truly self-service and that get widely embraced isn't so simple. Technical, procedural and cultural issues can all trip up deployments if BI managers don't plan carefully and forge a close partnership with their business counterparts.

In fact, the road to self-service BI can be quite bumpy, according to a survey of 234 BI and IT professionals, business users and consultants conducted in July 2012 by Wayne Eckerson, director of TechTarget Inc.'s BI Leadership Research unit. Sixty-four percent of the respondents rated the success of their self-service BI initiatives as "average" or lower.

The biggest challenge they cited was that self-service BI tools require more training than expected; that answer was chosen by 73% of the respondents. In addition, 61% said using self-service software "creates report chaos" and 42% said the tools "confuse users." Fifteen percent even said they were getting more requests for help with self-service tools than they were before. "How can something be 'self-service' if it requires the IT department to train and support users continually? That's the conundrum of self-service BI," Eckerson wrote in a report about the survey.

The key to avoiding such problems, consultants and experienced BI managers say, is eschewing a one-size-fits-all approach and instead deploying a set of tools and processes that will accommodate power users as well as "information consumer" users who might require substantial training and handholding.

Action item: Support user diversity

"Just installing an easy-to-use BI tool doesn't automatically mean you have a self-service BI environment," said Claudia Imhoff, president and founder of BI consultancy Intelligent Solutions Inc. in Boulder, Colo. "There are different needs within an organization. You need to know who your information workers are and what kind of self-service they really want."

For example, tech-savvy users likely will be immediately comfortable with the idea of using self-service applications to dive into BI data and create their own queries and reports. For more casual users, Imhoff said, self-service might simply mean being able to change the parameters on a report to get a different spin on the data.

Governance of users is also critical to self-service BI success, despite the fact that IT has to loosen its control over the data analysis process. Working in tandem with business managers, BI teams need to establish common data definitions for key performance metrics, such as revenue and profitability, so there is organizational consistency in analyzing them. IT and BI managers should then monitor usage of self-service software on an ongoing basis to detect and correct any compliance issues and to head off runaway queries that could choke the BI system.

Darren Taylor, president of Cobalt Talon, an analytics service provider that is a division of Blue Cross Blue Shield of Kansas City, said BI developers can help avoid such problems by hard-coding predefined performance metrics into self-service environments. "You could throw raw data into the self-service BI tool and let people be power users, but then you're talking the Wild West when creating metrics from one person to the next," said Taylor, who previously was vice president of enterprise analytics and data management at Blue KC.

Cooking up a self-service BI buffet

In much the same vein, Imhoff counsels BI managers to create a starter library of report templates and standard analytics routines as part of a self-service BI system so business users can pick and choose what they need based on their requirements. "Think of it as a buffet table of BI components," Imhoff said. "The more work IT can do on the front end, the more standardized this becomes, and it makes everything run easier."

Radiology management services provider HealthHelp did lots of work on both the front and back ends to ensure that its deployment of self-service BI tools was a success, said Steve Spar, the Houston-based company's chief information officer. IT and BI developers created standardized metrics for some of HealthHelp's more complex analytics parameters and also refined the data schema and database architecture underpinning the BI system so business users could easily locate data.

Spar said that with the right foundational technologies in place, users are truly empowered to do self-service BI, freeing IT workers from having to be hands-on in the data analysis process. "IT moves into a consultative role rather than a task-doing role," he added. "They can then help those who help themselves."