Training Module:

table transformations

Learn different ways you can optimize and perform table transformations.

Table of ContentS

• Schema Management
  
• Schema Behavior
  
• Use Schemas to separate Valid tables
• Create ETL Schema as Default and set MDW as Valid Schema
• Aggregate Tables
  
• Aggregate Tables
  
• Aggregate Tables for Monthly Sales by Customer
  
• Table Inserts
  
• Table Inserts
  
• Table Insert Selection Rule
  
• Add Related Records
  
• Handle ‘Early Arriving Facts‘
  
• SQL Snippets
  
• Snippets
  
• Add a Snippet to Transform Null to ‘None’
  
• Implement the SQL Snippet
  
• Custom Tables
  
• Custom Tables
• Add Custom Tables
  
• Custom Code & Scripting
  
• Add a Stored Procedure (SP)
  
• Execute Stored Procedure
• Add a User-Defined Function (UDF)
• Execute User-Defined Function
• Add Custom View as Table Insert
• Add Customized Code to Table
  

Schema Management

go to Table of Contents

schema behavior

Database schemas enable the application of a specific schema to a table or a set of tables. They are useful for limiting access to tables that are not necessary for reporting purposes, thus simplifying the process of creating reports from the data warehouse. Schemas can be applied to data warehouses, data sources, and individual tables. The schema settings are applied as following table level settings taking precedence over data source settings, which in turn take precedence over data warehouse settings.

Following are the Schema Behavior options available :

None

Main default schema

Main Raw default schema

Main Valid default schema

Main Transfer default schema

Main Error/Warning default schema

use schemas to separate valid tables

TimeXtender will utilize the schema with the Main Default Schema behavior as the destination for any ETL process. The Main Default Schema represents the default destination for newly created tables during the ETL process. In contrast, the Main Valid Schema is applied to all valid tables in the data areas, and it helps to conceal all the tables that are involved in the ETL process, thereby ensuring that other users can only view the relevant items.

create ETL schema as default and set mdw as valid schema

1. Right-click-> Database Schemas- > Add Database Schema under MDW Data Area.
2. Enter ETL and create the schema
   
3. Once created, select Schema Behavior as Main Default Schema for ETL
   
4. Select Main Valid default schema for MDW
   

Aggregate Tables

go to Table of Contents

aggregate tables

The aggregated table is a condensed version of another table in the instance, where transactional-level data is not required for financial or sales reports. This feature is beneficial for reporting directly from the data warehouse. By creating aggregated tables, analysts can quickly access crucial customer data, and for advanced reporting, a proper data warehouse or front-end reporting system is required. However, aggregate tables are effective for highly formatted or ad-hoc reporting with minimal development time.

Here is the list of Aggregation Types available

• Min: The lowest value of the field in question.
  
• Max: The highest value of the field in question.
  
• Count: The number of rows.
  
• Count_Big: Same as count, but is able to count higher than 2^31, because it uses the bigint data type instead of the int data type.
  
• DistinctCount: The number of unique values in the field.
  
• Sum: The sum of all row values.
  
• Average: The average of all row values.
  

Aggregate table for monthly sales by customer

Create an Aggregate table for monthly sales by customer

1. Select Add Aggregate Table under Advanced on DSA.SalesOrderDetail
   
2. Enter MonthlySalesByCustomer as name for the aggregate table
   
3. Select CustomerId and OrderDate (value as Month) GroupBy parameters
   
4. Select LineTotal Aggregate parameter and Aggregate Type as Sum.
   
5. Finish the table with OK.
   
6. Deploy and Execute.
   

Table Inserts

go to Table of Contents

table inserts

The process of inserting rows of data from one table into another is called Table Inserts. It is a simple concept that allows mapping of data from one source into a table. Additionally, there are other important functionalities, such as Incremental Loads, available. You can create SQL unions using Table Inserts

Add a Table Insert

1. Add TransactionHistory and TransactionHistoryArchive to DSA
   
2. Select Add Table Insert from Advanced on TransactionHistory
   
3. Provide Transactions as Name, Insert from table - TransactionHistoryArchive and Data Destination Table (i.e. Raw or Valid)
   
4. You may use the Auto map function to speed up the process
   
5. Alternatively, you may select each mapping manually in the Field Mapping area.
   

Add a table insert selection rule

1. Select Add Table Insert Selection Rule from Table Inserts on your destination table.
2. Select the Operator you wish to use to select the data.
   
3. Add the Value to select the data. Click Add.
   
4. Deploy and Execute the table.
   

add related records

The "Add Related Records" functionality is a table transformation tool that allows for the creation of records in one table based on information from another table.

A common scenario where this may be employed involves the consolidation of disparate data. This includes the insertion of records from one table into another when a corresponding key value does not already exist. Handling Early Arriving Facts is a common use case of adding related records

Add Related Records

1. Select Add Related Records under Advanced on the destination table
2. Provide a Name, and make selections the other drop down menu (i.e. Create Records from Table, Record Condition, and Data Destination Table)
   
3. Select your choices in the Field Mapping area, providing the mappings, and value parameters as needed.
   
4. Select Add near the Conditions field below to add conditions.
   
5. Select OK, to complete the process.
   

handling early arriving facts

The "Handling Early Arriving Facts" allows for the handling of transactional data that may contain values not yet added to the source database. When this occurs, the data will fall under the "Unknown" member for the corresponding dimension. TimeXtender offers a solution to this problem by allowing for the display of partial information until the missing data is properly added to the source database. This feature prevents data from being placed in the "Unknown" member and will populate all fields for the record once the missing data is added to the source database.

SQL Snippets

go to Table of Contents

snippets

Snippets are reusable pieces of code used in data warehouses for tasks such as field transformations. Using snippets saves time and effort in maintaining the same functionality across multiple fields and speeds up the development process.

SQL snippets can be used in the following ways -

• Field level transformations
  
• Views
  
• Stored procedures
  
• User Defined functions
  
• Script actions
  

Read more on snippets

Add a snippet to transform null to none

1. Select Snippets under the Tools menu.
   
2. Choose the SQL Snippet with Add.
   
3. Provide a descriptive Name and Provide a Description of the snippet.
   
4. Enter the script in Formula field to replace Null and empty strings with None.
   
5. For each parameter, highlight the parameter with the bracket in the Formula and click Add Parameter.
   
6. Select the Type field for each parameter, and then click Add.
   

implement the sql snippet

1. Right-click DSA.Product.ProductCategoryName select Add SQL Snippet Transformation and select the snippet
2. For the field parameter, drag the ProductCategoryName from the right side onto Object Name/Value
3. Click OK
4. Repeat steps for DSA.Product.ProductSubCategoryName
   
5. Deploy and Execute

Custom Tables

go to Table of Contents

custom Tables

The custom data tables feature allows for manual population of a table by entering data directly or importing it from an Excel spreadsheet. It's important to note that data imported through this method is static, meaning it will not update if changes are made to the original spreadsheet.

Add custom (DATA) Tables

1. Right-click the desired table select Advanced.
   
2. Select Custom Data from the options.
3. Enter or type the data manually for the fields in the cells.
1. Alternatively, use the Import Data button to import data from an Excel file.
   
4. Select OK, to complete the process.
   

Example Use Cases:

TimeXtender's Custom Data feature, using custom tables, is like a tailor-made solution for organizing and managing your data warehouse. Think of these tables as blank canvases that you can shape according to your specific needs:

1. Boolean Value Translation: Transform "0" and "1" into descriptive terms for clearer data interpretation. This makes your data easier to understand at a glance.
2. Row-Level Security Setup: Use custom tables to import security configurations, enhancing controlled data access. It's like creating a VIP list for your data, ensuring only the right people see sensitive information.
3. Data Persistence: Maintain consistent data throughout your project with custom table inserts. Imagine you're working on a long project. Custom tables help keep your data looking the same from start to finish, avoiding confusion and errors.

In short, custom tables in TimeXtender are a flexible tool for those who need to tweak their data warehouse to fit unique requirements, whether it's making data easier to read, securing sensitive information, or keeping data consistent over time.

Custom Code & Scripting

go to Table of Contents

Custom Code And Scripting

Custom code and scripting in your data warehouse is an option, best saved for special cases where your requirements. Considerations for using customized code in your data solution include the following:

1. Managed Execution: Custom code's impact on TimeXtender's task execution, based on object dependencies, needs careful consideration.
   
2. Code Maintenance: Keeping customized code in check might demand more dedicated effort compared to leveraging the platform's built-in tools.
   
3. Documentation: Thoroughly documenting custom code becomes paramount, as it might not inherently offer the same transparency as TimeXtender's native features.
   
4. Data Lineage: The inclusion of custom code could introduce complexity to data lineage, a critical component of data traceability and auditability.
   

Stored PRocedures (SP)

add a stored procedure (SP)

1. Open a data area, and then right-click on the Scripts folder and select Add Stored Procedure.
2. Enter the script for the Stored Procedure. Drag in the necessary objects from the right-hand pane, such as table and field names. Click OK.
3. Right-click on the stored procedure and select Deploy.

Execute a stored procedure (SP)

1. In a data area, right-click on Scripts and select Add Custom Step.
2. Enter SQL script for executing the SP. Drag in the necessary objects from the right-hand pane, such as the SP. Click OK.
3. Right-click on the table, select Advanced, and then Set Pre- and post scripts.
4. In Set Pre- and post scripts window, select Custom Step from dropdown menu for either the Pre-Step or Post Step column, depending on which one is appropriate for this custom step. Click OK.
5. Deploy and execute the chosen table, which will run the Custom Step and execute the Stored Procedure.

User-Defined Functions (UDF)

Add a Custom View as a Table Insert

1. First create the Custom View, then right-click the view and select Map Custom View fields.
2. Drag the custom view onto your data warehouse
3. Provide a Name for the table
4. Deploy and Execute to enact the changes

Add Customized Code To Table

1. Right click the table > Advanced > Customize code.
2. Click the "Add" button to the right of the step to be customized, which will open the "Choose Editor" window.
3. In the Editor Name list, the following options are available:
1. Standard is the basic built-in editor in TimeXtender.
   
2. Default File Program is the program that is set to open files of the type in question. For the data cleansing procedure and the transformation view, the file name extension is .sql.
   
3. Any custom editors that have been added
   
4. Select the desired editor from the list and close
   

Section Quiz...

Planning to take the Solution Architect exam? Then you want to be sure these questions are easy to answer.

In what contexts or scenarios can SQL snippets be utilized in TimeXtender?

What option is available for performing a SQL union operation?

What are the common use case for add related records?

When you're ready, see Answers Bellow

Section Quiz Answers

Planning to take the Solution Architect exam? Then you want to be sure these questions are easy to answer.

In what contexts or scenarios can SQL snippets be utilized in TimeXtender?

1. Field level transformations
2. Views
3. Stored procedures
4. User Defined functions
5. Script actions
   

What option is available for performing a SQL union operation?

1. Table Inserts
2. multiple mappings on the same tables
   

What are the common use case for add related records?

Handling early arriving facts and consolidating disparate data.

WANT TO LEARN EVEN MORE?

Learn even more about Slowly Changing Dimension from our TimeXtender Tuesdays

Congratulations! You've completed the training module

Table Transformations