SSAS ROLAP cubes with Vertica

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

[id10t]

Hi!

[DELETED]

[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.

[nidhikhetrapal]

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

[azharuddin]

Microsoft SQL Server 2014 Analysis Services (SSAS) delivers online analytical processing (OLAP) and data mining functionality for business intelligence applications. Analysis Services supports OLAP by letting us design, create, and manage multidimensional structures that contain data aggregated from other data sources, such as relational databases.