Create PostgreSQL DB

Objectives

I’ll be working this project at times on the cloud and at times on the local machine. We’ll start with these steps to create a DataWarehouse using PostgreSQL and mostly pgAdmin

  • Design a Data Warehouse using the pgAdmin ERD design tool.
  • Create the schema in the Data Warehouse

As well as being able to create reports like:

  • total sales per year per country
  • total sales per month per category
  • total sales per quarter per country
  • total sales per category per country

Design DW

Data

Design Star Schema

In order to design a Star Schema for the warehouse by identifying the columns for the various dimension and fact tables in the schema we need to

  • Create a PostgreSQL Instance
  • Save password:
  • In left panel > Servers>PostgreSQL>Databases>
  • Create a new db and name it: softcart
  • After creating the db above we need to create Tables in the db, so in the left panel make sure you are in the softcart db
  • Scroll down to Schemas tab > expand
  • Scroll down to Tables tab and
  • Right click > SQL query and enter the SQL code to create the tables

softcartDimDate

  • I’ll be using the ERD design tool design the table softcartDimDate.
  • The company is looking at a granularity of a day. Which means they would like to have the ability to generate the report on
    • yearly
    • monthly
    • daily, and
    • weekday basis.

Here is a list of the fields in DimDate I find useful:

  1. dateid
  2. date
  3. year
  4. quarter
  5. quartername
  6. month
  7. monthname
  8. day
  9. weekday
  10. weekdayname

CREATE TABLE softcartDimDate (
    dateid INT PRIMARY KEY,
    date DATE NOT NULL,
    year INT NOT NULL,
    quarter INT NOT NULL,
    quarterName VARCHAR(2) NOT NULL,
    month INT NOT NULL,
    monthname VARCHAR(9) NOT NULL,
    day INT NOT NULL,
    weekday INT NOT NULL,
    weekdayName VARCHAR(9) NOT NULL
);

softcartDimCategory

Here is the list:

  1. categoryid
  2. categoryname
CREATE TABLE softcartDimCategory (
    categoryid INT PRIMARY KEY,
    categoryname VARCHAR(15) NOT NULL
);

softcartDimItem

ERD Tool

Using the ERD design tool to design the table softcartDimItem.

  • Right click on database name
  • Choose ERD for db
  • CLick on + ADD table
  • General Tab > Name it
  • Columns > + two columns one for each field as shown below
  • itemid as PM integer
  • itemname as varchar(15) NOT NULL

Here is the list:

  1. itemid
  2. itemname
CREATE TABLE IF NOT EXISTS softcartDimItem (
    itemid INT PRIMARY KEY,
    itemname VARCHAR(15) NOT NULL
);

softcartDimCountry

Using the ERD design tool design the table softcartDimCountry.

Here is the list:

  1. countryid
  2. countryname

CREATE TABLE softcartDimCountry (
    countryid INT PRIMARY KEY,
    countryname VARCHAR(255) NOT NULL
);

softcartFactSales

Using the ERD design tool design the table softcartFactSales.

Here is the list:

  1. orderid
  2. price
  3. itemid
  4. categoryid
  5. coutryid
  6. dateid

CREATE TABLE softcartFactSales(
        orderid VARCHAR(255) PRIMARY KEY,
        price DECIMAL(10, 2) NOT NULL,
        itemid INT NOT NULL,
        categoryid INT NOT NULL,
        countryid INT NOT NULL,
        dateid INT NOT NULL
);

Design Relationships

  • In the ERD screen > 1M - 1 to many relationship tool

Create Schema

  • We will download the schema from the ERD into an sql file named: shema.sql
  • Using the downloaded schema we’ll create a db named: staging
  1. Click on db name
  2. Choose ERD for database (these steps were already done above)
  3. This step was done already: once the ERD is open design it (since we designed it all above we just save it)

Create SQL for Schema

  1. Now that the ERD is saved and open
  2. Click on the THIRD icon in the toolbar
  3. SQL optionS > generate SQL > save as > name it schema.sql
  4. The SQL code is here below

-- This script was generated by the ERD tool in pgAdmin 4.
-- Please log an issue at https://github.com/pgadmin-org/pgadmin4/issues/new/choose if you find any bugs, including reproduction steps.
BEGIN;


CREATE TABLE IF NOT EXISTS public.softcartdimcategory
(
    categoryid integer NOT NULL,
    categoryname character varying(15) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT softcartdimcategory_pkey PRIMARY KEY (categoryid)
);

CREATE TABLE IF NOT EXISTS public.softcartdimcountry
(
    countryid integer NOT NULL,
    countryname character varying(255) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT softcartdimcountry_pkey PRIMARY KEY (countryid)
);

CREATE TABLE IF NOT EXISTS public.softcartdimdate
(
    dateid integer NOT NULL,
    date date NOT NULL,
    year integer NOT NULL,
    quarter integer NOT NULL,
    quartername character varying(2) COLLATE pg_catalog."default" NOT NULL,
    month integer NOT NULL,
    monthname character varying(9) COLLATE pg_catalog."default" NOT NULL,
    day integer NOT NULL,
    weekday integer NOT NULL,
    weekdayname character varying(9) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT softcartdimdate_pkey PRIMARY KEY (dateid)
);

CREATE TABLE IF NOT EXISTS public.softcartdimitem
(
    itemid integer NOT NULL,
    itemname character varying(15) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT softcartdimitem_pkey PRIMARY KEY (itemid)
);

CREATE TABLE IF NOT EXISTS public.softcartfactsales
(
    orderid character varying(255) COLLATE pg_catalog."default" NOT NULL,
    price numeric(10, 2) NOT NULL,
    itemid integer NOT NULL,
    categoryid integer NOT NULL,
    countryid integer NOT NULL,
    dateid integer NOT NULL,
    CONSTRAINT softcartfactsales_pkey PRIMARY KEY (orderid)
);

ALTER TABLE IF EXISTS public.softcartfactsales
    ADD FOREIGN KEY (itemid)
    REFERENCES public.softcartdimitem (itemid) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IF EXISTS public.softcartfactsales
    ADD FOREIGN KEY (categoryid)
    REFERENCES public.softcartdimcategory (categoryid) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IF EXISTS public.softcartfactsales
    ADD FOREIGN KEY (categoryid)
    REFERENCES public.softcartdimcategory (categoryid) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IF EXISTS public.softcartfactsales
    ADD FOREIGN KEY (countryid)
    REFERENCES public.softcartdimcountry (countryid) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IF EXISTS public.softcartfactsales
    ADD FOREIGN KEY (dateid)
    REFERENCES public.softcartdimdate (dateid) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;

END;

Create New DB

  • name it staging

Import Schema

  • Now that we have a new db staging we’ll import the schema we just created above
  • Choose the db from left tree pane
  • Scroll down to Schemas
  • Right click > Query Tool
  • When that opens > Click on the first icon Folder icon
  • Load the sql file as shown in image below

  • When sql loads, Run code
  • Now we have all the tables in the db