1z0-591 Oracle Business Intelligence Foundation Suite 11g Essentials Questions and Answers

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

Click on “Edit Data Source” button and provide the sql to be used for the variable, e.g.

select lower(sys_context('USERENV','SESSION_USER'))||

'@'||

lower(sys_context('USERENV','DB_NAME'))

from dual;

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.

You should use a logical column (not physical). In fact,in the Explanation of the exam, it displays a logical column.

You have three sort of fragmentation:

Fact-based / Dimension-based fragmentation

Content-based / Value-based fragmentation

Level-based fragmentation / aggregate navigation

You can combine the techniques above to construct powerful design that are known as Complex partitioning / fragmentation. When you fragment a dimension, you are automatically in a complex fragmentation.

In a Star Schema the dimensional tables does not need to be normalized.

A Snowflake Schema is a modified Star Schema that has normalized the dimensional tables.

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.

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.

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.

Aggregate Persistence Wizard automates the creation and initial population of aggregates, persists them in a back-end database and configures the BI Server metadata layer so that they’re used when appropriate.

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

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.

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.

Purge and reseed once a week after the reports have run to improve report response time.

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.

It is a best practice to create aliases for all tables. Aliases are created in the Physical Layer of the repository. Some of the benefits of using aliases are given below:

(A) * An alias is used to avoid circular joins and to configure self joins.

* You can create a join relationship which is different than the actual foreign key relationship in the database. This helps in defining relationships to meet business model criteria.

* Aliases can function in numerous ways to define business model e.g. a table can act both as a dimension and a fact using aliases.

(B) * You can rename aliases to make physical and business model easier to understand.

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.

The Oracle BI repository development process adheres to the classic Software Configuration Management (SCM)process, which utilizes a three-way merge to manage concurrent development.

The merge process involves three repositories:

The (Original|Received|Master|Parent) repository. The repository that you received before changed. You must keep it preciously unchanged.

The Modified repository. The repository that contains the customizations you made to the original repository.

The Current repository. Generally, the repository that is in production and where some customisations could have been performed by other people.

In a multiuser environment (MUDE), Developers check out the file and make changes locally. Then, these changes are automatically reconciled and merged into the master repository.

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.

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.

Priority group

You can set priority group numbers to determine which logical table source should be used for queries for which there is more than one logical table source that can satisfy the requested set of columns.

For example, you might have user queries that can be fulfilled by both a data warehouse and an OLTP source. Often, access to an operational system is “expensive,” while access to a data warehouse is “cheap.” In this situation, you can assign a higher priority to the data warehouse to ensure that all queries are fulfilled by the data warehouse if possible.

Note that the priority group of a given logical table source does not always ensure that a particular query will be fulfilled by that source.

Priority Group Designation

0 the highest-priority source (the default)

1 a lowest-priority source

Security Realms are an Oracle Platform Security Services concept and bring together sets of users, groups, providers and so on for a WebLogic domain.

--------

In an Ibor or Agent, in the 'Destinations' tab you can set the 'Specific Devices' However, it is a Device or Delivery Profile the one used for alerts, see this OBIEE guide:

---------------x--------------

User's Guide

https://download.oracle.com/docs/cd/E21764_01/bi.1111/e10544/toc.htm

https://download.oracle.com/docs/cd/E21764_01/bi.1111/e10544.pdf

page 218

What Are Devices and Delivery Profiles?

Devices and delivery profiles control how you will be reached when an alert is

generated by an agent and which devices receive the content:

¦ Device — A device is the medium used to deliver content to you. The content of

an agent can be delivered to a range of devices as an e-mail or SMS message.

¦ Delivery profile — A delivery profile specifies which devices to use to deliver

content to you, based on the priority of the content. You can define several

delivery profiles to meet your needs, and switch

---------------x--------------

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: Much of the configuration of OBI that used to be done in the NQSConfig.ini files can now be done using this front end console, which in turns updates the NQSConfig.ini file. Things in the NQSConfig.ini file that are set by EM are noted in the file with '# This Configuration setting is managed by Oracle Business Intelligence Enterprise Manager'. Some things that needs to be setup manually in the NQSConfig.ini file are:

1. Usage Tracking

2. Dynamic Libraries

3. Query Optimization Flags

4. MDX Member Name Cache Section (SAP BW)

5. More specific General Settings where EM doesn't contain the level of detail required

D: Cache Management Techniques include

• Inspecting the cache reports

• Configuring the cache parameters

• Setting caching and cache persistence for tables

• Using the Cache Manager

• Inspecting SQL for cache entries

• Modifying the Cache Manager column display

• Purging the cache entries manually using the Cache Manager

• Purging the cache entries automatically

• Using event polling tables

• Seeding the cache

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.

BIEE 11g Install High level Steps :

Software Required

1. Database (Oracle, IBM DB2 or MS-SQL)

2. Repository Creation Utility (RCU)

3. OBIEE 11g (11.1.1.3 is latest OBIEE as of 30 April 2011)

4. WebLogic 10.3.3 (Optional – Only required for 64 bit machine or IBM AIX)

5. JDK 1.6 64 bit (Optional – Only required for 64 bit machine)

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.

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 Software Only Install type installs the Oracle Business Intelligence software binary

files in an Oracle home for later configuration as part of a Fusion Middleware

deployment.