Data Integration and Management

Recently I just posted an article on BeyeNETWORK, entitled Connecting Visibility to Value: Data Integration and Business Intelligence. The article summarizes five important factors that are critical for Business Intelligence platforms:

- Optimized data movement
- Data quality
- Real-time and consistent data
- Next generation interoperability with BI systems
- Actionable BI

Why are these factors critical? Check out the article for more, but here's a small teaser for you:

The strongest BI offerings embed versatile data integration solutions that increase the value of the information delivered to the business user. Optimizing data integration within a BI solution delivers consolidation across complex applications, clean and consistent data, real-time data access and actionable BI. Data integration and business intelligence are essential technologies to support enterprise-wide performance management, leveraging operational data to create smart, agile and aligned organizations that are achieving management excellence.

The posts in this series assume that you have some level of familiarity with ODI. The concepts of Context, Topology, Logical Architecture and Physical Architecture are used here assuming that you understand these concepts in the context of ODI. If you need more details on these elements, please refer to the ODI Tutorial for a quick introduction, or to the complete ODI documentation for detailed information on these concepts.

The concept of Contexts in ODI is both very powerful and very convenient. Over the years, I have seen too many people ignore this feature, or not use it properly – hence not fully taking advantage of the flexibility offered by the tool. Hopefully, this brief discussion on the subject will help with a better overall utilization of this feature.

1. THE ISSUE AT HAND – AND THE ODI WAY TO SOLVE IT

A typical challenge for any code that accesses remote systems is to update the connection parameters to access these without disrupting the code that has been developed. In particular in data integration solutions, when developers promote their code from development to QA and then to production, any modification of the code could alter its quality.

Many software solutions will provide parameter files that can be edited, or variables and parameters than can be changed to reflect the change of environment. The downside of these solutions is that maintenance is performed outside of the tool’s control, and leaves the door open for mis-configuration.

The architecture of ODI has been defined with this issue in mind. To solve this problem, developers will never have to know where they are physically connecting: the administrator will give them an alias (say ORACLE_SALES) and that will give them the connection they require. The details of the connection information is entered and managed by the administrator, through the GUI. This will allow the administrator to validate the connection strings and to check the validity of all environments: missing connections can be easily identified, different environments can be compared, etc.

As developers use the metadata to design the transformations, they have a limited need to access the actual systems. They will need to access the actual systems in two cases: to retrieve the Metadata (the reverse engineering operation) and to run the data integration processes. In the QA and production environments, we will need to access the systems as well to run the data integration jobs.

When metadata is imported from actual system, or when code is about to be executed, ODI will ask for the selection of a Context to define the execution environment.

The following screenshots show where ODI will prompt you for a context, and how you can switch from one execution context to the next.

This of course assumes that you have enough security privileges to do so. Different users may have privileges to run the code in one environment and not in the other. For instance, QA engineers may not have the right to execute the processes in the Development environment or in the Production environment. Or if contexts define different company branches, employees from one branch would not be allowed to run code in a different branch.

2. CREATION AND MANAGEMENT OF CONTEXTS

The contexts in ODI are basically labels that you can create as needed. ODI comes with only one context called Global. Typical implementations would actually have three contexts: Development, QA, Production. As mentioned earlier, other implementations could define contexts for different branch or anything that identifies different execution locations.

Contexts will be defined in the Topology interface, along with 2 other types of objects: Logical Schemas and Physical Schemas. In the Topology GUI, you will find three corresponding tabs: Physical Architecture, Contexts, Logical Architecture.

The physical and logical architectures are organized by technologies.

The Context will simply list the contexts that you have defined, independently of the technologies.

3. WORKING WITH CONTEXTS: REAL LIFE EXAMPLE

Let us now take a databases for a data integration project. Let us say this is a sales database. The structure of the data will have to be consistent from Development to QA, and from QA to Production (Over time, the structures will evolve in the different environments. But for the QA to be meaningful, we need to be consistent from Development to QA and from QA to Production).

3.1 Creation of a Logical Schema

To represent the connectivity to the data structures, we will define an alias. Let’s call this alias ORA_SALES. This is our Logical Schema. The Logical Schema is created in the Logical Architecture tree under a given technology.

When you create a logical schema, unless you associate it with a context, it is just an alias that points nowhere. Create a logical Schema under the Oracle Technology and call it ORA_SALES. We will revisit this entry shortly after creating contexts, and again after defining the connectivity to the actual physical servers.

3.2 Contexts

Switch now to the Context tab in Topology, and create 3 contexts: Development, Test and Production. Simply give a name to each – no passwords are required.

Once the contexts are created, we can revisit the definition of the logical schema we had created in the first step, and see that there is no physical connection defined for either context. Basically, we have said that we have a data model called ORA_SALES that will exist in three separate environments, but we have not defined the connectivity for either one yet: this will be done in the physical architecture.

3.3 Physical Architecture

The Physical Architecture is where we will define the actual servers that host the data. We will provide the credentials to connect to the servers: user name and password. We will also provide the JDBC connection strings, which usually contain the IP address or host name, the port number for the database, and any additional information as required by the technology: SID or service for Oracle, database name for SQL server, etc.

For this example to work in our environment, you will have to adapt it to point to your own databases and schemas.

Under the Oracle Technology, create a new Data Server (be careful when you make your selection from the menus: a common mistake is to create a new Technology instead of a new server!). Name this server, and then enter the appropriate user name and password for that server.

Once you have entered these parameters, you can click on the JDBC tab and enter the database JDBC connection information. Here, connecting to Oracle, we select the Oracle Driver name from the drop-down menu and update the JDBC URL with our server name, port number and SID.

Note: if you are not using an Oracle database, you will have to copy the JDBC driver files for your database in the appropriate ODI directory. Check out the ODI documentation for more details on this process.

You can validate the parameters you have entered with the “test” button: at this point the connection should be successful.

When you will save this entry (click Ok or Apply) ODI will open another window to select a Physical Schema on that server. If the schema window does not appear, you can right-click on the physical server you have just created in the Physical Architecture tree and select Insert Physical Schema.

Note: Different Technologies will have different terminologies and ODI will display the proper terminology as needed. From an ODI perspective though, we will always talk about a “Physical Schema”.

Select the Physical schema name, as well as a “work schema” from the drop-down menus.

Then click on the Context tab and click on the blue grid button to add a line, where you can select a context and the logical schema name. For instance, Development and ORA_SALES.

What you are saying here is that ODI will connect to the physical schema that you have just defined when the generated code will refer to the ORA_SALES object and when the context selected at runtime is set to Development.

You can then define the appropriate servers and/or schemas for your QA and production environments, and associate them with the appropriate Context for your Logical Schema.

4. REVIEWING LOGICAL SCHEMAS

Once all physical schemas have been defined and mapped properly, you can edit your logical schema and review your mappings. You can make sure that the schema is actually pointing to a physical schema for all contexts. If a context displays Undefined for the physical schema, then your code cannot run in this context: ODI would not know where to connect.

5. REVIEWING CONTEXTS

You can also edit your contexts and select the Schema tab to review the mapping of all logical schemas for this context: all you file directories, source systems and target systems must be properly mapped for your code to run in this environment.

For more information on ODI contexts, please refer to the ODI Users Guide (part of the Documentation Library that comes with the ODI installation), in particular the entry "What is the Topology? "

All Screenshots were taken using version 10.1.3.5 of ODI. Actual icons and graphical representations may vary with other versions of ODI.

The posts in this series assume that you have some level of familiarity with ODI. The concepts of Model, Project, Interface and Package are used here assuming that you understand these concepts in the context of ODI. If you need more details on these elements, please refer to the ODI Tutorial for a quick introduction, or to the complete ODI documentation for detailed information on these concepts.

In our last post in this series, we looked into passing parameters to dynamically set the name of the file that we want to process. An alternative may be to parse a list of file names that would be stored in a table in your database and to loop over this list to process all files.

To perform this, we will need to perform the following operations:
- Create a variable to store the file name, and a second variable that we will use as a cursor to point in our table
- Define a package and use the variables to loop over the different values in the table

If you need help with the creation of variables please refer to our post on the usage of variables in ODI Interfaces and Packages.

1. DEFINITION OF THE VARIABLES

1.1 The Counter Variable

We will start here with the definition of a variable that we will use to loop through your list of files. Let’s call it Counter. I usually use an alphanumeric variable and define the SQL query as being

select #Counter+1 from dual

(Note: the code would vary for non Oracle databases). I find alphanumeric variables easier to process than using numerics as numerics come with decimal values and may require conversions based on where and how you use them. One advantage of numeric variables though is that you can use ODI to increment their values when you select the Assign action on the variable.

1.2 The FileName Variable

For this example, we assume that the file names are stored into a table called ODI_FILES_TABLE. This table has only one column with the file names, called FILE_NAME. And we store the table in an Oracle database. You can easily adapt the code for different table structures or databases. Here we will parse the table taking advantage of the ROWNUM returned by the Oracle database.

Create a Variable in the same project, and call it FileName. Define the following query for the FileName variable:

select FILE_NAME
from (select FILE_NAME, ROWNUM COUNTER from ODI_FILES_TABLE)
where COUNTER=#Counter

Note that we are using our fist variable to retrieve one record only. To run this statement, remember that the Counter variable MUST have a value – otherwise the where clause would not be valid.

2. DEFINITION OF THE PACKAGE AND DESIGN OF THE LOOP

We will now create a package to design our loop. In the package, drag and drop the Counter variable and set the action to Set Variable. Assign the value 1 to the variable (select Assign in the properties window and enter the value 1 in the text box below the Assign selection).

Drag and drop the FileName variable in the package after the Counter variable. Set the action on the variable to Refresh Variable. In case of success after the refresh, execute any procedures or interfaces where the FileName is used. In case of failure of the refresh, execute a last step to indicate you are done: a failure here would indicate that you have exhausted all files listed in the table. In our example we are sending an email, but you could execute another branch of your process.

After the execution of the procedures and interfaces, add the Counter variable again, but this time set the action to Refresh Variable: this will increment the value of the variable. (If you are using a numeric variable, you can replace this with a Set Variable action and set the operation to Increment).

Then loop back to the refresh step of the FileName variable: this will either select the next file in the list… or fail and exit the loop.

3. BEYOND THE FIRST EXAMPLE

3.1 A Cleaner Exit

For a cleaner exit than the one described here, you may want to use a third variable where keep track of the number of files in your table with the following query:

select count(*) from ODI_FILES_TABLE

Refresh this new variable at the beginning of the package. You can then compare your Counter variable (using the action Evaluate Variable) to that value and exit the loop when you reach this value.

3.2 Guarantying that the tables are found in all Contexts

Instead of hard-coding schema names in your variable refresh queries, remember to use the ODI Substitution Method that will automatically rebuild the schema name in all contexts: odiRef.getObjectName. For instance:

select FILE_NAME
from (select FILE_NAME, ROWNUM COUNTER from < % = odiRef.getObjectName("ODI_FILES_TABLE") % >)
where COUNTER=#Counter

MORE ON VARIABLES IN ODI

For more information on ODI variables, please refer to the ODI Users Guide (part of the Documentation Library that comes with the ODI installation), in particular the entry "Creating and Using Variables"

All Screenshots were taken using version 10.1.3.5 of ODI. Actual icons and graphical representations may vary with other versions of ODI.

How is Raiffeisen International Bank able to streamline their data warehousing and improve their bottom line? The answer: unify data at the speed of business. Companies like Raiffeisen have taken this strategy to heart by recognizing that real-time information and faster time to value is critical to remain competitive.

How is your enterprise data unified? Are you currently struggling to:

- Speed the delivery of unified information?
- Eliminate custom code and custom SQL?
- Do more with less with your existing data-centric architecture?
- Ensure your data is clean, consistent, and trustworthy?
- Provide up-to-date synchronization of your data without impacting performance?

Join this web seminar to learn how companies like Raiffeisen International Bank reduced costs and improved efficiencies using Oracle Data Integration for its unmatched performance, ease of use, flexibility and lowest total cost of ownership. Learn how to promote data to be more agile, gain new ground towards reducing risk and provide revolutionary business insight.

Register Now for this live I-Seminar on April 30th, 9AM PST