OLAP Server for Excel users

Question

My company currently uses CSV files along with Excel (Pivot Tables) as a reporting instrument. We export CSV from our invoicing system (which is based on MSSQL, so we have MSSQL Server running, just a note).

Time has come for some upgrade because annual base is already 1 million rows. Excel can only import 1 million rows and slows down on some operations at that amount.

I want to have an OLAP Server so that users (approx 10 people) can connect directly from Excel to that server and create Pivot Tables without hassle.

Preferably free/opensource solution.

Answer 1

I have used Palo OLAP with Palo for Excel. Wasn't very practical or fast tho.

I have tried Pentaho also. Until version 4.8, not practical at all - too many configs, a simple change in the underlying data structure (or DBMS config) required lots of changes on the software config. Pentaho 5 simplified this very much, but I still feel like having too much work for little gain. Plus it is a bit of a bloatware.

If you are abandoning Excel for a Pentaho, it's worth to look into ART - A Reporting Tool. It's a minimalist reporting package, that with some config can talk to Pentaho's Mondrian server, and provide OLAP. I didn't get any further than a 3 minutes test drive of this functionality, so can't really recommend it.

Currently, when Excel won't cut it, I just use professional data science tools for everything. SPSS, R and Python (over SQL Server) are my favorites.

Answer 2

Try Power Pivot (previously PowerPivot). Power Pivot is an Excel add-in for Excel 2010 and 2013 that allows you to efficiently analyze and report on millions of rows of data.

It's not technically OLAP, but I think it meets your primary business goals. When you connect Power Pivot to a data source, such as Microsoft SQL Server, you do it in much the same way as you "Get External Data" in Excel itself, but when the data is imported into Power Pivot it is pulled into a sophisticated storage engine, called Vertipaq, that is optimized to handle millions of rows. Veripaq was originally written as a storage engine for SQL Server; I think that gives us a reason to look at Power Pivot as something that is beyond the the simple pivot chart upgrade that is can seem to be at first glance.

For more information on Power Pivot, check out the PowerPivotPro blog, along with all the normal Microsoft resources.

Answer 3

I don't know any product that can do that, unfortunately.

What I can suggest is to have PDI (Pentaho Data Integration) read the CSV (or even from SQL if tables are designed in a way that you can use for analysis too) once in a while (or when instructed, or when there is a change, if you are willing to do a bit of scripting).

The pivot tables would be available via Pentaho's web interface.

So, it does the job, but not from within Excel, unfortunately.

Answer 4

icCube is an in-memory OLAP server that can be accessed via Excel pivot table; that way you won't need anymore the CSV files. A free community version is available and should be well suited for your own need.