ODI 12.2.1: ASCII Files

1. Go to the Linux Desktop, and using either the File Browser or an editor from the command line, open OBE_Files/Complex_Files/Purchase_sample.txt file. Review its contents as shown below:

   

   
    6335722^Company One^First Street   999       San Jose       95129USCA650-801-6250   
           ^Oracle^Bridge Parkway 1600      Redwood Shores 94065USCA650-506-7000
    001|Oracle Data Integrator EE|20000,2,+40000+
    002|Application Adapter for ODI|10000,5,+50000+
    003|BPEL Process Manager Enterprise Edition|20000,2,+40000+
    004|BPEL Process Manager Standard Edition|10000,5,+50000+
    005|BPEL Process Manager Developer Edition|1000,20,+20000+#110000 
   		    

2. Start ODI Studio using the Desktop shortcut icon .

   

3. Click Connect to Repository. Log in to ODI Getting Started repository.

   

   

ODI is now started and ready to build nXSD schemas.

1. Go to Topology > Physical Architecture > Technologies > Complex File, right-click Complex File and select New Data Server.

   

2. In the Definition panel, name the Data Server Purchase Order.

   

3. In the JDBC panel, click Search () and select ODI JDBC Driver for Complex Files from the list.

   

4. Click Edit nXSD... to open the Native Format Builder wizard.

   

5. Enter PurchaseOrder.xsd as the File Name.

   Browse and select /home/oracle/Desktop/OBE_Files/Complex_Files as the Directory Name.

   

6. Select Complex Type in file type list.

   

7. In the File Description screen, browse and select /home/oracle/Desktop/OBE_Files/Complex_Files/Purchase_sample.txt as the File name.

   Enter http://xmlns.oracle.com/odi/complexfile/demo as the Target namespace.

   Enter invoice as the Root element.

   

8. Design the nXSD file using the Design Schema panel. Click the Add Complex Type icon ().

   

9. Double-click <new_complex_type> and rename it addressType.

   

1. Expand addressType, select C1 and click Edit ().

   

   Your panel will look wider, this has been trimmed for clarity. Set the following values:

   • Field Name = street1.
   • Style = fixedLength
   • Length = 15
   • Pad Style = tail
   • Padding = Single Space (which is the default)

   

2. Click addressType and click Add Element icon ().

   

   Select <new_element> and rename it street2. Click Edit ().

   

   Set the following values:

   • Style = fixedLength
   • Length = 10
   • Pad Style = tail
   • Padding = Single Space (which is the default)

   

3. Click addressType and click Add Element icon ().

   

   Select <new_element> and rename it city. Click Edit ().

   

   Set the following values:

   • Style = fixedLength
   • Length = 15
   • Pad Style = tail
   • Padding = Single Space (which is the default)

   

4. Click addressType and click Add Element icon ().

   

   Select <new_element> and rename it postal-code. Click Edit ().

   

   Set the following values:

   • Style = fixedLength
   • Length = 5
   • Pad Style = none
   • Padding = (blank) (which is the default)

   

5. Click addressType and click Add Element icon ().

   

   Select <new_element> and rename it country. Click Edit ().

   

   Set the following values:

   • Style = fixedLength
   • Length = 2
   • Pad Style = none
   • Padding = (blank) (which is the default)

   

6. Click addressType and click Add Element icon ().

   

   Select <new_element> and rename it state. Click Edit ().

   

   Set the following values:

   • Style = fixedLength
   • Length = 2
   • Pad Style = tail
   • Padding = Single Space (which is the default)

   

7. Click addressType and click Add Element icon ().

   

   Select <new_element> and rename it phone. Click Edit ().

   

   Set the following values:

   • Style = terminated
   • Terminated By = ${eol}

   

10. Select invoice and click Add Complex Type icon ().

    

11. Double-click <new_complex_type> and rename it line-itemType.

    

1. Expand line-itemType, select C1 and click Edit ().

   

   Set the following values:

   • Field Name = uid.
   • Style = fixedLength
   • Length = 3
   • Pad Style = none
   • Padding = (blank) (which is the default)

   

2. Click line-itemType and click Add Element icon ().

   

   Select <new_element> and rename it description. Click Edit ().

   

   Set the following values:

   • Style = surrounded
   • Surrounded By = | (the vertical bar or pipe)

   

3. Click line-itemType and click Add Element icon ().

   

   Select <new_element> and rename it price. Click Edit ().

   

   Set the following values:

   • Type = double
   • Style = terminated
   • Terminated By = , (comma)

   

4. Click line-itemType and click Add Element icon ().

   

   Select <new_element> and rename it quantity. Click Edit ().

   

   Set the following values:

   • Type = integer
   • Style = terminated
   • Terminated By = , (comma)

   

5. Click line-itemType and click Add Element icon ().

   

   Select <new_element> and rename it line-total. Click Edit ().

   

   Set the following values:

   • Type = double
   • Style = surrounded
   • Surrounded By = + (plus)

   

12. Select invoice and click Add Complex Type icon ().

    

13. Double-click <new_complex_type> and rename it partnerType.

    

1. Expand partnerType, select C1 and click Edit ().

   

   Set the following values:

   • Field Name = uid.
   • Style = fixedLength
   • Length = 7
   • Pad Style = tail
   • Padding = Single Space (which is the default)

   

2. Click partnerType and click Add Element icon ().

   

   Select <new_element> and rename it name. Click Edit ().

   

   Set the following values:

   • Style = surrounded
   • Surrounded By = ^ (caret)

   

3. Click partnerType and click Add Element icon ().

   

   Select <new_element> and rename it address. Click Edit ().

   

   Set the following value:

   • Type = addressType

   

14. Select invoice and click Add Complex Type icon ().

    

15. Double-click <new_complex_type> and rename it invoiceType.

    

1. Expand invoiceType, select C1 and click Edit ().

   

   Set the following value:

   • Field Name = purchaser.
   • Type = partnerType

   

2. Click invoiceType and click Add Element icon ().

   

   Select <new_element> and rename it seller. Click Edit ().

   

   Set the following value:

   • Type = partnerType

   

3. Click invoiceType and click Add Element icon ().

   

   Select <new_element> and rename it line-item. Click Edit ().

   

   Set the following values:

   • Type = line-itemType
   • Max Occurs = UNBOUNDED
   • Select Array
   • Cell Separator = ${eol}
   • Array Separator = # (hash)

   

4. Click invoiceType and click Add Element icon ().

   

   Select <new_element> and rename it total. Click Edit ().

   

   Set the following values:

   • Type = Double
   • Style = terminated
   • Terminated By = ${eol}

   

5. Click invoice (not invoiceType) and click Add Element icon ().

   

   Select <new_element> and rename it invoiceElt. Click Edit ().

   

   Set the following value:

   • Data Type = invoiceType

   

16. Review the XSD, scroll up and down to see it all. Click Test.

    

17. Click Play () to preview (on the right side) the XML file that will be created by the Complex File driver.

    

18. Using the Properties table in the JDBC panel of the Data Server set file to /home/oracle/Desktop/OBE_Files/Complex_Files/Purchase_sample.txt.

    

19. Click Test Connection and Save () the newly created Data Server.

    

You have successfully created an nXSD using the Native Format Builder Wizard. (There are other tools as well that you could have used.)

1. In Topology > Physical Architecture > Technologies > Complex File, right-click Purchase Order to create a New Physical Schema for the Purchase Order Data Server.

   

   

2. In Topology > Logical Architecture > Technologies > Complex File, right-click Complex File to create a New Logical Schema.

   

   

3. In Designer > Models, click the pull-down and create a New Model linked to this logical schema.

   

   

4. Reverse Engineer the Model.

   

5. Expand myComplex and right-click LINE_ITEM datastore to View Data.

   

   

You have created the Topology and Designer objects.

The sample XML file is geographic data (countries, regions, cities, and populations).

1. Go to the Linux Desktop, and using either the File Browser or an editor from the command line, open the /home/oracle/Desktop/OBE_Files/XML/GEO_DIM.xml file. Review its contents as shown below:

   

   
   <?xml version="1.0" encoding="UTF-8"?>
   <GEOGRAPHY_DIM>
       <country COUNTRY_ID="6" COUNTRY_NAME="Autralia">
           <region REGION_ID="72" REGION_NAME="Queensland">
               <city CITY_ID="63" CITY_NAME="Brisbane" POPULATION="505179"/>
               <city CITY_ID="64" CITY_NAME="Gold Coast" POPULATION="575174"/>
           </region>
           <region REGION_ID="70" REGION_NAME="South Wales">
               <city CITY_ID="61" CITY_NAME="Canberra" POPULATION="353288"/>
               <city CITY_ID="60" CITY_NAME="Sidney" POPULATION="890962"/>
           </region>
           <region REGION_ID="71" REGION_NAME="Victoria">
               <city CITY_ID="62" CITY_NAME="Melbourne" POPULATION="857478"/>
           </region>
       </country>
       <country COUNTRY_ID="2" COUNTRY_NAME="France">
           <region REGION_ID="31" REGION_NAME="French Alps">
               <city CITY_ID="24" CITY_NAME="Grenoble" POPULATION="488825">
                   <client ADRESS="12 allee Victor Hugo" AGE="42.0"
                       CLIENT_ID="204.0" FIRST_NAME="Martin" LAST_NAME="Christine"/>
               </city>
   
   <!-- (...many lines skipped for clarity...) -->
   		    

2. If ODI Studio is not started, start it now.

   

3. In Topology > Physical Architecture > Technologies > XML, right-click XML and create a New Data Server.

   

4. In the Definition panel, name the Data Server GEO_DIM.

   

5. In the JDBC panel, click Search () and select ODI JDBC Driver for XML from the list.

   

That was the easy part! Now comes the actual JDBC configuration...

The XML Data Server configuration is now done using the Properties table.

1. Scroll down in the list and click Browse ... in the Value field of the file row to Open an XML file.

   

2. Navigate to /home/oracle/Desktop/OBE_Files/XML and select GEO_DIM.xml.

   

3. Click Browse ... corresponding to the dtd Value field and select /home/oracle/Desktop/OBE_Files/XML/GEO_DIM.dtd.

   

4. Set root_elt to GEOGRAPHY_DIM to define the XML file root element.

   Set schema to ODIXMLSCHEMA.

   

5. To hold the XML data into a DB schema instead of the memory of the ODI agent, use the External DB Properties fields. Scroll down in the list and set dp_driver = oracle.jdbc.OracleDriver.

   Set dp_schema to ODIXMLSCHEMA.

   

6. Set dp_password to qFgaJhVx7tdruALdT6Sm1g== (encoded version of oracle).

   

   1. In Designer > Projects > (any project) > First Folder > Packages, create a New Package named EncryptMyPswd.

      

   2. Under Toolbox > Internet, add an OdiFtpGet tool step.

      

   3. Configure it by entering your password oracle.

      

   4. Click the Command tab, you should see the encoded string corresponding to oracle for your current repository.

      

7. Set dp_url to jdbc:oracle:thin:@localhost:1521/orcl.

   Set dp_user to system.

   

8. Click Test Connection and Save () the newly created Data Server.

   

The JDBC portion is successfully configured.

1. Under Topology > Physical Architecture > Technologies > XML > GEO_DIM, right-click to create a New Physical Schema for this Data Server.

   

   

2. Under Topology > Logical Architecture > Technologies > XML, right-click to create a New Logical Schema.

   

   

3. In Designer > Models, use the pull-down to create a New Model.

   

   

4. Go to Selective Reverse Engineering tab, select New Datastores and Objects to Reverse Engineer.

   

5. Click Reverse Engineer.

   

6. Expand myXMLgeo. Right-click COUNTRY datastore and select View Data.

   

   

7. Using a Terminal, connect to the Oracle Database using SQL*Plus and review the data at the DB level:

   
   [OS] sqlplus system/oracle 
   SQL> col country_id format a8 
   SQL> col country_name format a15 
   SQL> select * from ODIXMLSCHEMA.ODIXMLSCHEMA_COUNTRY; 
   
   COUNTRYORDER  COUNTRYPK COUNTRY_ COUNTRY_NAME	 GEOGRAPHY_DIMFK
   ------------ ---------- -------- --------------- ---------------
   	   0	      0 6	 Autralia		       0
   	   1	      1 2	 France 		       0
   	   2	      2 4	 Germany		       0
   	   3	      3 3	 Great Britain		       0
   	   4	      4 5	 Japan			       0
   	   5	      5 7	 Nederland		       0
   	   6	      6 1	 USA			       0
   
   7 rows selected.
   
   SQL> exit;  
   			

You have created the Topology and Designer objects.

1. If ODI Studio is not started, start it now.

   

2. In the Navigation panel, go to Topology > Physical Architecture > Technologies.

   

3. Under Physical Architecture > Technologies, right-click SOAP Web Service and select New Data Server.

   

4. Set Name: CountriesList.

   Set WSDL URL: http://www.webservicex.net/country.asmx?WSDL. (The trailing period is not part of the URL.)

   

5. Click Test Connection. On Test Connection, use Local (no agent) by clicking Test.

   

6. Right-click CountriesList and select New Physical Schema.

   

7. Set Service: country.

   Set Port: countrySoap.

   

8. Click Context tab and Add () a new Logical Schema: LSC_WS_COUNTRIESLIST linked to the Context: Global.

   

You have configured the Topology Physical and Logical Architectures.

1. On the Designer tab, expand Projects > Demo > Sales Administration > Packages. Right-click Packages and create a New Package.

   

2. Name it Invoke Web Service.

   

3. Create an OdiInvokeWebService tool step (Toolbox > Internet > OdiInvokeWebService).

   

4. In Properties, set the following paramters and values:

   • Step name: Get List of Countries
   • Context: Global
   • Logical Schema: LSC_WS_ COUNTRIESLIST

   

The basic parts of the tool step are configured. Next is the HTTP Analyzer.

1. On Properties > General, click HTTP Analyzer (a.k.a. "Advanced Editor").

   

2. In the Advanced Editor, set Operations: countrySoap.GetCountries(,).

   

3. In Properties, set the following paramters and values:

   • Storage Mode for Response File: New File
   • XML Encoding for Response File: UTF-8
   • Response File: /home/oracle/Desktop/OBE_Files/Web Service/CountriesList.xml
     (Since the file itself does not exist, you cannot navigate to it with browse ..., you must enter the whole path.)
   • Response File Format: XML

   

The package that invokes web service is created and configured.

1. Select the Invoke Web Service package and click Run ().

   

2. Review the execution in the Operator tab. Make sure there are no errors.

   

3. From the Linux Desktop, open the OBE_Files/Web Service folder and open CountriesList.xml to review its content.

   
    [OS]$  cd ~/Desktop/OBE_Files/Web\ Service/ 
    [OS]$  ll 
    total 16
    -rw-r-----. 1 oracle oinstall 15264 Feb  5 14:41 CountriesList.xml   
    [OS]$  more CountriesList.xml 
    <?xml version = '1.0' encoding = 'UTF-8'?>
    <GetCountriesResponse xmlns="http://www.webserviceX.NET">
      <GetCountriesResult><NewDataSet>
      <Table>
        <Name>Afghanistan, Islamic State of</Name>
      </Table>
      <Table>
        <Name>Albania</Name>
      </Table>
      <Table>
        <Name>Algeria</Name>
      </Table>
      <Table>
        <Name>American Samoa</Name>
      </Table>
      <Table>
        <Name>Andorra, Principality of</Name>
      </Table>
      <Table>
        <Name>Angola</Name>
      </Table>
    --More--(6%) 
   		    

You have tested the package and examined the returned file.