The BI application in Excel

Bi end users must rely on Excel to ensure the stability and security of BI data. BI managers have long been looking forward to a change in the status quo, but it always backfired. The place that Excel occupies in the BI process has never been lost, and the ultimate futile attempt has made the BI team more comfortable with it.

Boris Evelson, an analyst at Forrester Research in the United States, said: "The battle has been fought for 20 years, but we lost the game." "At present, BI managers can only do their best to optimize the performance of Excel in the bi aspect." Evelson An example, he recommends that Excel be integrated into BI software to export data to a table, so that Excel is a user interface and data display layer, rather than a simple data manipulation tool, its advantages can be better played.

Easier said than done; it's easy for business users to manipulate excel in their own way. "It's a bit like a cat," said Rick Sherman, founder of Athena consultancy. You can't control Excel completely. Those who say they can are deceiving themselves. ”

Sherman mentions that business users will continue to struggle with Excel until the day they get rid of it completely. At the same time, he suggested that the first task should be to have every employee of the enterprise use the same data, even if they would create their own format in Excel.

So how can we achieve this goal? Sherman, Evelson and Brydges (executives at the US Morganfranklin consulting firm) have all mentioned the same word: governance.

Governance of Excel Usage

Evelson that enterprises should implement a high degree of flexibility but can be unified implementation of the electronic form of the use of governance policies. Policies should allow different teams to report analysis when using Excel, but without the enterprise-wide BI functionality, and not as part of a typical agile process such as managing a report.

Sherman that companies can extend existing data governance programs and add Excel to their use. However, he cautioned that bi managers should be cautious about selling governance policies to corporate executives from the perspective of enhancing productivity and bringing uniform bi data and results to the enterprise. "If you want to go directly to the goal, you are likely to fail." ”

The use of Excel has gradually become one of the features of the BI process, not the problem. "People use Excel because it's the only tool that deals with data," says Brydges. He suggested that, in order not to turn it into trouble, the BI team should incorporate Excel as an important part into a fully integrated BI process so that it is no longer a stand-alone technology or a tool to connect to a bi system.

Brydges said: "Excel should be a tool to help users easily complete their work tasks, and the key is to govern when data is integrated into enterprise business processes and reports." ”

Let Excel find data errors automatically

The BI team can create predefined field values and balancing operations in Excel to automate error checking without having to manually stare at the data sequence to pick out the wrong values and formulas. In addition, controlling some spreadsheets through encryption and password protection is also an effective way to do so, if you can. Microsoft's SharePoint software and other Third-party applications can be password-protected, Brydges said.

Sherman mentions some of the advantages of SharePoint, which will help Microsoft's operating system enterprises in Excel's BI applications. Organizations can share and reuse Excel workbooks using Microsoft Excel Services software in SharePoint, or you can deploy BI widgets such as PowerPivot add-in for Excel on the server to facilitate Ensure that data integration and other data management tasks are completed outside of the stand-alone spreadsheet.

However, the overall support for Excel in the BI tool has improved significantly compared to the past. Once upon a time, spreadsheets were also seen as the bane of BI technology. In most cases, the BI vendor is using a coexistence strategy when working with Excel.

Charles Linville, founder and president of Ploughman Analytics Inc., said that in order to avoid the vicious use of Excel, the data needs of BI users should be met in a timely manner. If the operator requires different departments to approve the data request, and then wait for bi or IT staff to develop the data, then it is better to use Excel directly. "Sweep, how can there be any good solution?" he said. ”