Oracle Data Integrator
EPMA Dimension Library Build Author: Manohar Anchan
The process below demonstrates how to build a dimension in EPMA 11.1.1 using Oracle Data Integrator (ODI) (10.1.3.5). In this example the ‘Resource Dimension will be built via a metadata text file names ‘Resource.txt
All work will be done on the ODI Server (server name xxxxx) via remote login. 1) Log into Topology Manager
2) Click on the ‘Contexts’ Tab on the left and then click on ‘Insert Contexts’.
3) For the name put in ‘EPMA_Build’. This will then be the context we use for the dimension build in this environment. Click ‘OK’.
4) You should now see the ‘EPMA_Build’ context of the left. We will use this context for all dimension builds. This will ensure were clear on the context for what we are trying to accomplish. Context can also be used to distinguish between Dev, Test and Prod too. For our example we will use it for this way.
6) The new logical schema box appears. For the ‘Name’ enter ‘NU_EPMA’. This name can be anything but for this example we will be using the name of the company and the technology used will simplify it when we go to assign it later. Leave the ‘Undefined’ in the Physical Schema for each Context. Click ‘OK’.
7) Navigate to the Physical Architecture tab and right click on Oracle and Select ‘Insert Data Server’
8. For the name enter ‘NU_EPMA_Target’. Add the oracle Instance, in this example it is “baoradvl1.nu.com”. Enter the EPMA username and password. Click on the ‘JDBC’ tab.
10) Click on the ‘…’ next to the JDBC Url and enter in the Host, Port and Sid. For this example enter in the following information. Next, click on the ‘Test’ Button.
Host: enter server name Port: 1521
Sid: enter appropriate Sid
12) If you have entered the Host, Port and Sid correctly then you should see a ‘Successful connection’ Screen appears. For Successful Connection Click ‘OK’, then click ‘OK’ on the DataServer
13) This screen should pop up. The ‘Name’ field will auto populate. Enter Schema and Work Schema. In this case, Schema and Work Schema is ‘EPMA’. Click on ‘Context’ tab.
14) Click the ‘Add Context’ button. For the name enter ‘EPMA_Build’. This will be the ‘Context’ we use for a dimension build in this environment. Select ‘NU_EPMA’ as the logical schema. Click ‘OK’.
15) Navigate to ‘Logical Architecture’ and right-Click on ‘File’ and Select ‘Insert Logical Schema’.
17) Navigate to ‘Physical Architecture’ and Right Click on the ‘File’ Technology and Select ‘Insert Data Server’.
18) Enter the following: Name ‘EPMA_Dim_Build’. Host: enter appropriate server name. Leave User and Password fields blank. Click on the ‘JDBC’ Tab.
JDBC URL: jdbc:snps:dbfile
Note: The button to the right will allow you to choose the many divers and url’s here. In this case we are using the driver specified however it could change depending on the environment you are in.
Click on the ‘…’ and select Sunopsis File JDBC Driver
20) Click on the ‘Test’ Button to validate the connection. If you have entered the drivers correctly you will see the ‘Connection Successful’ button appear. Click ‘OK’ then Click ‘OK’.
location where the Metadata files will reside to build your dimensions. For this example we will use a local location on the server where ODI is installed.
22) The name field will auto populate. Enter the path to the desired location for both ‘Directory’ Fields. In this example we will use D:\ODI_Load_Files. Click on the ‘Context’ Tab
23) Click on the Add Context button. Select the ‘EPMA_Build’ context and the ‘NU_EPMA_Build’ Logical Schema. Click ‘OK’.
24) Navigate to the Logical Architecture tab and click on the ‘+’ to expand ‘Sunopsis Engine’, Select Sunopsis_Memory_Engine and then Select ‘Edit’.
25) On the Definition Tab enter ‘Sunopsis_Memory_Engine_Default’ for the Context ‘EPMA_Build’. Click ‘OK’
26) Navigate to the Physical Architecture and right- Click on ‘Sunopsis Engine and click on the ‘+’ to expand ‘Sunopsis Engine’, Select Sunopsis_Memory_Engine and then select ‘Edit’.
27) Leave user and Password blank on the Definition Tab. Click on the JDBC Tab
28) On the JDBC Tab the JDBC Driver and the URL need to be specified. The values in our example provided by NEU IT
JDBC Driver: org.hsqldb.jdbcDriver
Click on ‘Test’ to validate the connection is successful. Click ‘OK’ on the Successful Connection window and then click ‘OK’ on the Data Server window.
30) Click the ‘Add Context’ button and select ‘EPMA_Build’ for Context and ‘Sunopsis_Memory_Engine’ for Logical Schema. Click ‘OK’.
31) Once you have created the logical and Physical schema, it is time to create a project and import the Knowledge Modules (KM.) to create a Project, you need to access the ODI Designer. Once in the Designer tool, Click on the Projects tab and then click on the ‘Insert Project’ button. It may ask you to login to Designer if it’s not already open. Enter ‘NU_EPMA_Build’ for name. Click ‘OK’.
32) You should see a ‘NU_EPMA_Build’ Project list with some subfolders.
33) To import KM’s expand the Knowledge Modules folder and right click on any of the folders under ‘Knowledge Modules’ and Select ‘Import Knowledge Modules’
34) The following screen appears. Click on the button on the right of the File Import Directory’ and Browse to the location of the KM’s. Typically these are in the ODI install directory. In our example it will be D:\Installation Files\ODI\oracledi\impexp directory. This directory has all KM’s regardless of Technology. You could just copy the Hyperion related KM’s to another directory and import form there instead.
35) Choose the following KM’s to import and validate they show up in the tree as follows. You now have your project created. Note you will not use all of these now but you need them in the future.
36) Navigate to the ‘Models’ tab in Designer and click the ‘Insert Model Folder’ button.
38) After you created your model folder you now need to create folders for your flat files (metadata) and the EPMA Dimension library. Right click on NU_EPMA and Select ‘Insert Model’.
39) Enter ‘NU_EPMA_Files’ for name. Select File for Technology.
41) After you create the model folder for your files, you now need to create a folder for your staging tables.
Click on Insert model folder and enter ‘NU_EPMA_Stage’. Click ‘OK’
43) Enter ‘NU_EPMA_Staging_Tables’ for name. Select Oracle for Technology. Select ‘NU_EPMA’ For Logical Schema. Select Oracle Default for Action group. Click on the ‘Reverse’ tab
44) Be sure to select ‘Standard’. Select ‘EPMA_Build’ for Context. Select ‘Selective Reverse’ tab.
45) Click on the Box Next to ‘Selective reverse’ and make sure that ‘New Datastores’ and Objects to Reverse. A list of all of the staging tables should appear.
46) Select only the tables that have the prefix ‘HS_’. You could select them all of the staging tables but for this example we will only need the “HS_*” tables.
Click on the ‘Reverse’ Button.
49) Enter ‘Resource’ in the Name field and Select the ‘…’ next to ‘Resource Name’ filed. This will allow you to select the file you want to use. In this example we will be using Resource.txt
50) Find and Select Resource.txt. Click on the ‘Open’ button. Select the Files tab.
Note: You will need to put the resource file in the D:\ODI_Load_Files Folder before this process
51) The Resource.txt file we are using is a comma delimited File with one Header Line. Therefore make the selections below.
File Format: Delimited
Heading (Number of Lines): 1
Field Separator: Other and Comma (,) Text Delimiter: “
Click Apply
52) You will notice that the Resource (Resource.txt) entry will appear under the ‘NU_EPMA_Files’ Model folder. Also there is lock that appears next to the file. You will need to remove the lock before you can proceed to the next step.
Right click on Resource (Resource.txt). Select ‘Locks’. Then select ‘Unlock’. Next, click on the Columns tab.
53) You will now notice that the Lock has been removed from Resource (Resource.txt). In the Columns tab Select Reverse. This action goes out and reverse engineers the resource.txt metadata file into your file model. So now ODI has a framework for your source file stored in the backend tables. Later you will use this file as your ‘Source’ to map with the interface tables which will be used to load the EPMA dimension library. Click ‘OK’
55) Enter or Select the Following Name: Resource
Optimization Context: EPMA_Build
Place an X next to Staging Area different from Target Select Sunopsis_Memory_Engine
Click on Diagram Tab
56) Click and Drag Resource (Resource.txt) to the Sources area of the Diagram Tab
57) Under NU_EPMA_Staging_Tables, locate HS_Generic_Hierarchy and Click and Drag to the Target Datastore area of the diagram Tab
58) When you drag the staging table to the Target data store this screen should appear. Click ‘Yes’
59) You will now notice that the Mapping has been filled out in the HS_Generic_Hierarchy. You will need to manually map the remaining fields between ‘Target’ and ‘Source’. In this example we will not perform any more mappings and just take defaults.
61) Drag the Horizontal bar over to the right until you see the Target (EPMA) Box.
Verify that the IKM select is ‘IKM SQL to SQL Append’. For the Option Change the DELETE_ALL Option to ‘Yes’. This option will clear out the staging tables
62) The Execution box appears. Select the EPMA_Build for Context and Select ‘OK’
64) Navigate to the Operator application by clicking on the operator button. In the operator application you verify that the job ran successfully.
65) Expand the Date field under the Hierarchical Sessions and expand today and verify that your interface has completed successfully. You know that by
the green check mark. Make necessary fixed and re-run if it fails for any reason. In this example the job completed successfully.
66) Now log into Oracle Enterprise Performance Management System Workspace and navigate to Dimension Library
67) Now you will need to create an “Interface Tables type profile to import the dimension members defined in the interface tables into the ‘Resource defined in the Dimensional Library.
68) You will then need to identify they type of profile you need to create and where you want the dimension to load. For this example use the information below
Profile name: ResourceLoad Import Type: InterfaceTables Description: NA
Application: Shared Library Data Source: Datasource
69) Remove all other dimensions in this profile by selecting the Blank member in each dimension row
70) Resource is a Generic dimension so in this case we will choose the Generic Row in the Interface area and Choose Resource in the drop down.
72) Next we will set up the Mapping Options. Click Next.
74) Select ‘Yes’.
76) The screen below shows the results of the job. To view the job results, click on the “Import Results” URL under attachments.
77)View and correct you file from the errors. In this example “ID” is a parent of “IV”, and “ID” is not in the file as a child of Resource. You will need to correct the error and reload the file through ODI and reload the profile.
78) Navigate to the Dimension library and to see the Resource Hierarchy.
79) Congratulations!!!! You have successfully built a dimension in the EPMA Dimension Library using ODI
