SSAS ROLAP cubes with Vertica
Moderator: NorbertKrupa
-
- Newbie
- Posts: 6
- Joined: Sat Sep 06, 2014 4:59 am
SSAS ROLAP cubes with Vertica
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
Hi!
[DELETED]
[DELETED]
Last edited by id10t on Wed May 06, 2015 4:34 pm, edited 1 time in total.
-
- Beginner
- Posts: 36
- Joined: Fri Feb 17, 2012 6:09 pm
- Contact:
Re: SSAS ROLAP cubes with Vertica
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:
Sadly, what I found when I did testing was that SSAS would generate inefficient SQL such as the following.
This is very inefficient because it takes a long time to drag all of the CustomerIDs across the network into SSAS.
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;
Code: Select all
SELECT DISTINCT CustomerID
FROM CustomerTransaction
WHERE TransactionDate >= StartDate
AND TransactionDate <= EndDate;
-
- Newbie
- Posts: 6
- Joined: Sat Sep 06, 2014 4:59 am
Re: SSAS ROLAP cubes with Vertica
Thanks
Has anyone tested this with Vertica7 and SSAS 2014 ??
Has anyone tested this with Vertica7 and SSAS 2014 ??