In this tutorial, you learn how to federate Essbase multidimensional sources with other relational data sources supported in Oracle Business Intelligence (OBI) Suite Enterprise Edition Plus.
Approximately 1 hour
This tutorial covers the following topics:
Place the cursor over this icon to load and view all the screenshots for this tutorial.
(Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)
Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.
Beginning with Oracle Business Intelligence Suite Enterprise Edition Plus 10.1.3.3.2, Essbase is supported as an Oracle Business Intelligence (OBI) Server data source. This provides the ability to expose Essbase data to a broad user audience via OBI Answers, OBI Interactive Dashboards, and OBI Delivers.
Using the OBI Server repository, developers can build business models that federate Essbase data sources with other sources supported by OBI 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).
This tutorial provides the following:
|
Instructions to connect to the OBI Administration Tool and create a new repository |
|
Steps to import Essbase cubes into the Physical layer of the repository |
|
Steps to import a relational data source into the Physical layer of the repository |
|
Steps to build a business model that demonstrates horizontal federation between Essbase and a relational source |
|
Steps to build a business model that demonstrates vertical federation between Essbase and a relational source |
|
Steps to build the Presentation layer of the repository |
|
Steps to create Answers requests that demonstrate horizontal and vertical federation |
|
Steps to verify query results using OBI log files |
Before starting this tutorial, ensure that you have installed and configured Oracle Business Intelligence Suite Enterprise Edition, version 10.1.3.3.2 or later, and the following Oracle Hyperion products, version 9.3.1 or later:
|
Essbase Server |
|
Essbase Administration Services |
|
Essbase Integration Services |
|
Essbase Client |
|
Shared Services |
|
Provider Services |
You can download these products from the Oracle E-Delivery Web site.
If you need assistance with installation, system requirements, and so forth, you can access the most current documentation for these products on the Oracle Documentation page.
If you are an Oracle employee, you can use the BIC2G VMWare image found here. This VMWare image has all of these prerequisite components preinstalled.
Additional prerequisite steps:
|
Use the provided files to create and populate two new tables, SALES and SALARIES, in the HR sample schema in your Oracle database. |
|
Ensure that the Sample.Basic and ASOsamp.Sample databases provided during the Essbase installation have been installed, and that the sample data for both databases has been loaded. |
Note:
The servers for this OBE are hosted locally (localhost). If your server is not stored locally, the format is generally the IP address or the machine (server name). Confirm this information with your administrator before beginning this tutorial.
This tutorial was created using a Windows Server 2003 environment. Therefore, screenshots, Start Menu options, and so forth, will vary.
Oracle by Example (OBE) tutorials:
Instructor-led training:
|
Oracle BI Suite EE 10g R3: Build Repositories (D53149GC11 ) |
|
Oracle BI Suite EE 10g R3: Create Reports & Dashboards (D53151GC10) |
Articles:
|
Integrating Oracle Hyperion Essbase System 9 with Oracle Business Intelligence |
For additional courses and training, search Oracle University at this link.
The goal of this topic is to create a new OBI repository, and then import Essbase cubes and a relational schema into the Physical layer of the repository. To create the repository and import the data sources, perform the following steps:
|
Creating an OBI repository | |
|
Importing Essbase cubes into an OBI repository | |
|
Importing a relational schema into an OBI repository | |
This subtopic shows you how to open the OBI Administration Tool, create a new repository, and set the logging level for the Administrator user.
| 1. | From the Start menu, select Programs > Oracle Business Intelligence > Administration to open the Oracle BI Administration tool.
|
| 2. | Select File > New.
|
| 3. | Name the repository essbase_fed.rpd.
|
| 4. | Click Save to open the repository in the Administration Tool.
|
| 5. | Select Manage > Security to open Security Manager.
|
| 6. | In the left pane expand Security and select Users.
|
| 7. | Double-click Administrator in the right pane to open the User - Administrator dialog box.
|
| 8. | Set the password to Administrator and confirm the password.
|
| 9. | Set the logging level to 2.
|
| 10. | Click OK to close the User - Administrator dialog box.
|
| 11. | Select Action > Close to close Security Manager.
|
This subtopic shows you how to import two sample Essbase cubes into the Physical layer of an OBI repository.
| 1. | Select File > Import > from Multidimensional to open the "Import from Multi-dimensional" dialog box.
|
| 2. | Set Provider Type to Essbase.
|
| 3. | Set Essbase Server to your Essbase server. This tutorial uses localhost.
|
| 4. | Provide the username and password credentials to access the Essbase server. In this example, the username is admin and the password is password.
|
| 5. | Click OK to open the Select Source dialog box.
|
| 6. | Expand <Essbase server> and use Ctrl-click to select the Sample > Basic and ASOSamp > Sample cubes. In this example, localhost is the Essbase server.
|
| 7. | Click Import.
|
| 8. | When import completes, click Close to close the Select Source dialog box.
|
| 9. | Expand the objects in the Physical Layer and verify that the Sample > Basic and ASOSamp > Sample cubes are imported.
|
This subtopic shows you how to import a relational schema into the Physical layer of an OBI repository. In this tutorial, you import the HR sample schema from an Oracle database. It is assumed that you have added the SALES and SALARIES tables as described in the Prerequisites section of this tutorial.
| 1. | Select File > Import > from Database to open the Select Data Source dialog box.
|
| 2. | In the Connection Type field, select OCI 10g/11g from the drop-down list.
|
| 3. | Enter the TNS name for your Oracle instance. This tutorial uses orcl.
|
| 4. | Provide the username and password credentials to access the Oracle instance (hr/hr in this example).
|
| 5. | Click OK to open the Import dialog box.
|
| 6. | Select HR.
|
| 7. | Click Import to open the Connection Pool dialog box.
|
| 8. | Accept all the defaults and click OK to start the import process.
|
| 9. | When import completes, close the Import dialog box.
|
| 10. | Expand the objects in the Physical Layer and verify that the HR objects are imported.
|
The goal of this topic is to build a business model in the OBI repository that demonstrates horizontal federation of an Essbase data source and an Oracle relational data source. The completed business model allows you to generate the following report in OBI Answers:
This report demonstrates horizontal federation between Essbase and a relational source. Data for the Original Price measure is coming from the Essbase multidimensional source. Data for the Salary measure is coming from the HR schema in the Oracle relational source. Data from the conforming dimension, Store Manager, is applied across the measures.
To build this business model, perform the following steps:
|
Building a business model | |
|
Creating logical joins in the business model | |
|
Verifying your work in Answers | |
This subtopic shows you how to build a business model in the Business Model and Mapping layer of an OBI repository.
| 1. | Drag ASOsamp from the Physical layer to the Business Model and Mapping (BMM) layer to create the ASOsamp business model.
|
| 2. | Expand the ASOsamp business model in the BMM layer. Dragging the cube from the Physical layer to the BMM layer automatically creates the business model and all of its objects, including dimension hierarchies, logical dimension tables, logical fact tables, logical columns, logical joins, and so forth. Note that Sample has a yellow icon, indicating that it is the fact table in the logical schema.
|
| 3. | Expand Sample > Sources and note that there is currently only one logical table source, Sample, for this logical table.
|
| 4. | Drag HR > SALARIES > SALARY from the Physical layer to the Sample logical table in the BMM layer.
|
| 5. | Note that SALARY 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 SALARIES table in the HR relational schema.
|
| 6. | Double-click the SALARIES logical table source to open the Logical Table Source dialog box.
|
| 7. | Click the Column Mapping tab. Notice that the SALARY logical column is mapped to the SALARY physical column in the SALARIES table in the HR schema.
|
| 8. | Click OK to close the Logical Table Source dialog box.
|
| 9. | Double-click the Sample logical table source to open the Logical Table Source dialog box.
|
| 10. | If necessary, click the Column Mapping tab. Select both the Show mapped columns and Show unmapped columns options. Note that all of the remaining logical columns are mapped to the Essbase Sample cube source, which provides all the other facts in addition to Salary.
|
| 11. | Click OK to close the Logical Table Source dialog box.
|
| 12. | Double-click the SALARY logical column in the BMM layer to open the Logical Column - SALARY dialog box.
|
| 13. | Click the Aggregation tab.
|
| 14. | Set the Default aggregation rule to Sum.
|
| 15. | Click OK to close the Logical Column - Salary dialog box. Notice that the SALARY logical column icon has changed to indicate that it is a logical column with an aggregation rule.
|
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. | Expand ASOsamp > Store Manager > Sources. Note that there is currently only one logical table source, Sample, for this logical table.
|
| 2. | Drag the HR > SALARIES > EMPLOYEE physical column from the Physical layer to the Gen2,Store Manager logical column.
|
| 3. | Note that the Store Manager logical table now has two logical sources: Sample and SALARIES.
|
| 4. | Double-click the SALARIES logical table source to open the Logical Table Source - SALARIES dialog box.
|
| 5. | If necessary, click the Column Mapping tab and note that the Gen2,Store Manager logical column maps to the EMPLOYEE column in the SALARIES table in the HR schema.
|
| 6. | Close the Logical Table Source - SALARIES dialog box.
|
| 7. | Open the Sample logical table source and note that the remaining logical columns are mapped to the Essbase cube columns. Gen2,Store Manager is now mapped to columns in two physical sources.
|
| 8. | Close the Logical Table Source - Sample dialog box.
|
| 9. | Rename the Gen2,Store Manager logical column Store Manager.
|
| 10. | Drag the ASOsamp subject area to the Presentation Layer to create a presentation catalog.
|
| 11. | Save the repository and click Yes when prompted to check global consistency.
|
| 12. | You should receive a message that the business model ASOsamp is consistent. Click Yes to mark it available for queries.
|
| 13. | Verify that Consistency Check Manager appears with no errors or warnings. If you receive errors or warnings, correct them before proceeding to the next step.
|
| 14. | Close Consistency Check Manager. This set of steps demonstrated that it is fairly straightforward to build a logical relationship between an Essbase source and a relational source in the business model.
|
| 15. | Close the repository and the Administration Tool.
|
This subtopic shows you how to build Answers queries, view the results, and examine the corresponding log files.
| 1. | If necessary, stop the Oracle BI Server service.
|
| 2. | Add the essbase_fed.rpd to the REPOSITORY section of the NQSConfig.ini file in \OracleBI\server\Config.
|
| 3. | Save and close NQSConfig.INI and start the Oracle BI Server service to load the repository into memory. Verify that the other Oracle BI and Essbase services are started as well.
|
| 4. | Log in to Oracle Presentation Services as Administrator with the password Administrator.
|
| 5. | Click Answers.
|
| 6. | Click the ASOsamp subject area.
|
| 7. | Create the following query and filters: Store Manager.Store Manager, Sample.Original Price, Sample.SALARY where Store Manager is equal to / is in Arvin, Carrie, Debra, Eric.
|
| 8. | Display Results.
|
| 9. | Click Settings > Administration to open the Oracle BI Presentation Services Administration window.
|
| 10. | Click Manage Sessions to open the Session Management window.
|
| 11. | Click View Log for the query to open the Session Monitor.
|
| 12. | Check the log. It may be necessary to scroll to the bottom to locate your query.
This report demonstrates horizontal federation between Essbase and a relational source. Data for the Original Price measure is coming from the Essbase multidimensional source. Data for the Salary measure is coming from the HR relational source. Data from the conforming dimension, Store Manager, is applied across the measures.
|
| 13. | Close the log.
|
| 14. | Click Finished to close the Session Management window.
|
| 15. | Click Close Window to close the Oracle BI Presentation Services Administration window.
|
| 16. | Leave Answers open.
|
The goal of this topic is to build a business model in the OBI repository that demonstrates 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 OBI Answers:
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. In this example, aggregate sales data for Region and State is located in the Essbase cube, and detail sales data for City is located in the relational source. In this section, you build a business model and set different logical levels for the logical table sources. This tells the Oracle BI engine that for any queries that include the State level and above, use the Essbase cube. For any queries below the State level (City in this example), use the relational source.
To build this business model, perform the following steps:
|
Building a business model | |
|
Building a dimension hierarchy | |
|
Setting logical levels | |
|
Verifying your work in Answers | |
| 1. | Verify that the Oracle BI Server service is started.
|
| 2. | Open the Oracle BI Administration tool.
|
| 3. | Select File > Open > Online. Now that you have a valid, consistent repository, you can make changes in online mode.
|
| 4. | Log in as Administrator with the password Administrator to open the essbase_fed.rpd repository in online mode.
|
| 5. | Drag Sample from the Physical layer to the BMM layer to create the Sample business model.
|
| 6. | In the Physical layer, expand ORCL > HR > SALES.
|
| 7. | In the BMM layer, expand Sample > Basic.
|
| 8. | In the Physical layer, drag the SALES physical column from HR > SALES to the Sales logical column in Sample > Basic in the BMM layer.
|
| 9. | Expand Basic > Sources in the BMM layer and note that the previous step created a SALES logical table source for the Basic logical table. There are now two logical sources: Basic and SALES.
|
| 10. | Double-click the Sales logical column to open the Logical Column - Sales dialog box.
|
| 11. | Click the Data Type tab. Note that the Sales logical column maps to physical columns in two data sources: the Essbase cube and the HR relational source.
|
| 12. | Click the Aggregation tab and set the aggregation rule for the Sales logical column to Sum.
|
| 13. | Click OK to close the Logical Column - Sales dialog box.
|
| 14. | Select File > Check In Changes or click the Check In Changes icon on the toolbar.
|
| 15. | Click No when prompted to check global consistency.
|
| 16. | Save the repository.
|
The important point to note here is that because you cannot create physical joins between Essbase and relational sources, the relational source is unable to inherit the Market hierarchy from the Essbase source. Therefore, you have to physically define the Market hierarchy to allow for drill down. In this case, you must have Region, State, and City columns in your relational source, so that you are able to drill all the way through Region and State to City. In this set of steps, you duplicate the Market hierarchy to allow for this drill down through all of the hierarchy levels. You use this technique to enable drill-through from aggregated data in the Essbase cube into detail data in the relational source.
| 1. | In the BMM layer, expand Sample > Market.
|
| 2. | Drag the REGION and STATE physical columns from HR > SALES to their corresponding columns in the Market logical table. This creates a SALES logical table source for the Market logical table.
|
| 3. | Drag the CITY physical column from HR > SALES to the Market logical table. This creates a new CITY logical column in the Market dimension.
|
| 4. | Double-click the Market logical table to open the Logical Table - Market dialog box.
|
| 5. | Click the Keys tab.
|
| 6. | Double-click the existing Market key to open the Logical Key - Market dialog box.
|
| 7. | Deselect the Gen1,Market, Region, and State columns, and select the CITY column to set the key to CITY only.
|
| 8. | Click OK to close the Logical Key - Market dialog box.
|
| 9. | Click OK to close the Logical Table - Market dialog box.
|
| 10. | Expand the Market dimension hierarchy.
|
| 11. | Double-click the State level to open the Logical Level - State dialog box.
|
| 12. | Click the Keys tab.
|
| 13. | Delete the State key, which is a compound key of Region and State. Please note: In cases where there are duplicate member names (for example, duplicate city names across states/regions), you should keep the level keys as composite keys, otherwise you will get incorrect results.
|
| 14. | There should now be only one key, State#1, which contains the State logical column. Click OK to close the Logical Level - State dialog box.
|
| 15. | Right-click the State logical level in the Market hierarchy and select New Object > Child Level to open the Logical Level dialog box.
|
| 16. | Name the level City and click OK.
|
| 17. | Drag the CITY logical column from the Market logical table to the City child level in the Market hierarchy.
|
| 18. | Right-click the CITY column (not the level) in the hierarchy and select New Logical Level Key to open the Logical Level Key - CITY dialog box. Verify that the CITY column is selected and click OK.
|
| 19. | Double-click the City level to open the Logical Level - CITY dialog box.
|
| 20. | On the General tab, set the number of elements at this level to 22.
|
| 21. | Click OK to close the Logical Level - City dialog box.
|
| 22. | Check in changes. Do not check consistency at this time. Save the repository. Your repository should look similar to the screenshot:
|
In this subtopic, you set logical levels for the fact and dimension logical table sources so that the BI Server knows when to send queries to the Essbase source and when to send queries to the relational source.
| 1. | Expand Sample > Market > Sources.
|
| 2. | Double-click the SALES logical table source to open the Logical Table Source - SALES dialog box.
|
| 3. | Click the Column Mapping tab and note that the Region, State, and CITY logical columns map to physical columns in the SALES relational table.
|
| 4. | Click the Content tab and set the Market dimension logical level to City.
|
| 5. | Click OK to close the Logical Table Source - SALES dialog box.
|
| 6. | Double-click the Basic logical table source to open the Logical Table Source - Basic dialog box.
|
| 7. | Click the Column Mapping tab and note that the Gen1 Market, Region, and State logical columns map to physical columns in the Essbase cube.
|
| 8. | Click the Content tab and verify that the Market dimension logical level is set to State.
|
| 9. | Click OK to close the Logical Table Source - Basic dialog box.
|
| 10. | Expand Sample > Basic > Sources.
|
| 11. | Double-click the Basic logical table source to open the Logical Table Source - Basic dialog box.
|
| 12. | Click the Content tab and verify that the Market logical level is set to State.
|
| 13. | Click OK to close the Logical Table Source - Basic dialog box.
|
| 14. | Double-click the SALES logical table source to open the Logical Table Source - SALES dialog box.
|
| 15. | On the Content tab, set the Market logical level to City.
|
| 16. | Click OK to close the Logical Table Source - SALES dialog box. The lowest level in the cube is State. Setting different levels for the logical table sources tells the Oracle BI engine that for any queries that include the State level and above, use the Essbase cube. For any queries below the state level (City in this example) use the relational source (SALES table in this example). You verify this when you run Answers' queries later in this tutorial.
|
| 17. | Drag the Sample subject area to the Presentation layer to create a Sample presentation catalog.
|
| 18. | Select File > Check In Changes or click the Check In Changes icon on the toolbar.
|
| 19. | Click Yes to check global consistency.
|
| 20. | You should receive the message "Business model "Sample" is consistent. Do you want to mark it available for queries?".
|
| 21. | Click Yes to make it available for queries. The Consistency Check Manager should appear with no errors or warnings. Correct any errors or warnings before proceeding to the next step.
|
| 22. | Close the Consistency Check Manager.
|
| 23. | Save the repository.
|
This subtopic shows you how to build Answers queries, view the results, and examine the corresponding log files.
| 1. | Return to Answers and click Reload Server Metadata.
|
| 2. | Log in to the Sample subject area.
|
| 3. | Create the following request in Answers: Market.Region, Market.State, Basic.Sales.
|
| 4. | Click Results.
|
| 5. | Select Settings > Administration to open the Oracle BI Presentation Services Administration window.
|
| 6. | Click Manage Sessions to open the Session Management window.
|
| 7. | Click View Log for your query to open the log file.
|
| 8. | 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.
|
| 9. | Return to Answers.
|
| 10. | Drill down on a state to view CITY data.
|
| 11. | Check the log. As expected the data is retrieved from the relational source.
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. |
In this tutorial, you should have learned how to:
|
Connect to the OBI Administration Tool and create a new repository |
|
Import Essbase cubes into the Physical layer of the repository |
|
Import a relational data source into the Physical layer of the repository |
|
Build a business model that demonstrates horizontal federation between Essbase and a relational source |
|
Build a business model that demonstrates vertical federation between Essbase and a relational source |
|
Build the Presentation layer of the repository |
|
Create Answers requests that demonstrate horizontal and vertical federation |
|
Verify query results using OBI log files |
Place
the cursor over this icon to hide all screen shots.