Page 1 of 1

Excel + Vertica without MS SQL server cube

Posted: Thu Oct 11, 2012 10:51 am
by Minister
Hi all,

My customer would like to have an Excel report connecting to Vertica for some reporting purposes.
When playing around I found that indeed the ODBC to Vertica with Excel will work fine but for the smal data sets only, as if there is no CUBE in the middle then Excel needs to pull the whole data set from the source table and later on apply some processing logic (like filtering or aggregation).
Wonder if any of you have some experience with this, for the source tables having over > 10k rows this solution is just not accpetable as pulling the whole data sets kills the reponse time very badly here.
The same Excel connecting to SQL SErver OLAP works extremly fast as the whole processing is done on the OLAP site (as far as I know an MDX language is used for this).

So any idea on how to simulate a SQL server OLAP when connecting from Excel to Vertica via ODBC?

thx

Re: Excel + Vertica without MS SQL server cube

Posted: Fri Oct 12, 2012 2:25 am
by doug_harmon
I don't know of a way to speed up the need to download of all of the records without some type of intermediate caching system (Sharepoint, SSAS). Once the records are downloaded the best way to speed up the Excel analysis for smallish (< 1mm record) data sets is to use PowerPivot.

Re: Excel + Vertica without MS SQL server cube

Posted: Fri Oct 12, 2012 3:23 pm
by jpcavanaugh
I second power pivot over excel for even sets greater than 1m. When you run it, where is the bottleneck. Is it in the transfer of the data or what?