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

Re: SSAS ROLAP cubes with Vertica

Posted: Thu Jun 07, 2018 10:39 am
by 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.