Federating Essbase and Relational Data in Oracle Business Intelligence Enterprise Edition

Overview

Using the Oracle BI Server repository, developers can build business models that federate Essbase data sources with other sources supported by Oracle BI Server. This includes horizontal federation (which provides reports that can display data from both Essbase and relational data sources) and vertical federation (which provides reports with the ability to drill through aggregate Essbase data into detail relational data).

In this OBE, you learn how to open the Oracle BI Administration Tool, connect to the online repository, build a horizontally and vertically federated Business Model, create a Subject area, and create analyses from the objects in the Subject area.

Prerequisites

Before starting this tutorial, you should:

1.	Have access to or have Installed Oracle BI Suite EE 11.1.1.5
2.	Have access to or have Installed Oracle Essbase 11.1.2.1
3.	Verify that Oracle BI EE and Essbase services are started
4.	Have access to or have installed the Oracle Sample Application for Oracle BI EE, build 825 (for 11.1.1.3) Note: This tutorial assumes the Sample Application repository is already loaded to the Oracle BI server. The tutorial uses the Essbase BISAMPLE cube and the Oracle BISAMPLE database schema.

Building a Business Model to Provide Horizontal Federation

In this topic, you build a business model in the Oracle BI EE repository that provides horizontal federation of an Essbase data source and an Oracle relational data source. The completed business model enables you to generate the following ad hoc analysis in Oracle BI EE:

The analysis demonstrates horizontal federation between Essbase and a relational data source. Data for Discnt_Value is provided by an Oracle relational database, data for Units is provided by Essbase, and data from the conforming dimension, Product, is applied across the measures.

Creating a Business Model from an Essbase Data Source

1.	Select Start > Programs > Oracle Business Intelligence > BI Administration. The Oracle BI Administration Tool window is displayed.
2.	From the toolbar, click the Open Online () button. The Open Online coreapplication_OH601898404 dialog box is displayed. Note: The name of the dialog box will differ depending on your installation.
3.	Enter Admin123 into the Repository Password field and a valid username and password to access BI Server, and click Open. The BI Repository layers are displayed.
4.	In the Physical layer, expand 03 - Essbase Sample E1, and drag BISAMPLE to the Business Model and Mapping layer. The BISAMPLE business model is displayed in the Business Model and Mapping Layer.
5.	In the Business Model and Mapping layer, expand the BISAMPLE business model. Dragging the cube from the Physical layer to the Business Model and Mapping layer automatically creates the business model and all of its objects, including dimension hierarchies, logical dimension tables, logical fact tables, logical columns, and logical joins. Notice that the Sample table is marked with a hash symbol to indicate that it is the fact table in the logical schema.
6.	Expand Sample, and then Sources. Notice that there is currently only one logical table source, Sample, for this logical table.

Adding Relational Measures

1.	In the Physical layer, expand 01 - Sample App Data (ORCL) > Catalog > BISAMPLE > F10 Billed Rev.
2.	Drag Discnt_Value from the Physical layer to the Sample fact table for the BISAMPLE business model in the Business Model and Mapping layer. Notice that Discnt_Value is now a logical column in the Sample logical table, and the Sample logical table is now mapped to two logical table sources: the Essbase Sample cube and the F10 Billed Rev. table in the BISAMPLE relational schema.
3.	Double-click the F10 Billed Rev. logical table source to open the Logical Table Source dialog box.
4.	Select the Column Mapping tab. Notice that the Discnt_Value logical column is mapped to the corresponding physical column in the F10 Billed Rev. table in the BISAMPLE schema.
5.	Click OK to close the Logical Table Source dialog box.
6.	Double-click the Sample logical table source to open the Logical Table Source - Sample dialog box.
7.	If necessary, select the Column Mapping tab. Select both the Show mapped columns and Show unmapped columns options. Notice that the remaining logical columns are mapped to the Essbase Sample cube data source, which provides all the facts in addition to Discnt_Value.
8.	Click OK to close the Logical Table Source - Sample dialog box.
9.	Double-click the Discnt_Value logical column in the Business Modeling and Mapping layer to open the Logical Column - Discnt_Value dialog box.
10.	Select the Aggregation tab.
11.	From the "Default aggregation rule" drop-down list, select Sum.
12.	Click OK to close the Logical Column - Discnt_Value dialog box. Notice that the Discnt_Value logical column icon has changed to indicate that it is a logical column with an aggregation rule.

Creating Logical Joins in the Business Model: Products Dimension

This subtopic shows you how to create logical joins that establish the relationship between the Essbase source and the relational source in the business model.

1.	In the Business Model and Mapping layer, in the BISAMPLE model, expand the Products logical table, and then theSources folder. Notice that there is currently only one logical table source, Sample, for this logical table.
2.	In the Physical layer, expand 01 - Sample App Data (ORCL) > Catalog > BISAMPLE > D10 Product (Dynamic Table).
3.	Drag the Prod_Dsc physical column from the Physical layer to the Product Members logical column. Notice that the Products logical table now has two logical sources: Sample and D10 Product (Dynamic Table).
4.	Double-click the D10 Product (Dynamic Table) logical table source to open then Logical Table Source - D10 Product (Dynamic Table) dialog box.
5.	If necessary, select the Column Mapping tab and notice that the Product Members logical column maps to theProd_Dsc physical column in the relational schema.
6.	Click OK to close the Logical Table Source - D10 Product (Dynamic Table) dialog box.
7.	Open the Sample logical table source and notice that the remaining logical columns are mapped to the Essbase cube. The Product Members column is now mapped to columns in two physical sources.
8.	Click OK to close the Logical Table Source - Sample dialog box.
9.	Under BISAMPLE, expand the Products logical hierarchy, then All Products, then Brands, then LOBs, and thenProduct Types to display the Product Members logical level. Double-click Product Members to display the Logical Level - Product Members dialog box. If necessary, select the Keys tab. Notice that both the Product Members and Product Members - Member Key columns are part of the logical level key structure. The Member Key column is an internal Essbase reference and does not correspond to the relational data source. NOTE: When you create a business model from an Essbase data source, Member Key columns are automatically created and included in the logical level key structure for each level in the logical hierarchy. If you want to conform relational data to a level in the logical hierarchy, you must remove the Member Key column from the logical level key structure. You can do this by either modifying the logical level key or by removing the Member Key column from your business model.
10.	Close the Logical Level - Product Members dialog box.
11.	For the business model in this tutorial, you remove the Member Key logical columns from the Products logical table so that the logical level key does not conflict with the relational data source. Additionally, you remove the Memnor (outline sort) logical columns to provide a cleaner business model for end users. Under the Products logical table, select the Memnor and Member Key columns for each level of the Products dimension.
12.	Press Delete. At the prompt, click Yes.
13.	Under the Products logical hierarchy, double-click the Product Members logical level to display the Logical Level - Product Members dialog box again. If necessary, select the Keys tab. Notice that the Product Members - Member Key column is no longer displayed in the logical level key structure. Close the Logical Level - Product Members dialog box.

Creating a Subject Area and Saving the Repository

1.	Drag the BISAMPLE business model from the Business Model and Mapping Layer to the Presentation layer to create a presentation catalog.
2.	In the toolbar, click the Check In Changes () button to check in changes to the repository. When prompted to check global consistency, click Yes.
3.	You should receive a message that the business model BISAMPLE is consistent. Click Yes to mark it available for queries.
4.	Verify that Consistency Check Manager is displayed with no errors. If you receive errors, correct them before proceeding to the next step.
5.	Close Consistency Check Manager.
6.	Select File, and then Save to save the repository.

Verifying your Work in Oracle BI EE

This subtopic shows you how to build queries, view the results, and examine the corresponding log files.

1.	Log in to Oracle Business Intelligence with an administrator user name and password.
2.	Select New, and then Analysis. The Select Subject Area box is displayed.
3.	Click BISAMPLE. Analysis Editor is displayed.
4.	Create the following query: Products.Product Members, Sample.Units, Sample. Discnt_Value.
5.	Select the Results tab to display the analysis. This analysis demonstrates horizontal federation between Essbase and a relational data source. Data for Units comes from Essbase, while data for Discnt_Value comes from the Oracle database.
6.	Click the Save Analysis () button to save your analysis as Horizontal. Click OK to save the analysis.
7.	In the top right corner of the window, click Administration. Administration links are displayed.
8.	Under Session Management, click Manage Sessions.
9.	Under Cursor Cache, in the row for your query, click View Log. NOTE: The Oracle BI EE Sample Application has preset query logging levels for the included users. See Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for more information about setting the query logging level.
10.	Scroll down to view the SQL and MDX issued to the Oracle database and Essbase, respectively (entire query log not shown).

Building a Business Model to Provide Vertical Federation

The goal of this topic is to modify the BISAMPLE business model created in the preceding topic to add vertical federation of an Essbase data source and an Oracle relational data source. The completed business model allows you to generate the following report in Oracle BI EE:

This report shows vertical federation between Essbase and a relational source. It demonstrates that you can drill from aggregated data in Essbase cubes into detail data in relational sources. The report shows detail of revenue for the time hierarchy. The underlying Essbase cube grain is at the month level for time. If you do not expand the time hierarchy column below the month level, the Oracle BI server only generates queries to the Essbase cube. When you expand detail below the month level (week, day) in the time hierarchy, then the server retrieves data from the relational source to provide the details.

Adding Relational-level Detail to the Months Logical Table

Because you cannot create physical joins between Essbase and relational sources, the relational source is unable to inherit the Months hierarchy from the Essbase source. Therefore, you must physically define the Months hierarchy to allow for drill down. In this case, you must have Years, Half Years, Quarters, Months, Weeks, and Day columns in your relational source, so that you are able to drill all the way from Years down to Days. The BISAMPLE Essbase database outline contains two hierarchy levels, All Months and Total Time Hierarchy, which have no corresponding columns in the relational database; you modify your business model hierarchy to account for these missing levels in a later topic.

In this topic, you map relational columns to each level in the Essbase Months hierarchy. This technique enables drill-through from aggregated data in the Essbase cube into detail data in the relational source.

1.	NOTE: This topic assumes you have Oracle BI Administration Tool open and you have opened the online sample application repository, as described at the beginning of the horizontal federation topic. In the Business Model and Mapping layer, under BISAMPLE, expand the Months logical table.
2.	In the Physical layer, expand 01 - Sample App Data (ORCL) > Catalog > BISAMPLE > D01 Time Day Grain.
3.	Drag Per_Name_Year from the Physical Layer to the Years logical column in the Business Model and Mapping Layer. When you are prompted to check out objects, click Check Out.
4.	Notice that the D01 Time Day Grain physical table is now listed as a source for the Months logical table.
5.	Drag Per_Name_Half from the Physical Layer to the Half Years logical column in the Business Model and Mapping Layer.
6.	Drag Per_Name_Qtr from the Physical Layer to the Quaters logical column in the Business Model and Mapping Layer.
7.	Drag Per_Name_Month from the Physical Layer to the Month Members logical column in the Business Model and Mapping Layer.
8.	Drag Per_Name_Week and Calendar_Date from the Physical Layer to the Months logical table in the Business Model and Mapping Layer. Per_Name_Week and Calendar_Date are added as columns in the logical table.
9.	Double-click the Months logical table to open the Logical Table - Months dialog box. If necessary, select the Keystab.
10.	Select the row for Months, and click the Delete () button. At the confirmation prompt, click Yes.
11.	In the Key Name column, enter Time_Key_Day.
12.	In the Columns drop-down list, select Calendar_Date.
13.	In the "Primary key" drop-down list, select Time_Key_Day.
14.	Click OK to close the Logical Table - Months dialog box. Notice that Calendar_Date is now displayed with a key icon.
15.	Similar to the Products dimension, the Months dimension has internal Essbase logical level keys that conflict with the relational data source. For the business model in this tutorial, you remove the Member Key logical columns from the Months logical table so that the default logical level keys (in the Months hierarchy) do not conflict with the relational data source. Additionallly, you remove the Memnor (outline sort) logical columns to provide a cleaner business model for end users. Under the Months logical table, select the Memnor and Member Key columns for each level of the Months dimension.
16.	Press Delete. At the prompt, click Yes.

Modifying the Months Hierarchy for Vertical Federation

1.	Because there are no corresponding relational columns for the All Months and Total Year Hierarchy levels, you first modify the Months hierarchy to have Years as the the top level. In the Business Model and Mapping Layer, under the BISAMPLE business model, expand the Months hierarchy.
2.	Drag the Years logical level to the Months hierarchy icon. Check out objects if prompted. A duplicate Years level, Years#1, is displayed as a child of Months.
3.	Select the All Months logical level and press Delete. If prompted to check out objects, click Check Out. At the delete confirmation prompt, click Yes. The Years#1 hierarchy remains.
4.	Rename Years#1 to Years.
5.	Now you add levels for relational-level time detail (weeks and days). Right-click the Month Members logical level, and select New Object, and then Child level. At the confirmation prompt, click Yes. The Logical Level dialog box is displayed.
6.	In the Name text box, enter Weeks.
7.	Select the Keys tab.
8.	Create a key called Week_Key. Select Per_Name_Week for the column, and select Use for Display.
9.	Click OK to close the dialog box.
10.	Right-click the Weeks logical level, and select New Object, then Child Level. The Logical Level dialog box is displayed.
11.	In the Name text box, enter Day Detail.
12.	Select the Keys tab.
13.	Create a key called Date_Key. Select Calendar_Date for the column, and select Use for Display and Chronological Key.
14.	Click OK to close the dialog box.
15.	Add logical columns to the new hierarchy levels: Drag the Per_Name_Week logical column from the Months logical table to the Weeks logical level in the Months hierarchy. Drag the Calendar_Date logical column from the Monthslogical table to the Day Detail logical level in the Months hierarchy. NOTE: If you are prompted to check out objects, click Yes.

Mapping Facts to Multiple Sources and Setting Logical Levels

In this subtopic, you map a fact to both Essbase and relational data sources and set logical levels for the fact and dimension logical table sources so that the Oracle BI server knows when to send queries to the Essbase source and when to send queries to the relational source.

1.	In the Business Model and Mapping Layer, under the BISAMPLE business model, expand the Sample logical table.
2.	In the Physical Layer, expand 01 - Sample App Data (ORCL), then Catalog, then BISAMPLE, and then F10 Billed Rev.
3.	Drag Revenue from the Physical layer to the Gross Revenue logical column in the BISAMPLE business model. At the prompt, click Check Out.
4.	Double-click Gross Revenue to open the Logical Column - Gross Revenue dialog box. Select the Aggregation tab.
5.	For the default aggregation rule, select Sum, then click OK to close the dialog box.
6.	Expand the Months logical table, and then Sources.
7.	Double-click the Sample logical table source to open the Logical Table Source - Sample dialog box. Select theColumn Mapping tab and notice that the All Months, Half Years, Month Members, Quarters, Total Month Hierarchy, and Years logical columns map to physical columns in the Sample Essbase cube. NOTE: You may have to check Show unmapped columns.
8.	Select the Content tab and notice that the Months dimension logical level is set to Month Members.
9.	Click OK to close the Logical Table Source - Sample dialog box.
10.	Double-click the D01 Time Day Grain logical table source to to open the Logical Table Source - D01 Time Day Grain dialog box. Select the Column Mapping tab and notice that the Calendar_Date, Half Years, Month Members, Per_Name_Week, Quarters, and Years logical columns map to physical columns in the D01 Time Day Grain relational table.
11.	Select the Content tab and set the Months dimension logical level to Day Detail.
12.	Click OK to close the Logical Table Source - D01 Time Day Grain dialog box.
13.	Under BISAMPLE, expand Sample, and then Sources.
14.	Double-click the Sample logical table source to open the Logical Table Source - Sample dialog box. Check out objects if prompted.
15.	Select the Content tab and verify that the Months dimension logical level is set to Month Members.
16.	Click OK to close the Logical Table Source - Sample dialog box.
17.	Double-click the F10 Billed Rev. logical table source to open the Logical Table Source - F10 Billed Rev. dialog box.
18.	Select the Content tab and set the Months dimension logical level to Day Detail.
19.	Click OK to close the Logical Table Source - F10 Billed Rev. dialog box. NOTE: The lowest level of data stored in the Essbase cube is monthly. Setting different levels for the logical table sources tells the Oracle BI engine that for any queries that include the month level and above, use the Essbase cube. For any queries below the monthly level (weeks, for example), use the relational source.
20.	Drag the BISAMPLE business model from the Business Model and Mapping Layer to the Presentation layer. The subject area BISAMPLE#1 is created. NOTE: You can create multiple subject areas from a single business model. When you add objects to a business model on which an existing subject area is based, the new objects are not automatically added to the subject area. You must drag the new objects from the business model to the subject area if you want to update the subject area.
21.	In the toolbar, clilck the Check In Changes () button to check in changes to the repository. When prompted to check global consistency, click Yes.
22.	Verify that Consistency Check Manager is displayed with no errors. If you receive errors, correct them before proceeding to the next step.
23.	Close Consistency Check Manager.
24.	Select File, and then Save to save the repository.
25.	Close the repository and the Administration Tool.

Checking Vertical Federation in Oracle BI EE

1.	Log in to Oracle Business Intelligence with an administrator user name and password.
2.	Select New, and then Analysis. The Select Subject Area box is displayed.
3.	Click BISAMPLE#1. Analysis Editor is displayed.
4.	Create the following query: Months.Months (hierarchy), Sample.Gross Revenue.
5.	Select the Results tab to display the analysis.
6.	Expand 2010, then 2010 HY1, and then 2010 Q1.
7.	Click the Save Analysis () button, and save the analysis as Vertical. Click OK to close the Save As dialog box.
8.	At the top of the Oracle BI page, select Administration.
9.	Under Session Management, select Manage Sessions.
10.	For your query, select View Log to open the log file.
11.	Check the log. It may be necessary to scroll to the bottom to locate your query. As expected, the data is retrieved from the Essbase cube.
12.	Click the Back button for your browser to return to the Manage Sessions screen. Click Open, and then in the Recent list, select Vertical to return to the Vertical analysis.
13.	Expand 2010 /01, and then 2010 Week 2 to view daily detail.
14.	Open the Manage Sessions page again and view the log file for the most recent query. As expected, the data is retrieved from the relational source. This analysis demonstrates vertical federation between Essbase and a relational data source. If you do not expand the time hierachy column below the month level, the Oracle BI server only generates queries to the Essbase cube. As soon as you expand detail below the month level (week, day), then the server retrieves data from the relational source.

Summary

This tutorial covers how to federate data between Essbase and relational data sources in Oracle BI EE.

In this tutorial, you have learned how to:

• Create a business model in Oracle BI Administration Tool that horizontally federates data between Essbase and relational databases
• Create a business model in Oracle BI Administration Tool that vertically federates data between Essbase and relational databases.