Home » Developer & Programmer » Data Integration » How to load time_dimension (OWB 11g,Oracle 11g)
How to load time_dimension [message #408714] Wed, 17 June 2009 06:51 Go to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Hi,

I am very new to OWB(Datawarehousing) basically, I have some doubts in creating and loading the dimensions.

I was referring to the following website to learn
http://philip.greenspun.com/sql/data-warehousing.html

I created the following tables,

DDL & DML


create table product_categories (
	product_category_id	integer primary key,
	product_category_name	varchar(100) not null
);

create table manufacturers (
	manufacturer_id		integer primary key,
	manufacturer_name	varchar(100) not null
);

create table products (
	product_id		integer primary key,
	product_name		varchar(100) not null,
	product_category_id	references product_categories,
	manufacturer_id		references manufacturers
);

create table cities (
	city_id			integer primary key,
	city_name		varchar(100) not null,
	state			varchar(100) not null,
	population		integer not null
);

create table stores (
	store_id		integer primary key,
	city_id			references cities,
	store_location		varchar(200) not null,
	phone_number		varchar(20)	
);

create table sales (
	product_id	not null references products,
	store_id	not null references stores,
	quantity_sold	integer not null,
	-- the Oracle "date" type is precise to the second
	-- unlike the ANSI date datatype
	date_time_of_sale	date not null
);

-- put some data in 

insert into product_categories values (1, 'toothpaste');
insert into product_categories values (2, 'soda');

insert into manufacturers values (68, 'Colgate');
insert into manufacturers values (5, 'Coca Cola');

insert into products values (567, 'Colgate Gel Pump 6.4 oz.', 1, 68);
insert into products values (219, 'Diet Coke 12 oz. can', 2, 5);

insert into cities values (34, 'San Francisco', 'California', 700000);
insert into cities values (58, 'East Fishkill', 'New York', 30000);

insert into stores values (16, 34, '510 Main Street', '415-555-1212');
insert into stores values (17, 58, '13 Maple Avenue', '914-555-1212');

insert into sales values (567, 17, 1, to_date('1997-10-22 09:35:14', 'YYYY-MM-DD HH24:MI:SS'));
insert into sales values (219, 16, 4, to_date('1997-10-22 09:35:14', 'YYYY-MM-DD HH24:MI:SS'));
insert into sales values (219, 17, 1, to_date('1997-10-22 09:35:17', 'YYYY-MM-DD HH24:MI:SS'));

-- keep track of which dates are holidays
-- the presence of a date (all dates will be truncated to midnight)
-- in this table indicates that it is a holiday
create table holiday_map (
holiday_date		date primary key
);

-- where the prices are kept
create table product_prices (
product_id	not null references products,
from_date	date not null,
price		number not null
);

insert into product_prices values (567,'1997-01-01',2.75);
insert into product_prices values (219,'1997-01-01',0.40);


Created the Dimensions as below,

Time_Dimension
create table time_dimension (
	time_key		integer primary key,
	-- just to make it a little easier to work with; this is 
	-- midnight (TRUNC) of the date in question
	oracle_date		date not null,
	day_of_week		varchar(9) not null, -- 'Monday', 'Tuesday'...
	day_number_in_month	integer not null, -- 1 to 31
	day_number_overall	integer not null, -- days from the epoch (first day is 1)
	week_number_in_year	integer not null, -- 1 to 52
	week_number_overall	integer not null, -- weeks start on Sunday
	month			integer not null, -- 1 to 12
	month_number_overall	integer not null,
	quarter			integer not null, -- 1 to 4
	fiscal_period		varchar(10),
	holiday_flag		char(1) default 'f' check (holiday_flag in ('t', 'f')),
	weekday_flag		char(1) default 'f' check (weekday_flag in ('t', 'f')),
	season			varchar(50),
	event			varchar(50)
);


Stores_Dimension
create table stores_dimension (
	stores_key		integer primary key,
	name			varchar(100),
	city			varchar(100),
	county			varchar(100),
	state			varchar(100),
	zip_code		varchar(100),
	date_opened		date,
	date_remodeled		date,
	-- 'small', 'medium', 'large', or 'super'
	store_size		varchar(100),
	...
);


Also created Product_Dimension ( with product and manufacturer information)

Fact Table
create table sales_fact (
	time_key	integer not null references time_dimension,
	product_id	integer,
	store_id	integer,
	unit_sales	integer,
	dollar_sales	number
);


Please let me know whether the design of Dimension and Fact is correct, And also please let me know how to populate/load the Time_Dimension

Regards,
Ashoka BL
devanampriya@gmail.com
Re: How to load time_dimension [message #415162 is a reply to message #408714] Sat, 25 July 2009 08:50 Go to previous message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
On your design, i feel you are the only person can decide if it works with the data you are dealing with. It looks like a typical star-schema to me


On populating your time dimension table, its simple, you can write a simple pl/sql script to mass populate it.

What we do currently is, we have written a script to mass populate to time_dimension table for the next 20years. You may want to consider this approach
Previous Topic: mapping
Next Topic: owb study guide for frshers
Goto Forum:
  


Current Time: Fri Mar 29 09:16:46 CDT 2024