| Oracle® Business Intelligence Applications Data Warehouse Administration Console Guide Version 7.9.4 E10759-01 |
|
 Previous |
 Next |
| Oracle® Business Intelligence Applications Data Warehouse Administration Console Guide Version 7.9.4 E10759-01 |
|
Previous |
Next |
This chapter contains the following topics:
Figure 4-1 shows the main elements of the DAC window.
Table 4-1 provides a description of the DAC menu bar options.
Table 4-1 DAC Menu Bar Options
| Menu Names | Description |
|---|---|
|
File |
The File menu contains options to close the DAC client and to create, copy, or delete source system containers. For instructions on creating or copying a source system container, see "Creating or Copying a Source System Container". |
|
Views |
The Views menu allows you to navigate to the various tabs in the top pane window. |
|
Tools |
The Tools menu provides access to functionality related to the DAC and Informatica repositories. Table 4-2 provides a description of the Tools menu commands. |
|
Help |
The Help menu provides details about the current DAC login as well as the version of DAC metadata and software. There is no online help for the DAC. |
Table 4-2 provides a description of the Tools menu commands.
Table 4-2 DAC Tools Menu Commands
| Tools Menu Command | Description |
|---|---|
|
DAC Repository Management > Export |
Allows you to export the DAC metadata, in XML format, based on the source system container, in order to back up the metadata or to reproduce the environment elsewhere. In the Export dialog, you can specify a directory in which to store the XML file or accept the default directory, which is DAC\export. In the Export dialog, you can select the following category options:
|
|
DAC Repository Management > Import |
Allows you to import the DAC metadata for the source system containers you specify. In the Import dialog, you can specify the following:
In the Import dialog, you can select the following category options:
|
|
DAC Repository Management > Create Repository Report |
Allows you to generate a DAC repository report based on the following criteria:
The Clean Up command removes unused referenced objects. |
|
DAC Repository Management > Purge Run Details |
Allows you to purge completed runs from the run history. You can purge all runs (except the last run) or specify particular runs to be purged. The last run cannot be purged. In the Purging Runs... dialog, the following options are available:
|
|
DAC Repository Management > Analyze Repository Tables |
Allows you to run analyze table commands for all the DAC repository tables. |
|
DAC Repository Management > Default Index Properties |
Allows you to specify which databases will be associated with newly created indexes. |
|
DAC Repository Management > Drop DAC Repository |
Allows you to drop all the DAC repository tables. This action deletes all data in the repository. |
|
DAC Server Management > Get Server Log |
When the DAC server is running an ETL process, this command opens a text box that displays streaming data related to the process. |
|
DAC Server Management > DAC Server Setup |
Allows you to configure the DAC server connections and server email settings. This action should be performed on the machine where the DAC server is running. |
|
ETL Management > Configure |
Opens the Data Warehouse Configuration wizard, which allows you to create and drop data warehouse tables and to create delete triggers. |
|
ETL Management > Reset Data Warehouse |
Clears the refresh dates for all source and target tables. This action forces a full load to occur. |
|
Seed Data > Task Phases |
Allows you to add, edit, or delete task phases. |
|
Seed Data > Task Folders |
Allows you to add, edit, or delete task folders. |
|
Seed Data > Logical Data Sources |
Allows you to add, edit, or delete logical data sources. |
|
UI Styles > Windows (MFC) |
Changes the user interface to the Windows style. |
|
UI Styles > UNIX (MOTIF) |
Changes the user interface to the UNIX style. |
|
UI Styles > Java (METAL) |
Changes the user interface to the Java style. |
The DAC View buttons are located directly under the menu bar. Table 4-3 provides a description of the different DAC views.
Table 4-3 DAC Views
| View | Description |
|---|---|
|
Design |
The Design view provides access to functionality related to creating and managing subject areas. For more information, see "Design View Tabs". When the Design view is active, the Source System Container Drop-Down list appears to the right of the View buttons. It allows you to select the source system container that holds the metadata corresponding to a source system. |
|
Setup |
The Setup View provides access to functionality related to setting up DAC system properties, Informatica servers, database connections, and email notification. For more information, see "Setup View Tabs". |
|
Execute |
The Execute view provides access to functionality related to setting up, running, monitoring, and scheduling execution plans. For more information, see "Execute View Tabs". |
Table 4-4 describes the commands available in the top pane toolbar.
Table 4-4 DAC Top Pane Toolbar
| Command | Description |
|---|---|
|
New |
Creates a placeholder for a new record in the selected list. |
|
Save |
Saves the current record. |
|
Undo |
Undoes changes made to the current record after the last save. |
|
Delete |
Deletes the selected record. If you delete a parent record, the child records are also deleted. When you delete a column from a table, the column is not automatically deleted from the index. The DAC does not display deleted objects. You must look at the database to figure out what objects were deleted. |
|
Query |
Opens a blank query. |
|
Refresh |
Retrieves the data from the repository with the last used query. |
|
Reference |
Design view only. Opens the Reference dialog, which allows you to copy objects from one container to another. For more information about referencing objects, see "About Object Ownership in the DAC". |
|
Assemble |
Design view only. Assembles a subject area, with dimension and related tables as well as tasks. |
|
Drop-down list |
Design view only. Allows you to filter the source system container objects that appear in the top pane list. |
|
Run Now |
Execute view, Execution Plans tab only. Starts a new ETL process. |
|
Start |
Execute view, Current Run and Run History tabs only. Restarts the selected ETL, after the ETL has failed, stopped, or been aborted. |
|
Stop |
Execute view, Current Run and Run History tabs only. Stops an ETL in progress. All currently running tasks will complete, and queued tasks will stop. The status of the ETL changes to Stopped. |
|
Abort |
Execute view, Current Run and Run History tabs only. Causes an ETL in progress to abort. All currently running tasks will be aborted. The status of queued tasks and the ETL itself will change to Stopped. |
|
Auto Refresh |
Execute view, Current Run tab only. Allows you to turn on and off the automatic screen refresh functionality and set the refresh interval. |
The commands available in the right-click menus depend on the tab that is active. For descriptions of the commands, see the following topics:
Table 4-5 Common Right-Click Menu Commands
| Command | Description |
|---|---|
|
Copy String |
Copies the contents of a cell (editable and read-only) to the clipboard |
|
Paste String |
Pastes a string from the clipboard into a selected cell that supports a string data type. |
|
Copy Record |
Creates a copy of the selected record, with a unique record ID. The new record is committed to the database when you click the Save button or click outside the cell. In the Design view tabs (except for the Indices tab), Copy Record copies the selected record and the record's child records. When you copy a subject area, the tables are also copied but the tasks are not copied. You need to use the Assemble command to reassemble the subject area and add tasks to it. In the Design view Indices tab and Setup and Execute views, Copy Record copies only the selected record. |
|
Delete |
Deletes the selected record. If you delete a parent record, the child records are also deleted. When you delete a column from a table, the column is not automatically deleted from the index. You must manually delete columns from indexes that were deleted from a table or else the ETL process will fail. The DAC does not display deleted objects. You must look at the database to figure out what objects were deleted. |
|
Output to File |
Outputs to a text file in the DAC root folder the contents of the current tab's record list. |
|
Record Info |
Displays the record's unique ID, object type, current source system, owner source system, and the timestamp for when it was last updated. It also displays the source system lineage and the source systems that reference the object. |
|
Update Records |
For some columns, allows you to update the column value for each row to a single value. |
Table 4-6 Design View Right-Click Menu Commands
| Command | Description |
|---|---|
|
Ownership |
For more information about the ownership of objects, see "About Object Ownership in the DAC". |
|
Assemble |
Assembles a subject area, with dimension and related tables as well as tasks. |
|
Generate Index Scripts |
Generates drop index, create index, and analyze table scripts for all tables that participate in the ETL process. The results are stored in the log\scripts directory. |
|
Change Capture Scripts |
For Siebel sources only.
|
|
Import from Database |
|
|
Filter Indices |
Allows you to filter by database type the indexes that are displayed in the top pane list. |
|
Output Task Description |
Saves to an HTML file the description for a selected task or for all tasks. |
|
Synchronize Tasks |
Synchronizes the information the DAC has for a task's source and target tables with the information in the Informatica repository. |
|
Flat Views |
Opens a dialog that allows you to query for various objects, modify data, and do mass updates. You can query for the following objects: Tables tab:
Indices tab: Index columns Tasks tab:
|
Table 4-7 Setup View Right-Click Menu Commands
| Command | Description |
|---|---|
|
Test Connection |
In the Physical Data Sources tab, it allows you to test the database connection. In the Informatica Servers tab, it allows you to test the connection to the Informatica Server and Repository Server. The DAC server performs this command if the DAC client is connected to a server. If the DAC client is not connected to a DAC server, then the DAC client performs the command. |
Table 4-8 Execute View Right-Click Menu Commands
| Command | Description |
|---|---|
|
Add Refresh Dates |
(Execution Plans tab) Prepopulates tables associated with the selected execution plan in the Refresh Dates subtab of the Physical Data Sources tab. This feature enables you to set or reset refresh dates manually in the Refresh Dates subtab of the Physical Data Sources tab before running an execution plan. Note: The Refresh Dates subtab of the Execution Plans tab is reserved for micro ETL processes. |
|
Build |
(Execution Plans tab) Builds the execution plan, by assembling subject areas, tasks, indices, tags, parameters, source system folders, and phases. |
|
Mark as Completed |
(Current Run and Run History tabs) Changes the status of a stopped or failed ETL to Completed. In the audit trail for this ETL, the status is Marked as Completed. Use this command with caution; it can cause the data warehouse to be inconsistent. |
|
Get Run Information > Get Log File |
(Current Run and Run History tabs) Fetches the log file for this run from the DAC server and saves it in the ServerLog folder. |
|
Get Run Information > Analyze Run |
(Current Run and Run History tabs) Saves a description of the run as an HTML file in the Log/Statistics folder. |
|
Get Run Information > Get Chart |
(Current Run and Run History tabs) Displays a chart showing changes in task statuses over time in a separate window. |
|
Get Run Information > Get Graph |
(Current Run and Run History tabs) Displays a graph showing changes in task statuses over time in a separate window. |
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 4-2. 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 4-3. Finally, 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 4-4. In addition, 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 4-5. The tree root nodes correspond to the tabs in the top pane of the DAC window. When a plus sign (+) appears before a node, you can expand the node to view the records belonging to the node. You can double-click a record in the tree to have it display in the top pane in a single-record mode (New, Delete, Copy Record, and Query commands are unavailable), and double-click the root node to return to the list mode.
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 allows you to perform various tasks associated with a selected record in the list. For a description of the toolbar commands, see and "The DAC 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 "The DAC Right-Click Menus".
The list format allows 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.
For fields that refer to values from other entities, use the query functionality in pop-up dialogs.
Use Ctrl+C to copy an editable string to the clipboard (not available for read-only strings).
Ctrl+V to paste a string from the clipboard into a selected cell that supports a string data type.
The source system container in which an object originates is 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, but the object's ownership remains unchanged.
For example, W_INVOICE_F is a fact table whose owner is the Siebel 7.8 source system container. You can reuse W_INVOICE_F in any other container by referencing it.
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. Use the right-click command and select Ownership, then select Push to References to push the changes to the referenced object's child objects. 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 Table 4-6.
The different categories of objects are represented in the DAC with differing fonts.
Changes made to parent objects in the owner container are automatically pushed to the parent referenced objects.
When you add child objects to a parent object, you have to push the changes to the referenced objects. For example, if you add a column to a table registered in the DAC, the new column is not automatically added to the references.
When you delete a referenced object, only the referenced object is deleted, not the original object.
If you delete an object from the owner container, the object is deleted and all references are deleted. This is referred to as a deep delete. For example, if you delete a table from the owner container, the table and columns are deleted.
If you delete a column from the owner table, the column is deleted in all the referenced objects.
If you delete child objects from the owner object, the referenced child objects are automatically deleted.
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 child tabs in the bottom pane are not available.
This section includes the following topics:
Table 4-10 describes the query commands and operators you can use to define your query criteria.
Table 4-10 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 surround 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, click Query on the toolbar or in right-click menu.
A blank row in a list appears.
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 appears.
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 allows 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 DAC, 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.
An informational message tells you which records were updated.
Click OK to close the window.
