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.

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

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.