SSAS ROLAP cubes with Vertica

Moderator: NorbertKrupa

Post Reply
nidhikhetrapal
Newbie
Newbie
Posts: 6
Joined: Sat Sep 06, 2014 4:59 am

SSAS ROLAP cubes with Vertica

Post by nidhikhetrapal » Sat Sep 06, 2014 7:34 pm

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 ??

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: SSAS ROLAP cubes with Vertica

Post by id10t » Sun Sep 07, 2014 7:28 am

Hi!

[DELETED]
Last edited by id10t on Wed May 06, 2015 4:34 pm, edited 1 time in total.

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

Re: SSAS ROLAP cubes with Vertica

Post by doug_harmon » Mon Sep 08, 2014 3:08 pm

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.

nidhikhetrapal
Newbie
Newbie
Posts: 6
Joined: Sat Sep 06, 2014 4:59 am

Re: SSAS ROLAP cubes with Vertica

Post by nidhikhetrapal » Tue Sep 09, 2014 9:06 pm

Thanks
Has anyone tested this with Vertica7 and SSAS 2014 ??

Post Reply

Return to “Microsoft SQL Server Analysis Services (SSAS)”