From the Start menu, select Programs > Oracle Business Intelligence > Administration. The Oracle BI Administration Tool appears.

Click the Open Online ()icon. The Open Online AnalyticsWeb dialog box appears.

You can open a BI Repository for editing in either online or offline mode. You use online mode to view and modify a BI Repository while it is loaded into the Oracle BI Server. Online mode allows you to perform tasks such as managing scheduled jobs, user sessions, and so on.

Enter the password and click Open. The BI Repository layers appear.

Note: The initial default installation password is Administrator. Verify the password with your administrator.

Select File > Import > from Multi-dimensional.

The Import from Multi-dimensional dialog box appears.

Enter the following information:

Note: The Username and Password fields are assigned by your administrator.

Click OK. The Select Source dialog box appears.

A. Expand the localhost tree. Select the Demo database and click Import.

A localhost object is added to the Physical layer.

B. Select the ASOsamp database and click Import. Click Close in the Select Source dialog box.

Note: Within the Physical layer of the BI Repository, the Physical Database corresponds to an Essbase Server instance; the Physical Folder corresponds to an Essbase Application; and the Physical Cube corresponds to an Essbase cube.

A. Expand the localhost tree.

B. Double-click Connection Pool. The Connection Pool dialog box appears.

When you import an external multidimensional data source into the BI Repository, the Connection Pool is automatically set up in the Physical layer. You can add a connection pool manually as well. Click Cancel.

A. Click the plus sign (+) to expand the Demo database and the Basic cube.

Right-click the Basic physical cube table and select Properties.

The Physical Cube Table - Basic dialog box appears. A physical cube table type is automatically set to Physical Table.

B. Click the Hierarchies tab. This tab displays all the imported hierarchies. Leave this dialog box open.

The hierarchy dialog box lists all the defined levels for the selected hierarchy. The image below depicts an example of these levels for a modified Market hierarchy. The highest level for the hierarchy should be the first item in the list. Also note that this is a fully balanced hierarchy and the member type is appropriately set to ALL.

Note: If the dimension is known to be unbalanced, you should change Hierarchy Type to "Unbalanced."

If the hierarchy contains duplicate or shared members, you should deselect the "Use unqualified member name for better performance" check box.

Note: When you import an Essbase cube into the BI Repository, all levels will have names such as Gen1, Gen2, and so forth. To override the autogeneration of dimension level names and preclude having to manually change them in the Physical layer, rename the dimension levels, using Essbase Administration Services before import.

The graphic below indicates an example of this process for the Market dimension. From within Oracle Essbase Administration Services, you right-click the specific dimension, and then select Generations. In the Define Generations dialog box, you name each generation, and then click OK.

As mentioned in the introduction to this topic, measures by default are imported as a flat list versus a hierarchy. If the information that you need to present is more sophisticated, you need to rebuild the Measures dimension.

This image is a sample report with flat measures that was built in Answers.

This image depicts the same measures as an alternate measure dimension, but displays the data in a hierarchical, pivot-style where Gen 2, Accounts have been drilled on.

A. Double-click the Basic database. Click the Hierarchy tab. Double-click the Accounts hierarchy. The Hierarchy - Accounts dialog box appears.

B. Select Other from the Dimension Type drop-down list.

Click OK.

Oracle BI EE requires a measures dimension. In this tutorial, the Scenario dimension is used to satisfy this requirement.

A. Double-click the Scenario dimension. The Hierarchy - Scenario dialog box appears.

B. Select Measure Dimension from the Dimension Type drop-down list. Click OK twice.

A. Select all flattened measures as indicated in the following image.
Hint: To select multiple objects, press and hold Ctrl and click each object. Select Edit > Delete and confirm the delete by clicking Yes when prompted.

The Oracle Essbase Administration Services outline (outline) for the Demo Basic cube displays the three measures that you need to add to the BI Repository.

B. Right-click Basic and select New Object > Physical Cube Column from the shortcut menu.

The Physical Cube Column dialog box appears.

Enter the following information:

The dialog box should look like the following image:

Click OK. Add the Budget and Variance measures in the same manner. The following image shows the Physical layer after you add the three measures:

The Measures dimension is now complete.

Because an Essbase cube corresponds to your business model, creating the Business Model and Mapping layer is simplistic because the metadata is implied by the structure of the Essbase cube.

Drag the Demo physical model to the Business Model and Mapping layer. The business model and all its objects are created for you, including dimension hierarchies, logical dimension tables, logical fact tables, logical columns, logical joins, and so forth.

Drag the ASOsamp physical model to the Business Model and Mapping layer.

Create the Presentation layer. Drag the Demo business model to the Presentation layer.

Drag the ASOsamp physical model to the Presentation layer.

Save your changes. Select File > Save and click OK when prompted to check in your changes. You should also receive a message prompting you to check for global consistency.

BI Repository metadata must pass a consistency check before you can make the BI Repository available for queries. The Consistency Check Manager allows you to enable and disable rules for consistency checks, navigate to and fix inconsistent objects, and limit the consistency check to specific objects. Click Yes.

Note: When you make a change to the repository, the Check In () icon is enabled on the toolbar. After you have completed a task, you can verify the consistency by clicking the Check In icon and performing the consistency check.

You should receive a message stating that the business model is consistent along with a prompt to mark the model available for queries. Click Yes.

Respond similarly for the ASOsamp business model.

The Consistency Check Manager dialog box appears. If you receive errors or warnings, you can use this dialog box as a guide to correct the errors.

Click Close. The BI Repository is now ready to be used for queries and should look like this:

The following image shows the outline for the ASOsamp.Sample cube—specifically, the Stores dimension. The Stores dimension has two associated attribute dimensions: Square Footage and Store Manager.

When imported into the BI Repository, these dimensions are flattened. For reporting purposes, you want to associate these hierarchies to the Stores hierarchy, which will allow you to seamlessly drill down through the levels.

Begin by expanding the ASOsamp subject area within the Business Model and Mapping layer, then expand the Stores dimension as indicated in the image below:

Double-click the Store object.

The Store Logical Level dialog box appears. (If you receive a read-only message, click Yes to check out the object.)

Click the Preferred Drill Path tab, and then click Add.

The Browse dialog box appears.

Expand the ASOsamp subject area and the Square Footage dimension.

Click Gen1, Square Footage, and then click Select. (If the Check out objects dialog box appears, click Yes.)

The Store Logical Level dialog box reappears with the selected drill path specified.

Click Add again and expand the Store Manager dimension. Click Gen1, Store Manager and click Select. (If the Check out objects dialog box appears, click Yes.)

The Logical Level - Store dialog box reappears with the selected drill path specified.

Click OK. The drill paths are added.

When you sort a column in Answers, Answers returns dimension members in alphabetic (or numeric) order. However, Essbase users, such as individuals in the financial arena, may prefer to see dimension members in the order that they are physically stored in the outline.

You can specify an alternate sort order in the Business Model and Mapping Layer, using the EVALUATE function.

Expand the Demo subject area in the Business Model and Mapping layer and select Accounts.

Right-click and select New Object > Logical Column from the shortcut menu.

The Logical Column dialog box appears.

A. Enter Sort Order in the Name field and click OK.

The column is added to the Accounts dimension.

Expand the Sources folder.

Double-click the Basic source object. The Logical Table Source - Basic dialog box appears.

On the Column Mapping tab, select the Show unmapped columns check box. The Sort Order column appears.

Click the Expression Builder () icon. The Expression Builder dialog box appears.

In the Expression Builder pane, enter the following code:

EVALUATE('Rank( %1.dimension.currentmember,%2.members)' AS INTEGER , "localhost"."Demo".""."Basic"."Gen4,Accounts",
"localhost"."Demo".""."Basic"."Gen4,Accounts")

Note: "Localhost" is the name of the server for this tutorial. Click this link for additional information.

Hint: You can use the objects and buttons at the bottom of the Expression Builder pane to build this expression. Additionally, in the tutorial, you can copy and paste the above code into the pane.

Click OK. The Basic Logical Table Source dialog box reappears with the Sort Order column expression specified.

Click OK.

5.

Next, you must associate the Sort Order column with the columns that you want to sort. Double-click Gen4,Accounts.

On the General tab, click Set to establish the sort order.

The Browse dialog box appears.

Select the Sort Order column and click OK.

The Sort order column is associated to Gen4,Accounts and will display appropriately when used in Answers to run requests.

Click OK again.

Note: At this point, you might want to check in your changes to the repository. Click the Check In () icon and check for global consistency. Close Consistency Check Manager.

Click File > Exit to close the Administration Tool. If prompted to save your changes, click Yes.

A. From the Start menu, select Programs > Oracle Business Intelligence > Presentation Services. The Oracle Business Intelligence Log In window appears.

B. Enter Administrator in the User ID and Password fields and click Log In. The My Dashboard page appears.

C. Click the Answers link. The Answers Start page appears.

The Answers Start page is composed of two panes: Selection and Workspace.

The Selection pane on the left contains the Catalog and Dashboard tabs from which you select items to manipulate.

The Presentation Catalog (Catalog) stores the content created with Answers and Interactive Dashboards. The content can be organized into folders that are either shared or personal. Types of content that can be stored in the Catalog include requests created with Answers, HTML content, and links to other images, documents, and sites.

A dashboard is made up of sections of information that can contain items, such as results from Answers requests, external Web content, HTML text, graphics, links to other sites, embedded objects such as requests, and so on. Dashboard content is organized into pages. Pages appear as tabs across the top of the screen in Interactive Dashboards.

The Workspace pane on the right contains a list of the actions you can perform and the subject areas that are available to you. Oracle BI presents data in subject areas. A subject area contains columns that represent information about the areas of your organization’s business, or about groups of users within your organization. Subject areas have names that correspond to the types of information they contain, for example, Marketing Contracts, Service Requests, and Orders. Columns also have names that indicate the types of information they contain, such as Account and Contact.

The subject areas that you see in the image are the same ones represented in the Administration Tool.

The "feature links" listed at the top of the Workspace pane provide access to other Oracle BI functions.

Click the Demo subject area.

The Answers workspace page appears.

The Answers workspace displays the following tabs for working with a request:

	Criteria: Provides access to the columns selected for the request, and buttons to access the most common view types
	Results: Allows you to work with the results of the request
	Prompts: Allows you to create prompts to filter the request
	Advanced: Allows advanced users to work with the XML and logical SQL for the request

Each tab contains on-screen information and buttons to help you create, access, and manage requests.

The next few steps show you how to create a query that provides product family information for accounts, indicating the actual versus budget expenses.

A. Click the Plus Sign () icon next to the Basic, Product, and Accounts folders.

B. Click Family in the Products folder to add it to the Criteria workspace.

C. Click Gen4,Accounts in the Accounts folder to add it to the Criteria workspace. Gen4,Accounts is added to the right of Family.

D. Click Actual and Budget in the Basic folder to add both to the Criteria workspace.

Your workspace should look like the following image:

Click Display Results. Your query appears.

By default, the query appears in the Compound Layout format, that is, a Title and a Table.

A. Click the Edit View () icon in the Title pane. In the Title text entry box, enter Product Expenses - Actual vs. Budget.

B. Deselect the check box for Display Saved Name. Click OK. Your title is added to the report.

Now add a chart to your report. By default, a chart is displayed as a Vertical Bar graph.

A. Select Chart in the View menu.

The Chart View appears.

B. Select Line Bar Combo from the Graph drop-down list. Your Chart View should look like this:

Add the Chart View to your layout.

A. Select Compound Layout from the View drop-down menu.

B. Click the Add View link and select Chart to add the Chart View to the Compound Layout View.

The Chart View is added to the query layout.

Save your report.

A. Click the Save () icon. The Save Request dialog box appears.

In the Name field, enter Budget_vs_Actual.

B. Click OK.

In this practice, you use the ASOsamp subject area to build a pivot table query.

Click the Answers link.

Click the ASOsamp subject area link.

The Answers workspace page appears.

The next few steps show you how to create a query that provides the regional store manager return information across the current three quarters.

A. Click the Plus () icon next to the Geography, Time, Stores, Store Manager, and Sample folders.

B. Click Region in the Geography folder to add it to the Criteria workspace.

C. Click Quarters in the Time folder to add it to the Criteria workspace.

D. Click Gen2, Store Manager in the Store Manager folder to add it to the Criteria workspace.

E. Click Store Type in the Stores folder to add it to the Criteria workspace.

F. Click Returns in the Sample folder to add it to the Criteria workspace. Your workspace should look like the following image:

Click the Results tab and select Pivot Table from the View menu.

This opens the Pivot Table Layout page.

Examine the default pivot table that is created and displayed below the layout controls. Measure labels for the measures in your query appear in blue as columns in the pivot table. Row headings for the dimensional attributes in your query are displayed in gray as rows by default. The measures are displayed at the intersection of the rows and columns. As you modify the members in the pivot table, the changes are reflected in the Display Results area.

Drag Region to the Pages area. When you see a blue line appear around the control, you have a valid insertion point and can release the column.

A. Drag Store Type to the Pages area and to the right of Region and release it when the insertion point line appears. You can constantly monitor your progress by using the Display Results area, or you can deselect the check box for faster performance.

B. Drag Quarters beneath the Measure Labels control and release when a valid insertion point line (as indicated in the image) appears.

Your pivot table should look like the following image:

Examine the other regions. From the Region - Store Type drop-down list, select South - Brick & Mortar.

Note: If you deselected the check box for Display Results, you must select it now.

Your query should look like this image:

Add the Pivot Table View.

A. Click the Combine individual views and arrange () icon.

B. Click the Add View link and select Pivot Table from the View menu.

C. The Pivot Table View is added to the bottom of the request. The Title and Table Views are not needed for this request. Click the Delete () icon for each view.

Save your report.

A. Click the Save () icon. The Save Request dialog box appears. In the Name field, enter Quarterly Regional Store Manager Returns.

B. Click OK.

Click the Dashboards link to add your queries to the My Dashboard page.

Click Page Options > Edit Dashboard.

The Dashboard Editor window appears.

Drag the Budget_vs_Actual request (located in My Folders) from the pane on the left to the gray workspace area on the right. This gray area is actually a "Section" placeholder.

Your request is added to Section 1.

Click the Add Column () icon. A new column is added to the right of Section 1.

A. Drag the Quarterly Regional Store Manager Returns request to the new section. Your workspace should look like this:

B. Click Save. The My Dashboard page appears with your two reports added.