How to query a whole schema or even database?! This tip will save you hours and hours of boring work.

I really like this tip especially its simplicity. It has saved me hours and hours of mind numbing work in the past.

The use case

Imagine the following situation. You are tasked to complete a source to target map. With the help of various source system SMEs, legacy data models, and the data dictionary of the source database you have been able to complete 99% of the mappings. However, there is that one field that is nowhere to be found in the source system (there’s always one). The business users have given you some sample values for this field. Wouldn’t it be nice to run a query such as SELECT column_name FROM schema/database WHERE value = ‘Sample Value’.

I will show you how you can achieve something similar with ODI.

The trick is to dump the content of your schema or database into a folder and then use grep your Windows search or whatever to search for your sample value.

In ODI this is extremely easy. Using the Command on Source/Target functionality it can be completed with two lines of code.

An example

Our objective is to find the table where ODI stores Option values for interfaces.

In a first step we create the procedure that dumps the content of the ODI schema to a folder

Command on Source

We query the data dictionary for those tables that are owned by the ODI work repository user. If you want to dump out the whole database just get rid of the WHERE clause.

SELECT table_name,owner from all_tables WHERE owner = ‘<name of your work schema>’

Command on Target

We use the tool OdiSqlUnload to unload those tables from the Command on Source recordset. For each table we create a separate text file.

OdiSqlUnload “-FILE=C:\unload\#table_name.txt” “-DRIVER=oracle.jdbc.driver.OracleDriver” “-URL=<your connection>” “-USER=#owner” “-PASS=<your password>” “-FILE_FORMAT=VARIABLE” “-FIELD_SEP=;” “-ROW_SEP=\r\n” “-DATE_FORMAT=yyyy/MM/dd HH:mm:ss” “-CHARSET_ENCODING=ISO8859_1″ “-XML_CHARSET_ENCODING=ISO-8859-1″

SELECT * FROM #table_name

In a second step we enter a dummy value for one of our interfaces. This will be the value that we will search for in the dump.

Our dummy value is XXXXXX.

Next we run the procedure. This will dump the table contents into files.

Finally, we use Windows Search, grep or similar to find the file where our sample value is stored.

Windows search comes back with one result. The last column of table SNP_UE_USED, which is I_TXT_VALUE.

I am sure you can think of many more use cases for this. Of course, you can make all of this more sophisticated and wrap it up in a package with options to run against different databases such as MS SQL Server etc.

For very big databases you will need to watch performance. You may want to only extract a subset of tables and search for your sample value there before moving on to the next set and so on.

Uses of the modulo operator: How Oracle mod can make your life easier.

You may ask, what the heck is modulo? Well, below is an easy to understand definition.

“Modulo basically means keep taking the second number away from the first number. When you can’t do it any more without going into negative numbers, whatever’s left is the answer”

SELECT MOD(6,3) FROM DUAL;
 6-3=3
 3-3=0 (remainder is 0)

SELECT MOD(3,6) FROM DUAL;
 3-6=-3 (result is negative so remainder is 3)

SELECT MOD(5,3) FROM DUAL;
 5-3=2
 2-3 (result is negative so remainder is 2)

Ok. That is great. But how can we actually benefit from this.

What are common use cases?

Use case 1: Wrap values such as in a clock, e.g. convert seconds to hours, minutes, seconds.

SELECT FLOOR(10000/3600) || ':' || FLOOR(MOD(10000/60,60)) || ':' || MOD(10000,60)  FROM DUAL;

 Use case 2: Finding even or odd numbers

SELECT 10,CASE WHEN mod(10,2) = 0 THEN 'even' else 'odd' END FROM DUAL
 UNION
 SELECT 9,CASE WHEN mod(9,2) = 0 THEN 'even' else 'odd' END FROM DUAL;

Use case 3: Expressing something in decimal form

SELECT FLOOR(7/5) || ' + ' || MOD(7,5) || '/5' FROM DUAL;

Use case 4: Distribute a dataset into buckets in a round robin fashion

An example would be to update only every second or third record in a table that contains a sequenced list of items. If you don’t have a sequence you can use rownum.

SELECT MOD(object_id,2), x.* FROM all_objects x  WHERE MOD(object_id,2) = 1 order by object_id;

Selecting 2/3 of records

SELECT MOD(object_id,3), x.* FROM all_objects x  WHERE MOD(object_id,3) in (1,2) ORDER BY object_id;

Use case 5: Get last M digits from a number

Get the last digit:

SELECT MOD(98,10) FROM DUAL;

Get the last two digits:

SELECT MOD(980,100) FROM DUAL;

and so on.

Let me know how you use the mod operator to make your life easier.

Best practice of organizing interfaces and data stores into projects and models in ODI

Have you ever wondered what the best way is to structure your objects in ODI into projects? Look no further. I will outline what works well for an Enterprise Data Warehouse.
Let’s assume you follow the Oracle reference architecture for data warehousing and you have a couple of source systems, a staging area, a foundation layer (core data warehouse), and a bunch of data marts.
For each of these layers we will create a model folder and a project (minus the source layer for projects.
We should end up with a structure similar to below.

Staging

You can further subdivide or group the Source System model by type of technology, e.g. File, MS SQL, Oracle, XML etc.

Similarly, you should subdivide your stage model into various sub-models based on source.

The same applies to the Stage project

EDW

Now we come to the interesting part. The EDW should be structured based on the subject areas in your Enterprise Data Model. If your organization is not mature enough and does not have one then it should get one asap (easier said than done). In the meantime, structure ODI based on the analysis performed for the data warehouse.

Each subject area in your EDM gets its own folder in the EDW project. Each entity in the EDM has a home in one of the subject areas. The interface that populates the corresponding ODI data store will go to the corresponding ODI project folder.

Similarly you structure your EDW model into sub-models corresponding to the EDM subject areas.

Data Marts

You could take the same approach for the data marts as for the EDW. However, as your dimensions are de-normalized and may span multiple EDM entities rooted in different subject areas my preference is to split out the Data Marts project into a Dimension, Facts, and Aggregate Facts folder.

One note at the end: You find statements out there claiming that you should never exceed 300 objects in a project. I am not sure where these come from. I have never seen any issues exceeding this number. If it was the case then this would be a severe limitation in ODI.

ODI snippets: Generating ANSI compliant Joins in Oracle

It’s great to see that the Oracle technology in ODI 11g now supports ANSI compliant JOINS.

However, when you create an interface that joins one or more tables this is not the default option.

To enable this you need to click on the Join icon between your tables and select checkbox ‘Use Ordered Join Syntax’.

ODI 11g in a Services Oriented Architecture. Part IV. Consuming a SOAP OBIEE web service using ODI 11g

This post completes our series on using ODI in a web services environment.

In part one of this series (Oracle Data Integrator in a Services Oriented Architecture) I had laid the groundwork and explained the basics of a SOA environment and how ODI fits into it. In part two we then looked at creating and deploying data services with ODI 11g in Weblogic. In part three we looked at how to consume data services using ODI 11g.

In this last example we will examine how we can invoke Oracle Business Intelligence Session-Based Web Services to query the Oracle Business Intelligence Presentation Services. The Oracle Business Intelligence Presentation Services allow us (amongst various other things) to retrieve catalog information such as user names, groups, object privileges etc.

The services and methods that are available can be retrieved from a WSDL at the following location

http://localhost:9704/analytics/saw.dll/wsdl/v6

You can find a full list of all the methods and a description in the documentation

For the purpose of this tutorial we will retrieve the members of a given OBIEE catalog group.

Oracle Business Intelligence Session-Based Web Services require a valid Oracle Business Intelligence session ID to be passed as a parameter.

In a first step we will generate this session ID.

In ODI we create a new package, name it OBIEE and add an OdiInvokeWebService tool.

Next we highlight the OdiInvokeWebService tool and click the Advanced… button. This will launch the ODI web service client. Next we supply the WSDL URL, e.g. http://localhost:9704/analytics/saw.dll/wsdl/v6
and click the Connect to WSDL button. This will display all of the available web services and methods.

From the web services dropdown we select the SAWSessionServiceSoap service and highlight the logon method. In the Editor pane we submit an OBIEE username and password with SOAP Access privileges and click the Invoke Web Service button. This will return a new OBI session ID. Close the ODI web service client by clicking OK.

As parameters for the OdiInvokeWebService tool we then submit the following additional parameters.

Storage Mode for Response File: NEW_FILE
File Encoding for Response File: UTF8
XML Encoding for Response File: UTF-8
Response File: C:/obiee_session_id.xml
Response File Format: XML

Now we are ready to execute the Package using our local agent.

Once the execution of the package has finished successfully we should find the obiee_session_id.xml file on our local C:\ drive

We are now ready to extract the session ID from the XML using ODI. As in the previous example we will have to create a new physical and logical data server and a new model for this XML file.

In ODI Studio Topology insert a new XML data server in the Physical Architecture.

In the JDBC Driver field submit com.sunopsis.jdbc.driver.xml.SnpsXmlDriver and for JDBC Url submit jdbc:snps:xml?f=C:/obiee_session_id.xml

Next click the Test Connection button and test the connection with the Local Agent.

This will throw an error. We have come across ODI bug 9159263, which is documented on the My Oracle Support site in note 971754.1. In summary the ODI XML driver can not parse XML files that contain any xsi: references. The note suggests using a workaround and deleting any references to xsi: in our XML.

Let’s edit our XML and remove any instances of xsi. Your XML should look similar to the one in the figure below. Later when we extract information from the XML file in our package we will write some Jython code to replace any xsi: instances in the XML file at runtime.

Now let’s retest the connection to the XML.

Next we create the physical schema

Then we create the logical data server XML_OBIEE_SRC

Next we create a model for the XML_OBIEE_SRC logical schema.

Finally we reverse engineer the model

We are now ready to assign the session ID to an ODI variable. Create a new variable V_OBIEE_SESSION_ID. As the schema for the variable choose XML_OBIEE_SRC and for the query supply ‘select sessionid_data from OBIEE.SESSIONID’

As mentioned earlier, before we can add the ODI variable to our package we will first need to create a step that removes any xsi: instances from the obiee_session_id.xml file. We will use Jython to accomplish this.

We create an ODI procedure XSI_REPLACE and add one step “replace xsi” to it. As Technology, we select Jython.

s = open("").read()
s = s.replace('xsi:', '')
f = open("", 'w')
f.write(s)
f.close()

Next we create an ODI option FILE_PATH. The procedure we have just created will take the path to an XML file passed in via the option and replace any occurrence of xsi:

Next we will add our new procedure and the ODI variable we created earlier on to our OBIEE package. We also enter the path to the c:\obiee_session_id.xml file into the option field as per figure below.

In a final step we will add another OdiInvokeWebService tool, which we will use to retrieve the members of a given group.

As per figure below add the following piece of code to the XML Request parameter. The name BI_RPT is the name of the catalog group in OBIEE that we will retrieve the members from. You need to replace this with a catalog group that exists in your own OBIEE environment. As account type we pass in 1 for accounts of type group. As sessionID we pass in the ODI variable #V_OBIEE_SESSION_ID that we populated in an earlier step in our package.

   
      BI_RPT
      1
      
   
   
   #V_OBIEE_SESSION_ID

The values for the other parameters are

WSDL URL: http://localhost:9704/analytics/saw.dll/wsdl/v6
Port Type: SecurityServiceSoap
Operation: getMembers
Storage Mode for Response File: NEW_FILE
File Encoding for Response File: UTF8
XML Encoding for Response File: UTF-8
Response File: C:/OBIEE_group_members.xml
Response File Format: XML

Leave all the other parameter fields blank.

We are now ready to execute our package and retrieve the members of the BI_RPT catalog group.

We execute the OBIEE package with our local agent

As you can see from the figure below, our package executed successfully.

Let’s have a look at the output of the XML. As you can see from the figure below there is one user in our given catalog group.

Congratulations. We have completed the walkthrough. You now know how you can use ODI in a SOA environment and should be able to query SOAP and RESTful web services using ODI.

ODI work repository documentation

I see a lot of requests on the Oracle forums for ODI work repository documentation. It can also be downloaded from MOS (Note 579751.1).

It only covers ODI 10g, but the data models are similar.

Tom Kyte in Dublin take aways

Today I had the pleasure to listen to Tom Kyte talk about:

- Big Data and the Oracle perspective and tool set on it
- Database stuff: Statistics
- More database stuff: compression & partitioning

The most interesting part of the speech was the Oracle view on big data. The strategy is coherent and makes a lot of sense. However, I am not convinced why you would ever pay licenses for a NoSQL database or Hadoop? But as I said the strategy itself is sound from my point of view.

There were also one or two things I took away from the database agenda:

- For the best compression results you want to have the columns that share certain values to be sitting beside each other. That way they are more likely to end up on the same database block and compression will be applied to more values. Really only something to take into account for huge tables where you have similar values across columns and you want to squeeze out the last bit of performance.
- CTAS is less expensive than Delete when high enough number of rows are deleted from a table.

The other good thing about the event was to meet up with some of the Dublin Oracle ACEs Marcin Przepiorowski and Brendan Tierney. And of course I also had a pint or two as it was a lovely day in Dublin.

Copying and moving objects between projects in ODI

One of the really annoying things about ODI is that it is not easily possible to move or copy objects between projects. Below is a step by step guide you can follow to copy or move objects between projects. We will take the object of type package as an example as this is the most complicated and covers off any of the others as well. The method shown uses Duplication mode import. While this method is not recommended by Oracle support. I never had any issues with it.

So let’s get started:

1. Migrate any of the variables used inside the package using Export and Import (Duplication Mode). A variable with the same name may already exist in the target project. This means you have to rename the variable and fix any references to it in interfaces, procedures, packages, and the Topology (the details on this follow below).

2. Migrate any of the project functions and project Knowledge Modules used using Export and Import (Duplication Mode)

For the KMs consider using global KMs instead

3. Export the root folder (with child components) of where the package is located. The reason for this is how Duplication mode import in ODI works. Basically it does not recreate internal IDs for objects that are part of the export XML. This strategy is used to minimise the number of missing references and as a result the amount of work we need to do.

4. Next we import the exported folder into our new project.

Note the import needs to be made in Duplication Mode.

5. Next we move (drag and drop) the object(s) we need from our imported folder to our target folder in the new project.

6. Next we can delete the imported folder

7. Any missing references to Knowledge Modules need to be recreated for all of the interfaces used in the package

Note: This also applies to all of the options for each KM.

If variables are referenced using the old project code in any of the KMs or you had to rename a variable during import then these need to be updated as well

8. Update references to any variables or project functions used in procedures

If variables are referenced using the old project code in any of the Procedures or you had to rename a variable during import then these need to be updated as well

9. Update references to any variables or project functions used in other variables

If variables are referenced using the old project code in any of the Variables or you had to rename a variable during import then these need to be updated as well

10. Update references to any variables used in the Topology, e.g. as part of physical data servers

11. Update references to any variables or project functions used in other project functions

12. Next we need to replace any missing references inside the package

These are typically only references to variables unless your package also references procedures etc. from outside the root folder that we exported/imported

We need to recreate those links by deleting the variables in the package and adding them again. Make sure that you select the correct Variable Type when recreating the variable in the diagram (Refresh Variable, Declare Variable etc.).

Open the legacy package for a side by side comparison to minimise mistakes.

13. If you reference scenarios in your package and you pass variables to these scenarios you will also need to repoint the variables in the Additional Variables tab for this scenario

14. Replace and fix references to new markers

15. Delete any scenarios for the legacy object(s) and then for the migrated object(s) if applicable.

16. Generate scenario for the migrated object(s)

17. Export legacy objects for backup (optional, if you are the cautious type)

18. And finally: Delete any of the legacy objects you migrated.

Get a list of all of your ODI objects including the path in your project or model

Below is a query that will retrieve all of the ODI objects in your project and model. It will also spit out the path where they sit in your hierarchy. Very useful if you need to know which objects have changed over the last week or if you want to track down one of the thousands of objects in your ODI projects and models.

WITH obj as(
SELECT i_package i_instance, i_folder, NULL i_project, pack_name obj_name,3200 obj_type, 'Package' obj_type_name, last_date, last_user FROM SNP_PACKAGE
 UNION ALL
SELECT i_pop i_instance, i_folder, NULL i_project, pop_name obj_name,3100 obj_type, 'Interface' obj_type_name, last_date, last_user FROM SNP_POP
 UNION ALL
SELECT i_trt i_instance, i_folder, i_project, trt_name obj_name,3600 obj_type, CASE trt_type WHEN 'U' THEN 'Procedure' ELSE 'Knowledge Module' END obj_type_name, last_date, last_user FROM SNP_TRT
 UNION ALL
SELECT i_var i_instance,  NULL i_folder, i_project, var_name obj_name, 3500 obj_type, 'Variable' obj_type_name, last_date, last_user FROM SNP_VAR t
 UNION ALL
SELECT i_table i_instance, i_sub_model i_folder, i_mod i_project, table_name obj_name, 2400 obj_type, 'Table' obj_type_name, last_date, last_user from snp_table t
)
,fd (i_folder, i_project, folder_name, folder_path, lv) AS(
SELECT i_folder, i_project, folder_name, folder_name folder_path, 1 lv
  FROM snp_folder
 WHERE par_i_folder IS NULL
 UNION ALL
SELECT tf.i_folder, tf.i_project, tf.folder_name, fd.folder_path||'\'||tf.folder_name, fd.lv+1
  FROM snp_folder tf JOIN fd
    ON fd.i_folder = tf.par_i_folder
)
,mpl as (
SELECT sm.i_smod i_mc, 'sm' typemc, COALESCE(sm.i_smod_parent,sm.i_mod) i_mp, NVL2(sm.i_smod_parent,'sm','m') typemp, sm.smod_name name --, m.i_mod_folder
  FROM snp_sub_model sm
 UNION ALL
SELECT i_mod, 'm' typ, i_mod_folder, 'mf', mod_name
  FROM snp_model m
 UNION ALL
SELECT i_mod_folder, 'mf', par_i_mod_folder, 'mf', mod_folder_name FROM snp_mod_folder
)
,mp (i_mc, typemc, i_mp, typemp, model_tech, model_path, lv) AS(
SELECT i_mc, typemc, i_mp, typemp, name tname, name model_path, 1 lv
  FROM mpl
 WHERE i_mp IS NULL
 UNION ALL
SELECT mpl.i_mc, mpl.typemc, mpl.i_mp, mpl.typemp, mp.model_tech, mp.model_path||'\'||mpl.name model_path, mp.lv+1 lv
  FROM mpl JOIN mp
    ON mpl.i_mp = mp.i_mc AND mpl.typemp=mp.typemc
)
SELECT obj.i_instance, OBJ_NAME, CASE WHEN COALESCE(project_name,mp.model_tech) IS NULL THEN 'Global ' || OBJ_TYPE_NAME ELSE OBJ_TYPE_NAME END obj_type_name
      ,obj.last_date
      ,obj.last_user
      ,COALESCE(project_name,mp.model_tech) project_model
      ,COALESCE(fd.folder_path,mp.model_path) path  
  FROM obj
  LEFT OUTER
  JOIN fd
    ON fd.i_folder = obj.i_folder AND obj_type_name!='Table'
  LEFT OUTER
  JOIN mp
    ON mp.i_mc = obj.i_folder AND obj_type_name='Table'
  LEFT OUTER
  JOIN snp_project p
    ON p.i_project = COALESCE(obj.i_project, fd.i_project)
  LEFT OUTER
  JOIN snp_model m
    ON m.i_mod = obj.i_project

LISTAGG with CLOB? String aggregation exceeding 4000 characters with XMLAGG.

We all know that the Oracle LISTAGG function does not support the CLOB datatype. In my opinion this is a severe limitation of LISTAGG (and BTW also of PIVOT and UNPIVOT etc.).

So what are your options? One option is to create your own user defined aggregate function. I found such an example on the Oracle forums.

However, if you want to use pure SQL to achieve the same, we can leverage the XMLAGG SQL/XML functionality built into the Oracle database.

As per documentation: “You use SQL/XML standard function XMLAgg to construct a forest of XML elements from a collection of XML elements”

Below is an example to demonstrate how this works. For demonstration purposes I don’t exceed the 4K character limit in the example below. However, I guarantee you that it works with >4K strings as well.

SELECT 
   table_row_id,
   DBMS_XMLGEN.CONVERT(EXTRACT(xmltype('<?xml version="1.0"?><document>'||XMLAGG(XMLTYPE('<V>'|| DBMS_XMLGEN.CONVERT(data_value)|| '</V>')).getclobval()||'</document>'), '/document/V/text()') .getclobval(),1) AS data_value
FROM (
   SELECT 1 table_row_id,'abcdefg>' data_value FROM dual
   UNION ALL
   SELECT 1 table_row_id,'hijklmn' data_value FROM dual)
GROUP BY
   table_row_id;

Let’s have a closer look at the nested stuff in this query.

In a first step we escape the data into its XML equivalent

DBMS_XMLGEN.CONVERT(data_value)

‘abcdefg>’ ‘becomes abcdef&lt;g’

Next we create an XMLELEMENT for each row and convert the result to XML data type

XMLTYPE(''|| DBMS_XMLGEN.CONVERT(data_value)|| '')

Then we do the XML aggregation

XMLAGG(XMLTYPE(''|| DBMS_XMLGEN.CONVERT(data_value)|| '')).getclobval()

In a last step we create a well formed XML document, extract the text value from the XML elements and unescape the text.

DBMS_XMLGEN.CONVERT(EXTRACT(xmltype(''||XMLAGG(XMLTYPE(''|| DBMS_XMLGEN.CONVERT(data_value)|| '')).getclobval()||''), '/document/V/text()') .getclobval(),1)