Category: Data Warehousing

Business Intelligence, Data Warehousing, Reporting

OBIEE data source types and data retrieval methods

OBIEE is capable of connecting to and retrieving data from a variety of data sources.  The type of data sources that OBIEE can connect to are OLTP, OLAP, Data Warehouses (ROLAP), and Files.

  • OLTP databases – these include the normalized-design databases including ERP, CRM and other LOB systems.
    – The relational databases supported are: Oracle databases, Microsoft SQL Server, IBM DB2, and Teradata Warehouse.
    – And the ERP/CRM sources supported are: Oracle E-Business Suite, Oracle Peoplesoft, Oracle Siebel CRM, Oracle JD Edwards, and SAP.  Note: any ERP/CRM system running on one the databases mentioned above can be supported, but those mentioned here are special ERP/CRM sources.
  • OLAP databases – these include dimensional-databases including applications based on dimensional databses.
    – The OLAP datases supported are: Oracle Essbase, Oracle OLAP, Microsoft Analysis Services, and SAP Netweaver BI.
    – And the OLAP applications sources supported are: Oracle Hyperion Planning and Oracle Hyperion Financial Management.
  • Dimensionally-modeled data warehouses – these are relational databases designed with a star-schema / dimensional model, on one of the 4 supported relational databases mentioned above.
  • Files – Microsoft Excel, XML files, Flat files.

The data retrieval methods used to connect to these sources are:

  • OLTP – SQL
  • OLAP – MDX
  • Data Warehouse – SQL
  • Files – ODBC

OBIEE has the ability to connect to multiple of these data sources at the same time, and the data sources can be of the same or different types.  So, for example, an OBIEE Server can source data from an Oracle 11g Data Warehouse, and from an Oracle Essbase 11g OLAP cube at the same time, and join the data together for user consumption.
Similarly, file datasources can also be added to provide additional information, for example from an external source, and joined to data from the other sources mentioned above.  This “joining” of data is handled by the OBIEE BI Repository and BI Server.

To the end user accessing the data from a front-end tool (Analysis Editor / Answers), it seems like a single data source. That is one of the features that makes OBIEE such a great tool particularly for heterogeneous database environments.

Data Integration, Data Warehousing

Oracle positioned as a leader in the Gartner Magic Quadrant for Data Integration tools

Oracle, along with Informatica, IBM, SAP and SAS, are positioned as leaders in the Gartner Magic Quadrant for Data Integration tools.

Other data integration vendors that made it into the Magic Quadrant, but not in the leaders category are Microsoft, Information Builders, Syncsort, Talend, Pervasive Software.

GartnerMagicQuadrant_for_DataIntegrationTools

Source: Gartner (October 2012)

When performing its analysis, Gartner considers these areas of data integration:

  • Data acquisition for business intelligence (BI) and data warehousing
  • Consolidation and delivery of master data in support of master data management (MDM)
  • Data migrations/conversions
  • Synchronization of data between operational applications
  • Interenterprise data sharing
  • Delivery of data services in an SOA context

And analyzes these features and functionality that the data integration tools should provide:

  • Connectivity/adapter capabilities (data source and target support)
  • Support for different modes of interaction with a range of data structure types
  • Data delivery capabilities
  • Support for the delivery of data across a range of latency requirements
  • Data transformation capabilities
  • Provide facilities for developing custom transformations and extending packaged transformations
  • Metadata and data modeling capabilities
  • Design and development environment capabilities
  • Data governance support capabilities (via interoperation with data quality, profiling and mining capabilities)
  • Deployment options and runtime platform capabilities
  • Operations and administration capabilities
  • Architecture and integration capabilities
  • Service enablement capabilities

Per Gartner, Oracle’s strengths are:

  • Breadth of functionality,
  • Usability of core functionality across use cases
  • Addressing data challenges across a range of application- and data-oriented customer bases

And their cautions are:

  • Enabling product migration,
  • Complexity of integrated deployment across products,
  • Pricing perception and availability of skills

As Oracle-centric developers and solution providers, we are most concerned with the capabilities and future of Oracle Data Integrator (ODI), Oracle Warehouse Builder (OWB), Golden Gate and, in some cases, Informatica (a part of most OBIA installations). These products are doing well in the market, but are a bit behind the install bases of Microsoft, SAP and IBM.  The largest install base is with Microsoft (12K customers), followed by SAP (10K) and IBM (9.4K).  Oracle has 3.5K and Informatica has 5K data integration customers.  With a major push now behind the ODI tool, you can expect to see growth in Oracle’s numbers in the coming years.

Apparently, Oracle also has plans for providing a migration path (a migration wizard) from Oracle Warehouse Builder (OWB) to ODI.  This will be a very welcomed tool for OWB users seeking to migrate to ODI.  And this will further solidify ODI as the data integration tool for Oracle-centric IT organizations, and start to create more availability of skilled ODI resources.

You can read all the details at Gartner Magic Quadrant for Data Integration Tools.

Data Warehousing

Interested in learning more about Exadata?

The Oracle Exadata database machine is a packaged database system of pre-optimized and pre-configured software, servers, and storage (with massive memory), designed for extreme performance and high availability.  Exadata can handle both OLTP (transactional, high concurreny) systems and OLAP (analytical, high-scan) systems, and is a great option for supporting large-scale OBIEE/data warehousing projects.

If you are interested in learning more about Oracle’s Exadata database machine, the Independent Oracle Users Group (IOUG) has a series of webcasts to get you started.  You can check them out here …
http://www.ioug.org/p/cm/ld/fid=164

They also have sessions on other interesting Oracle topics that can be found here …
http://www.ioug.org/p/cm/ld/fid=153

Another great resource for Exadata learning content is the Oracle Learning Library, and the Exadata content can be found here …
http://apex.oracle.com/pls/apex/f?p=44785:2:0:FORCE_QUERY:NO:2,RIR,CIR:P2_TAGS:Exadata

Data Warehousing, Reporting

Data Federation, Horizontal Federation and Vertical Federation in OBIEE

Data Federation is the process of and implementation of integrating metadata from multiple data sources into a single metadata database to allow for the integrated reporting of the data from those multiple sources. In OBIEE terms, Data Federation involves bringing in the metadata from multiple sources into the physical layer of the RPD (BI Repository) and integrating the metadata into a single business model and possibly a single subject area.  In OBIEE, the data sources can be relational (OLTP databases or star-schema data warehouses), multidimensional (OLAP), or files (such as Excel or flat files); and the data can be of varying levels of aggregation in these sources.  This is one of the most powerful features of OBIEE.

When an Analysis (report) is run from Answers that uses data from these sources, the BI Server creates the appropriate SQL (OLTP) or MDX (OLAP) statements to retrieve the data from the appropriate source and then integrates the data for display to the user.  The user does not need to know what the source of the data is, how many sources there are, or how the data is retrieved.

There are two common patterns of how data can be federated in OBIEE for reporting – these patterns/methods are termed Horizontal Federation and Vertical Federation. I will explain them here.

Horizontal Federation involves setting multiple data sources for a common single logical table object in the Business Model and Mapping (BMM) layer of the RPD, such that the granularity of the data from the various sources is at the same level, and some columns of the single logical table come from one source, and some from another source(s) – basically each source adding columns (not rows) to the logical dataset.  A typical scenario for this pattern is where there is related data in multiple sources for a particular subject, but no single source holds the entire body of data for that subject – and this helps to bring all the descriptive data for the subject together into one.

Vertical Federation involves setting multiple data sources for a common single logical table object in the BMM layer of the RPD, such that the data for each columns could be coming from multiple sources, but at varying granularity levels – basically each set adding rows (not columns) to the logical dataset.  A typical scenario for this pattern is where data at an aggregated granularity is sourced from an aggregated OLAP data source or an aggregated OLTP data source, while data at a detailed granularity is sourced from the transaction level (OLTP or detailed-level star-schema) data source.