Rem
Rem $Header: oneseed.sql 16-jan-01.15:35:00 dthompso Exp $
Rem
Rem oneseed.sql
Rem
Rem  Copyright (c) Oracle Corporation 2000, 2001. All Rights Reserved.
Rem
Rem    NAME
Rem      oneseed.sql
Rem
Rem    DESCRIPTION
Rem      Populates tables with default seed data.
Rem
Rem    NOTES
Rem      
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem       dthompso 01/16/01 - add time attribute descriptors to dimension attri
Rem    
Rem    dthompso    06/26/00 - Attribute Descriptor type applies to dimension attribute.
Rem    dthompso    05/24/00 - Finalized seeded descriptors.
Rem    dthompso    05/15/00 - Updated descriptors
Rem    dthompso    05/11/00 - Update seed descriptors
Rem    dthompso    05/08/00 - Correct classificationentry population
Rem    dthompso    04/27/00 - Initial Version
Rem    dthompso    01/00/00 - Created
Rem

declare
  project_id number;
  type_id number;
  model_id number;
  desc_id number;
  dv_id number;
  dv_dim_id number;
  dv_fact_id number;
  
  function get_id return number is
    id number;
  begin
    SELECT olap_id_seq.nextval INTO id FROM dual;
    return id;
  end;
 
  -- create an entry in cwm$domain representing a column datatype
  -- uses package variable model_id to reference model stored in
  procedure add_cwm$domain(typename varchar2) is
    domain_id number;
  begin
    domain_id := get_id;
    insert into cwm$domain
    (irid
    , definitionmodel_irid
    , item_irid
    , name)
    values 
    (domain_id
    , model_id
    , model_id
    , typename);
  end;
 
  -- create an entry in cwm$function representing a function with a value parameter
  -- and optionally with a 2nd named parameter
  -- optional 3rd parameter has the id of the descriptor for indication of fact/dim table usage
  -- uses package variable model_id to reference model stored in  
  procedure add_cwm$function(function_name varchar2, param2_name varchar2 := null, desc_id number := null) is
    function_id number;
    param_id number;
    entry_id number;
  begin
    -- create function
    function_id := get_id;
    insert into cwm$function
    ( irid
    , processmodel_irid
    , name)
    values
    ( function_id
    , model_id
    , function_name);

     -- if a 2nd parameter specified then create it
    if param2_name is not null then
      param_id := get_id;
      insert into cwm$parameter
      ( irid
      , operation_irid
      , shortdescription
      , input_fk_seq
      , name)
      values
      ( param_id
      , function_id
      , 'IN'
      , 1
      , param2_name);
    end if;
    -- if a descriptor is specified
    if desc_id is not null then
      entry_id := get_id;
      INSERT INTO cwm$classificationentry
      ( irid
      , name
      , classification_irid
      , element_irid
      , object_owner
      , object_name)
      VALUES 
      ( entry_id
      , 'PARAMETER'
      , desc_id
      , param_id
      , function_name
      , param2_name);
    end if;
  end;
  
  
  function add_descriptor(desc_type varchar2
                         , entity_type varchar2
                         , desc_description varchar2 := null) return number is
    clty_id number;
    obj_id number;
  begin
    -- get cwm$classificationtype id, creating if it does not exist
    begin
      SELECT irid INTO clty_id 
      FROM cwm$classificationtype
      WHERE name = desc_type;
    exception
      when no_data_found then
        clty_id := get_id;
        INSERT INTO cwm$classificationtype
        ( irid
        , project_irid
        , name
        , description)
        VALUES
        ( clty_id
        , project_id
        , desc_type
        , desc_description);
    end;
    
    -- create cwm$objecttype entry if does not exist
    begin
      SELECT irid INTO obj_id FROM cwm$objecttype
      WHERE name = entity_type AND classificationtype_irid = clty_id;
    exception
      when no_data_found then
        obj_id := get_id;
        INSERT INTO cwm$objecttype
        ( irid
        , project_irid
        , classificationtype_irid
        , name)
        VALUES
        ( obj_id
        , project_id
        , clty_id
        , entity_type);
    end;
    
    return clty_id;

  end;
  
  function add_descriptor_value(desc_id number
                               , desc_value varchar2
                               , value_description  varchar2 := null) return number is
    cl_id number;
  begin
  
    -- create entry in cwm$classification for value
    cl_id := get_id;
    INSERT INTO cwm$classification
    ( irid
    , project_irid
    , classificationtype_irid
    , name
    , description)
    VALUES
    ( cl_id
    , project_id
    , desc_id
    , desc_value
    , value_description);
    return cl_id;
  end;

  
begin
  -- projects and catalog type
  project_id := get_id;
  insert into cwm$project(irid, name)
  values(project_id, 'OLAP');
  type_id := get_id;
  insert into cwm$classificationtype(irid, project_irid, name)
  values(type_id, project_id, 'ORACLE_OLAP_CATALOG');
  project_id := get_id;
  insert into cwm$project(irid, name)
  values(project_id, 'OLAP_DOMAINS');
  model_id := get_id;
  insert into cwm$model(irid, project_irid, physicalname)
  values(model_id, project_id, 'OLAP_COLUMN_DATATYPES');
  
  -- populate cwm$domain with possible oracle datatypes
  add_cwm$domain('VARCHAR2');
  add_cwm$domain('NVARCHAR2');
  add_cwm$domain('NUMBER');
  add_cwm$domain('FLOAT');
  add_cwm$domain('LONG');
  add_cwm$domain('NCHAR VARYING');
  add_cwm$domain('VARCHAR');
  add_cwm$domain('DATE');
  add_cwm$domain('RAW');
  add_cwm$domain('LONG RAW');
  add_cwm$domain('ROWID');
  add_cwm$domain('NCHAR');
  add_cwm$domain('CHAR');
  add_cwm$domain('MLSLABEL');
  add_cwm$domain('NCLOB');
  add_cwm$domain('CLOB');
  add_cwm$domain('BLOB');
  add_cwm$domain('BFILE');
  add_cwm$domain('CFILE');
  add_cwm$domain('TIME');
  add_cwm$domain('TIMESTAMP');
  
  
  -- populate descriptors and types in cwm$classification and cwm$classificationtype
  desc_id := add_descriptor('Dimension Type'
    , 'DIMENSION'
    , 'Type of Dimension');
  dv_id := add_descriptor_value(desc_id, 'Time');
  
  desc_id := add_descriptor('Dimension Primary Display Sort Order'
    , 'DIMENSION' 
    , 'Primary order the values of a dimension are displayed');
  dv_id := add_descriptor_value(desc_id, 'Hierarchical');  
  dv_id := add_descriptor_value(desc_id, 'Level');

  desc_id := add_descriptor('Dimension Secondary Display Sort Order'
    , 'DIMENSION'
    , 'Secondary order (within primary) the values of a dimension are displayed');
  dv_id := add_descriptor_value(desc_id, 'Ascending');  
  dv_id := add_descriptor_value(desc_id, 'Descending');
  
  desc_id := add_descriptor('Dimensional Attribute Descriptor'
    , 'LEVEL ATTRIBUTE'
    , 'Descriptive text for level attributes');
  desc_id := add_descriptor('Dimensional Attribute Descriptor'
    , 'DIMENSION ATTRIBUTE'
    , 'Descriptive text for dimension attributes');  
  dv_id := add_descriptor_value(desc_id, 'Description');
  dv_id := add_descriptor_value(desc_id, 'Long Description');
  dv_id := add_descriptor_value(desc_id, 'Short Description');
  
  desc_id := add_descriptor('Total Level'
    , 'LEVEL'
    , 'Identifies the top level in a hierarchy. Only used when there is one member that represents the sum of all dimension members');
  dv_id := add_descriptor_value(desc_id, 'Total');
  
  desc_id := add_descriptor('Time Dimension Level Type', 'LEVEL'
                          , 'Identifies the levels of a Time dimension');
  dv_id := add_descriptor_value(desc_id, 'Day');
  dv_id := add_descriptor_value(desc_id, 'Month');
  dv_id := add_descriptor_value(desc_id, 'Quarter');
  dv_id := add_descriptor_value(desc_id, 'Year');
  
  desc_id := add_descriptor('Time Dimension Attribute Type'
    , 'LEVEL ATTRIBUTE'
    , 'Identifies attributes specific to a time dimension');
  desc_id := add_descriptor('Time Dimension Attribute Type'
    , 'DIMENSION ATTRIBUTE'
    , 'Identifies attributes specific to a time dimension');  
  dv_id := add_descriptor_value(desc_id, 'End Date');
  dv_id := add_descriptor_value(desc_id, 'Time Span'); 
  dv_id := add_descriptor_value(desc_id, 'Prior Period');
  dv_id := add_descriptor_value(desc_id, 'Year Ago Period');


  -- populate descriptors and types in cwm$classification and cwm$classificationtype for parameter type
  desc_id := add_descriptor('Parameter Source Type', 'PARAMETER', 'Type of Table the parameter references a column in');
  dv_dim_id := add_descriptor_value(desc_id, 'Dimension Table');
  dv_fact_id := add_descriptor_value(desc_id, 'Fact Table');

  -- populate cwm$function and cwm$parameter with possible functions and arguments
  model_id := get_id;
  insert into cwm$model(irid, project_irid, physicalname)
  values(model_id, project_id, 'OLAP_FUNCTIONS');  
  add_cwm$function('COUNT');
  add_cwm$function('AVERAGE');
  add_cwm$function('FIRST', 'ORDER ATTRIBUTE', dv_dim_id);
  add_cwm$function('LAST', 'ORDER ATTRIBUTE', dv_dim_id);
  add_cwm$function('MIN');
  add_cwm$function('MAX');
  add_cwm$function('WEIGHTED AVERAGE', 'WEIGHT ATTRIBUTE', dv_fact_id);
  add_cwm$function('WEIGHTED SUM', 'WEIGHT ATTRIBUTE', dv_fact_id);
  add_cwm$function('SUM');
  add_cwm$function('AND');
  add_cwm$function('OR');
  add_cwm$function('HIERARCHICAL AVERAGE');
  add_cwm$function('SCALED SUM', 'WEIGHT ATTRIBUTE', dv_fact_id);
  
  commit;
end;
/