Oracle 1z0-591 Oracle Business Intelligence Foundation Suite 11g Essentials Exam Practice Test

Steps to Create a Dimension Hierarchy - >Create a dimension object.Add a parent-level object.Add child-level objects.Determine number of elements.Specify level columns.Create level keys.Create a level-based measure.Create additional level-based measures.Create share measures.Create rank measures.Add measures to Presentation layer.Test share and rank measures.

See BI Home Page screenshot below:

Note: The BI Home page contains the BI content catalog. Fusion Apps will use OBIEE web catalog to store all shipped reporting and analytics content.

From the BI home page users can browse and view the entire BI content. The BI catalog folder structure will match the Fusion Apps main menu structure. Folder structure would be content driven not technology driven which would enable ease of navigation for fusion Apps users.

Following content will be available under the BI catalog structure:

BI Publisher Reports (regardless of data source)

OTBI reports & dashboards

OBIA dashboards & scorecards

Financial Reporting

Also, users can do some additional actions from the BI Home page like: Access personal and shared reports, View/ edit BI publisher report schedules, Launch BI tools for advanced actions and create new reports with report wizard.

New reports created can be saved in BI catalog and added as embedded content within Fusion applications screens or links added to reporting pane. This enables users to extend the prebuilt embedded analytics within Fusion Applications.

This warning indicates that the given column is only mapped to logical table sources that are disabled. The warning brings this issue to the repository developer's attention in case the default behavior is not desired.

Denormalization is the process of intentionally backing away from normalization to improve performance by suppressing join and permitting the use of star transformation technique.

Denormalization should not be the first choice for improving performance and should only be used for fine tuning of a database for a particular application.

Note: A level-based measure is a column whose values are always calculated to a specific level of aggregation.

The calculation of this measure is independent of the query grain and used always the aggregation grain of the logical column.

Level-based measures allow :

* to return data at multiple levels of aggregation (the query grain and the level-based column grain) with one single query

* to create share measures (percentage), that are calculated by taking some measure and dividing it by a level-based measure. For example, you can divide salesperson revenue by regional revenue to calculate the share of the regional revenue each salesperson generates.

The Essbase cube looks just like any other relational star schema in my Presentation Layer, however when it is queried Oracle BI Server will issue MDX queries against a multi-dimensional data source, rather than an SQL query against a relational star schema.

Object-level security controls the visibility to business logical objects based on a user's role. You can set up object-level security for metadata repository objects, such as subject areas and presentation folders, and for Web objects, such as dashboards and dashboard pages, which are defined in the Presentation Catalog.

1) Business logic object

This controls access to objects, such as:

* subject areas

* presentation tables

* presentation columns

For example, users in a particular department can view only the subject areas that belong to their department.

2) Web object security

This provides security for objects stored in the Web Catalog, such as dashboards, dashboard pages, folders, and reports. You can view only the objects for which you are authorized. For example, a mid-level manager may not be granted access to a dashboard containing summary information for an entire department.

An action link is a link to an action that you have embedded in an analysis, dashboard page, scorecard objective, scorecard initiative, or KPI that, when clicked, runs an associated action.

There are static and dynamic repository variables.

Note:

Variables in the Oracle BI Server can be either repository or session variables, and are defined using the Variable Manager within the Oracle BI Administrator (Manage > Variables).

This brings up the Variable Manager, that divides variables out into repository and session variables, with a further subdivision into static and dynamic repository ones and system and non-system session ones. You can also define variables at the Presentation Server level, these are scoped to individual dashboards and are used amongst other things for setting dashboard prompt values and integrating the dashboard with BI Publisher parameters. Here’s a screenshot of a typical Variable Manager screen.

A data warehouse is a database used for reporting and data analysis. It is a central repository of data which is created by integrating data from multiple disparate sources. Data warehouses store current as well as historical data and are commonly used for creating trending reports for senior management reporting such as annual and quarterly comparisons.

Query environment can be managed by setting query limits (governors) in the Repository (RPD) for particular Application Roles. You should always set query limits for particular application roles rather than for individual users.

Steps:

1. Login to Repository using OBIEE Admin Tool

2. Navigate to Manage > Identity

3. Go to Application Role tab, choose the role and double click on it to open.

4. Click on Permissions tab

5. Set the Query Limits. You can limit queries by the number of rows received, by maximum run time, and by restricting to particular time periods. You can also allow or disallow direct database requests or the Populate privilege.

Horizonal Scaleout of System Components

* Used for adding additional managed servers (Java components) and

system components to an existing cluster

* Used for scalability and failover

* Set up via the Universal Installer > Scale Out BI System option

Content Tab

Aggregation Content

You can define the aggregation (ie the group by) of:

by hierarchy level

by logical column

It helps you to define an aggregate navigation

Note: logical table source

Logical table sources define the mappings from a single logical table to one or more physical tables. A logicaltable contains one or more logical table source. The mapping between physical columns and logical columns are done in this element.

The definition of the logical table source is used:

to specify transformations/calculations (in the column mapping tab),

to enable aggregate navigation and fragmentation (in the content tab)

The Content tab of the Logical Table Source is used to define :

any aggregate table content definitions (specify the grain of the physical tables),

fragmented table definitions for the source,

and WHERE clauses (if you want to limit the number of rows returned).

You do not use ODBC DSN to compare repositories.

Note: When an ODBC based application connects to a database, it connects to a logical name, the Data Source Name (DSN), which identifies the datasource to which it wants to connect.

Some external repository of configuration information holds the various DSN values that are available, together with the necessary configuration and control information needed by the ODBC driver to establish a connection and manage usage of the data source.

The DSN (data source name) is a data structure that contains the information about a specific data source (database, …), typically used by an ODBC driver to be able to connect to it.

The DSN contains information about the data source such as:

the name,

the directory,

and the driver.

On Windows, the DSN also specifies the ODBC driver to be used to access the database.

B: Before you can run Oracle Business Intelligence 11g Installer, you must first create

required Oracle Business Intelligence schemas in an unhardened database. You use

a tool called the Repository Creation Utility (RCU) to create these schemas with

the appropriate permissions and data.

E: The database 11gR2 is required if you want to run the Oracle Business Intelligence Enterprise Edition Plus Samples

One Oracle Database Preinstalled and not hardened to host the schemas.

Oracle Business Intelligence Office Plug-In is a Windows application that is an optional feature under the Oracle Business Intelligence Presentation Services. The Plug-In provides a way to browse the Analytics catalog, select a report, and then drop that report into Microsoft Word or Excel.

Denormalized data in the transactional system could be divided into different tables to increase performance.

Download and installation of the client

The client install executable is available for download from the More Products menu in Oracle BI Interactive Dashboards and Oracle BI Answers.

See picture below.

NQSConfig.ini is the main configuration file of Bi Server and contains a lot of parameters.

The parameters in the Query Result Cache Section provide configuration information for Oracle BI Server caching. The query cache is enabled by default. After deciding on a strategy for flushing outdated entries, you should configure the cache storage parameters in Fusion Middleware Control and in the NQSConfig.INI file.

Identity Store: It is directory server to perform the authentication. It contains user name , password and groups membership information .When a user name and password combination is entered at log in, the authentication provider searches the identity store to verify the credentials provided.

See figure below.

In OBIEE cache will be stored as files on the Hard Disk of the OBIEE Server in the form of files.

B: Example:

1. Configure the new source table to have the 'logical level' under the content set to 'Countries' (the summarized level)

b

2. Do the similiar setting for dimension table

Now when user query the data at 'Counties' level, the query will be redirected to these summary table instead of the more detail fact table

Note: Aggregate tables are Physical tables that store Aggregates of measures across multiple levels of a Hierarchy.

When you need to model many-to-many relationships between dimension tables and fact tables, you can create a bridge table that resides between the fact and the dimension tables. A bridge table stores multiple records corresponding to that dimension. In the Administration Tool, General tab in the Logical Table dialog box contains a check box that you can select to specify that a table is a bridge table.

A bridge table allows you to resolve many-to-many data relationships.

Reference; OBI Server Administration Server Guide, Version 10.1, Identifying Bridge Tables

The relationship is defined at the model and mapping layer.

Note:

At the highest level, an OBIEE Metadata Repository includes three layers of information:

1.

First, a Physical layer is defined. The metadata layer identifies the source data.

2.Second, a Business Model and Mapping layer is defined. This metadata layer organizes the physical layer into logical categories and records the appropriate metadata for access to the source data.

3.Finally, the Presentation layer is defined. This metadata layer exposes the business model entities for end-user access.

MapViewer is shipped as part of Oracle Fusion Middleware. Its main deliverable is a J2EE application that can be deployed to a J2EE container, such as that for Oracle Fusion Middleware. MapViewer includes the following main components:

* A core rendering engine (Java library) named SDOVIS that performs cartographic rendering. A servlet is provided to expose the rendering functions to Web applications.

* A suite of application programming interfaces (APIs) that allow programmable access to MapViewer features. These APIs include XML, Java, PL/SQL, and an AJAX-based JavaScript API.

* A graphical Map builder tool that enables you to create map symbols, define spatial data rendering rules, and create and edit MapViewer objects.

* Oracle Map, which includes map cache and FOI (feature of interest) servers that facilitate the development of interactive geospatial Web applications.

Oracle Maps can be configured now in the map viewer as a built in layer.

The Oracle BI Presentation Services Administration page provides the Manage Map Data link. This link displays the Manage Map Data page, where you can manage the logical and display versions of the data from various physical data sources. This defines the layers that content designers use when creating map views. The data that is available for managing maps and data is stored in Oracle Database as part of MapViewer.

Using this page, you provide:

* (A) Logical names to prevent any existing BI column mappings and map analyses from breaking because of changes to the physical data or to the data source.

* Display names so that the geographic data is meaningful to end users.

Note: Administering Maps Using Administration Pages

For (C) –layers, see 3-7 below.

For (D) –import images, see 11-12 below.

To administer maps using Administration pages:

Sign in to Oracle Business Intelligence.

In the global header, click Administration.

Click the Manage Map Data link to display the Manage Map Data page.

Click the Layers tab.

Click the Import Layers button to display the Import Layers dialog.

In the dialog, select the connection and the layers that are needed for zooming and drilling. (This tab is not prepopulated by other selections on the Administration pages. You can use any layers or images with any map.)

Click OK when you have finished selecting layers that are appropriate for the subject area with which you are working.

Back on the Layers tab, select a layer, then click the Edit Layer button to display the Edit Layer dialog in which you associate layers with attribute columns so that you can display BI data in the map view.

Click OK when you have finished editing the layer.

You use this tab to associate layers with BI data. If you use the City column in multiple subject areas, then you must associate it with a layer for each subject area.

Click the Background Maps tab, then click the Import Background Maps button to display the Import Background Maps dialog.

In the dialog, select the connection and the main maps to use.

The connection that you select for the main map can be different from the connection for the layers or images.

Click OK when you have finished selecting main maps.

Back on the Background Maps tab, select a map, then click the Edit Background Map button to display the Edit Background Map dialog in which you name the map and specify the order of layers and their zoom levels.

Click OK when you have finished editing the map.

Optionally, click the Images tab, then click the Import Images button to display the Import Images dialog. You can import images if you plan to use them as a format on maps.

In the dialog, select the connection and the images to use.

Click OK when you have finished selecting images.

Click Back when you have finished working with the Administration page. Your changes are saved automatically.

Creating Logical Time Dimensions

Compared to modeling an ordinary dimension, the time dimension requires just two additional steps: selecting the Time option in the Logical Dimension dialog, and designating a chronological key for every level of every dimension hierarchy.

Note: Time dimensions and chronological keys. You can identify a dimension as a time dimension. At least one level of a time dimension must have a chronological key. The following is a list of some guidelines you should use when setting up and using time dimensions:

* At least one level of a time dimension must have a chronological key.

* All time series measures using the AGO, TODATE, and PERIODROLLING functions must be on time levels. AGO, TODATE, and PERIODROLLING aggregates are created as derived logical columns.

In online mode, you connect to a catalog on a running web server. In this mode your permissions are applied, you can select a locale, and you can see the effects of any localization on the catalog. You can see only those objects for which you have the appropriate permissions. Both Presentation Services and the web server must be running for you to open catalogs in online mode.

Use online mode when you want to make minor incremental changes or additions to the catalog, such as changes to permissions, updates to a single object, or migration of new objects to a production environment.

Note:

* You can open a catalog in one of two modes — online or offline. Both modes can operate against an actual production catalog, with no need for any downtime.

* Repository changes can be done in either online

or offline mode. Online mode is when BI Server

is actively reading the repository file (when a

user is logged in). Offline is when BI Server is

idle. Best practice is to develop in Offline mode.

Minor changes can be made when in online

mode.

Security Manager – is used to setup users, groups, synchronize LDAP users and Groups, set access rights for Objects (tables, columns) and to control over when and what data a user can access.

Note: When you open the administration tool, you can reach the security manager by the Menu :

Manage > Security

You can specify a Driving table parameter in a complex join.

Driving tables are for use in optimizing the manner in which the Oracle BI Server processes cross-database joins when one table is very small and the other table is very large.

The BI Server will query the driving table (the small one) and will get a list of values. The BI Server will then generate a second SQL using a parameterized IN list with the values from the first query.

Specifying driving tables leads to query optimization only when the number of rows being selected from the driving table is much smaller than the number of rows in the table to which it is being joined.

There are two types of session variables:

System — A session variable that the Oracle BI Server and Oracle BI Presentation Services use for specific purposes.

System session variables have reserved names that cannot be used for other kinds of variables (such as static or dynamic repository variables and non-system session variables).

Non-system — A system variable that the administrator creates and names. For example, the administrator might create a SalesRegion non-system variable that initializes the name of a user's sales region.

The administrator creates non-system session variables using the Oracle BI Administration Tool.

Note: Session Variables

A session variable is a variable that is initialized at login time for each user. When a user begins a session, the Oracle BI Server creates a new instance of a session variable and initializes it.

There are as many instances of a session variable as there are active sessions on the Oracle BI Server. Each instance of a session variable could be initialized to a different value.

Note: This source should be combined with other sources at this level (check box)

Description: Check this box when data sources at the same level of aggregation do not contain overlapping information. In this situation, all sources must be combined to get a complete picture of information at this level of aggregation.

Note 2: To use a source correctly, the Analytics Server has to know what each source contains in terms of the business model. Therefore, you need to define aggregation content for each logical table source of a fact table. The aggregation content rule defines at what level of granularity the data is stored in this fact table.

Use the Content tab of the Logical Table Source dialog box to define any aggregate table content definitions, fragmented table definitions for the source, and Where clauses (if you want to limit the number of rows returned).

BSO is preferred to ASO in the following scenarios:

* Top-down planning and allocation (Sales Forecasting)

* Cost Allocations across products and customers (Profitability Analysis)

* Currency conversion, inter-company eliminations(Financial consolidations)