How to Integrate Excel and Business Intelligence Software

from wikiHow – The How to Manual That You Can Edit

In controlling department Excel is used to prepare and customize reports, presentations and ad hoc analysis according to management guidelines. Excel provides great functionalities too controllers and is essential tool in everyday work without alternative. On the other side business intelligence software is great in reporting, planning, workflow, consolidation, analysis, masterdata management and similar processes in controlling. Lack of integration between Excel and Business Intelligence software causes many everyday problems.

Steps

1. If Excel is used as a source for non structured or external data not contained in any legacy system, structure of form needs to be stabilized and to be fixed.
2. Apply ETL on Excel file and import data into Data Warehouse/Business Intelligence software.
3. Create logical multidimensional set of data – OLAP cube upon data form Data Warehouse.
4. Use ODBC connection and import data into Excel. ODBC will provide automatic connectivity of Excel with live data from production server used in Data Warehouse.
5. Apply data filtering in Excel pivot based on business definitions.
6. Use data from filtered pivot tables in standard reporting processes.
7. Link data from filtered pivot tables with standard templates in other documents in controlling tasks.

Tips

• Link data from filtered pivot tables with summary default reporting template. Many Excel files can be used as sources and one Excel file as summary document. Open all Excel files at same time. Use refresh button to update data in pivot tables. Extend report in summary file with copied last column. To get latest data in summary report replace date with new date in formula bar of column.

Warnings

• Excel 2007 can not handle more then several pivot tables per file. Use separate file for other group of pivot tables.
• Excel 2007 has limits in formula cells. Can not handle formula longer then 1000 characters. Use additional cells for helping calculations.

Sources and Citations

• http://www.business-intelligence-secrets.com – Original source, shared with permission.

Article provided by wikiHow, a wiki how-to manual. Please edit this article and find author credits at the original wikiHow article on How to Integrate Excel and Business Intelligence Software. All content on wikiHow can be shared under a Creative Commons license.

Related posts, articles and documents:

• Excel mappings systematic solution
• Excel in Business Intelligence and controlling
• Controlling and Management Functions