Is this way of using Excel 2007 Pivot table for BI scalable ?
- by Sim
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
