Drawbacks of using excel as data source for data warehouse

building semantic layer
1

Hi

Why its discouraged to use excel as a data source in datawarehouse?

Is it good if we make universe on Excel through ODBC?
What are the drawbacks we see?
and Is it any better If we perform ETL on excel and build a data mart for reporting purpose.

Thanks in advance for help!

shwetasabharwal (BOB member since 2007-12-11)

2

Welcome to B:bob:B!

Would you use a text file as a source for your data? That’s essentially what Excel is.

Dave Rathbun :us: (BOB member since 2002-06-06)

3

Hi Dave,

Can you elaborate that?

If thats true, why some companies are still preferring to use Excel for universe building.
What are the list of limitations when we build Universe on Excel as data source

What are the list of limitations when we use as Excel as a source from which ETL is sourced and data mart is populated?

shwetasabharwal (BOB member since 2007-12-11)

4

What else can I say? It’s just a bad idea. Is anyone going to seriously suggest that I store my datawarehouse data in a text file rather than a relational database?

Dave Rathbun :us: (BOB member since 2002-06-06)

5

I dont think there are any limitations in this. Sometimes you can not avoid loading some data from Excel into datamart/DWH. Once a universe is based on a relational DB then it is up to you how (how fast, how efficiently, how often, using which tools) DWH/datamart is populated.

just a couple of them:

  • excel spreadsheet can contain 65k rows. Need more data? You need more spreadsheets → a nightmare to manage this
  • indexes, views, referential integrity, management of access rights, scalability, accessibility… basically anything, any concept that you can think of regarding a relational database is missing in Excel.

Marek Chladny :slovakia: (BOB member since 2003-11-27)