Creating an OBIEE Repository for Relational Data Source
OBIEE (Oracle Business Intelligence Enterprise Edition)
In this part of my OBIEE blog, I'll guide you through basic steps to create an Oracle server repository and use it to bring over a relational data source for use in OBI Answers. As of OBIEE version 10.1.3.3.2, Essbase is supported as an OBI data source allowing the user to integrate Essbase data with OBI Answers, OBI Interactive Dashboards, and OBI Publisher. The latest version of OBIEE is 10.1.3.4x. Refer to my part 2 blog (OBIEE and Essbase - Defining OLAP Integration) for importing Essbase content into OBIEE.
For the below steps, I will be using SQL Server 2005 relational database as my source but you can use Oracle 10g or older versions of either tool.
To create an Oracle repository, open the Oracle BI Administration Tool and select File | New and name your new repository.
Set security by selecting Manage | Security. Assuming you are the admin, select Users | Administrator. Right click Administrator, select Properties to enter an admin password and confirm the password. Close out Security Manager.
To import a relational data source, select File | Import | from Database.
Select a connection type.
Select the relational data source, enter login credentials and click OK.
Enter the relational table to import. Deselect all but Tables, Keys, and Foreign Keys and click Import. If you are using Views instead of Tables, change accordingly.
The relational table will import into the Physical layer of the Administration Tool.
Test the import by right clicking a column (Branch_Name is selected in this example) and select View Data. Values for the selected column should populate in a view data table.
Drag the imported relational table folder from the Physical layer to the Business Model and Mapping (BMM) layer. In the BMM layer, here you can create logical tables and joins to develop the type of model needed for presentation.
Next, drag the relational table from the BMM layer to the Presentation layer to finalize its presentation for the user. Manipulation by presentation can be performed in this layer for Subject Area use in OBI Answers.
For your reference and as a recap from my part 2, here is the significance of each layer:
|1. Physical layer - imported tables and views come from the relational data source; physical joins can be performed here|
|2. BMM layer - this layer organizes imports from the physical layer into logical categories|
|3. Presentation layer - BMM entities are organized for user presentation|
Once your Presentation layer is complete, it can be made available for OBI Answers to create dashboard content bringing both relational and multidimensional data sources into one view. A sample of an Answers view is displayed below combining both relational and multidimensional data sources into a combo box allowing for choice among regions. Any selection of region updates both relational and multidimensional tables for view.
This is just one simple example of the product's capabilities. OBIEE is redefining how we approach BI with the evolution of this product improving on how we develop it. There is a great deal of flexibility within OBIEE for relational and multidimensional reporting and those who understand how to leverage this tool will see its impact upon their organization for the better.