CrowdforGeeks | Build Skills with Online Courses from Top Institutions

OBIEE repository contains all metadata of the BI Server and is managed through the administration device. It is used to store statistics about the utility surroundings which include −

Data Modeling
Aggregate Navigation
Caching
Security
Connectivity Information
SQL Information

The BI Server can get right of entry to a couple of repositories. OBIEE Repository can be accessed the use of the following path −

BI_ORACLE_HOME/server/Repository -> Oracle 10g
ORACLE_INSTANCE/bifoundation/OracleBIServerComponent/coreapplication_obisn/-> Oracle 11g

OBIEE repository database is also called a RPD due to its document extension. The RPD document is password covered and you could only open or create RPD files using Oracle BI Administration tool. To deploy an OBIEE utility, the RPD record need to be uploaded to Oracle Enterprise Manager. After importing the RPD, the RPD password then have to be entered into Enterprise Manager.

Designing an OBIEE Repository the use of Administration Tool

It is a 3 layer method − beginning from Physical Layer (Schema Design), Business Model Layer, Presentation Layer.

Creating the Physical Layer

Following are the common steps involved in growing the Physical Layer −

Create physical joins between the Dimension and Fact tables.
Change the names in the bodily layer if required.

The physical layer of repository consists of information approximately the facts sources. To create the schema inside the physical layer you need to import metadata from databases and other records assets.

Note − Physical layer in OBIEE helps more than one facts sources in a unmarried repository - i.E. Statistics units from 2 one of a kind facts assets may be carried out in OBIEE.

Create a New Repository

Go to Start → Programs → Oracle Business Intelligence → BI Administration → Administration Tool → File → New Repository.

A new window will open → Enter the name of Repository → Location (It tells the default place of Repository directory) → to import metadata pick radio button → Enter Password → Click Next.

Select the connection type → Enter Data Source call and User call and password to connect to statistics source → Click Next.

Accept the meta kinds you need to import → You can pick Tables, Keys, Foreign Keys, System tables, Synonyms, Alias, Views, etc. → Click Next.

Once you click on Next, you will see Data Source view and Repository view. Expand the Schema name and select tables you need to add to Repository the usage of Import Selected button → Click Next.

Connection Pool window opens up → Click OK → Importing window → Finish to open the repository as proven inside the following image.

Expand the Data Source → Schema call to look the list of tables Imported in Physical Layer within the new Repository.

Verify Connection and Number of Rows in Tables Under Physical Layer

Go to gear → Update all rows counts → Once it is completed you may move the cursor on the table and additionally for person columns. To see Data of a desk, proper-click on on Table name → View Data.

Create Alias in Repository

It is really useful that you use table aliases often inside the Physical layer to do away with more joins. Right-click on on table name and choose New Object → Alias.

Once you create an Alias of a table it suggests up below the equal Physical Layer within the Repository.

Create Primary Keys and Joins in Repository Design

Physical Joins

When you create a repository in OBIEE gadget, bodily be part of is generally used inside the Physical layer. Physical joins assist to understand how two tables must be joined to every different. Physical joins are usually expressed with the usage of Equal operator.

You also can use a bodily join in BMM layer, but, it is very not often seen. The cause of using a physical be a part of in BMM layer is to override the physical join inside the physical layer. It lets in users to define more complicated joining common sense in comparison to bodily be a part of in the bodily layer so it works much like complicated be part of inside the physical layer. Therefore, if we are the use of a complicated be a part of in the bodily layer for making use of extra be part of conditions, there's no want to apply a physical be part of in BMM layer again.

In the above image, you could see a bodily join among table names − Products and Sales. Physical Join expression tells how the tables need to be joined with each other as shown in the image.

It is always advocated to apply a physical be a part of inside the physical layer and complex join in BMM layer as a lot as viable to keep Repository design easy. Only whilst there's an real want for a exclusive be part of, then use a physical be part of in BMM layer.

Now to enroll in tables whilst designing Repository, select all the tables in the Physical layer → Right-click on → Physical diagram → Selected objects simplest alternative or you can also use Physical Diagram button on the top.

Physical Diagram container as proven in the following picture seems with all of the desk names added. Select the brand new foreign key on the top and pick Dim and Fact desk to enroll in.

Foreign Key in Physical Layer

A Foreign key inside the physical layer is used to outline Primary key-Foreign key relation among tables. When you create it inside the physical diagram, you have to point first the dimension after which the truth table.

Note − When you import tables from schema into RPD Physical Layer, you may also pick KEY and FOREIGN KEY along with the table data, then the primary key-overseas key joins are routinely defined, but it isn't advocated from overall performance factor of view.

The desk you click first, it creates one-to-one or one-to-many dating that joins column in first desk with foreign key column inside the second desk → Click Ok. The join can be visible in Physical Diagram box between two tables. Once tables are joined, near the Physical diagram container using ‘X’ choice.

To store the brand new Repository go to File → Save or click the save button on the top.

Creating Business Model and Mapping Layer of a Repository

It defines the business or logical version of items and their mapping among commercial enterprise model and Schema in the physical layer. It simplifies the Physical Schema and maps the consumer business requirement to physical tables.

The Business Model and Mapping layer of OBIEE system administration device can incorporate one or more business model gadgets. A commercial enterprise version object defines the enterprise model definitions and the mappings from logical to physical tables for the business version.

Following are the steps to construct the Business Model and Mapping layer of a repository −

Create a commercial enterprise version
Examine logical joins
Examine logical columns
Examine logical table assets
Rename logical table objects manually
Rename logical table items the usage of the rename wizard and deleting needless logical objects
Creating measures (Aggregations)

Create a Business Model

Right-click on Business Model and Mapping Space → New Business Model.

Enter the name of Business Model → click on OK.

In the physical layer, select all of the tables/alias tables to be brought to Business Model and drag to Business Model. You also can upload tables one after the other. If you drag all the tables concurrently, it will maintain keys and joins between them.

Also be aware the distinction in icon of Dimension and Fact tables. Last table is Fact desk and pinnacle three are size tables.

Now proper-click on on Business model → pick Business Model diagram → Whole diagram → All tables are dragged simultaneously so it's going to keep all joins and keys. Now double click on on any be a part of to open the logical be a part of field.

Logical and Complex Joins in BMM

Joins on this layer are logical joins. It doesn’t show expressions and tells the type of join among tables. It enables Oracle BI server to recognize the relationships between the diverse portions of the enterprise version. When you send a question to Oracle BI server, the server determines how to construct physical queries with the aid of analyzing how the logical version is dependent.

Click Ok → Click ‘X’ to close the Business version diagram.

To observe logical columns and logical table sources, first enlarge the columns under tables in BMM. Logical columns have been created for every table while you dragged all tables from the physical layer. To take a look at logical desk resources → Expand the supply folder beneath each table and it points to the desk within the physical layer.

Double-click on the logical table source (no longer the logical desk) to open the logical table supply conversation box → General tab → rename the logical desk supply. Logical table to bodily table mapping is defined underneath "Map to those tables" alternative.

Next, Column mapping tab defines the logical column to bodily column mappings. If mappings aren't shown, take a look at the choice → Show mapped columns.

Complex Joins

There is not any specific express complex be a part of like in OBIEE 11g. It simplest exists in Oracle 10g.

Go to Manage → Joins → Actions → New → Complex Join.

When complicated joins are used inside the BMM layer, they act as placeholders. They allow the OBI Server to determine on that are the first-class joins among fact and dimension logical desk source to satisfy the request.

Rename Logical Objects Manually

To rename logical desk objects manually, click the column name below the Logical desk in BMM. You can also right-click on on column name and pick out alternative rename, to rename the item.

This is called manual method to rename objects.

Rename Objects Using the Rename Wizard

Go to Tools → Utilities → Rename Wizard → Execute to open the rename wizard.

In the Select Objects screen, click Business Model and Mapping. It will show Business Model call → Expand Business Model name → Expand logical tables.

Select all of the columns underneath the logical desk to rename the usage of the Shift key → Click Add. Similarly, add columns from all different logical Dim and Fact tables → click on Next.

It shows all logical columns/tables brought to wizard → Click Next to open Rules screen → Add rules from the list to rename like : A;; text decrease case and alternate every prevalence of ‘_’ to area as proven within the following photo.

Click Next → end. Now, in case you amplify Object names underneath logical tables in Business model and Objects inside the physical layer, items beneath BMM are renamed as required.

Delete Unnecessary Logical Objects

In the BMM layer, expand Logical tables → select gadgets to be deleted → right-click on → Delete → Yes.

Create Measures (Aggregations)

Double-click on at the column name in the logical Fact desk → Go to Aggregation tab and pick the Aggregate feature from the dropdown listing → Click OK.

Measures constitute records this is additive, such as general revenue or general quantity. Click on shop option at pinnacle to store the repository.

Creating the Presentation Layer of a Repository

Right-click on on Presentation place → New Subject Area → In the General tab enter the name of subject location (Recommended much like Business Model) → Click OK.

Once difficulty vicinity is created, right click on subject place → New presentation table → Enter the name of the presentation desk → Click OK (Add number of presentation tables identical to number of parameters required inside the record).

Now, to create columns beneath Presentation tables → Select the gadgets underneath logical tables in BMM and drag them to Presentation tables underneath situation region (Use Ctrl key to choose a couple of gadgets for dragging). Repeat the method and upload the logical columns to the remaining presentation tables.

Rename and Reorder Objects in Presentation Layer

You can rename the gadgets in Presentation tables by way of a double-click on on logical objects underneath problem location.

In General tab → Deselect the test container Use Logical column call → Edit the name field → Click OK.

Similarly, you could rename all of the objects within the Presentation layer without changing their name in BMM layer.

To order the columns in a desk, double-click on at the desk call underneath Presentation → Columns → Use up and down arrows to alternate the order → Click OK.

Similarly, you could alternate items order in all presentation tables beneath Presentation place. Go to File → Click Save to keep the Repository.

Check Consistency and Load the Repository for Query Analysis

Go to File → Check Global Consistency → You will get hold of the subsequent message → Click Yes.

Once you click OK → Business model beneath BMM will exchange to Green → Click save the repository without checking international consistency again.

Disable Caching

To improve question performance, it's miles advised to disable BI server cache option.

Open a browser and input the subsequent URL to open Fusion Middleware Control Enterprise Manager: http://<machine name>:7001/em

Enter the person name and password and click Login.

On the left side, increase Business Intelligence → coreapplication → Capacity Management tab → Performance.

Enable BI Server Cache phase is by way of default checked → Click Lock and Edit Configuration → Click Close.

Now deselect cache enabled choice → It is used to enhance query performance → Apply → Activate Changes → Completed Successfully.

Loading the Repository

Go to Deployment tab → Repository → Lock and Edit Configuration → Completed Successfully.

Click Upload BI Server Repository phase → Browse to open the Choose report conversation field → Select the Repository .Rpd report and click on on Open → Enter Repository password → Apply → Activate Changes.