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


The PostgreSQL installation comes with a great tool, psql, to administer and inspect the database. pgcli extends this with syntax highlighting and autocompletion.




I want my photos to have location info in them, and Nikon wants way too much money for that. So I’ll do this: Let my Apple Watch track where I go, using the Outdoor Walk, export the route as GPX and use exiftool to tag all my images. Here’s how I do that.