This discussion is archived
4 Replies Latest reply: Jan 20, 2013 8:23 AM by Richard Harrison . RSS

Partitioning diemnsion swith hierarchies

user5716448 Explorer
Currently Being Moderated
Hi,

We are in process of testing our data warehouse.

We have a dimesnion (product with 3 levels).

The queries are written such that refer to the lowest level of the dimesnion so woul dlike to partition on column at this level as this joined to fact (a date colum)

Range partitioning 1 column for each month.

Oracle 11.2.03.

The 2 levels higher up the hierarchy obviously don't have this information so value is null and all these rows will fall inot default maxavlue partition.

Is this the best way to deal with such a deimsnion - is there a way to create a dedictaed partition for these null values?

If so how?

Any other thoughts?

Thanks
  • 1. Re: Partitioning diemnsion swith hierarchies
    damorgan Oracle ACE Director
    Currently Being Moderated
    Two things jump out at my from your question.

    1. Range partitioning 1 column for each month.
    2. Is this the best way to deal with such a dimension

    And the answer is no. Having a column per month has not a good modeling technique for any database.

    Post your DDL and some sample queries and perhaps we can help you further.
  • 2. Re: Partitioning diemnsion swith hierarchies
    user5716448 Explorer
    Currently Being Moderated
    Hi,

    sorry for the delay - been away.

    Thanks for reply.


    I phrased it badly - want I meant was one partition for each month

    ddl for dimesnion below 3 levels highest level in hierarchy stores history the other 2 don't.

    Over 90% of queries at lowest level

    e.g select prod_num, plia_actual_on_sale_date
    from media m
    where m.dimension_key = m.plis_id

    but as reporting tool used tio query the data any combination of queries possible.

    Fact queries join into sales cube on media.dimension_key but could add media.plis_partitiong_date to join as the sales cube wish to partition in similar fashion.


    -- Create table
    create table MEDIA
    (
      dimension_key                  NUMBER not null,
      plis_id                        NUMBER,
      plis_plant_num                 NUMBER(4),
      plis_issue_num                 NUMBER(18),
      plis_branch_num                NUMBER(4),
      plis_handled_year              NUMBER(4),
      plis_handled_week              NUMBER(2),
      plis_handled_day               NUMBER(1),
      plis_barcode                   NUMBER(18),
      plis_vendor_product_ref        VARCHAR2(35),
      plis_vendor_issue_ref          VARCHAR2(35),
      plis_retail_price              NUMBER(13,2),
      plis_actual_on_sale_date       DATE,
      plis_planned_on_sale_date      DATE,
      plis_invoice_date              DATE,
      plis_recall_date               DATE,
      plis_claim_start_date          DATE,
      plis_claim_end_date            DATE,
      plis_copy_weight               NUMBER(16,3),
      plis_copy_weight_measurement   VARCHAR2(50),
      plis_bale_size                 NUMBER(16,3),
      plis_num_supplements           NUMBER(2),
      plis_keep_flag                 VARCHAR2(1),
      plis_cvrmnt_flag               VARCHAR2(1),
      plis_cvrmnt_name               VARCHAR2(40),
      plis_cvrmnt_retail_price       NUMBER(13,2),
      plis_cvrmnt_vat_type_uk        VARCHAR2(1),
      plis_cvrmnt_vat_type_eire      VARCHAR2(1),
      plis_cost_discount             NUMBER(13,6),
      plis_cost_discount_type_descr  VARCHAR2(50),
      plis_trade_discount            NUMBER(13,6),
      plis_trade_discount_type_descr VARCHAR2(50),
      plis_publisher_num             NUMBER(10),
      plis_distributor_num           NUMBER(10),
      plis_custs_in_grp              NUMBER,
      plis_custs_who_can_be_supplied NUMBER,
      plis_sois_company_num          NUMBER(4),
      plis_sois_credit_start_date    DATE,
      plis_sois_credit_end_date      DATE,
      plis_sois_sas_code             VARCHAR2(2),
      plis_sois_terms_of_sale_descr  VARCHAR2(50),
      plis_sois_recall_date          DATE,
      plis_currency_code             VARCHAR2(5),
      iss_id                         NUMBER,
      iss_num                        NUMBER(18),
      iss_parent_iss_num             NUMBER(18),
      iss_parent_prod_num            NUMBER(18),
      iss_type_descr                 VARCHAR2(50),
      iss_name                       VARCHAR2(40),
      iss_original_year              NUMBER(4),
      iss_original_week              NUMBER(2),
      iss_original_day               NUMBER(1),
      iss_ean                        NUMBER(18),
      iss_wholesaler_ref             VARCHAR2(10),
      iss_calendar_ref               VARCHAR2(20),
      iss_vat_type_uk                VARCHAR2(1),
      iss_vat_type_eire              VARCHAR2(1),
      iss_net_item_flag              VARCHAR2(1),
      iss_time_sensitive_flag        VARCHAR2(1),
      iss_customer_flag              VARCHAR2(1),
      iss_class_descr                VARCHAR2(50),
      iss_frequency_descr            VARCHAR2(15),
      iss_num_issues_in_year         NUMBER(4),
      iss_cvrmnt_flag_m              VARCHAR2(1),
      iss_cvrmnt_vat_type_uk_m       VARCHAR2(1),
      iss_cvrmnt_vat_type_eire_m     VARCHAR2(1),
      iss_cvrmnt_retail_price_m      NUMBER(13,2),
      iss_keep_flag_m                VARCHAR2(1),
      iss_barcode_m                  NUMBER(18),
      iss_terms_of_sale_descr_m      VARCHAR2(50),
      iss_retail_price_m             NUMBER(13,2),
      iss_vendor_product_ref_m       VARCHAR2(35),
      iss_claim_start_date_m         DATE,
      iss_claim_end_date_m           DATE,
      iss_recall_date_m              DATE,
      iss_invoice_date_m             DATE,
      iss_official_on_sale_date_m    DATE,
      iss_actual_on_sale_date_m      DATE,
      iss_currency_code_m            VARCHAR2(5),
      iss_segment_code               VARCHAR2(6),
      iss_segment_name               VARCHAR2(40),
      iss_segment_sub_code           VARCHAR2(6),
      iss_segment_sub_name           VARCHAR2(40),
      iss_segment_sub_sub_code       VARCHAR2(6),
      iss_segment_sub_sub_name       VARCHAR2(40),
      prod_id                        NUMBER,
      prod_num                       NUMBER(18),
      prod_type_name                 VARCHAR2(50),
      prod_name                      VARCHAR2(40),
      prod_ceased_flag               VARCHAR2(1),
      prod_rc_exception_flag         VARCHAR2(1),
      prod_family_num                NUMBER(18),
      prod_family_name               VARCHAR2(40),
      prod_launch_date               DATE,
      prod_ceased_date               DATE,
      prod_eff_date                  DATE,
      prod_exp_date                  DATE,
      plis_partitioning_date         DATE,
      iss_status                     VARCHAR2(2)
    )
    partition by range (PLIS_PARTITIONING_DATE)
    (
      partition PART_200912 values less than (TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
        tablespace BI_DW_DATA
        ,  indexes 
    create index MEDIA_PROD_NK_IDX on MEDIA (PROD_NUM, PROD_EFF_DATE);
      
    
    -- all other partitions one each month
    partition PART_MAX values less than (MAXVALUE)
        );-- Create/Recreate primary, unique and foreign key constraints 
    alter table MEDIA
      add constraint MEDIA_PK primary key (DIMENSION_KEY)
      using index 
      ;
    alter table MEDIA
      add constraint MEDIA_UK1 unique (PLIS_PLANT_NUM, PLIS_ISSUE_NUM)
      using index 
    The table is used by a third-party reporting tool such that users can create their own queries based on the tables.

    Eaxmaple of query

    select prod_num, prod_name, plis_distriburor_num, plis_pubisher_num
    from media
    where media.dimension_key =media.plis_id
    and prod_num = 600
    and plis_actual_on_sale_date between '01-JAN-2011' AND '01-JAN-2102'


    Table is a scd type 2 dimension with history stored at highest level in the hierarchy (product/prod) and the issue levels (iss and plis_ have no history).

    Vast majority of queries at the plis level.

    Table data is such that when query at plis level all columns on table populated, when query at iss level all columns apart form plis level populated and when query at prod level only prod level columns populated.

    Levlels of hierarchy in odrer are

    product(prod), issue(iss) and plant_issue(plis).

    Essentailly, whilst the plis_partitioning_date allows us to houskeep our tabel more easily it results in the table being split into several partitions.

    The question I have is do queries aganist a partitioned table which have to scan multiple partitions and when partition key not in the predicates perform significantly worse/better or just the same when compared to a non-partitioned table
    Lookking for a a general answer here based on users experiences.The partitioning key will aid housekeeping but queries in the main won't use the partitioning key and it


    Anyone give any guidance here - realise a generalisation but because a warehouse, literally hundres of possibel queries which can be written by the users and whilst want to use partitioning to aid housekeeping want to make sure it won't have a negative impact in general reporting query performance.

    Thanks
    Edited by: user5716448 on 20-Jan-2013 06:59

    Edited by: user5716448 on 20-Jan-2013 07:05
  • 3. Re: Partitioning diemnsion swith hierarchies
    rp0428 Guru
    Currently Being Moderated
    >
    I phrased it badly - want I meant was one partition for each month

    ddl for dimesnion below 3 levels highest level in hierarchy stores history the other 2 don't.

    Over 90% of queries at lowest level

    e.g select prod_num, plia_actual_on_sale_date
    from media m
    where m.dimension_key = m.plis_id

    but as reporting tool used tio query the data any combination of queries possible.

    Fact queries join into sales cube on media.dimension_key but could add media.plis_partitiong_date to join as the sales cube wish to partition in similar fashion.
    >
    Sorry but I'm still not following you. Edit your post and remove all of the unnecessary table columns, storage and index stuff so we can see what your basic table looks like.

    And then provide some sample queries that you intend to use to query this table. Maybe that will help explain what this means:
    >
    Fact queries join into sales cube on media.dimension_key but could add media.plis_partitiong_date to join as the sales cube wish to
  • 4. Re: Partitioning diemnsion swith hierarchies
    Richard Harrison . Expert
    Currently Being Moderated
    Hi,
    Not 100% sure what your data model is but have you looked at reference partitioning? This might meet your needs you can partition data based on values in other tables and partition by reference to them. Generally used for parent child relationships but may work here?

    Cheers,
    Harry

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points