Maintaining Detail Logical Tables and Detail Tables

Example: Creating a Detail Logical Table as a View to Multiple Detail Tables

A common use of a Detail Logical Table is to create it as a view to multiple, related Detail Tables. The result is that you use the Detail Logical Table as a detail data store, which can then be used as an input source for other SAS/Warehouse objects, such as an OLAP summary data store.

Note: The following explanations describe the metadata and methods used to achieve the desired results. It is assumed that the appropriate Data Warehouse Environment, Data Warehouse, Subject, and ODDs exist. [cautionend]

Planning the Detail Logical Table to be Organized as a Star Schema

To illustrate how to create a Detail Logical Table as a view to multiple, related Detail Tables, this example creates a Detail Logical Table that organizes data in a star schema. A star schema is an arrangement of tables in which a large fact table has a composite, a foreign key, and is joined to several dimension tables.

For this example, the Detail Logical Table uses the toy sales data. The star schema organization is centered around sales transactions, which is joined to several dimension tables, with each dimension table having a single primary key. Toy Sales Star Schema shows the star schema organization:

Toy Sales Star Schema

[Toy Sales Star Schema]

Each Detail Table to be viewed by the Detail Logical Table obtains its data from a specific ODD. That is, the following ODDs exist:

[untitled graphic]

For this example, the Detail Tables will be defined as follows:

a fact Detail Table named Fact with input from ODD Sales Transactions
six dimension Detail Tables:
- Detail Table Customer with input from ODD Customer
- Detail Table Drop with input from ODD Drop
- Detail Table Geography with input from ODD Geography
- Detail Table Product with input from ODD Product
- Detail Table Time with input from ODD Time

Define Detail Logical Table Properties

In the SAS/Warehouse Administrator Explorer, position the cursor on the Subject, for example Toy Sales, click the right mouse button, select Add Item, and then Detail Logical Table. In the Explorer window, a new Detail Logical Table is added under the Subject as follows:

[untitled graphic]

To update the default metadata for the Detail Logical Table, position the cursor on its icon, click the right mouse button, and select Properties. The Detail Logical Table Properties window displays for you to enter the appropriate information.

General Tab

specifies the table name Sales Detail Grp, a description, an owner, and an administrator.

[untitled graphic]

Tables Tab

specifies the Detail Tables to be members of the Detail Logical Table, which do not exist yet.

[untitled graphic]

To add a table, click Add New Table to display the Detail Table Properties window. Enter the name of the Detail Table, for example Customer.

[untitled graphic]

To finish defining the Detail Table, complete the metadata for the remaining Detail Table Properties window tabs. For a description of adding a Detail Table, see Define Detail Table Properties.

After you define the Detail Table, click OK to add the Detail Table to the list of member tables in the Detail Logical Table. You are returned to the Tables tab in the Detail Logical Table Properties window.

You would repeat these steps adding each Detail Table, which for this example, includes the following Detail Tables:

	Customer (added)
	Drop
	Fact
	Geography
	Product
	Promotions
	Time

Columns Tab

specifies the Detail Table columns to be viewed by the Detail Logical Table, which have not been specified yet.

[untitled graphic]

To import columns from an input source, click Import to display a list of input sources, then select Detail Tables. From the Import Column Metadata window, which lists the available tables, select a table (for example, Customer) to display its available columns.

[untitled graphic]

Select the appropriate columns listed under Columns, which for this example are all the columns, use the double arrows to move them to Selected Columns, then click OK. You are returned to the Columns tab in the Detail Logical Table Properties window.

[untitled graphic]

Repeat the column import process for each remaining member Detail Table.

Note: When implementing a star schema, the key column, which links the fact table to each dimension table, is included in both tables. You need only to import that column for the Detail Logical Table from either the fact Detail Table or the dimension Detail Table. Otherwise, you will get a dialog message indicating that a duplicate column exists and asking you to rename it. If you import a key column twice, you can cancel the rename dialog if you choose. [cautionend]

Location Tab

specifies the physical location of the view that provides interactive access to the Detail Logical Table.

[untitled graphic]

Define Process Editor Job

In the Process Editor Job, the Detail Logical Table and all its children Detail Tables are specified in a single Job. Sales Detail Grp is specified as the output target. Each Detail Table that is a member of the Detail Logical Table is specified as input to the Detail Logical Table as well as an output target in the Job with corresponding ODDs specified as the input sources. The following Process Editor window shows the Process Flow for the Job:

[untitled graphic]

Note: For this example, a single Process Editor Job is illustrated with multiple input sources and output targets. However, you could also define separate Jobs for each Detail Table and a Job for the Detail Logical Table. [cautionend]

The processes defined in the Job are as follows:

Mapping Processes

The source code to map columns is generated by SAS/Warehouse Administrator, rather than being user written. The Source Code tab is shown for the Customer Detail Table:

[untitled graphic]

Column mapping is defined by one-to-one mapping and some data transformations. The Column Mapping tab is shown for the Customer Detail Table:

[untitled graphic]

Note: To produce one-to-one mapping, click 1 to 1 Mappings from the Column Mapping tab, which opens the One-to-One Column Mapping window. Then, click Quick Map. [cautionend]

Note: To define data transformations, first select the column(s) to be transformed, then click Derive Mapping, which opens the Expression Builder window. Use the Expression Builder window to define the transformation. [cautionend]

Load Step Processes

The source code to load each Detail Table is generated by SAS/Warehouse Administrator.

The source code to load the Detail Logical Table is user written, as shown in the Detail Process Attributes window. For a Detail Logical Table, you must supply the Load Step code. The code creates an SQL view. (Note that you can use the Query window to generate SQL code.)

[untitled graphic]

The code used to create the SQL view follows:

[untitled graphic]

For more information about Process Editor Jobs, see Maintaining Jobs. For more information about processes, see Maintaining Processes.

Top of Page