Data Warehousing: An ODS Example - dummies

By Thomas C. Hammergren

Here is a data warehousing example to help you understand ODS (operational data store). Suppose that you work in a large financial company that provides a variety of services to elite companies and individuals across the world.

Your company has grown to its current form as a result of a series of mergers and acquisitions throughout the last 25 years. The trend in recent years toward a convergence of banking and securities services has given your company an opportunity to become a full-service provider to your customers.

Your company’s average customer is likely to participate in many (perhaps all) of these types of activities:

  • Traditional stock brokering (buying and selling shares of stocks, including margin-account activity)

  • Fixed-income investments (corporate and governmental bonds)

  • Options trading accounts, including risk arbitrage

  • Cash asset management

  • Short-term loans and other debt instruments

  • Intermediate- and long-term loans and other debt instruments

  • Venture capital investments

You want your customers to use your company as one-stop shopping for anything involving large sums of money. Your company’s situation is a little complicated, however — particularly in these two areas:

  • The mergers and acquisitions have left your IT infrastructure with a large number of solo applications (applications that aren’t integrated with one another, even though they probably should be).

    One system handles stock trading for U.S. stocks, for example, and another system handles stock market activity from non-U.S. global exchanges. In addition, separate systems handle fixed-income activity, all debt activity in the U.S., all short-term debt in Europe, and all intermediate-, and long-term European debt. And the list goes on.

  • The definition of a customer is somewhat hazy. Individuals set up corporations and partnerships through which they make investments or secure loans for business deals. Your corporate customers might be subsidiaries of other corporations, which might also be your customers.

Your business practices call for all credit activity with every customer to pass through a series of quality assurance checks before being approved:

  • Every customer of yours, whether an individual or a company, has several ceilings on debt activity. One ceiling is an amount of total outstanding debt at any given time. Until a customer reaches this first-level ceiling, he or she can, without human intervention, automatically take out a new loan or act against a credit line, buy stocks on margin, or perform any other type of activity that increases debt.

  • Every customer can exceed the first-level ceiling to a second ceiling amount after receiving approval from one of your company’s executives.

  • For an executive to approve credit activity past the first ceiling to the second, he or she must check a series of measures.

    For example, the customer must have a certain asset balance in place; the customer might not have reduced total assets on hand in all accounts of all types (such as cash, stocks, and bonds) by more than 15 percent in the preceding 30 days; and the bank has maximum amounts for total debt in each country, adjusted by assets held in each country.

  • To help control risk, your company tracks the relationships between all your customers to get a real picture of a customer’s financial state. For example, an individual might control a series of corporations, each of which you treat as an individual customer with its own asset and debt activity, in addition to that of the individual’s own accounts.

    When your company’s executives approve any additional beyond-the-first-ceiling debt, however (for a real estate partnership that involves that individual, for example), the executives must assess an overall picture of what’s going on with that individual’s activity to avoid too much risk exposure in case of financial problems.

Although the quality assurance checks described in the preceding list are conceptually straightforward, they’re extremely complex to implement, for one simple reason: Those checks need data from systems all over your enterprise, from many different systems.

This data includes information such as all the asset activity, all the debt activity and current loans outstanding, and information about which loans were just paid down earlier in the day.

One approach you can try is to provide your company’s executives (the ones who have to make the loan-approval decisions) with interfaces into every system in which they might find necessary data. These executives then can run a long series of queries (if they can even be supported), pull out the appropriate values, paste them into a spreadsheet program, and make the decision.

This approach has two problems, however: The chance for human error is high, and the pace at which this type of activity must occur is okay only during “ordinary” times.

During a time of financial crisis, when many or most of your firm’s customers are buying and selling stocks, covering margins, buying and selling options, trying to handle their hedge accounts, executing against credit lines, and doing all kinds of other activities very quickly, your company’s employees just can’t keep up.

In this situation, the ODS comes to the rescue. This figure illustrates a conceptual architecture that you can use to implement an ODS that meets your business missions. First, the ODS provides a consolidated picture of a client’s balances for automatic loan processing under the first ceiling. Next, the ODS enables executives to make yes-or-no decisions about loan requests up to the second ceiling.

The ODS provides users with a consolidated, almost instantaneous picture of different data in suppo
The ODS provides users with a consolidated, almost instantaneous picture of different data in support of a specific business mission.

To get a better look at the data flows within the ODS environment, see this figure, in which updates to one of the data sources (the system that handles U.S. debt) propagate into the ODS environment.

The ODS has to reflect the state of data throughout the enterprise as quickly as possible.
The ODS has to reflect the state of data throughout the enterprise as quickly as possible.

The following steps indicate what occurs in the ODS environment:

  1. A customer makes a regularly scheduled loan payment, and the system that handles payments on U.S. loans and lines of credit processes the payment.

  2. The loan payment application updates its database to reflect the payment.

  3. The loan payment application then immediately pushes the updated data to the ODS.

  4. The ODS receives the update and processes it, updating its database contents (in this example, reducing the customer’s total outstanding debt amount).

  5. The ODS performs any internal processing, consolidation, alerts, or other necessary functions.

An environment like the one in the preceding list can — if everything is architected properly — provide a picture of all relevant data from all over the place — now — in support of the firm’s risk-management mission.

You must validate the need for real-time updates into your ODS because these updates are complex to create, as described in the following section.

Constantly challenge assumptions and ask questions: “What happens if you have to wait until the end of the day? What if updates were twice a day? Every hour?” Be absolutely certain that the mission dictates real-time updates because creating an ODS takes longer (and is more expensive) than a data warehouse.