Attunity Compose delivers an innovative approach that automates the design, deployment, management and updates of data warehouses on Amazon Redshift. In this tutorial, you'll learn how to move data from a source database to Amazon Redshift using Attunity Replicate, and then, how to transform that data into a star schema on Amazon Redshift using Attunity Compose.

Overview

To keep the tutorial as simple as possible, Microsoft SQL Server Express with a cut-down version of the Northwind sample database has been installed on the Attunity Compose for Amazon Redshift EC2 Instance, together with Attunity Replicate and Attunity Compose. Obviously, such a configuration is not meant to reflect a real-world scenario as databases are never as simple as Northwind and Attunity Replicate is usually not installed on the same machine as the database. Nevertheless, the concepts and procedures outlined in this tutorial can be applied to all environments, regardless of what database you are using or where it is located (on-premises or in the Cloud).

Note: After launching your Attunity Compose for Amazon Redshift AMI EC2 Instance you will need to contact Attunity for the required software licenses. Once contacted, we will provide you with licenses for Attunity Replicate and Attunity Compose and answer any questions you may have.

This tutorial is split into five parts. Parts 1-3 describe the prerequisites and explain how to set up your Attunity Compose for Amazon Redshift EC2 Instance, while parts 4-5 explain how to move the data from the Northwind source database into a star schema on your Amazon Redshift Cluster.

Part 1: Satisfy the Prerequisites

Amazon Redshift Cluster: If you haven't already done so, set up an Amazon Redshift cluster and make sure that following information about your Amazon Redshift Cluster is readily available:

  • Amazon Redshift Cluster Name
  • Amazon Redshift Cluster Port
  • Amazon Redshift User Name and Password
  • Amazon Redshift Database Name

Attunity Replicate performs the following operations on the replicated tables within Amazon Redshift:

  • CRUD (Select, Insert, Update, Delete)
  • Bulk Load
  • Create, Alter, Drop (if required by the task's definition)

If the user is the 'DB Owner', these permissions are in place by default. Otherwise, the user must be granted these permissions to achieve successful replication.

Port: Make sure that port 5439 (the Amazon Redshift Cluster port) is open for inbound connections from your Attunity Compose for Amazon Redshift AMI EC2 instance.

Amazon S3 Bucket: Make sure that the following information about your Amazon S3 Bucket is readily available:

  • Amazon S3 Bucket Name
  • Amazon S3 Bucket Region
  • Amazon S3 Access Key and Secret Key

Ensure that HTTPS Internet access is enabled to access the S3 bucket

The Attunity Replicate user must have the following permission on the Amazon S3 staging bucket:

  • On the Amazon S3 Bucket itself: 'List Bucket Objects'
  • On the Amazon S3 Bucket Folder: 'Read, Write, Delete of Objects'

The following is a sample policy with these permissions:

{

“Statement”: [

{

“Effect”: “Allow”,

“Action”: [

“s3:ListAllMyBuckets”

],

“Resource”: “arn:aws:s3:::*”

},

{

“Effect”: “Allow”,

“Action”: [

“s3:ListBucket”,

“s3:GetBucketLocation”

],

“Resource”: “arn:aws:s3:::demo-virginia”

},

{

“Effect”: “Allow”,

“Action”: [

“s3:PutObject”,

“s3:GetObject”,

“s3:DeleteObject”

],

“Resource”: “arn:aws:s3:::demo-virginia/*:

}

]

}

Part 2: Launch the Attunity Compose for Amazon Redhsift AMI
The Attunity Compose for Amazon Redshift AMI comes pre-installed with everything you need to successfully complete this tutorial: Attunity Replicate, Attunity Compose and Microsoft SQL Server (Express Edition). This part of the tutorial will guide you through the steps required to successfully launch an Attunity Compose for Amazon Redshift AMI EC2 Instance.
  1. Browse to Attunity Compose for Amazon Redshift – BYOL in AWS Marketplace.
  2. Click Continue.
  3. As with any EC2 Instance, a Key Pair is required in order to generate a Windows password. The password will enable you to log in to your Attunity CloudBeam EC2 instance remotely (using Remote Desktop or a similar tool).
  4. If you already have a Key Pair, skip to Step 7.
  5. Scroll down to the bottom of the Key Pair section. Click Key Pair and then follow the instructions to create a Key Pair.

Key Pair Diagram

  1. After your Key Pair is created, you will be redirected to the Launch on EC2 page.
  2. Click Accept Terms & Launch with 1-Click

Accept Terms & Launch with 1-Click

  1. A subscription confirmation message will be displayed. Close the message and wait for your EC2 instance to be ready. This may take a few minutes.

Subscription Confirmation Message

  1. Once the EC2 instance is running, select Manage in AWS Console.

Manage in AWS Console

  1. The AWS Console will open, showing your EC2 instance.

Launch EC2 Instance

  1. Right-click on the instance and select Get Windows Password. If a "Your password is not ready" message is displayed, wait a few minutes and then try again.
Get Windows Password
  1. The Retrieve Default Windows Administrator Password window opens.

Retrieve Default Windows Administrator Password

  1. Browse to your Key Pair file and then click Decrypt Password.
  2. The information required to connect remotely to your Attunity Compose for Amazon Redshift EC2 Instance will be displayed at the bottom of the page. Make a note of the Public DNS (i.e. the EC2 IP address). You will be prompted for this later when connecting to your Amazon Redshift EC2 Instance.

Retrieve Default Windows Administrator Password

Part 3: Set a Password Using the Attunity CloudBeam Configuration Utility
To do this:
  1. Log in to your Attunity Compose for Amazon Redshift EC2 Instance using Remote Desktop or a similar utility. After logging in, on the desktop, you will find a shortcut to the Attunity CloudBeam Configuration utility.

Attunity CloudBeam Login

  1. Right-click the short-cut and select Run as Administrator.

AMI Instance Configuration

  1.  Either:

Manually enter your own password.

OR

Click Generate Strong Password. Attunity CloudBeam requires a password for authentication purposes and to scramble the session key used to encrypt data transferred between the Attunity Replicate Server and the Attunity Compose for Amazon Redshift EC2 Instance.

  1. Click Set Password and then Copy to Clipboard.
Part 4: Use Attunity Replicate to Move the Source Data to Amazon Redshift

Before you can use Attunity Compose to create the data warehouse tables, you first need to move the source data to the "landing area" in your Amazon Redshift data warehouse. This is done using Attunity Replicate, Attunity's high-performance data replication and loading software.

This process consists of the following steps:

Step 1: Register your Attunity Replicate License

  • Double-click the desktop shortcut to open Attunity Replicate. You will be prompted for your user name and password.
  • Enter the credentials for your Attunity Compose for Amazon Redshift EC2 Instance. The Attunity Replicate console opens.
  • Click the Register License link at the top of the console.
  • In the Register License window, click Load and browse to the license file that you received from Attunity.
  • Click Register License to register the license.
  • A message confirming that the license was registered successfully is displayed. Close the Register License window.

Step 2: Open Attunity Replicate and Add a New Task

  • Double-click the desktop shortcut to open Attunity Replicate. You will be prompted for your user name and password.
  • Enter the credentials for your Attunity Compose for Amazon Redshift EC2 Instance.
  • Click the New Task toolbar button
  • In the New Task dialog box, specify a unique Name
  • For this tutorial, make sure that the Apply Changes and Store Changes options are not selected and then click OK. In a real world scenario, you could easily set up the task to capture any changes to the source tables and apply them to the target tables.

Step 3: Define your Data Source

  • Click the Manage Endpoint Connections toolbar button.
  • In the Manage Endpoint Connections dialog box, click New Endpoint Connection.
  • In the Name field, specify a unique name (e.g. SQLServer).
  • Select Source as the endpoint Role.
  • From the Type drop-down list, select Microsoft SQL Server.
  • In the Server Name field, enter localhost.
  • Select the SQL Server authentication option.
  • In the User name field, enter ComposeDemo.
  • In the Password field, enter ComposeDemo (the same as the user name).
  • Click the Browse button to the right of the Database name field and select the Northwind Source database.
  • Click Test Connection to verify the connection settings and then click Save to save your settings.

Step 4: Define your Data Target

  • Click New Endpoint Connection.
  • In the Name field, specify a unique name (e.g. AmazonRedshift).
  • Select Target as the endpoint Role.
  • From the Type drop-down list, select Amazon Redshift.
  • In the Amazon Redshift target section, enter the information required to connect to your Amazon Redshift cluster.
  • In the Attunity CloudBeam AMI section:
    • Select Amazon Redshift Premium - Hourly/BYOL as the CloudBeam AMI type.
    • In the AMI EC2 IP address field, specify the IP address of your for Attunity Compose for Amazon Redshift AMI EC2 Instance.
    • In the Password field, enter the password you created in PART 3: Set a Password Using the Attunity CloudBeam Configuration Utility.
  • In the Amazon S3 staging section, enter the information required to connect to your Amazon S3 bucket.
  • Click Test Connection to verify the connection settings and then click Save to save your settings.
  • Click Close to close the Manage Endpoint Connections dialog box.

Step 5: Add your Source and Target to the Task Definition

  • Drag your source endpoint (Microsoft SQL Server) from the Endpoints panel on the left of the console to the Drop source endpoint here area in the diagram on the right.
  • Drag your target endpoint (Amazon Redshift) from the Endpoints panel on the left of the console to the Drop target endpoint here area in the diagram on the right.

Step 6: Select the Source Tables

  • Click the Table Selection button in the toolbar on the right. In the Select Tables dialog box, click the Search button to list all of the tables.
  • Click the Double Arrow Image button to select all of the tables and then click OK to save your selection and close the Select Tables dialog box.

Step 7: Set the Target Schema

  • Click the Task Settings toolbar button.
  • In the Target Metadata tab's Target table schema field, specify the schema in which you want the target tables to be created. You will need to specify the schema name later when configuring the data source in Attunity Compose. The schema will be created automatically if it does not already exist.

Step 8: Run the Task

  • Click the Run toolbar button to run the task.
  • The console switches to Monitor view. Verify that the task completed successfully (ignore the "Truncation" warning) and then proceed to Stage 6: Use Compose to Create a Data Mart on Amazon Redshift.

Create a Data Mart

Part 5: Use Attunity Compose to Create a Data Mart on Amazon Redshift
In Part 4, you learned how to define an Attunity Replicate task that replicates the data from your source tables to the landing area in Amazon Redshift. In this part of the tutorial, you'll learn how to use Attunity Compose to create a data mart on Amazon Redshift.

This process consists of the following steps:

Step 1: Register your Attunity Compose License
  • Double-click the desktop shortcut to open Attunity Compose. You will be prompted for your user name and password.
  • Enter the credentials for your Attunity Compose for Amazon Redshift EC2 Instance. The Attunity Compose console opens.
  • Click the Register License link in the middle of the console.
  • In the Register License window, click Load and browse to the license file that you received from Attunity.
  • Click Register License to register the license.
  • A message confirming that the license was registered successfully is displayed. Close the Register License window.
Step 2: Add a New Compose Project
  • Open Attunity Compose by double-clicking the Attunity Compose desktop shortcut.
  • Click the New Project toolbar button.
  • Enter a name for your project and then click OK.
Step 3: Configure Connections to your Data Warehouse and Data Source
  • Click the Manage button in the bottom left of the DATABASES panel. The Manage Databases dialog box opens.
  • Click New. The New Data Warehouse dialog box opens.
  • In the Name field, specify a display name for your data warehouse.
  • From the Type drop-down list, select Amazon Redshift.
  • In the Server Name field, specify the IP address of your Amazon Redshift cluster.
  • Leave the default port (5439).
  • In the User Name and Password fields, enter your credentials for logging in to your Amazon Redshift cluster.
  • In the Database Name field, specify the name of the database specified in the Amazon Redshift target settings in the Attunity Replicate task.
  • In the Data Warehouse Schema field, either use the browse button to select an existing schema or type the name of the schema in which you want the data warehouse tables to be created. Non-existing schemas will be created automatically.
  • In the Data Mart Schema field, either use the browse button to select an existing schema or type the name of the schema in which you want the data mart tables to be created. It is recommended to use different schemas for the data warehouse and data mart tables. Non-existing schemas will be created automatically.
  • Click Test Connection to verify that Compose is able to establish a connection to the specified database and then click OK to save your settings.
  • Click New again.
  • The New Data Source dialog box opens.
  • In the Name field, specify a display name for your data source.
  • From the Content Type drop-down list, choose FULL_LOAD.
  • In the Schema name field, enter the schema name that you specified in the Target Metadata tab in the Replicate task settings. For more information, see PART 4: Define and Run a Replicate Task. (Link)
  • In the Error Mart Schema Name field, specify the schema where you want the data mart exception tables to be created. Although not covered by this tutorial, Compose lets you define Data Quality rules that can be used to cleanse or validate data. Data that is rejected by a data quality rule will be copied to tables in the specified schema.
  • Ignore the Associate with Replicate Task option for now. Selecting this option allows you to monitor the Attunity Replicate task from within Compose. For full details, refer to the Attunity Compose User Guide. Select the Source Database Connection check box.
  • From the Type drop-down list, select Microsoft SQL Server.
  • In the Server Name field, specify localhost.
  • Leave the default port (1433).
  • In the User Name field enter ComposeDemo.
  • In the Password fields, enter ComposeDemo (the same as the user name).
  • In the Database Name field, use the Browse button to select Northwind Source.
  • In the Schema field, use the Browse button to select dbo.
  • Click Test Connection to verify that is able to establish a connection to the specified database and then click OK to save your settings.
  • Click OK to save your settings.
Step 4: Create the Model As Compose is model-driven, the first thing you need to do after configuring your database connections is to create a model. There are several ways of doing this in Compose including importing the model from ERwin, but to keep things simple, we're going to get Compose to "discover" the data source and auto-generate the model for us.
  • In the Model panel, perform the following steps to create the model for data warehouse generation:
  • From the drop-down menu in the top right corner of the MODEL panel, select Discover. The Discover dialog box opens.
  • Select the source database (i.e. the database without the "_landing" suffix).
  • The Source Table/View Selection - Name dialog box opens.
  • Make sure that Tables is selected as the Search for option (the default).
  • Click the Search button.
  • Select all the tables by clicking the Double Arrow Button to the right of the Results list. Then click OK.
  • The Generating Model from Name window opens.
  • Wait for the "Model created successfully" message and then click Close.
  • The Source Table/View Selection - Name dialog box closes automatically.
Step 5: Create and Populate the Data Warehouse Tables Now that our model is in place, we can proceed with creating and populating the data warehouse tables.
  • Click the Create button in the bottom right of the DATA WAREHOUSE panel.
  • The Creating Data Warehouse window opens. Wait for the Data Warehouse to be created and then click Close.
  • Click Manage.
  • The Manage ETL Sets window opens.
  • Click Generate.
  • The Generating Instructions for ETL: Name window opens. Wait for the ETL instruction set to be generated and then click Close.
  • Click Run.
  • The Manage ETL Sets window switches to Monitor view and starts to populate the Data Warehouse with data.
Manage ETL Sets
  • Wait for the Data Warehouse to be populated and then close the Manage ETL Sets window.
Step 6: Create the Data Mart and Star Schema In the Data Mart panel, perform the following steps to create a Data Mart with a star schema:
  • Click New.
  • The New Data Mart dialog box opens. Leave the default name.
  • Make sure the Start New Star Schema Wizard check box is selected. Then click OK.
  • The New Star Schema wizard opens. Leave the default name.
  • Make sure that Transactional is selected as the star schema type (the default) and then click Next.
  • In the Facts screen, select Order Details. Then click Next.
  • In the Dimensions screen, you'll notice that all of the dimensions are selected by default. Again, to keep things simple, select Customers, Employees and Products only. Then click Next.
New Star Schema
  • In the Transaction Date screen, select OrderDate. Then click Finish. The wizard closes and the star schema with the selected fact table and dimensions is displayed on the right of the Manage Data Marts window.
  • Click Create Tables.
  • The Creating Data Mart: Name in Target window opens. Wait for the "Data Mart Name has been created successfully" message to appear and then close the window.
  • Click Generate.
  • The Generating Instructions for ETL: Name window opens. Wait for the process to complete and then close the window.
Generating Instructions for ETL
  • Click Run.
  • The Manage Data Marts window switches to Monitor view and populates the Data Mart with data. Leave the Manage Data Marts window open in Monitor view for now (The two buttons at the top right of the window allow you to switch between Designer and Monitor views).
Step 7: View the Data in a Pivot Table
  • In the Manage Data Marts window, click the Pivot toolbar button.
  • The Select Columns for Pivot Table window opens.
  • From the drop-down list at the top of the window, select the Pivot Table columns as follows:
  • From the 1Fct_order details table, select unitprice.
  • From the TDMA_1Dim_customers table, select companyname.
  • From the TDMA_1Dim_products table, select productname.
  • Click OK. A Pivot Table is created with your selected columns.
  • Drag the companyname dimension to the gray empty space on the left of the Pivot table. Then, drag the productname dimension to the gray empty space immediately above the Pivot table. Now we're going to make things a little more interesting by combining a "Heatmap" with a "Sum" calculation to show the sum total of all products sold by each company.
  • Select Heatmap from the drop-down list below the Customize Columns button on the left.
  • Then, select Sum from the drop-down list immediately below that.
  • And finally, select unitprice from the bottommost drop-down list. Your pivot table should now look like this:
  • MyRedShiftDataMart

    What Next?

    Now that you've successfully completed the tutorial, you'll probably want to waste no time applying what you've learned to you own environment. If you're source databases are located on-premises, then you will need to contact our Sales Team for an Attunity Replicate download link. If your data sources are located in the Cloud (e.g. Amazon RDS), you can simply continue using Attunity Replicate on your Attunity Compose for Amazon Redshift EC2 machine.

    Dev Tool:

    Request: products/compose/quick-start-guide-compose-amazon-redshift
    Matched Rewrite Rule: (.?.+?)(?:/([0-9]+))?/?$
    Matched Rewrite Query: pagename=products%2Fcompose%2Fquick-start-guide-compose-amazon-redshift&page=
    Loaded Template: page.php