Question

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

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.

Answer 2

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.

友情链接