Data Warehousing

Transforming departmental data to a common enterprise repository with the "hub-and-spoke" method. Fewer moving parts when you put your data through transforms after the loading. The following diagram illustrates a "Best Practice" Data Warehousing and 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 efficiently
The architecture supporting Artech’s Data Warehousing/Business Intelligence (DW/BI) practice combines the best database technology available to bring diverse data sources together and create one consistent dataset spanning an entire business.

 
Case Study: Enterprise Solution with Oracle and Hyperion Technologies

The Data Warehouse foundation is built with Oracle with Hyperion Essbase 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 (Oracle)

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 Hyperion 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 (Hyperion)

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

Excel Add-In

  • Permits direct spreadsheet access to cube-based data, no re-key
  • Powerful ad hoc query capability
  • Combined with Visual Basic for Applications (VBA) 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, and (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. Essbase can be tailored to particular individuals eliminating the need for someone to navigate through many menus to get to what they need. Combining Oracle and Hyperion…you pretty much can have whatever you want.