Data Warehousing 11g Essentials v8.3 (1z0-515)

Answer : A

Explanation: Conventional Extract, Transform, Load (ETL) tools closely intermix data transformation rules with integration process procedures, requiring the development of both data transformations and data flow. Oracle Data Integrator (ODI) takes a different approach to integration by clearly separating the declarative rules (the what) from the actual implementation (the how). With ODI, declarative rules describing mappings and transformations are defined graphically, through a drag-and-drop interface, and stored independently from the implementation. ODI automatically generates the data flow, which can be fine-tuned if required. This innovative approach for declarative design has also been applied to ODI's framework for Changed Data Capture. ODIs CDC moves only changed data to the target systems and can be integrated with Oracle GoldenGate, thereby enabling the kind of real time integration that businesses require.
Reference: Best Practices for Real-time Data Warehousing, Oracle White Paper

Answer : C

Explanation: Association is a data mining function that discovers the probability of the co- occurrence of items in a collection. The relationships between co-occurring items are expressed as association rules.

Market-Basket Analysis -
Association rules are often used to analyze sales transactions. For example, it might be noted that customers who buy cereal at the grocery store often buy milk at the same time.
In fact, association analysis might find that 85% of the checkout sessions that include cereal also include milk. This relationship could be formulated as the following rule.
Cereal implies milk with 85% confidence
This application of association modeling is called market-basket analysis. It is valuable for direct marketing, sales promotions, and for discovering business trends. Market-basket analysis can also be used effectively for store layout, catalog design, and cross-sell.

Association Algorithm -
Oracle Data Mining uses the Apriori algorithm to calculate association rules for items in frequent itemsets.
Reference: Oracle Data Mining Concepts 11g Release 2
http://docs.oracle.com/cd/E11882_01/datamine.112/e16808/market_basket.htm

Answer : D

Explanation: Use the CREATE MATERIALIZED VIEW statement to create a materialized view. A materialized view is a database object that contains the results of a query.
Incorrect answer:
A: Specify DEFERRED to indicate that the materialized view is to be populated by the next
REFRESH operation.
B: Oracle Database uses the default index to speed up incremental (FAST) refresh of the materialized view.
C: By default, Oracle Database creates a primary key materialized view with refresh on demand only. If a materialized view log exists on the table, then the column can be altered to be capable of fast refresh. If no such log exists, then only full refresh of the column is possible.
Reference: Oracle Database SQL Language Reference, 11g Release 1 (11.1),CREATE

MATERIALIZED VIEW -

Answer : D

Explanation: Data mining does not reduce the amount of data in the warehouse.
Note:
Data mining (the analysis step of the knowledge discovery in databases process, or KDD), a relatively young and interdisciplinary field of computer science is the process of discovering new patterns from large data sets involving methods at the intersection of artificial intelligence, machine learning, statistics and database systems. The overall goal of the data mining process is to extract knowledge from a data set in a human- understandable structure and besides the raw analysis step involves database and data management aspects, data preprocessing, model and inference considerations, interestingness metrics, complexity considerations, post-processing of found structure, visualization and online updating.

Answer : A

Reference: http://techsatwork.com/blog/?p=743

Answer : B

Explanation: DBMS_CUBE PL/SQL Package. In Database 11gR2, a new feature was added that allows cubes and dimensions to be entirely defined via PL/SQL calls, thus making it a much simpler job to automate the creation and refresh of cubes within the context of an application.

Answer : C

Explanation: A Physical standby database replicates the exact contents of its primary database across the Oracle Net network layer. While the physical storage locations can be different, the data in the database will be exactly the same as the primary database.
Incorrect answer:
A, B: Logical standby databases convert the redo generated at the Primary database into data and SQL and then re-apply those SQL transactions on the Logical standby, thus physical structures and organization will be different from the Primary database. Users can read from logical standby databases while the changes are being applied and, if the
GUARD is set to STANDBY (ALTER DATABASE GUARD STANDBY;), write to tables in the Logical standby database that are not being maintained by SQL Apply.
Unfortunately there are a number of unsupported objects (ie: tables or sequences owned by SYS, tables that use table compression, tables that underlie a materialized view or
Global temporary tables (GTTs)) and unsupported data types (ie: Datatypes BFILE,
ROWID, and UROWID, user-defined TYPEs, Multimedia data types like Oracle Spatial,
ORDDICOM, and Oracle Text Collections (e.g. nested tables, VARRAYs), SecureFile
LOBs, OBJECT RELATIONAL XMLTypes and BINARY XML).[2] Physical standby may be appropriate in such a case.

Answer : B

Explanation: Oracle Data Integrator streamlines the highperformance movement and transformation of data between disparate systems in batch, real-time, synchronous, and asynchronous modes.
Knowledge Modules are at the core of the Oracle Data Integrator architecture. They make all Oracle Data Integrator processes modular, flexible, and extensible.
Knowledge Modules implement the actual data flows and define the templates for generating code across the multiple systems involved in each process. Knowledge
Modules are generic, because they allow data flows to be generated regardless of the transformation rules. And they are highly specific, because the code they generate and the integration strategy they implement are finely tuned for a given technology. Oracle Data Integrator provides a comprehensive library of Knowledge
Modules, which can be tailored to implement existing best practices (for example, for highest performance, for adhering to corporate standards, or for specific vertical know-how).
By helping companies capture and reuse technical expertise and best practices,
Oracle Data Integrators Knowledge Module framework reduces the cost of ownership. It also enables metadata-driven extensibility of product functionality to meet the most demanding data integration challenges.
Reference: Oracle Data Integrator, Oracle Data Sheet

Answer : A

Explanation: Oracle Business Intelligence Enterprise Edition is most commonly used with the
Oracle Database using SQL as the query language. Although the OLAP cube is a multidimensional data type, it is represented in the Oracle database as a collection of relational views and is easily queried by SQL.
Note #1: The wording of the question is strange. SQL can be used and is the first choice.
So it seems to be the best answer.
Note #2: Oracle Business Intelligence Enterprise Edition (OBI EE) is a product suite based on the OBI EE Server. The OBI EE Server can map a logical business model to many different physical data sources and present the logical model for query to variety of client applications including Interactive Dashboards, Answers and Oracle
Business Intelligence Plug-in for Microsoft Office.
Reference: Using Oracle Business Intelligence Enterprise Edition with the OLAP Option to
Oracle Database 11g, Oracle Whitepaper

Answer : C

Explanation: Compression is useful because it helps reduce the consumption of resources such as data space or transmission capacity. Because compressed data must be decompressed to be used, this extra processing imposes computational or other costs through decompression.

Answer : D

Answer : B

Answer : A

Explanation: Adaptive Parallelism: The adaptive multiuser algorithm, which is enabled by default, reduces the degree of parallelism as the load on the system increases. When using the Oracle Database adaptive parallelism capabilities, the database uses an algorithm at
SQL execution time to determine whether a parallel operation should receive the requested
DOP or have its DOP lower to ensure the system is not overloaded.
In a system that makes aggressive use of parallel execution by using a high DOP, the adaptive algorithm adjusts the DOP down when only a few operations are running in parallel. While the algorithm still ensures optimal resource utilization, users may experience inconsistent response times. Using solely the adaptive parallelism capabilities in an environment that requires deterministic response times is not advised. Adaptive parallelism is controlled through the database initialization parameter
PARALLEL_ADAPTIVE_MULTI_USER.
Reference: Oracle Database VLDB and Partitioning Guide, 11g Release 2 (11.2), How

Parallel Execution Works -
http://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel002.htm#BEIECCDD

Answer : C

Explanation: All metadata for cubes and dimensions is stored in the Oracle database.
Reference: Oracle OLAP User's Guide, 11g Release 1 (11.1), What's New in Oracle
OLAP?
Reference: Oracle OLAP User's Guide, 11g Release 1 (11.1), 8 Security http://download.oracle.com/docs/cd/B28359_01/olap.111/b28124/whatsnew.htm

Answer : C

Explanation: A data warehouse is a relational database that is designed for query and analysis rather than for transaction processing. It usually contains historical data derived from transaction data, but it can include data from other sources. It separates analysis workload from transaction workload and enables an organization to consolidate data from several sources.