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
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.
SQL Database Cleanup tool will only identify the tables that you have deleted from your data areas in your data warehouse instance
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
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.
Table performance Setting
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).
Table Level Setting
In TimeXtender, the index automation setting can be adjusted at the instance level by editing the instance and selecting the preferred option from the dropdown menu. Additionally, users can set the index automation to manual for a Data Area by right-clicking on it and selecting "Advance" and then "Manual."
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.
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.
Read more on performance recommendation tool
Finding and Applying Performance Recommendations
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.
rollback to previous Instance Version
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. 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.
Leave the Dynamic perspective checkbox unchecked creates a Static 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.
Generate Documentation
Documents can be generated for the instance under Tools -> Generate Instance Documentation or from your data area
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.
Generate the data lineage for a custom field
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