Docstoc

Deleting Essbase Dimension Members using ODI

Document Sample
Deleting Essbase Dimension Members using ODI Powered By Docstoc
					                                  http://odinetwork.blogspot.com/




Document:
                   Oracle Data Integrator 11g (11.1.1)
                 Deleting Essbase Member through ODI

Description:

This document is one our documents from ODI integration with Hyperion Product suite.
The objective of the document is to assist new bees to perform various tasks using ODI
with Hyperion Essbase, Planning and HFM. The document is deigned with very simple
and step by step approach to add, delete, and modify member properties in Essbase
outline.



History:
      Version              Description Change              Author                Publish Date

           0.1                  Initial Draft          Gourav Atalkar            28-June-2011

           01.                  Review 1st              Amit Sharma               1st Jul 2011




.




                               Oracle Data Integrator


      Business Intelligence Solution Providers   | learnhyperion.wordpress.com             1
                              http://odinetwork.blogspot.com/
Title                                                                    Page No.
                                      Method-1
 1) Requirement                                                               3


 2) Creating Hyperion Essbase Topology and Data Model                         4


 3) Creating File Topology and Data Model                                     14


 4) Creating Interface                                                         21


 5) Setting up Rule File                                                      24


                                        Method-2
 1) Requirement                                                               30


 2)   Setting up Environment                                                  32


 3) Creating Interface                                                        40


 4) Setting up Rule file                                                      42


 5) Creating Package                                                          46




  Business Intelligence Solution Providers   | learnhyperion.wordpress.com    2
                                   http://odinetwork.blogspot.com/
.


    1) Requirement:

    Using the sample ASO database ASOsamp.Sample, the objective is to remove from the
    Products dimension the member “Handheld/PDAs” and its children.
    Only two columns are required in the source to carry out the deletion process, the
    required information is the parent member and the member itself.




2) Creating Hyperion Essbase Data Server, Physical Schema, Logical
Schema and Data Model


       Business Intelligence Solution Providers   | learnhyperion.wordpress.com   3
                                   http://odinetwork.blogspot.com/
Step: 1) Go to StartProgram FilesOracleOracle Data Integrator explore ODI and login
to work repository




Step: 2) After successfully login to work repository go to Topology Navigator select
Physical Architecture Tab and select Technology and expand it. Go to the Hyperion Essbase
Right Click and select ‘New Data Server’.




Step: 3) In the definition tab enter the following details as shown in the picture below:




       Business Intelligence Solution Providers   | learnhyperion.wordpress.com       4
                                   http://odinetwork.blogspot.com/




Step: 4) Click on Save it will ask you to create physical schema press ‘OK’.




Step: 5) In Technology Tab go to Hyperion Essbase expand it and select newly create data
server Right Click and select ‘New Physical schema’.




Step: 6) In the definition tab enter the Essbase Application name to which you want to
connect.


       Business Intelligence Solution Providers   | learnhyperion.wordpress.com   5
                                   http://odinetwork.blogspot.com/




Step: 7) Click on Save it will ask you to specify the context press ‘OK’.




 Step: 8) Go to the Logical Architecture tab Expand Technologies and select Hyperion
Essbase Right-click and select ‘New Logical Schema’.




Step: 9) Give appropriate name to logical schema and set the Context to Hyperion Essbase
physical schema.




       Business Intelligence Solution Providers   | learnhyperion.wordpress.com   6
                                   http://odinetwork.blogspot.com/




Step: 10) Click on ‘Save’.




Step: 11) Go to the ODI Designer and create new Project with appropriate name.




Step: 12) Click on ‘Save’. You can check in the project tab




Step: 13) Expand project go to knowledge module right click and import following
knowledge modules.

RKM Hyperion Essbase

LKM Hyperion Essbase Metadata to SQL

IKM SQL to File Append




       Business Intelligence Solution Providers   | learnhyperion.wordpress.com    7
                                   http://odinetwork.blogspot.com/




Step: 14) Press Ctrl and select all three knowledge




       Business Intelligence Solution Providers   | learnhyperion.wordpress.com   8
                                  http://odinetwork.blogspot.com/




Step: 15) Now go to the model tab and create new model Folder.




Step: 16) Now go to the model tab and create new model.

      Business Intelligence Solution Providers   | learnhyperion.wordpress.com   9
                                   http://odinetwork.blogspot.com/




Step: 17) In the definition tab enter the details




Step:18) Go to the Reverse Engineer tab select customize check box and enter details as
follows:




       Business Intelligence Solution Providers   | learnhyperion.wordpress.com   10
                                   http://odinetwork.blogspot.com/




Step: 19) ‘Save’.




Step:20) Newly created Model will be available for Reverse Engineering go to Model tab
select Hyperion planning model and Right Click and select Reverse Engineers. Before
Reverse Engineer the models make sure your agent services are running if not start it with
following command:

C:\oracle\Middleware\Oracle_ODI1\oracledi\agent\bin>agent –NAME=localagent




Step: 21) Now you can reverse engineer it.


       Business Intelligence Solution Providers   | learnhyperion.wordpress.com   11
                                  http://odinetwork.blogspot.com/




                                            Press ‘OK’.

Step: 22) Go to the Operator Tab check whether the Execution run successfully or not.




       If all the checks are green that means your model reversed successfully.

Step: 23) Go to the Designer and open model tab expand Hyperion Essbase model again.
You can check all the dimensions are visible there from your Essbase application.




      Business Intelligence Solution Providers   | learnhyperion.wordpress.com    12
                                  http://odinetwork.blogspot.com/




3) Creating Source File technology Data server, physical schema,
Logical schema and model
Step: 1) Go to Topology Navigator select Physical Architecture Tab and select Technology
and expand it. Go to the File Right Click and select ‘New Data Server’.




      Business Intelligence Solution Providers   | learnhyperion.wordpress.com   13
                                   http://odinetwork.blogspot.com/




Step: 2) Go to JDBC tab specify following details




Step: 3) Save it.




Step: 4) In Technology Tab go to File expand it and select newly create data server Right
Click and select ‘New Physical schema’.




       Business Intelligence Solution Providers   | learnhyperion.wordpress.com   14
                                   http://odinetwork.blogspot.com/




Step: 5) In the definition tab enter the Directory location which contains the file to which
you want to connect.




Step: 6) Click on Save it will ask you to specify the context press ‘OK’.




  Step: 7) Go to the Logical Architecture tab Expand Technologies and select File Right-
click and select ‘New Logical Schema’.



       Business Intelligence Solution Providers   | learnhyperion.wordpress.com     15
                                   http://odinetwork.blogspot.com/




Step: 8) Give appropriate name to logical schema and set the Context to Text physical
schema.




Step: 9) Click on ‘Save’.




Step: 10) Now go to the model tab and create new model Folder.




       Business Intelligence Solution Providers   | learnhyperion.wordpress.com   16
                                   http://odinetwork.blogspot.com/




Step: 11) Now go to the model tab and create new model inside the Text File Model Folder.
In the definition tab enter the details




Step: 12) ‘Save’.




Step: 13) We need to create new data store for source text file. Select the model Right click
and select New Data Store. In Definition tab enter the name then click on Resource Name
browse button select you text file from open dialog box and press ‘Open’.

       Business Intelligence Solution Providers   | learnhyperion.wordpress.com      17
                            http://odinetwork.blogspot.com/




Business Intelligence Solution Providers   | learnhyperion.wordpress.com   18
                                   http://odinetwork.blogspot.com/
Step: 14) Go to file tab select file format as delimited set heading to 1 and field separator to
Comma (,).




Step: 15) Now go to Column tab and click on reverse engineer button.




Step: 16) Save it.




       Business Intelligence Solution Providers   | learnhyperion.wordpress.com         19
                                  http://odinetwork.blogspot.com/




4) Create Interface
Step: 1) Source is ready, now we will create the interface. Select same project that we have
created previously open it and select interface right click and new interface.




      Business Intelligence Solution Providers   | learnhyperion.wordpress.com      20
                                  http://odinetwork.blogspot.com/




Step: 2) Go to mapping tab and drag Product datastore from Text file model and drop it to
source area. Similarly drag Essbase Product datastore to target place. It will ask you for
auto mapping press ‘Yes’.




      Business Intelligence Solution Providers   | learnhyperion.wordpress.com    21
                                   http://odinetwork.blogspot.com/




Step: 3) Make sure you set the target Parent/Child columns to execute on the staging area as
the essbase technology has no JDBC capabilities.

Go to overview tab and select staging area different from target check box.




Step: 4) Now go to Flow tab where you specify the Knowledge module for the interface
these are the required knowledge module. If you have not imported it till now import it.

   i) LKM File To SQL

   ii) IKM SQL TO Hyperion Essbase (Metadata).




       Business Intelligence Solution Providers   | learnhyperion.wordpress.com     22
                                   http://odinetwork.blogspot.com/




Step: 5) In the flow set the target KM to “IKM SQL to Hyperion Essbase (Metadata)”
In the KM options provide an essbase load rule name (it doesn’t exist at the moment)
Turn on logging and error logging and provide a full path and filename for the logs, the error log
is required to make sure there were no failures in performing the dimension build.

Save the interface but don’t run it.




5) Setting up Rule File:

       Business Intelligence Solution Providers   | learnhyperion.wordpress.com           23
                                   http://odinetwork.blogspot.com/
Step: 6) Now we will login to the Essbase Administration Services [EAS]




Step:7) Once logged into EAS and have expanded the essbase server, right click the essbase
database and create a new load rule.




Step: 8) Open the data file for creating rule file.




       Business Intelligence Solution Providers   | learnhyperion.wordpress.com   24
                                   http://odinetwork.blogspot.com/




Step: 9) Go to the Data Source Property and change the delimiter to Comma.




Step: 10) Select “Options” > “Dimension Build Settings”, click the “Dimension Build Settings”




Step: 11) Double click the dimension you are removing the members from, in my example this
is Product.



       Business Intelligence Solution Providers   | learnhyperion.wordpress.com         25
                                  http://odinetwork.blogspot.com/




Step: 12) Use parent/child references” has to be selected as the build method as this is the
only way to perform hierarchy builds when using ODI.




Step: 13) Now for the important part in the member update section “Remove unspecified”
must be specified. Remove unspecified basically deletes the members in the selected
dimension that do not exist in the source, click OK.




      Business Intelligence Solution Providers   | learnhyperion.wordpress.com      26
                                  http://odinetwork.blogspot.com/




Step: 14) Next the columns in the rule need to be matched to the output from ODI. Select
Field properties > Dimension Build Properties.




      Business Intelligence Solution Providers   | learnhyperion.wordpress.com   27
                                   http://odinetwork.blogspot.com/




Step: 15) Save the rule file and specify the same name in the KM options.




Step: 16) The interface can now be executed within ODI.




       Business Intelligence Solution Providers   | learnhyperion.wordpress.com   28
                                  http://odinetwork.blogspot.com/




 Step: 17) Go to the Operator Navigator and check the execution log of the interface.




If the interface successfully completes it doesn’t mean the members were definitely
removed, the best place to check is to see if the error log generated contains any records.




                     The Outline file says that the Member is deleted

Method: 2

      Business Intelligence Solution Providers   | learnhyperion.wordpress.com     29
                                     http://odinetwork.blogspot.com/
    1) Requirement:
There are situations where you may not have the full dimension hierarchy Available so the
last method does not work for you.

The end solution for today is to provide a flat file in parent/child format with only the
members we want to remove from the outline. Once again I will be using the
ASOSamp.Sample essbase database and using ODI 11G.

Highlighted are the members that are going to be deleted from the product dimension.




Below is the flat file that contains the members that are going to be deleted in parent/child format.




    2)   Setting Up the Environment:




         Business Intelligence Solution Providers    | learnhyperion.wordpress.com                30
                                  http://odinetwork.blogspot.com/
Step: 1) We need to create new data store for source text file. Select the model Right click
and select New Data Store. In Definition tab enter the name then click on Resource Name
browse button select you text file from open dialog box and press ‘Open’.




      Business Intelligence Solution Providers   | learnhyperion.wordpress.com      31
                                   http://odinetwork.blogspot.com/




Step:2) o to file tab select file format as delimited set heading to 1 and field separator to
Comma (,).




Step: 3) Now go to Column tab and click on reverse engineer button.




       Business Intelligence Solution Providers   | learnhyperion.wordpress.com      32
                                   http://odinetwork.blogspot.com/




Step: 4) Save it.




       Business Intelligence Solution Providers   | learnhyperion.wordpress.com   33
                                   http://odinetwork.blogspot.com/
Step: 5) We want to be able to use this solution across other dimensions in the essbase
database instead of having to create an interface per dimension, so the next step is to create
a variable that can be used to define which dimension to use.




Step: 6) The products Datastore was duplicated, any of the standard dimensions could
have been duplicated. I say standard dimensions as a measures type dimension contains
additional columns.




Step: 7) The duplicated Datastore was renamed and the Resource Name set as the variable
that was created earlier which was #DELETE_DIM. This means I can pass the variable value
to set which dimension I want to use in the extract interface.




Step: 8) Next step is to create an interface that will extract the dimension members from
essbase.

       Business Intelligence Solution Providers   | learnhyperion.wordpress.com       34
                                  http://odinetwork.blogspot.com/




I am using the memory engine as the staging area as the complexity and size of the output
extract is not that large.

Step:9) In the mapping area the StandardDimension Datastore was dragged on to the
source, in the target I am using a temporary Datastore, as I am using the memory engine as
the staging area this means that a temporary table will be created in memory when the
interface is executed.




Step: 10) The temporary DataStore was named and two columns added, in the properties
area for each column a name was given and Datatype was set to VARCHAR (80 length as
this is the maximum length for essbase member names)

The target columns were mapped to the source, PARENT to ParentName and
MEMBERNAME to MemberName.




      Business Intelligence Solution Providers   | learnhyperion.wordpress.com    35
                                   http://odinetwork.blogspot.com/




The aim is to remove the members in the source flat file that was created earlier from the
members that have been extracted. this is we perform with the use of DataSets.

DataSets basically lets you have a group of source Datastores and these Datastores can be
merged into the target Datastore using operators such as UNION,UNION ALL, MINUS and
INTERSECT.

With this new functionality I can add a new DataSet that will be the source flat file and use
the operator of MINUS to compare the source flat file from the dimension member extract.




Step: 11) The existing DataSet was named as “ExtractProduct” and a new one created
called “LoadMinusProds”, the operator was set to MINUS.




Once a new DataSet has been created you will notice that tabs are generated to distinguish
between each set

       Business Intelligence Solution Providers   | learnhyperion.wordpress.com      36
                                  http://odinetwork.blogspot.com/
Step: 12) The flat file Datastore containing the members to delete was dragged on to the
source area, the columns were then mapped to the temporary target table.




If you look at the flow diagram you get more of an understanding of what is happening,
member information is extracted from essbase using the KM “Hyperion Essbase
METADATA to SQL” and loaded into a temporary table in the staging area (memory
engine). The flat file is loaded into a temporary table in memory.

These two temporary tables are compared using the MINUS operator and finally loaded
into the temporary in-memory table “PROD_EXTRACT”. The PROD_EXTRACT tables
should only contain the parent/child members that need to be kept in the dimension.




If you run the interface in simulation mode (another nice feature in 11G) and look at step
14 “… Insert new Rows” you can see the MINUS SQL in operation.



      Business Intelligence Solution Providers   | learnhyperion.wordpress.com    37
                                   http://odinetwork.blogspot.com/




Step: 13) In the LKM options for extract the dimensional information the
MEMBER_FILTER_CRITERIA was set to Descendants and MEMBER_FILTER_VALUE set to
the variable value stored in #DELETE_DIM (which is products), Descendants was chosen as
I didn’t want to include the dimension name in the extract as the extract includes a parent
of NULL and this causes an issue when running the dimension build later on.

Using the variable in the filter value option means the interface doesn’t need to be updated
if it is ever used against a different dimension all that is required is to set the variable.




Step:14) When the interface loads the subtracted records into the temporary table
PROD_EXTRACT the IKM options of CREATE_TARG_TABLE and DELETE_ALL were set to
true, this is because the temporary table may not exist in memory so create it if it doesn’t
and if it does exist delete all the records.

       Business Intelligence Solution Providers   | learnhyperion.wordpress.com      38
                                  http://odinetwork.blogspot.com/




Right, so now an interface exists that will load only the records we want to keep in a
dimension and store this in a temporary in-memory table.

3) Create Interface
Step: 1) Source temporary interface is ready, now we will create the interface. Select same
project that we have created previously open it and select interface right click and new
interface.




      Business Intelligence Solution Providers   | learnhyperion.wordpress.com     39
                                   http://odinetwork.blogspot.com/
Step: 2) Go to mapping tab and drag temporary Interface Extract from Project tab and
drop it to source area. Similarly drag Essbase Product datastore to target place. It will ask
you for auto mapping press ‘Yes’.

The interface that has just been created (EXTRACT) was dragged on to the source so the
temporary table Target_dataSource becomes the source.

The essbase dimension Datastore that was duplicated earlier is dragged to become the
target The target dimension that is used in the interface is set by the value stored in the
variable #DELETE_DIM




                                          Do not execute it

4) Setting up Rule File:
Step: 1) Now we will login to the Essbase Administration Services [EAS]




       Business Intelligence Solution Providers   | learnhyperion.wordpress.com      40
                                   http://odinetwork.blogspot.com/




Step:2) Once logged into EAS and have expanded the essbase server, right click the essbase
database and create a new load rule.




Step: 3) Open the data file for creating rule file.




       Business Intelligence Solution Providers   | learnhyperion.wordpress.com   41
                                   http://odinetwork.blogspot.com/




Step: 4) go to the Data Source Property and change the delimiter to Comma.




Step: 5) Select “Options” > “Dimension Build Settings”, click the “Dimension Build Settings”




Step: 6) Double click the dimension you are removing the members from, in my example this is
Product.



       Business Intelligence Solution Providers   | learnhyperion.wordpress.com         42
                                  http://odinetwork.blogspot.com/




Step: 7) Use parent/child references” has to be selected as the build method as this is the
only way to perform hierarchy builds when using ODI.




Step: 8) Now for the important part in the member update section “Remove unspecified”
must be specified. Remove unspecified basically deletes the members in the selected
dimension that do not exist in the source, click OK.




      Business Intelligence Solution Providers   | learnhyperion.wordpress.com     43
                                  http://odinetwork.blogspot.com/




Step: 9) Next the columns in the rule need to be matched to the output from ODI. Select
Field properties > Dimension Build Properties.




      Business Intelligence Solution Providers   | learnhyperion.wordpress.com   44
                                   http://odinetwork.blogspot.com/




Step: 10) Save the rule file and specify the same name in the KM options.




5) Creating Package
Step: 1) Create a package to put this entire step together.




Step: 2) Go to the Diagram tab and drag the Delete_Dim, temporary interface extract and
Interface int member delete and arrange them in following sequence.

       Business Intelligence Solution Providers   | learnhyperion.wordpress.com   45
                                   http://odinetwork.blogspot.com/




Step: 3) Save it and execute it.




After successfully executing the package you can go to the outline file and validate it the members from
the flat file have been deleted from the hierarchy.




       Business Intelligence Solution Providers     | learnhyperion.wordpress.com              46
                                   http://odinetwork.blogspot.com/




                      The Outline file says that the Member is deleted

If I want to use the package to delete members from a different dimension it is as easy as setting the
DELETE _Dim variable to a different dimension name and updating the flat file to include the members
that need to be deleted.




       Business Intelligence Solution Providers    | learnhyperion.wordpress.com             47