Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2010
    Posts
    3

    Creating a FACT table

    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:
    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:
    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

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    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.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jan 2010
    Posts
    3
    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

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Quote Originally Posted by fawkman99 View Post
    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.
    Well, I guess you cannot. You will have to find a way to execute ALTER statements.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •