1. Creating and loading the dimension tables:
Create the STOCK and MANAGER dimension tables:
The STOCK and MANAGER dimension tables can be loaded from the STOCK and MANAGER tables using the following format of the INSERT command:
INSERT INTO table-name [(column-names)]
select-statement.
The script file period-dim.sql
contains a CREATE command and inserts for the PERIOD dimension table.
2. Creating and loading the PURCHASES fact table:
Run the script file lab12.sql
to add some additional rows to the HOLDING table.
Create the PURCHASES fact table; the primary key should be PERIOD CODE, STOCK SYMBOL and MANAGER NUMBER. Create foreign keys on these columns, referencing the corresponding dimension table.
Except for the period code, the data for this table can be obtained directly from the HOLDING and PORTFOLIO tables.
The period code can be generated from the PURCHASEDATE column. Run the script file fact-temp.sql to create and load a temporary table called fact_temp which contains the purchase date as an 8-character code in the format YYYYMMDD.
The purchases fact table can now be loaded from fact_temp using a group
by clause (group by period code, stock symbol, manager) to roll up the total
number of shares and the average purchase price values.
3. Indexing the fact table:
Create bitmap indexes on the PURCHASES fact table on the three dimension
columns.