2/12/2014

Building a Data Warehouse from Excel Reports

On the latest project, we were supplied 10 Excel spreadsheets.

The first report, has 20 tabs.  The first tab pulls data from all the other tabs.  And those tabs have data connections to other tabs, connections to one or more Access databases or back to the SQL Server source.

So tracking down the data is quite fun.

To build a Data Warehouse with Dim tables and Fact tables.  And the ETL to by pass the Excel doc and grab data directly from SQL.  Build the ETL to stage.  And from stage to the Data Warehouse tables.

And then push the data to SQL Server Analysis Services (SSAS), build Measures and Calculated measures with MDX.  And then pull the data from Excel.

And tonight our data matched across the board.  We got that working.  For the first report.  Of 10.

On to the next reports.  Yippie ~!

No comments:

Post a Comment