Business Intelligence (BI) is the organised use of data in the business environment. Usually it is used to analyse, predict, and report the performance. Specialised software BI tools are used to do this function. However with right support, spreadsheet software like Microsoft Excel ( part of Microsoft Office Suite ) can be a great tool; Excel can be deployed to do certain BI functions with ease.
Visicalc was one of the early spreadsheet. Lotus 1-2-3 was the leading spreadsheet next. Now, Excel has the largest user base, known and employed by almost all environments, personal and professional. The main concept of spreadsheet is ‘cells ‘, where in , data values are stored and enriched in many ways. Cells can contain any form of data. In spreadsheet, formulas can calculate new values from existing values. Macros are used to do simple programming. And then there are multiple ways of visualising and presentation.
Probably the poor handling of data and report generation was the earlier limitations in spreadsheets that prevented from being used for BI functions. BI systems should be integrated to data. The database should have adequate security. Real time updating is another sought after feature. The data presented should be ready for high quality analysis. Also, BI systems should be scalable to the limits of data with good speed in processing. Early stage spreadsheet did not meet all these requirements.
Of late, BI is becoming a user function rather than a specialist function. This is called as BI for everyone. Enabling this BI for every one involves using software tools that are available with every user, familiar to every user, easily manageable by the user. Towards this, Excel is a natural choice and becomes a great fit. Also, the ever growing features and capability to handle large volume of data makes Excel even more attractive!
Microsoft has been trying in the past to make BI more accessible to business users by incorporating and integrating some of its flagship products like Microsoft Office, SQL Server, SharePoint Server. As Microsoft Office is one of the programs that can be found in almost all desktop PCs, and Excel is one of the primary data tool, users figure out their own ways of analysing data and doing certain basic BI functions. In addition, there are some built in capabilities that facilitate the user. Also, in an enterprise environment, there are multiple databases and software applications. Not all are made available to all users. In that scenario when some users have the privilege of using costly BI tools, some users have to manage with their limited IT Infrastructure hardware and software.
Linking RDBMS data with excel, both to import and export, has been made easy without doing any additional programming. Excel 2010 has got a built in mechanism to connect with SQL Database server. If we know the name of the database, user name and password for connecting to SQL Server, we can pull data in to Excel.
There are some specialised add on capabilities built in with Excel. One of the important one is PowerPivot that allows user to anise millions of rows of data from disparate data sources like web. ExpertsTown is presenting about PowerPivot in a separate article. There are some third party add on tools also available for Excel to enhance its BI functionalities.
BI tools are required by every one and Excel is one of the readily available tools on our desktop that we can try to male use of ingeniously!