Excel and Central  Databases

    

613-749-4695
[email protected]

Home Page

Tips and Ideas on Excel
Table of Contents on Excel

Tips and Ideas on Macros
Table of Contents on VBA

Quick Links

 

Excel as front-end and back-end of large databases

The data that you need to analyze may  reside in an Excel workbook, a centralized database, a departmental database, a data cube, a datamart, an accounting program, a manufacturing program, an ERP or BI program, on a  website  or on a printout. Only if the data has been manually written  on a sheet of paper should you enter it manually in an Excel workbook. As a rule if the data has been keyed in anywhere you should not have to key it in again saving time and avoiding mistakes.

Getting the data

If there is a printed report or a print out of the data it means that there is a PRN file that you can open with Excel. A PRN file is what you get when you select " Print to file" from the " Print" dialog box of any program.

You can save a web page from the Internet and open it with Excel.

From a centralized database (Oracle, JDE, SAP,etc), a departmental database (Access), a data cube (Essbase), a datamart, an accounting program (JDE, PeopleSoft, Smartstream, Quickbook, Peachtree, etc.), a manufacturing program (JDE), an ERP or BI program (Essbase, Cognos, BO, etc.) the people responsible for the application can EASILY extract the data that you need and create a file that you can open with Excel (TXT, CSV, XLS). This exporting process can be scheduled and the file can be extracted automatically every day, week or month.

Bringing the data into an Excel workbook

So you can open almost any kind of file with Excel. Open Excel, select " File/Open" and follow the instructions. You can then copy/paste the data manually into your workbook. If you have only one report there is really no need to automate this process. But if you are dealing with many file, this process can be automated with a very simple macro to import one file in one workbook or to import many files in one workbook or to import many files in many workbooks.

You can have access directly to the database (Microsoft Query, Pivot Tables, IApps, Essbase add-in, Interactive Excel (SAP)) but these tools are not really accessible to the majority of users. More importantly Database Administrators are kind of reluctant to give open access to the database for reasons of security and integrity of the data. Its their responsibility and prerogative. If your analysis and reports are well designed (using SUMPRODUCT) you can minimize the number of extracts that you need and DBAs will be pleased to supply you with the right files.

Departmental Reporting and Multiple Sources

In medium size and large enterprises there is a centralized corporate database. In this database one will find corporately significant data and the corporate reporting is handled at that level. Corporate reporting is only one aspect of the decision making process though. Very important decisions are made at the department level and the analysis and reporting to support these decisions cannot be efficiently handled at the database level. A very special knowledge of the data is needed to develop analysis and reports at the departmental level and the users on the front line need to be able to develop these analysis and reports by themselves with or without assistance from a professional. The tool is Excel. Excel will even allow users to develop reports using data coming from the central database coupled with data that he compiles in his own small database, data that is not corporately significant and that will never find its way into the central database.

Resistance is Futile

Excel in there to stay. In my 25 year career I have tried many reporting programs and they are all too complicated and limited. By using anything else than Excel you create a reporting bottle neck and you are depriving the enterprise of the input of one of the most important resource which is the employees on the front line. And as I have seen so many times, a lot of money is spent to purchase a new  reporting program and to train a few specialists and the end user looks at the report keys in the data in Excel and issues his own report in the format that he wants.

 

  Other Articles

 

Discover Even More in 50 Excel spreadsheets

 

    

613-749-4695
[email protected]

Home Page

Tips and Ideas on Excel
Table of Contents on Excel

Tips and Ideas on Macros
Table of Contents on VBA

Quick Links

Excel and Central  Databases