Staffing a Data Warehouse Project

I have earlier talked about the merits of an agile, customer-focused approach to a data warehouse greatly enhances the chances of success for the project. In this post, I will outline a plan to staff such a project and how to get things started.

Christening

Hopefully, before embarking on a data warehouse initiative, you have done ample due diligence to form the basis and business justification for such a project. You have probably identified the key benefits you want to derive and the most important business questions you want to answer. You should have also set a not-to-exceed limit of the overall project cost. If you adopt the approach of most product companies, the very next thing you need to do is to establish the identity of the project. Most product companies thrive on acronyms – CDW for Customer Data Warehouse, SEW (Strategic Enterprise Warehouse) and the like. One of my clients, while building an enterprise scale data warehouse, created a theme or analogy of constructing an actual house (the “Great Room” at the centre of the house being the executive dashboard where all key information entities “meet”), and adopted a logo of a construction helmet as well that appeared in all presentations, deliverables, coffee mugs, etc. Such “christening” of the project helps to assimilate the project team to the same mission. It also serves as an introduction to the larger business or user community in the organization and delivers the not-too-subtle message to the business cohorts that they are, in part responsible for the care of the initiative in its infancy. The collaboration with all these business users is where the roots of success of this initiative lie.

Staffing the project

A data warehouse / BI project requires a number of different roles and skills from IS as well as the user communities. It is helpful to remember, that there is hardly a project where you will be able to find a “specialist” for every role of the project. Most projects I have worked have people with multi-faceted skills who can fill more than one role. A word of caution: try to avoid people who claim to have been skilled at every aspect of the project. Such people are less likely to be well versed in best practices in all the different areas and are more likely to cause irreparable damage if entrusted with a key area in which they are deficient, but have masked it well. At one of my previous projects, we had a “Superman Architect” who was not a believer of surrogate keys in the data warehouse. When the warehouse needed to accommodate an additional data source, it faced a tremendous development effort to move from having natural keys to surrogates.  In the real world, you will find people with excellent skills in just two or three areas who can fill those specialist roles.

The Financial Data Mart – Part 3

Prove the numbers. No one in the enterprise is as concerned as Finance to prove the numbers correct. Disparities and inaccuracies in financial numbers not only present a distorted picture of organizational performance, they also carry the threat of huge regulatory consequences. That is why, even before Finance starts using the data warehouse for analysis, they validate and reconcile the details with the different sub-ledgers to prove that the summaries indeed tie to the details. This task is not trivial and if business rules are not vetted and understood well enough, discrepancies will creep up that will take an inordinate amount of time to resolve. These issues crop up when there are external systems that feed financial information to the General Ledger as summarized journal entries, while retaining the details in those systems. The subledgers in the data warehouse then have to assimilate these details from all the external systems in addition to those in Oracle GL. Such issues are unique for each organization. I will attempt to broadly outline these issues in a later post. For now, let us look at some of the most common reports that are generated from the Financial data mart.

Financial Statement Analysis:

• Trial Balance reports are used to review balances for GL accounts for budgets, actuals and encumbrances for any currency. They are typically run for a date range, a range of accounts
• Balance Sheet detail reports showing balance sheet accounts (assets, liability and equity accounts). Period to period comparisons, month-to-date, quarter-to-date, year-to-date, budget-to-actual comparisons are typical of these reports.
• Cash Flow statements for companies (usually, the first segment of the GL chart of accounts), fiscal periods.
• GL Balances report – this is a detail of all balances in different currencies, for different periods, for different cost centers
• GL detail, or Journal Entry detail reports –to trace the GL balance to all the supporting transactions.
• P & Ls (Profit & Loss Statements) show the revenue, costs, expenses, etc. for different cost centers, fiscal periods. Usually, these are more complicated as further details of the underlying subledgers are usually sought. Revenue analyses by additional dimensions like sales persons are done, some times at the detail of individual billing transactions (or invoices).

Key Financial Ratios: Detail reports are not usually run for Financial ratios and metrics like Current Ratio, Quick Ratio, Debt to Asset, Fixed Asset Turnover, Inventory Turnover (GL), Gross Profit Margin, Net Income, Retained Earnings, Return on Equity, Return on Assets, Asset Turnover, etc. The metrics are calculated for the company and trends over time and comparisons with budgeted numbers are usually of most interest.

Statistical Reports: 

• Headcounts by cost center per company, budget-actual comparisons, and trends over time
• Asset count (e.g. how many laptops do we own?) by cost center, by company, budget-actual comparisons, trended for different time periods or period-period comparisons.

The source of almost all of these metrics is the General Ledger. The source of all detail level GL transactions are Journal Entries. For further analysis, one needs to drill down to the individual Subledger (revenue, cost, expense subledgers). Budget details are also sourced from the GL. Thus, in the Financial data mart, these will be our fact tables:

• General Ledger Balances
• Journal Entry Details 
• Budget details

 Looking at the different analyses above, the key dimensions are apparent: 

• Time (Day, Month, Quarter, Year)
• Fiscal Month (for all aggregate analysis)
• Chart of Accounts
• Set of Books

If you decide to build the subledgers as part of the subject area data marts (e.g. Payables Subledger as part of the payables data mart), then these are dimensions above will need to be used and are the designated conformed dimensions.

The Agile Road to a Successful Data Warehouse

Building an Enterprise Data Warehouse from Oracle eBusiness Suite is not an easy task. Most organizations, who are newly implementing Oracle eBS, undergo tremendous changes in business processes. Building the EDW concurrently with the Oracle implementation is possible, but carries many risks. More often than not, the EDW team learns of a significant business process change much too late, when development of that data mart has already been completed. Drastic design changes at such a late stage leave insufficient time for testing which invariably results in poor quality of data and severe design compromises. Data quality is the most critical component of success of any data warehouse effort. When your EDW project must go live concurrently with an Oracle eBS implementation, an agile, customer focused approach is highly recommended.

The other crucial factors for the success of an EDW from Oracle eBS or, for that matter, any data warehouse / business intelligence effort are:

• Thorough knowledge of the business processes
• Good understanding of reporting and analytic requirements
• A best-practice design
• Good coding practices
• More than adequate testing
• User training and communication

The next obvious question is: How do you know if your DW/BI effort has been successful? David Wells and James Thomann of TDWI describe three “types” of success and their definitions as they relate to data warehousing:

• Economic success – the data warehouse has a positive impact on the bottom line.
• Political success – people like what you’ve done. If the data warehouse isn’t used, it’s obvious that you failed politically.
• Technical success – this is the easiest to accomplish. However, don’t overwhelm your users with too much technology. Success also means that the chosen technologies are appropriate for the task and are applied correctly.

Financial Data Mart – Part 2

The Finance department is almost always the heaviest user of the data warehouse. They get consolidated numbers from all business operations and this is where concern for profit, cash flow and balance sheet is the greatest. The Finance department analyzes all costs, all revenues and provides information to management that drive initiatives or corrective action across the business. People in Finance understand how the board and banks see the numbers and how the shareholders and the investment community value them. The company I am currently doing a project was recently sold to an investment firm on the strength of these numbers.

Let’s look at the four areas of financial analyses and reporting that is usually done from the GL:

The Financial Data Mart

In many multi-part series, I will lay out the design for the Enterprise Data Warehouse from an Oracle eBusiness Suite. Obviously, these will be by business areas. I will start with the core financial module – the General Ledger. Let’s try to put a structure to each of these series:

Part 1 – Functional Overview
Part 2 – Key Reporting / Analyses
Part 3 – Target design and considerations
Part 4 – Source Schema, Key queries, ETL maps, business rules
And more as needed for the topic under discussion.

Part 1: A short Overview of Oracle General Ledger:

The General Ledger (GL) is at the heart of the Accounting system of Oracle eBS. The main purpose of GL is to record financial activity of the company and to produce financial and management reports. The GL consolidates financial information from all other transactional modules or subledgers and maintains summary level information. For example, it stores the accounting information for a Receivables invoice or an invoice payable to a vendor. It stores accounting entries for expense for depreciation of an asset or an inventory transfer. It also has accounting entries made within itself using journal entries.

If you want to know more on Oracle General Ledger, or any other Financial module, refer to the latest User Guide (pdf file) or the Oracle E-Business Suite Financials Handbook, a handy reference that I highly recommend to anyone who wants a thorough understanding of the functional concepts of the financial modules within Oracle eBusiness Suite.

The following diagram is a high level overview of how some of the core modules interact with each other within Oracle Financials:

http://intelligentbusiness.files.wordpress.com/2008/06/gl_int1.gif

Source: 11i Financial Functional Foundation

Let us dig a little deeper