Smarter ideas worth writing about.

Using Excel as a BI Solution

 

Timely access to data and ad hoc analysis are crucial needs that affect an organization's decision making. As such, orgs are increasingly looking beyond traditional, canned/tactical reports to a "self– service" business intelligence model. There is no other tool that better serves this model than Excel. Excel has always been the tool of choice for most analysts, given the familiarity to users – "Just give me Excel!" is a common phrase we frequently hear. Recently, Microsoft has taken Excel reporting capabilities to whole new level. These features aim to empower users at all levels of the organization and remove the overhead from IT of monitoring and managing user content.

The BI reporting capabilities of Excel can be broadly divided into 2 main groups:

  1. Native Excel

    When one thinks of native excel reporting capabilities, Excel pivot tables and pivot charts are the first things that come to mind. Pivot tables have long been used for analytics with Excel. They are interactive, and allow a user to slice–and–dice through simple drag and drop. Excel 2010 provided further enhancements with improved charting capabilities, advanced slicers, conditional formatting, and spark lines. Further, you can connect to SQL Server databases, OLAP cubes, SharePoint, and even external data sources–and then build some visually compelling reports using the native features of Excel.

  2. Excel Add–ins

    The process of transforming data to make it analytic–friendly is still limited and can be very laborious–both manual and non–repeatable processes. There are several add–ins that are available that make Excel an enterprise reporting solution, and enable users to build reports and analyze data in a matter of seconds. We will briefly touch upon a few of the popular and highly–effective add–ins.

    • PowerPivot

    PowerPivot is a powerful data analysis tool that you can download for free from Microsoft.com (if you own Excel 2010). PowerPivot combines and aggregates massive amounts (in terms of billions) of data from virtually any source in matter of seconds. This is made possible by the VertiPaq engine that compresses and processes data in memory so the only limit on performance is the memory of your machine. In addition, PowerPivot also supports multi–core as well as 64–bit processors.

    PowerPivot uses features of Excel like PivotTables and PivotChart along with Data Analysis Expression to create interactive reports. Besides doing some intuitive data analysis, users can easily share reports and collaborate via SharePoint 2010 thus enabling Excel as an enterprise solution.

    • XLCubed

    Excel is still limited when working with OLAP cubes. For example, Excel falls short when working with cube–aware calculations and multi–dimensional visualizations. For advanced analytics, we recommend our partner XLCubed, a Microsoft ISV solution. XLCubed brings the power and flexibility of a true OLAP or multi–dimensional reporting into Excel. It leverages Excel's strength as a calculation and modeling tool, while the connectivity to SQL server Analysis Services removes the risk associated with Excel as a data source or spreadmart.

    Through XLCubed, users can create sophisticated reports and analytics that provide significant flexibility in terms of layout and format. XLCubed also enables quick decomposition of any value in the report and visual data exploration through interactive charts. The end users can produce world class dashboards and publish it to the XLCubed web portal. With the enterprise license, one can also publish XLCubed dashboards and reports to SharePoint and iOS platforms. In my opinion, there is nothing in the Microsoft stack that comes even close to XLCubed for SQL Server Analysis Services cubes.

With all the advanced reporting and data analysis capabilities, Excel is undoubtedly the number one enabler of the "Self–Service" BI community. With the different add–ins that are available, organizations are combining the richness and familiarity of Excel into an enterprise Business Intelligence platform. If you are interested in seeing a demo or hearing adoption success stories, send me an email.

Share:

About The Author

Practice Manager
Elaine leads Cardinal’s Cincinnati Data Solutions Practice and has a wide breadth of experience in Business Intelligence, Data Warehousing, and Data Visualization.