2011 February

Oracle’s Business Intelligence Foundation

February 20, 2011/0 Comments/in Business Intelligence, Oracle BI, Posts /by Adam Getz

Oracle’s Business Intelligence Foundation is a complete enterprise business intelligence solution that delivers capabilities for reporting, ad-hoc query and analysis, OLAP, dashboards, and scorecards. In addition, the Oracle BI Foundation includes a common enterprise information model, which is a unified metadata model accessed by end user tools, allowing a “model once and deploy everywhere” paradigm. In addition, the Oracle BI Foundation allows users to access and interact with information in multiple ways, including web-based dashboards, collaboration workspaces, search bars, ERP and CRM applications, mobile devices, and MS Office applications.

Product Areas of Oracle BI Foundation

• Oracle Business Intelligence Suite Enterprise Edition Plus (OBIEE Plus)
• Oracle Business Intelligence Standard Edition One (OBISE One)
• Oracle Business Intelligence Standard Edition (OBISE)
• Oracle BI Publisher
• Oracle Essbase
• Oracle Real-Time Decisions (RTD)
• Oracle Scorecard and Strategy Management
• Oracle Essbase Analytics Link

Primary Components of Business Intelligence Systems

February 17, 2011/2 Comments/in Business Intelligence, Posts /by Adam Getz

Business Intelligence (BI) systems are software applications that enable better understanding of organizational data and provide the information organizations need to make enlightened decisions. Moreover, business intelligence systems are primarily focused on reporting, querying, and analysis of data residing in an enterprise data warehouse (EDW), and both dependent and independent data marts.

Primary Components of Business Intelligence (BI)

Fundamentally, there are five categories of business intelligence applications…

• Operational Reports: Displays data with rich presentation and within a structured layout (i.e. rows and columns).
• Query and Analysis: Interactive methods to query data, present data in an ad-hoc manner, and to find information on an as-needed basis.
• Dashboard Management: Graphical interfaces and real-time methods to provide guided analysis and to intuitively monitor organizational metrics.
• On-line Analytical Processing (OLAP): The capability of manipulating and analyzing data from multiple perspectives in a rapid fashion.
• Data Mining & Predictive Analytics: Utilizing statistics, algorithms, and sophisticated data search capabilities to discover hidden patterns and relationships in data and project future results.

Business Intelligence Vendor Consolidation (2003-2008)

February 11, 2011/0 Comments/in Business Intelligence, Posts /by Adam Getz

The business intelligence (BI) software marketplace has seen a wave of corporate acquisitions since the mid 1990s with the size and importance of the acquisitions coming to their apex during the years 2003 – 2008. This surge in corporate activity in the mid part of the decade has been characterized by a number of large software companies making major, strategic moves. Thus the industry is now in a state in which there are clear leaders or as Gartner, Inc puts it, “megavendors” that own a significant control over the over-all market share. In addition the rest of the BI vendors that not have been a part of acquisition activity are smaller organizations and are more commonly referred to as “niche” or “pure-play” vendors. These “niche” vendors each produce and sell viable business intelligence software products, but they do not encompass the breadth of complimentary technologies that the “megavendor” produces.

Business Intelligence Vendor Consolidation (2003-2008)

BI Vendor Consolidation (2003-2008)

As a result of the BI vendor consolidation of 2003-2008 the following megavendors have placed themselves in a position to control over 2/3 of the business intelligence software marketplace …: • IBM; • Microsoft; • Oracle; • SAP Business Objects

Basic Architecture of a Data Warehouse Appliance

February 9, 2011/0 Comments/in Data Warehousing, Posts /by Adam Getz

By definition, a data warehouse appliance is a complete hardware and software solution that contains a fully integrated stack of processors, memory, storage, operating system, and database management software. The data warehouse appliance is typically constructed to be optimized for enterprise data warehouses, designed to handle massive amounts of data and queries, and designed to scale and grow over time. At its core, a data warehouse appliance simplifies the deployment, scaling, and management of the database and storage infrastructure as it provides a self-managing, self-tuning, plug-and-play database management system that can be scaled out in a modular manner.

Basic Architecture of a Data Warehouse (DW) Appliance

Fundamentally, a data warehouse appliance is a fully-integrated solution that contains …: – Database Management System (DMBS); – Server Hardware; – Storage Capabilities; – Operating System (OS)

The primary factor for the platform scalability and large-query optimization of the data warehouse appliance is its massively parallel processing (MPP) architecture. These MPP architectures are comprised of numerous independent processors or servers that all execute in parallel. Also known as a “shared nothing architecture”, the MPP appliance architecture is characterized by a concept in which every embedded server is self-sufficient and controls its own memory and disk operations. Further, the MPP architecture effectively distributes data amongst a number of dedicated disk storage units connected to each server in the appliance. Computations are moved as close to the data as possible and data is logically distributed amongst numerous system nodes.

Almost all large operations in the data warehouse appliance environment, including data loads, query processes, backups, restorations, and indexing are executed completely in parallel. This divide-and-conquer approach allows for extreme high performance and allows for systems to scale linearly as new processors can easily be added into the environment.

Slowly Changing Dimensions – Type Four Models

February 8, 2011/0 Comments/in Data Warehousing, Posts /by Adam Getz

Type Four – Insert Into a History Table

Type four models, also known as leveraging “history tables”, is the most technically sophisticated of the four models and may be the most difficult to implement. This modeling technique provides for nearly unlimited tracking of historical records while having less storage requirement than type two models. Rather than storing the changes in the same table, a second “history” table is created which stores only the previous values of slowly changing dimensions.

Similar to type two models, type four models accommodate infinite changes to dimensional fields and create an additional record for every change to a dimensional attribute. But in contrast to type two, type four models allow for every change to an attribute to be generated within a new record in a relatively compact history table. The history table is subsequently more efficient in capturing a large amount of historical data.

Another key advantage of type four models is an efficient manner to query against a timeframe as the related search index only requires two fields (key and date fields). Other modeling techniques require more fields in the search index for date queries. Thus the search index utilizing a type four model is smaller, more intuitive, and quicker to retrieve the relevant record in the dimension table than the search index using other modeling techniques.

Type four models do have some important disadvantages. Namely type four models require implementation of multiple tables, are less intuitive for query developers, require more effort to develop and maintain than other types of models, and allow for history tables to grow to massive size.

Suppose that a vendor changes his phone number to 858-555-6555 from 202-555-8639 because the phone company has added a new area code. Utilizing a type four model, the vendor dimension table would be updated and a new record will be inserted into the vendor history table in the following manner…

• The vendor dimension table is updated:: – The phone number is updated from 202-555-8638 to 858-555-6555.
• A new record is inserted into the vendor history table:: – The vendor key is copied from the vendor dimension table.; – The phone number 858-555-6555 is inserted.; – The effective date of 12/15/2008 is inserted.

Slowly Changing Dimensions – Type Three Models

February 7, 2011/0 Comments/in Data Warehousing, Posts /by Adam Getz

Type Three – Leverage Previous and Current Value Fields

Type three models are defined by adding one or more columns to the dimension table, so that the new (or current or active) and old (or historical or inactive) value of an field are stored. In addition, multiple previous values can be stored within the table depending on how many previous columns are included in the the dimension table.

Type three modeling is a middle state between complete history loss of type one models and the numerous additional records of type two models. Moreover, type three models only provide only a limited view of history as only a predefined number previous value of any attribute can be retained, rather than a complete history. This modeling technique is fairly useful when changes to the dimension table are made on a regular schedule, such as annually, and when archival copies of the database are stored offline for historical and audit purposes. Type three modeling is not so useful when changes are more frequent and unpredictable. Multiple previous value fields could be added to the record to provide a longer historical trail, but it may be a challenge to design the table with the optimum number of previous fields. For the most part, type three modeling makes most sense to be implemented only when only there are a limited number of previous values that need to be retained.

Suppose that a vendor changes his phone number to 858-555-6555 from 202-555-8639 because the phone company has added a new area code. Utilizing a type three model, the vendor dimension table will contain both a current phone and previous phone field. Initially the vendor record contains a null value for the previous phone number and a value of 202-555-6555 for the current phone number.

In order to process this update using the previous and current fields, the vendor dimension table is updated:: – The previous phone field is updated to 202-555-6555, which is now the vendor’s last previous phone number.; – The current phone field is updated to 858-555-8639, which is now the vendor’s current number.; – The effective date is updated to the date of the change.

Slowly Changing Dimensions – Type Two Models

February 4, 2011/0 Comments/in Data Warehousing, Posts /by Adam Getz

Type Two – Update Record to Inactive / Create an Active Record

Type two modeling is a very reliable and straightforward technique for preserving history of changes to dimensional tables. It is commonly utilized when a full or partial set of the previous values of a dimension’s attributes must be retained. In type two modeling, every time an update occurs to any of the values in a dimensional table, a new record is physically inserted as active or current into the same table and the old records are marked inactive or historical.

The main advantage of this modeling technique is that it can accommodate and record a massive amount of history and nearly an unlimited changes to slowly changing dimensions. But this advantage also can become a significant drawback. Within this modeling technique, it is possible for dimension tables to grow to massive sizes and adversely affect both system and query performance. In addition, since upon every change this modeling technique requires an update to the linked fact table, implementation is fairly difficult and may require substantial effort to design, develop, and support.

Two unique sub-methods have been established for distinguishing the active (or current) record from the inactive (or historical) records: active flagging and tuple versioning.

In the type two – active flagging sub-method, an “Active” column is constructed within the dimensional table and acts as a flag. Flags are binary fields with permissible values being Y and N, T and F, or 1 and 0. A positive value (Y, T, 1) indicates an active record, while a negative value (N, F, 0) indicates an inactive record.

Example of Type Two – Active Flagging Sub-Method

Suppose that a vendor changes his phone number to 858-555-6555 from 202-555-8639 because the phone company has added a new area code. Using active flagging, the change to the vendor dimension table would be processed as follows…

• The current active record in the vendor dimension table is updated:: – The active flag is changed from T to F.; – The phone number field remains as 202-555-6555.
• A new record is inserted into the vendor dimension table:: – The vendor key is given the next available integer number.; – The vendor name field remains the same as in the old record.; – The phone number has the new value of 858-555-8639.; – The active flag is set to T.

In the type two – tuple versioning sub-method, start date and end date columns are included on the dimension table. The values of these date fields then define the period during which that record has been active. For the most part, the start date is the date the record has been created. Moreover, the end date is the date the record has become inactive, either because a newer record has replaced it or because the original record in the source system no longer exists. On the active record, the end date will either be left null, blank, or identified in another way such as all-nines, according to modeler’s preferences and standards.

The main advantage of the tuple versioning method over active flagging is that it provides an audit trail of the date and sequence of all updates.

Example of Type Two – Tuple Versioning Sub-Method

Continuing with the same example above but using the tuple versioning method, the updated phone number in a record in the vendor dimension table is processed as follows:

• The current active record in the vendor dimension table is updated:: – The end date is updated to the date of the change.; – The phone number field remains as 202-555-6555.
• A new record is inserted into the vendor dimension table:: – The vendor key is given the next available integer number.; – The vendor name field remains the same as in the old record.; – The phone number has the new value of 858-555-8639.; – The start date is the date the record is inserted into the table.; – The end date is intentionally left blank.