Training Module:

optimization

Learn how to you can utilize native features to efficiently optimize your data warehouse performance.

Table of ContentS

• Database Cleanup & Simple Mode
  
• SQL Database Cleanup Tool
  
• Simple Mode
• Table Performance
  
• Add Table Performance Setting
  
• Index Automation
• System Control Fields
• Performance Recommendations
• Version Control
  
• Instance Version Control & Rollback
  
• Perspectives
  
• Perspective
  
• Documentation & Lineage
  
• Documentation
  
• Data Lineage
  

Database Cleanup & Simple Mode

go to Table of Contents

SQL Database cleanup tool

When objects are deleted in TimeXtender, to prevent data loss, only the instance or "metadata" objects are deleted. This can result in many objects such as tables, views, or stored procedures that are left behind or "orphaned" in the database, taking up unnecessary space. To handle this discrepancy between the meta data and database we can use the SQL Database Cleanup Tool, which performs a scan of the database, quickly identifying orphaned objects and allows you to delete or "drop" these objects to free up space, from within TimeXtender Desktop.

1. Open SQL Database Cleanup Tool, from Advanced on a data warehouse.
   
2. The list will show objects in the database that are not a part of the project, or never were.
   
3. Select an object you'd like to drop such as a table or view
   
4. When you have dropped the all the objects you want to delete from the database, close the window.
   

If you have not yet deleted any objects from your instance, the scan may not result in any objects.

simple mode

Simple Mode in TimeXtender is a performance-enhancing feature that saves storage space when working with large tables without transformations. This mode offers only basic functionalities, retaining only the most recent instance of a table unless incremental load is enabled. By default, Simple Mode is not enabled, and it does not support transformations, validations, conditional lookup fields, Raw Table, _T View, _L Table, and _M Table.

enable simple mode

1. Open General tab under Table Settings on a table
   
2. select Enable under Simple mode.
   

Table Performance

go to Table of Contents

table performance setting

There are various methods to improve the performance of a table in TimeXtender, such as batch data cleansing, compression, and partitioning, but not all options are available for every table type. When using Azure Synapse Analytics for data warehousing, the Distribution option determines how data is distributed across the server's 60 distributions and compute nodes.

• Enable Physical Valid Table: Disabling the setting replaces the "valid" table with a database view, which saves storage space but may result in slower queries. This option is suitable for situations where storage constraints are a concern and query speed is not a priority.

• Enable Batch Data Cleansing: In TimeXtender, moving all Raw table records to Valid during execution can increase SQL log size and cause errors, especially for large tables. A batch data transfer feature can be activated to avoid this, reducing SQL logging overhead but slightly slowing down transfer rate.

• Raw and Valid Table Compression: While it can improve I/O intensive workload performance, it also requires additional CPU resources on the database server. If CPU resources are available but I/O is lacking, enabling compression can provide significant performance benefits. Compression can be applied to either Raw or Valid tables, but it is usually recommended to enable it for both tables..

• Row or Page-based Compression: TimeXtender offers two compression techniques: Row-based compression, which compresses each data row individually, and Page-based compression, which compresses data further using additional methods. Although Page-based compression offers the greatest space savings and I/O improvements, it also consumes the most CPU resources. For moderate space savings without significant CPU overhead, Row-based compression is a viable alternative.

• Table Partitioning: Partitioning is a technique that divides large tables into multiple units, distributed across multiple file groups in a database, to enhance performance. Queries that involve a single partition execute faster, and the partitions are invisible to external applications, so data organization on the hard disk is based on the specified partition type.

index automation

Index Automation considers several factors when designing indexes, such as table relationships with an Error or Warning relationship type, joins on conditional lookup fields, primary key fields on the raw table instance, selection rules and incremental selection rules on the data warehouse, and partitioning fields (DW_Partitionkey, DW_TimeStamp).

• Automatic (default) : The default setting in TimeXtender is automatic, which means that it will aim to minimize the number of indexes used. If there are two lookups that can use the same index, TimeXtender will make use of that. In general, the majority of users will not need to modify the default setting for index automation.
  
• Manual: In TimeXtender, users have the option to manually create indexes on specific tables, but TimeXtender does not handle the management of these indexes. Therefore, if any modifications are made to the table that have an impact on the indexes, no automatic adjustments will be made by TimeXtender.
  
• Disabled: Disabling the index automation in TimeXtender is possible, but it may not be the most optimized approach for performance since it could potentially result in the creation of the same index multiple times.
  

Table Level Setting

1. Open General tab under Table Settings
   
2. Select the options that apply to you in the Index Generation group.
   

System control fields

TimeXtender automatically adds four System Control fields on every table created. These fields can be leveraged for incremental loading, supernatural keys, and data lineage tasks.

• DW_Id: Stores an incremental integer uniquely identifying each row in a table which can be used as a surrogate key when needed. TimeXtender utilizes DW_Id as a clustered index so TimeXtender guarantees it to be unique.
• DW_Batch: Stores the batch or "execution number" within the table, linking each row with a specific execution.
• DW_SourceCode: Stores the name of the data source in which the row was originally generated from.
• DW_TimeStamp: Stores the date and time in which the row was populated.
  

Performance recommendations

The Performance Recommendations tool can analyze your project and suggest changes to improve its performance. It can merge conditional lookups, change lookup operators, align lookup operators, stop using deprecated options, change hashing algorithms, disable troubleshooting options, and verify automatic indexes. You can choose to apply some or all of the recommended changes, such as enabling differential and managed deployment to speed up deployment by only deploying necessary objects.

Performance recommended tool is recommended to use where there is an upgrade from a previous version.

Finding and Applying Performance Recommendations

1. On the Tools menu, point to Performance Recommendations and click Find. The Performance Recommendations window appears.
2. Review the recommendations in the Apply these changes list and clear the checkbox next to recommendations you do not want to apply. Mouse over objects in the Affected objects to see what issue has been identified for the object and the proposed change. Then click OK to apply the changes.

Version Control

go to Table of Contents

Instance Version COntrol & Rollback

Version control, known as source control or revision control, is a robust system that empowers data teams to seamlessly manage changes. TimeXtender makes it easy to open a previous version of an Instance, in just a couple clicks.

Perspectives

go to Table of Contents

perspective

The purpose of perspectives is to make it easier to work with an instance that contains a lot of objects. In instances with a lot of objects, it can be hard to maintain a good overview and find an individual object quickly. A perspective is a subset of the objects within an instance that relate to a specific area. In dynamic perspectives, any object that is related to an object already in the perspective will automatically be included in the perspective. When a perspective is active, all other objects are hidden. Perspectives can be static or dynamic, with dynamic perspectives any object that is related to an object already in the perspective will automatically be included in the perspective.

1. Open Perspectives in the Solution Explorer by right-clicking it and selecting Add/Edit Perspective.
   
2. Add and name the new perspective
   
3. Select the objects to include in the perspective. (optional) Select Dynamic to enable auto selection of dependencies.
   
4. Select OK to finish. When in use, you will see "(active)" after the perspective name.
   
5. Once complete right-click on the perspective and select Use Dynamic Perspective.

Documentation & Lineage

go to Table of Contents

Documentation

Generating comprehensive documentation for a data warehouse or an entire project can be effortlessly done with just a few clicks. This documentation comes in two variants: Full Documentation and Data Impact Documentation. Customization options abound, including the creation of documentation templates infused with your preferred style and branding annotations. In scenarios necessitating printable documentation for audits, regulatory compliance, and similar objectives, TimeXtender offers a streamlined solution, allowing swift creation of full data warehouse documentation at your fingertips.

Data Lineage

Data Lineage is a crucial tool that illuminates the origins of the data in the data warehouse or semantic model objects. Through clear visualizations, the native functionality showcases an object's path from the user's selection on the left to its sources on the right, encompassing any transformations or calculations. TimeXtender harnesses such visualization capabilities, aiding in the effective tracking of object dependencies and project growth.

Section Quiz...

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

What is the recommended method to remove objects that were deleted from a TimeXtender project, but are still visible in SQL Server?

Which of the System Control Fields can be used as a surrogate key?

What option creates perspective that includes object dependencies?

When you're ready, see Answers Below

Section Quiz Answers

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

What is the recommended method to remove objects that were deleted from a TimeXtender project, but are still visible in SQL Server?

By using the SQL Database Cleanup Tool.

Which of the System Control Fields can be used as a surrogate key?

DW_Id

What option creates perspective that includes object dependencies?

By using Dynamic Perspective

Congratulations! You've completed the training module

optimization