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

azharuddin
Newbie
Newbie
Posts: 1
Joined: Thu Jun 07, 2018 10:37 am

Re: SSAS ROLAP cubes with Vertica

Post by azharuddin » Thu Jun 07, 2018 10:39 am

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.

Post Reply

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