Pass the Oracle Oracle Other Certification 1z0-591 Questions and answers with CertsForce

B: The problem with caching of any kind is how to deal with stale data. How you decide to deal with this depends on how time critical your business intelligence queries happen to be. OBIEE provides a wide variety of mechanisms for dealing with stale data. The cache can be purged automatically at intervals. Caching can be enabled selectively for individual physical tables, so that data in a frequently updated table is never cached. OBIEE provides a set of ODBC procedures that can be called when ETL processes complete to programmatically purge designated tables. But, by far the best way to selectively purge the cache is to use an event polling table. This table is populated by the client database whenever changes are made to any table (by an ETL process for a data warehouse or by a table-based trigger for an OLTP database). At specified intervals the BI Server polls this event polling table and deletes from its cache any query results that are dependent on the changed data.

Example (see step 4 and 5 below): Getting Top-N Sales Reps for Year and Year-1

1. Under the “Business Model and Mapping” Layer, right click the “F1 Revenue” and select “New Object” -> “Logical Column…”

2. In the “Logical Column” Window give for Name: Year Ago Revenue as seen below:

3. Goto the “Column Source” Tab and select “Derived from existing columns using an expression” radio button then click on the “Edit Expression” icon.

4. Enter the following expression in the “Expression Builder” Window by selecting “Functions” in the “Category:” list, “Time Series Functions” in the “Functions:” list then “Ago” “Time Series Functions:” section.

5. Select the “<>” in the “Ago( )” function, then select “Logical Tables” in the “Category:” list, “F1 Revenue” in the “Logical Tables:” list, then double click the “Revenue” in the “Columns:” list.

Etc.

A:

* Combining Relational and OLAP Data Sources: Oracle’s Common Enterprise Information Model allows users to combine data from a relational system and an OLAP source in a single calculation. For example, a user can compare sales forecasts from an Oracle CRM System with budget data from an Oracle Essbase planning application. To clients of the Common Enterprise Information Model, the forecast and budget data appear to be from the same logical source.

* The Essbase server provides advanced multi-user read and write capabilities, including data update and multi-user recalculation. Business users with front-end tools can write data back to a server and recalculate the data on a server using calculation scripts—key functionality to support sophisticated modeling and planning applications.

* Using aggregate storage, Essbase serves a wide range of analytic needs—financial analysis, planning, budgeting, sales analysis, marketing analysis, supply-chain analysis, and profitability analytics—all from a single analytic infrastructure.

Note 2: Data model for business intelligence

The most important component of business intelligence is the concept of 'data model'. data model determinde what kind of analysis that end user could do with the data. It is also an independant concept that could span across different vendor 's product.

Unlike data model of the transaction system, the typical analytic data model is often denormalized and store extra data for analytic query and better query performance while transaction data model use is often normalized and optimized for a few data read and write, which is implemented by jointing many tables.

The most common used analytic data model in business intelligence is call the 'Star schema' data model.

With the Oracle business intelligence system, We could define star schema and dimension data model in the 'logical layer' in the admin tools. The data model in OBIEE could span different data source in physical layer, which means OBIEE have the capability of building one logical data model which is actually connect to different database in the backend

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.

If security attributes conflict, a user or group is granted the least restrictive security attribute.

B: The 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.

C: The wizard then shows me a preview of the script it’s going to generate, and asks me if I want to create any more summaries. Here’s how the preview script looks – note that it’s not SQL, it’s a script language that the BI Server understands and uses as the specification for an aggregation job, which it then turns into separate CREATE TABLE, INSERT … SELECT and so on SQL statements, “optimized” for the particular back-end database platform.

Script example:

"ag_Items"

for "Seminar - Complete"."Items"("Quantity")

at levels ("Seminar - Complete"."Customerdim"."State", "Seminar - Complete"."Productdim"."Category", "Seminar - Complete"."Timedim"."Year")

using connection pool "custdw"."CUSTDW_Pool"

in "custdw".."CUSTDW";

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.

After creating your base metrics, try using the Calculation Wizard to automate the process of creating time-series metrics or comparison metrics such as: Change, Percent Change, Index, and Percent. The Wizard may save you many hours of time and frustration, especially if you have to create more than a handful of these metrics. In addition, the Wizard allows you to include customized logic to handle nulls in the base metrics. You may refer to the Oracle documentation for further detail.

Level-based Hierarchy

Level-based hierarchy is the first type of hierarchy in OBIEE.

Consists of an ordered set of two or more levels. For example, a Time hierarchy might have three levels for Year, Quarter, and Month. Level-based hierarchies can also contain parent-child relationships.

Dimension hierarchy levels allow :

* to perform aggregate navigation,

* to configure level-based measure calculations,

* users from Dashboard and Answers to drill down from one parent to a child level.

Special type of level-based dimension are supported:

* Unbalanced (or ragged) and Skip-level hierarchy

* time dimension to provides special functionality for modeling time series data.

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.