1 Reply Latest reply: Aug 24, 2012 9:15 AM by DavidGreenfield RSS

    Creating metadata for AWs created programatically (11.2)

    728477
      Hi guys

      The aim is to create Analytic Workspace within certain tablespace and with certain number of physical partitions.

      I tried following ways to create the AW:

      1st way:
      begin
      dbms_aw.aw_create(name => 'MIS_LT', tbspace => 'AW_MIS_LT_DI', partnum => 15);
      end;
      /

      2nd way:
      begin
      dbms_aw.execute('AW CREATE MIS_LT PARTITIONS 15 TABLESPACE AW_MIS_LT_DI');
      end;
      /

      After executing code from either 1st way or 2nd way - I got the result that I could see that new AW (MIS_LT) when executing query:

      select * from all_aws;

      Unfortunaltely I could not see the newly created AW in AWM. I suppose it's somehow related to missing metadata (standard form metadata ?).

      When executing following code:
      begin
      dbms_cube.import_xml( to_clob(
      '<Metadata
      Version="1.3"
      MinimumDatabaseVersion="11.2.0.2">
      <AW
      Name="MIS_LT"
      Version="11.2">
      </AW>
      </Metadata>'));
      end;
      /

      PL/SQL block were executed successfully, but still MIS_LT was not visible in AWM (even after relogging):


      When trying to execute following code (like above -but one simple dimension was added):


      begin
      dbms_cube.import_xml( to_clob(
      '<Metadata
      Version="1.3"
      MinimumDatabaseVersion="11.2.0.2">
      <StandardDimension
      Name="CURRENCY_DIM_LT"
      ETViewName="V_CURRENCY_DIM_LT"
      ETKeyColumnName="DIM_KEY"
      ETLevelColumnName="LEVEL_NAME"
      ETMemberTypeColumnName="MEMBER_TYPE"
      ETDimensionOrderColumnName="DIM_ORDER"
      ETHierarchyOrderColumnName="HIER_ORDER"
      DefaultHierarchy="MAIN_HIERARCHY"
      ValueDescriptionAttribute="LONG_DESCRIPTION">
      <Hierarchy>
      <LevelHierarchy
      IsSkipLevel="False"
      IsRagged="False"
      Name="MAIN_HIERARCHY"
      ETParentColumnName="PARENT"
      ETDepthColumnName="DEPTH"
      ETViewName="V_CURRENCY_DIM_MAIN_HIERARC_LT">
      <Description
      Type="LongDescription"
      Language="ENGLISH"
      Value="MAIN HIERARCHY">
      </Description>
      <Description
      Type="ShortDescription"
      Language="ENGLISH"
      Value="MAIN HIERARCHY">
      </Description>
      <Description
      Type="Description"
      Language="ENGLISH"
      Value="MAIN HIERARCHY">
      </Description>
      <HierarchyLevel
      DimensionLevel="CURRENCY">
      <HierarchyLevelMap
      KeyExpression="MIS.MIS_CURRENCY_DIM.MCD_ID_CHAR"
      Query="MIS.MIS_CURRENCY_DIM"
      WhereClause="MIS.MIS_CURRENCY_DIM.MCD_CUNIT_ID = 2">
      <AttributeMap
      Name="LONG_DESCRIPTION"
      Attribute="LONG_DESCRIPTION"
      Expression="MIS.MIS_CURRENCY_DIM.MCD_CURRENCY_LEV_DESC">
      </AttributeMap>
      <AttributeMap
      Name="CURRENCY_LONG_DESCRIPTION"
      Attribute="CURRENCY_LONG_DESCRIPTION"
      Expression="MIS.MIS_CURRENCY_DIM.MCD_CURRENCY_LEV_DESC">
      </AttributeMap>
      </HierarchyLevelMap>
      </HierarchyLevel>
      </LevelHierarchy>
      </Hierarchy>
      <DimensionLevel
      Name="CURRENCY"
      ETKeyColumnName="CURRENCY">
      <Description
      Type="LongDescription"
      Language="ENGLISH"
      Value="CURRENCY">
      </Description>
      <Description
      Type="ShortDescription"
      Language="ENGLISH"
      Value="CURRENCY">
      </Description>
      <Description
      Type="Description"
      Language="ENGLISH"
      Value="CURRENCY">
      </Description>
      <VisibleAttribute
      Name="CURRENCY_LONG_DESCRIPTION"/>
      </DimensionLevel>
      <Attribute>
      <BaseAttribute
      SQLDataType="VARCHAR2(100)"
      AllowAutoDataTypeChange="False"
      ETAttributeColumnName="LONG_DESCRIPTION"
      IsMultiLingual="False"
      PopulateLineage="False"
      IsVisibleForAll="True"
      IsLevelUnique="False"
      Name="LONG_DESCRIPTION">
      <Classification
      Value="STAR"/>
      <Description
      Type="ShortDescription"
      Language="ENGLISH"
      Value="Long Description">
      </Description>
      <Description
      Type="LongDescription"
      Language="ENGLISH"
      Value="Long Description">
      </Description>
      <Description
      Type="Description"
      Language="ENGLISH"
      Value="Long Description">
      </Description>
      </BaseAttribute>
      </Attribute>
      <Attribute>
      <BaseAttribute
      SQLDataType="VARCHAR2(100)"
      AllowAutoDataTypeChange="False"
      ETAttributeColumnName="CURRENCY_LONG_DESCRIPTIO"
      IsMultiLingual="False"
      PopulateLineage="True"
      AttributeGroupName="LONG_DESCRIPTION"
      IsVisibleForAll="False"
      IsLevelUnique="False"
      Name="CURRENCY_LONG_DESCRIPTION">
      <Description
      Type="LongDescription"
      Language="ENGLISH"
      Value="CURRENCY Long Description">
      </Description>
      <Description
      Type="ShortDescription"
      Language="ENGLISH"
      Value="CURRENCY Long Description">
      </Description>
      <Description
      Type="Description"
      Language="ENGLISH"
      Value="CURRENCY Long Description">
      </Description>
      </BaseAttribute>
      </Attribute>
      <Organization>
      <AWPrimaryDimensionOrganization
      MVOption="NONE"
      HierarchyConsistencyRule="CONSISTENT"
      AddUniqueKeyPrefix="False"
      AW="MIS_LT">
      <MVCreationOptions
      RefreshOn="DEMAND"
      RefreshType="COMPLETE"
      UsingConstraintsClause="TRUSTED">
      </MVCreationOptions>
      <AttributeOrganization
      CreateIndex="False"
      Attribute="LONG_DESCRIPTION">
      </AttributeOrganization>
      <AttributeOrganization
      CreateIndex="False"
      Attribute="CURRENCY_LONG_DESCRIPTION">
      </AttributeOrganization>
      <DefaultBuild>
      <![CDATA[BUILD SPEC SYS_DEFAULT
      (
        LOAD,
        COMPILE
      )]]>
      </DefaultBuild>
      </AWPrimaryDimensionOrganization>
      </Organization>
      <Description
      Type="LongDescription"
      Language="ENGLISH"
      Value="CURRENCY DIM">
      </Description>
      <Description
      Type="ShortDescription"
      Language="ENGLISH"
      Value="CURRENCY DIM">
      </Description>
      <Description
      Type="Description"
      Language="ENGLISH"
      Value="CURRENCY DIM">
      </Description>
      </StandardDimension>
      <AW
      Name="MIS_LT"
      Version="11.2">
      </AW>
      </Metadata>'));
      end;
      /

      I got following errors:

      ORA-34492 Analytic Workspace object MIS_REPORTS.MIS_LT!___AW_ALL_OBJECTS doesn't exists.
      XOQ-01600 OLAP DML error when executing DML"SYS.AWXML!R11_MANANGE_DIMENSION"


      All in all it's got to be related to some missing metadata. Is there any way to create that metadata from PL/SQL wihout using AWM?

      Thanks in advance for any help.

      Best Regards
      Piotr
        • 1. Re: Creating metadata for AWs created programatically (11.2)
          DavidGreenfield
          You are correct in suspecting missing "standard form" metadata.

          The first two of you methods, "dbms_aw.aw_create" and "dbms_aw.execute" will both create an empty AW with no standard form metadata. Such an AW is fully supported and can be accessed through OLAP DML, but it is not visible in AWM and does not show up in the various USER_CUBE_* dictionary views.

          The third method, dbms_cube.import_xml, should have created a valid "standard form" AW that was visible in AWM.

          The error you got from your final method is easier to explain.

          >
          ORA-34492 Analytic Workspace object MIS_REPORTS.MIS_LT!___AW_ALL_OBJECTS doesn't exists.
          XOQ-01600 OLAP DML error when executing DML"SYS.AWXML!R11_MANANGE_DIMENSION"
          >

          The problem is that the import_xml procedure can create a new "standard form" AW or modify an existing "standard form" AW. But it will not convert an existing non-"standard form" AW into a "standard form" AW. I suspect the same thing happened in your third method, but no error was reported.

          The solution is to delete the empty AW by hand and then run your third method again.
          begin
          dbms_aw.execute('AW DELETE MIS_LT PARTITIONS');
          dbms_cube.import_xml( to_clob(
           '<Metadata
              Version="1.3"
              MinimumDatabaseVersion="11.2.0.2">
              <AW
                Name="MIS_LT"
                Version="11.2">
              </AW>
           </Metadata>'));
          end;
          /