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.
- 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.
- 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.
- 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:
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:
- From the 1Fct_order details table, select unitprice.
- From the TDMA_1Dim_customers table, select companyname.
- From the TDMA_1Dim_products table, select productname.