Oracle Data Integrator Tutorial- ODI for beginners

I am going to be writing up a few posts and tutorials on the basics of the architecture (see previous post) and a few beginner topics to get the masses in a mode where they are not intimidated by this tool. The previous post described various aspects to ODI, and the architecture. We saw various GUIs that ODI allows us to use. This post (and possibly another one or two) will look at the DESIGNER GUI in more detail.

One of the first things I will say is that the tool can feel a bit foreign if you come from an Informatica/OWB type background like myself. The emphasis is on building smaller and reusable pieces of code, if you will. Other tools that tend to be more ETL based (Informatica) and also more GUI and non-declarative based (OWB) are not necessarily the easiest tools to break things into small pieces. Yes, there are many that say breaking mappings into smaller pieces is a best practice, but the tool doesn't necessarily thrive on your ability to follow that practice. They don't really revolve around that practice. There are even mapplets and pluggable mappings, but these pale in comparison to the design methodologies that ODI offers. ODI thrives on code re-use.

For example, the diagram below shows a typical ETL/ELT from something that may come out of OWB or Informatica (a bit bigger, defined, and manual).
The following is the type of development that ODI supports and emphasizes for code reuse:


If you are a beginner to ODI and used other ETL/ELT tools and yoou are beginning to build out mappings you'll notice many of the mappings you'll build will be more discrete, if you will. There are also many steps that are done for you. You can build mappings quite fast in ODI actually. However, do not be too worried that that tool does quite a bit for you. Like Oracle's JDeveloper tool, you can get into most of the gory details if you'd like to fine-tune certain aspects, even if the tool does much of the work for you.

You will do the bulk of your work doing mappings in the following interface:


A brief description of what we see above in the Diagram above. First, notice that the Diagram TAB is selected on the top. This opens the diagramming tool where 'mappings' are built. In the middle are the data sources. These sources are physically dragged and dropped from the far left pane of the GUI to this 'sources' section and are then joined, filtered, and so forth. The right side of the GUI is labeled as the TARGET datastore. This is your target. For a beginner, this can be a bit confusing if you're used to OWB because here, the sources and target is separated by differnet parts of the GUI. You would drag your TARGET table to this target datastore pane in the GUI and not into the same area as the sources. So, a bit different in that respect.

Below is a zoom in on only the mapping portions of the tool. Again, on the left are the source portions of the mapping diagram, and on the left is the target datastore. You might not use the terminology 'datastore' too often, but in this tool you should get used to seeing that term a lot!
Here is another screenshot of the far left portion of the main GUI, which I tend to call the 'explorer' since it is built out like many software tool's explorer-like interfaces.
Again, one final screenshot to tie it all together what we have seen so far:

In the next posting, I will go into a bit more detail on the 'explorer' and the many tabs and options available within. As you might have imagined, these 'mappings' have been created and opened from the explorer on the left, and they are technically referred to as 'interfaces'. I'll talk a bit more on the concept of interfaces, and go into a bit more detail on working with your interfaces (A.K.A. mappings to most of us).

ETL Misconceptions and Mistakes Made While Working With Large Databases - 1TB or Greater

I'd first like to give some due credit to a fellow blogger that has pushed out so much information on data warehouse topics, it is staggering. He is Dan Lindstedt, and this next piece on my blog is about some common etl mistakes on large systems, with my commentary. His work appears frequently on the Beyenetwork blogs.

Please note that some of these issues listed below might not necessarily make a difference if you are dealing with a smaller system with less data / rows. If you have never worked on a larger size database, some of these concepts might even seem unfamiliar or odd.

Top ETL mistakes


1) Incorporating Inserts, Updates, and Deletes in to the same data flow / same process.

This is a great point, if for nothing less than splitting the individual tasks into discrete pieces. For a target table load, try an insert job, an update job, and other jobs like deletion or massive bulk loading jobs. These will all vary based on time and loads. Inserts in many cases being simple and easy, and updates and especially deletes sometimes becoming more taxing.

2) Sourcing multiple systems at the same time, depending on heterogeneous systems for data.

This can be cause for great concern if you try this, unless the data volume is smaller, again, we are referring to larger loads and larger systems though.

3) Targeting more than 1 or 2 target tables

You might consider 2 tables at once if it has a relationship, hierarchical, parent-child, or otherwise, but this should not happen often! That should be an exception, most large volume ETL jobs should be 1 target table.

4) moving rows that are too wide through a single process

Any ways to get around this issue or mistake? Comments?

5) loading very large data sets to targets WITH INDEXES ON

Many tools allow you to turn indexes off and then recompute and turn on again after a job- datastage, owb, etc. You can code it custom if you need to. Again, you don't need indexes and keys if you have other ways to check your referential integrity.

6) not running a cost-based optimizer in the database

Big mistake here. You should always consider a routine after all loads are finished to analyze, or gather stats or other such details for your database just so that your cost-based optimizer, or other tool (in a non-Oracle database) can gather all the necessary information about new structures, indexes, and so forth.

7) not keeping statistics up to date in the database

Again, this is similar to 6, almost exactly. You'd want current statistics so the cost based optimizer takes advantage of the current structure and used the right joins and indexes, etc.

8) not producing the correct indexes on the sources / lookups that need to be accessed

You should know well enough where the majority of your lookups and/or joins are going to happen....indexes....use them! But, again, see the above mistake about keeping them during a load.

9) not purchasing enough RAM for the ETL server to house the RAM caches in memory.

Not applicable to OWB necessarily, more for things like Informatica and DataStage.

10) running on a 32 bit environment which causes significant OS swapping to occur
11) running on a 32 bit environment which causes significant OS swapping to occur
12) running on a 32 bit environment which causes significant OS swapping to occur

The original author had a major issue with #10, so as to repeat it for #11 and for #12!!!

The original author points out the following concerning #10-#12:

"Let me explain this one. Any single process started within a 32 bit environment is limited (immediately) to 2GB of addressable RAM, now - take away the RAM needed for the threads or the executable engine code... usually around 300M or more, leaving us with 1.5GB to 1.7GB of RAM to work with. Then, take away any "caching" objects that might use this RAM, and multiply that "drop" in RAM by the number of caching objects that you have....

Now, you are left with possibly 200MB, maybe 100MB of RAM left to allocate for "data reading/data writing"... and it drops from there.

Let me explain this other piece as well: Windows 32 bit OS MUST RUN CODE UNDER THE 640k BOUNDARY!!! So there's additional Limitations there, Windows 32 Bit PAGEFILE.SYS (swap/temp) is SINGLE THREADED, AND BLOCKS I/O OPERATIONS when swapping pieces to disk. Oh yes, one more: Windows 32 Bit - RAM allocation will ONLY give the application 1/2 of the requested RAM, and AUTOMATICALLY put 1/2 in the pagefile.sys swap area.

By the way, here's a tid-bit for you that you have to try to believe:
If you are running Windows 32 bit, SQLServer (either version)....
1. Change your ETL "block size" to 8k to match the Database (4k I think in SQLServer2000)
2. Change your row size to fit as many rows as possible into an 8k block, the more rows per block the faster the performance.
3. Change your commit point to 8,500 rows (this is the sweet spot)
4. Check in on NETWORK PACKET SIZES and increasing those between the ETL engine and the Database, increase them from 2k/4k to 8k - again to match the disk.
5. USE ODBC/OLE DB connectivity; do NOT use NATIVE libraries (except in SQLServer2005 direct CT-LIB). If your tool uses DB-LIB as "native" connectivity, it will be slow, slow slow - DB-LIB is 12 year old technology, and is NOT multi-threaded, nor parallel, where CT-LIB is.

Windows 64 Bit does NOT have these limitations, but requires 30% more hardware to run "cool" and efficiently, but it is FAST when installed and configured correctly on the right hardware.

Linux, and Unix do not have these limitations either.

So, architecture is everything."

13) Trying to do "too much" inside of a single data flow, increasing complexity and dropping performance

This does make the job harder to debug and sometimes you get those random out of resource errors...Beware. Split work where appropriate. If a mapping or process looks too complicated, it probably is.

14) believing that "I need to process all the data in one pass because it's the fastest way to do it."

"breaking-up" a single flow into multiple flows , and changing the width of the data sets and possibly the parallelism along the way would help the performance.

15) Letting the database "bounce" errors back to the ETL tool, dropping flow rates and throughput rates by factors of 4x to 10x.

comments?

16) "THINKING" in a transactional mode, rather than a batch mode, and processing each row, one row at a time (like they would code a cursor in a database language).

A bit hard to avoid in a row-by-row ETL tool! The parallel architecture and caching and memory sharing and a bunch of other things make it fast.

17) LOOPING inside an ETL process, because they think it's necessary (transactional processing again).

Careful here no matter which tool you are using. Looping, as it goes WAY back in simple logical programming can be a blessing or a disaster. If you need to loop, there's probably another way to do what you're attempting, in a more efficient way. This does not necessarily mean using a LOOP in an OWB process flow, but trying to loop somehow in the ETL itself, called a stored function or procedure, etc.

Date Dimensions anybody?

Here is some sample code for creating a date dimension, It's been out there before, My colleague Dave posted this on his blog at one point. I'd like to take a moment and maybe look at how Oracle implements their date dim with the OWB date dimension wizard....hmmmm!

SELECT TO_CHAR (dt, 'YYYYMMDD') date_id,
      dt date_value,
      TO_CHAR (dt, 'MM/DD/YYYY') date_formatted_name,
      TO_CHAR (dt, 'Mon DD, YYYY') date_name,
      TO_CHAR (dt, 'YYYY') year_num,
      TRUNC (dt, 'YYYY') year_start_date,
      ADD_MONTHS (TRUNC (dt, 'YYYY'), 12) - 1 year_end_date,
      TO_NUMBER (TO_CHAR (dt, 'Q')) quarter_num,
      'Q' || TO_CHAR (dt, 'Q') quarter_name,
      TRUNC (dt, 'Q') quarter_start_date,
      ADD_MONTHS (TRUNC (dt, 'Q'), 3) - 1 quarter_end_date,
      TO_CHAR (dt, 'YYYY') || '-' || 'Q' || TO_CHAR (dt, 'Q') year_quarter_name,
      TO_CHAR (dt, 'MM') month_num,
      TO_CHAR (dt, 'Mon') month_short_name,
      TO_CHAR (dt, 'Month') month_long_name,
      TRUNC (dt, 'MM') month_start_date,
      LAST_DAY (dt) month_end_date,
      TO_CHAR (dt, 'YYYY') || '-' || TO_CHAR (dt, 'Mon') year_month_name_1,
      TO_CHAR (dt, 'YYYY') || '-' || TO_CHAR (dt, 'MM') year_month_num_1,
      TO_CHAR (dt, 'Mon') || ' ' || TO_CHAR (dt, 'YYYY') year_month_name_2,
      TO_CHAR (dt, 'YYYY') || TO_CHAR (dt, 'MM') year_month_num_2,
      TO_CHAR (dt, 'WW') week_of_year_num,
      TO_CHAR (dt, 'W') week_of_month_num,
      TRUNC (dt, 'W') week_start_date,
      TRUNC (dt, 'W') + 6 week_end_date,
      TO_CHAR (dt, 'YYYY') || TO_CHAR (dt, 'WW') year_week_num,
      TO_CHAR (dt, 'YYYY') || '-' || TO_CHAR (dt, 'WW') year_week_name,
      TO_CHAR (dt, 'D') weekday_num,
      TO_CHAR (dt, 'Day') weekday_name
 FROM (SELECT     TO_DATE (:start_date) + ROWNUM dt
             FROM DUAL
       CONNECT BY TO_DATE (:start_date) + ROWNUM <= TO_DATE(:end_date));

Data Warehousing Training

I just got done custom creating an advanced data warehousing class, along with updating our beginner course to utilize Oracle 11g technologies. Whew, talk about a lot of work and only a short time to get it done! To summarize, the course that I teach is a full 5 day class on the following topics:

-Beginner Course Topics-
Beginning Data Warehousing
Dimensional Modeling
Oracle 11g Database options for Data Warehousing
Oracle Warehouse Builder OWB 11gR1 introduction
OWB11g Architecture and Installation
OWB11g and OWB 10gR2 differences, similarities
Exploring OWB11g Interface
Common setup of typical scenarios with remote machines and schemas
Building ETL Mappings from source to staging to target
Deep-Dive into OWB Mapping best practices and tips with the operators
Understanding Data Quality
Data Profiling and Data Rules Option within OWB11g
Advanced operators- Match & Merge, pre-post mapping operators, table functions, etc.
Understanding Workflow installation and Process Flow Basics

-Advanced Course Topics-
Oracle OLAP
Understanding Oracle's Position on OLAP & Hyperio Essbase
How to use OWB to construct full MOLAP cubes and how it differs from ROLAP
Extensive ETL Lab (2-3 hour lab encompassing many advanced techniques)
Pluggable Mappings
The Metadata Manager (Impact, Dependency, and Propagation)
Lifecycle Management (MDLs, Snapshots, Signatures, importation, exportation, comparison of
snapshots, etc.)
Security in OWB
Extra detail on Process Flows and Scheduling
OMB*PLUS scripting
OWB Experts
Advanced Tips and Tricks for project management, and high Performance


...and that's all. I built out about 90% of this class and taught it recently to pretty good reviews... Let's hope there's more students in store as I prepare to build out a new offering to my students!

Cheers
-Greg

Helpful Links on Data Warehousing Fundamentals

These might not all be 'fundamentals' but the articles at this link are solid. Many may not apply to you since they are great when applied to very specific situations (such as modeling multiple employee dimensions).

In general though, I think many people can learn something here. Kimball and sometimes Margy Ross here share good insight. They're not going to give you the gold nuggets that you'll likely get from their paid training, but the articles will get you thinking about your design.

HERE

FULL Outer Joins FOJ in OWB

See the owb blog: here

BIWA Summit

All- I am going to post and link to the summit upcoming in December here, and leave a perma-link on the right side of my page if you care to visit! Go here! link

Data Warehouse Structures: a bridge table

Bridge tables; I see this topic come up quite often. They are also known in some circles as gap tables, associative tables, helper tables, or sometimes just B tables. I have referenced a few very valuable bits about this structure below. Unfortunately, google needs a few more words than just bridge table or helper table, since the card game 'bridge' actually uses a special table, thus making the search for our info a bit harder. Sheesh! Copied here for fear they might be removed for some reason...I cannot always trust that the Internet Wayback Machine will work perfectly. So here in its original glory:

Help for Dimensional Modeling

Helper tables let you design and manage multivalued dimensions successfully.

By Ralph Kimball, DBMS online magazine

The goal of dimensional modeling is to represent a set of business measurements in a standard framework. Dimensional modeling is attractive because end users usually easily understand this framework. The schemas that result from dimensional modeling are so predictable that query tool vendors can build their tools around a set of well-known structures. The classic star join schema that results from a dimensional model usually has between six and 10 dimension tables connected directly to the fact table, as Figure 1 shows. These dimension tables have a very specific and precise relationship to the fact table.

FIG 1

Think of the fact table as a set of measurements made in the marketplace. These measurements are usually numeric and taken before the creation of a given fact table. A fact table record may represent an individual transaction, such as a customer withdrawal made at an ATM, or the fact table record may represent some kind of aggregated total, such as the sales of a given product in a store on a particular day. The dimension tables, on the other hand, usually represent textual attributes that are already known about such things as the product, the customer, or the calendar.

When you are building a fact table, the most important step in the detailed logical design is to declare the grain of the fact table. The grain declares the exact meaning of an individual fact record. In these examples, the first grain is individual customer transaction and the second grain is daily product total in the store. If the database designer is very clear about the grain of the fact table, then choosing the appropriate dimensions for the fact table is usually easy.

The secret of choosing dimensions for a given fact table is to identify any description that has a single value for an individual fact table record. In this way, you can work outward from the grain of the fact table and "decorate" the fact table with as many dimensions as you can imagine. For the ATM customer transaction example, imagine that the following dimensions all have a single value at the instant of the transaction:

• Calendar Date
• Time of Day
• Customer Account
• Physical Location
• Transaction Type (deposit, withdrawal, balance inquiry, and so on)
• Weather Summary for the Day.

These are all excellent dimensions to attach to the transaction-grained fact table record. Each of these dimensions has many descriptive attributes. The descriptive attributes are usually textual and are the basis for constraining and grouping in the user's reports. In the case of Time of Day, the textual attributes could refer to specific periods such as Morning Rush Hour, Mid Morning, Lunch Hour, Mid Afternoon, Afternoon Rush Hour, and so on. In this case, the sole numeric measured fact is the amount of the transaction. This is the basis for Figure 1.

Strict adherence to the grain definition lets you disqualify dimensions that do not have a single value for a given fact record. In the daily product totals example, you can propose the classic retail dimensions of:

• Calendar Date
• Product
• Store
• Promotion (assuming the promotion remains in effect the entire day).

But you must omit the Customer and the Check Out Clerk dimensions that you might assume would be included because these dimensions have many values at the daily grain we have chosen.

These examples give a very powerful insight. You are more likely to disqualify a dimension if you are dealing with an aggregated or summarized table. The more the fact table is summarized, the fewer the number of dimensions you can attach to the fact records. The converse of this is eye opening. The more granular the data, the more dimensions make sense. The lowest-level data in any organization is the most dimensional. This is why I argue that the "atomic data store" must be presented as a dimensional model.

Having made a pretty tidy argument for single-valued dimensions, perhaps I should consider whether there are ever legitimate exceptions. Are there situations where you might need to attach a multivalued dimension to a fact table? Is this even possible, and what problems might arise?

Consider the following example from healthcare billing. You are handed a data source for which the grain is the individual line item on a doctor bill. The data source could be patient visits to doctor offices or individual charges on a hospital bill. These individual line items have a rich set of dimensions that we can list rapidly:

• Calendar Date (of incurred charge)
• Patient
• Doctor (usually called "Provider")
• Location
• Service Performed
• Diagnosis
• Payer.

The numeric additive facts in this design (which are the core of every fact table in a dimensional design) would include Amount Charged and perhaps others including Amount Paid, depending on the nature of the data source.

So far, this design seems to be pretty straightforward, with obvious single values for all of the dimensions. But there is a sleeper. In many healthcare situations, there may be multiple values for Diagnosis. What do you do if a given patient has three separate diagnoses at the moment the service was performed? How about really sick people in hospitals that might have up to 10 diagnoses? How do you encode the Diagnosis dimension if you wish to represent this information?

Database designers usually take one of four approaches to this kind of open-ended, multivalued situation:

• Disqualify the Diagnosis dimension because it is multivalued
• Choose one value (the "primary" diagnosis) and omit the other values
• Extend the dimension list to have a fixed number of Diagnosis dimensions
• Put a helper table in between this fact table and the Diagnosis dimension table.

Let's not take the easy way out by disqualifying the Diagnosis dimension from the design.

Frequently, designers choose a single value (the second alternative). In the healthcare field this often shows up as the primary, or admitting, diagnosis. In many cases, your hands are tied because you are given the data in this form by the production system. If you take this approach, the modeling problem goes away, but you are left doubting whether the Diagnosis data is useful.

The third approach of creating a fixed number of additional Diagnosis dimension slots in the fact table key list is a hack, and you should avoid it. Inevitably, there will be some complicated example of a very sick patient who exceeds the number of Diagnosis slots you have allocated. Also, you cannot easily query the multiple separate Diagnosis dimensions. If "headache" is a diagnosis, which Diagnosis dimension should be constrained? The resulting or logic across dimensions is notorious for running slowly on relational databases. For all these reasons, you should avoid the multiple dimensions style of design.

FIG 2

If you really insist on modeling this multivalued situation, then a "helper" table placed between the Diagnosis dimension and the fact table is the best solution. (See Figure 2.) The Diagnosis key in the fact table is changed to be a Diagnosis Group key. The helper table in the middle is the Diagnosis Group table. It has one record for each diagnosis in a group of diagnoses. If I walk into the doctor's office with three diagnoses, then I need a Diagnosis Group with three records in it. It is up to the modeler to build either these Diagnosis Groups for each individual or a library of "known" Diagnosis Groups. Perhaps my three diagnoses would be called "Kimball's Syndrome."

The Diagnosis Group table is joined to the original Diagnosis dimension on the Diagnosis key. The Diagnosis Group table in Figure 2 contains a very important numeric attribute: the weighting factor. The weighting factor allows reports to be created that don't double count the Billed Amount in the fact table. For instance, if you constrain some attribute in the Diagnosis dimension such as "Contagious Indicator" with the values Contagious and Not Contagious, then you can group by the Contagious Indicator and produce a report with the correct totals. To get the correct totals, we must multiply the Billed Amount by the associated weighting factor. This is a correctly weighted report.

You can assign the weighting factors equally within a Diagnosis Group. If there are three diagnoses, then each gets a weighting factor of 1/3. If you have some other rational basis for assigning the weighting factors differently, then you can change the factors, as long as all the factors in a Diagnosis Group always add up to one.

You can, interestingly enough, deliberately omit the weighting factor and deliberately double count the same report grouped by Contagious Indicator. In this case, you have produced an "impact report" that shows the total Billed Amount implied partially or fully by both values of Contagious Indicator. Although the correctly weighted report is the most common and makes the most sense, the impact report is interesting and is requested from time to time. Such an impact report should be labeled so that the reader is not misled by any summary totals.

Although the helper table clearly violates the classic star join design where all the dimension tables have a simple one-to-many relationship to the fact table, there is no avoiding the issue of what to do with multivalued dimensions that designers insist on attaching to a fact table. Fortunately, designers rarely insist on attaching a multivalued dimension to a set of measurements. In most cases, you have no rational basis for assigning weighting factors, or even worse, you don't have a plausible short list of candidates from the multivalued dimension. Thus you don't waste time trying to attach the retailer's Customer dimension to an aggregated daily or monthly sales fact table. It just isn't interesting or useful.

When you attach a helper table to a fact table, you can preserve the star join illusion in your end-user interfaces by creating a view that prejoins the fact table to the helper table. The resulting view then appears to have a simple Diagnosis key that joins to our Diagnosis table in the healthcare example. The view can also predefine the multiplication of the weighting factor with any additive facts in the fact table.

I have seen two other situations besides healthcare where a multivalued dimension makes sense. In addition to the Diagnosis example discussed in this column, I have built multivalued dimensions for retail banks and standard industry classifications.

Retail Banks. There are often cases where the Account Dimension is "inhabited" by one or more human customers. If the bank wants to associate individual customers with account balances, then the Account dimension must play the same role as the Diagnosis Group dimension in the healthcare example. Banks are quite interested in both correctly weighted reports and impact reports.

Standard Industry Classifications. This term defines the so-called SIC code that is assigned to a commercial enterprise to describe what industry segment they are in. The problem with SIC codes is that all big enterprises are represented by multiple SIC codes. If you encode a fact table with an SIC dimension, you have the same problem as with the multiple diagnoses. SIC codes are really quite useful, however. If you want to summarize all the business you do selling to "Manufacturers" and "Retailers" by using their SIC code, you will need an SIC Group helper table. Here's a case where with some careful thought you may assign unequal weighting factors if you believe the enterprise is mostly a manufacturer and only a little bit a retailer.

Next month I will describe a different kind of helper table for dimensions. In this case, the helper table does not describe a many-to-many relationship but rather describes a hierarchical relationship. We can use this second kind of helper table to navigate organizational hierarchies and manufacturing parts explosions.

Data integration is not necessarily creating you a Data Warehouse

Nice quick read, but older article! link