Creating a FACT table

fawkman99 · #1 (**permalink**) 01-05-10, 14:02

Hi Guys

I am doing an assignment to create a data warehouse. This is the first time I have had to create one using Oracle, I apologise if some of my questions seem stupid. But I am hoping I can learn a lot from this.

I was given two flat files as spreadsheets and I imported them into Oracle and when doing so created two tmp tables. The column names for boths spreadsheets/ tmp_tables are id, location,2002_03,2004_05,2005_06,flag and crime_type.

I have then moved the location types into three different dimension tables, region_dim, county_dim and town_dim. I also have a crime_dim which is a table that holds crime_type and a date_dim table that holds the years from the tmp_tables. I inserted these with an INSERT statement and NOT a SELECT AS like the other ones. I now need to create my FACT table which is where I have an issue. The statement I am trying to run:

Quote:

Create table police_fact(
date_id NUMBER (2),
crime_id NUMBER (2),
region_id NUMBER (2),
county_id NUMBER (2),
town_id NUMBER (2),
total_offences NUMBER (10),
CONSTRAINT fk_date
FOREIGN KEY(date_id)
REFERENCES date_dim(date_id),
CONSTRAINT fk_crime
FOREIGN KEY(crime_id)
REFERENCES crime_dim(crime_id),
CONSTRAINT fk_region
FOREIGN KEY(region_id)
REFERENCES region_dim(region_id),
CONSTRAINT fk_county
FOREIGN KEY(county_id)
REFERENCES county_dim(county_id),
CONSTRAINT fk_town
FOREIGN KEY(town_id)
REFERENCES town_dim(town_id));

When I try and run this i get an error:

Quote:

ORA-02270: no matching unique or primary key for this column-list

In all the examples I have seen of a FACT table, i haven't seen them with a PK. Can FACT tables have PRIMARY KEYS?? If so is it a good idea to creat a fact_id on this table. I am interested to know the best way of doing this or what anybodies thoughts are.

THe other thing is, from the flat file, which is now the tmp_tables, there is data inside them, so far I have only created the columns in the dimension tables, I havent done anything with the data. Where do I put this, where is it stored or how do I move it? As much as I read about creating a DW and Star Schema, what to do with the actual data and how to do it is the most confusing thing. If anyone can help and advise me I would be most grateful. I really appreciate your time, sorry for such a long post.

kind regards

n_i · #2 (**permalink**) 01-05-10, 16:07

It actually complains about referenced (dimension) tables, which must have unique constraints or PKs defined for parent attributes.

There is no reason why you cannot have a PK for the fact table either. After all, facts are supposed to be uniquely identifiable. As to whether create a surrogate key or use a natural key, please refer to the numerous holy wars on the subject.

To answer your last question, you would need to write a statement or an SP that reads temporary tables, cross-references dimensions, and inserts appropriates values into the fact table.

fawkman99 · #3 (**permalink**) 01-05-10, 20:57

Hi Nick

Thanks for your response,

Your right, of course, I have no PK's. I have created my tables using this code:

Code:

create table region_dim AS(
select region_sequence.nextval as region_id, region as LOCATION from drug_offences_temp where flag = 'R');

Where can I define the PRIMARY KEY constraint in this statement?? I am using Oracle APEX and this doesn't like the ALTER statement quite annoyingly.

Hopefully once I have these PK's in place I can create the Fact table with a fact_id PK. The statement / SP (what do you mean by SP) to read the temp tables and cross reference dimensions sounds very tricky, i can see this challenging me greatly, but thanks for the advice. If you could explain how I can add a PK using the statement I am using above that would be great. Thanks again