Is this way of using Excel 2007 Pivot table for BI scalable ?

Question

Hi all,

Background:

• We need to consolidate sales data across the country to do analysis

• Our Internet connection/IT expertise/IT investment is not quite strong, therefore full BI solution is out of question

• I tried several SaaS BI solution (GoodData, ZohoReports) and while they're good, they seem not to fully support what we need

• We're looking at 'bout 2 millions record for every 2 months

My current approach

• Our (10) sites currently gathers data from all their branches and consolidate them into 1 Excel file with Pivot table and embed source data

• In HQ, I will request 10 sites to send back those Excel files periodically

• We will import those Excel to our MSSQL server

• There will be a master Excel file, that will also have the same pivot table (as those came from site Excel file), and datasource is the MSSQL server

More details

• For testing, I currently use MSSQL 2008 Express on my laptop

• So far, I imported our transactions for the past 2 months and there are 2 millions+ row in 1 table in MSSQL (we just use 1 table, corresponding to our common pivot table structure). DB size is ~ 600 MB

• In the master Excel file, if not including the source data, it's just < 10MB. Including the source data will increase the size to 60 MB (so I supposed Office 2007 automatically zip the data ?)

• I try using the Pivot (drag-and-drop fields) and the performance so far is OK (my laptop specs: C2D T7200, 3GB RAM, Windows XP)

So my question is :

• If we're looking at full year transaction (roughly 15 millions rows in MSSQL 2008 Express, 3.6 GB in size), is there any issue with that 15 million rows in 1 table in SQL Express ?

• Is there any performance issue with the pivot table at that time ? Can it still embed the source data ? (I google-ed but didn't find the maximum size of source data Excel 2007 can embed)

• Any other suggestions on how we can better do this ? Given that we can't afford the full BI solution, any light-weight/budget/SaaS BI that you can recommend ?

Thanks

Answer 1

In theory, it should work. I am not seen that you exceed the Excel/SQL-Server limits. But it will work lots quicker if you can afford an Annalisys Services licence (SQL Server Standard, minimum) and do it by OLAP Cubes.

Excell will probably move those million of rows, but the performance is not going to be very good. It's all about your requirements but the move of this pivot table will not be more fluent. In OLAP Cubes, if they are well designed, the pivotting of the table will be instant.

Answer 2

I would not consider your solution to be scalable (or advisable). While you might be able to get it work in the short run, trying to process that many rows in Excel is definitely not a best practice -- particularly when you consider maintenance and operational issues.

Building a simple cube in Analysis Services is normally pretty straightforward. Once built, your sales team would then be able to connect to a central server using Excel, and issue their queries against it using Pivot Tables or Pivot Charts, with drag-and-drop, etc. If you only have a dozen or so users, you could license SQL Server on a CAL basis, and it should be pretty cheap.

In case it helps, I walk through the process of building a simple cube in my book: Ultra-Fast ASP.NET. There are also a number of good examples online.