Results 1 to 3 of 3

Thread: Dimension table

  1. #1
    Join Date
    Jul 2005

    Unanswered: Dimension table

    Create a table time
    create table dw_time_dim(
    year number(4)
    ,month varchar2(10)
    ,month_year varchar2(10)
    ,day_of_month varchar2(10)
    ,date_key date)
    I'm creating a Time dimension for table TIME and not sure if I'm doing it right.

    Create dimension time_dim
    LEVEL Year IS dw_time_dim.YEAR
    LEVEL month IS dw_time_dim.month
    LEVEL Date_key IS dw_time_dim.date_key
    hierarchy time_rollup (
    date_key CHILD OF
    month CHILD OF
    ATTRIBUTE date_key DETERMINES(dw_time_dim.day_of_month, nth_year)
    month_year is just a concatenation of month and year.

    And when I finally execute it, i get the following message.

    SQL> ED
    Wrote file afiedt.buf

    1 Create DIMENSION time_dim
    2 LEVEL Year IS dw_time_dim.YEAR
    3 LEVEL month IS dw_time_dim.month
    4 LEVEL Date_key IS dw_time_dim.date_key
    5 hierarchy time_rollup (
    6 date_key CHILD OF
    7 month CHILD OF
    8 year)
    9* ATTRIBUTE date_key DETERMINES(dw_time_dim.day_of_month, nth_year)
    SQL> /
    Create DIMENSION time_dim
    ERROR at line 1:
    ORA-01031: insufficient privileges

    SQL> create table test(a number);

    Table created.
    But I have the privileges for create.

  2. #2
    Join Date
    Nov 2006
    your code worked for me (after fixing the missing stuff in the DETERMINES clause)

    Check your privs - I think this should work:

    select * from (select * from user_sys_privs union select * from role_sys_privs)
    where privilege like '%DIME%';

  3. #3
    Join Date
    Jul 2005
    I dont have privileges to create Dimension and I asked my DBA for that.

    your code worked for me (after fixing the missing stuff in the DETERMINES clause)
    What is the missing stuff?

Posting Permissions

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