Prepare

Hi team, I have a table in a data-area that is filled by a single mapping set, in this case containing 7 tables. Because we discovered some differences between the fields we need from each table, I want to convert it to a normal mapping. I tried to do this via ‘Smart Synchronize’, but it is only working for fields that have not had a name change. In other words, when choosing to smart synchronize a single table onto a table with a mapping set, it seems to perform a ‘Synchronize (only existing fields)’ instead of looking at the name of the fields in the mapping. Is this expected behavior?Kind regards,Andrew

Hi,I have a very regular table that is loaded from a SQL Datasource. The table has a little over 3 million records and the following definition:  Nothing out of the ordinary as you can see. The table is loaded with the ODX with an incremental selection rule on te mut_dt column. The ODX load works fine; loading the table full or incremental is no problem and the data is stored in the container as expected within 1 or 2 minutes.However, when I drag the table to the DSA data area and load it there, something strange happens. Loading the table to the DSA hangs after 900.000 records are inserted into the raw table. We have tried waiting for several hours, but it just hangs there and nothing happens. Other tables from the same source load fine to the DSA.Data source version is TimeXtender SQL Source v 19.1.1.0 (but load from source to ODX works fine)ODX and TX version is 6590.1 .

Good Day,  We are receiving the following error messasge: Finished executing project 'COMPANY' Execution Package 'Client execution'Execution failedStart Time : 5/24/2024 7:57:22 AM End Time : 5/24/2024 7:57:22 AM on server: NAME-Execute Execution Package Client execution 'Failed'    The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.        Details:        SQL Server: 'NAME'    SQL Procedure: ''    SQL Line Number: 1    SQL Error Number: 242        The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.    Module: .Net SqlClient Data Provider    System.Data.SqlClient.SqlException       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)       at System.Data.SqlClient.TdsParser.TryR

In my source table, I have a column called [lastSync] that is varchar.In my history table I have last_sync as datetime.Currently the only way that I know of how to solve this is to eitherCreate new Custom field [last_sync] in my source table that includes TRY_CAST([lastSync] as DATETIME) The problem here would be that I would need to change my Source table from Simple mode. I would like to keep the simple mode table. Data redundancy as this specific column is stored twice. Create a new Custom field [lastSync] in my history table to accomodate the data before [last_sync] uses Custom Field Transformation to cast to datetime. The problem here would be redundancy as I’m storing the same column twice over a long period. Of the two I would prefer option 1.Is there no way to do this on the fly, so that data is converted when transferring data from the Source Table → History Table?  I’m Using TimeXtender Business Units 20.10.32.64

Hi Everyone, I was playing around with Clustered Column Store indexes to see if it would help increase performance of reading data from our DWH. It didn't help so i wanted to revert to the old situation by removing the index. However, on deploy i get an error message and i'm not sure i'm doing something wrong here.I basically just created a Clustered Column Store index, deployed deleted it again and deployed. On the last deploy i get this error message (so the problem is in deleting the index somehow):An error occurred during update table. See exception details for the failing object: Exception occurred during execution of RebuildHeap process. An exception occurred while executing a Transact-SQL statement or batch.. failed for Table 'MDW.FactHistStockManagementData'.This is not a valid data compression setting for a columnstore index. Please choose COLUMNSTORE or COLUMNSTORE_ARCHIVE compression. I've ran the profiler against the deployment and it seems the error is coming from this sta

I have a database role on my DSA which cannot be adjusted or removed. When I delete the role and deploy I get an error: “An error occurred during drop role. See exception details for the failing object: Drop failed for DatabaseRole 'SBIE'. The database principal owns a schema in the database, and cannot be dropped.”.What does this mean and where should I look to solve this issue. In the end I want to have this database rule deleted. Perhaps I could do it outside TX, but I want to check the options in TX first.

When having an historic snapshot table in TimeXtender MDW, based on a table insert, I need to limit the table somehow to prevent it from growing too large. I want to limit the [Date] field for only 1 year of data. Because it's based on a table insert, there are no fields available in the table to use for a table “Data selection rule”. I can only place a data filter on the table insert which only has data of 1 day.My historic table works like this: Actual table (snapshot of today) Historic table (table insert, daily, of actual table, so it stacks up the daily snapshots)(TX version 20.10.43.64)

As you know in TX you can have a lotof data areas that in the SQL data base are schemas.In SSMS you can group the objects in folders based on their schema using this addon:https://github.com/nicholas-ross/SSMS-Schema-Folders Hope this helps. 

I’m using Timextender version 6521.1 and I get an error when using hard deletes on a incremental table. If I choose no deletes it works just fine, soft deletes also throw me and error. As far as I could tell from these notes incremental loading should be fully supported. Things I have checked:Primary key on the table in the ODX Incremental load is setup in the ODX Incremental load is setup on the Snowflake table. I’m not quite sure if this is just a bug or I missed something obvious. This is the first table i’m trying to load incremental in Snowflake. This is the error it throws at me:System.AggregateException: One or more errors occurred. ---> Snowflake.Data.Client.SnowflakeDbException: Error: Error parsing the parquet file: Invalid: Parquet file size is 0 bytes  Row 0 starts at line 0, column   If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, plea

Hi,We have been using ADF for transferring data from our ODX storage account to Azure SQL database for a long time but now our manual and scheduled reloads get stuck in ADF.It looks as if TX does not pass the runtime to ADF. This is required because our storage account is behind a private endpoint so we need to use an IR that resolves in our VNet in Azure.Do you have any idea why the runtime does not appear on the pipeline? As you can see there is no runtime assigned to this run:  Normally the runtime appears in ADF:  We are on TX 20.10.45 by the way.Would love to hear from you!

Hi all,My first post here. My company is currently using legacy TX but I started poking around the new version. I was a bit surprised to find out that passwordless authentication methods are not supported yet. Are there plans to support managed identity authentication in the legacy or new version?Our company security policy requires password expiration even on service accounts so you can imagine the hazard to update them in many places regularly.TX MDW SSMS 

Current advice on partitioning on a non-date field calls for creating a partitioning “bucket” using field transformations. While attempting to follow the instructions outlined here I encountered a problem. Partitioning works by creating a calculated field in the valid table that has the capacity to handle NULL values if the partition template is so configured. However, that same field, along with the calculated default value, is also present in the Raw table. This means that when anyone following TimeXtender’s guidance for non-date transformations executes the table, that execution will fail with the error “Cannot insert the value NULL into column ‘DW_PartitionKey’”Is this a bug, or does the documentation for creating partitions need to be updated? 

Good morning All,We are encountering the following:We have history tables that use the DW_SourceCode as part of the primary key. This is because we have multiple sources with the same structure which are merged together in one table after ODX. As primary keys can overlap in the different sources, we need the DW_SourceCode to make distinguish the data coming from one or other source. Also we need history as the sources have a tight cleanup system and we need to keep track of information ourselves. While developing some of the tables, the DW_SourceCode was somehow not included in the primary key in the first deployment and this was corrected afterwards. TimeXtender also reflects this: Although deployment was successful, the computed column definition of the surrogate hashkey was not altered: The problem we're having now is that the history is matched based on the surrogate hashkey. Now wrong records are updated (type 1 history) because the hashkey matches but actually shouldn't because t

One of my colleagues made a change to an incremental field on a very large table, not realizing that this would force a full load. We only realized what had happened halfway through our promotion, but since we needed to make changes to that table anyway, we proceeded. Reloading that table and its downstream tables took five days. Luckily, a long weekend greatly reduced the impact of that reload on our users. We have now begun our next promotion cycle.  Unfortunately, despite the fact that the table in question was reloaded in our development environment, differential deployment is again forcing a full reload of that table. We really can’t afford to spend a week reloading this table a second time. We’d love to know why the flag remains flipped in the first place, how we can avoid this happening in the future, and how we can avoid this extremely costly reload, as it is blocking our ability to promote urgently needed changes into production. 

TimeXtender beings almost all tasks by checking to see if the objects involved currently exist. It does this by querying SQL Server system views. However, this does not take into account a very unusual property of system views: querying them naively can result in SQL Server attempting to get a lock on the underlying system metadata tables. When it does this is wildly inconsistent - sometimes it will try to get a lock on the table when doing only a simple SELECT, other times it will only attempt to get a lock when certain joins are used. This isn’t really documented anywhere I’ve been able to find, but I have observed it in action many times. The problem is that SQL Server schema changes write to those metadata tables, which also locks them. And as long as the change is occurring, the tables stay locked.  This becomes an issue when rebuilding indexes. This is a very frequent occurrence curing TimeXtender execution as it happens whenever a table is fully reloaded. For large tables, this

v20.10.35.64I’ve got a History table with ± 16 million rows in PROD and ± 2.3 million rows in TEST. To increase loading times I tried to delete some columns of the table, first in TEST. Here I was able to successfully Deploy & Execute the History table with Differential + Managed deployment, which finished in several minutes.However, when I try the same procedure in PROD it keeps stuck on the step Deploy Valid Table Structure as shown below. I also tried to Deploy with Differential Deployment unchecked, but with no result. It just keeps loading forever.Anybody an idea? 

Hi,We are setting up a new Azure environment for a client and we are having some issues with the Azure SQL DB.The app server VM is based on the one available in the Azure marketplace:https://azuremarketplace.microsoft.com/en-us/marketplace/apps/timextender.timextender-app-server?tab=OverviewThe issue occurs when we try to test the storage connection for the MDW instance. When we try to use Azure AD integrated or password authentication we get the error that adalsql.dll is not able to load when we test the connection in the desktop app.Azure AD integrated authenticationAzure AD password authenticationThe URL leads to a 404 not found: http://go.microsoft.com/fwlink/?LinkID=513072I found these two posts about AAD integrated auth, but it is not clear to me if it is possible or not to get it to work:I didn’t find any discussions about the Azure AD password authentication.Are these not supposed to work as authentication methods for a DW instance deployed in an Azure SQL DB? If they are, is t

Hi all,  I am relatively new to SQL. Although with help and searching, I am making progress, I am not succeeding with the following question. Based on a date, I can easily set a filter in my reports to look ahead for x period of time. However, when a holiday comes into view, my report becomes blank. For this, I am looking for an index/row_number/sequence that skips the holiday dates. Can I set up a Custom Field for this within my BaseCalendar?

At a customer, we are developing in a team of 2 on  TimeXtender 6536.1 .The Team development feature is switched on and, like in the 20.10.nn.mm  version, I would expect the refresh button to bring out any deployments that my colleague makes and vice versa.But this doesn’t happen so. Only by closing an instance and re-opening it, do the deployed modifcations appear.Are we the only ones experiencing this? Or are there others?We know that you can use workitems to show others what you’re working on, to avoid working on the same objects, but would expect a refresh to also show new items, if one depends on the  others work to proceed. 

I am trying to convert TX to single database and stuck here-- Set the source databaseUSE DVH_PROD_ODX; -- Source databaseDECLARE @SourceDatabase NVARCHAR(MAX) = 'DVH_PROD_ODX'; -- Source databaseDECLARE @SourceSchema NVARCHAR(MAX) = 'ODX_TZ'; -- Source schema in the source databaseDECLARE @TargetDatabase NVARCHAR(MAX) = 'DVH_PROD_MDW'; -- Target databaseDECLARE @TargetSchema NVARCHAR(MAX) = 'ODX_TZ'; -- Target schema in the target database-- Declare variables for table name and dynamic SQLDECLARE @TableName NVARCHAR(MAX);DECLARE @SQL NVARCHAR(MAX);-- Create a cursor to loop through tables in the source schemaSET @SQL = N'DECLARE table_cursor CURSOR FOR ' + N'SELECT t.TABLE_NAME ' + N'FROM INFORMATION_SCHEMA.TABLES t ' + N'WHERE t.TABLE_SCHEMA = ''' + @SourceSchema + '''' + N' AND EXISTS ( ' + N'SELECT 1 ' + N'FROM '+ @TargetDatabase + N'.INFORMATION_SCHEMA.TABLES t2 ' + N'WHERE t2.TABLE_NAME = t.TABLE_NAME ' + N'AND t2.TABLE_SCHEMA = ''' + @TargetSchema + '''' + N')';EXEC sp_executesql

Hi!We have installed TimeXtender and ODX on an Azure VM (TimeXtender 6536.1). They are both running in ADF.I have installed TimeXtender Desktop locally on my own computer (outside of the VM) and I’m able to log in and open the instances. However, when I try to execute a table I get this error:-Execute Execution Package Execution Queue Default 'Failed'    No DNS entries exist for host <nameofVM>.    No such host is knownI am connected to the same network as the TimeXtender VM ('nameofVM') through a VPN connection.Is this setup not supported or am I missing some network configurations?

Hi!I want to download TimeXtender Desktop 6521.1 (not the newest one). Where do I find it?

I've created view, and successfully deployed and executed, but while previewing the table it says timeout error, but that view I can open it in SSMS server, it takes 2 minutes to execute the view in server.what are steps or setting i should change to preview it in Timextender.

Hi, I am in the proces of migrating old Power BI managed data sources to our data warehouse in TimeXtender. However, in power query I use a function called List.Dates to create a row for each data between two dates. The data in this situation is sick days. Example below of what I need it to become: Sickdatefrom SickdateTo Sickday 1-1-2024 3-1-2024 1-1-2024 1-1-2024 3-1-2024 2-1-2024  In summary: I need to be able to add the Sickday column in TimeXtender just like I do in Power Query. I couldn't find anything online related to this subject. I am reading all thsis data from a CSV file by the way.

TimeXtender Version 20.10.35.64I'm integrating 6 SQL databases/sources from the same ERP at the moment and want to set up incremental loading on my data source & DSA. I followed the basic steps but it seems like refreshing the incremental DSA tables takes way longer than a full load.The ODX datasources incremental LOAD tasks from all 6 data sources finish under 60 seconds, but the DSA load for e.g. AP_Products table now takes almost 15 minutes, for ± 40.000 rows, against 3 minutes for a full load.  My Last modified DateTime column is ‘Date_Captured’ and is applied on the tables below. For all the 4 tables the PK's are set up.  In the DSA I included column ‘Source’ in every table, which represents DW_SourceCode, to the PK of the tables to have an unique identifier per row. However, it looks like I'm doing something wrong or i’m missing a step, since it takes forever to refresh these DSA tables now