Replicating Transactions Between Microsoft SQL Server and Oracle Database Using Oracle GoldenGate

Most Oracle technology professionals who are interested in data replication are familiar with Oracle Streams. Until 2009, Streams was the recommended and most popular Oracle technology for data distribution.
In July 2009, Oracle acquired GoldenGate, a provider of database replication software. The company is now encouraging its customers to use Oracle GoldenGate (which is part of the Oracle Fusion Middleware family) for their data replication needs in new applications. Oracle's statement of direction regarding Oracle Streams says that product “will continue to be supported, but will not be actively enhanced.”
In this article we will build a simple transaction replication example using Oracle GoldenGate, in order to get acquainted with this new technology.

Oracle GoldenGate Architecture

GoldenGate v11 enables transaction level replication among heterogeneous platforms. It supports Oracle Database, IBM DB2, Microsoft SQL Server, MySQL, Teradata, and many other platforms. (It also supports access through a generic ODBC driver.)
The most important components that we need to be familiar with are the Extract and Replicat processes. The Extract process runs at the source system and captures the data changes. The Replicat is running at the target machine and is responsible for applying the changes to the target database.

There are two common configurations for the Extract process. The so called “initial load” is used for populating the target database with an exact copy of the source data (i.e. Extract is fetching all data from the source database and typically runs only once). Then the “change synchronization” can take place. In “change synchronization” configuration the Extract is constantly monitoring the source database and captures all changes on the fly.
In this demonstration we will setup a Microsoft SQL Server 2008 as a source database, configure and perform an initial load and then start an Extract process in a change synchronization mode. In order to show that this replication is truly heterogeneous, we will run SQL Server on Windows XP and Oracle Database 11g Release 2 on Oracle Linux 5. As a prerequisite I will assume that you already have a clean installation of SQL Server 2008 on the Windows box and Oracle Database on the Linux machine.
We will start building the demonstration scenario by installing GoldenGate. Let's start with the Windows box.

GoldenGate for SQL Server Installation on Windows XP

First you need a copy of Oracle GoldenGate v11 for SQL Server. You can download it from http://edelivery.oracle.com (Oracle Fusion Middleware → Microsoft Windows x32 → Oracle GoldenGate for Non Oracle Database v11). The serial number of the media pack that you need is V22241-01.

Extract the downloaded archive in a location where you want to have the Oracle GoldenGate installation (in this example – C:\GG). Then open a command prompt, go to the directory, and launch GGSCI (the GoldenGate command interface):

C:\GG>ggsci

Oracle GoldenGate Command Interpreter for ODBC
Version 11.1.1.0.0 Build 078
Windows (optimized), Microsoft SQL Server on Jul 28 2010 18:55:52

Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.

GGSCI (MSSQL) 1>

Next execute the command CREATE SUBDIRS to create the Oracle GoldenGate working directories.

GGSCI (MSSQL) 1> CREATE SUBDIRS

Creating subdirectories under current directory C:\GG

Parameter files                C:\GG\dirprm: created
Report files                   C:\GG\dirrpt: created
Checkpoint files               C:\GG\dirchk: created
Process status files           C:\GG\dirpcs: created
SQL script files               C:\GG\dirsql: created
Database definitions files     C:\GG\dirdef: created
Extract data files             C:\GG\dirdat: created
Temporary files                C:\GG\dirtmp: created
Veridata files                 C:\GG\dirver: created
Veridata Lock files            C:\GG\dirver\lock: created
Veridata Out-Of-Sync files     C:\GG\dirver\oos: created
Veridata Out-Of-Sync XML files C:\GG\dirver\oosxml: created
Veridata Parameter files       C:\GG\dirver\params: created
Veridata Report files          C:\GG\dirver\report: created
Veridata Status files          C:\GG\dirver\status: created
Veridata Trace files           C:\GG\dirver\trace: created
Stdout files                   C:\GG\dirout: created


GGSCI (MSSQL) 2> EXIT

C:\GG>

According to the official documentation GGSCI supports up to 300 concurrent Extract and Replicat processes per Oracle GoldenGate instance. There is however a single process that is responsible for controlling the other processes; it's called the Manager process. Although you can run this process manually it is a good practice to install it as service - otherwise it will stop when the user that started it logs off.
To add the Manager process as a Windows service execute the INSTALL ADDSERVICE command within the GoldenGate installation directory.

C:\GG>INSTALL ADDSERVICE

Service 'GGSMGR' created.

Install program terminated normally.

C:\GG>

This pretty much completes the Windows installation. Let's move on to the Linux machine.

GoldenGate for Oracle Installation on Oracle Linux 5

Installing Oracle GoldenGate on Linux is not much different than the installation that you did on top of Windows XP. You will need to download the media pack of GoldenGate for Oracle on Linux (V22228-01). You create an installation directory and unzip the archive there. In this example, I use the /u01/app/oracle/gg directory, as our ORACLE_BASE is pointing to /u01/app/oracle. After this is done you have to set the PATH and LD_LIBRARY_PATH environment variables like this:

[oracle@oradb ~]$ export PATH=$PATH:$ORACLE_BASE/gg
[oracle@oradb ~]$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_BASE/gg

Let's start GGSCI and execute CREATE SUBDIRS.

[oracle@oradb ggs]$ cd $ORACLE_BASE/gg
[oracle@oradb gg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.0.0 Build 078
Linux, x86, 32bit (optimized), Oracle 11 on Jul 28 2010 13:22:25

Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.


GGSCI (oradb) 1> CREATE SUBDIRS

Creating subdirectories under current directory /u01/app/oracle/gg

Parameter files                /u01/app/oracle/gg/dirprm: created
Report files                   /u01/app/oracle/gg/dirrpt: created
Checkpoint files               /u01/app/oracle/gg/dirchk: created
Process status files           /u01/app/oracle/gg/dirpcs: created
SQL script files               /u01/app/oracle/gg/dirsql: created
Database definitions files     /u01/app/oracle/gg/dirdef: created
Extract data files             /u01/app/oracle/gg/dirdat: created
Temporary files                /u01/app/oracle/gg/dirtmp: created
Veridata files                 /u01/app/oracle/gg/dirver: created
Veridata Lock files            /u01/app/oracle/gg/dirver/lock: created
Veridata Out-Of-Sync files     /u01/app/oracle/gg/dirver/oos: created
Veridata Out-Of-Sync XML files /u01/app/oracle/gg/dirver/oosxml: created
Veridata Parameter files       /u01/app/oracle/gg/dirver/params: created
Veridata Report files          /u01/app/oracle/gg/dirver/report: created
Veridata Status files          /u01/app/oracle/gg/dirver/status: created
Veridata Trace files           /u01/app/oracle/gg/dirver/trace: created
Stdout files                   /u01/app/oracle/gg/dirout: created


GGSCI (oradb) 2> EXIT
[oracle@oradb gg]$

Installation on the Linux machine is now completed.

Preparing the Source Database

Next step is to create a new database in SQL Server and populate it with some sample data. The name of the database will be EMP. You can create it by launching SQL Server Management Studio, right-clicking on Databases, and selecting New Database.

 

Type EMP in the database name field and click OK, leaving all other options by default.

Let's add a new database schema (HRSCHEMA), a table (EMP) and a few test records in the newly created database. This will be accomplished by running the following SQL:

set ansi_nulls on
go
set quoted_identifier on
go
create schema hrschema
go
create table [hrschema].[emp] (
     [id] [smallint] not null,
     [first_name] varchar(50) not null,
     [last_name] varchar(50) not null,
constraint [emp_pk] primary key clustered (
     [id] asc
) with (pad_index = off, statistics_norecompute=off, ignore_dup_key=off, allow_row_locks=on, allow_page_locks=on) on [primary]
) on [primary]
go

-- TEST DATA
INSERT INTO [hrschema].[emp] ([id], [first_name], [last_name]) VALUES (1,'Dave','Mustaine')
INSERT INTO [hrschema].[emp] ([id], [first_name], [last_name]) VALUES (2,'Chris','Broderick')
INSERT INTO [hrschema].[emp] ([id], [first_name], [last_name]) VALUES (3,'David','Ellefson')
INSERT INTO [hrschema].[emp] ([id], [first_name], [last_name]) VALUES (4,'Shawn','Drover')
GO

First create a new query (by right-clicking on the database name and selecting New Query). Then paste-in the SQL text above and hit F5 to execute it.

Now, in order for Oracle GoldenGate to be able to access the EMP database, you have to create an ODBC data source for it. Let's go to Control Panel -> Administrative Tools -> Data Sources (ODBC) and add a new System DSN. Select SQL Server as the database driver and name the data source HR. You point the source to the local SQL Server (MSSQL) and fill in the login credentials. The data source summary should be similar to this:

Now it's time to enable Oracle GoldenGate to acquire the transaction information for the EMP table from the transaction logs. Again you will be using GGSCI:

C:\GG>ggsci.exe

Oracle GoldenGate Command Interpreter for ODBC
Version 11.1.1.0.0 Build 078
Windows (optimized), Microsoft SQL Server on Jul 28 2010 18:55:52

Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.


GGSCI (MSSQL) 1> DBLOGIN SOURCEDB HR
Successfully logged into database.

GGSCI (MSSQL) 2> ADD TRANDATA HRSCHEMA.EMP

Logging of supplemental log data is enabled for table hrschema.emp

GGSCI (MSSQL) 3>

Because the data types in Oracle and SQL Server are different you have to establish a data type conversion. GoldenGate provides a dedicated tool called DEFGEN that generates data definitions and is referenced by Oracle GoldenGate processes when source and target tables have dissimilar definitions. Before running DEFGEN you have to create a parameter file for it, specifying which tables should the tool inspect and where to place the type definitions file after the tables are inspected. You can create such a parameter file using the EDIT PARAMS command within GGSCI.

GGSCI (MSSQL) 3> EDIT PARAMS DEFGEN

GGSCI (MSSQL) 4>

This creates an empty parameter file named DEFGEN.PRM and located in the DIRPRM folder of your GoldenGate installation. Put the following contents inside the file:

defsfile c:\gg\dirdef\emp.def
sourcedb hr
table hrschema.emp;

The parameters are pretty self explanatory.  We want DEFGEN to inspect the EMP table inside the HRSCHEMA and to place a definitions file named EMP.DEF in the DIRDEF sub-directory. Let's invoke DEFGEN and examine its output.

C:\GG>defgen paramfile c:\gg\dirprm\defgen.prm

***********************************************************************
         Oracle GoldenGate Table Definition Generator for ODBC
                     Version 11.1.1.0.0 Build 078
   Windows (optimized), Microsoft SQL Server on Jul 28 2010 19:16:56

Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.


                    Starting at 2011-04-08 14:41:06
***********************************************************************

Operating System Version:
Microsoft Windows XP Professional, on x86
Version 5.1 (Build 2600: Service Pack 3)

Process id: 2948

***********************************************************************
**            Running with the following parameters                  **
***********************************************************************
defsfile c:\gg\dirdef\emp.def
sourcedb hr
table hrschema.emp;
Retrieving definition for HRSCHEMA.EMP

Definitions generated for 1 tables in c:\gg\dirdef\emp.def

C:\GG>

If you bother to check the contents of EMP.DEF it will be something similar to this:

*
* Definitions created/modified  2011-07-07 10:27
*
*  Field descriptions for each column entry:
*
*     1    Name
*     2    Data Type
*     3    External Length
*     4    Fetch Offset
*     5    Scale
*     6    Level
*     7    Null
*     8    Bump if Odd
*     9    Internal Length
*    10    Binary Length
*    11    Table Length
*    12    Most Significant DT
*    13    Least Significant DT
*    14    High Precision
*    15    Low Precision
*    16    Elementary Item
*    17    Occurs
*    18    Key Column
*    19    Sub Data Type
*
*
Definition for table HRSCHEMA.EMP
Record length: 121
Syskey: 0
Columns: 3
id          134     23        0  0  0 1 0      8      8      8 0 0 0 0 1    0 1 0
first_name   64     50       11  0  0 1 0     50     50      0 0 0 0 0 1    0 0 0
last_name    64     50       66  0  0 1 0     50     50      0 0 0 0 0 1    0 0 0
End of definition

It basically lists all tables/columns and describes the native database types using a more general definitions.
Now you have to copy the EMP.DEF file to the target machine as it should be available to the Replicat process. The Replicat will have to do another conversion. It will map the more general types back to database specific types (but this time the types will correspond to the ones used by the target database). For copying the file you can use FTP/SFTP or SCP transfer. (Personally I am using a free FTP/SFTP/SCP client called WinSCP to copy EMP.DEF from the Windows box to the /u01/app/oracle/gg/dirdef folder on the Linux machine.)

 

Preparing the Target Database

After the source preparations are finalized it's time to move to the target machine. Let's create a schema (GG_USER) and a table where the Replicat process can apply the transactions coming from the source.

[oracle@oradb ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 8 14:11:49 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user gg_user identified by welcome1;

User created.

SQL> grant connect, resource,select any dictionary to gg_user;

Grant succeeded.

SQL>

The EMP table should reside in GG_USER's schema:

SQL> create table gg_user.emp (id number not null, first_name varchar2(50), last_name varchar2(50));

Table created.

SQL>

You have to keep in mind that should the Replicat process apply data to tables residing in different schemas, GG_USER will need additional privileges (like SELECT ANY TABLE, LOCK ANY TABLE etc.). A detailed list of the required privileges is listed in the official documentation.
Setting Up the Extract & Replicat for Initial Data Load
Let's start by setting up the Extract process on the source machine. Name the process INEXT (for INitial EXTract). Next create a parameters file in the same manner as the parameter file that you created for the DEFGEN utility. The filename will be INEXT.PRM.

C:\GG>ggsci.exe

Oracle GoldenGate Command Interpreter for ODBC
Version 11.1.1.0.0 Build 078
Windows (optimized), Microsoft SQL Server on Jul 28 2010 18:55:52

Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.

GGSCI (MSSQL) 1> EDIT PARAMS INEXT

Paste the following contents to INEXT.PRM:

SOURCEISTABLE
SOURCEDB HR
RMTHOST ORADB, MGRPORT 7809
RMTFILE /u01/app/oracle/gg/dirdat/ex
TABLE hrschema.emp;

The SOURCEISTABLE parameter instructs the Extract process to get the data directly from the table instead of the transaction logs. This is the behavior that we want in order to do a full extraction. SOURCEDB points to the database that contains the data. RMTHOST and MGRPORT specify the remote machine and Manager's port. RMTFILE specifies the file to which the extracted data will be written.
That's all the configuration you need for the initial data extraction. Let's move to the Linux machine and configure the initial data loading.
You have to deal with the Manager process first: Start GGSCI and create a parameter file called MGR.PRM.

[oracle@oradb gg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.0.0 Build 078
Linux, x86, 32bit (optimized), Oracle 11 on Jul 28 2010 13:22:25

Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.


GGSCI (oradb) 1> EDIT PARAM MGR

There is only one line that you have to put in MGR.PRM:

PORT 7809

After saving the file execute the START MANAGER command within GGSCI and see if the manager starts correctly.

GGSCI (oradb) 2> START MANAGER

Manager started.


GGSCI (oradb) 3>

Next you have to set the parameters for the Replicat process. So create a new parameters file and name it INLOAD (for INitial LOADing).

GGSCI (oradb) 3> EDIT PARAMS INLOAD

Put the following contents inside INLOAD.PRM:

SPECIALRUN
END RUNTIME
USERID gg_user, PASSWORD welcome1
EXTFILE /u01/app/oracle/gg/dirdat/ex
SOURCEDEFS /u01/app/oracle/gg/dirdef/emp.def
MAP hrschema.emp, TARGET gg_user.emp;

The SPECIALRUN parameter defines an initial-loading process (it is a one-time loading that doesn't use checkpoints). The next line of the file instructs the Replicat process to terminate after the loading is finished.

Next you provide the database user and password, the extract file, and the table definition. The final parameter, MAP, instructs the Replicat to remap the table HRSCHEMA.EMP to GG_USER.EMP.

Running the Initial Extract and Loading

The databases and processes are finally configured. Now you can start the initial loading and see the data replication in action.
First you have to run the Extract process; it will fetch all data residing at the SQL Server's EMP table and write it to the RMTFILE (/u01/app/oracle/gg/dirdat/ex) at the Linux host.
Start the Extract by running the EXTRACT command and providing parameters and log file as command line arguments.

C:\GG>extract paramfile dirprm\inext.prm reportfile dirrpt\inext.rpt

***********************************************************************
                  Oracle GoldenGate Capture for ODBC
                     Version 11.1.1.0.0 Build 078
   Windows (optimized), Microsoft SQL Server on Jul 28 2010 19:22:00

Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.


                    Starting at 2011-04-08 15:57:48
***********************************************************************

Operating System Version:
Microsoft Windows XP Professional, on x86
Version 5.1 (Build 2600: Service Pack 3)

Process id: 556

Description:

***********************************************************************
**            Running with the following parameters                  **
***********************************************************************

2011-04-08 15:57:48  INFO    OGG-01017  Wildcard resolution set to IMMEDIATE bec
ause SOURCEISTABLE is used.

Using the following key columns for source table HRSCHEMA.EMP: id.

CACHEMGR virtual memory values (may have been adjusted)
CACHEBUFFERSIZE:                         64K
CACHESIZE:                                1G
CACHEBUFFERSIZE (soft max):               4M
CACHEPAGEOUTSIZE (normal):                4M
PROCESS VM AVAIL FROM OS (min):        1.85G
CACHESIZEMAX (strict force to disk):   1.62G

Database Version:
Microsoft SQL Server
Version 10.00.1600
ODBC Version 03.52.0000

Driver Information:
SQLSRV32.DLL
Version 03.85.1132
ODBC Version 03.52

Database Language and Character Set:

Warning: Unable to determine the application and database codepage settings.
Please refer to user manual for more information.


2011-04-08 15:57:49  INFO    OGG-01478  Output file /u01/app/oracle/gg/dirdat/ex
 is using format RELEASE 10.4/11.1.

2011-04-08 15:57:55  INFO    OGG-01226  Socket buffer size set to 27985 (flush s
ize 27985).

Processing table HRSCHEMA.EMP

***********************************************************************
*                   ** Run Time Statistics **                         *
***********************************************************************

Report at 2011-04-08 15:57:55 (activity since 2011-04-08 15:57:49)

Output to /u01/app/oracle/gg/dirdat/ex:

From Table HRSCHEMA.EMP:
       #                   inserts:         4
       #                   updates:         0
       #                   deletes:         0
       #                  discards:         0


C:\GG>

The run time statistics shows that 4 rows were successfully extracted. Let's move to the Linux machine and start the Replicat.

To apply the extracted data to the target database, run the replicat command and provide the prepared parameters file. Here is an excerpt from the replicat run:

[oracle@oradb gg]$ ./replicat paramfile dirprm/inload.prm

***********************************************************************
                 Oracle GoldenGate Delivery for Oracle
                     Version 11.1.1.0.0 Build 078
   Linux, x86, 32bit (optimized), Oracle 11 on Jul 28 2010 15:42:30

Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.


                    Starting at 2011-04-11 12:52:52
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Mon Mar 29 20:06:41 EDT 2010, Release 2.6.18-194.el5
Node: oradb
Machine: i686
                         soft limit   hard limit
Address Space Size   :    unlimited    unlimited
Heap Size            :    unlimited    unlimited
File Size            :    unlimited    unlimited
CPU Time             :    unlimited    unlimited

Process id: 23383

Description:

***********************************************************************
**            Running with the following parameters                  **
***********************************************************************
SPECIALRUN
END RUNTIME
USERID gg_user, PASSWORD ********
EXTFILE /u01/app/oracle/gg/dirdat/ex
SOURCEDEFS /u01/app/oracle/gg/dirdef/emp.def
MAP hrschema.emp, TARGET gg_user.emp;

CACHEMGR virtual memory values (may have been adjusted)
CACHEBUFFERSIZE:                         64K
CACHESIZE:                              512M
CACHEBUFFERSIZE (soft max):               4M
CACHEPAGEOUTSIZE (normal):                4M
PROCESS VM AVAIL FROM OS (min):           1G
CACHESIZEMAX (strict force to disk):    881M

Database Version:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

...

Reading /u01/app/oracle/gg/dirdat/ex, current RBA 1210, 4 records

Report at 2011-04-11 12:53:15 (activity since 2011-04-11 12:53:14)

From Table HRSCHEMA.EMP to GG_USER.EMP:
       #                   inserts:         4
       #                   updates:         0
       #                   deletes:         0
       #                  discards:         0


Last log location read:
     FILE:      /u01/app/oracle/gg/dirdat/ex
     RBA:       1210
     TIMESTAMP: 2011-04-08 16:57:55.433993
     EOF:       NO
     READERR:   400

...

[oracle@oradb gg]$

You can login to the Oracle Database as GG_USER and check the contents of the EMP table.

SQL> select id, first_name from emp;

     ID FIRST_NAME
---------- --------------------------------------------------
      1 Dave
      2 Chris
      3 David
      4 Shawn

SQL>

The EMP table now contains a copy of all records that were originally inserted at the SQL Server.

Live Data Capture Configuration

With the Oracle database having an exact copy of the SQL Server's EMP table, it is now time to create a live capture configuration. We will setup the Extract and Replicat processes to run all the time and continuously transmit/apply changes of the EMP table.
In order to implement the new configuration you will have to create new parameter files for extracting and replicating. First however you have to perform two additional steps on SQL Server: Confirm that the database is set to Full Recovery and then take a full database backup of the EMP database. Failure to take a full backup will prevent the Extract process from capturing live data changes.
You can easily check if the EMP database is in Full Recovery by right-clicking on it, selecting Properties, and inspecting the value of Recovery model.

Taking a full backup is done in a few clicks as well. Right-click on the EMP database, select Tasks and then Back Up. This brings up the backup database dialog. We confirm that the Backup type is set to Full and then click OK.

If everything goes well in a couple of seconds we should see a notification that the operation is successful.

Time to set the processes. We will start by configuring a Manager process on the Windows machine. We skipped this step in the initial loading phase, but in the new configuration that you are building the Extract process must be running all the time. This requires an active manager process that will perform resource management functions. You will follow the same steps as with the Linux box configuration.

GGSCI (MSSQL) 1> EDIT PARAM MGR

GGSCI (MSSQL) 2>

Put a single line in MGR.PRM to set the port of the Manager instance.

PORT 7809

Then we start the Manager.

GGSCI (MSSQL) 2> START MANAGER

Starting Manager as service ('GGSMGR')...
Service started.


GGSCI (MSSQL) 3>

Let's create a new extract group for mining the transaction logs and name it MSEXT. Then set a destination where the data changes should be written (/u01/app/oracle/gg/dirdat/ms).

GGSCI (MSSQL) 3> ADD EXTRACT MSEXT, TRANLOG, BEGIN NOW
EXTRACT added.

GGSCI (MSSQL) 4> ADD RMTTRAIL /u01/app/oracle/gg/dirdat/ms, EXTRACT MSEXT
RMTTRAIL added.

You will also need a new parameters file.

GGSCI (MSSQL) 5> EDIT PARAMS MSEXT


GGSCI (MSSQL) 6>

Type the following lines in it:

EXTRACT MSEXT
SOURCEDB HR
TRANLOGOPTIONS MANAGESECONDARYTRUNCATIONPOINT
RMTHOST ORADB, MGRPORT 7809
RMTTRAIL /u01/app/oracle/gg/dirdat/ms
TABLE HRSCHEMA.EMP;

The difference here is that we are omitting the SOURCEISTABLE parameter and introducing a new one: TRANLOGOPTIONS MANAGESECONDARYTRUNCATIONPOINT. This options tells the Extract process to routinely check and delete the CDC capture job, resulting in better performance and less occupied space for captured data.
This is all you need on the source machine. Let's move on and configure the replication at the target.
On the Linux box you have to start by creating a checkpoint table. Checkpoints are used to store the current read/write positions of the Extract and Replicat processes. They prevent loss of data and insure that the processes can recover from faults (for example if the network between the source and target machine goes down for a moment). Create a table that holds checkpoints information by issuing the ADD CHECKPOINT command at the target.

GGSCI (oradb) 1> DBLOGIN USERID gg_user, PASSWORD welcome1
Successfully logged into database.

GGSCI (oradb) 2> ADD CHECKPOINTTABLE gg_user.chkpt

Successfully created checkpoint table GG_USER.CHKPT.

GGSCI (oradb) 3>

Let's add a Replicat group and setup its parameters.

GGSCI (oradb) 3> ADD REPLICAT MSREP, EXTTRAIL /u01/app/oracle/gg/dirdat/ms, CHECKPOINTTABLE gg_user.chkpt
REPLICAT added.


GGSCI (oradb) 4> EDIT PARAMS MSREP

GGSCI (oradb) 5>

As a final step put the following lines in MSREP.PRM.

REPLICAT MSREP
SOURCEDEFS /u01/app/oracle/gg/dirdef/emp.def
USERID gg_user, PASSWORD welcome1
MAP hrschema.emp, TARGET gg_user.emp;

The configuration is now completed. Let's start the Extract and Replicat and do some testing.

Starting and Testing Online Transaction Replication

To start the Extract process, use GGSCI and execute the START EXTRACT command. 

GGSCI (MSSQL) 1> START EXTRACT MSEXT

Sending START request to MANAGER ('GGSMGR') ...
EXTRACT MSEXT starting


GGSCI (MSSQL) 2>

On the Linux machine use the START REPLICAT command respectively.

GGSCI (oradb) 1> START REPLICAT MSREP

Sending START request to MANAGER ...
REPLICAT MSREP starting


GGSCI (oradb) 2>

Let's login as GG_USER and see the contents of the EMP table.

SQL> select id, first_name from emp;

     ID FIRST_NAME
---------- --------------------------------------------------
      1 Dave
      2 Chris
      3 David
      4 Shawn

SQL>

Nothing new here. The data hasn't change since the last time we checked. Let's go back to the SQL Server machine and run the following query, adding one additional row to the EMP table at the source.

BEGIN TRAN
INSERT INTO [hrschema].[emp] ([id], [first_name], [last_name]) VALUES (9,'Gar','Samuelson')
COMMIT TRAN

Let's go back to the Oracle Database and see if anything changed there.

SQL> select id, first_name from emp;

     ID FIRST_NAME
---------- --------------------------------------------------
      1 Dave
      2 Chris
      3 David
      4 Shawn
      9 Samuelson

SQL>

Congratulations! The data is getting replicated in a sub-second interval, reflecting every single transaction.

Conclusion

In this article we performed a very basic demonstration of some of the Oracle GoldenGate features. You should be aware that there are many different topologies and usage scenarios. For instance, you can configure GoldenGate to perform bidirectional replication (where two different databases simultaneously replicate changes to each other). There are also broadcast (where a single database replicates to multiple targets) and consolidation (many databases replicate to a central  database) configurations. One can use GoldenGate to implement query offloading (separating reporting from production, but avoiding the time gap of the traditional data warehouses). It is also a powerful solution for implementing zero downtime upgrades and database migrations.

Legions of remote workers create opportunities and concerns for businesses.

It’s no longer unusual for employees to take the office with them, whether that means on the road, at home or just in their pockets. Mobile devices have allowed many people to unplug from their workstations and access company resources wherever there’s an Internet connection, creating a decentralized work force that’s running at all hours.
This shift in business culture is made possible by technological advances. Improved functionalities such as faster processing speed, GPS capability, larger memory stores and robust applications make it increasingly realistic for organizations to have a legion of remote workers and to offer more flexible work schedules.
The potential benefits to businesses are many. Greater mobility can help with employee retention as more people like to be untethered, but it also aids an enterprise to stay nimble through being “always on.” Questions can be answered at any hour, which is a boon in a global economy where customers might be 12 time zones away. Having fewer in-office staff members can also reduce expenses as organizations downsize their office space.
Such advantages have led companies to increase their mobile capabilities and turn more of their cubicle dwellers into on-the-go employees. According to IDC’s “Worldwide Mobile Worker Population 2007-2011 Forecast,” this demographic could exceed 1 billion, or 30% of the global work force, by 2011. This is up from 758.6 million in 2006.
Many staff members remotely access information from a data warehouse, since an application can “push” the data to a mobile device. Likewise, content, including reports, calculations, charts, sales contacts and schedules, can be pushed back from the employee. Remote workers don’t have to physically sync up with an office machine for data warehouse information to be updated, creating an environment in which data can be refreshed quickly.

Change brings challenges

As with any major change in doing business, there are challenges, particularly for executives who need to keep pace with technology while keeping management fundamentals in place. It isn’t enough to give employees a laptop and send them home to work. To tap into the promise of mobility, leadership must implement a comprehensive strategy that includes training, goal setting and support services.
Although technical issues must be addressed—such as creating a strong technical support infrastructure and making sure in-house applications function on devices—for many organizations, the greatest challenges may be in developing an effective management framework that harnesses mobility’s power without creating a fractured, unmanaged work force.
“You need to be able to trust employees when they’re out of the office, but the employees also have to trust you—that you’re putting enough structure in place that it supports them,” says Scott Morrison, an analyst at Gartner. “If you have strong management practices that take advantage of the best aspects of telework, rather than simply policing employee use, then a remote work force can be a huge company advantage.”

Stay connected

Some traditional management tactics will always be relevant, no matter where employees are based. Strategies like rewarding productivity, evaluating progress and setting goals are standard in any industry and every department. But when they extend to remote workers, those policies can create unique challenges.
Perhaps one of the most common difficulties relates to the manager/employee connection. Although text messages and e-mails may be frequent, the lack of personal contact can create a sense of distance, Morrison says. Also, some remote workers may be on different schedules, since one of the advantages of mobile technology is the ability to work at any hour in any time zone, and this can affect reaction time to pressing issues.
Another consideration is that not all employees are well-suited to this work style, especially if they’re completely mobile and lack an in-office space. “The trouble many times is that people who telework suffer from isolation,” Morrison says. “They don’t get the decompression of the water-cooler chat, and for many personality types, that’s a problem. People that might be extremely creative and productive in the office suddenly find themselves adrift at sea when they work from home.”
Even when employees are enthusiastic about working remotely, defining and tracking productivity can be a sticking point for managers. Traditionally, companies use workplace attendance as one measure of productivity, but with mobile employees, other factors such as output must replace that metric.

"It helps to have a trial period of six months to create best practices around a remote work strategy. It’s important to have a structured approach in launching a system."

-Scott Morrison, Gartner

Technical support

Technical aspects must be addressed as well. Data management, for example, can be tricky, because it requires greater security controls, policies for sharing and access, and well-articulated principles about data collection.
An organization must also ensure security and privacy if workers are to access company data remotely, and this requires the input of IT with technical resources. It also necessitates contributions from other departments, such as human resources, to make sure employees are following directives about technology use. Job candidates should even be screened for mobile-friendly personality traits, such as being a self-starter.
A remote work force relies heavily on the data warehouse, Morrison notes, since that repository of organizational information is crucial for an employee’s daily operations. Therefore, managers need greater data warehouse functionality and reporting to determine productivity levels, craft projects that might extend across departments, and produce detailed analysis and reporting. Potholes in the highway between remote workers and the data warehouse can significantly slow business intelligence (BI) efforts and hinder productivity.

Management strategies

The first step in developing cohesive mobile policies is the creation of a “mobile center,” Morrison advises. This might involve hiring one person to direct remote working efforts, but it will more likely be a multi-department committee that meets regularly to look at what types of short-term and long-term goals are being met through the use of mobile technology.
This centralized team should train managers in developing effective definitions of productivity and revisit those parameters often to make sure they’re realistic. Morrison notes that productivity in a remote work force often is measured through specific deadlines and results that are reported frequently, sometimes on a weekly basis.
“Typically, it helps to have a trial period of six months to create best practices around a remote work strategy,” he says. “It’s important to have a structured approach in launching a system, and for that, you need a telework center of excellence.”

Enterprises seek to improve access to electronic data for legal cases.

Imagine a multinational manufacturing company being asked to search every nook and cranny of its disaster recovery backup tapes for documents containing hundreds of keyword search terms. The company spends millions of dollars conducting a search of its databases, only to face a multimillion-dollar fine for failing to produce all of the necessary documents.
While that manufacturing company is fictional, an increasing number of enterprises are confronting this very real challenge: e-discovery, the process through which electronic data is requested, found, secured and searched to be used as evidence in a court of law. E-mail, instant message logs, PowerPoint presentations and tweets on Twitter are among the data that can be called into evidence in U.S. court cases because of a December 2006 amendment to the U.S. Federal Rules of Civil Procedure to encompass electronically stored information.
“The bottom line is: Whether it’s e-mail, text messaging or some other Web 2.0 technology, everything is grist for the e-discovery mill,” warns Jason R. Baron, director of litigation at the U.S. National Archives and Records Administration, the agency responsible for preserving all of the documents and materials created by the federal government.
With U.S. courts empowered to order companies to quickly produce the right data, organizations must preserve and be prepared to examine mounds of electronic data with the precision of a forensics team. Failure to produce the right records can expose a company to legal fines, unfavorable judgments, increased operating costs and a tarnished corporate reputation.
These risks should serve as a wake-up call for lawyers and IT professionals alike, many of whom maintain a manual, ad hoc approach to e-discovery in response to litigation and regulatory inquiries. Too many businesses rely on a hodgepodge of technologies to reactively identify and report relevant content and data.

It’s now or never

With data volumes at companies rapidly expanding, the time is ripe for organizations to view all electronic documents, no matter how seemingly insignificant, as critical assets that must be managed strategically. Fortunately, companies can take steps to better comply with the rules of e-discovery and minimize their risk of exposure to legal fines and burdensome operating costs. Here are some strategies to consider:

With U.S. courts empowered to order companies to quickly produce the right data, organizations must preserve and be prepared to examine mounds of electronic data with the precision of a forensics team.

• Collaborate. The cafeteria isn’t the only place a company’s IT employees and legal experts should cross paths. Techies and lawyers must work together to develop a strategy for saving and storing electronic documents, as well as making them readily accessible. “Lawyers, IT people and records managers need to sit together and decide who the custodians of data are before a crisis hits,” says Baron.
• Create standardized policies and IT practices. Don’t just leave your data retrieval processes to chance. Companies need to create standardized policies and IT practices for identifying, storing and collecting data, Baron advises. These policies and practices should be enforced enterprise-wide for a consistent e-discovery strategy.
• Establish an interdepartmental knowledge council. One of the smartest ways to avoid finger-pointing when a crisis erupts is to create a team of go-to people—department heads who take on the responsibility of overseeing the technical and legal requirements of e-discovery on an ongoing basis. Consisting of IT, human resources, accounting and legal representatives, this team pools resources and knowledge to set e-discovery policies and procedures as well as update one another on trends and developments in their respective fields.
• Purge regularly. Mergers, acquisitions, Web 2.0 technologies and legacy systems can result in a mountain of antiquated and unnecessary data that is nearly impossible to sift through. To avoid such a fate, companies would be wise to establish clear-cut policies on what records must be kept, how they should be stored and for how long. “Many corporations and institutions do not have a handle on what data each employee has stored in his or her account, so there’s all this knowledge that’s not being properly bundled or stored,” says Baron.
• Evangelize e-discovery. The urgency of readily retrieving electronic data for legal purposes shouldn’t be a secret. Companies need to inform employees of the importance of preserving e-mail and educate them about an organization’s potential exposure to fines and increased operating costs associated with poor records management. Seminars, webinars and guest speakers can help educate employees on e-discovery and minimize litigation risks.
• Put technology to use. Of course, people and processes are only a part of preparing for e-discovery. Powerful technology used for data warehousing, business intelligence (BI), e-mail archiving and records management can also help structure data collections for easy storage and retrieval. A master data management solution, for example, can provide a single view of an enterprise’s data, thereby improving the availability of high-quality information at the right time. Similarly, the right data backup and restoration tools can help companies get a better handle on the exponential growth of their data volumes as well as ensure the long-term survival of data in case of e-discovery needs.

Case closed

In today’s litigious world, companies must be ready and willing to swiftly hand over all electronic documents in a legal case. After all, Baron says, “any particular e-mail can be pulled out of context and used as evidence in litigation.” But retrieving the right information quickly doesn’t have to feel like searching for a needle in a virtual haystack. Interdepartmental collaboration, standardized policies and robust technology tools can help ease the process, enabling companies to produce all relevant records and avoid legal entanglements—because real-world businesses can’t afford to ignore the data challenges of e-discovery.

Data Cleansing for Data Warehousing

How important is Extract, Transform, Load (ETL) to data Warehousing?

Politicians raising money can be used as an analogy to compare data cleansing to data warehousing. There is almost no likelihood of one existing without the other. Data cleansing is often the most time intensive, and contentious, process for data warehousing projects.

What is Data Cleansing?
The elevator pitch: "Data cleansing ensures that undecipherable data does not enter the data warehouse. Undecipherable data will affect reports generated from the data warehouse via OLAP, Data Mining and KPI's."
A very simple example of where data cleansing would be utilized is how dates are stored in separate applications. Example: 11th March 2007 can be stored as '03/11/07' or '11/03/07' among other formats. A data warehousing project would require the different date formats to be transformed to a uniform standard before being entered in the data warehouse.

Why Extract, Transform and Load (ETL)?
Extract, Transform and Load (ETL) refers to a category of tools that can assist in ensuring that data is cleansed, i.e. conforms to a standard, before being entered into the data warehouse. Vendor supplied ETL tools are considerably more easy to utilized for managing data cleansing on an ongoing basis. ETL sits in front of the data warehouse, listening for incoming data. If it comes across data that it has been programmed to transform, it will make the change before loading the data into the data warehouse.
ETL tools can also be utilized to extract data from remote databases either through automatically scheduled events or via manual intervention. There are alternatives to purchasing ETL tools and that will depend on the complexity and budget for your project. Database Administrators (DBAs) can write scripts to perform ETL functionality which can usually suffice for smaller projects. Microsoft's SQL Server comes with a free ETL tool called Data Transforming Service (DTS). DTS is pretty good for a free tool but it does has limitations especially in the ongoing administration of data cleansing.
Example of ETL vendors are Data Mirror, Oracle, IBM, Cognos and SAS. As with all product selections, list what you think you would require from an ETL tool before approaching a vendor. It may be worthwhile to obtain the services of consultants that can assist with the requirements analysis for product selection.

Figure 1. ETL sits in front of Data Warehouses

How important is Data Cleansing and ETL to the success of Data Warehousing Projects?
ETL is often out-of-sight and out-of-mind if the data warehouse is producing the results that match stakeholders expectations. As a results ETL has been dubbed the silent killer of data warehousing projects. Most data warehousing projects experience delays and budget overruns due to unforeseen circumstances relating to data cleansing.
How to Plan for Data Cleansing?
It is important is start mapping out the data that will be entered into the data warehouse as early as possible. This may change as the project matures but the documentation trail will come in extremely valuable as you will need to obtain commitments from data owners that they will not change data formats without prior notice.
Create a list of data that will require Extracting, Transforming and Loading. Create a separate list for data that has a higher likelihood of changing formats. Decide on whether you need to purchase ETL tools and set aside an overall budget. Obtain advice from experts in the field and evaluate if the product fits into the overall technical hierarchy of your organization.

The Evolving Role of the Enterprise Data Warehouse in the Era of Big Data Analytics

The enterprise data warehouse (EDW) community has entered a new realm of meeting new and growing business requirements in the era of big data. Common challenges include:

• extreme integration
• semi- and un-structured data sources
• petabytes of behavioral and image data accessed through MapReduce/Hadoop
• massively parallel relational database
• structural considerations for the EDW to support predictive and other advanced analytics.

 These pressing needs raise more than a few urgent questions, such as:

• How do you handle the explosion and diversity of data sources from conventional and non-conventional sources?
• What new and existing technologies are needed to deepen the understanding of business through big data analytics?
• What technological requirements are needed to deploy big data projects?
• What potential organizational and cultural impacts should be considered?

This white paper provides detailed guidance for designing and administering the necessary deployment processes to meet these requirements. Ralph Kimball fills the hole where there is a lack of specific guidance in the industry as to how the EDW needs to respond to the big data analytics challenge, and what design elements are needed to support these new requirements.

Make room for data

Given enough time, most organizations will reach a point when they wonder how their Teradata systems can possibly take in any more data. Buying new hardware can solve this problem—but that may be unnecessary.
One company found a way to more readily accommodate its growing volumes of data. Teradata Magazine spoke with Dietmar Trummer, senior IT architect at mobilkom austria, about its use of Teradata multi-value compression. Trummer explained how he was able to free space, increase performance and save money, all without reducing service.

Q: What prompted you to investigate using Teradata’s multi-value compression?

A: Starting at the end of 2006, we anticipated performance issues in the near future due to shortage
of free disk space Our users had data analysis results and reports to deliver on time, so in my role as a Teradata system user and developer of smaller data marts, I tried to figure out how to cope with this disk space storage issue.

Q: What approaches did you consider?

A: A Management and administrators at mobilkom austria considered several options: buy new hardware, archive data to external storage or remove indexes. We limited that to these choices:

• Reduce redundant data by deleting specialized data marts. This required developing more complex queries to rebuild the logic of the data marts.
• Aggregate data and skip the details, or reduce history by removing old data. We would lose information and would have to reduce our internal service portfolio.
• Optimize the table definitions based on Teradata technology. This option offers the use of size-optimal data types, primary indexes with optimal table distribution and multi-value compression with optimal size impact.

Q: Why did you select Teradata’s multi-value compression approach?

A: First of all, we didn’t want to develop more complex queries. Second, we didn’t want to reduce
our service portfolio.
I was looking for a solution that had the least possible influence on the daily work of the users and developers. Optimizing table definitions appeared to be worth investigating.
To be honest, the multi-value compression approach was the most interesting. It promised a high potential and also delivered a technical and mathematical challenge. Besides, I had some prior experience implementing the compression approach.
Two years ago during the development of a simple data mart, I had to store a large amount of intermediate data in a table. The space in my staging database was insufficient, so I needed to find a way to reduce the table space.
However, with multi-value compression I could fit my table into the staging database in a short time, without the help of an administrator.
One year later, I adapted what I learned from this experience and developed a method to ease our new storage problems using multi-value compression.

Q: Is this code for using compression as simple as it looks?

 CREATE MULTISET TABLE dwh_ua.uaf_contract ( phone_id INTEGER NOT NULL, call_mode_code CHAR(1) COMPRESS (‘A’,’P’,’S’), source_table_id SMALLINT NOT NULL COMPRESS (1,4,10), charge_usage DECIMAL(18,4) COMPRESS (0.0000), ... ) PRIMARY INDEX (phone_id) 

A: Yes, it is. We optimized our biggest table using multi-value compression: 1TB without compression; 480GB with manual (not optimized) compression; 370GB with optimized compression. The sample code is actually a fraction of the table definition.
That’s the simple part—the challenging part is how to get the values for optimized compression.

Q: What about the importance of data analysis and finding the break-even point of compression? How does multi-value compression work, and how do you find that break-even point?

A: Unlike other databases, the Teradata Database compresses specific user-defined values to zero space. That sounds like magic but, of course, it isn’t. The idea is to reduce the row size of many rows by a large amount, and to enlarge the row size of all rows by a small amount.

Click to enlarge

The code that follows and the corresponding row storage table [see table 1] illustrate how this works: We observed the compression of a single column. All rows with a call_mode_code value contained in the compress list (‘A,’ ‘P’ or ‘S’) skipped the storage for this column in the row data. The compressed values are stored as binary code, and the column was reduced to zero space. Consequently, the rows got smaller. In table 1, the binary code “00” indicates that the value is stored in the row data.

 … call_mode_code CHAR(1) COMPRESS (‘A’,’P’,’S’), … 2 bit: ‘A’ = 01, ‘P’ = 10, ‘S’ = 11 

The binary code is also used in the presence bits to indicate if the values are not compressed. If the table contains no call_mode_code value of ‘A,’ ‘P’ or ‘S,’ small amounts of storage are added in the presence bits and the rows get slightly larger. This explains why it is important to know the values contained in your table.
In summary, if you use compression, all rows—regardless of whether the column value is compressed—have to add the presence bits to their row storage.
Then, the question arose: How can we use this information to find the optimal compression list for a column’s table?
The answer is detailed mathematically, but the principle is not very complex: The values that occur more frequently are more likely to be added to the compression list. Therefore, we had to fill the compression list with the most frequently occurring values. The break-even point is reached when the addition of a new value to the list will not result in a further decrease of the column’s total space consumption.
But be aware that this equation is based only on the static point of view. Data changes over time, so analysis about volatility of data is also necessary and has to be taken into consideration when finding the best compression list.

Q: What kinds of data gain the best compression by setting “obvious” compression values? Are there problems using this technique?

A: That’s difficult to generalize because we experienced different and unexpected kinds of data with great compression performance. Of course columns with large data types have a better compression ratio than those with small data types. Also, columns that contain a few very frequent values gain good compression. These values might be words in natural language, flags, categories, status and years.
But data columns that contain measures can also be a good source for compression—especially default values, zeros and values that are near the most frequent value of a Gaussian or Poisson distributed column.
The problem with what might be considered “obvious” compression values is that it is difficult to find the break-even point—i.e., the optimal compression list—without data analysis. We frequently experienced that manual compression with no data analysis often leads to compression lists that are too large. In these cases, too many values are used for compression, which can lead to “over-compression.” Less would have been better.

Q: You described actual results when using multi-value compression. How much table scan performance improvement did you see? Do you have examples to share?

A: We didn’t analyze the table scan performance in a way that would enable me to present a percentage of performance improvement. Our result is based on the theoretical fact that the table scan performance is determined by the table size—and by our users’ experiences.

Click to enlarge

Nevertheless, we conducted experiments to check a potential negative performance impact caused by “decoding” the compressed values during querying. The results showed that, on the one hand, we could not find a negative performance impact; on the other hand, the table scan performance improvement is directly proportional to the space reduction.

Q: How large of a saving in data size did you see in your results?

A: The bar chart demonstrates a graphical representation of different compression scenarios of a single column. [See figure.] The horizontal axis breaks down the number of presence bits that must be used to code the compression values, and the vertical axis shows the size of the column in megabytes.
The red portion of each bar displays how much space will remain after the compression, and the green portion indicates how much space would be freed. Combined, the size of this column without compression is about 760MB.

Click to enlarge

The bars, from left to right, indicate how much space would be freed when compressing:

• First column = 1 compression bit: the most frequent value
• Second column = 2 compression bits: the most and second-most frequent value
• Third column = 2 compression bits: the most, second-most and third-most frequent value
• Thirteenth column (the rightmost bar in the chart) = 4 compression bits: in this case all occurring values

Notice that the first compression scenario is optimal—the size of this column would be reduced to about 50MB.
Table 2 displays the data analysis results in textual form. The recommendation of the tool is to compress one value (= 1 compression bit), which then generates the corresponding compress clause.
This table is a real example from our biggest table. You can see in row 7 of the table that multi-value compression was used. In this case the developer manually set a large compress list, which resulted in a column size of about 290MB as shown in cell C7. This is one-third the size without compression, indicated in cell C6, but nearly six times the size of the optimally compressed column that appears in cell C8.

Q: Beyond the space savings, what other benefits and cost savings
did you experience?

A: The cost savings stemmed directly from the fact that we didn’t need to buy new hardware or perform other actions that would result in indirect costs, such as reducing our service portfolio.
An indirect benefit was that the overall performance of our system was stabilized because we could raise the level of free storage to the recommended percentage. Multi-value compression had its part in this. It also played a part in other actions, like archiving.

Q: Have there been any impacts on your end users or application developers since you implemented compression? Have they had to change anything?

A: The end users who get our reports and analysis results didn’t realize that anything had changed, except that we were able to consistently meet their service level agreements because of our stabilized system performance.
What is interesting is the impact this procedure has on our developers, like me. The method was used with the intention to free some space in a one-time action. The administrators would analyze our biggest tables and change their table definitions to optimize compression.
Today all application developers in our unit know how to use it. During development of large to medium-sized data marts, a procedure is used to optimize bigger tables; therefore, the tables go into production with optimized compression. What is important is that the developers know about the pitfalls, such as data volatility, which can make a perfect compression change over time to a bad compression.
Also, our administrators didn’t confine themselves to optimizing just the biggest tables. They optimized medium-sized and even small tables. This is why we have approximately 3,000 optimized tables!

Q: Has compression added work to maintenance efforts?

A: Yes, it has. We have been using optimized compression since June 2007, and most tables have been optimized as of January 2008.
We know that we have to check and re-adjust our compression lists because of data volatility. Therefore, the developers, as well as the administrators, re-analyze the biggest tables in the form of control samples. But we have not gained enough experience to estimate how much effort we’ll have to invest in compression maintenance.
To reduce maintenance efforts, we try to avoid compression on highly volatile data columns, or we use more robust and less optimal compression lists for those columns.

Q: How does a compression assessment tool work, and is it available to others?

A: I developed an Excel macro that does data analysis on selected columns of a specified table. It visualizes its results in Excel tables and charts and produces recommendations for the compress clause of the analyzed columns. Tables 1 and 2 show some output of the tool.
I’ve received about 50 e-mails from Teradata users asking about the tool. So far, mobilkom has granted me permission to give the macro tool to them free for personal use. Any future requests would have to be negotiated. Anyone who is interested should e-mail TDCompress@mobilkom.at. Of course, since I am not a software producer, I cannot offer warranties or provide any service for this tool.
I want to mention that there are professional tools on the market that deal with Teradata multi-value compression, its optimization and similar issues. Atanasoft is one vendor of such a tool.

Cube Development for Beginners

From school, we know that although most math tasks have plain-English formulation, we still have to state an equation with x, or, sometimes, a system of equations with x, y, and maybe even more variables, to find a solution. Similarly, in a decision support system, we have to design a set of data objects such as dimensions and cubes, according to the business questions formulated in plain English, so that we can get those questions answered.
This article focuses on just that: constructing a dimensional environment for answering business questions. In particular, it will explain how to come from certain analytic questions to the set of data objects needed to get the answers, using Oracle Warehouse Builder as the development tool. Since things are best understood by example, the article will walk you through building a simple data warehouse.

From Questions to Answers

A typical data warehouse concentrates on sales, to help users find answers to questions regarding the state of the business using the results retrieved from a sales cube based on Time, Product, or Customer criteria. This article example deviates from this practice, however. Here, you’ll look at an example of how you might analyze the outgoing traffic related to a certain Website, using the information retrieved from a traffic cube based on Geography, Resource, and Time criteria.
Suppose you have a Website whose resources are hosted on more than one server, each of which may differ from another in the way it stores traffic statistics. The storage types used vary from a relational database to flat files. You need to consolidate traffic statistics over all of the servers so that you can analyze users activity over resources being accessed, date and time, and geographical location to answer the questions such as the following:

• What are our five most attractive resources on the site?
• Users from what country loaded this resource most of all over the course of the previous year?
• Connections from what region generated most outgoing traffic on the site for the last three months?

At first glance, it looks like you can use SQL alone to get these questions answered. After all, the CUBE, ROLLUP, and GROUPING SETS extensions to SQL are specifically designed to perform aggregation over multiple dimensions of data. Remember, however, that some data here is stored in flat files, which makes the SQL-based approach impractical. Moreover, looking at the above questions, you may notice that some require one year of historical data to get answered. What this means, in practice, is that you’ll also need to access archives containing historical data derived from transaction data and implemented as separate sources. In SQL, managing all those sources and transforming the data in each one into a consistent format for unified query operations would be quite laborious and error-prone.

So, briefly,  the main tasks here are:

• Consolidate data stored in disparate sources into a consistent format.
• Work with historical data derived from transaction data.
• Use preloaded data to speed up queries.
• Organize data in a way convenient for dimensional analysis.

A data warehouse is designed precisely to perform these tasks. Just to recap, a warehouse is a relational database tuned to handle analytic queries rather than transaction processing, and is kept up to date with periodical refreshes and updates, downloading a subset of data from its sources by the ETL (extraction, transformation, and loading) process (scheduled normally for a particular day of the week or at a predetermined time of the day or night). The loaded data is transformed into a consistent format, and loaded into the warehouse target object., Once populated, the warehouse is typically available for queries through dimensional objects such as cubes and dimensions. Schematically, this might look like the figure below:

Figure 1 Gathering data from disparate sources and transforming it into useful information available to business users.

In particular, for this example, a simple warehouse consisting of a cube with several dimensions is an appropriate solution. Since the traffic is the subject matter here, you might want to define outgoing traffic as the cube measure. For simplicity, in this example we will measure outgoing traffic based on the size of the resource being accessed. For example, if someone downloads a 1MB file from your site, then it’s assumed that 1MB of outgoing traffic will be generated. It’s similar in meaning to how the dollar amount of a purchase depends on the price of the product chosen. While dollar amount is normally a measure of a sales cube, price is a characteristic of product that is usually used as a dimension in that cube. A similar situation is here, while outgoing traffic is our traffic cube’s measure, resource size is a characteristic of resource that will be used as a dimension.
Moving on to dimensions, the set of ones to be used in the cube in this example can be determined by examining the list of questions you have to answer. So, looking over the questions listed at the beginning of this section, you might want to use the following dimensions to organize the data in the cube:

• Geography, which organizes the data related to the geography locations the site users come from
• Resource, which categorizes the data related to the site resources
• Time, which is used to aggregate traffic data across time

Each traffic record will have specific values for each geography location, for each resource, and for each day and time. To clarify, the time value in a traffic record refers to the time the resource is accessed.
The next essential step is to define the levels of aggregation of data for each dimension, organizing those levels into hierarchies. As for the Geography dimension, you might define the following hierarchy of levels (with the highest level listed first):

• Region
• Country

The hierarchy of levels for the Resource dimension might look like this:

• Group
• Resource

The time dimension might contain the following hierarchy:

• Year
• Month
• Day

In more complex and realistic scenarios, a dimension may contain more than one hierarchy—for example, fiscal versus calendar year. In this particular example, however, each dimension will have only a single hierarchy.

Implementing a Data Warehouse with Oracle Warehouse Builder

Now that you’ve decided what objects you need to have in the warehouse, you can design and build them .This task can be accomplished with Oracle Warehouse Builde, which is part of the standard installation of Oracle Database, starting with Oracle Database 11g Release 1. To enable it, though, some preliminary steps are required:
First of all, you need to unlock database schemas used by Oracle Warehouse Builder. In Oracle Warehouse Builder 11g Re1ease 1, the OWBSYS schema is used; in 11g Release 2, both OWBSYS and OWBSYS_AUDIT are used. These schemas hold the OWB design and runtime metadata. This can be done with the following commands, connecting to SQL*Plus as SYS or SYSDBA:

ALTER USER OWBSYS IDENTIFIED BY owbsyspwd ACCOUNT UNLOCK;
ALTER USER OWBSYS_AUDIT IDENTIFIED BY owbsys_auditpwd ACCOUNT UNLOCK;

Next, you must create a Warehouse Builder workspace. A workspace contains the objects for one or more data warehousing projects; in complex environments, you may have several workspaces. (Instructions for creating a workspace are in the Oracle Warehouse Builder Installation and Administration Guide for Windows and Linux.Follow the instructions to create a new workspace with a new user as workspace owner.)
Now you can launch the Warehouse Builder Design Center, which is the primary graphical user interface of Oracle Warehouse Builder.  Click Show Details and connect to the Design Center as the newly created workspace user, with the required host/port/service name or Net Service name.
Before going any further, though, let’s outline the set of tasks to accomplish. Broadly described, the tasks to be done in this example are:

• Define a data warehouse to house the dimensional objects described earlier
• Consolidate data from various data sources
• Implement the dimensional objects: dimensions and cube
• Load data extracted from the sources into the dimensional objects

The following sections describe how to accomplish the above tasks, implementing the dimensional solution discussed here. Before you proceed to it, though, you have to decide what implementation model will be used. In fact, you have two options: a relational target warehouse, which stores the actual data in relational tables, or a multidimensional warehouse. In the latter case, dimensional data is stored in an Oracle OLAP analytic workspace. This feature is available in Oracle Database 10g and Oracle Database 11g. For this example, , the dimensional model will be implemented as a relational target warehouse.

Defining a Target Schema

In this initial step, you begin by creating a new project or configuring the default one in the OWB Design Center. Then, you might identify the target schema that will be used to contain the target data objects: the dimensions and cube described earlier in this article.
Assuming you've decided to use the default project MY_PROJECT, let’s move on to creating the target schema. The steps below describe the process of creating the target schema and then a target module upon that schema in the Design Center:

1. In the Globals Navigator, right-click the Security->Users node and select New User in the popup menu to launch the Create User wizard.
2. On the Select DB user to register screen, click Create DB User… to open the Create Database User dialog.
3. In the Create Database User dialog, enter the system user password and then specify the user name, say, owbtarget and the password for a new database user. Then, click OK.
4. You’ve now returned to the Select DB user to register screen, where the newly created owbtarget user should show up in the Selected Users pane. Click Next to continue.
5. On the Check to create a location screen, make certain that the To Create a location checkbox is checked for the owbtarget user, and then click Next.
6. On the Summary screen, click Finish to complete the process.

As a result of the above steps, the owbtarget schema is created in the database. (Also, the owbtarget user should show up under the Security->Users node in the Globals Navigator.) The next step is to create a target module upon the newly created database schema. As a quick recap, you use modules in Warehouse Builder to organize the objects you’re dealing with into subject-oriented groups. So, the following steps describe how you might build an Oracle module upon the owbtarget database schema:

1. In the Projects Navigator, expand the MY_PROJECT->Databases node and right-click the Oracle node.
2. In the popup menu, select New Oracle Module to launch the wizard.
3. On the Name and Description screen of the wizard, specify a name for the module being created, say, target_mdl. As of the module status, you can leave Development.
4. On the Connection Information screen, first make sure that the selected location is the one associated with the target_mdl module being created (it may appear under the TARGET_MDL_LOCATION1 name). Then, you need to provide the connection information for this location. So, click the Edit… button and provide the details of the Oracle database location, specifying owbtarget as the User Name. After you’re done with it, you might want to make sure that everything is correct and test the connection by clicking the Test Connection button. Close all of the dialogs opened by clicking OK to return to the Connection Information screen. Click Next to continue.
5. On the Summary screen, click Finish.
6. In the Design Center, select File->Save All to save the module you just created.

As a result of the above steps, the TARGET_MDL module should appear under the MY_PROJECT->Databases->Oracle node in the Projects Navigator. If you expand the module node, you’ll see what types of objects you can create under it. Among others, it includes nodes for holding: cubes, dimensions, tables, and external tables.

Consolidating data from disparate data sources

Here, you will need not only to extract data from disparate sources but also transform the extracted data in a way so that it can be consolidated into a single data source. Thus, this task usually has the following stages:

1. Import the metadata into OracleWarehouse Builder .
2. Design ETL operations.
3. Load source data into the warehouse.

What you need to start with however is develop a general strategy for extracting the source data, transforming it, and loading it into the warehouse. That said, you first have to make strategic decisions about how to best implement the task of consolidating data from data sources.
As far as flat files are concerned, your first decision to be made is probably about how you’re going to move data from them into the warehouse. The available options include: utilizing SQL*Loader or through external tables. In this particular example, using external tables seems to be a preferable option because the data being extracted from the flat files has to be joined with relational data. If you recall, the example assumes the source data is to be extracted from both database tables and flat files.
Your next decision to be made is of whether to define a source module for the source data objects you’re going to use. Although it’s is generally considered good practice to keep source and target objects in separate modules, for this simple example we will create all objects in a single database module.
Now let’s take a closer look at the data sources that will be accessed.
As mentioned earlier, what we have here is a Website whose resources are hosted on more than one server, each of which differs from another in the way it stores traffic statistics. For example, one server stores it in flat files and another in the database. The content of a flat file containing real-time data and called, say, access.csv might look like this:

User IP,Date Time,Site Resource
67.212.160.0,5-Jan-2011 20:04:00,/rdbms/demo/demo.zip 
85.172.23.0,8-Jan-2011 12:54:28,/articles/vasiliev_owb.html
80.247.139.0,10-Jan-2011 19:43:31,/tutorials/owb_oracle11gr2.html 

As you can see, the above file contains information about accessing resources by users, storing it in comma-separated value (CSV) format. In turn, the server that uses the database in place of flat files might store this same information in an accesslog table with the following structure:

USERIP                          VARCHAR2(15)
DATETIME                        DATE
SITERESOURCE                    VARCHAR2(200)

As you might guess, in this example, IP address data is necessary to identify the geolocation of the user accessing a resource. In particular, it allows you to deduce the geographic location down to the region, country, city, and even organization the IP address belongs to. To obtain this information from an IP address, you might use one of a number of free or paid subscription geolocation databases available today. Alternatively, you might utilize the geolocation information provided by the user during his/her registration, thus relying on the information stored in your own database. In that case, however, you’d probably want to rely on the user’s id rather than the IP address.
For the purpose of this example, we will use a free geolocation database that identifies IP address ranges on a country level, such as MaxMind's GeoLite Country database. (Maxmind also offers more accurate paid databases for country and city-level geolocation data.)For more details, you can check out the MaxMind Website.
The GeoLite Country database is stored as a CSV file containing geographical data for publicly assigned IPv4 addresses, thus allowing you to determine the user's country based on the IP address. To take advantage of this database you need to download the zipped CSV file, unzip it, and then import the data into your data warehouse. The imported data will be then joined with the Web traffic statistics data obtained from the flat files and the database discussed earlier in this section.
Examining the structure of the GeoLite Country CSV file, you may notice that aside of the IP diapasons each of which is assigned to a particular country and defined by their beginning and ending IP addresses represented in dot-decimal notation, it also includes corresponding IP numbers derived from those IP addresses with the help of the following formula:

IP Number = 16777216*w + 65536*x + 256*y + z

where

IP Address = w.x.y.z

The obvious advantage of using IP numbers rather than direct IP addresses is that IP numbers, being regular decimal numbers, can be easily compared, which simplifies the task of determining to what country the corresponding IP address belongs. The problem is however, that our traffic statistics data sources store direct IP addresses rather than the numbers derived from them. You will have to transform the Web traffic data so that the result of this transformation includes IP numbers rather than IP addresses.
The following diagram gives a graphical depiction of transforming and joining data:

 

Figure 2 Oracle Warehouse Builder is extracts, transforms, and joins the source data. 

Remember that dimension and cube data are usually derived from more than one data source. For this example, in addition to the  traffic statistics and geolocation data, you will also need the data sources containing the resource and region information. For that purpose, you might assume you have two database tables: RESOURCES and REGIONS. Assume the RESOURCES table has the following structure:

SITERESOURCE                VARCHAR2(200)    PRIMARY KEY
RESOURCESIZE                NUMBER(12)
RESOURCEGROUP               VARCHAR2(10)

And assume the REGIONS table is defined as follows:

COUNTRYID                   VARCHAR2(2)      PRIMARY KEY
REGION                      VARCHAR2(2)

The data in the above tables will be joined with the Web traffic statistics and geolocation data.
Now that you understand the structure and the meaning of your source data, it’s time to move on and define all the necessary data objects in the Warehouse Builder. We will create the objects needed for the flat files first.  The general steps to perform are the following:

1. Create a new flat file module in the project and associate it with the location where your source flat files reside.
2. Within the newly created flat file module, define the flat files of interest and specify their structure.
3. Add external tables to the target warehouse module defined as discussed in the preceding section, associating those tables with the flat files created in the above step.
4. Import the accesslog, resources, and regions database tables to the target warehouse module.

To create the flat file module, follow these steps in the Design Center:

1. In the Projects Navigator, right-click the MY_PROJECT->Files node and choose New Flat File Module in the popup menu.
2. On the Name and Description screen of the wizard, specify a name for the module being created, or leave the default. Then, click Next.
3. On the Connection Information screen, click the Edit… button on the right of the Location select box.
4. In the Edit File System Location dialog, specify the location where the flat files from which you want to extract data can be found. Click OK to come back to the wizard.
5. On the Summary screen, click Finish to complete the wizard.

Now you can define a new flat file within the newly created flat file module. Let’s start with creating a flat file object for the access.csv file you saw earlier in this section:

1. In the Projects Navigator, right-click the MY_PROJECT->Files->FLAT_FILE_MODULE_1 node and select New Flat File to launch the Create Flat File wizard.
2. On the Name and Description screen of the wizard, specify a name for the flat file object being created, say, ACCESS_CSV_FF. Then, make sure to specify the physical file name. On this page, you can also change the character set or accept the default presented in the wizard.
3. On the File Properties screen, make sure that the record delimiter character is set to carriage return: <CR>, and the field delimiter is set to (,).
4. On the Record Type Properties screen, make sure that Single Record is selected.
5. On the Field Properties screen, you’ll need to define the structure of the access.csv file record, setting the SQL properties for each field. Please note that the first set of properties that follows the Name property are SQL*Loader properties. You don’t have to define those properties however, because you’re going to use the external table option rather than the SQL*Loader utility. External tables are the most performant way to load flat file data into Oracle data warehouses. So, you’ll need to scroll right to get to the second set of properties: SQL properties. Define the properties as follows:
   
   

   Name             SQL Type       SQL Length USERIP           VARCHAR2       15 
   DATETIME         DATE
   SITERESOURCE     VARCHAR2       200 

6. On the Summary screen, click Finish to complete the wizard.

At this point, you may want to commit your changes to the repository. Select File->Save All to commit your changes.
Repeat the above steps for the GeoIPCountryWhois.csv file that contains the geolocation data, defining the following properties on the Field Properties screen of the wizard:

Name             SQL Type       SQL Length 
STARTIP          VARCHAR2       15 
ENDIP            VARCHAR2       15 
STARTNUM         VARCHAR2       10 
ENDNUM           VARCHAR2       10 
COUNTRYID        VARCHAR2       2 
COUNTRYNAME      VARCHAR2       100 

Once these are done, define external table objects in the the target module. These will expose the flat file data as tables in the database To define an external table upon the ACCESS_CSV_FF flat file object created earlier, follow the steps below:

1. In the Projects Navigator, expand the MY_PROJECT->Databases->Oracle->TARGET_MDL node, right-click External Tables and select New External Table.
2. On the Name and Description screen of the wizard, specify a name for the external table, say, ACCESS_CSV_EXT.
3. On the File Selection screen, select ACCESS_CSV_FF that you should see under the FLAT_FILE_MODULE1.
4. On the Locations screen, select the location where the external table will be deployed.
5. On the Summary screen, click Finish to complete the wizard.

Repeat the above steps for the GEOLOCATION_CSV_FF flat file object.
Now that you have all of the necessary object definitions created you have to deploy them to the target schema before you can use them. Another preliminary step is to make sure that the target schema in the database is granted the privileges to create and drop directories. For that, you can connect to SQL*Plus as sysdba and issue the following statements:

GRANT CREATE ANY DIRECTORY TO owbtarget; 
GRANT DROP ANY DIRECTORY TO owbtarget; 

After that, you can come back to the Design Center and proceed to deploying. The following steps describe how to deploy the external tables:

1. In the Projects Navigator, expand the MY_PROJECT->Databases->Oracle->TARGET_MDL->External Tables node and select both the ACCESS_CSV_EXT and GEOLOCATION_CSV_ EXT nodes.
2. Right-click the selection and choose Deploy … The process starts with compiling the selected objects and then proceeds to deploying, which may take some time to complete.

If the deployment has completed successfully, this means you have the definitions of the external tables created in the target schema within the database, and, therefore, you can query those tables. To confirm that everything is going as planned so far, it would be a good idea to look at the data you can access through the newly deployed tables. The simplest way to do this is by selecting the Data… command in the popup menu that appears when you right-click the node of an external table in the Project Navigator.
While you should have no problem with the GEOLOCATION_CSV_ EXT table containing about 140,000 rows, you may see nothing when it comes to the ACCESS_CSV_EXT data. The first thing you might want to check out to determine where the problem lies are the ACCESS_CSV_EXT’s access parameters, which you can access through the ALL_EXTERNAL_TABLES data dictionary view. Thus, being connected as sysdba to SQL*Plus, you might issue the following query:

SELECT access_parameters FROM all_external_tables 
WHERE table_name ='ACCESS_CSV_EXT'; 

The output should look like this:

records delimited by newline
    characterset we8mswin1252
    string sizes are in bytes
    nobadfile
    nodiscardfile
    nologfile
    fields terminated by ','
    notrim
    ("USERIP" char,
     "DATETIME" char,
     "SITERESOURCE" char
    )

Examining the above, you might notice that the DATETIME field comes with no date mask, which may cause a problem when accessing the date data whose format differs from the default. The problem can be fixed with the following ALTER TABLE statement:

ALTER TABLE owbtarget.access_csv_ext ACCESS PARAMETERS
   (records delimited by newline
    characterset we8mswin1252
    string sizes are in bytes

 nobadfile

 nodiscardfile
    nologfile
    fields terminated by ','
    notrim
    ("USERIP" char,
     "DATETIME" char date_format date mask "dd-mon-yyyy hh24:mi:ss",
     "SITERESOURCE" char
    )
   );

Now, returning to the Design Center, if you click the Execute Query button in the Data-ACCESS_CSV_EXT window, you should see the rows generated from the data derived from the access.csv file.
All that is left to complete the task of creating the data source object definitions is to import the metadata for the source database tables ACCESSLOG, RESOURCES, and REGIONS described earlier in this section. To do this, you can follow these steps:

1. In the Projects Navigator, expand the MY_PROJECT->Databases->Oracle->TARGET_MDL node and right-click Tables. In the popup menu, select Import->Database Objects… to launch the Import Metadata wizard.
2. On the Filter Information screen of the wizard, select Table as the type of objects you want to import.
3. On the Object Selection screen, move accesslog, regions, and resources tables from the Available to the Selected pane.
4. On the Summary and Import screen, click Finish to complete the wizard.

As a result of the above steps, the ACCESSLOG, REGIONS, and RESOURCES objects must appear under the MY_PROJECT->Databases->Oracle->TARGET_MDL->Tables node in the Projects Navigator.

Aggregating Data Across Dimensions with Cubes

Having the source object definitions created and deployed, let’s build the target structure. In particular, you’ll need to build a Traffic cube to be used for storing aggregated traffic data. Before moving on to building the cube, though, you’ll have to build the dimensions that will make up the edges of it.
If you recall from the discussion in the beginning of the article, you need to define the following three dimensions to organize the data in the cube: Geography, Resource, and Time. The following steps describe how you might build the Geography dimension and then load data into it:

1. In the Projects Navigator, right-click node MY_PROJECT->Databases->Oracle-> TARGET_MDL->Dimensions and select New Dimension in the popup menu to launch the Create Dimension wizard.
2. On the Name and Description screen of the wizard, type in GEOGRAPHY_DM in the Name field.
3. On the Storage Type screen, select ROLAP.
4. On the Levels screen, enter the following levels:

   Region
   Country

5. On the Level Attributes screen, make sure that all the level attributes for both the Region and Country levels are checked.
6. On the Slowly Changing Dimension screen, select Type1:Do not keep history.
7. After you’re done with the wizard, you should see the GEOGRAPHY_DM object under the MY_PROJECT->Databases->Oracle-> TARGET_MDL->Dimensions node in the Project Navigator. Now, right-click it and select Bind. As a result, table GEOGRAPHY_DM_TAB should appear under the MY_PROJECT->Databases->Oracle->TARGET_MDL->Tables node. Right-click it and select Deploy… Also, the GEOGRAPHY_DM_SEQ should appear under the MY_PROJECT->Databases->Oracle->TARGET_MDL->Sequences node, which you have to deploy too. After both deployments have been completed, come back to GEOGRAPHY_DM and deploy it.

Now you will define an ETL mapping that loads the GEOGRAPHY_DM dimension from the source data. The steps are as follows:

1. In the Projects Navigator, expand the MY_PROJECT->Databases->Oracle->TARGET_MDL node and right-click Mappings. In the popup menu, select New Mapping to launch the Create Mapping dialog. In this dialog, specify the mapping name, say, GEOGRAPHY_DM_MAP. After you click OK, the Mapping Editor canvas should appear.
2. In the Projects Navigator, expand the MY_PROJECT->Databases->Oracle->TARGET_MDL->Tables node, and then drag and drop the REGIONS table to the GEOGRAPHY_DM_MAP’s mapping canvas in the Mapping Editor.
3. Then, expand the MY_PROJECT->Databases->Oracle->TARGET_MDL->Dimensions node and drag and drop the GEOGRAPHY_DM dimension to the mapping canvas, to the right of the REGIONS table operator.
4. In the mapping canvas, connect the COUNTRYID attribute of the REGIONS operator to the COUNTRY.NAME attribute of GEOGRAPHY_DM, and then connect the COUNTRYID attribute of the REGIONS operator to the COUNTRY.DESCRIPTION attribute of GEOGRAPHY_DM.
5. Similarly, connect the REGION attribute of the REGIONS operator to the REGION.NAME, COUNTRY. REGION_NAME and REGION.DESCRIPTION attributes of GEOGRAPHY_DM.
6. In the Projects Navigator, expand the MY_PROJECT->Databases->Oracle->TARGET_MDL->Mappings node. Right-click GEOGRAPHY_DM_MAP, and then select Deploy… in the popup menu.
7. The final step here is to load the GEOGRAPHY_DM dimension. To do this, you need to execute the GEOGRAPHY_DM_MAP mapping. Thus, right-click GEOGRAPHY_DM_MAP and select Start…
8. Similarly, you should create and deploy the RESOURCE_DM and RESOURCE_DM_MAP objects, using the resources table as the source and specifying the following levels in the RESOURCE_DM dimension:

      
   Group
   Resource

9. When defining the RESOURCE_DM dimension attributes, don’t forget to increase the length of both the NAME and DESCRIPTION attributes to 200, so that they can be connected with the SITERESOURCE attribute of the RESOURCES operator.

Finally, you need to create a time dimension. The easiest way to do this is to use the Create Time Dimension wizard, which defines both a Time Dimension object and an ETL mapping to load it for you. For details on how to create and populate a time dimension, you can refer to the Creating Time Dimensions section in the Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide.
Once you have the dimensions set up, carry out the following steps to define a cube:

1. In the Projects Navigator, right-click node MY_PROJECT->Databases->Oracle->TARGET_MDL->Cubes and select New Cube in the popup menu.
2. On the Name and Description screen of the wizard, enter the cube name in the Name field: TRAFFIC.
3. On the Storage Type screen, select ROLAP: Relational storage.
4. On the Dimensions screen, move all the available dimensions from the Available Dimensions pane to the Selected Dimensions pane, so that you have the following dimensions selected:

      
   RESOURCE_DM
   GEOGRAPHY_DM
   TIME_DM

5. On the Measures screen, enter the following measures:

   OUT_TRAFFIC with the data type NUMBER 

6. After the wizard is completed, the TRAFFIC cube and TRAFFIC_TAB table should appear in the Project Navigator. You must deploy them before going any further.

As with a dimension, the next step to be done is to create a mapping defining how the source data will be loaded into the cube.

Transforming the Source Data for the Cube Loading

So now, you have to design ETL mappings that will transform the source data and load it into the cube. Here is the list of the transformation operations you’ll need to design:

1. Combine the rows of the access_csv_ext external table and the accesslog database table into a single row set consolidating the traffic statistics data.
2. Transform IP addresses within the traffic statistics data into corresponding IP numbers to simplify the task of determining the diapason? an IP address in question belongs to.
3. Join the traffic statistics data with the geographical data.
4. Aggregate the joined data, loading the output data set to the cube.

As mentioned, the above operations must be described in a mapping. Before moving on to creating a mapping, though, let’s define a transformation described at the second step above. This transformation will be implemented as a PL/SQL function. The following steps describe how you might do that without leaving the Design Center:

1. In the Projects Navigator, expand the MY_PROJECT->Databases->Oracle->TARGET_MDL->Transformations node and right-click Functions. In the popup menu, select New Function.
2. In the Create Function dialog, specify the name for the function, say, IpToNum, and click OK. As a result, the Function Editor associated with the function being created is displayed.
3. In the Function Editor, move on to the Parameters tab and add parameter IPADD, setting data type to VARCHAR2 and I/O to Input.
4. In the Function Editor, move on to the Implementation tab and edit the function code as follows:

    
    p NUMBER;
    ipnum NUMBER;
    ipstr VARCHAR2(15);
   BEGIN
    ipnum := 0;
    ipstr:=ipadd;
    FOR i IN 1..3 LOOP
     p:= INSTR(ipstr, '.', 1, 1); 
     ipnum := TO_NUMBER(SUBSTR(ipstr, 1, p - 1))*POWER(256,4-i) + ipnum;
     ipstr := SUBSTR(ipstr, p + 1);
    END LOOP;
    ipnum := ipnum + TO_NUMBER(ipstr);
    RETURN ipnum;
   END;

5. In the Projects Navigator, right-click the newly created IPTONUM node and select Deploy…

Now you can create a mapping in which you then define how data from the source objects will be loaded into the cube:

1. In the Projects Navigator, expand the MY_PROJECT->Databases->Oracle->TARGET_MDL node and right-click Mappings. In the popup menu, select New Mapping to launch the Create Mapping dialog. In this dialog, specify the mapping name: TRAFFIC_MAP. After you click OK, the Mapping Editor canvas should appear.
2. To accomplish the task of combining the rows of the access_csv_ext and accesslog tables, first drag and drop the ACCESS_CSV_EXT and ACCESSLOG table objects from the Project Navigator to the Mapping Editor canvas. As a result, the operators representing the above tables should appear in the canvas.
3. From the Component Palette, drag and drop the Set Operation operator to the mapping canvas. Then, in the Property Inspector, set the Set operation property of the operator to UNION.
4. In the mapping canvas, connect the INOUTGRP1 group of the ACCESSLOG operator to the INGRP1 group of the SET OPERATION operator. As a result, all corresponding attributes under those groups will be connected automatically.
5. Next, connect the OUTGRP1 group of the ACCESS_CSV_EXT operator to the INGRP2 group of the SET OPERATION operator.
6. The next task to accomplish is joining the traffic statistics data with the geographical data. To begin with, drag and drop the GEOLOCATION_CSV_EXT table object from the Project Navigator to the mapping canvas.
7. From the Component Palette, drag and drop the Joiner operator to the mapping canvas. Then, connect the OUTGRP1 group of the GEOLOCATION_CSV_EXT operator to the INGRP1 group of the JOINER operator. Next, connect the OUTGRP1 group of the SET OPERATION operator to the INGRP2 group of the JOINER operator.
8. In the Projects Navigator, expand the MY_PROJECT->Databases->Oracle->TARGET_MDL->Transformations->Functions node and drag and drop the IPTONUM function to the mapping canvas.
9. In the mapping canvas, select and delete the line connecting the USERIP output attribute of the SET OPERATION operator with the USERIP input attribute of the JOINER operator. Connect the USERIP output attribute of the SET OPERATION operator with the IPADD input attribute of the IPTONUM operator. Then, connect the output attribute of the IPTONUM operator with the USERIP input attribute of the JOINER operator. You also need to change the data type of the JOINER’s USERIP input attribute for NUMERIC. This can be done on the Input Attributes tab of the Joiner Editor dialog, which you can invoke by double-clicking the header of the JOINER operator.
10. In the Joiner Editor dialog, move on to the Groups tab and add an input group INGRP3. Then, click OK to close the dialog.
11. From the Project Navigator, drag and drop the RESOURCES table object to the Mapping Editor canvas. Then, connect the INOUTGRP1 group of the RESOURCES operator with the INGRP3 group of the JOINER operator.
12. Click the header of the JOINER operator. Then move onto the JOINER Property Inspector, in which you should click the Join Condition button. As a result, the Expression Builder dialog should appear, in which you build the following join condition:

    (INGRP2.USERIP  BETWEEN  INGRP1.STARTNUM  AND  INGRP1.ENDNUM)  
    AND  
    (INGRP2.SITERESOURCE  =  INGRP3.SITERESOURCE)

13. Next, you need to add an Aggregator that will aggregate the output of the Joiner operator. From the Component Palette, drag and drop the Aggregator operator to the mapping canvas.
14. Connect the OUTGRP1 group of the JOINER operator with the INGRP1 group of the AGGREGATOR operator. Then, click the header of the AGGREGATOR operator and move on to the Property Inspector, in which click the Ellipsis button to the right of the Group By Clause field to invoke the Expression builder dialog. In this dialog, specify the following group by clause for the aggregator:

    INGRP1.COUNTRYID,INGRP1.SITERESOURCE,INGRP1.DATETIME

15. Double-click the header of the AGGREGATOR operator and move on to the Output tab of the dialog, where add the RESOURCESIZE attribute, specifying the following expression for it: SUM(INGRP1.RESOURCESIZE).
16. From the Component Palette, drag and drop the Expression operator to the mapping canvas. Then, double-click the header of the EXPRESSION operator and move on to the Input Attributes tab of the dialog, in which define the DATETIME attribute of type DATE. Then, move on to the Output Attributes tab and define the DAY_START_DAY attribute of type DATE, specifying the following expression:

       
    TRUNC(INGRP1.DATETIME, 'DD')

17. Delete the line connecting the DATETIME attribute of the JOINER operator with the DATETIME attribute of the AGGREGATOR operator. Then, connect the JOINER’s DATETIME to the EXPRESSION’s DATETIME and connect the EXPRESSION’s DAY_START_DAY to the AGGREGATOR’s DATETIME.
18. In the Projects Navigator, expand the MY_PROJECT->Databases->Oracle->TARGET_MDL->Cubes node and drag and drop the TRAFFIC cube object to the canvas.
19. Connect the attributes in the OUTGRP1 group of the AGGREGATOR operator with the TRAFFIC operator’s attributes as follows:

         
     RESOURCESIZE to OUT_TRAFFIC
     COUNTRYID to GEOGRAPHY_DM_NAME 
     DATETIME to TIME_DM_DAY_START_DATE
     SITERESOURCE to RESOURCE_DM_NAME

By now the mapping canvas should look like the figure below:

 

Figure 3 The mapping canvas , showing the TRAFFIC_MAP mapping that loads data from the source objects into the cube.

20. You are now ready to deploy the mapping. In the Project Navigator, right-click the TRAFFIC_MAP object under the MY_PROJECT->Databases->Oracle->TARGET_MDL->Mapping node and select Deploy… This actually generates
21. After the deployment has been successfully completed, you can execute the mapping, starting the job for the ETL logic defined. To do this, right-click the TRAFFIC_MAP object and select Start…

Once you’ve completed the above steps, you have the TRAFFIC cube populated with the data from the sources in accordance with the logic implemented in the mapping. Practically speaking, however, you rather have the fact table (it’s the TRAFFIC_TAB table in this particular example) populated with data. In other words, cube records are stored in the fact table. The cube itself is just a logical representation or visualization of the dimensional data used here.
Similarly, dimensions are physically bound to corresponding dimension tables, which store dimensions’ data in the database. Dimension tables are joined to the fact table with foreign keys, making up a model known as a star schema (because the diagram of such a schema resembles a star). Oracle Database's query optimizer can apply powerful optimization techniques when it comes to star queries (join queries issued against the fact table and the dimension tables joined to it), thus providing efficient query performance for the queries answering business questions.

Conclusion

Business intelligence as the process of gathering information with the purpose to support decision making needs a foundation for its environment. A data warehouse provides such a foundation being a relational database that is designed just for that: consolidating information gathered from disparate sources and providing access to that information to business users so that they can make better decisions.
As you saw in this article, a small data warehouse may consist of a single cube and just a few dimensions, which make up the edges of that cube. In particular, you looked at an example of how traffic statistics data can be organized into a cube whose edges contain values for Geography, Resource, and Time dimensions.

Email task, Session and Workflow notification : Informatica

One of the advantages of using ETL Tools is that functionality such as monitoring, logging and notification are either built-in or very easy to incorporate into your ETL with minimal coding. This Post explains the Email task, which is part of Notification framework in Informatica. I have added some guidelines at the end on a few standard practices when using email tasks and the reasons behind them.
1. Workflow and session details.
2. Creating the Email Task (Re-usable)
3. Adding Email task to sessions
4. Adding Email Task at the Workflow Level
5. Emails in the Parameter file (Better maintenance, Good design).
6. Standard (Good) Practices
7. Common issues/Questions
1. Workflow and session details.
Here is the sample workflow that I am using. The workflow (wkf_Test) has 2 sessions.
s_m_T1 : Loads data from Source to Staging table (T1).
s_m_T2 : Loads data from Staging (T1) to Target (T2).
The actual mappings are almost irrevant for this example, but we need atleast two sessions to illustrate the different scenarios possible.

Test Workflow (2 Sessions)

2. Creating the Email Task (Re-usable)
Why re-usable?. Becuase we’d be using the same email task for all the sessions in this workflow.
1. Go to Workflow Manager and connect to the repository and the folder in which your workflow is present.
2. Go to the Workflow Designer Tab.
3. Click on Workflow > edit (from the Menu ) and create a workflow variable as below (to hold the failure email address).

Failure Email workflow variable

4. Go to the “Task Developer” Tab and click create from the menu.
5. Select “Email Task”, enter “Email_Wkf_Test_Failure” for the name (since this email task is for different sessions in wkf_test).
Click “Create” and then “Done”. Save changes (Repository -> Save or the good old ctrl+S).
6. Double click on the Email Task and enter the following details in the properties tab.

Email User Name : $$FailureEmail   (Replace the pre-populated session variable $PMFailureUser, 
                                    since we be setting this for each workflow as needed).
Email subject   : Informatica workflow ** WKF_TEST **  failure notification.
Email text      : (see below. Note that the server varibles might be disabled, but will be available during run time).
Please see the attched log for Details. Contact ETL_RUN_AND_SUPPORT@XYZ.COM for further information.
 
%g
Folder : %n
Workflow : wkf_test
Session : %s

Create_Email_Task

3. Adding Email task to sessions
7. Go to the Workflow Tab and double click on session s_m_T1. You should see the “edit task” window.
8. Make sure you have “Fail parent if this task fails” in the general tab and the “stop on errors” is 1 on the config tab.
Go to “Components” tab.
9. For the on-failure email section, select “reusable” for type and click the LOV on Value.
10. Select the email task that we just created (Email_Wkf_Test_Failure), and click OK.

Adding Email Task to a session

4. Adding Email Task at the Workflow Level
Workflow-level failure/suspension email.
If you are already implementing the failure email for each session (and getting the session log for the failed session), then you should consider just suspending the workflow. If you don’t need session level details, using the workflow suspension email makes sense.
There are two settings you need to set for Failure notification emails at workflow level.
a) Suspend on error (Check)
b) Suspension email (Select the email task as before). Again, remember that if you have both session and workflow level emails, you’ll get two emails, if a session fails and causes the parent to fail.

Informatica workflow suspension email

Workflow Sucesss email
In some cases, you might have a requirement to add a success email once the entire workflow is complete.
This helps people know the workflow status for the day without having to access workflow monitor or asking run teams for the status each day. This is particularly helpful for business teams who are more concerned whether the process completed for the day.
1) Go to the workflow tab in workflow manager and click Task > Create > Email Task.
2) Enter the name of the email task and click OK.
3) In the general tab, select “Fail parent if this task fails”. In the properties tab, add the necessary details
Note that the variables are not available anymore, since they are only applicable at the session level.
4) Add the necessary Session.status=”succeedeed” for all the preceding tasks.
Here’s how your final workflow will look.

Informatica success emails

5. Emails in the Parameter file (Better maintenance, Good design).
We’ve created the workflow variable $$FailureEmail and used it in the email task. But how and when is the value assigned?
You can manage the failure emails by assigning the value in the parameter file.
Here is my parameter file for this example. You can seperate multiple emails using comma.

infa@ DEV /> cat wkf_test.param
[rchamarthi.WF:wkf_Test]
$$FailureEmail=rajesh@etl-developer.com
 
[rchamarthi.WF:wkf_Test.ST:s_m_T1]
$DBConnection_Target=RC_ORCL102
 
[rchamarthi.WF:wkf_Test.ST:s_m_T2]
$DBConnection_Target=RC_ORCL102

While it might look like a simpler approach initially, hard-coding emails IDs in the email task is a bad idea. Here’s why.
Like every other development cycle, Informatica ETLs go thorugh Dev, QA and Prod and the failure email for each of the environment will be different. When you promote components from Dev to QA and then to Prod, everything from Mapping to Session to Workflow should be identical in all environments. Anything that changes or might change should be handled using parameter files (similar to env files in Unix). This also works the other way around. When you copy a workflow from Production to Development and try to make changes, the failure emails will not go to business users or QA teams as the development parameter file only has the developer email Ids.
If you use parameter files, here is how it would be set up in different environments once.
After the initial set up, you’ll hardly change it in QA and Prod and migrations will never screw this up.

In development   : $$FailureEmail=developer1@xyz.com,developer2@xyz.com"
In QA / Testing  : $$FailureEmail=r=developer1@xyz.com,developer2@xyz.com,QA_TEAM@xyz.com
In Production    : $$FailureEmail=IT_OPERATIONS@xyz.com,ETL_RUN@xyz.com,BI_USERS@xyz.com

6. Standard (Good) Practices
These are some of the standard practices related to Email Tasks that I would recommend. The reasons have been explained above.
a) Reusable email task that is used by all sessions in the workflow.
b) Suspend on error set at the workflow level and failure email specified for each session.
c) Fail parent if this task fails (might not be applicable in 100% of the cases).
c) Workflow Success email (based on requirement).
d) Emails mentioned only in the parameter file. (No Hard-coding).
7. Common issues/Questions
Warning unused variable $$FailureEmail and/or No failure emails:
Make sure you use the double dollar sign, as all user-defined variables should. (unless you are just using the integration service variable $PMFailureEmailUser). Once that is done, the reason for the above warning and/or no failure email could be…
a) You forgot to declare the workflow variable as described in step 3 above or
b) the workflow parameter file is not being read correctly. (wrong path, no read permissions, invalid parameter file entry etc.)
Once you fix these two, you should be able to see the success and failure emails as expected.

Informatica Unable to fetch log

Quite often, you might come across the following error when you try to get the session log for your session.

Unable to Fetch Log.
The Log Service has no record of the requested session or workflow run.

The first place to start debugging this error would be one-level up from the session log, which is the workflow log.
The most common reasons I have seen this happen is because of the following .
a ) One or more of the following parameters have been specified incorrectly.

• Session Log File directory
• Session Log File Name
• Parameter Filename (at the session (and/or) workflow level)

b) You do not have the necessary privileges on the directory to create and modify (log) files.
Whatever be the case , the workflow log is your next point of debugging. In my test scenario , I entered the following parameters for the log file name and directory to simulate this error.
Session Log File directory : $InvalidLogDir\
Session Log File Name : s_m_test_cannot_fetch_log.log
When I ran the workflow, the session failed and I could not get the session log (becuase it was never created) . The error in the workflow log is as follows.

Session task instance [s_m_test_cannot_fetch_log] : 
[CMN_1053 [LM_2006] Unable to create log file
[$InvalidLogDir/download/INFA/QuickHit/ParmFiles/s_m_test_cannot_fetch_log.log.bin].

There seems to be too much guesswork to fix this error based on the posts on internet forums . Somehow, developers seem to think of this error as something wrong with the Informatica client installation.
The next time you get this error, please check your workflow log.
If you have seen this happen before for another reproducible case , please comment and I’ll modify the post to include the same if needed.