Oracle® Fusion Middleware User's Guide for Oracle Business Intelligence Data Warehouse Administration Console 11g Release 1 (11.1.1) Part Number E14849-05 |
|
|
PDF · Mobi · ePub |
This chapter provides an overview of DAC and explains fundamental concepts that you need to know before you begin using DAC.
This chapter contains the following topics:
DAC has a distributed client-server architecture in which the DAC Client issues service requests of the DAC Server. However, because of security considerations, the DAC Client must be physically located in the server tier with the other server-tier components. For more information about DAC Client security, see "DAC Client Installation Requirements".
DAC runs either as an enterprise application on WebLogic Server (Web mode), or as a standalone Java application (standalone mode). For topology diagrams of these two modes, see Figure 1-1 in "DAC Authentication in Oracle Fusion Middleware (WebLogic Server) Mode" and Figure 1-2 in "DAC Authentication in Standalone Mode".
As shown in Figure 1-1 and Figure 1-2:
The Client tier contains the Informatica PowerCenter client tools and the DAC Client.
The Server tier contains the following:
DAC Server. The DAC Server executes the instructions from the DAC Client. It manages data warehouse processes, including scheduling, loading of the ETL, and configuring the subject areas to be loaded. It dynamically adjusts its actions based on information in the DAC repository. Depending on your business needs, you might incrementally refresh the Oracle Business Analytics Warehouse once a day, once a week, once a month, or on another similar schedule.
DAC Repository. The DAC repository stores the metadata (semantics of the Oracle Business Analytics Warehouse) that represents the data warehouse processes.
Informatica PowerCenter Integration Service. The Integration Service reads workflow information from the repository. The Integration Service connects to the repository through the Repository Service to retrieve metadata from the repository.
Informatica PowerCenter Repository Service. The Repository Service manages connections to the Informatica repository from client applications. The Repository Service is a separate, multi-threaded process that retrieves, inserts, and updates metadata in the repository database tables.
Informatica Repository. Stores the metadata related to Informatica workflows.
The Database tier contains the transactional and data warehouse databases.
DAC provides a framework for the entire life cycle of data warehouse implementations, including the setup, configuration, administration, and loading of data warehouses. DAC enables you to create, configure, execute, and monitor modular data warehouse applications in a parallel, high-performing environment.
DAC is a metadata-driven ETL orchestration tool that complements ETL platforms, such as Informatica. It provides application-specific capabilities that are not prebuilt into ETL platforms. For example, ETL platforms are not aware of the semantics of the subject areas being populated in the data warehouse nor the method in which they are populated.
DAC provides application capabilities at a layer of abstraction above the ETL execution platform that enable you to do the following:
Minimize installation, setup, and configuration time
Create a physical data model in the data warehouse
Design subject areas and build execution plans
Manage metadata driven dependencies and relationships
Generate custom ETL execution plans
Capture deleted records
Manage indexes
Perform test runs of execution plans
Provide reporting and monitoring to isolate bottlenecks
Perform error monitoring and email alerting
Perform structured ETL analysis and reporting
Utilize performance execution techniques
Automate full and incremental mode optimization rules
Set the level of ETL session concurrency
Load balance across multiple ETL servers
Restart from point of failure
Create indexes in parallel
Run appropriate tasks before query indexes have been created
Queue execution tasks for performance (see Figure 2-1)
DAC manages the task execution queue based on metadata driven priorities and scores computed at runtime. This combination allows for flexible and optimized execution. Tasks are dynamically assigned a priority based on their number of dependents, number of sources, and average duration.
Figure 2-1 illustrates how tasks are assigned a position in the execution queue. When a task becomes eligible to run, DAC assesses first its phase priority and then the task execution priority. Next, DAC assigns an order of execution based on the number of task dependents, the number of sources, and the estimated duration of the task.
Figure 2-2 and Figure 2-3 show a comparison between poor utilization of resources and optimum utilization of resources. Figure 2-2 illustrates a queue with no task management, in which wait periods can be extensive because resources are not optimized. Figure 2-3 illustrates the strategy used by DAC, in which the queue is managed dynamically based on information gathered about each task, as illustrated in Figure 2-2.
Figure 2-3 DAC's Optimum Resource Utilization
DAC is used by different user groups to design, execute, monitor, and diagnose execution plans. These phases together make up the DAC process life cycle, as shown in Figure 2-4.
The phases of the process and the actions associated with them are as follows:
Setup
Set up database connections
Set up ETL processes
Set up email recipients
Design
Define application objects
Design execution plans
Execute
Define scheduling parameters to run execution plans
Access runtime controls to restart or stop currently running schedules
Monitor
Monitor runtime execution of data warehouse applications
Monitor users, DAC repository, and application maintenance jobs
Source system containers hold repository objects that correspond to a specific source system. You cannot modify objects in the predefined source system containers. You can make a copy of a predefined container and then modify the metadata to create your own custom source system container. This enables the DAC Client to track customizations you make in the copy of the source system container, such as newly created objects and modified objects. DAC is also able to compare the modified objects with the predefined object definitions in the predefined container. This feature enables DAC to rollback changes to the objects if necessary. For more information, see "About Object Ownership in DAC".
Caution:
You cannot modify objects in the predefined source system containers either through the DAC Client or directly through SQL statements to the DAC repository. You must make a copy of a predefined container in order to make any changes to it.
For instructions on creating a new source system container or copying an existing container, see "Creating or Copying a Source System Container".
All DAC repository objects are associated with a source system container. The DAC repository stores objects in a hierarchical framework that defines a data warehouse application. DAC enables you to view the repository application objects based on specific source system containers. The source system container holds the metadata that corresponds to the source system with which you are working.
A data warehouse application includes but is not limited to the following repository objects:
Execution plan. A data transformation plan that is defined on subject areas and is transformed at certain frequencies of time. Execution plans are defined based on business requirements for when the data warehouse needs to be loaded. Execution plans can be scheduled for full or incremental loads.
Subject area. A logical grouping of tables related to a particular subject or application context. A subject area includes the tasks that are associated with the tables, as well as the tasks required to load the tables. Subject areas are assigned to execution plans.
Tables. Physical database tables defined in the database schema. The tables can be transactional database tables or data warehouse tables. Table types include dimension, hierarchy, aggregate, and so on. Flat files can also be used as sources or targets.
Tasks. Units of work for loading tables. Tasks comprise the following: source and target tables, phase, execution type, truncate properties, and commands for full or incremental loads. Tasks can do the following: execute Informatica workflows, execute batch files, call database stored procedures, and execute SQL, XML, and operating system commands. When you assemble a subject area, DAC automatically assigns tasks to it. Tasks that are automatically assigned to the subject area by DAC are indicated by the Autogenerated flag in the Tasks subtab of the Subject Areas tab.
Task properties are critical in ETL design and execution. DAC automatically assembles tasks into subject areas based on task properties, such as source and target tables. Tasks in the ETL queue are prioritized by the DAC Server, based on task properties, such as phase, source and target connections, and truncate properties.
Task group. A group of tasks that you define because you want to impose a specific order of execution. A task group is considered to be a "special task."
Indexes. Physical database indexes to be defined in the database schema to improve the performance of the ETL processes or the queries for reporting purposes.
Schedule. A schedule specifies when and how often an execution plan runs. An execution plan can be scheduled for different frequencies or for recurrences by defining multiple schedules.
Figure 2-5 illustrates the hierarchy among the DAC repository objects.
Figure 2-5 DAC Repository Object Hierarchy
For best practice tips about the DAC repository objects, see"Considerations When Defining Repository Objects".
The source system container in which an object originates is known as the owner container. The tabs in the DAC Design view display the owner of the various repository objects.
You can reuse an object among different source system containers by referencing the object. A reference works like a symbolic link or shortcut. You can use the referenced object just as you would an original object; the object's ownership remains unchanged.
For example, W_INVOICE_F is a fact table whose owner is the data warehouse source system container. You can reuse W_INVOICE_F in any other container by referencing it, but the owner is always the data warehouse.
You can reference an object from its owner container, and you can also reference an object that has already been referenced by another source system container.
If you modify a referenced object, the modified object becomes a clone and the ownership changes to the source system container in which you performed the modification.
When you make changes to an original object that has been referenced by other containers, any updates to the original object are immediately reflected in the referenced object. If you delete the original object, all referenced objects are also deleted.
Changes to an original object's child objects are not automatically reflected in the referenced object's child objects. You need to push the changes to the referenced object's child objects by using the Push to References right-click command. And, conversely, you can import into a referenced object the changes made to an original object. This function is referred to as a re-reference.
For a description of the ownership functionality available in the Design view right-click menu, see "Ownership Right-Click Commands".
This section describes the main elements of the DAC user interface. It contains the following topics:
Figure 2-6 shows the main elements of the DAC window.
Key to figure:
a. File menus
b. Views button and container drop-down list
c. Top pane tabs
d. Top pane toolbar
e. DAC Server monitor icon
f. Editable lists
g. Bottom pane tabs
The menu bar provides access to the File, Views, Tools, and Help menu commands. For a detailed description of these commands, see the following sections in Chapter 15, "DAC Functional Reference":
The View buttons are located directly under the menu bar and provide access to the Design, Setup, and Execute views. For detailed information about the functionality provided in the DAC views, see the following sections in Chapter 15, "DAC Functional Reference":
For a detailed description of the commands available in the top pane toolbar, see the section titled "Top Pane Toolbar Commands" in Chapter 15, "DAC Functional Reference."
For a detailed description of the commands available in right-click menus, see the section titled "Right-Click Menu Commands" in Chapter 15, "DAC Functional Reference."
The Server Monitor is located in the upper-right corner of the DAC Client. Its color and shape change based on the DAC Server status. When the DAC Client cannot establish a connection to the DAC Server, the Server Monitor icon resembles a red electrical plug, as shown in Figure 2-7. When Accessibility Mode is enabled, the text "Not connected to DAC Server" is displayed.
When the client is connected to the server and the server is idle, the icon resembles an orange electrical plug in a socket, as shown in Figure 2-8. When Accessibility Mode is enabled, the text "Connected to idle DAC Server" is displayed.
If the client is connected to a server that is running an ETL process, the icon resembles a green electrical plug with a lightning sign superimposed on it, as shown in Figure 2-9. When Accessibility Mode is enabled, the text "Connected to active DAC Server" is displayed. In addition, in standalone mode, clicking on the icon when there is a connection to the server opens a text box that displays data related to the ETL process.
The navigation tree appears on the left side of the DAC window, as shown in Figure 2-10. It displays the top-level tabs of the selected view.
The top and bottom panes of the DAC window display records in a list format. Some of the columns in the list are editable, and others are read-only. The toolbar at the top of each pane enables you to perform various tasks associated with a selected record in the list. For a description of the toolbar commands, see "Top Pane Toolbar".
A right-click menu is also accessible from the lists in both the top and bottom panes. For a description of these commands, see "Right-Click Menus".
The list format enables you to do the following:
Edit the data in place and save the record by either clicking another record in the list or clicking the Save button.
Reorder the columns.
Sort the data in the list by clicking on the column name.
Select predefined values from picklists.
Use the query functionality in pop-up dialogs for fields that refer to values from other entities.
Use Ctrl+C to copy an editable string to the clipboard (not available for read-only strings).
Use Ctrl+V to paste a string from the clipboard into a selected cell that supports a string data type.
The different categories of objects are represented in the DAC with differing fonts. For a description of the object types, see "About Object Ownership in DAC".
Querying is a way to locate one or more records that meet your specified criteria. Query functionality is available in every DAC screen. When you enter query mode, the Edit and Description subtabs in the bottom pane are not available.
This section contains the following topics:
Table 2-2 describes the query commands and operators you can use to define your query criteria.
Table 2-2 DAC Query Commands and Operators
Operator | Description |
---|---|
= |
Placed before a value, returns records containing a value equal to the query value. |
< |
Placed before a value, returns records containing a value less than the query value. |
> |
Placed before a value, returns records containing a value greater than the query value. |
<> |
Placed before a value, returns records containing a value that is not equal to the query value. |
<= |
Placed before a value, returns records containing a value less than or equal to the query value. |
>= |
Placed before a value, returns records containing a value greater than or equal to the query value. |
* |
Wildcard that can be placed in the middle, or at the beginning or end of a text string. |
! |
Used for negation. |
"" |
Surrounds a string that, unless modified by a wildcard, must be matched exactly. |
\ |
Escape symbol is used when double quotes should not be processed as a special symbol. For example, |
() |
Surrounds the values and operators that will be processed first. |
NULL |
Returns records for which the query field is blank. |
AND |
Placed between values, returns only records for which all the given conditions are true. (Not case sensitive.) |
OR |
Placed between values, returns records for which at least one condition is true. (Not case sensitive.) |
The following examples show different ways you can query on the Name column of the Tasks tab.
Extract*
lists all tasks whose name starts with Extract.
*Extract*
lists all tasks whose name contains the word Extract
.
!Extract*
lists all tasks whose name does not start with the word Extract
.
!null
lists all tasks whose name is not null.
Extract*
or Aggregate*
lists all tasks whose name starts with Extract
or Aggregate
.
Load*
and *Aggregate*
lists all tasks whose name starts with Load
and also contains the word Aggregate
.
"Extract for Wave Dimension"
or "Load into Wave Dimension"
lists tasks whose name is either Extract for Wave Dimension or Load into Wave Dimension.
Note:
When using spaces within strings, you need to enclose the string with quotes ("").
This section includes instructions for common query procedures.
To create and execute a query in the DAC
In the top or bottom pane of the DAC Client, click Query on the toolbar or in the right-click menu.
A blank row in a list is displayed.
Enter the query criteria in the appropriate fields.
Click Run Query on the toolbar.
The query is executed and the records appear.
To enter a query value in a date field
In the date field, click the Calendar icon on the right side of the cell.
The Date dialog is displayed.
Enter the date and time for which you want to search, and select the appropriate query condition.
You can use the Flat Views query feature to query for various objects, modify data, and do mass updates. This feature is available in the right-click menu in the Tables, Indices, and Tasks tabs of the Design view. The Flat Views right-click command is context-sensitive and enables you to query only on certain columns.
You can modify individual records in the query results window, or you can use the Update Records right-click command to update multiple records.
To update multiple records using the Flat Views query feature
In the Design view, right-click in the Tables, Tasks or Indices tab.
Select Flat Views, and then select a context-sensitive column on which you want to query.
In the query dialog, enter search criteria, and click Go.
In the query results dialog, right-click and select Update Records.
In the Update Record Set dialog, select the column you want to update, and then click Set Value.
Enter a value for the column.
To update records that are referenced objects, select Update Referenced Records.
If you select this check box, referenced objects as well as original and cloned objects will be updated. The referenced objects will become clones, and the ownership column for these records will be updated to reflect the new ownership.
If you do not select this check box, only the columns in records that are original or cloned objects (objects owned by the source system container) will be modified.
Click OK.
Click Yes when asked if you want to proceed.
A message dialog tells you which records were updated.
Click OK to close the window.
Some DAC tabs and dialogs open in Query mode, which displays an empty record in which you can enter query criteria. DAC tabs that open in Query mode do not display the list of records contained in the tab. You need to exit Query mode in order to see the list of records. To exit Query mode, click Go in the toolbar.
Table 2-3 describes the DAC accessibility keyboard options.
Table 2-3 DAC Accessibility Keyboard Options
To Do This | Press |
---|---|
Expand the Views menu to the first level. |
ALT+V. |
Move between menu options. |
Arrow keys. |
In a tabular form, move one cell to the right. |
Tab or right arrow key. |
Select a menu option. |
Enter. |
In a tabular form, move one cell to the left. |
Shift+Tab or left arrow key. |
In a tabular form, move one row up. |
Up arrow key. |
In a tabular form, move one row down. |
Down arrow key. |
Activate a cell. |
Spacebar. |
Activate a cell with a drop-down list. |
F2. |
Move between options in an open drop-down list. |
Arrow keys. |
Select an option from a drop-down list. |
Enter. |
To escape from a cell in edit mode. |
Esc. |
Add a new value to a cell with a drop-down list. |
F2 to enter edit mode, then enter text. |
Select a check box. |
F2 to activate the check box, spacebar to toggle between selecting and clearing the check box. |
To activate a multi-value group (MVG) cell. |
Spacebar to activate the cell, F2 to enter edit mode, Ctrl+E to display the dialog (such as a date dialog). |
Move to the next focusable object. |
Tab |
Move to the previous focusable object. |
Shift+Tab. |
Move between tabs. |
Arrow keys. |
Move between panels of a dialog. |
F6. |
Select a button on a dialog. |
Alt+the letter underlined on the button. |
To display the context menu for a dialog. |
Shift+F10. |
To activate the menu bar on a dialog. |
F10. |
To display the default Windows menu in a dialog. |
Alt+spacebar. |
You can install the DAC Client and Server in various ways, as described below.
Oracle Fusion Applications Provisioning. During the Oracle Fusion Applications installation and provisioning process, the DAC Client and DAC Server software files are installed in the Oracle Home directory for Oracle Business Intelligence. After you complete the Fusion Applications provisioning process, you must perform additional setup and configuration tasks related to DAC. For more information about the required tasks you must perform, see Oracle Fusion Middleware Installation and Configuration Guide for Oracle Business Intelligence Applications.
Oracle Business Intelligence Applications Client Installer. Installs the DAC Client on Windows machines. For instructions on running this installer, see Oracle Fusion Middleware Installation and Configuration Guide for Oracle Business Intelligence Applications. For important security considerations when installing the DAC Client, see "DAC Client Installation Requirements".
Table 2-4 lists the DAC installation directory paths.
Table 2-4 DAC Installation Directory Paths
ORACLE_HOME (read only) | $DOMAIN_HOME/dac | DAC_CONFIG_LOCATION (shared network location) | Client Config |
---|---|---|---|
DACSystem.jar |
/*.bat, *.sh |
/conf-shared/*.* |
/conf-client/login.xml |
/lib/*.* |
/utilities/*.* |
/conf-shared/connection_template.xml |
/conf-client/deleteTriggers.list |
/documentation/*.* |
/conf/numeric.precision.override.conf |
/conf-shared/extralibs.properties |
/conf-client/ws/ |
unix_script_bkp/*.* |
/conf/sqlgen/*.* |
/conf-shared/infa_command.xml |
/conf-client/connections/*.* |
version.txt |
/export/*.* |
/conf-shared/security/mail |
/conf-client/security |
/Informatica/*.* |
/conf-shared/security/repository |
||
/log/*.* |
/conf-shared/server.properties |
||
/conf-shared/task_restartability.xml |
|||
/conf-shared/upgrade/*.* |
|||
/CustomSQLs/*.* |
About the /conf-client/ws Directory
Note the following points about the /conf-client/ws directory:
The oracle-webservice-client.xml file resides in the dac/conf-client/ws directory. This file declares the Oracle Web Services Manager policies for the DAC Client. When you configure the DAC Client to run over SSL, this policy needs to be replaced by oracle/wss_username_token_over_ssl_client_policy
.
oracle/wsmtom_policy
supports log file transfers.