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


Apples Time Machine is a great backup solution, you only have to do one thing: Connect your disk from time to time.

Since that is way harder than it sounds, there’s a second option: Buy a 329€ Time Capsule and do backups over wifi!

That’s too expensive? Here’s how to build your own Time Capsule with a Raspberry Pi 3 and an external hard drive.




Sonoff takes the standard ESP8266 chip and adds two things:

  • a casing with wifi and great connectors and
  • a custom OS and an app to control the devices.

Unfortunately, the second addition also means that all communication goes through Sonoff’s servers. Here’s how to get rid of that. Without soldering or connecting anything.




“Hey Siri, turn on the bedroom lights!” I want that. Here’s how I did it:

  • I bought a bunch of Sonoff devices (5€ each, 10€ for a light switch).
  • I bought a raspberry pi (33€).
  • I installed an MQTT broker and homebridge on the pi.