Excel + Vertica without MS SQL server cube

Moderator: NorbertKrupa

Post Reply
Minister
Newbie
Newbie
Posts: 2
Joined: Thu Oct 04, 2012 3:55 pm

Excel + Vertica without MS SQL server cube

Post by Minister » Thu Oct 11, 2012 10:51 am

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

doug_harmon
Beginner
Beginner
Posts: 36
Joined: Fri Feb 17, 2012 6:09 pm
Contact:

Re: Excel + Vertica without MS SQL server cube

Post by doug_harmon » Fri Oct 12, 2012 2:25 am

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.

jpcavanaugh
Intermediate
Intermediate
Posts: 149
Joined: Mon Apr 30, 2012 10:04 pm
Location: New York
Contact:

Re: Excel + Vertica without MS SQL server cube

Post by jpcavanaugh » Fri Oct 12, 2012 3:23 pm

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?

Post Reply

Return to “Vertica Analytics”