Business Model Canvas - Building Blocks - Summary

The 9 business model Building Blocks form the basis for a handy tool, which we call the Business Model Canvas.

This tool represents the painters canvas - preformatted with the 9 blocks - which allow you to paint pictures of new or existing business models. The Business model canvas works best when printed out on a large surface so groups of people can jointly start sketching and discussing business model elements with Post-it notes or board markers.

It is a hands-on tool that fosters:

• understanding, 
• discussion, 
• creativity and 
• analysis.

Below is a slightly different business model used by lean startup guys:

Additional References:

• https://www.quora.com/What-should-everyone-know-about-the-Business-Model-Canvas
• http://diytoolkit.org/tools/business-model-canvas/

Business Model Canvas - Building Blocks - 09 - Cost Structure

Cost Structure = all costs incurred to operate a business model

Some business models, though, are more cost-driven than others. So-called "no frills" airlines, have built business models entirely around low Cost Structures.

Questions to ask ourselves while thinking about Cost Structure:

• Which are the most important costs inherent in our business model?
• Which key resources are the most expensive?
• Which Key Activities are the most expensive?

It is useful to distinguish between two broad classes of business model Cost Structures:

• cost driven
• value driven

Many business models fall in between these two extremes:

1. Cost Driven:
• focus on minimizing costs wherever possible.
• This approach aims at creating and maintaining the leanest possible cost structure.
• No frills airlines, such as SpiceJet and Indigo typify cost driven business models.
2. Value Driven:
• Some companies are less concerned with the cost implications of a particular business model design and instead focus on value creation.
  
• Premium value propositions and a high degree of personalized service usually characterize value driven business models.
  
• Luxury hotels, with their lavish facilities and exclusive services, fall into this category.

Cost Structures can have the following characteristics:

• Fixed Costs:
• Costs that remain the same despite the volume of goods or services produced.
• Examples include salaries, rents, and physical manufacturing faciliites.
• Some businesses such as, manufacturing companies are characterized by a high proportion of fixed costs.
• Variable Costs:
• Costs that vary proportionally with the volume of goods or services produced.
• Some businesses such as - music festivals are characterized by a high proportion of variable costs.
• Economies of Scale:
• Cost advantages that a business enjoys as its output expands.
• Larger companies, for instance, benefit from lower bulk purchase rates.
• This and other factors cause average cost per unit to fall as output rises.
• Economies of Scope:
• Cost advantages that a business enjoys due to a larger scope of operations.
• In a large enterprise, for example, the same marketing activities or Distribution Channels may support multiple products.

Business Model Canvas - Building Blocks - 08 - Key Partnerships

Key Partnerships = network of suppliers and partners that make the business model work.

Companies create alliances to:

• optimize their business models
• reduce risk
• acquire resources

We can distinguish between four different types of partnerships:

1. Strategic alliances between non-competitors 
2. Competition: strategic partnerships between competitors 
3. Joint ventures to develop new businesses
4. Buyer-supplier relationships to assure reliable supplies

Questions to ask ourselves while thinking about Key Relationships:

• Who are our Key Partners?
• Who are our Key Suppliers?
• Which Key Resources are we acquiring from partners?
• Which Key Activities do partners perform?

It can be useful to distinguish between three motivations for creating partnerships:

1. Optimization and Economies of Scale:
• The most basic form of partnership or buyer-seller relationship is designed to optimize the allocation of resources and activities.
• It is illogical for a company to own all resources or perform every activity by itself.
• Optimization and economy of scale partnerships are usually formed to reduce costs, and often involve outsourcing or sharing infrastructure.
2. Reduction of Risk and Uncertainity:
• Partnerships can help reduce risk in a competitive environment characterized by uncertainity.
• It is not unusual for competitors to form a strategic alliance in one area while competing in another.
• Blu-ray for example, is an optical disk format jointly developed by a group of the world's leading consumer electronics, personal computer and media manufacturers. The group cooperated to bring Blu-ray technology to market, yet individual members compete in selling their own Blu-ray products.
3. Acquisition of particular resources and activities:
• Few companies own all the resources or perform all the activities described by their business models. Rather, they extend their own capabilities by relying on other firms to furnish particular resources or perform certain activities.
  
• Such partnerships can be motivated by needs to acquire knowledge, licenses or access to customers.
  
• A mobile phone manufacturer, for example, may license an operating system for its handsets rather than developing one in-house.
  
• An insurer may choose to rely on independent brokers to sell its policies rather than develop its own sales force.

Business Model Canvas - Building Blocks - 07 - Key Activities

Key Activities = Most important things a company must do to make its business model work

Company	Business Model	Key Activity
Microsoft	Software Maker	Software Development
Dell	PC Manufacturer	Supply Chain Management
McKinsey	Consultancy	Problem Solving

 Questions to ask ourselves while thinking about Key Activities:

• Our Value Propositions require what type of Key Activities?
• Our Distribution Channels require what type of Key Activities?
• Our Customer Relationships require what type of Key Activities?
• Our Revenue Streams require what type of Key Activities?

These are some ways to categorize Key Activities:

• Production:
• Example: Activities like: Designing, Making and Delivering a Product in substantial quantities or of superior quality.
• Production activity dominates the business model of manufacturing firms.
• Problem Solving:
  
• Activities include - coming up with new solutions to individual customer problems.
• Example: operations of consultancies, hospitals and other service organizations are typically dominated by problem solving activities.
• Their business models call for activities like: knowledge management and continuous training.
• Platform / Network:
  
• Business models designed with a platform as a key resource are dominated by platform or network related key activities.
• Networks, matchmaking platforms, software and even brands can function as a platform.
  
• eBay's business model requires that the company continually develop and maintain its platform: the website at eBay.com.
• Visa's business model requires activities related to its Visa credit card transaction platform for merchants, customers and banks.
• Microsoft's business model requires managing the interface between other vendor's software and its Windows operating system platform.
  
• Key activities in this category relate to platform management, service provisioning and platform promotion.

Business Model Canvas - Building Blocks - 06 - Key Resources

Key Resources = most important assets required to make a business model work

The Key Resources of a Business allow an enterprise to:

• create and offer a Value Proposition
• reach markets
• maintain relationships with Customer Segments 
• earn revenues

Depending on the type of business model we need different kind of Key Resources. For example:

• Microchip manufacturer requires: capital intensive production facilities
• Microchip designer focusses more on: human resources.

Key Resources can be:

• Owned or,
• Leased by the company or,
• Acquired from Key Partners

Questions to ask ourselves while thinking about Key Resources:

• Our Value Propositions require what type of Key Resources?
• Our Distribution Channels require what type of Key Resources?
• Our Customer Relationships require what type of Key Resources?
• Our Revenue Streams require what type of Key Resources?

These are some ways to categorize Key Resources:

• Physical:
• Key resources are physical like: manufacturing facilities, buildings, vehicles, machines, systems, point-of-sales systems and distribution networks.
• Retailers like Walmart and Amazon.com rely heavily on physical resources, which are often capital intensive.
• Walmart has an enormous global network of stores and related logistics infrastructure.
• Amazon.com has an extensive IT, warehouse and logistics infrastructure.
• Intellectual:
• Intellectual Resources are like: Brands, Proprietary Knowledge, Patents and Copyrights, Partnerships, and Customer Databases
• These are increasingly important components of a strong business model.
• Intellectual resources are difficult to develop but when successfully created may offer substantial value.
  
• Consumer goods companies such as Nike and Sony rely heavily on Brand as a Key Resource.
• Microsoft and SAP depend on software and related intellectual property developed over many years.
• Qualcomm - a designer and supplier of chipsets for broadband mobile devices, built its business model around patented microchip designs that earn the company substantial licensing fees.
  
• Human:
  
• Every enterprise requires human resources, but people are particularly prominent in certain business models.
  
• Example: Knowledge intensive and creative industries.
• Pharmaceutical company like Novartis relies heavily on Human Resources. Its business model is predicated on an army of experienced scientists and a large and skilled sales force.
  
• Financial:
• Some business models call for financial resources and/or financial guarantees, such as cash, lines of credit, or a stock option pool for hiring key employees.
  
• Ericsson, the telecom manufacturer provides an example of financial resource leverage within a business model.
  
• Ericsson may opt to borrow funds from banks and capital markets, then use a portion of the proceeds to provide vendor financing to equipment customers, thus ensuring that the orders are placed with Ericsson rather than competitors.

Business Model Canvas - Building Blocks - 05 - Revenue Streams

Revenue Streams = How much money the company makes from each customer segment ?

Note: Costs must be subtracted from revenues to create earnings)

If,

 customers = heart of business model

then,

 revenue = arteries.

The question a company must ask itself, for what value is each customer segment truly willing to pay? Successfully, answering this question allows the firm to generate one or more revenue streams from each customer segment. 

Each revenue stream may have different pricing mechanisms, such as:

• fixed list prices
• bargaining
• auctioning
• market dependent
• volume dependent
• yield management

A business model can involve two different types of revenue streams:

1. Transaction revenues resulting from one time customer payments.
2. Recurring revenues resulting from ongoing payments to either deliver a Value Proposition to customers or provide post-purchase customer support.

Questions to ask ourselves while thinking about Revenue Streams:

• For what value proposition are our customers truly willing to pay?
• For what do they currently pay?
• How are they currently paying?
• How would they prefer to pay?
• How much does each Revenue Stream contribute to overall revenues?

These are some ways to generate Revenue Streams:

• Asset Sale:
  
• This is the most widely understood Revenue Stream.
  
• It derives from selling ownership rights to a physical product.
  
• Example: Maruti sells automobiles, which buyers are free to drive, resell, or even destroy.
• Usage Fee:
  
• This Revenue Stream is generated by use of a particular service. The more a service is used, the more the customer pays.
  
• Example: BSNL/MTNL may charge customers for the number of minutes spent on the phone.
  
• Ginger hotel charges its customers for the number of night roooms used.
  
• DTDC package delivery charges customers for the delivery of a parcel from one location to another.
• Subscription Fees:
  
• This Revenue Stream is generated by selling continuous access to a service.
  
• Example: A fitness club provides access to its facilites in exchange for monthly or yearly subscriptions.
  
• JustBooks is a library which allows users to access reading unlimited  books in exchange for monthly subscription fees.
• Lending/Renting/Leasing:
  
• Make money by temporarily granting someone the exclusive right to use a particular asset for a fixed period in return for a fee.
  
• Example: Ramesh Tours and Travels - allows customers to rent luxurious cars by the hour in Bangalore. Ramesh Babu's service has led many people to decide to rent rather than purchase automobiles.
• Licensing:
  
• This revenue stream is generated by giving customers permission to use protected intellectual property in exchange for licensing fees.
•  Licensing is common in media industry and technology sectors.
• Brokerage Fees:
  
• Generated by Inter-mediation services performed on behalf of two or more parties.
  
• Example: Real Estate agents
• Advertising:
  
• Results from fees for advertising a particular product, service or brand.
  
• Example: In recent years, software and services, have started relying on advertising revenues.

Each Revenue streams can have a different pricing mechanisms. The type of pricing mechanism chosen can make a big difference in terms of revenues generated. 

There are two main types of pricing mechanism: 

• fixed pricing
• dynamic pricing

1. Fixed Pricing - Predefined prices are based on static variables
• List Price - Fixed price of individual products, services or other value propositions.
• Product Feature Dependent - Price dependent on the number or quality of value propositions features.
• Customer segment dependent - Price dependent on the type of the customer segment.
• Volume Dependent - Price as the function of the quantity purchased.
2. Dynamic Pricing: Prices change based on market conditions
• Negotiation (bargaining) - Price negotiated between two or more partners depending on negotiation power and negotiation skills.
• Yield management - Price depends on inventory and time of purchase (normally used for perishable resources such as hotel rooms or airline seats.)
• Real-time market - Price is established dynamically based on supply and demand.
• Auctions - Price determined by outcome of competitive bidding.

Business Model Canvas - Building Blocks - 04 - Customer Relationships

Customer Relationships = Company's relationships with each specific customer Segment

A company should clarify the type of relationship it wants to establish with each customer segment.

Relationship can be:

• personal
• automated

Customer relationships may be driven by the following motivations:

• Customer acquisition
• Customer retention
• Boosting sales (upselling)

Example: In its early days, Reliance Jio mobile network provider is providing free services as part of its aggressive customer acquisition strategy. Once it reaches its limit of customers, it will focus on customer retention and increasing average revenue per customer.

The customer relationships mentioned in the company's business model deeply influences the overall customer experience.

Questions to ask ourselves when thinking about - Customer Relationships:

• What relationship each of our Customer Segments expects us to establish and maintain with them?
• Which ones have we already established?
• How costly is it to maintain the customer relationship?
• How is it integrated with the rest of the business model?

We can distinguish between several categories of customer relationships, which may co-exist in a company's relationship with a particular customer segment:

• Self Service: 
• Company maintains no direct relationship with the customer.
• Personal Assistance: 
• Example: Through Call centeres, Point of Sale, Email.
• Dedicated Personal Assistance: 
• In private banking services, dedicated bankers serve high net worth individuals. 
• Key Account Managers maintain personal relationships with important customers.
• Automated Services: 
• Can recognize individual customers and their characteristics and provide information related to orders or transactions. 
• At best, automated services can simulate a personal relationship (example: offering a book or movie recommendations)
• Communities: 
• Many companies maintain online communites that allow users to exchange knowledge and solve each other's problems. 
• Example: GlaxoSmithKline launched a private online community when it introduced alli, a new prescription free weight loss product.
• Co-creation: 
• Amazon.in invites customers to write reviews and thus create value for other book lovers. 
• Youtube.com solicit customers to create content for public consumption.

Business Model Canvas Example: Gillette 

Business Model Canvas - Building Blocks - 03 - Channels

Channels (Building Block) = how a company reaches its customer segments to deliver Value Propositions

Company's interface with customers:

• communication channels
• distribution channels
• sales channels

Channels are customer touch points which play a crucial role in customer experience. The several functions of channels are:
Raises awareness about company's products and services.
helps customer to judge a company's value proposition
allows customers to purchase specific goods (products and services)
providing customer support - post purchase

Questions to ask ourselves while thinking about channels:

• which channels will our customer segment use to reach us?
• through which channel are we reaching our customer segments now?
• in which ways are our various channels integrated?
• which channels work best?
• which channels are the most cost efficient?
• how are we integrating them (Here, them means - rest of the business model) with customer routines?

There are 5 distinct phases to channels. Each channel can cover some or all of these phases. 

Channels can be distinguished as:

• Direct Channels
• Indirect channels

Channels can also be distinguished as:

• Owned channels
• Partner channels

Finding the right mix of channels is crucial in taking the Value Proposition to market. 

• Partner channels lead to lower margins but they allow an organization to expand its reach and benefit from partner strength.
• Owned Channels and particularly direct ones have higher margins, but can be costly to put in place and to operate. 

The trick is to find the right balance between the different types of channels, to integrate them in a way to create a great customer experience, and to maximize revenues.

Channel Types			Channel Phases
Own	Direct	Sales force	Awareness	Evaluation	Purchase	Delivery	After Sales
		Web sales	how to raise awareness of our products ?	How to help customer evaluate our value proposition ?	How to allow customer to purchase specific products and services ?	How to deliver our Value Proposition ?	How to provide post purchase customer support?
	Indirect	Own stores
Partner		Partner stores
		wholesaler

Business Model Canvas - Building Blocks - 02 - Value Propositions

Value Proposition = Bundle of Products / Services / Benefits which satisfies the needs of a specific customer segment.

Value Proposition is what attracts customers towards a company.
It is the reason for loyalty shift of customers.
Value Proposition is the solution to customers problem.

Value Proposition maybe:

• Innovative and Disruptive in terms of offerings to customers.
• Value addition to existing offerings in the market like: additional features and attributes.

Values maybe:
Quantitative: Price , Speed of Service
Qualitative: Design, Customer Experience


Questions to ask while thinking about value Propositions:

• What value is being delivered to the customer?
• Which particular customer problem are we addressing?
• Which needs of the customer are we fulfilling?
• What Bundle of Products is being offered by us to each Customer Segment?

Handy Tips for that can contribute to customer value creation:

1. Newness
• Satisfy entirely new set of needs. Not always Technology
• Example: Technology: Cell Phones
• Example: Non-Technology: Ethical Investment Funds
  
2. Performance
• Most common way to create value
• Example: PC sector - new machines more powerful than previous ones.
• It has limits: faster PCs, more disk storage, better graphics have failed to produce growth in demand.
  
3. Customization
• Tailoring products to suit customer (individual / segment) needs creates value.
• Mass Customization and Customer co-creation have gained importance.
4. "Getting the Job Done"
• Simply by helping certain jobs of customer to be done.
• Example: Airline customers rely entirely on Rolls-Royce to manufacture and service their jet engines. This arrangement allows the customer to focus on running their airlines. Rolls Royce is paid for every hour the engine runs.
  
5. Design
• Difficult to measure Design.
  
• A product may stand out due to superior design.
• Example: Fashion and Consumer Electronics.
  
6. Brand / Status
• Simple act of using and displaying specific brands.
• Example: Wearing a Rolex.
  
7. Price
• Similar value offered at lower price - meets the need of price-sensitive customer segments.
• Example: Reliance Jio, Air Deccan
• Example: Tata Nano - makes automobile affordable to a whole new segment of Indian population.
  
8. Cost Reduction
• Help customers to reduce their costs.
• Example: salesforce.com sells a hosted CRM application. Relieves operating and maintenance costs for customers.
  
9. Risk Reduction
• Customers value reducing the risks
• Example: One year guarantee for used car buyers reduces the risk of post purchase breakdowns.
  
10. Accessibility
• Making products / services available to customer segments who previously lacked them - creates value.
• This is result of: BusinessConvenience / Usability Model Innovation, Technology , Both
• Example: Mutual Funds - made it possible even for those with modest wealth to build diversified investment portfolios.
  
11. Convenience / Usability
• Make things convinient / easier to use - creates value.
• Example: iPod and iTunes - apple offered customers unprecedented convenience searching, buying, downloading and listening to digital music.
  

Slack - Value Proposition Canvas:

Database - Configuring and using Amazon Redshift , S3 and JackDB console

Step 0: Login to Amazon Web Console and Choose Redshift from Services:

Step 1: Choose you preferred AWS Zone. We have chosen Sydney. Now click on Launch a Redshift cluster:

Step 2: Cluster Details - Key in appropriate Cluster and Database Information :

The information entered is as follows:

Field Name	Value Entered
Cluster identifier*	cluster01
Database name	cluster01db01
Database port*	5439
Master user name*	master
Master user password*	Redshift123
Confirm password*	Redshift123

Click Continue to proceed to the Node Configuration Page...

Step 3: In the Node Configuration page the default settings should match these values:

1. Node Type: dc1.large
2. Cluster Type: Single Node
3. Number of Compute Nodes: 1

Click Continue to move to the Additional Configuration page...

Step 4: Additional Configuration:

In the Additional Configuration section, for the Cluster Parameter Group (if an option is available), select default.redshift-1.0. Otherwise, leave it at the default.

For the Encrypt Database option, select None.
The database does not need to be encrypted for this tutorial.

In the Configure Networking Options and optional settings sections make sure that the configuration matches the following:

1. Choose a VPC: Default VPC(vpc- xxxxx)
2. Cluster Subnet Group: default
3. Publicly Accessible: Yes
4. Choose a Public IP Address: No
5. Availability Zone: no preference
6. VPC Security Groups : default( sg-xxxxx )
7. Create CloudWatch Alarm: No

Note You may safely ignore any permissions warnings you may see.

Step 5: Additional Configuration - Click Continue:

(You may be presented with a warning regarding pricing which you can ignore.)

Step 6: Review the settings of the cluster that you are about to launch. When you are ready to create the cluster, click the Launch Cluster button at the bottom of the screen.

The cluster may take a few minutes to generate.


Step 7: You can now view its status by clicking View your cluster on the Clusters dashboard. Otherwise you may select Close.

Step 8: cluster01 is the cluster created just now:

Configure Secure Client Access to an Amazon Redshift Cluster

With Amazon Redshift it is possible to query your database using third party client software. This will allow you to interact with your database with ease using SQL queries that are compatible with ANSI SQL and PostgreSQL.


Before you can connect the client, you will have to configure an AWS Security Group to allow traffic from that client to your Amazon Redshift cluster.

The client software used for this exercise is JackDB. It is a hosted service with a 14 day free trial that we can use .

In your web browser, open a new tab and go to https://www.jackdb.com.
Click START YOUR FREE TRIAL.
If you already have a JackDB account, log in to it and skip the next two steps.

Enter your details in the Sign up for JackDB page, and click Create an account.
Once you sign up confirmation mail will be sent to your email address.

Open the confirmation e-mail, click the link to activate your account, and then log in to your JackDB account.
Click the + Add a data source button in the top right of the main page.

Click Amazon Redshift to create a new data source.

You will now be brought to a connection creation page.

In the Security and Network section make a note of the IP address that JackDB will use to connect from.

Grant client access to your cluster

To allow client access to your Redshift databases you need to modify the Security Group to allow inbound TCP traffic on the port configured for your Redshift cluster from the ip-address that will be used by your client. This can be a range of addresses but in this lab we only need to provide access to a single IP address.

Return to the browser tab that is open on the AWS Console. On the Services menu, click EC2.

Click Security Groups.

Confirm that the default group is selected by clicking anywhere on the default security group row. You should only see one security group.
Click the Inbound tab, at the bottom of the screen.
Click Edit.
Click the Add Rule button to add an Inbound Rule (if needed).

For Type select Redshift. This sets the protocol to TCP and the Port Range to 5439, the default for Amazon Redshift.
For Source, select Custom from the drop-down list, and enter the JackDB Client™s origin IP address you obtained and noted down in the earlier step followed by /32 subnet suffix, eg.107.23.138.60/32.
Note that the IP address provided may be different to the example given.


Click Save.

Connect Client Software to an Amazon Redshift Cluster. Explore your cluster details:

Click Services and then Redshift to return to the AWS Redshift console page.
Once the cluster has been created and the Cluster Status indicates that the cluster is **available, then click the cluster name (the clickable link *cluster01**) to open it.

The clusters status can be viewed in the left navigational pane at any time during its creation by clicking on the Clusters menu item. The DB Health and In Maintenance fields may show an unknown state for a short period after the cluster is initialized. The cluster's details can be viewed by clicking on the cluster's name or by clicking the page details icon as shown below.

This will bring you into the Configuration tab. Within the Configuration tab you can view the Cluster Properties, Cluster Status, Cluster Database Properties, Backup, Audit Logging and Maintenance, Capacity Details and SSH Ingestion Settings of the cluster chosen.

Cluster Properties contains information about the Cluster including Name, Type, Node Type, number of Nodes, Zone location, Time and version of the creation as well as other information.

Cluster Status allows you to see the current status of the cluster whether it is available or not and also whether it is currently In Maintenance Mode.

Cluster Database Properties contains information on the Endpoint, which is the DNS address of the cluster, and the port number on which the database accepts connections. These are required when you want to create SQL connections. It also lets you know whether the cluster has a public IP address that can be accessed from the public internet. The JDBC URL and ODBC URL contain the URLs to connect to the cluster via a java database connection or an Oracle database connection client.

Backup, Audit Logging and Maintenance contains information on how many days the automated snapshots are retained, whether they are automatically copied to another region, and whether logging is enabled on the cluster.

Capacity Details contains information about the data warehouse node type, number of EC2 Compute Units per node, memory, disk storage, I/O performance as well as the processor architecture of the node type.

SSH Ingestion Settings contains information about the Public Key of the cluster as well as the Public and Private IP addresses of the node.

Locate the Endpoint for your database

The database Endpoint is the address of the database that we will use to configure the client.

In the Configuration tab, under Cluster Database Properties, find the Endpoint. The Endpoint will be required to connect the client to the database. Copy the Endpoint name and save it in a text file for use in the next section.




Configure your client database connection

Return to the browser tab that is open on the JackDB connection creation page.
Enter the data as displayed below for the new connection.



a. Title: Redshift Test Cluster01

b. Server (hostname or IP address): Paste in the Endpoint you recorded above, without the port number : cluster01.chargwxquyiw.ap-southeast-2.redshift.amazonaws.com:5439

c. Port number: 5439

d. Database Name: cluster01db01

e. Database user: master

f. Database password: Redshift123


Click Create this data source.



Then click Connect to this data source. If the connection was successful you be brought to the Data Sources page.

Once the connection has been established, click the Back to home button at the top right of the screen.

If an error occurs please check the following:
a. That the Endpoint is correct.

b. The port number is the correct and your system firewall is not blocking TCP port 5439.

c. That the Database name, Userid and Password are correct.

d. The Security Group inbound permissions have been configured correctly.

Load Sample Data into an Amazon Redshift Cluster

Create a test database table

After the connection has been established with JackDb, queries can be executed on the database. You will now execute SQL commands to create a table and import sample data.

Copy the SQL command below into the command screen.

create table users( userid integer not null distkey sortkey, username char(8), firstname varchar(30), lastname varchar(30), city varchar(30), state char(2), email varchar(100), phone char(14), likesports boolean, liketheatre boolean, likeconcerts boolean, likejazz boolean, likeclassical boolean, likeopera boolean, likerock boolean, likevegas boolean, likebroadway boolean, likemusicals boolean);

Click the Execute button above the SQL Command line windows to execute this SQL script.

The command should execute and display Executed Successfully in the result section below the query.

Load sample data from S3 into your test database table

Switch back to the JackDB tab on your system.
Delete the earlier SQL command line.
Copy the following SQL command SQL command window in JackDB.
copy users from 's3://awssampledbuswest2/tickit/allusers_pipe.txt'

CREDENTIALS 'aws_access_key_id=Access Key Id;aws_secret_access_key=Secret Access Key'

delimiter '|';
Copy the S3 link for the region you selected when launching the lab from the list below and use it to replace the S3 link in the SQL command in the JackDB command window. The S3 link is the green text in the command above. You can find the region in the upper right hand side of the AWS console between the awsstudent user name and the Support link if you did not record it earlier.
US East 1 (Northern Virginia): s3://awssampledb/tickit/allusers_pipe.txt

US West 2 (Oregon): s3://awssampledbuswest2/tickit/allusers_pipe.txt

EU West 1 (Ireland): s3://awssampledbeuwest1/tickit/allusers_pipe.txt

AP SouthEast 1 (Singapore): s3://awssampledbapsoutheast1/tickit/allusers_pipe.txt

AP SouthEast 2 (Sydney): s3://awssampledbapsoutheast2/tickit/allusers_pipe.txt

AP NorthEast 1 (Tokyo): s3://awssampledbapnortheast1/tickit/allusers_pipe.txt

In the JackDB command windows now replace the values for the Access Key Id and Secret Access Key that you saved in the previous section. These are the two items of red text items in the SQL command.
Click the Execute button again to execute the SQL command.
Note: It may take a few moments to execute the command. If successful it will report:

Executed Successfully

Updated 0 rows in xx.xxx seconds


The copy command will not report the number of table rows that have been imported in this lab with this client. If the command does not execute correctly please ensure that command is modified as stated above with your own lab access key values and retry. The command should look something like this:

Query an Amazon Redshift Database From an External Client

Execute SQL queries against your test database

Now that you have data in your Redshift database you can query the data using SQL select statements and queries. Note that these are just sample queries to demonstrate that standard SQL commands work.

Delete the previous query from the SQL command window and enter the following command:
SELECT * from users;

Click the Execute button again to execute the SQL command.

This style of query, which returns all columns, is not recommended for Redshift since it is a columnar database that is designed for queries that restrict the columns in the query and result. The command syntax is still valid and functions correctly however.

Delete the previous query from the SQL command window and enter the following command to see the results:
SELECT userid,firstname,lastname,city,likesports from users where likesports = 'true' order by firstname;

Click the Execute button again to execute the SQL command.


We have been able to successfully load data from S3 bucket into Amazon Redshift!!

Let us have a look the properties of our Amazon Redshift Cluster Endpoint:

We can now sign out of our Amazon Web Services Console: