0 Replies Latest reply: Aug 9, 2012 10:47 AM by 364677 RSS

    Creating dimension

    364677
      create dimension location_DIM
      LEVEL REPORT IS (ACTIVITY_COUNTS.REPORT_ID)
      LEVEL POST_CODE IS (ACTIVITY_COUNTS.POST_CD)
      level district IS (ACTIVITY_COUNTS.DISTRICT_CD)
      LEVEL county IS (ACTIVITY_COUNTS.COUNTY_CD)
      LEVEL CITY_TOWNSHIP IS (ACTIVITY_COUNTS.city_TWNSHIP_NUM)
      HIERARCHY REP_ROLLUP
      (REPORT CHILD OF
      POST_CODE CHILD OF
      DISTRICT CHILD OF
      COUNTY CHILD OF
      CITY_TOWNSHIP
      )
      ATTRIBUTE POST_CODE DETERMINES (activity_counts.POST_DESC)
      ATTRIBUTE DISRICT DETERMINES (activity_counts.DISTRICT_desc)
      ATTRIBUTE COUNTY DETERMINES (activity_counts.COUNTY_desc)
      ATTRIBUTE CITY_TOWNSHIP DETERMINES (activity_counts.CITY_TWNSHIP_DESC);

      I followed this example.

      CREATE DIMENSION products_dim
      LEVEL product IS (products.prod_id)
      LEVEL subcategory IS (products.prod_subcategory)
      LEVEL category IS (products.prod_category)
      HIERARCHY prod_rollup (
      product CHILD OF
      subcategory CHILD OF
      category
      )
      ATTRIBUTE product DETERMINES
      (products.prod_name, products.prod_desc,
      prod_weight_class, prod_unit_of_measure,
      prod_pack_size,prod_status, prod_list_price, prod_min_price)
      ATTRIBUTE subcategory DETERMINES
      (prod_subcategory, prod_subcat_desc)
      ATTRIBUTE category DETERMINES
      (prod_category, prod_cat_desc);

      But I get this error
      Error at Command Line:15 Column:12
      Error report:
      SQL Error: ORA-30342: referenced level is not defined in this dimension
      30342. 00000 - "referenced level is not defined in this dimension"
      *Cause:    A reference to a level not defined within the dimension was found.
      ------------------------------Even this one is giving me reference error. The two tables do not have referential integrity constraints

      create dimension location
      LEVEL REPORT IS (ACTIVITY_COUNTS.REPORT_ID)
      LEVEL POST_CODE IS (DISTRICT_COUNTY_POST_CITY.POST_CODE)
      level district_code IS (DISTRICT_COUNTY_POST_CITY.DISTRICT_CODE)
      LEVEL county_nbr IS (DISTRICT_COUNTY_POST_CITY.COUNTY_NBR)
      LEVEL CITY_TOWNSHIP IS (DISTRICT_COUNTY_POST_CITY.city_township_nbr)
      HIERARCHY GEOG_ROLLUP
      (report CHILD OF
      post_CODE CHILD OF
      DISTRICT_CODE CHILD OF
      COUNTY_NBR CHILD OF
      CITY_TOWNSHIP
      JOIN KEY (activity_counts.CITY_TWNSHIP_NUM) REFERENCES CITY_TOWNSHIP)
      ATTRIBUTE POST_CODE DETERMINES (DISTRICT_COUNTY_POST_CITY.POST_DESC)
      ATTRIBUTE DISRICT_CODE DETERMINES (DISTRICT_COUNTY_POST_CITY.DISTRICT_NAME)
      ATTRIBUTE COUNTY_NBR DETERMINES (DISTRICT_COUNTY_POST_CITY.COUNTY_NAME)
      ATTRIBUTE CITY_TOWNSHIP DETERMINES (DISTRICT_COUNTY_POST_CITY.CITY_TOWNSHIP_NAME);


      tried to create this but not getting through got stuck in reference key

      CREATE DIMENSION customers_dim
      LEVEL customer IS (customers.cust_id)
      LEVEL city IS (customers.cust_city)
      LEVEL state IS (customers.cust_state_province)
      LEVEL country IS (countries.country_id)
      LEVEL subregion IS (countries.country_subregion)
      LEVEL region IS (countries.country_region)
      HIERARCHY geog_rollup (
      customer CHILD OF
      city CHILD OF
      state CHILD OF
      country CHILD OF
      subregion CHILD OF
      region
      JOIN KEY (customers.country_id) REFERENCES country
      )
      ATTRIBUTE customer DETERMINES
      (cust_first_name, cust_last_name, cust_gender,
      cust_marital_status, cust_year_of_birth,
      cust_income_level, cust_credit_limit)
      ATTRIBUTE country DETERMINES (countries.country_name)
      ;