April 23, 2013

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.

April 9, 2013

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?

April 7, 2013

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."

April 2, 2013

Self-service BI, the Next Big Thing or Disaster Waiting to Happen?

The last day, i join a poll in it-toolbox 'Self-Service Business Intelligence: Good or Bad?' My answer: A win-win for IT and business professionals. Then will Self-Service BI be the Next Big Thing or Disaster Waiting to Happen?
Here is an article, Self-service BI a 'win-win' for IT and biz, by zdnet editor Jamie Yap, who express the same opinion with me. 
Considering the challenge of handling and then analyzing increasing volumes and varieties of data, self-service business intelligence (BI) is a "win-win" proposition for companies, allowing business end users to quickly get relevant insights on their own, and freeing up IT to focus on the related critical matters beneath the user interface such as data governance.
Yet, this "liberalization" with self-service BI would just be a myth, unless end users are truly empowered in reality to analyze data and create analytic reports by themselves without turning to IT, said JY Pook, Asia-Pacific vice president at Tableau Software.

Only then will the IT middlemen not be diverted to generating reports, and can fully concentrate on value-added tasks at the backend that optimize self-service BI, such as ensuring data governance, security, scalability and network performance, the Singapore-based executive said in an interview Wednesday.

Freeing up IT resources is a significant benefit, considering the tide of big data means companies don't just need better analytics capabilities, but also better storage and database management to cope and curate the data, he explained.

Spot new patterns through visualization
According to Pook, data visualization is one of the key factors to help non-IT end users interact with data easily and effectively on their own.

Tableau's software tools, he said, leverage the fact that when data is presented in a variety of ways quickly, as decided by the user, new patterns get spotted. It causes users to form new insights and new questions that need answering, and this freestyle, iterative process continues in real time as the "drill down" analysis goes on.

That users, based on their own needs and discretion, can "see the data move in front of them" as they slice and dice it, contrasts with traditional enterprise BI, said Elissa Fink, CMO at Tableau Software, who sat in at the same interview.
Pook agreed, noting that self-service BI addresses a fundamental problem of traditional BI. The latter cannot keep up with big data as well as the fast-changing business demands and competitive landscape of data.

Traditional BI tools required specialists to do the job and were not easy to use for non-IT end users to use. So they had to send requests to IT for data analysis, waiting between days to weeks for IT to come back with a static report that may not accurately reflect the constantly changing business environment, he explained.

Simple, but not oversimplifying
While self-service BI is used by non-IT users, it does not mean the resulting data analyses will be oversimplified and hence paint a potentially inaccurate picture, the two executives emphasized.

Fink highlighted that self-service BI is meant to be intuitive, and the more experience users have to explore and work with data, the more refined and sophisticated their queries and insights become over time.

Preventing or protecting end users from facing the high volumes of data is doing them a "great disservice", she said. "Why can't someone at the frontline get insights? Let them do it [and learn]."

Pook pointed out it is usually the IT departments, not end users, that need more convincing to implement self-service BI, since they would naturally be concerned with issues like security, governance and compliance.

He added that raising awareness and educating companies of the possibilities of self-service BI is important. Some vendors might describe their product as such, but if it ultimately still required IT to write reports, it does not fulfill the promise of self-service BI at all.