Extracting Dimensions from an Oracle Database Table

by Jannik Arndt

Task

You have a denormalized table and want to extract a column into a dimension table.

Caveat

You have to keep the ids.

Extra-Caveat

You use an Oracle database.

Example

CARS

id manufacturer_id model_id color
1 1 1 ‘blue’
2 1 4 ‘red’
3 2 6 ‘black’
4 2 8 ‘red’

becomes

CARS

id manufacturer_id model_id color_id
1 1 1 1
2 1 4 2
3 2 6 3
4 2 8 2

COLORS

id name
1 ‘blue’
2 ‘red’
3 ‘black’

Solution:

-- Create new table from Select-Statement
CREATE TABLE colors AS
  SELECT ROWNUM as id, name FROM
    (SELECT color FROM cars GROUP BY color);

-- Add constraints on newly create table
CREATE UNIQUE INDEX colors_id_uindex ON colors(id);
ALTER TABLE colors ADD CONSTRAINT colors_id_pk PRIMARY KEY (id);

-- Add reference from fact-table to new dimension
ALTER TABLE cars ADD color_id NUMBER DEFAULT NULL NULL;
UPDATE cars SET color_id = (SELECT id FROM colors WHERE colors.id = cars.color);

-- Delete original column
ALTER TABLE cars DROP COLUMN color;

Blog


This example show how to write a reactive reader for the AWS Simple Queue Service, using Scala and alpakka (respective akka streams).




While the Akka documentation is incredibly well written, it has surprisingly few images. Since I visualize concepts to remember them, here is my take on how Event Sourcing in Akka Persistence works:




Changing the password for a PostgreSQL database user involves two steps: The change in the database and the change in the application code. This blog post describes how to do this without any downtime or failed authentication tries.