ODI-Creating Data Server

Learnhyperion.wordpress.com

Document: Oracle Data Integrator 11g (11.1.1) Creating data Server Description: (We are committed to provide THE BEST learning material to the new bees in Hyperion, OBIEE and ODI technologies. In the same series, this document describes the basic concepts of the ODI

Topolog Topology, y, Creati Creating ng data data Server Server, Physic Physical al schema schema and logical logical schema)

History: Version

Description Change

Author

Publish Date

0 .1

Initial Draft

Gourav Atalkar

6-July-2011

0 .1

Review 1st

Amit Sharma

10-July02011

Busine Business ss Intell Intellige igence nce soluti solution on Provid Providers ers

|

Creati Creating ng a Logica Logicall Schem Schema a for XML

1


Table of Contents 1)

Creating Creati ng XML data Server

3

2)

Creating Creati ng Excel Data Server

8

3)

Creating Creati ng RDBMS( RDBMS(Oracle Oracle)) data Server

15

4)

Creating Creati ng File technolo technology gy data Serve Serverr

21


|


2


1)Creating an XML Data Server

An XML Data Server Server correspond correspondss to one XML file that is accessible accessible through through your local local network. XML files are accessed through the Oracle Data Integrator Driver for XML. This JDBC driver loads loads the XML file hierarchic hierarchical al structure in a relationa relationall structure structure in a schema schema stored in memory memory to enabl enable e SQL queries queries throug through h JDBC. JDBC. It is also also able able to unload unload the relati relationa onall structure back in the XML File. You must have following information: • • •

The location of the DTD file f ile associated with your XML file The location of the XML file The name of the Root element of your XML file

Creation of the Data Server

Step: Step: 1) Select Select XML from from the Techno Technolog logy y list list view. Right click and select New Data Server.

Step: 2) Fill in the following fields in the Definition tab: •

Name: Name of the Data Server as it will appear in Oracle Data Integrator.


|


3


•

User/Password: Not used here.

Step: 3) Fill in the following fields in the JDBC tab: 1 2

JDBC Driver: com.sunopsis.jdb com.sunopsis.jdbc.driver.xml.Snp c.driver.xml.SnpsXmlDriver sXmlDriver JDBC URL: jdbc:snps:xml?[pro jdbc:snps:xml?[property=value&property=va perty=value&property=value...] lue...]

JDBC Driver Properties: Parameter f d

Values

re

ro

true true | false false

Description XML File location (relative or absolute) in UNC format. Use slash “/” in the path name and not backslash “\” in the file path. DTD File location (relative of absolute) in UNC format. Use slash “/” in the path name and not backslash “\” in the file path. If this parameter is missing, the driver will build the name of the DTD file from the XML file, replacing the ".xml" extension with “.dtd”. Name of the element to take as the root table of the schema. This value is case sensitive. This parameter can be used for reverse-engineering a specific message definition from a WSDL file, or when several possible root elements exist in a XSD file. if true, true, the the XML XML file file is open opened ed in rea read d only mode. mode.


|


4


s cs

true | false

Name of the relational schema where the XML file will be loaded. If this parameter is missing, a schema name is automatically generated generated from the file name. Load the XML file in case sensitive or insensitive mode. For case insensitive mode, all element names in the DTD file should be distinct. The case sensitive parameter is a permanent parameter for the schema. It CANNOT be changed after schema creation. Please note that when opening the XML file in insensitive mode, case will be preserved for the XML file.

Example: jdbc:snps:xml?f=../xml/GEO_DIM.xml&re=GEOGRAPHY_ jdbc:snps:xml?f=../ xml/GEO_DIM.xml&re=GEOGRAPHY_DIM&ro=false&cas DIM&ro=false&case_sens=true&s=GE e_sens=true&s=GEO O

Step: 4) Click test Connection button it will ask you for saving it press ‘ok’ then it will ask again for creating physical schema press ‘ok’


|


5


Creating a Physical Schema for XML

The Physical Schema will be a storage location for the tables associated with the XML file. Step: 1) Select the appropriate XML Data Server then right-click and select New Physical Schema. The Physical Schema window will appear.

Step: 2) Name the Schema Schema and Work Schema. Schema. Note that if you have named the schema with the s= property of the JDBC URL of the XML Data Server, you must use the same schema name here.

Step: 3) Save it.

Creating a Logical Schema for XML

A Logical Schema can be associated with only one Physical Schema in a given Context


|


6


Creating Model for XML:

Step: 1) Select Model tab in designer Navigator and select New model.

Step: 2) In Definition Tab Specify the following details.

Step: 3) Perform the reverse Engineering


|


7


2)Creating an Excel Data Server Prepare your Excel spreadsheet

First open up a Microsoft Excel spreadsheet, spreadsheet, we will need to define a named range. Step: 1) Open spreadsheet

Step: 2) Select FormulasDefine Name


|


8


Step: 3) Select the range for the data

Define an ODBC Data Source

Step: 1) ODI will use an ODBC connection to natively access Microsoft Excel. Select start Administrative Administrative Toolsdata Source (ODBC)


|


9


Step: 2) click on Add select the Excel Driver press ‘Finish’. Specify the data source Name and description. Click on ‘Select ‘ Select Workbook’

Step: 3) Specify the Excel file path Press ok. You should now see your new ODBC Data Source listed in the ODBC Data Source Administrator. Administrator.

Create a Data Server in Topology Manager

Step: 1) Open up Topology Manager and go to Physical Architecture. Right click on the Microsoft Excel technology and select New Data Server. In the Data Server window enter a name in the Name field.


|


10


Step: 2) Go to the JDBC tab. Select the Sun JDBC-ODBC Bridge in the JDBC Driver List.

Step: 3) In the JDBC URL template replace with the name of the ODBC Data Source you specified earlier. I used Excel data Server in this example.

Step: 4) Click on Test and make sure you get a successful connection.

Click OK. Step: 5) Go to Newly created data server and Right click select New select New Physical Schema


|


11


Step: 6) Verify newly created Physical Schema.

Create New Logical Schema:

Step: 1) Select Microsoft Excel in Logical Architecture tab Right click and select new logical schema. Specify the name and map it with Contexts.

Create Model for Excel

Step: 1) Go to the Designer Navigator Navigator select model tab and create new model for excel.


|


12


Step: 2) Specify the Name, Technology and Logical schema.

Step: 3) Select Reverse Engineering Tab. Select system table check box and go to Selective reverse Engineering tab.

Step: 4) Here select Selectiv Selective e reverse reverse Enginee Engineering ring and Objects Objects to reverse reverse Engineer Engineering ing check boxes.

Step: 5) Save it and perform reverse Engineer of this model.


|


13


Step: 6) Right click on data store select View data.


|


14


3)Create 3) Create an Oracle Oracle data server: Step: 1) Click the Physical Architecture tab. expand the Technologies node, select the Oracle node, and then right-click and select New Data Server.

Step: 2) Enter the following information on the Definition tab: 

Name: Oracle Data Server



Instance Name: ORCL



User: bisp



Password: password


|


15


Step: 3) Click the JDBC tab. Click the button at the right of the JDBC Driver field. In the window that appears, select Oracle JDBC Driver, and then click OK.

Step: 4) Click the button to the right of the JDBC URL field. In the window that appears, select the first URL, and then click OK.

Step: 5) Edit the JDBC URL to have the following:


|


16


URL: jdbc:oracle:thin:@localhost:15 jdbc:oracle:thin:@localhost:1521:ORCL 21:ORCL The JDBC tab should now appear as follows:

Step: 6) Test this data server:

Click the Test Connection button. Click Yes to confirm saving your data before testing the connection. In the Information window, click OK.

In the dialog box that appears, click the Test button. Click OK.

Create a physical schema for this data server:


|


17


Step: 1) Oracle bisp physical schema with the following parameters: 

Data Schema: ora_sales_dev ora_sales_dev



Work Schema: odi_staging



Default check box: Selected

Expa Expand nd the the Or Orac acle le node node.. Righ Rightt-cl clic ick k the the newl newly y crea create ted d data data serv server er,, Or Orac acle le Data Data Serv Server er,, and the then sele selecct New New Phy Physica sicall Schema. Step: 2) In the new window that appears, sele select ct Or Ora_ a_sa sale les_ s_de dev v from from the the Sche Schema ma (Schema)

drop-down list, and then select Odi_staging from the Schema (Work Schema) drop-down list list.. Veri Verify fy that that the the Defa Defaul ultt chec check k box box is sele select cted ed,, and and leav leave e all all the the othe otherr fiel fields ds unchanged. Click the Save button. Click OK in the Information window that appears.


|


18


Step: 3) Expand: Oracle > Oracle Data Server. The Physical s chema Oracle Physical Schema appears in the tree view:

Step: 4) Create a physical schema Oracle Data Server production for the Oracle Data Server data server:

• Data Schema: Schema: ora_sales_prod ora_sales_prod • Work Schema: Schema: odi_staging odi_staging • Default Schema: Schema: Not selected

Step: 5) Select the Oracle Data Server data server in the tree tree view, view, and then rightright-cli click ck and select select New New Physical Schema.

In the new window that appears, select ora_sales_prod from the Schema (Schema) drop-down list, and then select select odi_s odi_stag tagin ing g from from the Schema Schema (Work (Work Schem Schema) a)


|


19


drop- down list. Checks that the Default check box is not selected, and leave all the other fields unchanged. unchanged. Click the Save button. In the Information window, window, click OK

Create a logical schema: Create a logical schema, Oracle Bisp Logical, and map this schema to physical schemas, for the different contexts. 

Development Context: To the Oracle Data Server.ora_sales_dev Server.ora_sales_dev physical schema



Production Context: To the Oracle Data Server.ora_sales_prod physical physical schema



Global Context: To the Oracle Data Server.ora_sales_dev physical schema

Select the Logical Architecture tab and expand the Technologies node. Select the Oracle node, and then right-click and select New Logical Schema.


|


20


Step: 7) Enter the name of the logical schema Oracle data Server. To map this logical schema to physical schemas in different contexts, select the appropriate appropriate physical schema in front of each context, as shown in the following screen. Click the Save button.

This is how we define the Data server for oracle, create physical, logical schema and set context for these schemas.

4)Create 4) Create File Data Server Server Step: 1) Click Topology navigator. In Topology navigator, click the Physical Architecture tab, select Technologies > File. Right-click and then select New Data Data Server.


|


21


In Definition tab specify the Name for the Data Server and go to JDBC tab. specify the JDBC Driver and JDBC Url by selecting browse button.

Step: 3) Save the Data Server

Step: 4) Now in Topology navigator, click the Physical Architecture tab, select Technologies > File. Right-click, Demonstration Demonstration and then select New Physical Schema. Schema.


|


22


Step: 5) For the Directory (Schema) and Directory (Work Schema) fields, enter the path to the directory where your input flat files are located (C:\oracle\Mid C:\oracle\Middleware\Orac dleware\Oracle_ODI1\oracle le_ODI1\oracledi\Demo\Dem di\Demo\Demonstration onstration). ). Sele Select ct the the Defa Defaul ult t check box and click Yes in the Confirmation window, and then click the Save button. Close the editing window for your new physical schema. On the Information window that follows, click OK. Close the editing tab.

Step: 6) Verify that your new Physical schema was added under the Demonstration data server in the Technologies tree view.


|


23


Step: 7) Open Logical Architecture, navigate to Technologies > File, right-click File, and select New Logical Schema.

Step: 8) Enter the Logical Schema name: Demonstration Integration, Integration, and select the Physical schema Demonstration in all three contexts as shown here. Click Save and close the editing Window.

Step: 9) Verify that your new Logical schema was added under the File node in the Technologies tree view.


|


24

ODI-Creating Data Server

Recommend Documents