Approach‎ > ‎

Data Warehouse Approach

Transforming departmental data to a common enterprise repository with the "hub-and-spoke" method. Fewer moving parts when you put your data through appropriate transformations after the loading. The following diagram illustrates a "Best Practice" Data Warehousing and Online Analytically Processing (OLAP) Architecture.


The multi-tier or "hub-and-spoke" data warehouse features a general-purpose relational data 
                staging area as 
the "hub", coupled with OLAP-based application specific data marts as "spokes" 
to deliver information accurately and efficiently.

The architecture supporting the Data Warehousing/Business Intelligence (DW/BI) practice combines the enterprise database technology available to bring diverse data sources together and create one consistent dataset spanning an entire business.

Case Study: Enterprise Solution with Oracle Technologies
This foundation is built using an Oracle Data Warehouse sitting on top, yielding a robust, industrial-strength platform at the center of the evolution path for relational-OLAP hybrid database technology. Placed in an intranet framework, this architecture offers virtually unlimited access to huge amounts of information with simple browser-based navigation tools for anyone in your organization.

Data Backbone

Extraction
  • Batch, Record and Field level integrity checks
  • Verify minimum required field entry for processing (reject nulls)
  • Map source field names to standard (generic) naming convention
Transformation
  • Calculation of all derived measures
  • Convert source units to standard units
  • Map source field names to standard (generic) naming convention
Load
  • Map “scrubbed” transactions to Star Schema “fact table”
  • Update attributes in secondary Star tables from source master data
  • Flag changes for cube refresh
In addition to mapping heterogeneous, legacy data sources to a common data set, the Data Backbone has two other generic interface architectures that support the creation of a complete view of the business. For example, information critical to reporting can exist only in spreadsheets at field locations. The same benefits of standard business rules that apply to legacy system extracts are even more compelling when content and quality are vulnerable to spreadsheet support and turnover. Browser-based, standardized data templates replace spreadsheets, capturing key information once for subsequent reuse.

Another browser-based capability involves the maintenance of factors used by the transformation algorithms within the Data Backbone. When exchange rates or duties change, the people with knowledge of those changes can enter the new information directly in the tables supporting the transforms. Maintenance screens also permit remedial action for transactions that fail integrity checks. Web access ensures that the individual most aware of what needs to be done can make the fix directly rather than relying on intermediaries with other priorities. amounts of information with simple browser-based navigation tools for anyone in your organization.

OLAP Server

Analyzer
  • Executive Information System (EIS) summaries with “drill-down”
  • Multiple visual formats for intuitive navigation/problem identification
  • Dashboard-style “traffic-lighting” for Key Performance Indicators
Java/Excel Add-In
  • Permits direct spreadsheet access to cube-based data, no re-key
  • Powerful ad hoc query capability
  • Combined with Java/JSP/HTML generation of virtually any presentation, report or analysis can be fully automated
The overall intent of this architecture is two-fold:
  1. Let an entire organization work off the same set of numbers
  2. Give everyone direct access to the information required for them to be effective in their jobs.
With this basic framework in place, many other data needs can be addressed. With a year of history accumulated, budgeting can be done with the high level aggregates planners are comfortable with, then pushed down with allocation rules to have budgets at the same level actuals are captured at. The OLAP multi-slice world then extends to the budget, as well, and math doing actual vs. budget can be done “on-the-fly” from the cube.