Training Module:
table transformations
Learn different ways you can optimize and perform table transformations.
Table of ContentS
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
The schema will be applied to the tables you manually assign it to.
Main default schema
The schema will be applied to all tables and views in the region
Main Raw default schema
The schema will be applied to all Raw (_R postfix) tables in the region
Main Valid default schema
The schema will be applied to all Valid (_V postfix) tables and views in the region
Main Transfer default schema
The schema will be applied to all Transfer (_T postfix) views in the region
Main Error/Warning default schema
The schema will be applied to all Link and Message (_L and _M postfix) tables in the region
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
At the table level, you have the ability to modify schemas. This can be accomplished by adding or setting the desired schema through the drop-down menu located in the table settings.
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
Aggregate table for monthly sales by customer
Create an Aggregate table for monthly sales by customer
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
Add a table insert selection rule
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
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 -
Add a snippet to transform null to none
implement the sql snippet
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
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:
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.
For more information about custom data tables, please see the knowledge base article “Custom Tables“.
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:
Stored PRocedures (SP)
add a stored procedure (SP)
Stored Procedures (SP) are stored in the SQL Server, and compiled the first time that they are run. The execution plan can be reused when running the SP several times, which means faster execution compared to Script Actions.
Execute a stored procedure (SP)
The Stored Procedure (SP) must be scheduled to run before or after the deployment & execution of a table, which can be configured or edited by using the Set Pre- and post scripts dialog.
User-Defined Functions (UDF)
add a user defined function (UDF)
Execute A user-defined function (UDF)
The User Defined Functions (UDF) must be scheduled to run before or after the deployment & execution of a table, which can be configured or edited by using the Set Pre- and post scripts dialog.
Add a Custom View as a Table Insert
Add Customized Code To Table
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?
What option is available for performing a SQL union operation?
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