Blog

You are currently viewing Dimensional modeling in Amazon Redshift

Dimensional modeling in Amazon Redshift


Amazon Redshift is a totally managed and petabyte-scale cloud knowledge warehouse that’s utilized by tens of 1000’s of shoppers to course of exabytes of information on daily basis to energy their analytics workload. You may construction your knowledge, measure enterprise processes, and get beneficial insights shortly will be carried out through the use of a dimensional mannequin. Amazon Redshift offers built-in options to speed up the method of modeling, orchestrating, and reporting from a dimensional mannequin.

On this publish, we talk about the best way to implement a dimensional mannequin, particularly the Kimball methodology. We talk about implementing dimensions and information inside Amazon Redshift. We present the best way to carry out extract, remodel, and cargo (ELT), an integration course of centered on getting the uncooked knowledge from an information lake right into a staging layer to carry out the modeling. Total, the publish provides you with a transparent understanding of the best 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 exhibit the important thing elements of the dimensional mannequin. After that, we create an information mart utilizing Amazon Redshift with a dimensional knowledge mannequin together with dimension and reality tables. Information is loaded and staged utilizing the COPY command, the info within the dimensions is loaded utilizing the MERGE assertion, and information can be joined to the size the place insights are derived from. We schedule the loading of the size and information utilizing the Amazon Redshift Question Editor V2. Lastly, we use Amazon QuickSight to realize 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 publish, we’ve got narrowed down the dataset for simplicity and demonstration functions. The next tables present examples of the info for ticket gross sales and venues.

In response to 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 size.
  4. Establish and implement the information.

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

Stipulations

For this walkthrough, you must 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 corporation. Normally, corporations have some type of operational supply system that generates their knowledge in its uncooked format. It is a good place to begin to establish varied sources for a enterprise course of.

The enterprise course of is then endured as a knowledge mart within the type of dimensions and information. Taking a look at our pattern dataset talked about earlier, we will clearly see the enterprise course of is the gross sales made for a given occasion.

A typical mistake made is utilizing departments of an organization because the enterprise course of. The information (enterprise course of) must be built-in throughout varied departments, on this case, advertising and marketing can entry the gross sales knowledge. Figuring out the proper enterprise course of is important—getting this step unsuitable can impression your entire knowledge mart (it might trigger the grain to be duplicated and incorrect metrics on the ultimate studies).

Declare the grain of your knowledge

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

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

Establish and implement the size

Your dimension desk describes your reality desk and its attributes. When figuring out the descriptive context of your corporation course of, you retailer the textual content in a separate desk, conserving the very fact desk grain in thoughts. When becoming a member of the size 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’ll 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 carefully align with the enterprise course of; subsequently, 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 size.

Stage the supply knowledge

Earlier than we will create and cargo the size desk, we’d like supply knowledge. Due to this fact, we stage the supply knowledge right into a staging or non permanent 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 info from the dimensional-modeling-in-amazon-redshift public S3 bucket situated on the us-east-1 Area. Notice 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);

  1. 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

  1. 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;

  1. 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

  1. 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);

  1. 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 size desk

Designing the size desk can rely on your corporation requirement—for instance, do you want to monitor adjustments to the info 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 adjustments. For extra about kind 2, seek advice from Simplify knowledge loading into Kind 2 slowly altering dimensions in Amazon Redshift. The scale desk can be denormalized with a major key, surrogate key, and some added fields to point adjustments to the desk. See the next code:

create schema SalesMart;

CREATE TABLE SalesMart.DimVenue( 
    "VenueSkey" int IDENTITY(1,1) major 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;

Just a few notes on creating the size desk creation:

  • The sphere names are remodeled into business-friendly names
  • Our major key’s VenueID, which we use to uniquely establish a venue at which the sale happened
  • Two extra rows can be added, indicating when a report was inserted and up to date (to trace adjustments)
  • We’re utilizing an AUTO distribution model to provide Amazon Redshift the duty to decide on and alter the distribution model

One other necessary issue to think about in dimensional modelling is the utilization of surrogate keys. Surrogate keys are synthetic keys which can be utilized in dimensional modelling to uniquely establish every report in a dimension desk. They’re usually generated as a sequential integer, they usually don’t have any which means within the enterprise area. They provide a number of advantages, reminiscent of guaranteeing 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 enables 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 mechanically 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.

Just a few ideas for designing surrogate keys:

  • Use a small, fixed-width knowledge kind for the surrogate key. It will enhance efficiency and cut back space for storing.
  • Use the IDENTITY key phrase, or generate the surrogate key utilizing a sequential or GUID worth. It will be certain that the surrogate key’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 may arrange the MERGE assertion in a saved process to populate the info. You then schedule the saved process to run programmatically by way of the question editor, which we exhibit later within the publish. 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;

Just a few notes on the dimension loading:

  • When a report in inserted for the primary time, the inserted date and up to date date can be populated. When any values change, the info 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 info can be utilized by enterprise customers, we have to change NULL values, if any, with extra business-appropriate values.

Establish and implement the information

Now that we’ve got declared our grain to be the occasion of a sale that happened at a particular time, our reality desk will retailer the numeric information for our enterprise course of.

We have now recognized the next numerical information to measure:

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

Implementing the Truth

There are three kinds of reality tables (transaction reality desk, periodic snapshot reality desk, and accumulating snapshot reality desk). Every serves a distinct view of the enterprise course of. For our instance, we use a transaction reality 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 report was loaded. You should use 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 observe however elective to have calendar date dimensions, which permit the end-user to navigate the very fact desk. Information can both be loaded when there’s a new sale, or day by 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.

  1. Create the saved process with the next code. To maintain the identical knowledge integrity that we utilized within the dimension load, we change NULL values, if any, with extra enterprise acceptable values:
create or change process SalesMart.FactSaleTransactionsLoad(loadate datetime)
language plpgsql
as
    $$
start
--------------------------------------------------------------------
/*** Delete data loaded for the day, ought to there be any ***/
--------------------------------------------------------------------
Delete from SalesMart.FactSaleTransactions
the place forged(InsertedDate as date) = CAST(loadate as date);
RAISE INFO 'Deleted rows for load date: %', loadate;
--------------------------------------------------------------------
/*** Insert data ***/
--------------------------------------------------------------------
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');
--Optionally available filter, do you have to need to load solely the most recent knowledge from supply
--where forged(a.saletime as date) = forged(loadate as date);
  
finish;
$$;

  1. Load the info by calling the process with the next command:
name SalesMart.FactSaleTransactionsLoad(getdate())

Schedule the info 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 Truth Hundreds. They are going to 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 need to load the very fact desk with outdated dimensions.

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

  1. We schedule the question to run on daily basis at 5:00 AM.
  2. Optionally, you’ll be able to add failure notifications by enabling Amazon Easy Notification Service (Amazon SNS) notifications.

Report and evaluation the info in Amazon Quicksight

QuickSight is a enterprise intelligence service that makes it straightforward to ship insights. As a totally managed service, QuickSight permits you to simply create and publish interactive dashboards that may then be accessed from any machine and embedded into your purposes, 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, seek advice from 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 info mart.

Our finish dashboard will comprise the insights of the info mart and reply important enterprise questions, reminiscent of complete fee per venue and dates with the very best gross sales. The next screenshot exhibits the ultimate product of the info mart.

Clear up

To keep away from incurring future fees, delete any sources you created as a part of this publish.

Conclusion

We have now now efficiently carried out an information mart utilizing our DimVenue, DimCalendar, and FactSaleTransactions tables. Our warehouse isn’t full; as we will develop the info mart with extra information and implement extra marts, and because the enterprise course of and necessities develop over time, so will the info warehouse. On this publish, 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 at the moment.

Concerning 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 guaranteeing knowledge governance and safety. He’s keen about utilizing knowledge to drive insights, whereas aligning with enterprise necessities and targets.

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

Leave a Reply