2/26/2014

Follow the Data Trail Through Excel and Access

Sometimes it's helpful to be curious.  Curious as in being a detective.  Being a detective as in following the intricate pattern a report uses to gather data.

I'm talking about Excel reports.  An Excel report which gathers data from multiple tabs.  Each tab gathers data from connections.  Those connections could be an Access Database.  Which calls another Access Database.  Which has a 15 step process to build temporary tables.  Which in fact derives its data from the Operational Data Warehouse.

Of course, there's hard coded values in the Excel spreadsheet.  Which get updated by some user somewhere based on some unknown frequency.  And how or why these values get updated is anybody's guess.

So you have a spreadsheet with 20 tabs, pulling data from everywhere imaginable, and your task is to recreate the business logic to grab data directly from the ODS in order to by-pass the Excel / Access.

Sounds simple.  Well, that's a project I've been working.  And tracking down the business rules is so much fun.  You really get to know VLookup quite well.

Suffice to say, it's a challenge indeed.  Except at the end of the day, when the business logic is converted, the data is stored in an Enterprise Data Warehouse, which gets refreshed nightly, the client can find a use for the developer's time rather than taking 3 hours to refresh the reports daily and users can access the data via Excel Pivot Tables connected to an SSAS Cube, that's what we call:

Win-Win~!

No comments:

Post a Comment