Power of Excel, BI.

What is BI?

Business intelligence (BI) is the process of aggregating, storing, analyzing, and then reporting that data in order to make decisions and future forecasting, current business state, Risk involve, predict,manage business performance and take business decisions.

Tools Selection for BI.

In current market there are lots of BI tools available for analysis. While selecting BI tools it is advised that to select the tool which can interact with all the versions of Microsoft Excel effectively and also should be able to interact with other MS Office products. 

Why excel support is required in BI Tools?

Lots of BI vendors say that "Our Bi Product offer analysis". But if you check in the market there
are lots of BI vendors who have changed their tool to support Excel or use the power of Excel in analysis and reporting, like Cognos, which unveiled Go! Office; Business Objects, which introduced Live Office; and Information Builders, which recently introduced Quick Data for WebFocus. Excel is used widely because Excel has lots of in built spreadsheet functions, analytical features, and universal presence on desktop.

The current king of Business Intelligent (BI) tools is Microsoft Excel. Microsoft states that there
are over 150 million excel users, with many of them using Excel for BI-reporting and analysis of corporate data.

The spreadsheet program that some experts have called “KING OF BI”, “Number One BI
Tool” and I Say “Heart of BI” because without excel its very difficult to get BI reports and analysis. Excel is more widely used for planning purposes. To prepare plans, one should have to starts from actuals, and that actuals comes from BI Report. In the absence of a planning system, there is not better place to enter plan data than in excel.It's perfect for entering data, doing what-if-analysis or producing forecasts based on formulas, thanks to its analytical features and its universal presence on desktop.

Also in the perspective of the software development or IT professionals rarely prefer the Excel as BI Tool but BI reports are very dynamic and the tool which is developed for the BI should be very flexible which can adopt the change immediately and produce report.

Frequent changes in Business Logic and tracking Metrics require the power of the change
at end-user. Most of the time the changes require is very simple but critical from business perspective. In case of BI tools the end user/ management have to take help of the IT/ BI specialist developer for customization, this involves to go through whole SDLC, Find sponsor for project, request for requirement change then management will assign project to one development team, and then after some period of time the reports added to existing tool or new tool is developed on the basis of the requirement. The whole process for additional changes will
become very long for Business. However if Excel is used as BI, Excel is the Smart tool which allows users to change reports in a few minutes or hours and which is the most important feature for the BI Tool that enable users to rapidly develop and maintain dashboards, including dynamic, interactive data capabilities such as drill-down, filter, and sort. Second thing is that customization can be done by the User him self, it doesn’t required any programmer. If user is aware of some
coding (VBA) skills then he can do automation of the report and reduce manual input work.
With Excel BI User can analyze and report data from any number of sources on one page.

No other BI system provides such analytical power.
Excel 2007 is the new version of Excel with more analytical and reporting features. It provides
more spontaneous access to OLAP data, conditional formatting based on the KPI. Excel 2007 is more powerful because of the feature of integration with Microsoft SQL Server 2005 Analysis Services to provide a rich end-user analysis, newly designed PivotTable reports, a new formula-based model for accessing Analysis Services data, improved sorting and filtering capabilities, and a completely redesigned visualization engine. Bi user can share Excel workbook to browser based environment using SharePoint Excel services. It can fully interactive, data-bound workbooks that include charts, tables, and PivotTable reports as parts of a portal, dashboard, or business scorecard, without requiring any help from IT or development team, and that present a "single version of the truth."

Alex Payne, group product manager, Microsoft office Business Applications, says “We have different approach when we say BI features are just part of Excel.” It is not separate add-on that you pay for. We are providing BI natively inside Excel.” 

Excel Services for BI 

It is great to use excel to create BI Reports and analysis, but if you give this copy of excel which
contains report to many people and in future if process or functionality needs to be changed,then its very difficult to redistribute the new version of Excel report to the whole user group. “One version of truth”…. Where excel based BI system fails.There is also a chance of exposing the business proprietary formulas to people or user group who are not meant to see it. The problem we all face is “HOW DO YOU GET THIS DATA PUBLISHED SO OTHERS CAN USE AND INTERACT WITH THE DATA WHILE KEEPING PROPRIETARY INFORMATION HIDDEN.” Answer is Excel services.

Excel Services is a new server technology that was designed from the ground up to be scalable and robust with web based interface which uses Excel Web Access which is created by using client application Excel 2007. Workbook authors no longer need to send them in e-mail messages. Sending workbooks in e-mail messages often results in multiple copies of a workbook, essentially creating conflicting the "single version of truth" and exposing proprietary formulas and logic.

Another advantage is in a multi user scenario, each user has own web session. The server loads the workbook in memory according to the user permission of access read-only, (or targeted
user group defined by author).If a user applies a filter to a workbook, other users are not affected by those changes, and their interactions do not modify the original file.

To create a PivotTable report, user can interact with Microsoft SQL server analysis services data, select the cubes, fields.The new cube functions added to the Excel 2007 and Excel Services which add on to the business intelligence. Using that You can convert PivotTables directly into cube functions which enables general users to rapidly model data when needed, to support business decisions.

With the Excel 2007 business user can ascertain how to respond change needs without the involvement of the IT or Programmer. Using cubes Users can pull in data from SQL server analysis services and various other data sources, which greatly increases the flexibility to combine external data points and create reports and analyse the data effectively.

In Excel 2007, a user has full control over the content, whereas in Excel Services, the workbook author can control the level of functionality that is available to the workbook consumer, in addition to controlling the visibility of formulas and the ability to edit data values.

Following snap of Excel 2007 shows the Pivot table with different types of the KPI presented in various ways from cube data.  

Bringing Data Together in Dashboards

You can use the new features in Excel 2007 and Excel Services to access, manipulate, and display data in new and different ways. When data is ready for publication, information workers can combine their data with other data in dashboards. Dashboards enable visualization and reporting on large amounts of data in an easily consumable format. Excel Services and SharePoint Server 2007 have the ability to create business dashboards is a key business intelligence capability. Below figure shows a dashboard that was created by using Excel Services, KPIs, and filtered Web Parts from Office SharePoint Server 2007. It focuses on identifying trends, anticipating challenges and opportunities, and supporting business decisions.

Dashboard-using Excel Services

No code is required to create dashboards. SharePoint Server 2007 includes a set of Web Parts that enable users to develop and maintain dashboards, including dynamic, interactive data capabilities such as drill-down, filter, and sort with familiarities with Excel by using PivotTables reports, Excel charts, and formulas. Dashboards can also take advantage of SharePoint Server 2007 KPI web parts which are external to Excel, which are highly visual representations of important business factors.

One big limitation with excel services is the inability to utilize the excel object model and a spreadsheet with the VBA code behind cannot be published to Excel Server.

SQL server reporting-services Integrated with Windows SharePoint Services v3 to enable publishing, viewing and management of reports.

In SharePoint, the data for KPIs can come from any SharePoint list, an excel workbook, SQL server analysis services or it can be manually entered.

The data is exposed using a KPI list and KPI appropriate data. The KPI web parts then expose that data in to SharePoint site.

Conclusion
Excel Services enables end users to create robust solutions without involving developers, and it  gives workbook authors, database administrators, and developer’s greater control over the environment. The combination of the Excel Web Access and the Excel Web Services API provides rich and versatile access to workbooks. At the same time, the security model is the same whether you access the workbook through Excel Web Access or programmatically through the Excel Web Services interface.

Workbook authors use Excel 2007 to create workbooks and accompanying business logic, including connecting directly to back-end data sources for live data. Those authors, including non-technical users, can create workbooks, reports, and dashboards with dynamic data, more securely sharing that information across the enterprise and making it available to other users and applications. Developers can take advantage of business logic created by those business users without needing to re-create it in application code. The physical infrastructure that underlies Excel Services is highly scalable and configurable. This enables you to support changing business needs as they arise and provide guaranteed availability for mission-critical calculations.

The combination of Excel Services and Excel 2007 enables both technical and non-technical users to securely share, manage, and reuse data and business logic, creating a true enterprise solution for workbooks.