Analyze Amazon Redshift data in Microsoft SQL Server Analysis Services
Created by Sunil Vora (AWS)
Environment: PoC or pilot | Source: Amazon Redshift | Target: Microsoft SQL Server Analysis Services |
R Type: N/A | Workload: Microsoft | Technologies: Analytics |
AWS services: Amazon Redshift |
Summary
This pattern describes how to connect and analyze Amazon Redshift data in Microsoft SQL Server Analysis Services, by using the Intellisoft OLE DB Provider or CData ADO.NET Provider for database access.
Amazon Redshift is a fully managed, petabyte-scale data warehouse service in the cloud. SQL Server Analysis Services is an online analytical processing (OLAP) tool that you can use to analyze data from data marts and data warehouses such as Amazon Redshift. You can use SQL Server Analysis Services to create OLAP cubes from your data for rapid, advanced data analysis.
Prerequisites and limitations
Assumptions
This pattern describes how to set up SQL Server Analysis Services and Intellisoft OLE DB Provider or CData ADO.NET Provider for Amazon Redshift on an Amazon Elastic Compute Cloud (Amazon EC2) instance. Alternatively, you can install both on a host in your corporate data center.
Prerequisites
An active AWS account
An Amazon Redshift cluster with credentials
Architecture
Source technology stack
An Amazon Redshift cluster
Target technology stack
Microsoft SQL Server Analysis Services
Source and target architecture
Tools
Epics
| Task | Description | Skills required |
|---|---|---|
| Analyze the tables and data to be imported. | Identify the Amazon Redshift tables to be imported and their sizes. | DBA |
| Task | Description | Skills required |
|---|---|---|
| Set up an EC2 instance. | In your AWS account, create an EC2 instance in a private or public subnet. | Systems administrator |
| Install tools for database access. | Download and install the Intellisoft OLE DB Provider for Amazon Redshift | Systems administrator |
| Install Visual Studio. | Download and install Visual Studio 2019 (Community Edition) | Systems administrator |
| Install extensions. | Install the Microsoft Analysis Services Projects extension in Visual Studio. | Systems administrator |
| Create a project. | Create a new tabular model project in Visual Studio to store your Amazon Redshift data. In Visual Studio, choose the Analysis Services Tabular Project option when creating your project. | DBA |
| Task | Description | Skills required |
|---|---|---|
| Create an Amazon Redshift data source. | Create an Amazon Redshift data source by using the Intellisoft OLE DB Provider for Amazon Redshift (or CData ADO.NET Provider for Amazon Redshift) and your Amazon Redshift credentials. | Amazon Redshift, DBA |
| Import tables. | Select and import tables and views from Amazon Redshift into your SQL Server Analysis Services project. | Amazon Redshift, DBA |
| Task | Description | Skills required |
|---|---|---|
| Delete the EC2 instance. | Delete the EC2 instance you launched previously. | Systems administrator |
Related resources
Amazon Redshift (AWS documentation)
Install SQL Server Analysis Services
(Microsoft documentation) Tabular Model Designer
(Microsoft documentation) Overview of OLAP cubes for advanced analytics
(Microsoft documentation)
