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

BI EE uses the ADM driver to extract the data from HFM.

Note: HFM (Hyperion Financial Management)

Example:

I EE connects to HFM through JavaHost. So, the setup for configuring the connectivity is to basically make the EPM environment variables available for access to BI EE. Before making the changes, there are a couple of things to be aware of

1. Ensure that DCOM is enabled on the BI Server machine. This is needed for HFM client to work/connect properly with HFM Server.

2. Ensure that HFM Client is installed.

The above assumes EPM 11.1.2.x version. If you are on Hyperion 9.3.3, then you need to have the ADM drivers installed manually. All of this is documented clearly. So, rather than re-iterating that i will just point to the link here. Ensure that you follow all the steps mentioned in the docs else you will have issues in the connectivity. Once all the pre-requisite steps are done, we start off with the metadata import process.

Note: The Administration tool is used to manage OBIEE server and have some capabilities to automate some repetitive tasks and principally his embedded tools.

Oracle Business Intelligence Administration Tool is available only for Windows operating systems.

The Administration Tool is a Windows application that allows the BI Server administrator to create and edit repositories.

Repositories can be edited with the administration tool in either online or offline mode.

The Administration Tool also includes a session manager and a cache manager which are available in online mode. The Cache Manager displays what queries have been cached and how frequently they have been accessed. You can also select and delete items

Initialization blocks are used to initialize dynamic repository variables, system session variables, and nonsystem session variables.

CurrentDay and CurrentMonth can be calculated by a sql statement.

Note:

REPOSITORY VARIABLE(DYNAMIC)

· Initialized when BI server is started

· Assigned value dynamically based on the result of a query

· Value is dependent of the sql provided in Initialization Block

Create a dynamic variable and provide a Name and Default Initializer.Create a new initialization block by clicking on the New button in the above screen.

Provide Intialization Block variable Name

The principal connection to an OBI Server inside the Oracle BI System is made through an Odbc Connection.

OBI Server can handle multiple repository but you can connect to only one at the same time by DSN Odbc Connection. Therefore, you must set up an ODBC connection for each repository.

In a standard OBIEE implementation, we generally want to purge and re-build the cache after every ETL (extract, transform, and load) run.

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.

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.

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.

Dynamic Repository Variables:

You initialize dynamic repository variables in the same way as static variables, but the values are refreshed by data returned from queries. When defining a dynamic repository variable, you will create an initialization block or use a pre-existing one that contains a SQL query. You will also set up a schedule that the Oracle BI Server will follow to execute the query and periodically refresh the value of the variable.

Note: The value of a static repository value is initialized in the Variable dialog box. This value persists, and does not change until an Oracle BI Administrator decides to change it.

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.

Ragged Hierarchies are hierarchies where some routes down the hierarchy are longer than others; for example, you may have an organizational hierarchy for salespeople where the overall boss has two assistant managers, one of them works on their own and another has three sales managers under them; some of those sales manager may have sales staff under them, and some sales staff may have trainees under them.

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.

See figure below.

If you want to create a dynamic time-series reference such as the value of a measure as at this time last year, or over the past three months, or year-to-date, you can use one of the OBIEE time-series functions (AGO, PERIODROLLING, TODATE) to provide an offset value for a measure.

Figure:

Note: OBIEE 11g offered a bunch of improvements that were particularly aimed at Essbase, and other OLAP server, customers; the new hierarchical columns feature brought proper OLAP analysis to OBIEE, and coupled with OLAP-oriented features such as hierarchical prompts and selection steps, made OBIEE 11g a much more conducive environment for Essbase users.

There’s much better support for Essbase-native features such as value-based hierarchies, alternate hierarchies, multiple alias tables, UDAs and other Essbase artifacts, and the value-based hierarchy feature in particular is very welcome as it can avoid the need to completely re-import your Essbase database if your outline gains additional hierarchy levels.

So coupled with the new and improved visualization capabilities in OBIEE 11g, including maps, scorecards, improved dashboard controls, the whole new UI, improved BI Publisher and so on, there’s a lot to recommend OBIEE 11g to users of the “legacy” Hyperion tools looking for a bit of a refresh of their BI environment.

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.

Steps for Creating Aggregate Tables in OBIEE

1. Create all Dimension Tables, Fact Tables & Hierarchies, which are required to be aggregated.

2. Go to ToolsàUtilities (from Administration tool menu bar), a dialog box will pop up with all available utilities.

3. Select Aggregate Persistence Wizard, and then click Execute Button.

4. Now Select appropriate path where you need to generate Aggregate Table SQL.

Note : Click Generate DDL file for first time generation of Aggregate Table

Click Next, to move to next page (Select Business Model & Measure Page)

In Select Business Model & Measure Page, Select Appropriate Business Model & then select associated Fact / Measure.

Etc.

A single query can be performed accessing data from both tables through a join on the columns in the foreign key relation.

Initialization blocks are used to initialize dynamic repository variables, system session variables, and nonsystem session variables.

OBIEE - Grain (Level of summarization)

Grain definition in the context of OBIEE.

The following list describes the different grains in navigating a query:

* Aggregation grain. The grain of the aggregate source of from a level based metrics

* Query grain. The grain of the request.

* Time Series grain. The grain at which the time series function aggregation is requested.

* Storage grain. The query in the example can be computed from daily sales or from monthly sales, or from quarterly sales.

Note:

The aggregation grain is defined in the content tab of the logical table source.

When the query grain match the aggregation grain of a logical table source, this one is used to create the physical query.

Pull the table and column definition into the physical layer

Use the OBIEE “import metadata” wizard to pull the table and column definition into

the physical layer. The wizard is used so that all of the column definitions do not need to

be manually added into the physical layer.

Figure:

Note: The physical layer is the layer of the Logical Business Model where you define the physical data model of your data source. It contains information about the physical data sources.

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.

The rank measure is numbered from 1. The highest sales amount will be ranked 1.

Open up the Administration tool, go to Manage then variables.

See screenshot below.

Then follow this picture to create a new Initialization Block.

Name your Initialization block init_block_test.

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 1: There are four types of variables that you can use:

Session

Repository

Presentation

Request

Note 2: 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.

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.

All metadata for cubes and dimensions is stored in the Oracle database and revealed in the data dictionary views, so that you can query the entire business model in SQL.

The Catalog Manager workspace enables you to view and work with catalog objects. It displays the following folders for an open catalog:

The shared folder— Contains content that is shared among catalog users. This includes the preconfigured dashboards and analyses that are distributed with prebuilt applications, and other objects such as shared filters.

The system folder — Contains administrative elements of Presentation Services. Some of these elements are distributed with the product, and others are configured by you as the administrator, such as privileges. Avoid modifying any files in this folder. Presentation Services uses these files internally and modifying them might cause unexpected results.

The users folder — Contains content that catalog users with the appropriate permissions have saved to their personal folders, such as individual analyses.

Note 1: OBIEE offers two calculation types for measure in the repository:

logical

physical

You can define calculations in either of the following ways:

* Before the aggregation, in the logical table source.

* After the aggregation, in a logical column derived from two other logical columns. For example: sum(col A) * sum(col B)

Note 2: A measure column is a column unlike the baseline column that has a default aggregation rule defined in the Aggregation tab of the Logical Column dialog in the repository.

Measure columns always calculate the aggregation with which they are defined.

Measures need to be defined in a logical fact. Any column with an aggregation rule is a measure.

B: Click Refresh All from the Oracle BI menu in PowerPoint to refresh all the refreshable objects in the presentation.

C: Right-click Table and select Insert as List from the shortcut menu. The Edit Prompts and Levels dialog box appears. Accept the default options and click Insert.

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.