Identifying Source System Data Changes for Incremental ETL Processes

Problem: When designing incremental ETL processes, ETL Architects face the challenge of identifying algorithms to identify data changes in the source system between ETL runs. Some of the options that might be available are (from the best case scenario to the worst):

1. Database Log Readers: This approach utilizes an ETL tool that is capable of reading the source database log files to identify inserted and updated records. For example, Informatica Power Center supports this through its Change Data Capture (CDC) functionality. However, source system owners may not be willing to grant read access to the database logs or an ETL tool that supports this functionality may not be available.
2. Timestamp columns in the source database: If the source system maintains an insert and update timestamp column for each table of interest, then the ETL process can utilize these columns to identify source system changes since the last ETL execution timestamp. Chances are, however, that the source system does not provide that functionality.
3. Triggers to populate log tables: This is by far the worst option since it adds a significant resource utilization burden to the source system. In this case, triggers are created for all tables of interest. The purpose of these triggers is to capture all insert/updates/deletes into log tables. The ETL process then reads the data changes from the log tables and removes all records that it has successfully processed. Again, source system owners will most likely be very hesitant to support this approach.

What to do if none of these options are available?



Solution: We propose the following checksum based approach. In this blog, we will utilize SQL Server’s CHECK_SUM algorithm; however, Oracle’s ORA_HASH can be used in a similar fashion.

This approach requires that the entire source table (only columns and rows of interest, of course) be loaded into a staging table. During the staging load, the ETL process will assign a checksum value to each record. For example, when loading data from SOURCE_TABLE_A into STAGING_TABLE_A, the SQL would look something like this:

insert into staging_table_a ( col1, col2, cold3, business_key, check_sum)
select col1, col2, cold3, business_key, check_sum(col1,col2,cold3,business_key)
from source_table_a

Let us further assume that business_key is the primary key of the source system record. In other words, business_key uniquely identifies a record in the source system.

Both business_key and check_sum must be stored in their corresponding dimension tables. In our example, the dimension table for source_table_a would include a surrogate dimension key (dim_key), business_key, and check_sum as shown below.
For performance optimization reasons, we recommend to create a composite index on business_key and check_sum.

In order to identify new records that were inserted into the source system since the last ETL run, we have to find all business_keys in the staging table that have no corresponding business_key in the dimension table. The SQL code would look something like this:

select s.* from staging_table_a s
Where not exists (select * from dimension_table_a d
where d.business_key = s.business_key)

To identify updated records since the last ETL run, we have to find all records in the staging table that have a matching business_key in the dimension table with a different check_sum value. Here is the SQL code for this:

select s.*
from staging_table_a s
inner join dimension_table_a d on
d.business_key = s.business_key and
d.check_sum <> s.check_sum

In all cases, the joins against the dimension table will be based on index lookups because we have a composite index on business_key and check_sum. Therefore, identifying new or updated records is quite efficient. The drawback of this solution is the necessity to perform a full data load into the staging area, which may not be feasible for large source systems.

One of the major benefits of this approach is its immunity against getting out-of-sync with the source system (due to aborted or failed ETL processes). No matter at what point the previous ETL process has failed, this approach will always correctly identify source system changes and re-sync without any additional human intervention.

In summary, the check_sum approach may be a feasible alternative for environments that have no other means for identifying data changes in the source system.

Please contact us if you have any questions.

Multi-Developer OBI EE Environments

In an environment with more than two or three OBI EE developers, it becomes increasingly difficult to coordinate and control code changes and updates to the OBI EE catalog, repository, and BI Publisher XMLP content. The larger the development team, the more likely the chance of two developers updating the same report and inadvertently overwriting each other’s work.

Often, some type of control is enforced by dividing the content into separate areas of responsibility. For example, developer 1 is responsible for maintaining the repository, developer 2 is responsible for all accounting reports, and so on. However, this approach makes resource utilization planning difficult for project managers since work-loads are never equally distributed across the areas of responsibility.

Since OBI EE has no built-in source code control capability, one has to look for third party software that can add this capability to an OBI EE development environment. There are several options including Microsoft Visual SourceSafe, CVS, and Tortoise Subversion (TortoiseSVN), which is an open source version control tool that can be downloaded for free at http://tortoisesvn.net/.

Regardless of the tool, the solution boils down to version control on the OBI EE content files as depicted in the diagram below.

Developers run local instances of the OBI EE environment on their own workstations. All files and subfolders in the OBI EE web catalog folder, the BI Publisher XMLP folder, and the repository files are placed under source code control in a central master repository. Each workstation has a local repository that is synchronized with the master repository via update, check-in, and check-out operations.

The development server, which is mainly used by the business analysts for testing, is another subscriber to the master repository. A simple update from the repository will deploy the most current version to the development server.

Once a developer has checked-out a file, the file is locked in the master repository and no other developer is allowed to change the file until it is checked in again. Thus, no longer can one developer inadvertently overwrite changes of another. In addition, this approach provides the capability to roll back the environment to a previous version.

Enabling BI Publisher with OBIEE for External Web Users

A challenging task in implementing an OBIEE environment can be the hosting and accessing the BI publisher reports outside of a DMZ or by the general public. Enabling OBIEE using the presentation server plug-in is fairly well documented in Oracle’s install documentation as well as various blogs on the web. Once you have created a website in IIS and followed the steps your installation should look something like this:




With a virtual directory called Analytics that is pointed to the \OracleBI\web\app folder. The application it runs is the saw.dll file. The virtual directory execute permissions property should be set to "Scripts and Executables". The next step is to make sure the Siebel Analytics Web Service Extension (saw.dll) is added as an extension and marked as Allowed.

If you plan on exposing OBIEE and BI Publisher reports to external web users outside the corporate firewall you need to plan for requesting that the correct ports are opened between the Presentation Server and the IIS server and the BI Publisher Server and the IIS server. The default configuration is to open port 9710 for OBIEE and port 9704 for BI Publisher. In the diagram Presentation Server and BIP are on the same internal box. On the IIS server you need to configure the \OracleBIData\web\config\ isapiconfig.xml file with the Internal Server and the port 9710.

Example:



Also you need to configure the \OracleBI\web\app\WEB-INF\web.xml with the internal presentation server and port 9710.




Following the above steps you should have OBIEE working just fine over the internet hosted by IIS. But if you have any BI Publisher reports they will not be available. So to over-come this limitation we need to follow the instructions outlined in section 9.2 of Oracle® Business Intelligence New Features Guide

The Oracle BI Publisher component of the OBIEE installation require Oracle Containers for Java (OC4J) and will not run natively on Microsoft's Internet Information Server (IIS).IIS can be configured as a listener for OC4J. This is accomplished via an IIS proxy plug-in that is provided with the BI EE installation files. When configured, the requests are routed from IIS to OC4J so that it appears to the user that everything is being executed by IIS. Below are the configuration steps:

1. From your BI EE install files, locate oracle_proxy.dll. The navigation path is as follows:
   \Server\Oracle_Business_Intelligence\oc4jproxy\oracle_proxy.dll


2. Create a folder on an accessible drive, for example: c:\proxy. Copy oracle_proxy.dll to this folder.


3. In the same folder, create a configuration file called "proxy.conf " . Following is a sample configuration file:


1. # Server names that the proxy plug-in will recognize.
   oproxy.serverlist=Internal


2. # Hostname to use when communicating with
   a specific server.
   oproxy.Internal.hostname=internalserver.company.com


3. # Port to use when communicating with a specific server.
   oproxy.Internal.port=9704


4. # Description of URL(s) that will be
   redirected to this server.
   oproxy.Internal.urlrule=/xmlpserver
   oproxy.Internal.urlrule=/xmlpserver/*


5. When you complete this Step, there will be two files (oracle_proxy.dll and proxy.conf) in the folder that you created in Step 2.


4. Define the OracleAS Proxy Plug-in Registry as follows:


1. Edit your registry to create a new registry key named: HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\IIS Proxy Adapter.



2. Specify the exact location of your configuration file with the name server_defs, and a value pointing to the location of your configuration file, for example: c:\proxy\proxy.conf.


3. (Optional) Specify a log_file and log_level: Add a string value with the name log_file, and the desired location of the log file, for example, c:\proxy\plugin.log. Add a string value with the name log_level, and a value for the desired log level. Valid values are "debug", "inform", "error", and "emerg".


5. Create the "oproxy" virtual directory in IIS as follows:


1. Using the IIS management console, add a new virtual directory to your IIS Web site with the same physical path as that of oracle_proxy.dll. Name the directory "oproxy" and give it execute access.



2. Using the IIS management console, add oracle_proxy.dll as a filter in your IIS Web site. The name of the filter should be "oproxy" and its executable must point to the directory that contains oracle_proxy.dll, for example, c:\proxy\oracle_proxy.dll.


3. Add Oproxy as a Web service extension for c:\proxy\oracle_proxy.dll and set status to allow.



4. Restart IIS (stop and then start the IIS server), ensuring that the filter is marked with a green arrow pointing up.


6. Check the following configuration files to remove the port 9704 because now IIS is routing all the calls to OC4J.
   \oracleBIData\web\Config\instanceconfig.xml\oracleBI\xmlp\Admin\configutation\xmlp-server-config.xml


7. To access BI Servlets from the IIS / OracleAS Proxy Plug-in, you must specify the complete URL for example:
   http:///xmlpserver/login.jsp

Value Add: You just need three letters: WHY?

As “experts” in the field of business intelligence, we are called upon day in and day out to help customers find ways to use the data they capture to help solve problems, find opportunities, and to add value to their companies. However, too many times in the process of understanding the customer, the approach taken is to figure out what they do now and make it better. While finding more efficient ways of doing business does add value to the customer, it’s only the tip of the iceberg. By incorporating three little letters, W-H-Y, into the process of understanding our customer, we can often find numerous other ways to help them be successful.

The Pitfalls of Traditional Requirements Gathering:

Traditional Requirements gathering often takes the approach of examining what the customer is currently doing. For example:

• What types of reports do you currently run?
• What type of data do you currently use?
• What type of analysis do you currently do?

While these are all relevant questions to ask as part of the process of understanding our customer’s line of business, too many times this is where the discovery process ends. Often we leave out the word “WHY” when we asking the questions. For example, when asking the question, “What types of reports do you currently run?”, the real value in the question comes when you ask why they need to run these reports. Not only does it give you, as the advisor, better insight into what things are important to their business, it also opens doors to other avenues of data or other applications of existing data that may be of use to the customer. Likewise, the more you can get at the root of why the data is important to the customer the more you can come to understand their pain points, and in doing so, become less prone to be seen as a window dresser and more as a problem solver. As simple as it seems on more than one occasion I’ve had the business area say, ‘it’s really refreshing to feel like someone is taking the opportunity to truly understand our business and help us figure out ways to make it better.’ The amazing thing is that to this point, all you’ve really done is asked questions and listened to what they had to say, and you’ve established yourself in a position of trust.

Helping the end user community determine how to make it better:

It doesn’t take a variety of independent studies to conclude that including your customers in the solution development process will likely result in a much higher adoption rate. Now, there is a time and place for everything. For example, bringing an end user into a meeting to discuss hardware specifications and communications protocol, will often leave the end user feeling lost and confused. This type of session tends to leave them feeling like they have nothing to bring to the table, and it is typically a waste of their time.

However, bringing an end user into a meeting to discuss report layout and navigation will be time well spent. First of all, they probably didn’t have much of a say in how the currently do things, and giving them a say in how the new system will work is very empowering to them. Not only will the end user feel like they have had a say in the process, but it should also expedite time to market by reducing back and forth between developers and end users at user acceptance testing. Likewise, the process of coming up with those specifications will often lead to other discussions that will result in finding other useful ways to unlock the power of the data they have at their disposal.

Result = BI app that was built by and for the end user

At the end of the day, the end user will be the one that has to live with the system that is built. While we as advisors often take a fair amount of pride in the solutions we craft, 9 times out of 10 we get to walk away from a customer to move onto something new. For the customer though, they are left with something that they will work with day in and day out. The relationships we build with our customers are the foundation for future opportunities, and nothing serves us better than to walk away from a project where the end user is excited about what they’ve help create versus something that’s been dropped in their lap. The more we take to time to realize this, the better we set ourselves up to work with the customer in the future.

In summary, it’s not enough as an advisor to survey what’s already in place for a customer and figure out a way to make it look better or run faster. The real value we add is when we help them uncover unique new ways of harnessing the power of their data. Taking the time to understand the customer’s business and how they go about managing the success of that business goes a long way in helping us make those types of discoveries. Taking that information and involving the end users in the development of the solution help cement the value of the work that is being done. It all results in a BI application that is built by and for the customer. Let’s face it, that’s the end game we should all be playing for. At the end of a project, if all you wind up doing is making the same reports run faster or with a different wrapper, at the end of the day your customers may be the ones saying WHY???