Dimensional modeling in Amazon Redshift

Amazon Redshift is a completely managed and petabyte-scale cloud knowledge warehouse that’s utilized by tens of hundreds of shoppers to course of exabytes of knowledge every single day to energy their analytics workload. You possibly can construction your knowledge, measure enterprise processes, and get precious insights shortly could be performed by utilizing a dimensional mannequin. Amazon Redshift gives built-in options to speed up the method of modeling, orchestrating, and reporting from a dimensional mannequin.

On this put up, we talk about the right way to implement a dimensional mannequin, particularly the Kimball methodology. We talk about implementing dimensions and information inside Amazon Redshift. We present the right way to carry out extract, rework, and cargo (ELT), an integration course of centered on getting the uncooked knowledge from a knowledge lake right into a staging layer to carry out the modeling. General, the put up will provide you with a transparent understanding of the right way to use dimensional modeling in Amazon Redshift.

Answer overview

The next diagram illustrates the answer structure.

Within the following sections, we first talk about and reveal the important thing facets of the dimensional mannequin. After that, we create a knowledge mart utilizing Amazon Redshift with a dimensional knowledge mannequin together with dimension and truth tables. Knowledge is loaded and staged utilizing the COPY command, the information within the dimensions is loaded utilizing the MERGE assertion, and information shall be joined to the scale the place insights are derived from. We schedule the loading of the scale and information utilizing the Amazon Redshift Question Editor V2. Lastly, we use Amazon QuickSight to achieve insights on the modeled knowledge within the type of a QuickSight dashboard.

For this resolution, we use a pattern dataset (normalized) offered by Amazon Redshift for occasion ticket gross sales. For this put up, now we have narrowed down the dataset for simplicity and demonstration functions. The next tables present examples of the information for ticket gross sales and venues.

In accordance with the Kimball dimensional modeling methodology, there are 4 key steps in designing a dimensional mannequin:

1. Establish the enterprise course of.
2. Declare the grain of your knowledge.
3. Establish and implement the scale.
4. Establish and implement the information.

Moreover, we add a fifth step for demonstration functions, which is to report and analyze enterprise occasions.

Conditions

For this walkthrough, you need to have the next conditions:

Establish the enterprise course of

In easy phrases, figuring out the enterprise course of is figuring out a measurable occasion that generates knowledge inside a company. Often, firms have some kind of operational supply system that generates their knowledge in its uncooked format. It is a good start line to establish numerous sources for a enterprise course of.

The enterprise course of is then persevered as a knowledge mart within the type of dimensions and information. our pattern dataset talked about earlier, we are able to clearly see the enterprise course of is the gross sales made for a given occasion.

A standard mistake made is utilizing departments of an organization because the enterprise course of. The info (enterprise course of) must be built-in throughout numerous departments, on this case, advertising and marketing can entry the gross sales knowledge. Figuring out the proper enterprise course of is essential—getting this step incorrect can impression all the knowledge mart (it will possibly trigger the grain to be duplicated and incorrect metrics on the ultimate stories).

Declare the grain of your knowledge

Declaring the grain is the act of uniquely figuring out a file in your knowledge supply. The grain is used within the truth desk to precisely measure the information and allow you to roll up additional. In our instance, this might be a line merchandise within the gross sales enterprise course of.

In our use case, a sale could be uniquely recognized by wanting on the transaction time when the sale came about; this would be the most atomic stage.

Establish and implement the scale

Your dimension desk describes your truth desk and its attributes. When figuring out the descriptive context of your enterprise course of, you retailer the textual content in a separate desk, maintaining the very fact desk grain in thoughts. When becoming a member of the scale desk to the very fact desk, there ought to solely be a single row related to the very fact desk. In our instance, we use the next desk to be separated right into a dimensions desk; these fields describe the information that we are going to measure.

When designing the construction of the dimensional mannequin (the schema), you’ll be able to both create a star or snowflake schema. The construction ought to intently align with the enterprise course of; due to this fact, a star schema is finest match for our instance. The next determine exhibits our Entity Relationship Diagram (ERD).

Within the following sections, we element the steps to implement the scale.

Stage the supply knowledge

Earlier than we are able to create and cargo the scale desk, we want supply knowledge. Subsequently, we stage the supply knowledge right into a staging or short-term desk. That is sometimes called the staging layer, which is the uncooked copy of the supply knowledge. To do that in Amazon Redshift, we use the COPY command to load the information from the dimensional-modeling-in-amazon-redshift public S3 bucket positioned on the us-east-1 Area. Word that the COPY command makes use of an AWS Id and Entry Administration (IAM) position with entry to Amazon S3. The position must be related to the cluster. Full the next steps to stage the supply knowledge:

1. Create the venue supply desk:

CREATE TABLE public.venue (
    venueid bigint,
    venuename character various(100),
    venuecity character various(30),
    venuestate character(2),
    venueseats bigint
) DISTSTYLE AUTO
        SORTKEY
    (venueid);

2. Load the venue knowledge:

COPY public.venue
FROM 's3://redshift-blogs/dimensional-modeling-in-amazon-redshift/venue.csv'
IAM_ROLE '<Your IAM position arn>'
DELIMITER ','
REGION 'us-east-1'
IGNOREHEADER 1

3. Create the gross sales supply desk:

CREATE TABLE public.gross sales (
    salesid integer,
    venueid character various(256),
    saletime timestamp with out time zone,
    qtysold BIGINT,
    fee numeric(18,2),
    pricepaid numeric(18,2)
) DISTSTYLE AUTO;

4. Load the gross sales supply knowledge:

COPY public.gross sales
FROM 's3://redshift-blogs/dimensional-modeling-in-amazon-redshift/gross sales.csv'
IAM_ROLE '<Your IAM position arn>'
DELIMITER ','
REGION 'us-east-1'
IGNOREHEADER 1

5. Create the calendar desk:

CREATE TABLE public.DimCalendar(
    dateid smallint,
        caldate date,
        day varchar(20),
        week smallint,
        month varchar(20),
        qtr varchar(20),
        yr smallint,
        vacation boolean
) DISTSTYLE AUTO
SORTKEY
    (dateid);

6. Load the calendar knowledge:

COPY public.DimCalendar
FROM 's3://redshift-blogs/dimensional-modeling-in-amazon-redshift/date.csv'
IAM_ROLE '<Your IAM position arn>'
DELIMITER ',' 
REGION 'us-east-1'
IGNOREHEADER 1

Create the scale desk

Designing the scale desk can depend upon your enterprise requirement—for instance, do that you must observe modifications to the information over time? There are seven totally different dimension sorts. For our instance, we use kind 1 as a result of we don’t want to trace historic modifications. For extra about kind 2, consult with Simplify knowledge loading into Kind 2 slowly altering dimensions in Amazon Redshift. The scale desk shall be denormalized with a main key, surrogate key, and some added fields to point modifications to the desk. See the next code:

create schema SalesMart;

CREATE TABLE SalesMart.DimVenue( 
    "VenueSkey" int IDENTITY(1,1) main key
    ,"VenueId" VARCHAR NOT NULL
    ,"VenueName" VARCHAR NULL
    ,"VenueCity" VARCHAR NULL
    ,"VenueState" VARCHAR NULL
    ,"VenueSeats" INT NULL
    ,"InsertedDate" DATETIME NOT NULL
    ,"UpdatedDate" DATETIME NOT NULL
) 
diststyle AUTO;

A couple of notes on creating the scale desk creation:

• The sector names are remodeled into business-friendly names
• Our main secret’s VenueID, which we use to uniquely establish a venue at which the sale came about
• Two further rows shall be added, indicating when a file was inserted and up to date (to trace modifications)
• We’re utilizing an AUTO distribution fashion to present Amazon Redshift the duty to decide on and alter the distribution fashion

One other essential issue to think about in dimensional modelling is the utilization of surrogate keys. Surrogate keys are synthetic keys which might be utilized in dimensional modelling to uniquely establish every file in a dimension desk. They’re usually generated as a sequential integer, and so they don’t have any that means within the enterprise area. They provide a number of advantages, comparable to making certain uniqueness and bettering efficiency in joins, as a result of they’re usually smaller than pure keys and as surrogate keys they don’t change over time. This permits us to be constant and be a part of information and dimensions extra simply.

In Amazon Redshift, surrogate keys are usually created utilizing the IDENTITY key phrase. For instance, the previous CREATE assertion creates a dimension desk with a VenueSkey surrogate key. The VenueSkey column is robotically populated with distinctive values as new rows are added to the desk. This column can then be used to hitch the venue desk to the FactSaleTransactions desk.

A couple of suggestions for designing surrogate keys:

• Use a small, fixed-width knowledge kind for the surrogate key. This can enhance efficiency and scale back space for storing.
• Use the IDENTITY key phrase, or generate the surrogate key utilizing a sequential or GUID worth. This can be certain that the surrogate secret’s distinctive and may’t be modified.

Load the dim desk utilizing MERGE

There are quite a few methods to load your dim desk. Sure components should be thought-about—for instance, efficiency, knowledge quantity, and maybe SLA loading instances. With the MERGE assertion, we carry out an upsert without having to specify a number of insert and replace instructions. You possibly can arrange the MERGE assertion in a saved process to populate the information. You then schedule the saved process to run programmatically by way of the question editor, which we reveal later within the put up. The next code creates a saved process known as SalesMart.DimVenueLoad:

CREATE OR REPLACE PROCEDURE SalesMart.DimVenueLoad()
AS $$
BEGIN
MERGE INTO SalesMart.DimVenue USING public.venue as MergeSource
ON SalesMart.DimVenue.VenueId = MergeSource.VenueId
WHEN MATCHED
THEN
UPDATE
SET VenueName = ISNULL(MergeSource.VenueName, 'Unknown')
, VenueCity = ISNULL(MergeSource.VenueCity, 'Unknown')
, VenueState = ISNULL(MergeSource.VenueState, 'Unknown')
, VenueSeats = ISNULL(MergeSource.VenueSeats, -1)
, UpdatedDate = GETDATE()
WHEN NOT MATCHED
THEN
INSERT (
VenueId
, VenueName
, VenueCity
, VenueState
, VenueSeats
, UpdatedDate
, InsertedDate
)
VALUES (
ISNULL(MergeSource.VenueId, -1)
, ISNULL(MergeSource.VenueName, 'Unknown')
, ISNULL(MergeSource.VenueCity, 'Unknown')
, ISNULL(MergeSource.VenueState, 'Unknown')
, ISNULL(MergeSource.VenueSeats, -1)
, ISNULL(GETDATE() , '1900-01-01')
, ISNULL(GETDATE() , '1900-01-01')
);
END;
$$
LANGUAGE plpgsql;

A couple of notes on the dimension loading:

• When a file in inserted for the primary time, the inserted date and up to date date shall be populated. When any values change, the information is up to date and the up to date date displays the date when it was modified. The inserted date stays.
• As a result of the information shall be utilized by enterprise customers, we have to exchange NULL values, if any, with extra business-appropriate values.

Establish and implement the information

Now that now we have declared our grain to be the occasion of a sale that came about at a particular time, our truth desk will retailer the numeric information for our enterprise course of.

We have now recognized the next numerical information to measure:

• Amount of tickets offered per sale
• Fee for the sale

Implementing the Reality

There are three forms of truth tables (transaction truth desk, periodic snapshot truth desk, and accumulating snapshot truth desk). Every serves a special view of the enterprise course of. For our instance, we use a transaction truth desk. Full the next steps:

1. Create the very fact desk

CREATE TABLE SalesMart.FactSaleTransactions( 
    CalendarDate date NOT NULL
    ,SaleTransactionTime DATETIME NOT NULL
    ,VenueSkey INT NOT NULL
    ,QuantitySold BIGINT NOT NULL
    ,SaleComission NUMERIC NOT NULL
    ,InsertedDate DATETIME DEFAULT GETDATE()
) diststyle AUTO;

An inserted date with a default worth is added, indicating if and when a file was loaded. You should utilize this when reloading the very fact desk to take away the already loaded knowledge to keep away from duplicates.

Loading the very fact desk consists of a easy insert assertion becoming a member of your related dimensions. We be a part of from the DimVenue desk that was created, which describes our information. It’s finest follow however non-obligatory to have calendar date dimensions, which permit the end-user to navigate the very fact desk. Knowledge can both be loaded when there’s a new sale, or every day; that is the place the inserted date or load date turns out to be useful.

We load the very fact desk utilizing a saved process and use a date parameter.

2. Create the saved process with the next code. To maintain the identical knowledge integrity that we utilized within the dimension load, we exchange NULL values, if any, with extra enterprise acceptable values:

create or exchange process SalesMart.FactSaleTransactionsLoad(loadate datetime)
language plpgsql
as
    $$
start
--------------------------------------------------------------------
/*** Delete information loaded for the day, ought to there be any ***/
--------------------------------------------------------------------
Delete from SalesMart.FactSaleTransactions
the place solid(InsertedDate as date) = CAST(loadate as date);
RAISE INFO 'Deleted rows for load date: %', loadate;
--------------------------------------------------------------------
/*** Insert information ***/
--------------------------------------------------------------------
INSERT INTO SalesMart.FactSaleTransactions (
CalendarDate    
,SaleTransactionTime    
,VenueSkey  
,QuantitySold  
,Salecomission
)
SELECT DISTINCT
    ISNULL(c.caldate, '1900-01-01') as CalendarDate
    ,ISNULL(a.saletime, '1900-01-01') as SaleTransactionTime
    ,ISNULL(b.VenueSkey, -1) as VenueSkey
    ,ISNULL(a.qtysold, 0) as QuantitySold
    ,ISNULL(a.fee, 0) as SaleComission
FROM
    public.gross sales as a
 
LEFT JOIN SalesMart.DimVenue as b
on a.venueid = b.venueid
 
LEFT JOIN public.DimCalendar as c
on to_char(a.saletime,'YYYYMMDD') = to_char(c.caldate,'YYYYMMDD');
--Elective filter, must you wish to load solely the most recent knowledge from supply
--where solid(a.saletime as date) = solid(loadate as date);
  
finish;
$$;

3. Load the information by calling the process with the next command:

name SalesMart.FactSaleTransactionsLoad(getdate())

Schedule the information load

We are able to now automate the modeling course of by scheduling the saved procedures in Amazon Redshift Question Editor V2. Full the next steps:

1. We first name the dimension load and after the dimension load runs efficiently, the very fact load begins:

BEGIN;
----Insert Dim Hundreds
name SalesMart.DimVenueLoad();

----Insert Reality Hundreds. They may solely run if the DimLoad is profitable
name SalesMart.FactSaleTransactionsLoad(getdate());
END;

If the dimension load fails, the very fact load won’t run. This ensures consistency within the knowledge as a result of we don’t wish to load the very fact desk with outdated dimensions.

2. To schedule the load, select Schedule in Question Editor V2.

3. We schedule the question to run every single day at 5:00 AM.
4. Optionally, you’ll be able to add failure notifications by enabling Amazon Easy Notification Service (Amazon SNS) notifications.

Report and evaluation the information in Amazon Quicksight

QuickSight is a enterprise intelligence service that makes it simple to ship insights. As a completely managed service, QuickSight enables you to simply create and publish interactive dashboards that may then be accessed from any system and embedded into your functions, portals, and web sites.

We use our knowledge mart to visually current the information within the type of a dashboard. To get began and arrange QuickSight, consult with Making a dataset utilizing a database that’s not autodiscovered.

After you create your knowledge supply in QuickSight, we be a part of the modeled knowledge (knowledge mart) collectively primarily based on our surrogate key skey. We use this dataset to visualise the information mart.

Our finish dashboard will comprise the insights of the information mart and reply essential enterprise questions, comparable to whole fee per venue and dates with the very best gross sales. The next screenshot exhibits the ultimate product of the information mart.

Clear up

To keep away from incurring future prices, delete any sources you created as a part of this put up.

Conclusion

We have now now efficiently applied a knowledge mart utilizing our DimVenue, DimCalendar, and FactSaleTransactions tables. Our warehouse isn’t full; as we are able to broaden the information mart with extra information and implement extra marts, and because the enterprise course of and necessities develop over time, so will the information warehouse. On this put up, we gave an end-to-end view on understanding and implementing dimensional modeling in Amazon Redshift.

Get began along with your Amazon Redshift dimensional mannequin as we speak.

In regards to the Authors

Bernard Verster is an skilled cloud engineer with years of publicity in creating scalable and environment friendly knowledge fashions, defining knowledge integration methods, and making certain knowledge governance and safety. He’s obsessed with utilizing knowledge to drive insights, whereas aligning with enterprise necessities and aims.

Abhishek Pan is a WWSO Specialist SA-Analytics working with AWS India Public sector prospects. He engages with prospects to outline data-driven technique, present deep dive periods on analytics use circumstances, and design scalable and performant analytical functions. He has 12 years of expertise and is obsessed with databases, analytics, and AI/ML. He’s an avid traveler and tries to seize the world by way of his digital camera lens.