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


I recently created a wonderful bug.




This is a basic example how to implement oAuth2 using Akka HTTP and Scala. It provides three endpoints. From the clients point of view:

  • / — publicly accessible, returns “Welcome!”,
  • /auth — provide your username and password, receive an access_token in return,
  • /api — secured by oAuth, send the access_token in a header to gain access.

From the server’s point of view:

  • / — publicly accessible, do nothing,
  • /auth — receive basic auth credentials, verify they’re in the list of known credentials, create an access_token, return it,
  • /api — receive authorization header, check if access_token is in list of valid tokens.

Since oAuth tokens are short lived, the server also has to invalidate expired tokens.




Getting a Akka HTTP-based backend up and running on Heroku for free can be done in less then 30 minutes — if you know the tricks.