New Script

Click to open the form that allows for definition of a script.

Save As Template

Data Source Tables can be saved to the Template library for use in future projects. Select a Dimension Table and click. The displayed dialog gives the user the opportunity to either Save the Table as New or to Overwrite an existing Table.

Copy

A Data Source Table can be copied to clipboard by clicking.

Delete

Click to delete the selected Data Source Table. An "Are you sure?" dialog requiring a confirmation is displayed before the delete is performed.

Save

Click to save the information defined in the Details and the Column Mapping panes to the Solution database.

Reset

Click to undo any edits and or deletes and redisplays the original configuration as of the most recent Save.

Process / Edit Data

The "Process" button will load source data using the previously used load option.
The "Edit Data" button is visible if the table is in an editable state. The user can then edit the data warehouse table data directly.

Tasks and Data

Data Warehouse Table Management.

The "Tasks and Data" button will allow for specifying reload options for an ad hoc reload or perform delete operations.

Load Options:
- Reload Days (Number of days, using Transdate column)
- Reload Months (Number of months, using Transdate column) 
- Reload From Date (using Transdate column)
- Reload From and To Date (using Transdate column) 
- Custom Reload (using Source Filter and Delete Filter)
- Incremental Reload (MERGE using Primary Key)
- Reload All 
- Auto Update (optionally utilizing auto update table or auto update filter) 

Manual updates to the Data Warehouse are performed using the command buttons in the Tasks and Data form. The following commands are available:

Name

Name of the Data Source Table

Type

Type of Data Source Table

Tag type (Tag Value tables only)

All Values (Automatically fetch all values)
Multiple Values (Allow user to select from multiple Values)
Number List (Allow user to select from multiple values to populate a number list)
Single Value (Allow user to select a single value)
String List (Allow user to select from multiple values to populate a string list)

Get Table Row Counts (Table Metadata tables only)

Turns on table by table row count (SELECT COUNT(*) FROM tableprefix.tablename is executed and the resulting row count is inserted into the MetadataTableRowCount field)

Get View Row Counts (Table Metadata tables only)

Turns on view by view row count (SELECT COUNT(*) FROM tableprefix.viewname is executed and the resulting row count is inserted into the MetadataTableRowCount field)

Get Primary Key Info (Field Metadata tables only)

Turns on gathering of primary key information (the SQL defined in the Primary Key Info field is executed and the resulting primary key information is inserted into the MetadataFieldKeyInfo field)

Add source tables as staging tables (Processed Table Metadata tables only)

Will add selected source tables as staging tables.

Add source tables as linked data tables (Processed Table Metadata tables only)

Will add selected source tables as linked data tables.

Usage (Tag Value tables only)

Will perform a scan in all data source tables within the data source and display a list of all contexts where it is used.

Description

Optional

Comments

Optional

View Source Table

Click to display the Data Source Table and Column names and the results of the Source Query. Using drag and drop, column headings can be copied into the Source Column cells of the Column Mapping table.

View File (File and Excel only)

Click to view the file chosen in the Table Name pull down list.

Source location

Select from the pull down list. Source is set to either External or Data Warehouse depending on whether the data will stem from the source system or existing data warehouse data.

Source data

Table or view - Select a table or view from the "Table name" pull down list.
SQL source query - Click on the "Edit Source Query" button to define a SQL query.
Edit table - Click on the "Edit Data" button in the topmost toolbar to manually update the data. If the table has not yet been transferred or created it will be created on-the-fly when the "Edit Data" button is pushed.
PoSh source script - Click on the "Edit Source Query" button to view or edit a PowerShell source script.

Table name / SQL expression / File name

Select the Source System table name from the pull down list. Alternatively specify a SQL Expression.

Table filter

Define a filter on the rows in the source table, i.e. the Where Clause. For example, the Table Row Filter: SHIPMTHD = ‘GROUND’ will return only the rows with the GROUND shipping method.

Apply main filter

Indicate whether the main filter applies to this data source table or not. (Main filter is set on Data Source page)

Table Name… (SQL Expression)

Click the button to display the Edit SQL Expression Editor. See SQL Expression Editor below for details.

Table Filter…

Click the button to display the Edit Filter Editor.

Expression Pane

The Expression Pane displays the SQL Expression that retrieves information from the Data Source and updates the Dimension Table in the Data Warehouse. Expressions are automatically constructed by the system when one table is the target. When more complex expressions are required the user can create the expression in the editor, or copy and paste expressions from other editors. The Expression Pane includes the following commands:

Preview Pane

The Preview Pane displays the SQL Expression results or displays Data Source Table-Column data when browsing. Column headers can be selected, dragged and dropped into the Column Mapping table.

Objects Pane

The Object Pane is used to browse the Tables and Columns of a Data Source. There are three (3) tabs in the Object Pane provides the following capabilities.

Source Tab 

The Source Tab provides the following functions:

Functions Tab 

Displays a library of commonly used SQL Functions and allows the user to drag & drop Functions into SQL Expressions.

Tags Tab

Displays a list of the defined Tags and allows the user to drag & drop Tags into SQL Expressions. 

Data Source Column

The Data Source Column is defined by first clicking, then clicking in the Data Source Column cell of the newly added row. Select the desired Data Source Column using the pull down menu. Hint: Type all or part of the Dimension Column name to quickly find items in the pull down list.

Primary Key / Index (Type if Parent / Child tables)

Define if column should be (part of) primary key or index.

Source Expression

There are several ways to enter the name of the Source Column. They are:
1. Type the name in the field. The format is typically: LTRIM(RTRIM(sourcecolumn))
2. Click to display the table of raw data via the View Source Table command button; then Drag & Drop the desired column header to the Source Column cell. The system will automatically format the field.
3. Select the Source tab in the Source Mapping pane to display the columns from the Table Name defined in the Details pane. Drag & Drop the desired Source Column name to the Source Column cell or position the cursor in the desired Source Column cell then double-click on the desired Source Column name. The system will automatically format the field appropriately.
4. Select the cell. Click Edit Column Expression to display the Edit Column Expression Editor.

Comments 

Optional

New

Click to insert a new row in the Column Mapping table.

Delete

Click to delete the selected row immediately. Click Save to apply the Delete(s) or click Reset to undo the Delete(s).

Map Source Columns

Click to automatically map unused source columns.

Multi (Single) Column PK/Index

Enable using a multi-column primary key and/or a multi-column non clustered index. After checking this option you must go to the "Primary Key / Index" drop down to set the sequence of the primary key and/or index columns.

Edit Column Expression

Click to display the Edit Column Expression form that allows for a more complex mapping expression rather than a simple data source column reference.

View SQL Expression

Click to display the SQL Expression that the system constructs to extract the data from the Data Source.

Preview Table / View Source Table / View Datawarehosue Table (F5)

Preview result of Column Mappings. The context sensitive F5 key show either source/mapping or dw data.

Show/Hide Source Mapping Panel

The Show Source Mapping Panel command is used to make the Source Mapping Panel visible. Use Hide Source Mapping Panel as a means to make more space available for the Column Mapping grid. 

Reload Modes

Transdate reload, Allow for date based reload options. User must specify a Date Column in the source table.
Incremental reload, Allow for incremental load of data. Data is inserted or updated based on a Primary Key. User must specify a Reload Source Filter.
Custom reload, Allow for a fully customized reload of data. User specifies Reload Source Filter and Delete Filter.

Reload Options

The following reload options are available:

Use page compression

The table will be stored using page compression. This can give better query performance. The Estimate Compression Savings function in Data Source context will present the best candidates for compression.

Use memory-optimized table

SQL 2016 supports memory-optimized tables. 
Durable
Non-durable
Index bucket count 

Use columnstore index

The table will be stored using column store indexing. This will in most cases give better query performance.
Clustered
Nonclustered 

Use partitioning

Specify whether table should be partitioned. Select existing partition from drop down list. Solution Partitions are maintained in the Solution Management part of the application.
If the selected partition is not a SQL partition. the partition scheme is only used to track changes per partition. For more details. See tutorial here. 
 

Skip partitioning of Distinct Tables (visible if Use partitioning is selected)

By default also distinct tables will be partitioned to allow for tracking data changes by partition. If this is not wanted the user can avoid partitioning of distinct table(s).

Process modes

The process modes group contains the following options:

Load options

The following load options are available:

SSIS source settings

The following SSIS source settings are available:

SSIS destination settings

The following SSIS destination settings are available:

Skip auto update

Check to indicate that this table will not be a part of auto update reloads.

Maintain auto update table

Specify whether auto update on the data warehouse module level should add missing DimensionID's in a TABLENAME_AutoUpdate table. Applies to Wide and Parent/Child Dimension tables.

Auto update filter

The user can specify a separate filter for auto update reloads. This filter can for example make use of the TABLENAME_AutoUpdate table if the "Maintain auto update table" is checked.

Refresh

Click to Refresh partition information after Processing.

Sequence

The partition range sequence number.

Boundary

The boundary values for the partition range.

ID

ID of the partition range.

Last Data Change

The last time partition range data was changed.

Rows

Number of rows in partition range.