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