Overview
Sources
Data mart Specification
Master Data Management
OWB Mapping
Loading
Transformation
OWB Data Flow Diagram
Using Oracle OLAP
Deployment Diagram
Business Intelligence Beans Walkthrough
Conclusion
Products Used
This document describes some of the processes involved in the development of a multi-project data warehouse using Oracle Data Warehouse Builder 9.2.0.2.8.
In the context of this document, a project is defined as a construction exercise that requires the procurement of many thousands of items and many hundreds of thousands of labour hours.
The data warehouse was intended to contain enough performance data to allow labour and procurement costs to be analysed for multiple projects at the same time, and allow projects to be compared side-by-side.
For each project, two different operation data systems (ODS) were to be interrogated. These were a Purchase Management System (PMS) and a Work Operation System (WOS).
Relevant data from each source system were to be extracted, and transformed into a format that would be reportable against similar data from other projects.
As mentioned earlier, two source systems were identified. These are PMS to provide purchasing data and WOS to provide work data.
Data from PMS was relatively easy to obtain, as all instances run the same schema design, are deployed to Oracle RDBMS and there is a good supply of knowledge of the internal structures.
Views were developed to return a de-normalised dump of purchase order data. While a schema owner account was created for each PMS instance, into which the purchase order data view was created.
There were hundreds of attributes in each source system. Only a few key attributes were required for analysis. Initial investigation highlighted the following Dimension and Measure candidates.
|
Dimensions |
Measures |
|
Project
|
Quantity (Number of items procured) |
|
Supplier |
Spend (Amount spent on procurement) |
|
Discipline |
Actual Hours (Hours spent performing an activity) |
|
Commodity |
Earned Hours (Hours of original estimate completed) |
|
Currency |
|
|
Time |
|
|
Work Breakdown |
|
The power of Dimensions really depends on the hierarchies inside of them. For example, the hierarchy for the time dimension is very well known. Most people understand a hierarchy of Year, Quarter, Month and Day, which is what was used here.
For the Project dimension, there were project and sub project levels of hierarchy, but no other hierarchy was evident in any of the source systems for the remaining dimensions.
Where no business information was available to construct a hierarchy, a default level of 'All' was added to each dimension, to allow roll-up to include everything. As shown below:-
All Items
-> Item Name
The Work Breakdown dimension, a way of classifying types of effort, was applicable to data from all ODS. Unfortunately though, the coding used was different from one ODS to the next. So a way to resolve the disparate coding schemes and consolidate into a common dimensional hierarchy was required.
This consolidation was achieved through the development of a Master Data Manager; which would allow business users to maintain master reference data. In addition to this, the hierarchies could be maintained there too.
Importing business data from different sources required some method of standardisation such that the reference data from each source is converted into master reference data. This was then to be used to drive the dimensions in the data warehouse.
The master data manager shown here was developed very quickly using Oracles ADF components, and deploys as a web based application. It allows business users to manage reference data using no more than a web browser.
The tool targets a straight forward Oracle Schema known as the 'Master Data' schema.
An example of one of the hierarchies; work breakdown is shown below:-
The hierarchy levels are arranged as follows:-
All Types
->Type
--> Work Breakdown Name
Example data shown in the diagram below for Procurement type
For each of the work breakdown names there can be any number of alias names. This enables loading of source data that use incompatibly named work breakdowns.
The best way to understand this is to view the example below:-
Using the master data manager, the work breakdown for Engineering / Architectural is selected from a list:-
Engineering / Architectural is selected.
The configured aliases, for the selected work breakdown can be accessed by clicking the ‘Aliases’ button, giving the screen shown below:-
All of the possible spelling or name variations for the work breakdown can be maintained here. Any source data loaded, referring to any of the listed attributes, would have been mapped to the parent work breakdown.
Oracle Warehouse Builder is a development tool, and a runtime environment.
The developer uses the OWB client, and connects to a design repository for the storage and version maintenance of the warehouse design. This design includes the dimensions, cubes, staging tables and mapping tasks.
The design is deployed to a target schema, which is controlled by the OWB runtime environment.
In order to get the source data into the target warehouse schema, several stages of data processing was required. All processing was implemented as OWB mappings.
A set of loader mappings were created for each of the source system instances. The mappings pull the de-normalised data from the ODS into staging tables.
The dimension and cube tables are maintained by a number of mappings with prefix MAP_D for dimensions and MAP_C for cubes.
The dimension mappings insert+update records from reference tables in the Master Data schema.
The cube mappings validate the staging data against the dimension tables and insert+update records in the cube table.
The simplified diagram below shows the flow of source and master reference data into the OWB server.
The Oracle solution provides more than simple ETL processing. OLAP meta data can be configured in the database and used in analytical querying of the data in the form of cubes.
To achieve this, the dimensions and cubes had to be developed to the standards required by Oracle OLAP. Then the meta data generated in OWB can be loaded into the OLAP catalogue, using the Meta Data Export Bridge in OWB. At this point any business intelligence tool that works with the OLAP API can begin to query this data.
As Oracle 9i and later contains a dedicated calculation engine for analytical processing, it is possible to go further to load all the relational data stored in OWBTARGET into a separate schema containing OLAP cubes. These cubes are created in an analytical workspace in the POCOLAP schema. An analytical workspace is a kind of database within the Oracle instance that is designed to store multidimensional cubes.
Once the Analytical Workspace is created, it can be queried by several means. In this system, Business Intelligence Beans were selected, as they are free to use, and web based.
The diagram below shows how the warehouse moves from a relational star schema, to OLAP cubes.
A minimum amount of hardware and software resource was required in setting up this Data Warehouse.
Oracle 9i database was required with the OLAP option and OLAP patch 9.2.0.5. The OWB design was stored in a separate Design Repository.
The Warehouse runtime environment and reference data were stored on the OWB Server. The Business Intelligence Beans were assembled into a J2EE web archive, using Oracle JDeveloper, and then deployed to an instance of Oracle Application Server 10g.
The deployment diagram below describes the infrastructure used.
Providing a web application built on BI Beans, gives a good feel for how a meta data aware, analytical application works. The slides below pictures a type user session during the creation of a chart of engineering labour.
Selecting the graph type
Available measures shown automatically.
User selects required measures and clicks ‘Add’
The dimensions relevant for the selected measures are automatically added to the chart
User is able to control the chart layout; dimensions can be moved to appropriate axes
User can specify which dimension levels to view. In this case,
Engineering from the Work Breakdown dimension is selected
A chart is rendered automatically.
User can drill up and down through the dimensions by clicking on the links
Obtaining access to source system data presents several challenges, even in the same company.
There is the need for buy-in from the support personnel for the source systems. Database administrators must co-operate to provide adequate account access to the data.
There can also be legal and contractual issues surrounding the data. Some data is just not permitted to leave a source system.
These issues are not to be taken lightly, nor can they be left to the designer to resolve. There needs to be sufficient support from all levels of seniority such that everyone complies with the requests for data access, and does not hold up the project.
Building a successful data warehouse requires a clear knowledge of the business areas contributing to it. It is not sufficient to allow a source system to dictate the design, as the source system is focused on a specific area, and rarely has the ‘big picture’. Generating a warehouse in this way simply produces a glorified transactional reporting system.
Ideally, business users with complete knowledge of the required areas should be gathered together with the designer, so that they can model the business data.
When changing the structure of a dimension, re-creating the dimension requires that the cubes referencing it are dropped. This is because the foreign key constraints on the cube table require it.
If the aim is to target Oracle OLAP cubes, there are quite a few items to be aware of, and these are as follows:-
In order to export star schema data into an Oracle OLAP cube, there must be only one measure value for each valid combination of dimension values. As a best practice, use group by, sum aggregation operator.
Numeric scale and precision defined on a measure column must be able to contain the total sum of all values in that column.
Time dimensions are special in Oracle OLAP. All levels in the time dimension must have the following attributes: END_DATE, ID, NAME, TIME_SPAN
All attributes added to a dimension table must be made using the OWB Dimension editor, and not simply added to the underlying table, as meta data is needed for all attributes, and only the Dimension editor will create it.
When loading data into the analytic workspace, is important that there are no null foreign key values in the cube, or the load will fail.
It is worthwhile checking the source data for white space, as key lookup will happily and quietly return nulls for values that did not match. In some cases it’s a good idea to use the NVL () function to return a fixed ‘unassigned’ dimension value for anything that was not found.
The options of analysing the OLAP cubes are now very well catered for.
Custom web and desktop applications can be built using Business Intelligence Beans, which are free to use and deploy.
Oracle Discoverer now supports Oracle OLAP and provides a complete environment for business users to mine for KPI’s.
The following Oracle Products were used in the development of this data warehouse.
Oracle 9.2.0.5 with OLAP option
Oracle OLAP patch version 9.2.0.5
Oracle Workflow 2.6.2
OWB design repository
OWB runtime repository
OWB target schemas
Oracle AWM (Analytical Workspace Manager)
Oracle JDeveloper 10.1.2
