One of the most interesting new features promised with Oracle Warehouse Builder "Paris" is better support for the creation and maintenance of dimensions. If you're an existing OWB user, you'll know that support for dimensions is pretty limited - you can't create snowflake or parent-child dimensions, you can only create MOLAP dimensions by first creating a relational implementation and then exporting it through the OLAP bridge, there's no support for skip-level or ragged hierarchies, it doesn't by default create the CWM1 metadata required for BI Beans or Discoverer for OLAP use, and you can't import in definitions of existing dimensions. What this means is that you generally use OWB to create the first cut of a dimension, then use tools such as Analytic Workspace Manager or Oracle Enterprise Manager to finish off the job. I was particularly interested then to take a look at the new dimensional modelling features that come with "Paris".
Logging on to the Design Center and navigating to the target schema, as with earlier versions of OWB there's a "Dimensions" node which you can right-click on to create a dimension. Once you name this dimension (in our case, "Customer") you are asked to choose between a relation ("ROLAP") or multidimensional ("MOLAP") implementation. In my case, I wanted a relational implementation and so went for the default option.
When I first saw this I was a bit concerned, as it seemed as if you made the choice here, and you had to stick with this for ever onwards; however, you'll see later that once you've created the dimension using this wizard, you can then edit the dimension and change from ROLAP to MOLAP and back again. Also - note the lack of any HOLAP (hybrid OLAP) option - although technically you can create a HOLAP cube using Oracle OLAP (storing your detail level data in relational tables, your summary data in an analytic workspace and using the DBMS_ADVANCED_REWRITE package in 10g to direct summary level SQL queries to the AW) it's not a particularly elegant solution and I've not seen it used in practice. Maybe a later version of Oracle will properly support HOLAP and it'll then appear as an option in OWB.
Anyway, once you've chosen the storage type for your dimension, you then go on to create the dimension attributes. This is quite a bit different to how dimensions are created in current versions of OWB.
What you're doing here is creating attributes that apply to the dimension as a whole, and can be applied to individual levels. At the simplest level, these are the ID (the surrogate key for each of the levels), the Name (usually the business key from the source system) and the Description (the value that will be used as the dimension member name in tools such as Discoverer for OLAP). This way of setting up the dimension is the same as Analytic Workspace Manager 10g, where you define attributes at a dimension level and them implement them for specific levels.
I guess the reason that the tool takes this new approach is because you're now creating OLAP metadata, as opposed to the old data dictionary DIMENSION definitions, and the OLAP API expects to have dimensions defined in this way, regardless of whether you implement relationally or multidimensionally. Once you get the hang of it it's quite intuitive, it just takes a few moments to adjust to the new way of doing things.
Once you create the dimension attributes, you then create the levels in the default hierarchy, which for us was called "Shipments" (I used the GLOBAL schema that comes with the AMW10g demo, available for download from OTN), and then you can select which of the dimension attributes applies to each level.
Once you've created the levels and selected attributes, if you've previously selected "ROLAP" as the storage type for your dimension, you get presented with the Data Policy dialog.
This allows you to specify whether your dimension is a type 1, type 2 or type 3 slowly changing dimension, and is used later by "Paris" when generating code to perform your dimension updates. Anybody who's used earlier versions of OWB will know how much work was involved in implementing slowly changing dimensions and the promise of automatic support for SCDs is quite exciting. I'll take a look at this feature in more depth at a later date.
Once you've made the choice around SCD type and confirmed the wizard settings, "Paris" then creates the entries in the design repository to implement the dimension.
At this point, the wizard has created a ROLAP dimension for you with a single, default hierarchy. You can then generate the code to implement the dimension, and what comes out for the dimension is actually a set of PL/SQL calls to the CWM2 API, which creates entries in the OLAP Catalog for your dimension - note that this is different to OWB, which creates a CREATE DIMENSION statement instead. From what I can see, "Paris" doesn't create the CREATE DIMENSION statement at all, instead just creating an OLAP Dimension that works with OLAP API applications such as BI Beans and Discoverer for OLAP. Here's the code for my Customer dimension:
/**********************************************************************
-- Product : Oracle Warehouse Builder
-- Generator Version : 10.0.1.1.32
-- Created Date : Wed Apr 13 12:50:40 BST 2005
-- Modified Date : Wed Apr 13 12:50:40 BST 2005
-- Created By : owb_paris
-- Modified By : owb_paris
-- Generated Object Type : OLAP DIMENSION
-- Generated Object Name : CUSTOMER
-- Comments :
-- © 2003 Oracle Corporation. All Rights Reserved.
**********************************************************************/
WHENEVER SQLERROR EXIT FAILURE;
BEGIN
CWM2_OLAP_DIMENSION.CREATE_DIMENSION('null', 'CUSTOMER', 'CUSTOMER', 'CUSTOMER', 'CUSTOMER', 'CUSTOMER');
CWM2_OLAP_DIMENSION_ATTRIBUTE.CREATE_DIMENSION_ATTRIBUTE('null','CUSTOMER', 'DESCRIPTION', 'DESCRIPTION', 'DESCRIPTION', 'DESCRIPTION','LONG DESCRIPTION');
CWM2_OLAP_DIMENSION_ATTRIBUTE.CREATE_DIMENSION_ATTRIBUTE('null','CUSTOMER', 'NAME', 'NAME', 'NAME', 'NAME','SHORT DESCRIPTION');
CWM2_OLAP_DIMENSION_ATTRIBUTE.CREATE_DIMENSION_ATTRIBUTE('null','CUSTOMER', 'ID', 'ID', 'ID', 'ID');
CWM2_OLAP_LEVEL.CREATE_LEVEL('null', 'CUSTOMER', 'REGION', 'REGION', 'REGION', 'REGION', 'REGION');
CWM2_OLAP_LEVEL_ATTRIBUTE.CREATE_LEVEL_ATTRIBUTE('null', 'CUSTOMER', 'NAME', 'REGION', 'NAME', 'NAME', 'NAME', 'NAME');
CWM2_OLAP_LEVEL_ATTRIBUTE.CREATE_LEVEL_ATTRIBUTE('null', 'CUSTOMER', 'DESCRIPTION', 'REGION', 'DESCRIPTION', 'DESCRIPTION', 'DESCRIPTION', 'DESCRIPTION');
CWM2_OLAP_LEVEL_ATTRIBUTE.CREATE_LEVEL_ATTRIBUTE('null', 'CUSTOMER', 'ID', 'REGION', 'ID', 'ID', 'ID', 'ID');
CWM2_OLAP_LEVEL.CREATE_LEVEL('null', 'CUSTOMER', 'SHIP_TO', 'SHIP_TO', 'SHIP_TO', 'SHIP_TO', 'SHIP_TO');
CWM2_OLAP_LEVEL_ATTRIBUTE.CREATE_LEVEL_ATTRIBUTE('null', 'CUSTOMER', 'NAME', 'SHIP_TO', 'NAME', 'NAME', 'NAME', 'NAME');
CWM2_OLAP_LEVEL_ATTRIBUTE.CREATE_LEVEL_ATTRIBUTE('null', 'CUSTOMER', 'ID', 'SHIP_TO', 'ID', 'ID', 'ID', 'ID');
CWM2_OLAP_LEVEL_ATTRIBUTE.CREATE_LEVEL_ATTRIBUTE('null', 'CUSTOMER', 'DESCRIPTION', 'SHIP_TO', 'DESCRIPTION', 'DESCRIPTION', 'DESCRIPTION', 'DESCRIPTION');
CWM2_OLAP_LEVEL.CREATE_LEVEL('null', 'CUSTOMER', 'TOTAL_CUSTOMER', 'TOTAL_CUSTOMER', 'TOTAL_CUSTOMER', 'TOTAL_CUSTOMER', 'TOTAL_CUSTOMER');
CWM2_OLAP_LEVEL_ATTRIBUTE.CREATE_LEVEL_ATTRIBUTE('null', 'CUSTOMER', 'DESCRIPTION', 'TOTAL_CUSTOMER', 'DESCRIPTION', 'DESCRIPTION', 'DESCRIPTION', 'DESCRIPTION');
CWM2_OLAP_LEVEL_ATTRIBUTE.CREATE_LEVEL_ATTRIBUTE('null', 'CUSTOMER', 'NAME', 'TOTAL_CUSTOMER', 'NAME', 'NAME', 'NAME', 'NAME');
CWM2_OLAP_LEVEL_ATTRIBUTE.CREATE_LEVEL_ATTRIBUTE('null', 'CUSTOMER', 'ID', 'TOTAL_CUSTOMER', 'ID', 'ID', 'ID', 'ID');
CWM2_OLAP_LEVEL.CREATE_LEVEL('null', 'CUSTOMER', 'WAREHOUSE', 'WAREHOUSE', 'WAREHOUSE', 'WAREHOUSE', 'WAREHOUSE');
CWM2_OLAP_LEVEL_ATTRIBUTE.CREATE_LEVEL_ATTRIBUTE('null', 'CUSTOMER', 'ID', 'WAREHOUSE', 'ID', 'ID', 'ID', 'ID');
CWM2_OLAP_LEVEL_ATTRIBUTE.CREATE_LEVEL_ATTRIBUTE('null', 'CUSTOMER', 'DESCRIPTION', 'WAREHOUSE', 'DESCRIPTION', 'DESCRIPTION', 'DESCRIPTION', 'DESCRIPTION');
CWM2_OLAP_LEVEL_ATTRIBUTE.CREATE_LEVEL_ATTRIBUTE('null', 'CUSTOMER', 'NAME', 'WAREHOUSE', 'NAME', 'NAME', 'NAME', 'NAME');
CWM2_OLAP_HIERARCHY.CREATE_HIERARCHY('null', 'CUSTOMER', 'STANDARD', 'STANDARD', 'STANDARD', 'STANDARD','UNSOLVED LEVEL-BASED');
CWM2_OLAP_DIMENSION.SET_DEFAULT_DISPLAY_HIERARCHY('null', 'CUSTOMER', 'STANDARD');
CWM2_OLAP_LEVEL.ADD_LEVEL_TO_HIERARCHY('null', 'CUSTOMER', 'STANDARD', 'TOTAL_CUSTOMER');
CWM2_OLAP_LEVEL.ADD_LEVEL_TO_HIERARCHY('null', 'CUSTOMER', 'STANDARD', 'REGION', 'TOTAL_CUSTOMER');
CWM2_OLAP_LEVEL.ADD_LEVEL_TO_HIERARCHY('null', 'CUSTOMER', 'STANDARD', 'WAREHOUSE', 'REGION');
CWM2_OLAP_LEVEL.ADD_LEVEL_TO_HIERARCHY('null', 'CUSTOMER', 'STANDARD', 'SHIP_TO', 'WAREHOUSE');
CWM2_OLAP_TABLE_MAP.MAP_DIMTBL_HIERLEVEL('null','CUSTOMER','STANDARD','TOTAL_CUSTOMER','null','CUSTOMER_4','TOTAL_CUSTOMER_ID');
CWM2_OLAP_TABLE_MAP.MAP_DIMTBL_HIERLEVELATTR('null','CUSTOMER','DESCRIPTION','STANDARD','TOTAL_CUSTOMER','DESCRIPTION','null','CUSTOMER_4','TOTAL_CUSTOMER_DESCRIPTION');
CWM2_OLAP_TABLE_MAP.MAP_DIMTBL_HIERLEVELATTR('null','CUSTOMER','NAME','STANDARD','TOTAL_CUSTOMER','NAME','null','CUSTOMER_4','TOTAL_CUSTOMER_NAME');
CWM2_OLAP_TABLE_MAP.MAP_DIMTBL_HIERLEVELATTR('null','CUSTOMER','ID','STANDARD','TOTAL_CUSTOMER','ID','null','CUSTOMER_4','TOTAL_CUSTOMER_ID');
CWM2_OLAP_TABLE_MAP.MAP_DIMTBL_HIERLEVEL('null','CUSTOMER','STANDARD','REGION','null','CUSTOMER_4','REGION_ID','TOTAL_CUSTOMER_ID');
CWM2_OLAP_TABLE_MAP.MAP_DIMTBL_HIERLEVELATTR('null','CUSTOMER','NAME','STANDARD','REGION','NAME','null','CUSTOMER_4','REGION_NAME');
CWM2_OLAP_TABLE_MAP.MAP_DIMTBL_HIERLEVELATTR('null','CUSTOMER','DESCRIPTION','STANDARD','REGION','DESCRIPTION','null','CUSTOMER_4','REGION_DESCRIPTION');
CWM2_OLAP_TABLE_MAP.MAP_DIMTBL_HIERLEVELATTR('null','CUSTOMER','ID','STANDARD','REGION','ID','null','CUSTOMER_4','REGION_ID');
CWM2_OLAP_TABLE_MAP.MAP_DIMTBL_HIERLEVEL('null','CUSTOMER','STANDARD','WAREHOUSE','null','CUSTOMER_4','WAREHOUSE_ID','REGION_ID');
CWM2_OLAP_TABLE_MAP.MAP_DIMTBL_HIERLEVELATTR('null','CUSTOMER','ID','STANDARD','WAREHOUSE','ID','null','CUSTOMER_4','WAREHOUSE_ID');
CWM2_OLAP_TABLE_MAP.MAP_DIMTBL_HIERLEVELATTR('null','CUSTOMER','DESCRIPTION','STANDARD','WAREHOUSE','DESCRIPTION','null','CUSTOMER_4','WAREHOUSE_DESCRIPTION');
CWM2_OLAP_TABLE_MAP.MAP_DIMTBL_HIERLEVELATTR('null','CUSTOMER','NAME','STANDARD','WAREHOUSE','NAME','null','CUSTOMER_4','WAREHOUSE_NAME');
CWM2_OLAP_TABLE_MAP.MAP_DIMTBL_HIERLEVEL('null','CUSTOMER','STANDARD','SHIP_TO','null','CUSTOMER_4','SHIP_TO_ID','WAREHOUSE_ID');
CWM2_OLAP_TABLE_MAP.MAP_DIMTBL_HIERLEVELATTR('null','CUSTOMER','NAME','STANDARD','SHIP_TO','NAME','null','CUSTOMER_4','SHIP_TO_NAME');
CWM2_OLAP_TABLE_MAP.MAP_DIMTBL_HIERLEVELATTR('null','CUSTOMER','ID','STANDARD','SHIP_TO','ID','null','CUSTOMER_4','SHIP_TO_ID');
CWM2_OLAP_TABLE_MAP.MAP_DIMTBL_HIERLEVELATTR('null','CUSTOMER','DESCRIPTION','STANDARD','SHIP_TO','DESCRIPTION','null','CUSTOMER_4','SHIP_TO_DESCRIPTION');
CWM2_OLAP_VALIDATE.VALIDATE_DIMENSION('null','CUSTOMER');
CWM2_OLAP_METADATA_REFRESH.MR_REFRESH;
END;
/
Along with the script to create an OLAP Dimension, "Paris" also creates the relevant table DDL script, which in our case was:
/**********************************************************************
-- Product : Oracle Warehouse Builder
-- Generator Version : 10.0.1.1.32
-- Created Date : Wed Apr 13 12:55:14 BST 2005
-- Modified Date : Wed Apr 13 12:55:14 BST 2005
-- Created By : owb_paris
-- Modified By : owb_paris
-- Generated Object Type : TABLE
-- Generated Object Name : CUSTOMER_4
-- Comments :
-- © 2003 Oracle Corporation. All Rights Reserved.
**********************************************************************/
WHENEVER SQLERROR EXIT FAILURE;
CREATE TABLE "CUSTOMER_4"
(
"DIMENSION_KEY" NUMBER NOT NULL ,
"REGION_NAME" VARCHAR2(25),
"REGION_DESCRIPTION" VARCHAR2(40),
"REGION_ID" NUMBER,
"SHIP_TO_NAME" VARCHAR2(25),
"SHIP_TO_ID" NUMBER,
"SHIP_TO_DESCRIPTION" VARCHAR2(40),
"TOTAL_CUSTOMER_DESCRIPTION" VARCHAR2(40),
"TOTAL_CUSTOMER_NAME" VARCHAR2(25),
"TOTAL_CUSTOMER_ID" NUMBER,
"WAREHOUSE_ID" NUMBER,
"WAREHOUSE_DESCRIPTION" VARCHAR2(40),
"WAREHOUSE_NAME" VARCHAR2(25))
;
ALTER TABLE "CUSTOMER_4" ADD
CONSTRAINT "CUSTOMER_4_DIMENSION_KEY" PRIMARY KEY(
"DIMENSION_KEY");
At this point I could go on and deploy the dimension. What I was interested in though was seeing what happened if I switched the storage option to MOLAP, so I then started up the Data Object Editor (new for "Paris") and took a look at the Storage tab.
Using this dialog, you can change the relational implementation to a snowflake or a custom design, or switch the storage to an analytic workspace. If you choose to implement your dimension as MOLAP dimension, it will be placed in an analytic workspace and all the other dimensions for this cube, as well as the cube itself, will also need to go in this analytic workspace - i.e., you can't have a cube with the measures in an AW, half the dimensions implemented relationally and the other half multidimensionally, not now at least.
Selecting the MOLAP radio button, and then clicking on the "Select" button brings up a dialog box to allow us to pick an AW in which to deploy the dimension. At this stage, I hadn't previously created an analytic workspace using "Paris", so this is obviously an AW that only exists in the design repository for the moment.
After selecting the CUSTOMER_AW analytic workspace, naming the dimension and choosing a tablespace, I then went to generate the code to implement the dimension. This time, instead of creating a relational table and then calling the CWM2 API to create the OLAP Catalog entries, the generated code generated an XML definition of the AW, and then the dimension, and then passed this XML to the AWXML Java API to create the analytic workspace and the dimension. The analytic workspace was in this case generated first, as the code detected that it didn't already exist (you can't create the dimension without the AW first being present). Here's the code that was generated:
/**********************************************************************
-- Product : Oracle Warehouse Builder
-- Generator Version : 10.0.1.1.32
-- Created Date : Wed Apr 13 13:19:01 BST 2005
-- Modified Date : Wed Apr 13 13:19:01 BST 2005
-- Created By : owb_paris
-- Modified By : owb_paris
-- Generated Object Type : DIMENSION
-- Generated Object Name : CUSTOMER
-- Comments :
-- © 2003 Oracle Corporation. All Rights Reserved.
**********************************************************************/
WHENEVER SQLERROR EXIT FAILURE;
Create or replace view STG_TBLcustomer_dim_aw as select * from dual;
declare
xml_clob clob;
xml_st varchar2(4000);
xml_awcreate_clob clob;
xml_awcreate_st varchar2(4000);
isAW number;
begin
select count(*) into isAW from all_olap2_aws where aw = 'CUSTOMER_AW';
if (isAW=0) then
DBMS_LOB.CREATETEMPORARY(xml_awcreate_clob,TRUE);
dbms_lob.open(xml_awcreate_clob, DBMS_LOB.LOB_READWRITE);
dbms_lob.writeappend(xml_awcreate_clob, 48, '<?xml version = ''1.0'' encoding = ''ISO-8859-1'' ?>');
dbms_lob.writeappend(xml_awcreate_clob, 43, '<!-- <!DOCTYPE XMI SYSTEM ''Model.dtd'' > -->');
dbms_lob.writeappend(xml_awcreate_clob, 63, '<AWXML version = ''1.0'' timestamp = ''Mon Feb 11 13:29:11 2002'' >');
dbms_lob.writeappend(xml_awcreate_clob, 15, '<AWXML.content>');
dbms_lob.writeappend(xml_awcreate_clob, 25, ' <Create Id="Action20">');
dbms_lob.writeappend(xml_awcreate_clob, 19, ' <ActiveObject >');
dbms_lob.writeappend(xml_awcreate_clob, 123, ' <AW Name="CUSTOMER_AW" LongName="CUSTOMER_AW" ShortName="CUSTOMER_AW" PluralName="CUSTOMER_AW" Id="CUSTOMER_AW.AW"/>');
dbms_lob.writeappend(xml_awcreate_clob, 19, ' </ActiveObject>');
dbms_lob.writeappend(xml_awcreate_clob, 11, ' </Create>');
dbms_lob.writeappend(xml_awcreate_clob, 16, '</AWXML.content>');
dbms_lob.writeappend(xml_awcreate_clob, 8, '</AWXML>');
dbms_lob.close(xml_awcreate_clob);
xml_awcreate_st := sys.interactionExecute(xml_awcreate_clob);
end if;
begin
DBMS_AW.EXECUTE('AW DETACH CUSTOMER_AW');
exception
when others
then
null;
end;
DBMS_AW.EXECUTE('AW ATTACH CUSTOMER_AW RW');
DBMS_LOB.CREATETEMPORARY(xml_clob,TRUE);
dbms_lob.open(xml_clob, DBMS_LOB.LOB_READWRITE);
dbms_lob.writeappend(xml_clob, 48, '<?xml version = ''1.0'' encoding = ''ISO-8859-1'' ?>');
dbms_lob.writeappend(xml_clob, 43, '<!-- <!DOCTYPE XMI SYSTEM ''Model.dtd'' > -->');
dbms_lob.writeappend(xml_clob, 63, '<AWXML version = ''1.0'' timestamp = ''Mon Feb 11 13:29:11 2002'' >');
dbms_lob.writeappend(xml_clob, 15, '<AWXML.content>');
dbms_lob.writeappend(xml_clob, 47, ' <Attach Id="Action16" AWName="CUSTOMER_AW"/>');
dbms_lob.writeappend(xml_clob, 25, ' <Create Id="Action17">');
dbms_lob.writeappend(xml_clob, 19, ' <ActiveObject >');
dbms_lob.writeappend(xml_clob, 200, ' <Dimension Name="customer_dim_aw" LongName="CUSTOMER" ShortName="CUSTOMER" PluralName="customer_dim_aw" Id="CUSTOMER_DIM_AW.DIMENSION" Schema="AW$DEFAULT" isTime="false" isMeasure="false" UseNa');
dbms_lob.writeappend(xml_clob, 16, 'tiveKey="false">');
dbms_lob.writeappend(xml_clob, 200, ' <Attribute Name="DESCRIPTION" LongName="DESCRIPTION" ShortName="DESCRIPTION" PluralName="DESCRIPTION" Id="CUSTOMER_DIM_AW.DESCRIPTION.ATTRIBUTE" DataType="Text" Classification="MEMBER_LONG_DE');
dbms_lob.writeappend(xml_clob, 61, 'SCRIPTION" InstallAsRelation="false" IsDefaultOrder="false"/>');
dbms_lob.writeappend(xml_clob, 200, ' <Attribute Name="NAME" LongName="NAME" ShortName="NAME" PluralName="NAME" Id="CUSTOMER_DIM_AW.NAME.ATTRIBUTE" DataType="Text" Classification="MEMBER_SHORT_DESCRIPTION" InstallAsRelation="fals');
dbms_lob.writeappend(xml_clob, 27, 'e" IsDefaultOrder="false"/>');
dbms_lob.writeappend(xml_clob, 169, ' <Hierarchy Name="STANDARD" LongName="STANDARD" ShortName="STANDARD" PluralName="STANDARD" Id="CUSTOMER_DIM_AW.STANDARD.HIERARCHY" isDefault="true" Type="LEVEL">');
dbms_lob.writeappend(xml_clob, 182, ' <HierarchyLevelAssociation Name="TOTAL_CUSTOMER" LongName="HLA1" ShortName="HLA1" PluralName="HLA1" Id="CUSTOMER_DIM_AW.STANDARD.TOTAL_CUSTOMER.HIERARCHYLEVELASSOCIATION">');
dbms_lob.writeappend(xml_clob, 33, ' <SourceMappingGroup >');
dbms_lob.writeappend(xml_clob, 200, ' <DimensionMapGroup Name="MAPGROUP1" LongName="MAPGROUP1" ShortName="MAPGROUP1" PluralName="MAPGROUP1" Id="CUSTOMER_DIM_AW.STANDARD.TOTAL_CUSTOMER.MAPGROUP1.DIMENSIONMAPGROUP" Type="HIER');
dbms_lob.writeappend(xml_clob, 12, 'ARCHYLEVEL">');
dbms_lob.writeappend(xml_clob, 200, ' <DimensionKeySourceExpression Name="KeyMap" LongName="KeyMap" ShortName="KeyMap" PluralName="KeyMap" Id="CUSTOMER_DIM_AW.STANDARD.TOTAL_CUSTOMER.MAPGROUP1.KEYMAP.DIMENSIONKEYSOURCEEXP');
dbms_lob.writeappend(xml_clob, 9, 'RESSION">');
dbms_lob.writeappend(xml_clob, 200, ' <SourceColumn Name="OBJ361056228" LongName="" ShortName="OBJ361056228" PluralName="OBJ361056228" Id="CUSTOMER_DIM_AW.STANDARD.TOTAL_CUSTOMER.MAPGROUP1.KEYMAP.OBJ361056228.SOURCECOLU');
dbms_lob.writeappend(xml_clob, 56, 'MN" Column="STG_TBLcustomer_dim_aw.TOTAL_CUSTOMER_KEY"/>');
dbms_lob.writeappend(xml_clob, 75, ' <Level XMLIDref="CUSTOMER_DIM_AW.TOTAL_CUSTOMER.LEVEL" />');
dbms_lob.writeappend(xml_clob, 77, ' <Hierarchy XMLIDref="CUSTOMER_DIM_AW.STANDARD.HIERARCHY" />');
dbms_lob.writeappend(xml_clob, 47, ' </DimensionKeySourceExpression>');
dbms_lob.writeappend(xml_clob, 34, ' </DimensionMapGroup>');
dbms_lob.writeappend(xml_clob, 33, ' </SourceMappingGroup>');
dbms_lob.writeappend(xml_clob, 69, ' <level XMLIDref="CUSTOMER_DIM_AW.TOTAL_CUSTOMER.LEVEL" />');
dbms_lob.writeappend(xml_clob, 38, ' </HierarchyLevelAssociation>');
dbms_lob.writeappend(xml_clob, 166, ' <HierarchyLevelAssociation Name="REGION" LongName="HLA2" ShortName="HLA2" PluralName="HLA2" Id="CUSTOMER_DIM_AW.STANDARD.REGION.HIERARCHYLEVELASSOCIATION">');
dbms_lob.writeappend(xml_clob, 33, ' <SourceMappingGroup >');
dbms_lob.writeappend(xml_clob, 200, ' <DimensionMapGroup Name="MAPGROUP1" LongName="MAPGROUP1" ShortName="MAPGROUP1" PluralName="MAPGROUP1" Id="CUSTOMER_DIM_AW.STANDARD.REGION.MAPGROUP1.DIMENSIONMAPGROUP" Type="HIERARCHYLEV');
dbms_lob.writeappend(xml_clob, 4, 'EL">');
dbms_lob.writeappend(xml_clob, 200, ' <DimensionKeySourceExpression Name="KeyMap" LongName="KeyMap" ShortName="KeyMap" PluralName="KeyMap" Id="CUSTOMER_DIM_AW.STANDARD.REGION.MAPGROUP1.KEYMAP.DIMENSIONKEYSOURCEEXPRESSION"');
dbms_lob.writeappend(xml_clob, 1, '>');
dbms_lob.writeappend(xml_clob, 200, ' <SourceColumn Name="OBJ361056232" LongName="" ShortName="OBJ361056232" PluralName="OBJ361056232" Id="CUSTOMER_DIM_AW.STANDARD.REGION.MAPGROUP1.KEYMAP.OBJ361056232.SOURCECOLUMN" Colu');
dbms_lob.writeappend(xml_clob, 40, 'mn="STG_TBLcustomer_dim_aw.REGION_KEY"/>');
dbms_lob.writeappend(xml_clob, 67, ' <Level XMLIDref="CUSTOMER_DIM_AW.REGION.LEVEL" />');
dbms_lob.writeappend(xml_clob, 77, ' <Hierarchy XMLIDref="CUSTOMER_DIM_AW.STANDARD.HIERARCHY" />');
dbms_lob.writeappend(xml_clob, 47, ' </DimensionKeySourceExpression>');
dbms_lob.writeappend(xml_clob, 200, ' <HierarchicalParentSourceExpression Name="PrntMap" LongName="PrntMap" ShortName="PrntMap" PluralName="PrntMap" Id="CUSTOMER_DIM_AW.STANDARD.REGION.MAPGROUP1.PRNTMAP.HIERARCHICALPARENT');
dbms_lob.writeappend(xml_clob, 18, 'SOURCEEXPRESSION">');
dbms_lob.writeappend(xml_clob, 200, ' <SourceColumn Name="OBJ361056234" LongName="" ShortName="OBJ361056234" PluralName="OBJ361056234" Id="CUSTOMER_DIM_AW.STANDARD.REGION.MAPGROUP1.PRNTMAP.OBJ361056234.SOURCECOLUMN" Col');
dbms_lob.writeappend(xml_clob, 61, 'umn="STG_TBLcustomer_dim_aw.STANDARD_REGION_TOTAL_CUSTOMER"/>');
dbms_lob.writeappend(xml_clob, 75, ' <Level XMLIDref="CUSTOMER_DIM_AW.TOTAL_CUSTOMER.LEVEL" />');
dbms_lob.writeappend(xml_clob, 77, ' <Hierarchy XMLIDref="CUSTOMER_DIM_AW.STANDARD.HIERARCHY" />');
dbms_lob.writeappend(xml_clob, 53, ' </HierarchicalParentSourceExpression>');
dbms_lob.writeappend(xml_clob, 34, ' </DimensionMapGroup>');
dbms_lob.writeappend(xml_clob, 33, ' </SourceMappingGroup>');
dbms_lob.writeappend(xml_clob, 61, ' <level XMLIDref="CUSTOMER_DIM_AW.REGION.LEVEL" />');
dbms_lob.writeappend(xml_clob, 38, ' </HierarchyLevelAssociation>');
dbms_lob.writeappend(xml_clob, 172, ' <HierarchyLevelAssociation Name="WAREHOUSE" LongName="HLA3" ShortName="HLA3" PluralName="HLA3" Id="CUSTOMER_DIM_AW.STANDARD.WAREHOUSE.HIERARCHYLEVELASSOCIATION">');
dbms_lob.writeappend(xml_clob, 33, ' <SourceMappingGroup >');
dbms_lob.writeappend(xml_clob, 200, ' <DimensionMapGroup Name="MAPGROUP1" LongName="MAPGROUP1" ShortName="MAPGROUP1" PluralName="MAPGROUP1" Id="CUSTOMER_DIM_AW.STANDARD.WAREHOUSE.MAPGROUP1.DIMENSIONMAPGROUP" Type="HIERARCHY');
dbms_lob.writeappend(xml_clob, 7, 'LEVEL">');
dbms_lob.writeappend(xml_clob, 200, ' <DimensionKeySourceExpression Name="KeyMap" LongName="KeyMap" ShortName="KeyMap" PluralName="KeyMap" Id="CUSTOMER_DIM_AW.STANDARD.WAREHOUSE.MAPGROUP1.KEYMAP.DIMENSIONKEYSOURCEEXPRESSI');
dbms_lob.writeappend(xml_clob, 4, 'ON">');
dbms_lob.writeappend(xml_clob, 200, ' <SourceColumn Name="OBJ361056238" LongName="" ShortName="OBJ361056238" PluralName="OBJ361056238" Id="CUSTOMER_DIM_AW.STANDARD.WAREHOUSE.MAPGROUP1.KEYMAP.OBJ361056238.SOURCECOLUMN" C');
dbms_lob.writeappend(xml_clob, 46, 'olumn="STG_TBLcustomer_dim_aw.WAREHOUSE_KEY"/>');
dbms_lob.writeappend(xml_clob, 70, ' <Level XMLIDref="CUSTOMER_DIM_AW.WAREHOUSE.LEVEL" />');
dbms_lob.writeappend(xml_clob, 77, ' <Hierarchy XMLIDref="CUSTOMER_DIM_AW.STANDARD.HIERARCHY" />');
dbms_lob.writeappend(xml_clob, 47, ' </DimensionKeySourceExpression>');
dbms_lob.writeappend(xml_clob, 200, ' <HierarchicalParentSourceExpression Name="PrntMap" LongName="PrntMap" ShortName="PrntMap" PluralName="PrntMap" Id="CUSTOMER_DIM_AW.STANDARD.WAREHOUSE.MAPGROUP1.PRNTMAP.HIERARCHICALPAR');
dbms_lob.writeappend(xml_clob, 21, 'ENTSOURCEEXPRESSION">');
dbms_lob.writeappend(xml_clob, 200, ' <SourceColumn Name="OBJ361056240" LongName="" ShortName="OBJ361056240" PluralName="OBJ361056240" Id="CUSTOMER_DIM_AW.STANDARD.WAREHOUSE.MAPGROUP1.PRNTMAP.OBJ361056240.SOURCECOLUMN" ');
dbms_lob.writeappend(xml_clob, 59, 'Column="STG_TBLcustomer_dim_aw.STANDARD_WAREHOUSE_REGION"/>');
dbms_lob.writeappend(xml_clob, 67, ' <Level XMLIDref="CUSTOMER_DIM_AW.REGION.LEVEL" />');
dbms_lob.writeappend(xml_clob, 77, ' <Hierarchy XMLIDref="CUSTOMER_DIM_AW.STANDARD.HIERARCHY" />');
dbms_lob.writeappend(xml_clob, 53, ' </HierarchicalParentSourceExpression>');
dbms_lob.writeappend(xml_clob, 34, ' </DimensionMapGroup>');
dbms_lob.writeappend(xml_clob, 33, ' </SourceMappingGroup>');
dbms_lob.writeappend(xml_clob, 64, ' <level XMLIDref="CUSTOMER_DIM_AW.WAREHOUSE.LEVEL" />');
dbms_lob.writeappend(xml_clob, 38, ' </HierarchyLevelAssociation>');
dbms_lob.writeappend(xml_clob, 168, ' <HierarchyLevelAssociation Name="SHIP_TO" LongName="HLA4" ShortName="HLA4" PluralName="HLA4" Id="CUSTOMER_DIM_AW.STANDARD.SHIP_TO.HIERARCHYLEVELASSOCIATION">');
dbms_lob.writeappend(xml_clob, 33, ' <SourceMappingGroup >');
dbms_lob.writeappend(xml_clob, 200, ' <DimensionMapGroup Name="MAPGROUP1" LongName="MAPGROUP1" ShortName="MAPGROUP1" PluralName="MAPGROUP1" Id="CUSTOMER_DIM_AW.STANDARD.SHIP_TO.MAPGROUP1.DIMENSIONMAPGROUP" Type="HIERARCHYLE');
dbms_lob.writeappend(xml_clob, 5, 'VEL">');
dbms_lob.writeappend(xml_clob, 200, ' <DimensionKeySourceExpression Name="KeyMap" LongName="KeyMap" ShortName="KeyMap" PluralName="KeyMap" Id="CUSTOMER_DIM_AW.STANDARD.SHIP_TO.MAPGROUP1.KEYMAP.DIMENSIONKEYSOURCEEXPRESSION');
dbms_lob.writeappend(xml_clob, 2, '">');
dbms_lob.writeappend(xml_clob, 200, ' <SourceColumn Name="OBJ361056244" LongName="" ShortName="OBJ361056244" PluralName="OBJ361056244" Id="CUSTOMER_DIM_AW.STANDARD.SHIP_TO.MAPGROUP1.KEYMAP.OBJ361056244.SOURCECOLUMN" Col');
dbms_lob.writeappend(xml_clob, 42, 'umn="STG_TBLcustomer_dim_aw.SHIP_TO_KEY"/>');
dbms_lob.writeappend(xml_clob, 68, ' <Level XMLIDref="CUSTOMER_DIM_AW.SHIP_TO.LEVEL" />');
dbms_lob.writeappend(xml_clob, 77, ' <Hierarchy XMLIDref="CUSTOMER_DIM_AW.STANDARD.HIERARCHY" />');
dbms_lob.writeappend(xml_clob, 47, ' </DimensionKeySourceExpression>');
dbms_lob.writeappend(xml_clob, 200, ' <HierarchicalParentSourceExpression Name="PrntMap" LongName="PrntMap" ShortName="PrntMap" PluralName="PrntMap" Id="CUSTOMER_DIM_AW.STANDARD.SHIP_TO.MAPGROUP1.PRNTMAP.HIERARCHICALPAREN');
dbms_lob.writeappend(xml_clob, 19, 'TSOURCEEXPRESSION">');
dbms_lob.writeappend(xml_clob, 200, ' <SourceColumn Name="OBJ361056246" LongName="" ShortName="OBJ361056246" PluralName="OBJ361056246" Id="CUSTOMER_DIM_AW.STANDARD.SHIP_TO.MAPGROUP1.PRNTMAP.OBJ361056246.SOURCECOLUMN" Co');
dbms_lob.writeappend(xml_clob, 58, 'lumn="STG_TBLcustomer_dim_aw.STANDARD_SHIP_TO_WAREHOUSE"/>');
dbms_lob.writeappend(xml_clob, 70, ' <Level XMLIDref="CUSTOMER_DIM_AW.WAREHOUSE.LEVEL" />');
dbms_lob.writeappend(xml_clob, 77, ' <Hierarchy XMLIDref="CUSTOMER_DIM_AW.STANDARD.HIERARCHY" />');
dbms_lob.writeappend(xml_clob, 53, ' </HierarchicalParentSourceExpression>');
dbms_lob.writeappend(xml_clob, 34, ' </DimensionMapGroup>');
dbms_lob.writeappend(xml_clob, 33, ' </SourceMappingGroup>');
dbms_lob.writeappend(xml_clob, 62, ' <level XMLIDref="CUSTOMER_DIM_AW.SHIP_TO.LEVEL" />');
dbms_lob.writeappend(xml_clob, 38, ' </HierarchyLevelAssociation>');
dbms_lob.writeappend(xml_clob, 20, ' </Hierarchy>');
dbms_lob.writeappend(xml_clob, 26, ' <MemberSelection >');
dbms_lob.writeappend(xml_clob, 145, ' <Level Name="REGION" LongName="REGION" ShortName="REGION" PluralName="REGION" Id="CUSTOMER_DIM_AW.REGION.LEVEL" isDimensioned="false">');
dbms_lob.writeappend(xml_clob, 200, ' <AttributeProjection Name="DESCRIPTION" LongName="DESCRIPTION" ShortName="DESCRIPTION" PluralName="DESCRIPTION" Id="CUSTOMER_DIM_AW.REGION.DESCRIPTION.ATTRIBUTEPROJECTION" DataType="Text"');
dbms_lob.writeappend(xml_clob, 72, ' Classification="USER" InstallAsRelation="false" IsDefaultOrder="false">');
dbms_lob.writeappend(xml_clob, 78, ' <Attribute XMLIDref="CUSTOMER_DIM_AW.DESCRIPTION.ATTRIBUTE" />');
dbms_lob.writeappend(xml_clob, 65, ' <Level XMLIDref="CUSTOMER_DIM_AW.REGION.LEVEL" />');
dbms_lob.writeappend(xml_clob, 34, ' </AttributeProjection>');
dbms_lob.writeappend(xml_clob, 200, ' <AttributeProjection Name="NAME" LongName="NAME" ShortName="NAME" PluralName="NAME" Id="CUSTOMER_DIM_AW.REGION.NAME.ATTRIBUTEPROJECTION" DataType="Text" Classification="USER" InstallAsRel');
dbms_lob.writeappend(xml_clob, 37, 'ation="false" IsDefaultOrder="false">');
dbms_lob.writeappend(xml_clob, 71, ' <Attribute XMLIDref="CUSTOMER_DIM_AW.NAME.ATTRIBUTE" />');
dbms_lob.writeappend(xml_clob, 65, ' <Level XMLIDref="CUSTOMER_DIM_AW.REGION.LEVEL" />');
dbms_lob.writeappend(xml_clob, 34, ' </AttributeProjection>');
dbms_lob.writeappend(xml_clob, 33, ' <SourceMappingGroup >');
dbms_lob.writeappend(xml_clob, 186, ' <DimensionMapGroup Name="MAPGROUP1" LongName="MAPGROUP1" ShortName="MAPGROUP1" PluralName="MAPGROUP1" Id="CUSTOMER_DIM_AW.REGION.MAPGROUP1.DIMENSIONMAPGROUP" Type="LEVEL">');
dbms_lob.writeappend(xml_clob, 192, ' <DimensionKeySourceExpression Name="KeyMap" LongName="KeyMap" ShortName="KeyMap" PluralName="KeyMap" Id="CUSTOMER_DIM_AW.REGION.MAPGROUP1.KEYMAP.DIMENSIONKEYSOURCEEXPRESSION">');
dbms_lob.writeappend(xml_clob, 200, ' <SourceColumn Name="OBJ361056187" LongName="" ShortName="OBJ361056187" PluralName="OBJ361056187" Id="CUSTOMER_DIM_AW.REGION.MAPGROUP1.KEYMAP.OBJ361056187.SOURCECOLUMN" Column="STG_T');
dbms_lob.writeappend(xml_clob, 31, 'BLcustomer_dim_aw.REGION_KEY"/>');
dbms_lob.writeappend(xml_clob, 67, ' <Level XMLIDref="CUSTOMER_DIM_AW.REGION.LEVEL" />');
dbms_lob.writeappend(xml_clob, 47, ' </DimensionKeySourceExpression>');
dbms_lob.writeappend(xml_clob, 196, ' <AttributeSourceExpression Name="AttrMap1" LongName="AttrMap1" ShortName="AttrMap1" PluralName="AttrMap1" Id="CUSTOMER_DIM_AW.REGION.MAPGROUP1.ATTRMAP1.ATTRIBUTESOURCEEXPRESSION">');
dbms_lob.writeappend(xml_clob, 200, ' <SourceColumn Name="OBJ361056190" LongName="" ShortName="OBJ361056190" PluralName="OBJ361056190" Id="CUSTOMER_DIM_AW.REGION.MAPGROUP1.ATTRMAP1.OBJ361056190.SOURCECOLUMN" Column="STG');
dbms_lob.writeappend(xml_clob, 41, '_TBLcustomer_dim_aw.REGION_DESCRIPTION"/>');
dbms_lob.writeappend(xml_clob, 100, ' <TargetObject XMLIDref="CUSTOMER_DIM_AW.REGION.DESCRIPTION.ATTRIBUTEPROJECTION" />');
dbms_lob.writeappend(xml_clob, 67, ' <Level XMLIDref="CUSTOMER_DIM_AW.REGION.LEVEL" />');
dbms_lob.writeappend(xml_clob, 84, ' <BaseAttribute XMLIDref="CUSTOMER_DIM_AW.DESCRIPTION.ATTRIBUTE" />');
dbms_lob.writeappend(xml_clob, 44, ' </AttributeSourceExpression>');
dbms_lob.writeappend(xml_clob, 196, ' <AttributeSourceExpression Name="AttrMap2" LongName="AttrMap2" ShortName="AttrMap2" PluralName="AttrMap2" Id="CUSTOMER_DIM_AW.REGION.MAPGROUP1.ATTRMAP2.ATTRIBUTESOURCEEXPRESSION">');
dbms_lob.writeappend(xml_clob, 200, ' <SourceColumn Name="OBJ361056193" LongName="" ShortName="OBJ361056193" PluralName="OBJ361056193" Id="CUSTOMER_DIM_AW.REGION.MAPGROUP1.ATTRMAP2.OBJ361056193.SOURCECOLUMN" Column="STG');
dbms_lob.writeappend(xml_clob, 34, '_TBLcustomer_dim_aw.REGION_NAME"/>');
dbms_lob.writeappend(xml_clob, 93, ' <TargetObject XMLIDref="CUSTOMER_DIM_AW.REGION.NAME.ATTRIBUTEPROJECTION" />');
dbms_lob.writeappend(xml_clob, 67, ' <Level XMLIDref="CUSTOMER_DIM_AW.REGION.LEVEL" />');
dbms_lob.writeappend(xml_clob, 77, ' <BaseAttribute XMLIDref="CUSTOMER_DIM_AW.NAME.ATTRIBUTE" />');
dbms_lob.writeappend(xml_clob, 44, ' </AttributeSourceExpression>');
dbms_lob.writeappend(xml_clob, 34, ' </DimensionMapGroup>');
dbms_lob.writeappend(xml_clob, 33, ' </SourceMappingGroup>');
dbms_lob.writeappend(xml_clob, 18, ' </Level>');
dbms_lob.writeappend(xml_clob, 150, ' <Level Name="SHIP_TO" LongName="SHIP_TO" ShortName="SHIP_TO" PluralName="SHIP_TO" Id="CUSTOMER_DIM_AW.SHIP_TO.LEVEL" isDimensioned="false">');
dbms_lob.writeappend(xml_clob, 200, ' <AttributeProjection Name="DESCRIPTION" LongName="DESCRIPTION" ShortName="DESCRIPTION" PluralName="DESCRIPTION" Id="CUSTOMER_DIM_AW.SHIP_TO.DESCRIPTION.ATTRIBUTEPROJECTION" DataType="Text');
dbms_lob.writeappend(xml_clob, 73, '" Classification="USER" InstallAsRelation="false" IsDefaultOrder="false">');
dbms_lob.writeappend(xml_clob, 78, ' <Attribute XMLIDref="CUSTOMER_DIM_AW.DESCRIPTION.ATTRIBUTE" />');
dbms_lob.writeappend(xml_clob, 66, ' <Level XMLIDref="CUSTOMER_DIM_AW.SHIP_TO.LEVEL" />');
dbms_lob.writeappend(xml_clob, 34, ' </AttributeProjection>');
dbms_lob.writeappend(xml_clob, 200, ' <AttributeProjection Name="NAME" LongName="NAME" ShortName="NAME" PluralName="NAME" Id="CUSTOMER_DIM_AW.SHIP_TO.NAME.ATTRIBUTEPROJECTION" DataType="Text" Classification="USER" InstallAsRe');
dbms_lob.writeappend(xml_clob, 38, 'lation="false" IsDefaultOrder="false">');
dbms_lob.writeappend(xml_clob, 71, ' <Attribute XMLIDref="CUSTOMER_DIM_AW.NAME.ATTRIBUTE" />');
dbms_lob.writeappend(xml_clob, 66, ' <Level XMLIDref="CUSTOMER_DIM_AW.SHIP_TO.LEVEL" />');
dbms_lob.writeappend(xml_clob, 34, ' </AttributeProjection>');
dbms_lob.writeappend(xml_clob, 33, ' <SourceMappingGroup >');
dbms_lob.writeappend(xml_clob, 187, ' <DimensionMapGroup Name="MAPGROUP1" LongName="MAPGROUP1" ShortName="MAPGROUP1" PluralName="MAPGROUP1" Id="CUSTOMER_DIM_AW.SHIP_TO.MAPGROUP1.DIMENSIONMAPGROUP" Type="LEVEL">');
dbms_lob.writeappend(xml_clob, 193, ' <DimensionKeySourceExpression Name="KeyMap" LongName="KeyMap" ShortName="KeyMap" PluralName="KeyMap" Id="CUSTOMER_DIM_AW.SHIP_TO.MAPGROUP1.KEYMAP.DIMENSIONKEYSOURCEEXPRESSION">');
dbms_lob.writeappend(xml_clob, 200, ' <SourceColumn Name="OBJ361056197" LongName="" ShortName="OBJ361056197" PluralName="OBJ361056197" Id="CUSTOMER_DIM_AW.SHIP_TO.MAPGROUP1.KEYMAP.OBJ361056197.SOURCECOLUMN" Column="STG_');
dbms_lob.writeappend(xml_clob, 33, 'TBLcustomer_dim_aw.SHIP_TO_KEY"/>');
dbms_lob.writeappend(xml_clob, 68, ' <Level XMLIDref="CUSTOMER_DIM_AW.SHIP_TO.LEVEL" />');
dbms_lob.writeappend(xml_clob, 47, ' </DimensionKeySourceExpression>');
dbms_lob.writeappend(xml_clob, 197, ' <AttributeSourceExpression Name="AttrMap1" LongName="AttrMap1" ShortName="AttrMap1" PluralName="AttrMap1" Id="CUSTOMER_DIM_AW.SHIP_TO.MAPGROUP1.ATTRMAP1.ATTRIBUTESOURCEEXPRESSION">');
dbms_lob.writeappend(xml_clob, 200, ' <SourceColumn Name="OBJ361056200" LongName="" ShortName="OBJ361056200" PluralName="OBJ361056200" Id="CUSTOMER_DIM_AW.SHIP_TO.MAPGROUP1.ATTRMAP1.OBJ361056200.SOURCECOLUMN" Column="ST');
dbms_lob.writeappend(xml_clob, 43, 'G_TBLcustomer_dim_aw.SHIP_TO_DESCRIPTION"/>');
dbms_lob.writeappend(xml_clob, 101, ' <TargetObject XMLIDref="CUSTOMER_DIM_AW.SHIP_TO.DESCRIPTION.ATTRIBUTEPROJECTION" />');
dbms_lob.writeappend(xml_clob, 68, ' <Level XMLIDref="CUSTOMER_DIM_AW.SHIP_TO.LEVEL" />');
dbms_lob.writeappend(xml_clob, 84, ' <BaseAttribute XMLIDref="CUSTOMER_DIM_AW.DESCRIPTION.ATTRIBUTE" />');
dbms_lob.writeappend(xml_clob, 44, ' </AttributeSourceExpression>');
dbms_lob.writeappend(xml_clob, 197, ' <AttributeSourceExpression Name="AttrMap2" LongName="AttrMap2" ShortName="AttrMap2" PluralName="AttrMap2" Id="CUSTOMER_DIM_AW.SHIP_TO.MAPGROUP1.ATTRMAP2.ATTRIBUTESOURCEEXPRESSION">');
dbms_lob.writeappend(xml_clob, 200, ' <SourceColumn Name="OBJ361056203" LongName="" ShortName="OBJ361056203" PluralName="OBJ361056203" Id="CUSTOMER_DIM_AW.SHIP_TO.MAPGROUP1.ATTRMAP2.OBJ361056203.SOURCECOLUMN" Column="ST');
dbms_lob.writeappend(xml_clob, 36, 'G_TBLcustomer_dim_aw.SHIP_TO_NAME"/>');
dbms_lob.writeappend(xml_clob, 94, ' <TargetObject XMLIDref="CUSTOMER_DIM_AW.SHIP_TO.NAME.ATTRIBUTEPROJECTION" />');
dbms_lob.writeappend(xml_clob, 68, ' <Level XMLIDref="CUSTOMER_DIM_AW.SHIP_TO.LEVEL" />');
dbms_lob.writeappend(xml_clob, 77, ' <BaseAttribute XMLIDref="CUSTOMER_DIM_AW.NAME.ATTRIBUTE" />');
dbms_lob.writeappend(xml_clob, 44, ' </AttributeSourceExpression>');
dbms_lob.writeappend(xml_clob, 34, ' </DimensionMapGroup>');
dbms_lob.writeappend(xml_clob, 33, ' </SourceMappingGroup>');
dbms_lob.writeappend(xml_clob, 18, ' </Level>');
dbms_lob.writeappend(xml_clob, 185, ' <Level Name="TOTAL_CUSTOMER" LongName="TOTAL_CUSTOMER" ShortName="TOTAL_CUSTOMER" PluralName="TOTAL_CUSTOMER" Id="CUSTOMER_DIM_AW.TOTAL_CUSTOMER.LEVEL" isDimensioned="false">');
dbms_lob.writeappend(xml_clob, 200, ' <AttributeProjection Name="DESCRIPTION" LongName="DESCRIPTION" ShortName="DESCRIPTION" PluralName="DESCRIPTION" Id="CUSTOMER_DIM_AW.TOTAL_CUSTOMER.DESCRIPTION.ATTRIBUTEPROJECTION" DataTyp');
dbms_lob.writeappend(xml_clob, 80, 'e="Text" Classification="USER" InstallAsRelation="false" IsDefaultOrder="false">');
dbms_lob.writeappend(xml_clob, 78, ' <Attribute XMLIDref="CUSTOMER_DIM_AW.DESCRIPTION.ATTRIBUTE" />');
dbms_lob.writeappend(xml_clob, 73, ' <Level XMLIDref="CUSTOMER_DIM_AW.TOTAL_CUSTOMER.LEVEL" />');
dbms_lob.writeappend(xml_clob, 34, ' </AttributeProjection>');
dbms_lob.writeappend(xml_clob, 200, ' <AttributeProjection Name="NAME" LongName="NAME" ShortName="NAME" PluralName="NAME" Id="CUSTOMER_DIM_AW.TOTAL_CUSTOMER.NAME.ATTRIBUTEPROJECTION" DataType="Text" Classification="USER" Inst');
dbms_lob.writeappend(xml_clob, 45, 'allAsRelation="false" IsDefaultOrder="false">');
dbms_lob.writeappend(xml_clob, 71, ' <Attribute XMLIDref="CUSTOMER_DIM_AW.NAME.ATTRIBUTE" />');
dbms_lob.writeappend(xml_clob, 73, ' <Level XMLIDref="CUSTOMER_DIM_AW.TOTAL_CUSTOMER.LEVEL" />');
dbms_lob.writeappend(xml_clob, 34, ' </AttributeProjection>');
dbms_lob.writeappend(xml_clob, 33, ' <SourceMappingGroup >');
dbms_lob.writeappend(xml_clob, 194, ' <DimensionMapGroup Name="MAPGROUP1" LongName="MAPGROUP1" ShortName="MAPGROUP1" PluralName="MAPGROUP1" Id="CUSTOMER_DIM_AW.TOTAL_CUSTOMER.MAPGROUP1.DIMENSIONMAPGROUP" Type="LEVEL">');
dbms_lob.writeappend(xml_clob, 200, ' <DimensionKeySourceExpression Name="KeyMap" LongName="KeyMap" ShortName="KeyMap" PluralName="KeyMap" Id="CUSTOMER_DIM_AW.TOTAL_CUSTOMER.MAPGROUP1.KEYMAP.DIMENSIONKEYSOURCEEXPRESSION">');
dbms_lob.writeappend(xml_clob, 200, ' <SourceColumn Name="OBJ361056207" LongName="" ShortName="OBJ361056207" PluralName="OBJ361056207" Id="CUSTOMER_DIM_AW.TOTAL_CUSTOMER.MAPGROUP1.KEYMAP.OBJ361056207.SOURCECOLUMN" Colum');
dbms_lob.writeappend(xml_clob, 47, 'n="STG_TBLcustomer_dim_aw.TOTAL_CUSTOMER_KEY"/>');
dbms_lob.writeappend(xml_clob, 75, ' <Level XMLIDref="CUSTOMER_DIM_AW.TOTAL_CUSTOMER.LEVEL" />');
dbms_lob.writeappend(xml_clob, 47, ' </DimensionKeySourceExpression>');
dbms_lob.writeappend(xml_clob, 200, ' <AttributeSourceExpression Name="AttrMap1" LongName="AttrMap1" ShortName="AttrMap1" PluralName="AttrMap1" Id="CUSTOMER_DIM_AW.TOTAL_CUSTOMER.MAPGROUP1.ATTRMAP1.ATTRIBUTESOURCEEXPRESSI');
dbms_lob.writeappend(xml_clob, 4, 'ON">');
dbms_lob.writeappend(xml_clob, 200, ' <SourceColumn Name="OBJ361056210" LongName="" ShortName="OBJ361056210" PluralName="OBJ361056210" Id="CUSTOMER_DIM_AW.TOTAL_CUSTOMER.MAPGROUP1.ATTRMAP1.OBJ361056210.SOURCECOLUMN" Col');
dbms_lob.writeappend(xml_clob, 57, 'umn="STG_TBLcustomer_dim_aw.TOTAL_CUSTOMER_DESCRIPTION"/>');
dbms_lob.writeappend(xml_clob, 108, ' <TargetObject XMLIDref="CUSTOMER_DIM_AW.TOTAL_CUSTOMER.DESCRIPTION.ATTRIBUTEPROJECTION" />');
dbms_lob.writeappend(xml_clob, 75, ' <Level XMLIDref="CUSTOMER_DIM_AW.TOTAL_CUSTOMER.LEVEL" />');
dbms_lob.writeappend(xml_clob, 84, ' <BaseAttribute XMLIDref="CUSTOMER_DIM_AW.DESCRIPTION.ATTRIBUTE" />');
dbms_lob.writeappend(xml_clob, 44, ' </AttributeSourceExpression>');
dbms_lob.writeappend(xml_clob, 200, ' <AttributeSourceExpression Name="AttrMap2" LongName="AttrMap2" ShortName="AttrMap2" PluralName="AttrMap2" Id="CUSTOMER_DIM_AW.TOTAL_CUSTOMER.MAPGROUP1.ATTRMAP2.ATTRIBUTESOURCEEXPRESSI');
dbms_lob.writeappend(xml_clob, 4, 'ON">');
dbms_lob.writeappend(xml_clob, 200, ' <SourceColumn Name="OBJ361056213" LongName="" ShortName="OBJ361056213" PluralName="OBJ361056213" Id="CUSTOMER_DIM_AW.TOTAL_CUSTOMER.MAPGROUP1.ATTRMAP2.OBJ361056213.SOURCECOLUMN" Col');
dbms_lob.writeappend(xml_clob, 50, 'umn="STG_TBLcustomer_dim_aw.TOTAL_CUSTOMER_NAME"/>');
dbms_lob.writeappend(xml_clob, 101, ' <TargetObject XMLIDref="CUSTOMER_DIM_AW.TOTAL_CUSTOMER.NAME.ATTRIBUTEPROJECTION" />');
dbms_lob.writeappend(xml_clob, 75, ' <Level XMLIDref="CUSTOMER_DIM_AW.TOTAL_CUSTOMER.LEVEL" />');
dbms_lob.writeappend(xml_clob, 77, ' <BaseAttribute XMLIDref="CUSTOMER_DIM_AW.NAME.ATTRIBUTE" />');
dbms_lob.writeappend(xml_clob, 44, ' </AttributeSourceExpression>');
dbms_lob.writeappend(xml_clob, 34, ' </DimensionMapGroup>');
dbms_lob.writeappend(xml_clob, 33, ' </SourceMappingGroup>');
dbms_lob.writeappend(xml_clob, 18, ' </Level>');
dbms_lob.writeappend(xml_clob, 160, ' <Level Name="WAREHOUSE" LongName="WAREHOUSE" ShortName="WAREHOUSE" PluralName="WAREHOUSE" Id="CUSTOMER_DIM_AW.WAREHOUSE.LEVEL" isDimensioned="false">');
dbms_lob.writeappend(xml_clob, 200, ' <AttributeProjection Name="DESCRIPTION" LongName="DESCRIPTION" ShortName="DESCRIPTION" PluralName="DESCRIPTION" Id="CUSTOMER_DIM_AW.WAREHOUSE.DESCRIPTION.ATTRIBUTEPROJECTION" DataType="Te');
dbms_lob.writeappend(xml_clob, 75, 'xt" Classification="USER" InstallAsRelation="false" IsDefaultOrder="false">');
dbms_lob.writeappend(xml_clob, 78, ' <Attribute XMLIDref="CUSTOMER_DIM_AW.DESCRIPTION.ATTRIBUTE" />');
dbms_lob.writeappend(xml_clob, 68, ' <Level XMLIDref="CUSTOMER_DIM_AW.WAREHOUSE.LEVEL" />');
dbms_lob.writeappend(xml_clob, 34, ' </AttributeProjection>');
dbms_lob.writeappend(xml_clob, 200, ' <AttributeProjection Name="NAME" LongName="NAME" ShortName="NAME" PluralName="NAME" Id="CUSTOMER_DIM_AW.WAREHOUSE.NAME.ATTRIBUTEPROJECTION" DataType="Text" Classification="USER" InstallAs');
dbms_lob.writeappend(xml_clob, 40, 'Relation="false" IsDefaultOrder="false">');
dbms_lob.writeappend(xml_clob, 71, ' <Attribute XMLIDref="CUSTOMER_DIM_AW.NAME.ATTRIBUTE" />');
dbms_lob.writeappend(xml_clob, 68, ' <Level XMLIDref="CUSTOMER_DIM_AW.WAREHOUSE.LEVEL" />');
dbms_lob.writeappend(xml_clob, 34, ' </AttributeProjection>');
dbms_lob.writeappend(xml_clob, 33, ' <SourceMappingGroup >');
dbms_lob.writeappend(xml_clob, 189, ' <DimensionMapGroup Name="MAPGROUP1" LongName="MAPGROUP1" ShortName="MAPGROUP1" PluralName="MAPGROUP1" Id="CUSTOMER_DIM_AW.WAREHOUSE.MAPGROUP1.DIMENSIONMAPGROUP" Type="LEVEL">');
dbms_lob.writeappend(xml_clob, 195, ' <DimensionKeySourceExpression Name="KeyMap" LongName="KeyMap" ShortName="KeyMap" PluralName="KeyMap" Id="CUSTOMER_DIM_AW.WAREHOUSE.MAPGROUP1.KEYMAP.DIMENSIONKEYSOURCEEXPRESSION">');
dbms_lob.writeappend(xml_clob, 200, ' <SourceColumn Name="OBJ361056217" LongName="" ShortName="OBJ361056217" PluralName="OBJ361056217" Id="CUSTOMER_DIM_AW.WAREHOUSE.MAPGROUP1.KEYMAP.OBJ361056217.SOURCECOLUMN" Column="ST');
dbms_lob.writeappend(xml_clob, 37, 'G_TBLcustomer_dim_aw.WAREHOUSE_KEY"/>');
dbms_lob.writeappend(xml_clob, 70, ' <Level XMLIDref="CUSTOMER_DIM_AW.WAREHOUSE.LEVEL" />');
dbms_lob.writeappend(xml_clob, 47, ' </DimensionKeySourceExpression>');
dbms_lob.writeappend(xml_clob, 199, ' <AttributeSourceExpression Name="AttrMap1" LongName="AttrMap1" ShortName="AttrMap1" PluralName="AttrMap1" Id="CUSTOMER_DIM_AW.WAREHOUSE.MAPGROUP1.ATTRMAP1.ATTRIBUTESOURCEEXPRESSION">');
dbms_lob.writeappend(xml_clob, 200, ' <SourceColumn Name="OBJ361056220" LongName="" ShortName="OBJ361056220" PluralName="OBJ361056220" Id="CUSTOMER_DIM_AW.WAREHOUSE.MAPGROUP1.ATTRMAP1.OBJ361056220.SOURCECOLUMN" Column="');
dbms_lob.writeappend(xml_clob, 47, 'STG_TBLcustomer_dim_aw.WAREHOUSE_DESCRIPTION"/>');
dbms_lob.writeappend(xml_clob, 103, ' <TargetObject XMLIDref="CUSTOMER_DIM_AW.WAREHOUSE.DESCRIPTION.ATTRIBUTEPROJECTION" />');
dbms_lob.writeappend(xml_clob, 70, ' <Level XMLIDref="CUSTOMER_DIM_AW.WAREHOUSE.LEVEL" />');
dbms_lob.writeappend(xml_clob, 84, ' <BaseAttribute XMLIDref="CUSTOMER_DIM_AW.DESCRIPTION.ATTRIBUTE" />');
dbms_lob.writeappend(xml_clob, 44, ' </AttributeSourceExpression>');
dbms_lob.writeappend(xml_clob, 199, ' <AttributeSourceExpression Name="AttrMap2" LongName="AttrMap2" ShortName="AttrMap2" PluralName="AttrMap2" Id="CUSTOMER_DIM_AW.WAREHOUSE.MAPGROUP1.ATTRMAP2.ATTRIBUTESOURCEEXPRESSION">');
dbms_lob.writeappend(xml_clob, 200, ' <SourceColumn Name="OBJ361056223" LongName="" ShortName="OBJ361056223" PluralName="OBJ361056223" Id="CUSTOMER_DIM_AW.WAREHOUSE.MAPGROUP1.ATTRMAP2.OBJ361056223.SOURCECOLUMN" Column="');
dbms_lob.writeappend(xml_clob, 40, 'STG_TBLcustomer_dim_aw.WAREHOUSE_NAME"/>');
dbms_lob.writeappend(xml_clob, 96, ' <TargetObject XMLIDref="CUSTOMER_DIM_AW.WAREHOUSE.NAME.ATTRIBUTEPROJECTION" />');
dbms_lob.writeappend(xml_clob, 70, ' <Level XMLIDref="CUSTOMER_DIM_AW.WAREHOUSE.LEVEL" />');
dbms_lob.writeappend(xml_clob, 77, ' <BaseAttribute XMLIDref="CUSTOMER_DIM_AW.NAME.ATTRIBUTE" />');
dbms_lob.writeappend(xml_clob, 44, ' </AttributeSourceExpression>');
dbms_lob.writeappend(xml_clob, 34, ' </DimensionMapGroup>');
dbms_lob.writeappend(xml_clob, 33, ' </SourceMappingGroup>');
dbms_lob.writeappend(xml_clob, 18, ' </Level>');
dbms_lob.writeappend(xml_clob, 26, ' </MemberSelection>');
dbms_lob.writeappend(xml_clob, 18, ' </Dimension>');
dbms_lob.writeappend(xml_clob, 19, ' </ActiveObject>');
dbms_lob.writeappend(xml_clob, 11, ' </Create>');
dbms_lob.writeappend(xml_clob, 47, ' <Commit Id="Action18" AWName="CUSTOMER_AW"/>');
dbms_lob.writeappend(xml_clob, 47, ' <Detach Id="Action19" AWName="CUSTOMER_AW"/>');
dbms_lob.writeappend(xml_clob, 16, '</AWXML.content>');
dbms_lob.writeappend(xml_clob, 8, '</AWXML>');
dbms_lob.close(xml_clob);
xml_st := sys.interactionExecute(xml_clob);
end;
So what we've got here then is a situation where we can define a dimension logically using a wizard, or through the Data Object Editor, and then at deployment time we can choose whether to implement it relationally or multidimensionally. This creates a distinction then between OWB "Paris" and Enterprise Manager DBConsole, as DBConsole can only define relational dimensions, and their OLAP Metadata, and between OWB "Paris" and Analytic Workspace Manager 10g, which can only define multidimensional dimensions. Using "Paris", if you've got a relational dimension, you can also specify whether it's a type 1 (record no history), type 2 (record all history) or type 3 (record current and previous history) dimension - the reason (I think) that you can't do this for MOLAP dimensions is that you can't have multiple records in a dimension for the same dimension member, which would be requirement if you need to record multiple historical versions of a dimension member. In practice then, what this would mean is that if you want to implement a dimension multidimensionally, and your source data is from a type 2 or 3 slowly changing dimension, you'd need to create the dimension to only hold the current version of the dimension member, and have the loading routine only pass across the most recent version of the dimension member. I'll look at this in more detail at a later date.
From quickly looking around the Data Object Editor, there are a number of new features that I've not had a chance to look at properly yet. For example, if you've chosen a relational implementation, the "Orphan" tab lets you decide how to handle orphan dimension records when you load and delete records from the dimension - I'm not quite sure what the relevance of this is, perhaps it's to do with parent-child or snowflake dimensions - I'll have to look in more detail later.
The logical next step for this would be to create the remaining dimensions, then populate them using the GLOBAL demo data. After that, you'd want to create the cube, containing the measures, and if I get some spare time I'll take a look at this over the next couple of days. Until then, I thought this a useful look at the new dimension building features in "Paris", if you've got any further questions feel free to drop me a line.