Access Keys:
Skip to content (Access Key - 0)

Sample Instaview template for use with Amazon Redshift that can be easily added to your installation. It demonstrates how to access data stored in Amazon's Redshift Data Warehouse in the Cloud, immediately visualize the data and save offline for access when not connected to Redshift. Included with this template is a data set that can be loaded into Redshift for demo purposes.

Prerequisites

In order to follow along with this how-to guide you will need a desktop installation of Pentaho Data Integration with Instaview.
Download Instaview

Amazon Redshift

You will need an amazon Redshift account to be able to run this template. Connection details can be found by logging into your AWS account here: http://aws.amazon.com

Data Files

How to load Foodmart data on Redshift:

  1. Download Foodmart data files here:
    1. *Download data: Click Foodmart-Redshift-Load.zip to download the data and save it locally.
  2. Log in to Redshift on Amazon Web Services.
    Assuming you have a Redshift cluster already created, (see Amazon Redshift documentation for instructions on creating a cluster) and have created a Bucket on Amazon S3, (see Amazon Web Services documentation for instructions on creating an S3 bucket), navigate to your bucket on S3.
  3. Click on the Actions menu and choose upload. Select all files ending in .txt in the foodmart-Redshift directory and upload them to your bucket on S3. These are the data files for the tables in the Foodmart DW.
  4. In PDI, open the "Redshift SQL createscript" transformation.
  5. In the Execute SQL script step, configure your connection information with connection info for your Redshift cluster. For the Hostname field, enter the value that is found on the info page for the Redshift cluster in the Endpoint field under Cluster Database Properties. Save and run the transformation. This will create the tables on the Redshift cluster.
  6. In PDI, open the "Redshift SQL copyscript".
  7. In the Execute SQL script configure connection info to the Redshift cluster. In the SQL script, replace all values of <S3_Bucket_Name>, <aws_access_key_id>, and <aws_secret_access_key_id> with the correct info for your AWS account.
  8. Save and run transformation. This will copy the data from the S3 bucket to the Redshift cluster.

Add the Redshift sample template to your copy of Instaview

Now that there is data in the right place, all we need to do is drop the Template into the correct folder

  1. Get the Template: Click Redshift.ktr to download the Template and save it to your instaview template Samples folder. Navigate to the install directory where Pentaho Data Integration is installed. From there, navigate to:
    plugins/spoon/agile-bi/platform/pentaho-solutions/system/instaview/templates/Samples
  2. Get the Icon: Click Redshift.png to download the icon and save in the same folder as the previous step
  3. Switch to Instaview: Start up Pentaho Data Integration (you do not need to restart) and select the "Instaview" perspective.
  4. Try the sample: From the Instaview Welcome Screen:
    1. Click: "Create New"
    2. Click: "Samples"
    3. Select: "Redshift"
    4. A dialog box will appear. Select your database connection for Redshift. If not yet created, the next step will guide through the creation of the connection.
      Click to enlarge image.
    5. Click: "New Database Connection"
  5. Another dialog box will appear, allowing you to establish your connection to Redshift. Use the connection details you received from Amazon. Enter those connection details for Redshift and confirm the connection by clicking "Test".
    Click to enlarge image.
    1. Click: "OK"
    2. The SQL query found in the dialog box was established for use with the Foodmart Dataset found in a previous step.
    3. Click "OK" to run

Summary

During this guide you learned how to install and use the Redshift template. Redshift is a cloud-based data warehouse, so establishing connection is dependent on your setting up a Redshift Cluster. Once the connection is made, the template can be used with the Foodmart data, or you can use your own data from Redshift by changing the SQL Query in the input dialog.

Other guides in this series cover the sorting and grouping of MongoDB data, create reports, and combine data from MongoDB with data from other sources, accessing and enhancing Google Analytics data, and using the Twitter Template for analyzing twitter feeds.


This documentation is maintained by the Pentaho community, and members are encouraged to create new pages in the appropriate spaces, or edit existing pages that need to be corrected or updated.

Please do not leave comments on Wiki pages asking for help. They will be deleted. Use the forums instead.

Adaptavist Theme Builder (4.2.0) Powered by Atlassian Confluence 3.3.3, the Enterprise Wiki