Connecting AWS Redshift to SSAS Cubes

Though it is a rare combination but there are cases where you would like to connect an MPP database like Redshift to an OLAP solution for analytics solutions. But, there might be scenarios where you would want a cube to power your reports without the BI server hitting your Redshift cluster. In this post I will show you how to connect to a Redshift instance from a SQL Server Analysis Services 2014.

Environment Setup

In my setup, the Redshift instance is in a VPC while the SSAS server is hosted on an EC2 machine in the same VPC. Access to the Redshift instance and SSAS host machine are controlled by two different security groups. Security group attached to the Redshift cluster has an ingress rule setup for the security group attached to the EC2 machine. I have tried to keep the environment as close to real life setups as possible. In most cases, your environment will be similar to this setup.

Steps to Connect Redshift to SSAS 2014

Step 1:
Download the PGOLEDB driver for your windows machine. You can get the setup file from the website:

Step 2: 
Install the driver by following the installation wizard.

Step 3: Configure your Data Source in SSAS
Note: If you have SSL enabled on your cluster, come back to this step after you have installed the certificate (steps given towards the end of the post).

Create a new project in Business Intelligence Dev Studio (or SQL Server Data Tools if you are using SSAS 2014). In the Object Explorer, create a new Data Source by following these steps:
·     
      Select the Redshift Native OLE DB Provider.
·     
   Fill in the details for the server name, user name and password and the database you want to connect to.


·  Click on the Data Links button to get into the advanced properties of the connection.

·   In the Connection tab, enter the port on which your Redshift cluster is allowing TCP connections



   Move to the Advanced tab and make sure that all the properties are what you want (callout for the SSL mode, depending on your cluster settings). If you have SSL enabled on your cluster, follow the steps in the next section to install the certificate before doing this.


Once done, test the connection. You should see the "Test Successful" popup message.

SSL Connections: Installing the SSL certificate
If your cluster only allows connections over SSL, you will need to install the SSL certificate to fix the issue "Partial Certificate Chain". Download the certificate from this location,

https://s3.amazonaws.com/redshift-downloads/redshift-ssl-ca-cert.pem

Once downloaded, rename the file as a .cert file and install it.

Once the setup is done and you are able to connect SSAS to Redshift, you should be able to see your Redshift data source in the SSAS connections. Now, you can use this data source to explore tables, build joins and ultimately build your cube. 

 That's all. This is a very straightforward setup but not very well documented in the docs. This was my attempt to fill that gap. Do share your feedback in the comments.


Comments

  1. KashAugust 23, 2019 at 3:47 PM

    Really thanks. Would please help me to understand how end-user will access cube like excel and outside aws

    ReplyDelete
    Replies
    1. Kautuk PandeyAugust 28, 2019 at 11:21 PM

      It should be the same as for any other SSAS based cube. Only extra thing you need to do here is to open up your RDS/ EC2 machine's CIDR/ IP rules to your IP. You will have to add these security ingress/ outgress rules to a security group and attach it to the EC2 machine/ RDS where your SSAS is hosted.

      Delete
  2. Lafay Tech PlazaMay 4, 2021 at 7:58 AM

    Amazon Web Services is proud to announce the launch of its latest solution, theAws Data Lakewhich is designed for enterprises looking to create a central repository for all of their big data needs. The Aws Data Lake is useful for enterprises looking to build out cloud-based data storage. It's especially well-suited for companies that want to build a data lake on top of cloud computing, as opposed to in-house data centers.

    ReplyDelete

Post a Comment

Popular posts from this blog

Uber Data Model

Image
In this post I will try to come up with a data model which can serve the requirements of ride sharing companies like Uber, Lyft, Ola etc. We will approach the problem as an interview and see how we can come up with a feasible data model by answering important questions. Important Entities The first step towards building a data model is to identify important actors/ entities involved in the process. In our case, if we think about our interaction with taxi apps, we can identify important entities involved. The user (i.e. Rider) is one such entity, so is the Driver/ Partner . Once we open the app, we try to book a trip by finding a suitable taxi/ cab from a particular location to another . After the trip gets finished, the app collects the payment and we are done . Ideally, the flow continues to reviews/ ratings, helpcenter in case of issues etc. but for this post we will only consider scenarios till the ride gets finished. So, to summarize, we have the following key entities...
Read more

Data Engineer Interview Questions: SQL

Image
In this post, I will try to share some actual questions asked by top companies for Data Engineer positions. A lot of these companies will cover data modelling as one of the rounds and will use the data model for the next round based on SQL queries. Q1: Find the number of drivers available for rides in any area at any given point of time. Q2: Do you consider Driver and Rider as separate entities? Why or why not? Q3: Give me all passenger names who used the app for only airport rides. Q4: How will you decide where to apply surge pricing? Q5: How will you calculate wait times for rides? Q6: A driver can ride multiple cars, how will you find out who is driving which car at any moment? Q7: Find out Rank without using any function. Q8: How will you delete duplicates from a table? Q9: How will you find percentile? Q10:  You have 3 tables, user_dim (user_id, account_id), account_dim (account_id, paying_customer), and dload_facts (date, user_id, and downloads), fin...
Read more

Cracking Data Engineering Interviews

Of late, a lot of people have asked me for tips on how to crack Data Engineering interviews at FAANG (Facebook, Amazon, Apple, Netflix, Google) or similar companies. I have not worked at all of these companies so I can't share tips which will necessarily apply for all of them but I will share tips which can be generalized for most of the big companies. First of all, the field of Data Engineering has expanded a lot in the last few years and has become one of the core functions of any big technology company. The obvious reason for this expansion is the amount of data being generated by devices and data-centric economy of the internet age. Each company is focussed on making the best use of data owned by them by making data driven decisions. If you compare this to the Data Engineering roles which used to exist a decade back, you will see a huge change. In the past, Data Engineering was invariably focussed on Databases and SQL. Even now, these two form some part of most Data Engin...
Read more

Hive Challenges: Bucketing, Bloom Filters and More

In the second post of this series, we will learn about few more aspects of table design in Hive. Now that you know about partitioning challenges , you will be able to appreciate these features which will help you to further tune your Hive tables. Bucketing In addition to Partitioning the tables, you can enable another layer of bucketing of data based on some attribute value by using the Clustering method. Clustering can be used with partitioned or non-partitioned hive tables. One particular use case where Clustering becomes useful when your partitions might have unequal number of records (e.g. users logging in per country, US partition might be a lot bigger than New Zealand). In such cases, you can define the number of buckets and the clustered by field (like user Id), so that all the buckets have equal records. In partitioning each partition gets a directory while in Clustering, each bucket gets a file. Records with the same bucketed column will always be stored in the same bucke...
Read more

UBER Data Architecture

Image
In this post, I will try to cover the data architecture Uber has built to support their big data applications. This should be applicable for other ride hailing apps as well. There are multiple modules at play here so I will try to give a brief overview along with a detailed discussion on the data architecture.  An aeroplane view of the problem tells us that effectively we are trying to solve a demand vs. supply problem. All the Drivers active at any point of time constitute the supply while all the cab requesters (Riders) form the demand. What Uber tries to do is to have the best matching between demand and supply "at any point at any moment". I have highlighted the location and temporal conditions as they are critical for the success of what Uber tries to do. Let's get started to see how Uber comes around these challenges by creating a scalable data architecture. Every Driver active on Uber keeps sending his location data to the server (e.g. every 5 seconds). T...
Read more