In the last part I went through interface tables and if you followed the blog you should now understand the requirements to load metadata to existing dimensions using them
I ended up with a set of tables ready to be populated with metadata though I am not going populate them just yet as I think it is worthwhile running through an interface import profile first.
An import profile provides all the required information about the dimensions and properties that are going to be imported, it defines the type of actions to take when loading members and their properties.
The easiest way to understand them is to go through the steps in creating one.
From within the dimension library go to File > Import > Create Profile
A profile name is entered and the Import Type is set to “Interface Tables”.
The application is set to “Shared Library” as the intention is to load to the already created Shared Dimensions, if you wanted to load directly to an application it can be selected from the dropdown.
The Data Source was created in the last part and holds the connection information to the database where the interface tables are located.
The Map Dimensions section defines the source dimensions from the interfaces tables and which dimension they will be mapped to in the Shared Library.
The source dimension information is picked up from the interface table IM_DIMENSIONS and column C_DIMENSION_NAME
There are a number of options under Process Type.
• Merge as Primary—Merge as Primary processes all sections and adds new dimensions, members, relationships, properties and associations that exist in the source, but do not exist in the Shared Library or target application. No dimensions, members, relationships, properties, or associations are removed. If a dimension, relationship, member, or property specified in the source exists in the Shared Library or target application it is overwritten with the value specified in the source. Properties not included in the source are unchanged in the Shared Library or target application.
• Merge as Move—This mode only processes members in the IsPrimary column that are set to “True” and ignores any others, so basically just looking at shared members.
• Replace—All new elements are added and all property updates are made. Then, any members or member relationships that are not specified in the source are deleted from the Shared Library or target application. This option would be used if you wanted to rebuild the dimension hierarchy.
There are two options available for Reorder Type
• Merge to Top —Places new imported child members at the beginning of the child list under their parent.
• Merge to Bottom—Places new imported child members at the end of the child list under their parent.
If “Reorder Existing Members” is selected then the action depends on what has been selected in “Reorder Type”
• If “Merge to Top” is selected, the first child member in the import source becomes the first child under the parent, all imported child members are sorted to match the order in the import source, and any existing members not in the import source will be pushed to the end of the child list in their existing order.
• If “Merge to Bottom” is selected, the last child member in the import source becomes the last child under the parent, all imported child members are sorted to match the order in the import source, and any existing members not in the import source will be pushed to the beginning of the child list in their existing order.
If you want a more detailed example have a look at the following section in the EPMA admin documentation – “Reordering Existing Member Examples”
The “Dimension Mapping” section provides the functionality to map each dimension source member property to a target member property in the Shared Library.
As I have created the source table columns with the same naming convention to the member properties in EPMA it is a direct mapping.
There is another parameter that can be set for each member that defines how existing property values are dealt with.
• Clear Before Importing – if selected values will be cleared out, therefore making values match the import source exactly. If not selected, values will be merged in with existing values and all existing values will remain.
• Allow Overwrites with Blank –if the source value is blank the target property will be overwritten with the default property, so for instance if selected and there was no value in the DataStorage column the default value “StoreData” will be applied.
No need to execute the profile as there is no metadata in the hierarchy tables yet, I will be covering loading the metadata in the next instalment.
I ended up with a set of tables ready to be populated with metadata though I am not going populate them just yet as I think it is worthwhile running through an interface import profile first.
An import profile provides all the required information about the dimensions and properties that are going to be imported, it defines the type of actions to take when loading members and their properties.
The easiest way to understand them is to go through the steps in creating one.
From within the dimension library go to File > Import > Create Profile
A profile name is entered and the Import Type is set to “Interface Tables”.
The application is set to “Shared Library” as the intention is to load to the already created Shared Dimensions, if you wanted to load directly to an application it can be selected from the dropdown.
The Data Source was created in the last part and holds the connection information to the database where the interface tables are located.
The Map Dimensions section defines the source dimensions from the interfaces tables and which dimension they will be mapped to in the Shared Library.
The source dimension information is picked up from the interface table IM_DIMENSIONS and column C_DIMENSION_NAME
There are a number of options under Process Type.
• Merge as Primary—Merge as Primary processes all sections and adds new dimensions, members, relationships, properties and associations that exist in the source, but do not exist in the Shared Library or target application. No dimensions, members, relationships, properties, or associations are removed. If a dimension, relationship, member, or property specified in the source exists in the Shared Library or target application it is overwritten with the value specified in the source. Properties not included in the source are unchanged in the Shared Library or target application.
• Merge as Move—This mode only processes members in the IsPrimary column that are set to “True” and ignores any others, so basically just looking at shared members.
• Replace—All new elements are added and all property updates are made. Then, any members or member relationships that are not specified in the source are deleted from the Shared Library or target application. This option would be used if you wanted to rebuild the dimension hierarchy.
There are two options available for Reorder Type
• Merge to Top —Places new imported child members at the beginning of the child list under their parent.
• Merge to Bottom—Places new imported child members at the end of the child list under their parent.
If “Reorder Existing Members” is selected then the action depends on what has been selected in “Reorder Type”
• If “Merge to Top” is selected, the first child member in the import source becomes the first child under the parent, all imported child members are sorted to match the order in the import source, and any existing members not in the import source will be pushed to the end of the child list in their existing order.
• If “Merge to Bottom” is selected, the last child member in the import source becomes the last child under the parent, all imported child members are sorted to match the order in the import source, and any existing members not in the import source will be pushed to the beginning of the child list in their existing order.
If you want a more detailed example have a look at the following section in the EPMA admin documentation – “Reordering Existing Member Examples”
The “Dimension Mapping” section provides the functionality to map each dimension source member property to a target member property in the Shared Library.
As I have created the source table columns with the same naming convention to the member properties in EPMA it is a direct mapping.
There is another parameter that can be set for each member that defines how existing property values are dealt with.
• Clear Before Importing – if selected values will be cleared out, therefore making values match the import source exactly. If not selected, values will be merged in with existing values and all existing values will remain.
• Allow Overwrites with Blank –if the source value is blank the target property will be overwritten with the default property, so for instance if selected and there was no value in the DataStorage column the default value “StoreData” will be applied.
No need to execute the profile as there is no metadata in the hierarchy tables yet, I will be covering loading the metadata in the next instalment.
Hi John!
ReplyDeleteGreat job sharing yout experience of ODI with us. I really apretiate it and learned a lot.
Regarding this post, i wonder if you can explain how to populate the interfaces, so that metadata can be loaded to ESSBASE and EPM applications.
Thanks again
Thank you John, really appreciated.
ReplyDeleteI was just passing by oracle by example explaining the same issue but it was not that clear as it is here.
Thank you and wish you a happy new year.