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.

113

2

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.

32

42

52

62

To import a relational data source, select File | Import | from Database.

71

Select a connection type.

81

Select the relational data source, enter login credentials and click OK.

94

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.

102

112

The relational table will import into the Physical layer of the Administration Tool.

122

132

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.

142

152

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.

162

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.

172

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.

182

192

202

212

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.

Advertisements

2 thoughts on “Creating an OBIEE Repository for Relational Data Source

  1. Pingback: Oracle OBIEE with SQL Server « Blog Home for MSSQLDUDE

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s