Auto Discovery Wizard

Auto Discovery Wizard Overview

The Fabric Studio Auto Discovery Wizard enables you to automatically generate or edit an LUDB schema based on predefined database constraints like a Primary Key or a Foreign Key.

The Auto Discovery Wizard can run in two modes:

  • Fast, the Auto Discovery Wizard generates a new Logical Unit schema based on Foreign Keys only.
  • Medium, the Auto Discovery Wizard generates a new Logical Unit schema based on Primary Keys, Foreign Keys and Virtual Primary Keys. This mode allows you to be more involved in certain aspects of the process.

How Do I Use the Auto Discovery Wizard?

STEP 1: Define the DB Interface

The first screen displays a list of all DB interfaces defined in a Fabric project. You need to select the DB interface for the Auto Discovery process.

image

  1. Click the DB interface to display its connection string.
  2. To validate the connection settings, click Test Connection.
  3. Click Next.

STEP 2: Define the Root Table and Field

In this step you will select a DB Table and column as the Root Table and Instance ID of the Logical Unit.

image

  1. Go to the Tables list and click the Root Table and then click the Instance ID.
  2. Optional: In the File Name Prefix field, enter a string prefix for the LU table names. By default, the LU table name receives the name of the original source DB table.
  3. Optional: Uncheck Add schema name to the generated files. When checked, the Schema Name is concatenated to the LU Table Name.
  4. Optional: Check Table population based Broadway flow. When checked, all the tables populations are created based on Broadway flows.
  5. Click Next.

STEP 3: Define the Auto Discovery Mode and Settings

This screen allows you to select the Auto Discovery mode (auto discovery type) and add additional settings to it.

image

Auto Discovery Mode (Type)

Auto Discovery supports the following modes:

  • Fast mode - the default mode. Follow the connections of the physical foreign keys as defined in the selected database to identify the relationships between the database tables.
  • Medium mode – allows you to be more involved in certain aspects of the process and checks the primary key fields in the tables to search for a link between the tables. See the list of parameters for Medium Mode in the table below.

Auto Discovery Settings

Tables Regular Expression Checkbox

Optional. To limit the number of tables included in the Auto Discovery Wizard, define a subset of the source DB tables to be connected.

A subset is defined using a regular expression that matches the names of the tables to be included. By default, it is populated by the schema name of the selected DB.

Example 1: if the schema name is CRM_DB, then this field is populated by default by ^CRM_DB\.

Example 2: if the regular expression ^CUSTOMER is defined, the Auto Discovery Wizard searches for relationships only within tables whose name begins with CUSTOMER.

Enable Backwards Discovery Checkbox

 

By default, this checkbox is unchecked. When checked, tables identified by Auto Discovery as the parent tables of the selected Root Table are added to the LU schema as child tables and are not filtered out of the schema.

See the example in Step 4 below.

Medium Mode Parameters

These optional settings are only displayed when Medium Mode is checked in the left pane.

Column Aliases Collections

Defines the relationship between fields within different tables.  You can drag columns from the tables of the selected DB or edit this field manually.

For example:  to populate the alias using CUSTOMER_ID, CUST_ID, CUST_NO, Auto Discovery links between these fields even though they have different names.

Virtual Primary Keys

Fields that are not defined as the  primary key that are recognized and treated as a primary key by the Auto Discovery Wizard, in addition to the original Primary Key.

Primary Keys that are Skipped

Primary keys that need to be ignored or skipped by the Auto Discovery Wizard.

STEP 4: Check the ERD Diagram

Display the suggested ERD diagram and the selected Root Table (pink) and related tables.

Connections are colored:

  • Foreign key connection = blue.
  • Primary key connection = green.
  • Virtual key connection = olive.

If the selected Root Table is not defined as the root of the identified ERD, consider changing the selected Root Table or check the Enable Backwards Discovery checkbox. If not, the created LU includes only the selected Root Table and its subordinate table.

For Example image

The selected Root Table is CRM_DB.ACTIVITY. However, the Auto Discovery Wizard identifies the CRM_DB.CUSTOMER table as the Root Table of the CRM_DB.

When saving the CRM_DB.ACTIVITY table as a Root Table:

  1. If Enable Backwards Discovery is unchecked (default), then Auto Discovery creates the following LU and filters out the remaining tables of the ERD diagram.

image

  1. When Enable Backwards Discovery is checked, Auto Discovery creates the following LU and links the CUSTOMER table and its subordinate table to the ACTIVITY table.

image

Create the Logical Unit Diagram

Display the suggested logical unit diagram and click Finish to create the LU.

image

Optional: you can check tables out of the LU schema to create them as LU tables and add them manually to your LU if needed.

Previous

Auto Discovery Wizard

Auto Discovery Wizard Overview

The Fabric Studio Auto Discovery Wizard enables you to automatically generate or edit an LUDB schema based on predefined database constraints like a Primary Key or a Foreign Key.

The Auto Discovery Wizard can run in two modes:

  • Fast, the Auto Discovery Wizard generates a new Logical Unit schema based on Foreign Keys only.
  • Medium, the Auto Discovery Wizard generates a new Logical Unit schema based on Primary Keys, Foreign Keys and Virtual Primary Keys. This mode allows you to be more involved in certain aspects of the process.

How Do I Use the Auto Discovery Wizard?

STEP 1: Define the DB Interface

The first screen displays a list of all DB interfaces defined in a Fabric project. You need to select the DB interface for the Auto Discovery process.

image

  1. Click the DB interface to display its connection string.
  2. To validate the connection settings, click Test Connection.
  3. Click Next.

STEP 2: Define the Root Table and Field

In this step you will select a DB Table and column as the Root Table and Instance ID of the Logical Unit.

image

  1. Go to the Tables list and click the Root Table and then click the Instance ID.
  2. Optional: In the File Name Prefix field, enter a string prefix for the LU table names. By default, the LU table name receives the name of the original source DB table.
  3. Optional: Uncheck Add schema name to the generated files. When checked, the Schema Name is concatenated to the LU Table Name.
  4. Optional: Check Table population based Broadway flow. When checked, all the tables populations are created based on Broadway flows.
  5. Click Next.

STEP 3: Define the Auto Discovery Mode and Settings

This screen allows you to select the Auto Discovery mode (auto discovery type) and add additional settings to it.

image

Auto Discovery Mode (Type)

Auto Discovery supports the following modes:

  • Fast mode - the default mode. Follow the connections of the physical foreign keys as defined in the selected database to identify the relationships between the database tables.
  • Medium mode – allows you to be more involved in certain aspects of the process and checks the primary key fields in the tables to search for a link between the tables. See the list of parameters for Medium Mode in the table below.

Auto Discovery Settings

Tables Regular Expression Checkbox

Optional. To limit the number of tables included in the Auto Discovery Wizard, define a subset of the source DB tables to be connected.

A subset is defined using a regular expression that matches the names of the tables to be included. By default, it is populated by the schema name of the selected DB.

Example 1: if the schema name is CRM_DB, then this field is populated by default by ^CRM_DB\.

Example 2: if the regular expression ^CUSTOMER is defined, the Auto Discovery Wizard searches for relationships only within tables whose name begins with CUSTOMER.

Enable Backwards Discovery Checkbox

 

By default, this checkbox is unchecked. When checked, tables identified by Auto Discovery as the parent tables of the selected Root Table are added to the LU schema as child tables and are not filtered out of the schema.

See the example in Step 4 below.

Medium Mode Parameters

These optional settings are only displayed when Medium Mode is checked in the left pane.

Column Aliases Collections

Defines the relationship between fields within different tables.  You can drag columns from the tables of the selected DB or edit this field manually.

For example:  to populate the alias using CUSTOMER_ID, CUST_ID, CUST_NO, Auto Discovery links between these fields even though they have different names.

Virtual Primary Keys

Fields that are not defined as the  primary key that are recognized and treated as a primary key by the Auto Discovery Wizard, in addition to the original Primary Key.

Primary Keys that are Skipped

Primary keys that need to be ignored or skipped by the Auto Discovery Wizard.

STEP 4: Check the ERD Diagram

Display the suggested ERD diagram and the selected Root Table (pink) and related tables.

Connections are colored:

  • Foreign key connection = blue.
  • Primary key connection = green.
  • Virtual key connection = olive.

If the selected Root Table is not defined as the root of the identified ERD, consider changing the selected Root Table or check the Enable Backwards Discovery checkbox. If not, the created LU includes only the selected Root Table and its subordinate table.

For Example image

The selected Root Table is CRM_DB.ACTIVITY. However, the Auto Discovery Wizard identifies the CRM_DB.CUSTOMER table as the Root Table of the CRM_DB.

When saving the CRM_DB.ACTIVITY table as a Root Table:

  1. If Enable Backwards Discovery is unchecked (default), then Auto Discovery creates the following LU and filters out the remaining tables of the ERD diagram.

image

  1. When Enable Backwards Discovery is checked, Auto Discovery creates the following LU and links the CUSTOMER table and its subordinate table to the ACTIVITY table.

image

Create the Logical Unit Diagram

Display the suggested logical unit diagram and click Finish to create the LU.

image

Optional: you can check tables out of the LU schema to create them as LU tables and add them manually to your LU if needed.

Previous