ETL TOOLS

Informatica Tutorial

2013-06-15T00:49:00.000+05:30

Informatica is a widely used ETL tool for extracting the source data and loading it into the target after applying the required transformation. In the following section, we will try to explain the usage of Informatica in the Data Warehouse environment with an example. Here we are not going into the details of data warehouse design and this tutorial simply provides the overview about how INFORMATICA can be used as an ETL tool.

Note: The exchanges/companies that are explained here is for illustrative purpose only.

Bombay Stock Exchange (BSE) and National Stock Exchange (NSE) are two major stock exchanges in India in which the shares of ABC Corporation and XYZ Private Limited are traded between Mondays through Friday except Holidays. Assume that a software company “KLXY Limited” has taken the project to integrate the data between two exchanges BSE and NSE.

In order to complete this task of integrating the Raw data received from NSE & BSE, KLXY Limited allots responsibilities to Data Modelers, DBAs and ETL Developers. During this entire ETL process, many IT professionals may involve, but we are highlighting the roles of these three personals only for easy understanding and better clarity.

Data Modelers analyze the data from these two sources(Record Layout 1 & Record Layout 2), design Data Models, and then generate scripts to create necessary tables and the corresponding records.
DBAs create the databases and tables based on the scripts generated by the data modelers.
ETL developers map the extracted data from source systems and load it to target systems after applying the required transformations.

Informatica Upgrade Process:

2013-06-15T00:45:00.000+05:30

Informatica Upgrade Process:
Stages across upgrade can be categorized as below:

Upgrading the domain and server file: run the Informatica server installer and select the upgrade option. The domain upgrade wizard installs the server files and configures the domain. If the domain has multiple nodes, you must upgrade on all the nodes.

The following table describes the actions that the installer performs when you upgrade Informatica:

Tasks	Description
1. Installs Informatica.	Installs Informatica directories and files into the new Directory.
2. Copies infa_shared directory.	Copies the contents of the infa_shared directory from the existing installation directory into the new installation Directory.
3. Copies mm_files directory.	Copies the contents of the mm_files directory from the default location in the existing installation directory into the New installation directory.
4. Upgrades the domain.	Upgrades the domain to run version 9.0.1 application Services. The upgrade retains the user and administrator accounts in The domain.
5. Starts Informatica Services.	Starts Informatica Services on the node.

2.Upgrading the application services: After you upgrade the domain and server files, log in to the Administrator Tool and upgrade the application services. The service upgrade wizard provides a list of all application that must be upgraded. It upgrades the services based on the order required by the dependent objects.
3.Upgrading the Informatica client: To upgrade the Informatica client, run the Informatica client installer and Select the upgrade option
Pre-Upgrade Tasks
Before you upgrade the domain and server files, complete the following tasks:
1. Review the prerequisites.
2. Verify the file descriptor settings.
3. Verify the configuration of the environment variables used by the installer.
4. Clear the configuration of environment variables that pertain to previous installations of Informatica.
5. Prepare the domain.
6. Prepare the Power Center repository.
7. Prepare the Power Center Profiling warehouse.
8. Prepare for upgrade from Power Center 8.6.1

Export Reference Table Manager Data.
Prepare Metadata Manager.
Prepare the Data Analyzer repository.

9. Shut down the domain.
Upgrading the Domain and Server in Graphical Mode:
You can upgrade the Informatica domain and server files in graphical mode on Windows or UNIX.
1. Verify that your environment meets the minimum system requirements and complete the pre-upgrade tasks.
2. Log in to the machine with the same user account that you used to install the previous version.
3. Close all other applications.
4. To begin the upgrade on Windows, run install.bat from the root directory.
To begin the upgrade on UNIX, use a shell command line to run install.sh from the root directory, and then Select the option for graphical mode installation.
5. In the Installation Type window, select Upgrade to Informatica 9.0.1 and click next.

The Upgrade Pre-Requisites window displays the upgrade system requirements. Verify that all requirements are met before you continue the upgrade.

6. Click Next.
7. In the Upgrade Directory window, enter the following directories

Directory	Description
1.Directory of the Informatica Product to upgrade.	Directory that contains the previous version of Power Center that you want to upgrade.
2.Directory for Informatica 9.0.1	Directory in which to install Informatica 9.0.1. Enter the absolute path for the installation directory. The directory cannot be the same as the directory that contains the previous version of Power Center. The directory names in the path must not contain spaces or the following special characters: @\|* $ # ! % ( ) { } [ ] , ; ' On Windows, the installation directory must be on the current machine.

Click Next.
The upgrade wizard displays a warning to shut down the Informatica domain before you continue the upgrade.
9. Click OK.
10. In the Pre-Installation Summary window, review the upgrade information, and click Install to continue.
The upgrade wizard installs the Informatica server files to the Informatica 9.0.1 installation directory.
11. In the Domain Configuration Upgrade window, the upgrade wizard displays the database and user account information for the domain configuration repository to be upgraded.

Property	Description
Database type	Database for the domain configuration repository.
Database user ID	Database user account for the domain configuration repository.
User password	Password for the database user account.
Tablespace	Displayed for IBM DB2 only. Name of the tablespace for the upgraded domain configuration repository tables. If the database of the domain configuration repository that you are upgrading does not use a 32 K tablespace, this property is blank. Enter the name of a tablespace with a page size of 32 K. In a single-partition database, if you do not specify a tablespace name, the installer writes the upgraded tables in the default tablespace. The default tablespace must be 32 K. In a multi-partition database, you must specify a 32 K tablespace.

The upgrade wizard displays the database connection string for the domain configuration repository based on how the connection string of the previous version was created at installation:

If the previous version used a JDBC URL at installation, the upgrade wizard displays the JDBC connection properties, including the database address and service name.
If the previous version used a custom JDBC connection string at installation, the upgrade wizard displays the custom connection string.
Optionally, you can specify additional JDBC parameters to include in the connection string. To provide Additional JDBC parameters, select JDBC parameters and enter a valid JDBC parameter string.

12. Click Test Connection to verify that you can connect to the database, and then click OK to continue.
13. Click Next.
On the Port Configuration Upgrade window, the upgrade wizard displays the default port numbers assigned to the domain and node components.
14. You can specify new port numbers or use the default port numbers.
The following table describes the ports that you can specify:

Port	Description
Service Manager port	Port number used by the Service Manager in the node. Client applications and the Informatica command line programs use this port to communicate to the services in the domain.
Informatica Administrator port	Port number used by the Administrator tool. Available if you upgrade a gateway node.
Informatica Administrator shutdown port	Port number used by the Administrator tool to listen for shut down commands. Available if you upgrade a gateway node.

15. Click Next.
On Windows, the upgrade wizard creates a service to start Informatica. By default, the service runs under the same user account as the account used for installation. You can run the Windows service under a different User account.
16. Select whether to run the Windows service under a different user account.
The following table describes the properties that you set:

Property	Description
Run Informatica under a different user account	Indicates whether to run the Windows service under a different user account.
User name	User account with which to run the Informatica Windows service. Use the following format: DomainName\UserAccount This user account must have the Act as operating system permission.
Password	Password for the user account with which to run the Informatica Windows service.

17. Click Next.
The Post-Upgrade Summary window indicates whether the upgrade completed successfully.
18. Click Done.
Upgrade the Application Services:
1. Configure Informatica Environment Variables
You can configure the INFA_JAVA_OPTS, INFA_DOMAINS_FILE, and INFA_HOME environment variables to store memory, domain, and location settings.
(i) INFA_JAVA_OPTS
For example, to configure 1 GB of system memory for the Informatica daemon on UNIX in a C shells.
setenv INFA_JAVA_OPTS “-Xmx1024m”
(ii) INFA_DOMAINS_FILE
Set the value of the INFA_DOMAINS_FILE variable to the path and file name of the domains.infa file. If you configure the INFA_DOMAINS_FILE variable, you can run infacmd and pmcmd from a directory other than /server/bin.
Configure the INFA_DOMAINS_FILE variable on the machine where you install the Informatica services. On Windows, configureINFA_DOMAINS_FILE as a system variable.
(iii) INFA_HOME
Use a softlink in UNIX for any of the Informatica directories. To configure INFA_HOME so that any Informatica application or service can locate the other Informatica components it needs to run, set INFA_HOME to the location of the Informatica installation directory.
2. Configure Locale Environment Variables
Use the following command to verify that the value for the locale environment variable is compatible with the Language settings for the machine and the type of code page you want to use for the repository:
Eg : locale –a
Locale for Oracle Database Clients
if the value is american_america.UTF8, set the variable in a C shell with the following command:
setenv NLS_LANG american_america.UTF8
Service Upgrade:
Use the service upgrade wizard to upgrade services.
1. In the Informatica Administrator header area click Manage > Upgrade.
2. Select the objects to upgrade.
3. Click Next.
4. If dependency errors exist, the Dependency Errors dialog box appears. Review the dependency errors and click OK. Then, resolve dependency errors and click next.
5. Enter the repository login information. Optionally, choose to use the same login information for all Repositories.
6. Click Next.
The service upgrade wizard upgrades each service and displays the status and processing details.
7. When the upgrade completes, the Summary section displays the list of services and their upgrade status.
Click each service to view the upgrade details in the Service Details section.
8. Optionally, click Save Report to save the upgrade details to a file.
If you choose not to save the report, you can click Save Previous Report the next time you launch the Service upgrade wizard.
9. Click Close.
10. Restart upgraded services.
After you upgrade the PowerCenter Repository Service, you must restart the service and its dependent Services.
Informatica Client Upgrade
1. Close all applications.
2. Run install.bat from the root directory.
The Upgrade Pre-Requisites window displays the system requirements. Verify that all installation Requirements are met before you continue the installation.
3. Click Next.
On the Select Component window, select the Informatica client you want to upgrade.
You can upgrade the following Informatica client applications:

Informatica Developer
PowerCenter Client

If both Informatica Developer and PowerCenter Client are installed on the machine, you can upgrade the tools in the same process.
4. On the Upgrade Directory window, enter the following directories:

Directory	Description
Directory of the Informatica client to upgrade	Directory that contains the previous version of the Informatica client tool that you want to upgrade
Directory for Informatica 9.0.1 client tools	Directory in which to install the Informatica 9.0.1 client tools Enter the absolute path for the installation directory. The installation directory must be on the current machine. The directory names in the path must not contain spaces or the following special characters: @\|* $ # ! % ( ) { } [ ] , ; '

5. Click Next.
6. On the Pre-Installation Summary window, review the installation information, and click Install.
The installer copies the Informatica client files to the installation directory.
The Post-installation Summary window indicates whether the upgrade completed successfully.
7. Click Done.
Post Upgrade Tasks:
Informatica Domain

Configure LDAP Connectivity.
Update the Log Events Directory.
Update ODBC Data Sources.
Update Statistics for the Domain Configuration Repository.
View Log Events from the Previous Informatica Version.

Metadata Manager Service

Reload Metadata Manager Resources
Update the Metadata Manager Properties File
Reference Table Manager

For detailed study on version Upgrade, you may go through Documents from informatica corporation on upgrade.

Read more: http://informaticatutorials-naveen.blogspot.com/#ixzz2WDfms5w2
Under Creative Commons License: Attribution Non-Commercial

Design Tip #155 Going Agile? Start with the Bus Matrix

2013-05-15T01:23:00.002+05:30

Many organizations are embracing agile development techniques for their DW/BI implementations. While we strongly concur with agile’s focus on business collaboration to deliver value via incremental initiatives, we’ve also witnessed agile’s “dark side.” Some teams get myopically focused on a narrowly-defined set of business requirements. They extract a limited amount of source data to develop a point solution in a vacuum. The resultant standalone solution can’t be leveraged by other groups and/or integrated with other analytics. The agile deliverable may have been built quickly, so it’s deemed a success. But when organizations lift their heads several years down the agile road, they often discover a non-architected hodgepodge of stovepipe data marts. The agile approach promises to reduce cost (and risk), but some organizations end up spending more on redundant, isolated efforts, coupled with the ongoing cost of fragmented decision-making based on inconsistent data.

It’s no surprise that a common criticism of the agile approaches for DW/BI development is the lack of planning and architecture, coupled with ongoing governance challenges. We believe the enterprise data warehouse bus matrix (described in our article “The Matrix: Revisited”) is a powerful tool to address these shortcomings. The bus matrix provides a master plan for agile development, plus it identifies the reusable common descriptive dimensions that provide both data consistency and reduced time-to-market delivery in the long run.

With the right mix of business and IT stakeholders in a room, along with a skilled facilitator, the bus matrix can be produced in relatively short order (measured in days, not weeks). Drafting the bus matrix depends on a solid understanding of the business’s needs. Collaboration is critical to identifying the business’s core processes. It’s a matter of getting the team members to visualize the key measurement events needed for analyses. Involving business representatives and subject matter experts will ensure the team isn’t paralyzed by this task. You’ll likely discover that multiple business areas or departments are interested the same fundamental business processes. As the business is brainstorming the list of measurement events, IT representatives are bringing a dose of reality about the available operational source data and any known limitations.

Once the matrix has been drafted, the team can then adopt agile development techniques to bring it to life. Business and IT management need to identify the single business process matrix row that’s both a high priority for the business, and highly feasible from a technical perspective. Focusing on just one matrix row minimizes the risk of signing up for an overly ambitious implementation. Most implementation risk comes from biting off too much ETL system design and development; focusing on a single business process, typically captured by a single operational source system, reduces this risk. Incremental development can produce the descriptive dimensions associated with the selected matrix row until sufficient functionality is available and then the dimensional model is released to the business community, as we describe in Design Tip #135: Conformed Dimensions as the Foundation for Agile Date Warehousing.

White Paper: Evolving Role of the Enterprise Data Warehouse in the Era of Big Data Analytics

2013-05-15T01:22:00.004+05:30

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.

Writing a Proper Data Warehousing Request for Proposal!

2012-10-07T12:46:00.002+05:30

1. General Information for Vendors

§ Lists the Project Vision

§ Project timelines

§ Nature of contract

§ Scope

Reasoning: Give an overview of project and format for the RFP process.

2. System Background

Description of the Technical Environment

a) Description on the Client and Server specifications at the Ministry.

b) Breakdown of user types for the data warehouse:

User Types (Examples Only)	Description	Number of Users
Novice User	Uses “canned” reports and queries.	x
Power User	Frequent user, edits reports, queries directly and builds cubes for multi-dimensional analysis.	x

c) Software tools for the data warehouse:

· ETL Tools

· Report and Query Tools

· Data Analysis

· Data Visualization Tools

Reasoning: Give an overview of the expected installation. Also specify points of particular

interest.

3. System Requirements

List of General Requirements

→ Give an overview of the data warehouse environment.

Reasoning: An overview of the rational for the data warehouse implementation.

Mandatory Requirements

a) List if the software is Client/Server based or web-based. Described what functions are available via the Internet browser. Describe what plug-ins (if any) are necessary for the client machines.

b) State that the vendor must communicate with the Ministry’s database and applications. Ask the vendor how they propose to do this.

c) The vendor must be committed to its products remaining compatible with future versions of Oracle. Ask the vendor to offer documents as evidence of commitment to this environment.

d) The vendor’s system must enable the overall optimization of the Ministry’s environment:

I) Ask for how the vendor’s system minimizes user response time while maximizing the use of system resources.

ii) Ask the vendor to explain the system’s architecture for data selection, processing, formatting.

iii) Ask for evidence that network traffic is minimized and how the need to process large data sets on the client is also minimized.

Reasoning: Based on user interviews and technical review, these are the must

have features for the data warehouse installation.

Strongly Desired Features

Examples:

→ The vendor’s system must allow users to schedule reports.

→ Does your system permit users to schedule at a certain time

and date? Daily? Weekly? Based on an event?

Describe how scheduling is accomplished and the options for scheduling reports.

Reasoning: Based on the requirements analysis, the items listed here are

additional features that will be good to have but not mandatory.

Desired Features

Examples:

→ Does your system possess a redundant query control?

This feature returns a saved version of a report whenever an identical report has been executed earlier – the report, output format, and raw data all remain constant in the interim. Describe how this works in your system.

What are the limits in terms of how far back reports are archived?

Reasoning: Following the rational of “Strongly Desired Features” above, this

section lists a lower desired set of requirements.

§ Product Profile

o Product name

o Product description

o Current release level

o Date current release level was available

o Projected date for the next release candidate

o Current products install base

o Industry verticals of installation

o Number of installations

o Breakdown of installations/users

Reasoning: Gauge vendor’s product specifications and maturity.

§ Consulting

o Describe the level of involvement the consultants will have at each stage of the project.

o What is the experience of the consultants?

o Location of Consultants.

o Please forward resumes of consultants.

Reasoning: Evaluate experience and competency of the vendor’s team.

Also enquire on the consultants’ geographic location, this will help evaluate

possible response rate.

§ Business Partners

o Ask the vendor to state which products are fully integrated to your solution.

o Describe the interconnectivity and interface requirements.

Reasoning: Enquire what other products can integrate well with the solution, e.g. Crystal Reports, Cognos. Also, the vendor will state the ease at which integration can occur.

§ Full feature of the business intelligence solution and products

o Ask what makes the product stand out.

Reasoning: This will give the vendor a chance to distinguish their products.

Further business intelligence capabilities such as OLAP, Data mining can be evaluated.

§ Application areas

o Describe the areas the solution supports i.e. budgeting, financial planning etc.

Reasoning: Evaluate which areas the data warehousing solution can serve.

The “Mandatory Features” section above will inform the vendor the area which are necessary for the Ministry. This section gives further information on the vendors solution and possible areas which the Ministry can use in the future.

§ Web-based and standalone clients

o Query if the solution supports both web-based and standalone clients. Ask which functionality is not supported in both versions (i.e. what works in a standalone client and not web-based and vice versa)

Reasoning: The architecture is of a fundamental importance. This section will enquire if the vendor’s solution will match the Ministry requirements.

§ Pricing and Licensing Model

o How much does the product cost, including initial training, support and consulting?

Reasoning: Gauge the cost of the solution and other indirect costs.

§ System Installation and Tool Administration

Examples:

o Please describe the platform and system requirements of your product.

o For web-based clients, are there specific requirements on the type and version of browser that may be used (e. g. Netscape vs. Internet Explorer)?

o Please describe the overall architecture of your product (e. g. Client/ Server, two-tier/ three-tier architecture, etc.), including how your product functions in a geographically distributed environment.

o Please describe the steps involved in installing your product.

o Please list the administrative functions that are included in your product (e. g. defining roles, backup/ recovery, etc.).

o How do tool administrators detect when your product requires additional hardware or requires human intervention (e. g. to restart a process, etc.).

o How often are major/ minor software upgrades available? How would we be notified of a new software release?

o Please describe how standalone (I. e. non-web-based) clients are distributed to end-user desktops. How are software upgrades distributed to end-users?

o Are there ways to make the upgrade process transparent to the end-users?

Reasoning: The above questions and statements will give further information on the vendor’s solution regarding the installation and administration of your product.

§ Security

o How are end-users and administrators authenticated to your product?

o How do end-users and administrators gain access to reports, data sets, etc. (e. g. via roles)?

o Please fully describe how reports and data are protected from unauthorized users.

o Please describe how row-level access to the data is attained.

o What level of encryption is used on passwords, data, reports, etc.?

o How does the existence of a firewall change the security architecture?

o How does the web-based client differ in terms of security from the standalone client?

Reasoning: Questions on security must also pass the security criteria set by regulators or industry standards .

§ Performance and Scalability

o Does the product supports a substantial increase in data size, frequency and complexity of end-user queries?

o How is performance measured within your product?

Reasoning: Evaluation of how the product can scale to future expectations.

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

2012-10-07T12:45:00.000+05:30

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.

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.

2012-10-07T12:42:00.003+05:30

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.

2012-10-07T12:40:00.001+05:30

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

2012-10-07T12:38:00.001+05:30

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

2012-10-07T12:34:00.000+05:30

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?

Make room for data

2012-10-07T12:32:00.000+05:30

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

2012-10-07T12:27:00.000+05:30

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:

In the Globals Navigator, right-click the Security->Users node and select New User in the popup menu to launch the Create User wizard.
On the Select DB user to register screen, click Create DB User… to open the Create Database User dialog.
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.
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.
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.
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:

In the Projects Navigator, expand the MY_PROJECT->Databases node and right-click the Oracle node.
In the popup menu, select New Oracle Module to launch the wizard.
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.
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.
On the Summary screen, click Finish.
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:

Import the metadata into OracleWarehouse Builder .
Design ETL operations.
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:

Create a new flat file module in the project and associate it with the location where your source flat files reside.
Within the newly created flat file module, define the flat files of interest and specify their structure.
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.
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:

In the Projects Navigator, right-click the MY_PROJECT->Files node and choose New Flat File Module in the popup menu.
On the Name and Description screen of the wizard, specify a name for the module being created, or leave the default. Then, click Next.
On the Connection Information screen, click the Edit… button on the right of the Location select box.
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.
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:

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.
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.
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 (,).
On the Record Type Properties screen, make sure that Single Record is selected.
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 
```
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:

In the Projects Navigator, expand the MY_PROJECT->Databases->Oracle->TARGET_MDL node, right-click External Tables and select New External Table.
On the Name and Description screen of the wizard, specify a name for the external table, say, ACCESS_CSV_EXT.
On the File Selection screen, select ACCESS_CSV_FF that you should see under the FLAT_FILE_MODULE1.
On the Locations screen, select the location where the external table will be deployed.
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:

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.
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:

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.
On the Filter Information screen of the wizard, select Table as the type of objects you want to import.
On the Object Selection screen, move accesslog, regions, and resources tables from the Available to the Selected pane.
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:

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.
On the Name and Description screen of the wizard, type in GEOGRAPHY_DM in the Name field.
On the Storage Type screen, select ROLAP.
On the Levels screen, enter the following levels:
```
Region
Country
```
On the Level Attributes screen, make sure that all the level attributes for both the Region and Country levels are checked.
On the Slowly Changing Dimension screen, select Type1:Do not keep history.
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:

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.
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.
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.
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.
Similarly, connect the REGION attribute of the REGIONS operator to the REGION.NAME, COUNTRY. REGION_NAME and REGION.DESCRIPTION attributes of GEOGRAPHY_DM.
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.
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…
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
```
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:

In the Projects Navigator, right-click node MY_PROJECT->Databases->Oracle->TARGET_MDL->Cubes and select New Cube in the popup menu.
On the Name and Description screen of the wizard, enter the cube name in the Name field: TRAFFIC.
On the Storage Type screen, select ROLAP: Relational storage.
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
```

On the Measures screen, enter the following measures:
```
OUT_TRAFFIC with the data type NUMBER 
```

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:

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.
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.
Join the traffic statistics data with the geographical data.
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:

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.
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.
In the Function Editor, move on to the Parameters tab and add parameter IPADD, setting data type to VARCHAR2 and I/O to Input.

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;

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:

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.
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.
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.
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.
Next, connect the OUTGRP1 group of the ACCESS_CSV_EXT operator to the INGRP2 group of the SET OPERATION operator.
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.
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.
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.
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.
In the Joiner Editor dialog, move on to the Groups tab and add an input group INGRP3. Then, click OK to close the dialog.
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.
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)
```

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.
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
```

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).
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')
```

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.
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.

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.

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
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

2012-08-11T18:12:00.000+05:30

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

2012-08-11T18:10:00.000+05:30

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.

Informatica Workflow Successful : No Data in target !

2012-08-11T18:09:00.002+05:30

This is a frequently asked in the Informatica forums and the solution is usually pretty simple. However, that will have to wait till the end because there is one important thing that you should know before you go ahead and fix the problem.
Your workflow should have failed in the first place. If this was in Production, Support Teams should know that something Failed. Report users should know the data in the Marts is not ready for reporting. Dependent workflows should wait until this is resolved. This coding practice basically violates the Age-old Principle of fail-fast when something goes wrong, instead of continuing flawed execution pretending “All is well”, causing the toughest-to-debug defects.
Of Course, this is not specific to Informatica. It is not uncommon to see code in other languages which follows this pattern. The only issue that is specific to Informatica is that this is the default behavior when you create a session. So you might have this “bug” in your code without even knowing it.

Stop On Errors:
Indicates how many non-fatal errors the Integration Service can encounter before it stops the session. Non-fatal errors include reader, writer, and DTM errors. Enter the number of non-fatal errors you want to allow before stopping the session. The Integration Service maintains an independent error count for each source, target, and transformation. If you specify 0, non-fatal errors do not cause the session to stop.
Optionally use the $PMSessionErrorThreshold service variable to stop on the configured number of errors for the Integration Service.

In Oracle, it is the infamous “when others then null” .

BEGIN
  <process SOME Data>
exception
   WHEN others 
       THEN NULL;  
END;
/

In Java..Something like..

try {
   fooObject.doSomething();
}
catch ( Exception e ) {
   // do nothing
}

The solution to this problem in Informatica is to set a limit on the number of allowed errors for a given session using one of the following methods.
a) Having “1″ in your default session config : Fail the session on the first non-fatal error.
b) Over-write the session Configuration details and enter the “Stop On Errors” to “1″ or a fixed number.
c) Use the $PMSessionErrorThreshold variable and set it at the integration service level. You can always override the variable in the parameter file. Take a look at this Article on how you can do that.
Remember, if your sessions do not belong to one of these categories, you are doing it wrong!.
a) Your session Fails and Causes the workflow to fail whenever any errors occur.
b) You allow the session to continue despite some (expected) errors, but you always send the .bad file and the log file to the support/business team in charge.
Why is there no data in Target
The solution to “why the records didn’t make it to the target” is usually pretty evident in the session log file. The usual case (based on most of the times this question is asked) is becuase all of your records are failing with some non-fatal error.
The only point of this article is to remind you that your code has to notify the right people when the workflow did not run as planned.

ORA-01403: no data found

2012-08-11T18:08:00.003+05:30

Pretty common oracle error. Raised when you are trying to fetch data from sql into a pl/sl variable and the sql does not return any data.
>> Using the data from this schema

 
SQL> SELECT COUNT(*)
  FROM scott_emp
  WHERE empno = 9999;
 
  COUNT(*)
----------
         0
 
SQL> DECLARE
   l_ename scott_emp.ename%TYPE;
   l_empno scott_emp.empno%TYPE := 9999;
BEGIN
   SELECT ename
     INTO l_ename
     FROM scott_emp
     WHERE empno = l_empno;
END;
/
 
DECLARE
*
ERROR at line 1:
ORA-01403: no DATA found
ORA-06512: at line 5

What to do next
1. Re-Raise it with a error message that provides more context.

DECLARE
   l_ename scott_emp.ename%TYPE;
   l_empno scott_emp.empno%TYPE := 9999;
BEGIN
   SELECT ename
     INTO l_ename
     FROM scott_emp
     WHERE empno = l_empno;
EXCEPTION
  WHEN no_data_found
   THEN raise_application_error(-20001,'No employee exists with employee id ' || l_empno);
END;
/
ERROR at line 1:
ORA-20001: No employee EXISTS WITH employee id 9999
ORA-06512: at line 11

2. Suppress the error if this is a valid business scenario and do the necessary processing.
Example CASE : If a user has a preference to display the numbers in local currency, convert the amount, else, display in USD.

 
CREATE OR REPLACE PROCEDURE p_calc_sales_metrics(
   p_user_id IN users.user_id%TYPE,
   p_profit  IN net_sales.profit%TYPE
) AS
  l_pref_currency user_prefs.pref_currency%TYPE;
  l_profit_local_amt net_sales.profit%TYPE;
BEGIN
 
---other code
 BEGIN
 
 SELECT pref_currency
   INTO l_pref_currency
  WHERE user_id = p_user_id
 
        l_cur_conv_factor := get_conv_rate('USD',l_pref_currency);
 
 
 exception
   WHEN no_data_found 
    THEN l_cur_conv_factor := 1;
 END;
 
--- other code..
 
 l_profit_local_amt :=  p_profit * l_cur_conv_factor;
 
 
END;
/

3. Functions, by design, do not raise the NO_DATA_FOUND exception, instead they return null to the calling program.

CREATE OR REPLACE FUNCTION STGDATA.f_get_ename(
   i_empno IN scott_emp.empno%TYPE
) RETURN scott_emp.ename%TYPE
AS
  l_ename scott_emp.ename%TYPE;
BEGIN
 
  SELECT ename
    INTO l_ename
    FROM scott_emp
   WHERE empno = i_empno;
 
  RETURN l_ename;  
 
END;
/
 
SQL> SELECT f_get_ename(7839) FROM dual;
 
F_GET_ENAME(7839)
---------------------
KING
 
SQL>  SELECT f_get_ename(9999) FROM dual;
 
F_GET_ENAME(9999)
-------------------------------------------
 
 
SQL> SELECT nvl(f_get_ename(9999),'NULL RETURNED') FROM dual;
 
NVL(F_GET_ENAME(9999),'NULLRETURNED')
------------------------------------------------
NULL RETURNED

Introducing Informatica Cloud 9 – The Defining Capability For Cloud Computing

2012-08-10T13:26:00.000+05:30

Today we made an announcement called Informatica Cloud 9. This is the culmination of many years of hard work and effort and builds on the Informatica 9 announcement we made last week. So what is so special about Informatica Cloud 9? Is it the new Platform-as-a-Service offering? Or it is the new Cloud Services we delivered? Or is it the new capabilities on Amazon EC2? What are all these things and why are they important?
Let me explain:
Informatica Cloud 9 started over four years ago when we noticed the beginnings of a revolution happening around us – namely Cloud Computing. Many of you may not be familiar with our work in the Cloud, but we have been very focused on delivering data integration as a “Software-as-a-Service” (SaaS) solution. This has meant taking our enterprise class capabilities and simplifying the interface to an extent where a simple non-technical business user can point-and-click to connect a cloud application with an on-premise application.
We have always believed that this is critical to realize since business users typically put off the integration tasks because they don’t want to rely on IT for time or resources. So we wanted to make it incredibly easy for the business user to do this on their own. We focused on salesforce.com and built data movement and then data synchronization capabilities. Indeed, our Data Loader Service for salesforce.com was voted the best data integration solution on their AppExchange this year.
However, our belief is that while business users must be able to define an integration task, their IT colleagues should be able to see the same integration processes from within their environment. Only then will the business user be able to truly bring new applications into critical usage. The same needs to be true the other way around – we want to be able to define complex integrations and enable business users to be able to run them through an easy-to-use browser interface. Only then will the CFO, and others, be confident that they can trust the data being deployed across public and private clouds and begin to embrace cloud computing for core business requirements. We call this Business-IT collaboration and it was a big part of last week’s Informatica 9 announcement as well.
Cloud computing is re-defining IT and data integration needs to follow-suit. It is data integration that will be THE defining capability for cloud computing – not outsourced datacenters, or sexy new application solutions. So, to really embrace cloud computing, one needs a whole new way of delivering enterprise data integration that brings together the ease of use that business users require with the sophistication that must be delivered for IT architects. Otherwise cloud computing will simply remain the domain of non-critical fancy-looking applications on the periphery of true enterprise business requirements.
Informatica Cloud 9 is a significant step forward in solving this problem of providing data integration in the clouds. With today’s announcement, anyone who is involved with Informatica can build, share and deploy any data integration components and deploy them anywhere. These components may relate to data quality or data integration or indeed, with time, any of the other components that make up the Informatica 9 Platform. After all, Informatica Cloud 9 is built on the comprehensive and unified Informatica 9 and therefore will eventually inherit the core capabilities of the platform.
Informatica Cloud 9 delivers on this belief and provides three critical components towards that goal:

With Data Quality Cloud Edition and PowerCenter Cloud Edition on Amazon EC2 we are providing a low-cost hourly build capability for IT users. With this, developers can build complex integrations between applications that can be published to non-technical line-of-business managers to consume and manage. Indeed any of the 50,000+ developers on the Informatica TechNet, or any of our Systems Integration partners will be able to do this. These integrations can be thought of as templates – picked up by anyone using the Informatica Cloud 9 Platform and re-deployed.
With the Informatica Cloud 9 Platform-as-a-Service we are providing the multi-tenant, scalable enterprise engine for deploying data integration in the clouds. One note that you may not be familiar with – we are already running over 17,000 jobs a day through our multi-tenant Informatica Cloud Services and moving over three Billion rows a month of client data.
With our new Informatica Cloud 9 services we are enhancing our own simple-to-use suite of data integration cloud applications that continue to evolve the role of the business user to be self-sufficient in their approach to accessing and integrating trustworthy cloud-based and on-premise data.

Now an enterprise can enable business users to use any cloud application and remain in control of their most critical asset – their data. Developers can share re-usable templates across the business; System Integrators can build data integration templates for specific cloud and on-premise applications and deploy them across to their clients; consultants can move from client to client with toolboxes of pre-configured templates.
Informatica Cloud 9 is the evolution of enterprise data integration to the clouds. Take a few moments please to re-read the press release and, in particular, the quotes therein:

“Informatica Cloud 9 will dramatically simplify cloud-to-cloud and cloud to on-premise data integrations…”
“… the ability to develop more complex mappings and workflows and run them as custom services for line of business managers will allow us to continue to provide self-service, while IT remains in control…”
“… we’ve developed an SAP data integration as a service solution…”
“… we plan to develop re-usable templates to accelerate time to market and reduce total cost of ownership for our customers… “
“Informatica Cloud Platform gives us the power and flexibility to meet enterprise requirements and deliver solutions to non-technical business users …”

Hopefully now you can see why we are all on Informatica Cloud 9 here!

How Big Data Changes Data Integration

2012-08-10T13:24:00.004+05:30

With Big Data systems now in the mix within most enterprises, those charged with data integration are interested in how their world will soon change. Rest assured, most of the patterns of integration that we deal with today will still be around for years to come.
However, there are some clear trends that data integration managers need to understand, such as:

The ability to imply structure to the data at the time of use.
The ability to store both structured and unstructured data.
The need for faster data integration technology.

The ability to imply structure to the data at the time of use refers to the fact that Big Data systems using the Hadoop set of technologies have the ability to add a structure at the time of use. Thus, you don’t need to pre-define a structure as we do in the world of relational data, you can map a structure to existing data.
While this has certain advantages, such as the ability to create dynamic structure around in-line analytical services, this also causes some complexity when dealing with data integration technology. Most data integration technology leverages some type of structure on either end of the integration flow. The idea is that you need to layer a structure as the data is consumed, translated, and produced from one system or data store to another.
The ability to store both structured and unstructured data, as related to the layering in a dynamic structure, brings both complexity and flexibility. Big Data systems are basically file systems with anything and everything stored in them. This means that documents, text, and data are all intermingled. This information may be bound to a structure, or freestanding. In any event, you need to provide the ability to move both structured and unstructured data from store to store.
The need for faster data integration technology is a result of the fact that we deal with much larger volumes of data than more traditional enterprise systems. Therefore, there is more data that has to be moved from data store to data store. Thus, there is a renewed focus on data integration technology’s ability to keep up with the data integration performance requirements.
In many respects, the ability to create a data integration solution that is able to move larger volumes of structured and unstructured data between data stores is dependent upon the way you’ve designed the data integration flows, as much as the data integration technology itself. As Big Data systems move into your enterprise, and you join them together using data integration technology, you’ll find that the patterns of the integration flows need to change as well. Before these systems are put into production, it’s a good idea to review what needs to change and best practices around the design of the integration flows.
Big Data is more of an evolution around the way we store and deal with data. It provides more primitive commodity mechanisms that provide more flexibility and the ability to deal with larger amounts of data using highly distributed data management technology. Data integration technology needs to adapt to this change, which is further reaching than anything we’ve seen of late.

How Integration Platform-as-a-Service Impacts Cloud Adoption

2012-08-10T13:22:00.001+05:30

Did you know that Forrester estimates in their 10 Cloud Predictions For 2012 blog post that on average organizations will be running more than 10 different cloud applications and that the public Software-as-a-Service (SaaS) market will hit $33 billion by the end of 2012?
However, in the same post, Forrester also acknowledged that SaaS adoption is led mainly by Customer Relationship Management (CRM), procurement, collaboration, and Human Capital Management (HCM) software and that all other software segments will “still have significantly lower SaaS adoption rates”. It’s not hard to see this in the market today, with cloud juggernaut salesforce.com leading the way in CRM, and Workday and SuccessFactors doing battle in HCM, for example. Forrester claims that amongst the lesser known software segments, Product Lifecycle Management (PLM), Business Intelligence (BI), and Supply Chain Management (SCM) will be the categories to break through as far as SaaS adoption is concerned, with approximately 25% of companies using these solutions by 2012.
I am not at all surprised that CRM, and HCM are leading the way as far as SaaS application adoption is concerned. One only needs to examine the reason behind why these categories took off. During the so-called “Great Recession,” companies wanted an efficient way in which to grow revenues and cut costs. On the revenue side of the equation, companies found that sales force automation (SFA) helped them close more deals faster, and increased customer visibility allowed them to focus on customer retention as well as potential upsell and cross-sell opportunities. On the costs side, some have argued that functions such as HR and Talent Management were the first to be moved to the cloud as they were considered “non-core”.
As data volumes, global deployment, and end-user adoption grew, it became increasingly clear that out-of-the-box CRM or HCM functionality was not going to cut it, and that customization options would be necessary. Out of this necessity evolved the world of Platform-as-a-Service (PaaS). Similar to the concept of SaaS, a PaaS environment involves built-in scalability, reliability, security, databases, interfaces to web services and a container with development tools for building custom apps. Salesforce.com was one of the early creators of this new cloud ecosystem with its Force.com platform. This platform, which now numbers over 220,000 apps (as of the publication of this blog post) provides numerous options to customize a CRM deployment as well as build websites, and numerous productivity-enhancing and vertical specific apps from the ground up that tied into the core CRM functionality.
While the PaaS ecosystem provides a great avenue to build custom apps and increase cloud application adoption, too often it is tied into the code-base of the dominant SaaS player that brought it into existence. As a result, other non-CRM and non-HCM functions such as PLM, SCM, BI, and ERP still largely remain in the on-premises world.
This is where iPaaS, or integration PaaS comes into play. Each of these other non-CRM functions is an important part of the value chain, whether upstream, or downstream. PLM and SCM systems for instance interact frequently with ERP systems. BI and analytics software have multiple touch points with all these systems. Integrating all these systems together and tying them to specific customer records in the CRM system has been such a time-consuming task that most SaaS providers simply chant the mantra of “web services” when asked by customers how they can connect various SaaS ecosystems together. Web services typically accomplish a very specific business process and specific task between two different SaaS applications, and the web services APIs do not lead to repeatability. In fact, a Slashdot blog on the API economy mentioned that there were some 5,000 APIs estimated by the end of 2012 and some 30,000 estimated in the next four years.
The proliferation of APIs along with SaaS adoption only strengthens the need for an integration PaaS that abstracts the underlying orchestrations of these APIs to end users. An integration PaaS, allows developers to build full (or partial, if desired) native connectivity to every single object within an application, whether SaaS or not. By building native connectors, every permutation and combination of objects between different SaaS applications is possible, thereby increasing the possibility for companies to choose those SaaS apps that fit their business function or department. With increasing confidence of the existence of the integration PaaS, companies will continue to adopt SaaS apps in other LOBs, and not just the mainstream CRM or HCM categories. This in turn spurs the other SaaS category providers to invest more in R&D and come out with even more advanced functionality.
With the increased innovation occurring across all SaaS applications, we can expect more and more complex use cases involving larger amounts of data. All of this coupled with custom apps built on competing PaaS platforms will only further increase the use of an integration PaaS to achieve cloud data integration.

Delivering IT Value with Master Data Management and the Cloud

2012-08-10T13:20:00.005+05:30

Over the last few years most enterprises have implemented several (if not more) large ERP and CRM suites. Although these applications were meant to have self-contained data models, it turns out that many enterprises still need to manage “master data” between the various applications. So the traditional IT role of hardware administration and custom programming has evolved to packaged application implementation and large scale data management. According to Wikipedia: “MDM has the objective of providing processes for collecting, aggregating, matching, consolidating, quality-assuring, persisting and distributing such data throughout an organization to ensure consistency and control in the ongoing maintenance and application use of this information.” Instead of designing large data warehouses to maintain the master data, many organizations turn to packaged Master Data Management (MDM) packages (such as Informatica MDM). With these tools at hand, IT shops can then build true Customer Master, Product Master (Product Information Management – PIM), Employee, or Supplier Master solutions.
MDM solutions vary by industry in terms of tactical approaches taken – e.g., pharmaceutical/life sciences will adopt semi-batch, database-centric approaches for master physician data to be deployed to sales forces, while financial services providers and online retailers will require near real-time, business process-centric solutions to compete in the business-to-consumer (B2C) online world. These different types of implementations require technical IT expertise in delivering an end-to-end solution. Based on quarterly surveys of the MDM Institute Business Council™ (8,000+ subscribers to the MDM Alert newsletter engaged in MDM projects), the perennial top four business drivers for MDM initiatives are summarized as:
(1)    compliance and regulatory reporting;
(2)    economies of scale for mergers and acquisitions (M&A);
(3)    synergies for cross-sell and up-sell;
(4)    legacy system integration and augmentation; and
Note that this list represents business drivers, not technical initiatives. Ideally, the business analyst “owns” the data and is responsible for the initial definition of what the master data looks like (whether this is from a custom application or a packaged solution). In addition, they are responsible for the processes (not actual data entry) of inputting the data into the source systems. IT acts as “data stewards” – coordinators between various business groups. IT’s role should be project managers that phase in updates to the primary MDM. These data stewards must be equally savvy in data modeling as well as business processes. IT must also be the technical gurus to glue applications and databases together. This also involves data quality processes, such as standardization, cleansing, validation, enrichment and matching.
Traditional MDM solutions have been implemented on premise, primarily as data hubs to various applications spokes such as Human Resources, PLM, ERP, and CRM applications. With the huge uptick of software as a service (SaaS) CRM providers such as salesforce.com, this requires MDM solutions to integrate data from the cloud.
While an on-premise model works well when most of the data is updated within the “four walls” of the enterprise, a hybrid cloud + on premise model may be better suited to a B2C environment when massive customer updates happen on a seasonal basis. In this case, a hybrid model will allow for extra cloud resources to be tapped in order to increase performance. In addition, with a hybrid model, sensitive data that may be legally prohibited from residing in the cloud can be kept on premise.
Should MDM be completely implemented in the cloud?
In this case, the master data model engine will reside in the cloud and will act as a hub between multiple SaaS applications and potentially on premise applications. A common scenario might be managing customer data between Salesforce CRM, Order fulfillment with UPS services, and on-premise ERP Receivables. Or replace the on-premise ERP solution with a cloud-based ERP such as NetSuite. In these cases, having MDM in the cloud might be the right approach. A cloud-based solution also makes sense for piloting a longer term MDM project. So look for a vendor that provides both on-premise and cloud-based MDM solutions for maximum deployment flexibility.
The Hybrid IT organization continues to evolve with new responsibilities. Cloud-based solutions tend to free up the IT staff from the more routine data center operations to get more involved with business activities such as Master Data Management. IT will play an important role in managing MDM solutions. Although, they don’t “own” the data, the technical requirements for implementing a solution remain in the IT domain. And acting as a data steward to capture the business requirements of what data needs to be managed and formulate the detailed rules and processes will become a key role. IT will also need to decide between on-premise and cloud-based architectures for the enterprise.
—-
Mercury Consulting is a trusted technology advisor with deep expertise in cloud applications. We offer strategic guidance to senior executives to select the right cloud solution and services assistance to help enterprises accelerate their adoption of cloud solutions.
Mike Canniff is a faculty member of Management Information Systems at the University of Pacific – Eberhardt School of Business. He has worked in the Information Technology field for over 20 years beginning with IBM as a software engineer and as Vice President, Development for Acuitrek Software. Mike has specialized his career research in the areas of Enterprise Application Integration and Electronic Commerce systems. He has published several papers on Electronic Commerce and Business Process Management best practices.

Electronic Trading Systems Moving to the Cloud

2012-08-10T13:19:00.002+05:30

More and more business applications are moving from the desktop to the cloud, and electronic trading applications are no different.
Over the last five or ten years, application vendors have established several advantages of running major applications, even mission-critical applications like salesforce.com, over the cloud.
These advantages include:

Easier and smoother upgrades, which provides much better adaptability and agility in the face of changing market and business conditions, plus a better user experience,
Better scalability, with newer technology advances, and
Better portability across a wide array of device types, including smartphones and tablets (especially in the last 2-3 years).

Recent improvements in Web technology, such as HTML5 WebSockets, are helping to speed this transition along by providing several throughput and latency advantages over earlier iterations of Web technology, and even over native Windows applications. Now, application architects can freely choose the technology that provides a better path for growth, agility, and scalability, which is often a Cloud-based solution.
As I write this, a few of our customers who provide electronic trading solutions to their clients are making the strategic move to develop a next generation application based in the Cloud. The main driver for one customer was to be able to take on more clients more quickly and therefore grow the business faster by increasing marginal revenue and profitability. They found that the list of challenges with a thick desktop client to be just too big for growing the business as quickly as they wanted to — or needed to.
Messaging middleware, especially peer-to-peer solutions such as Informatica Ultra Messaging, can be a very important piece of a Cloud-based application. The peer-to-peer “nothing in the middle” model provides applications not just ultra-high performance (whether for high throughput or low latency), but also near-linear scalability, true 24×7 reliability and availability, and business and IT agility. These qualities tie directly to the advantages listed above.
Cloud-based applications, of course, must also contend with the Internet and all that comes with that: support for various browsers and platforms (and versions of each), scalability and bandwidth issues, and mobile devices like smartphones and tablets. New web technologies like HTML5 WebSockets from Kaazing are best positioned to take care of the path from server to the smartphone or tablet, and with JMS connectivity to Ultra Messaging on the back end, can provide a Cloud-based application with a lean, scalable and agile infrastructure, usually with less hardware.

SAP HANA Architecture

2012-05-29T21:55:00.005+05:30

In this article we will discuss about the architecture overview of the In-Memory Computing Engine of SAP HANA. The SAP HANA database is developed in C++ and runs on SUSE Linux Enterpise Server. SAP HANA database consists of multiple servers and the most important component is the Index Server. SAP HANA database consists of Index Server, Name Server, Statistics Server, Preprocessor Server and XS Engine.

Index Server contains the actual data and the engines for processing the data. It also coordinates and uses all the other servers.
Name Server holds information about the SAP HANA databse topology. This is used in a distributed system with instances of HANA database on different hosts. The name server knows where the components are running and which data is located on which server.
Statistics Server collects information about Status, Performance and Resource Consumption from all the other server components. From the SAP HANA Studio we can access the Statistics Server to get status of various alert monitors.
Preprocessor Server is used for Analysing Text Data and extracting the information on which the text search capabilities are based .
XS Engine is an optional component. Using XS Engine clients can connect to SAP HANA database to fetch data via HTTP.

Now let us check the architecture components of SAP HANA Index Server.

SAP HANA Index Server Architecture:

Connection and Session Management component is responsible for creating and managing sessions and connections for the database clients. Once a session is established, clients can communicate with the SAP HANA database using SQL statements. For each session a set of parameters are maintained like, auto-commit, current transaction isolation level etc. Users are Authenticated either by the SAP HANA database itself (login with user and password) or authentication can be delegated to an external authentication providers such as an LDAP directory.
The client requests are analyzed and executed by the set of components summarized as Request Processing And Execution Control. The Request Parser analyses the client request and dispatches it to the responsible component. The Execution Layer acts as the controller that invokes the different engines and routes intermediate results to the next execution step. For example, Transaction Control statements are forwarded to the Transaction Manager. Data Definition statements are dispatched to the Metadata Manager and Object invocations are forwarded to Object Store. Data Manipulation statements are forwarded to the Optimizer which creates an Optimized Execution Plan that is subsequently forwarded to the execution layer.
- The SQL Parser checks the syntax and semantics of the client SQL statements and generates the Logical Execution Plan. Standard SQL statements are processed directly by DB engine.
- The SAP HANA database has its own scripting language named SQLScript that is designed to enable optimizations and parallelization. SQLScript is a collection of extensions to SQL. SQLScript is based on side effect free functions that operate on tables using SQL queries for set processing. The motivation for SQLScript is to offload data-intensive application logic into the database.
- Multidimensional Expressions (MDX) is a language for querying and manipulating the multidimensional data stored in OLAP cubes.
- The SAP HANA database also contains a component called the Planning Engine that allows financial planning applications to execute basic planning operations in the database layer. One such basic operation is to create a new version of a dataset as a copy of an existing one while applying filters and transformations. For example: Planning data for a new year is created as a copy of the data from the previous year. This requires filtering by year and updating the time dimension. Another example for a planning operation is the disaggregation operation that distributes target values from higher to lower aggregation levels based on a distribution function.
- The SAP HANA database also has built-in support for domain-specific models (such as for financial planning) and it offers scripting capabilities that allow application-specific calculations to run inside the database.
The SAP HANA database features such as SQLScript and Planning operations are implemented using a common infrastructure called the Calc engine. The SQLScript, MDX, Planning Model and Domain-Specific models are converted into Calculation Models. The Calc Engine creates Logical Execution Plan for Calculation Models. The Calculation Engine will break up a model, for example some SQL Script, into operations that can be processed in parallel. The engine also executes the user defined functions.
In HANA database, each SQL statement is processed in the context of a transaction. New sessions are implicitly assigned to a new transaction. The Transaction Manager coordinates database transactions, controls transactional isolation and keeps track of running and closed transactions. When a transaction is committed or rolled back, the transaction manager informs the involved engines about this event so they can execute necessary actions. The transaction manager also cooperates with the persistence layer to achieve atomic and durable transactions.
Metadata can be accessed via the Metadata Manager. The SAP HANA database metadata comprises of a variety of objects, such as definitions of relational tables, columns, views, and indexes, definitions of SQLScript functions and object store metadata. Metadata of all these types is stored in one common catalog for all SAP HANA database stores (in-memory row store, in-memory column store, object store, disk-based). Metadata is stored in tables in row store. The SAP HANA database features such as transaction support, multi-version concurrency control, are also used for metadata management. In distributed database systems central metadata is shared across servers. How metadata is actually stored and shared is hidden from the components that use the metadata manager.
The Authorization Manager is invoked by other SAP HANA database components to check whether the user has the required privileges to execute the requested operations. SAP HANA allows granting of privileges to users or roles. A privilege grants the right to perform a specified operation (such as create, update, select, execute, and so on) on a specified object (for example a table, view, SQLScript function, and so on). The SAP HANA database supports Analytic Privileges that represent filters or hierarchy drilldown limitations for analytic queries. Analytic privileges grant access to values with a certain combination of dimension attributes. This is used to restrict access to a cube with some values of the dimensional attributes.
Database Optimizer gets the Logical Execution Plan from the SQL Parser or the Calc Engine as input and generates the optimised Physical Execution Plan based on the database Statistics. The database optimizer which will determine the best plan for accessing row or column stores.
Database Executor basically executes the Physical Execution Plan to access the row and column stores and also process all the intermediate results.
The Row Store is the SAP HANA database row-based in-memory relational data engine. Optimized for high performance of write operation, Interfaced from calculation / execution layer. Optimised Write and Read operation is possible due to Storage separation i.e. Transactional Version Memory & Persisted Segment.
- Transactional Version Memory contains temporary versions i.e. Recent versions of changed records. This is required for Multi-Version Concurrency Control (MVCC). Write Operations mainly go into Transactional Version Memory. INSERT statement also writes to the Persisted Segment.
- Persisted Segment contains data that may be seen by any ongoing active transactions. Data that has been committed before any active transaction was started.
- Version Memory Consoliation moves the recent version of changed records from Transaction Version Memory to Persisted Segment based on Commit ID. It also clears outdated record versions from Transactional Version Memory. It can be considered as garbage collector for MVCC.
- Segments contain the actual data (content of row-store tables) in pages. Row store tables are linked list of memory pages. Pages are grouped in segments. Typical Page size is 16 KB.
- Page Manager is responsible for Memory allocation. It also keeps track of free/used pages.
The Column Store is the SAP HANA database column-based in-memory relational data engine. Parts of it originate from TREX (Text Retrieval and Extraction) i.e SAP NetWeaver Search and Classification. For the SAP HANA database this proven technology was further developed into a full relational column-based data store. Efficient data compression and optimized for high performance of read operation, Interfaced from calculation / execution layer. Optimised Read and Write operation is possible due to Storage separation i.e. Main & Delta.
- Main Storage contains the compressed data in memory for fast read.
- Delta Storage is meant for fast write operation. The update is performed by inserting a new entry into the delta storage.
- Delta Merge is an asynchronous process to move changes in delta storage into the compressed and read optimized main storage. Even during the merge operation the columnar table will be still available for read and write operations. To fulfil this requirement, a second delta and main storage are used internally.
- During Read Operation data is always read from both main & delta storages and result set is merged. Engine uses multi version concurrency control (MVCC) to ensure consistent read operations.
- As row tables and columnar tables can be combined in one SQL statement, the corresponding engines must be able to consume intermediate results created by each other. A main difference between the two engines is the way they process data: Row store operators process data in a row-at-a-time fashion using iterators. Column store operations require that the entire column is available in contiguous memory locations. To exchange intermediate results, row store can provide results to column store materialized as complete rows in memory while column store can expose results using the iterator interface needed by row store.
The Persistence Layer is responsible for durability and atomicity of transactions. It ensures that the database is restored to the most recent committed state after a restart and that transactions are either completely executed or completely undone. To achieve this goal in an efficient way the per-sistence layer uses a combination of write-ahead logs, shadow paging and savepoints. The persistence layer offers interfaces for writing and reading data. It also contains SAP HANA 's logger that manages the transaction log. Log entries can be written implicitly by the persistence layer when data is written via the persistence interface or explicitly by using a log interface.

Distributed System and High Availability

The SAP HANA Appliance software supports High Availability. SAP HANA scales systems beyond one server and can remove the possibility of single point of failure. So a typical Distributed Scale out Cluster Landscape will have many server instances in a cluster. Therefore Large tables can also be distributed across multiple servers. Again Queries can also be executed across servers. SAP HANA Distributed System also ensures transaction safety. Features

N Active Servers or Worker hosts in the cluster.
M Standby Server(s) in the cluster.
Shared file system for all Servers. Serveral instances of SAP HANA share the same metadata.
Each Server hosts an Index Server & Name Server.
Only one Active Server hosts the Statistics Server.
During startup one server gets elected as Active Master.
The Active Master assigns a volume to each starting Index Server or no volume in case of cold Standby Servers.
Upto 3 Master Name Servers can be defined or configured.
Maximum of 16 nodes is supported in High Availability configurations.

Name Server Configured Role	Name Server Actual Role	Index Server Configured Role	Index Server Actual Role
Master 1	Master	Worker	Master
Master 2	Slave	Worker	Slave
Master 3	Slave	Worker	Slave
Slave	Slave	Standby	Standby

Failover

High Availability enables the failover of a node within one distributed SAP HANA appliance. Failover uses a cold Standby node and gets triggered automatically. So when a Active Server X fails, Standby Server N+1 reads indexes from the shared storage and connects to logical connection of failed server X.
If the SAP HANA system detects a failover situation, the work of the services on the failed server is reassigned to the services running on the standby host. The failed volume and all the included tables are reassigned and loaded into memory in accordance with the failover strategy defined for the system. This reassignment can be performed without moving any data, because all the persistency of the servers is stored on a shared disk. Data and logs are stored on shared storage, where every server has access to the same disks.
The Master Name Server detects an Index Server failure and executes the failover. During the failover the Master Name Server assigns the volume of the failed Index Server to the cold Standby Server. In case of a Master Name Server failure, another of the remaining Name Servers will become Active Master.
Before a failover is performed, the system waits for a few seconds to determine whether the service can be restarted. Standby node can take over the role of a failing master or failing slave node

SAP HANA - An Introduction for the beginners

2012-05-29T21:55:00.001+05:30

SAP HANA: High-Performance Analytic Appliance (HANA)is an In-Memory Database from SAP to store data and analyze large volumes of non aggregated transactional data in Real-time with unprecedented performance ideal for decision support & predictive analysis.

The In-Memory Computing Engine is a next generation innovation that uses cache-conscious data-structures and algorithms leveraging hardware innovation as well as SAP software technology innovations. It is ideal for Real-time OLTP and OLAP in one appliance i.e. E-2-E solution from Transactional to high performance Analytics. SAP HANA can also be used as a secondary database to accelerate analytics on existing applications.

Hardware Innovations - Leading to HANA

In real world we have so many variety of data sources, e.g. Unstructured Data, Operational Data Stores, Data Marts, Data Warehouses, Online Analytical Stores, etc. To do analytics or information mining from this Big Data at real time we come across the hurdles like Latency, High Cost and Complexity.
Disk I/O was the Performance bottleneck in the past, whereas in memory computing was always much faster than that. Earlier, however, the cost of in-memory computing was prohibitive for any large scale implementation. Now with Multi-Core CPU and high capacity of RAM, we can host the entire database in memory. So now CPU is waiting for data to be loaded from main memory into CPU cache - and that's what is the Performance bottleneck today.
This is a total paradigm shift; Tape is Dead, Disk is Tape, Main Memory is Disk & CPU Cache is Main Memory. HANA is optimized to exploit the parallel processing capabilities of modern multi-core/CPU architectures. With this architecture, SAP applications can benefit from current hardware technologies.

Memory Overview - Where we stand

Let us have a quick look on Multi-Core CPU Caches, Main Memory i.e. RAM & traditional Hard Disk with respect to response time.

L1 cache - Primary & within core. SRAM - Fastest. L1 cache | ~ 1ns | 64k
L2 cache – Intermediate & within core. DRAM - Slower. L2 cache | ~ 5ns | 256k
L3 Cache – Shared across all cores. DRAM - Slowest. L3 cache | ~ 20ns | 8M
Main Memory | ~ 100ns | TBs
Hard Disk | > 1.000.000ns | TBs

HANA Hardware Requirement

HANA can be installed on many certified SAP hardware partners: Hewlett Packard, IBM, Fujitsu Computers, CISCO systems, DELL.

Currently SUSE Linux Enterprise Server x86-64 (SLES) 11 SP1 is the Operating System supported by SAP HANA.

A typical example of CPU and RAM can be 4 Intel E7-4870 / 40 cores and 512 GB RAM. SAP recommends a dedicated server network communication of 10 GBit/s between the SAP HANA landscape and the source system for efficient data replication.

HANA Database Features

Important database features of HANA include OLTP & OLAP capabilities, Extreme Performance, In-Memory , Massively Parallel Processing, Hybrid Database, Column Store, Row Store, Complex Event Processing, Calculation Engine, Compression, Virtual Views, Partitioning and No aggregates. HANA In-Memory Architecture includes the In-Memory Computing Engine and In-Memory Computing Studio for modeling and administration. All the properties need a detailed explanation followed by the SAP HANA Architecture.

Basic Concepts behind HANA Database

Extreme Hardware Innovations:

Main memory is no-longer a limited resource, modern servers can have 2TB of system memory and this allows complete databases to be held in RAM. Currently processors have up to 64 cores, and 128 cores will soon be available. With the increasing number of cores, CPUs are able to process increased data per time interval. This shifts the performance bottleneck from disk I/O to the data transfer between main memory and CPU cache.

In-Memory Database:

HANA fully leverages the hardware innovations like Multi-Core CPU, High capacity RAM availability. The basic concept is to cache the entire database into fast accessible Main Memory close to CPU for faster execution and to avoid disk I/O. Disk storage is still required for permanent persistency since Main Memory is volatile. SAP HANA, holds the bulk of its data in memory for maximum performance, but still uses persistent storage to provide a fallback in case of failure. Data and log are automatically saved to disk at regular save points, the log is also saved to disk after each COMMIT of a database transaction. Disk write operations happens asynchronously and as a background task. Generally on system start-up HANA loads the tables into memory.

Massively Parallel Processing:

With availability of Multi-Core CPUs, higher CPU execution speeds can be achieved. Multiple CPUs call for new parallel algorithms to be used in databases in order to fully utilize the computing resources available. HANA Column-based storage makes it easy to execute operations in parallel using multiple processor cores. In a column store data is already vertically partitioned. This means that operations on different columns can easily be processed in parallel. If multiple columns need to be searched or aggregated, each of these operations can be assigned to a different processor core. In addition operations on one column can be parallelized by partitioning the column into multiple sections that can be processed by different processor cores. With the SAP HANA database, queries can be executed rapidly and in parallel.

Hybrid Data Store:

Common databases store tabular data row-wise, i.e. all data for a record are stored adjacent to each other in memory. Row store tables are linked list of memory pages. Conceptually, a database table is a two-dimensional data structure with cells organized in rows and columns. Computer memory however is organized as a linear structure. To store a table in linear memory, two options exist:

A row-oriented storage stores a table as a sequence of records, each of which contain the fields of one row.
A column-oriented storage stores all the values of a column in contiguous memory locations.

Use of column store will help to prevent table scan of unnecessary columns while performing searching and aggregation operations on single column values stored in contiguous memory locations. Such an oper-ation has high spatial locality and can efficiently be executed in the CPU cache. With row-oriented storage, the same operation would be much slower because data of the same column is distributed across memory and the CPU is slowed down by cache misses. Column store is optimized for high performance of read operation and efficient data compression. This combination of both classical and innovative technologies of data storage and access allows the developer to choose the best technology for their application and, where necessary, use both in parallel.

OLTP and OLAP Database:

HANA is a hybrid database, having both read optimised column store ideally suited for OLAP and write optimised row store best for OLTP systems relational engines. Both the stores are In-Memory. Using column stores in OLTP applications requires a balanced approach to insertion and indexing of column data to minimize cache misses. The SAP HANA database allows the developer to specify whether a table is to be stored column-wise or row-wise. It is also possible to alter an existing table from columnar to row-based and vice versa.

Higher Data Compression:

The goal of keeping all relevant data in main memory can be achieved with less cost if data compression is used. Columnar data storage allows highly efficient compression. If a column is sorted, there will normally be several contiguous values placed adjacent to each other in memory. In this case compression methods, such as run-length encoding, cluster coding or dictionary coding can be used. In column stores a compression factor of 10 can typically be achieved compared to traditional row-oriented storage systems.

Building the Next Generation ETL data loading Framework

2012-05-29T21:54:00.000+05:30

Do you wish for an ETL framework that is highly customizable, light-weight and suits perfectly with all of your data loading needs? We too! Let's build one together...

What is an ETL framework?

ETL Or "Extraction, Transformation and Loading" is the prevalent technological paradigm for data integration. While ETL in itself is not a tool, ETL processes can be implemented though varied tools and programming methods. This includes, but not limited to, tools like Informatica PowerCentre, DataStage, BusinessObjects Data Services (BODS), SQL Server Integration Services (SSIS), AbInitio etc. and programming methods like PL/SQL (Oracle), T-SQL (Microsoft), UNIX shell scripting etc. Most of these tools and programming methodologies use a generic setup that controls, monitors, executes and Logs the data flow through out the ETL process. This generic 'setup' is often referred as 'ETL framework'
As an example of an ETL framework, let's consider this. "Harry" needs to load 2 tables everyday from one source system to some other target system. For this purpose, Harry has created 2 SQL jobs, each of which reads data from source through some "SELECT" statements and write the data in the target database using some "INSERT" statements. But in order to run these jobs, Harry needs couple of more information - e.g.

when is a good time to execute these jobs? Can he schedule these jobs to run automatically everyday?
Where is the source system located? (Connection information)
What will happen if one of the jobs fail while loading the data? Will Harry get an alert message? Can he simply rerun the jobs after fixing the issue of the failure?
How will Harry know if at all any data is retrieved or loaded to the target?

Turns out that, Harry needs something more. He needs some kind of setup that will govern the job execution regularly. This includes - scheduling the jobs, executing the jobs, logging any failure/error information (and also alerting Harry about such failures), maintaining the connection information and even ensuring that Harry does not end up loading the duplicate data.
Such a setup is called "ETL Framework". And we are trying to build the perfect one here.

Critical Features of an ETL framework

In a very broad sense, here are a few of the features that we feel critical in any ETL framework

Support for Change Data Capture Or Delta Loading Or Incremental Loading
Metadata logging
Handling of multiple source formats
Restartability support
Notification support
Highly configurable / customizable

Good-to-have features of ETL Framework

These are some good-to-have features for the framework

Inbuilt data reconciliation
Customizable log format
Dry-load enabling
Multiple notification formats

Request for Proposal for the next-gen ETL framework

Based on the feature sets above, we are trying to build a generic framework that we would make available here for free for everyone's use.

However the list of features above are not complete. We are requesting our readership to send us RFP for the proposed ETL framework that would resolve the incapability / issues in their existing frameworks.

Incremental Loading for Dimension Table

2012-05-29T21:53:00.003+05:30

In our previous article we have discussed the concept of incremental loading in general. In this article we will see how to perform incremental loading for dimension tables.

Should we do incremental loading for dimensions?

In a dimensional model, we may perform incremental loading for dimension tables also. One may argue that this wont be necessary as data volume in dimension tables are not as high as the data volumes in the fact tables, hence we can simply do a full load every time.
I personally do not agree to this argument. This is because during the last few years I have seen tremendous growth in the data in dimension tables and things can get quite heavy especially if we are trying to load SCD type 2 dimensions. Anyway, without much ado, let's delve deep.

Standard Method of Loading

Like before, for our purpose we will assume we have the below customer table in our source system from where we need to perform the data loading

CustomerID  CustomerName  Type         LastUpdatedDate
1           John          Individual   22-Mar-2012
2           Ryan          Individual   22-Mar-2012
3           Bakers'       Corporate    23-Mar-2012

As discussed in the previous article, a typical SQL query to extract data incrementally from this source system will be like this:

SELECT t.* 
FROM Customer t
WHERE t.lastUpdatedDate > (select nvl(
                                 max(b.loaded_until), 
                                 to_date('01-01-1900', 'MM-DD-YYYY')
                                )
                      from batch b
                      where b.status = 'Success');

Here "batch" is a separate table which stores the date until which we have successfully extracted the data.

Batch_ID  Loaded_Until  Status
1         22-Mar-2012   Success
2         23-Mar-2012   Success

Which one to use: "Entry Date" / "Load Date" or "Last Update Date"?

In an incremental load methodology, we should extract the record when it is first created and after that whenever the record is updated. Therefore, we should always look for "last update date" column for extracting records. This is because, "entry date" or "load date" columns in the source systems are not enough to determine if the record is updated in the later point in time.

Often source systems maintain 2 different columns as load_date and last_update_date. When extracting data based on "last update date", ensure that source systems always populate "last updated date" field with "load date" when the record is first created.

What are the benefits of incremental loading of dimension tables?

Once we extract records incrementally based on their last update date, we can compare each record with the target based on their natural keys and determine if the record is a new record or updated record.
However, if we do not extract incrementally (and every time extract all the records from source), then the number of records to compare against target will be much higher resulting into performance degradation. If we are doing incremental loading, records that do not have any change will not come - only new or updatable records will come. But if we are doing full load, everything will come irrespective of any change.

ETL TOOLS

Informatica Tutorial

Informatica Upgrade Process:

Design Tip #155 Going Agile? Start with the Bus Matrix

White Paper: Evolving Role of the Enterprise Data Warehouse in the Era of Big Data Analytics

Writing a Proper Data Warehousing Request for Proposal!

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

Oracle GoldenGate Architecture

GoldenGate for SQL Server Installation on Windows XP

GoldenGate for Oracle Installation on Oracle Linux 5

Preparing the Source Database

Preparing the Target Database

Running the Initial Extract and Loading

Live Data Capture Configuration

Starting and Testing Online Transaction Replication

Conclusion

Legions of remote workers create opportunities and concerns for businesses.

Change brings challenges

Stay connected

Technical support

Management strategies

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

It’s now or never

Case closed

Data Cleansing for Data Warehousing

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

Make room for data

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

Q: What approaches did you consider?

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

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

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?

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

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?

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

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

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

Q: Has compression added work to maintenance efforts?

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

Cube Development for Beginners

From Questions to Answers

Implementing a Data Warehouse with Oracle Warehouse Builder

Defining a Target Schema

Consolidating data from disparate data sources

Aggregating Data Across Dimensions with Cubes

Transforming the Source Data for the Cube Loading

Conclusion

Email task, Session and Workflow notification : Informatica

Informatica Unable to fetch log

Informatica Workflow Successful : No Data in target !

ORA-01403: no data found

Introducing Informatica Cloud 9 – The Defining Capability For Cloud Computing

How Big Data Changes Data Integration

How Integration Platform-as-a-Service Impacts Cloud Adoption

Delivering IT Value with Master Data Management and the Cloud

Electronic Trading Systems Moving to the Cloud

SAP HANA Architecture

SAP HANA Index Server Architecture:

Distributed System and High Availability

Failover

SAP HANA - An Introduction for the beginners

Hardware Innovations - Leading to HANA

Memory Overview - Where we stand

HANA Hardware Requirement

HANA Database Features

Basic Concepts behind HANA Database

Extreme Hardware Innovations:

In-Memory Database:

Massively Parallel Processing:

Hybrid Data Store:

OLTP and OLAP Database:

Higher Data Compression:

Building the Next Generation ETL data loading Framework

What is an ETL framework?

Critical Features of an ETL framework

Good-to-have features of ETL Framework

Request for Proposal for the next-gen ETL framework

Based on the feature sets above, we are trying to build a generic framework that we would make available here for free for everyone's use.

Incremental Loading for Dimension Table

Should we do incremental loading for dimensions?

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