Page 1 of 1

SSAS ROLAP cubes with Vertica

Posted: Sat Sep 06, 2014 7:34 pm
by nidhikhetrapal
Does anyone has experience building ROLAP cubes with Vertica 7 ?? We are looking into vertica but have lot of ROLAP cubes , will we be able to use ROLAP cubes without any changes ??

Re: SSAS ROLAP cubes with Vertica

Posted: Sun Sep 07, 2014 7:28 am
by id10t
Hi!

[DELETED]

Re: SSAS ROLAP cubes with Vertica

Posted: Mon Sep 08, 2014 3:08 pm
by doug_harmon
When I looked at this a year ago I found that SSAS 2008 R2 doesn't play well with Vertica v6 in ROLAP mode.

Here's the use case you will want to test, assuming you're using a more recent version of SSAS and Vertica.

Create a Vertica table called CustomerTransaction with three columns (CustomerID, TransactionID, TransactionDate). Populate it with data.
Create a SSAS cube in ROLAP mode. Create a metric in SSAS that counts the number of Unique Customers that had a TransactionDate between a Start Date and an End Date. Look at the SQL that SSAS generates in the vertica system table v_monitor.query_requests. Ideally, the BI tool would generate a SQL statement such as:

Code: Select all

SELECT COUNT(DISTINCT CustomerID) 
FROM CustomerTransaction 
WHERE TransactionDate >= StartDate
   AND TransactionDate <= EndDate;
Sadly, what I found when I did testing was that SSAS would generate inefficient SQL such as the following.

Code: Select all

SELECT DISTINCT CustomerID
FROM CustomerTransaction 
WHERE TransactionDate >= StartDate
   AND TransactionDate <= EndDate;
This is very inefficient because it takes a long time to drag all of the CustomerIDs across the network into SSAS.

Re: SSAS ROLAP cubes with Vertica

Posted: Tue Sep 09, 2014 9:06 pm
by nidhikhetrapal
Thanks
Has anyone tested this with Vertica7 and SSAS 2014 ??