English 中文(简体)
Is this way of using Excel 2007 Pivot table for BI scalable?
原标题:

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

问题回答

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.

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.





相关问题
Export tables from SQL Server to be imported to Oracle 10g

I m trying to export some tables from SQL Server 2005 and then create those tables and populate them in Oracle. I have about 10 tables, varying from 4 columns up to 25. I m not using any constraints/...

SQL server: Can NT accounts be mapped to SQL server accounts

In our database we have an SQL server account that has the correct roles to access some of the databases. We are now switching to windows authentication and I was wondering if we can create a NT user ...

SQL Server 2000, ADO 2.8, VB6

How to determine if a Transaction is active i.e. before issuing Begin Transaction I want to ensure that no previous transaction are open.. the platform is VB6, MS-SQL Server 2000 and ADO 2.8

热门标签