TimeXtender Desktop Q&A

I’m looking for a TimeXtender Fabric Lakehouse (pyspark.sql) expert 😀I’ve got a SQL transformation that selects the maximum value of 10 columns:(select max(val) from (values ([Column_1]), ([Column_2]), ..., ([Column_10])) as [Values](val))How do I achieve this in a Prepare Lakehouse custom transformation?I could build the mother/father of all massive case statements, but I’d prefer something simpler and more elegant … if possible!?

I have a client that wants to explore a significant rework of their Prepare instance. They want to duplicate their existing Prepare instance in Dev to create a sandbox instance. Is there a recommended approach to achieve this? They are using version 6898.1

Does anyone know exactly what happens when we ingest data from e.g. a SQL Server into Fabric Lakehouse? It seems concurrency impacts performance (more can be less) but there might also be Fabric assets in play that could act like bottlenecks, and which requires “capacity” management.Any best recommendations/practices in this space?

I am loading a table incrementally into my ODX storage in an Azure Data Lake, where new Parquet files are added daily. This approach is because the source only holds data every two weeks, and I want to maintain a log in the ODX. The Parquet storage is very compact.However, for downstream analysis, I only need to retrieve data from the last 1 to 2 days into my prepare instance. I am using a data selection rule on the mapping, and I have also tried applying it directly on the table. Both approaches take a very long time to complete (+1 hour), whereas running the same query on the source SQL database filtering for 2 days of data completes in about 10 seconds.I suspect that the prepare instance is scanning through all the Parquet files, including older days, causing the slow performance.My question:Is there a way to configure the TX prepare instance to only process the most recent X Parquet files (e.g., the last 2 days) instead of scanning all files? This would significantly improve the se

Version 7026.1 - Ingest into LakehouseI’ve got a SQL data source where some column names contain spaces. This causes the TX SQL connector (23.0.3.0) to throw the following error when I try to ingest data into a Lakehouse:Executing table dbo_tx_profitcentreinfo:failed with error:Found invalid character(s) among ' ,;{}()\n\t=' in the column names of your schema. Please enable Column Mapping on your Delta table with mapping mode 'name'.You can use one of the following commands.If your table is already on the required protocol version:ALTER TABLE table_name SET TBLPROPERTIES ('delta.columnMapping.mode' = 'name')If your table is not on the required protocol version and requires a protocol upgrade:ALTER TABLE table_name SET TBLPROPERTIES (   'delta.columnMapping.mode' = 'name',   'delta.minReaderVersion' = '2',   'delta.minWriterVersion' = '5')I can read what it says 😀 but I thought I’d ask the Community for advice. Has anyone encountered and resolved this issue? If so, what do you recommen

Hi,TX ver: latest I am getting an error when import metadata or doing table flattening. This worked fine earlier, before 3 june. Works fine using Postman. Any suggestion what I am missing? “One or more errors occurred.Failed to execute request for endpoint 'Get Charge Points'.One or more errors occurred.Cannot send a content-body with this verb-type.System.AggregateException: One or more errors occurred. ---> System.Net.ProtocolViolationException: Cannot send a content-body with this verb-type.   at System.Net.HttpWebRequest.CheckProtocol(Boolean onRequestStream)   at System.Net.HttpWebRequest.BeginGetRequestStream(AsyncCallback callback, Object state)” /Bjørn A.

I am fetching refresh logs from power bi using the following endpoint: https://api.powerbi.com/v1.0/myorg/groups/{groupid}/datasets/{id}/refreshes(https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/get-refresh-history-in-group#refresh)In my use case, the groupid is hard coded and I iterate over the datasetid {id} using dynamic values FromEndpointTable.The setup for this is the same as in this guide: My problem is that the dataset id is not included in the payload for the refresh log endpoint, so I have no way of knowing which power bi dataset has been refreshed.Could I somehow include the dynamic endpoint as a field in the result set? Or is there any other way to solve this?Best regards,Pontus B

Somebody can explain how to use the custom editor feature?Is it possible to use SSMS to edit the t-sql scripts?How must be setup it? 

HiLooking to quickly replicate an existing data pipeline in a new prepare instance. Is it possible to clone a table in one prepare instance into another, or somehow export a table from one prepare instance and import into another?I cannot see any option, so my guess is no, but just thought I’d ask.RegardsPaul

Suddenly all the excel sheets are not loading anymore however some of them no one touched 6 months ago.the error is:Executing table excel_sheet:failed with error:Exception Type: System.ExceptionMessage: Invalid column count             Stack Trace: at DataStorageEngine.DataLakeGen2.DataLakeGen2Transfer.<>c__DisplayClass41_5.<UploadData>b__14(DataTable dataTable, Partition partition)                          at DataStorageEngine.DataLakeGen2.DataLakeGen2Transfer.UploadData(ADLSGen2APIManager api, DataSourceModel dataSourceModel, DataLakeGen2ExecutionMethod executionMethod, Action`2 writeExecutionLog)                          at DataStorageEngine.DataLakeGen2.DataLakeGen2StorageEngine.<>c__DisplayClass35_2.<TransferDataAsync>b__7(DataLakeGen2ExecutionMethod executionMethod)                          at DataSourceEngine.Custom.CustomSourceEngine.InvokeDataSourceExecution(Action`1 action)                          at DataSourceEngine.Custom.CustomSourceEngine.RunExecu

Dear Sir,Our client is running TimeXtender 6963.1.So want a rowcount, count(*) on our tables loaded in de ODX.Our ODX is configured with ADSL (Azure Datalake Gen2 Storage).So we want to use the ODX TimeXtender Parquet Data Source connector (23.1.1.0 64bit).Is it possible to run a count(*) query to our tables when we setup this connection to our ADSL?We need to know how many records are loaded in all of our tables.We have a lot of incremental tables, so we worry of all the increment files in the lake. Do we need to configure some sort of a aggregation ?Is there documentation available how to configure this ? The connector has a lot of options, we don’t know all the details.Hope somebody has some experience with this Parquet connector.Thank you in advance for looking into this.Regards,Arthur

Hi,We've updatet our Data Integration version from 6766.1 to 6963.1. Which means we had to update our outdated providers. So we followed the steps mentioned in this post Change data source provider | Community (timextender.com).This results in an error when we try to full load changed data source. It keeps giving is the error: “The schema …. already exists on the SQL storage and cannot be used” Which actually means we can’t update our providers/ data sources. We have to create new ones en have to map all our tables in de odx. Anyone else facing this problem and have a solution for this?

I have a table X in the DSA layer from TX and I can't find the script to get the right output.  There is a field FCL that possible have multiple records with different YearFrom and YearEnd. FCL   YearFrom   YearEnd1        2010           20201        2021           20502        2010           2050  I need a data selection rules that only imports from the ODX layer the FCL with the highest YeardEnd group by FCLFCL   YearFrom   YearEnd1        2021           20502        2010           2050  Is there anyone who has a suggestion. The output from is table is userd as lookup table for another table in the same DSA layer. 

I'm currently loading Parquet "tables" in TX Classic with the following setup: Using Synapse Serverless to read Parquet files and expose them as external tables Connecting via SQL Server authentication This works, but I’m looking for a better approach.Is there a way to read Parquet "tables" directly in TX Classic? The Enhanced Data Source seems limited to reading or aggregating files, not treating them as tables.Alternatively, could I replace Synapse Serverless with Fabric Serverless? The main blocker is authentication—TX doesn't appear to support SQL providers with service principal (OAuth) authentication, only SQL Server authentication, which we’d prefer to avoid.Any suggestions, recommendations or workarounds would be appreciated. 

Hello,TimeXtender version 20.10I have a customer that previously had only one project (lets call it Project A). However they have now taken out certain parts of that project into new ones (Project B and Project C).Previously to be most effective we could run everything in project A in one execution package. Now with there being 3 projects in total this can’t be done (as far as I know). B + C needs to complete before project A can run. We want to run it as part of one scheduled trigger so the only way I have found to do it is as follows:Project B runs => when done triggers project C package with external step => project C package runs => when done triggers project A package with external step.Most optimally would be for B + C to run at the same time and then trigger A, but we do not want to create a complex custom solution with command prompt/powershell scripts or building logic off the repository which is the only way I can think of achieving that.Anyone have a similar case th

Hello,I am trying to setup an endpoint to PowerBI Premium in version 20 (20.10.58)  The connection is setup as below, and it tests OK (See pic).When I Deploy I get the error:Failed to save modifications to the server. Error returned: 'Either the database with the ID of 'TXFinance1' does not exist in the server with the ID of 'host002_datasets-231', or the user does not have permissions to access the object.The Semantic model is created fine in PowerBI, but nothing inside of it.Is there a setting I am missing? The stange thing is that it appears to work for a colleague in the same workspace using the same app credentials, but not on my machine  Many thanks,Tim

This is a simple Deploy and its been running for almost 20 minutes.Whats wrong here?  

Hi! We are currently setting up TimeXtender with Snowflake and have a bit of an issue when it comes TimeXtender not having the capability of managing the ODX in Snowflake (internal stage).  As you know an external stage is needed via Azure Data Lake Gen2.  The issue for us is that we would like to use private endpoint when creating this data lake in Azure but that requires business critical subscription to Snowflake which is 30 percent more expensive.   Are there any plans/timeline for when TimeXtender will be capable of handling the Ingest instance in Snowflake and if so could you please give a rough estimate on when that might be available?  We need to make some decisions on our end on how to proceed and would like to know the answer before we move forward.

As title suggests, I wonder if anyone in this community has experience with ingesting TX with the data source Adnamics and their API. Thanks in advance!

Hey, I use the REST connector to consume a CSV file. In my query parameters I got one for auth key. So when I try to load the file a get an error and the reason is that parameter get encoded (% → %25) .  How can I avoid encoding in the query? /Bjørn A.  

Hi, I am facing an issue with duplicate records when using incremental load on a table that receives data via a table insert from another table in the same data area. The incremental load selection rule is based on DW_TimeStamp, and there are three PK in the target table.The issue is that the primary key does not seem to be enforced in the target table, as I am getting duplicate rows in the table. I tested this with another table where data is inserted through a table insert (but without incremental load), and I still see duplicate values for the PK fields and despite setting the table option to "Use instance setting (error)", no error is thrown when duplicates occur.  I have tested this behavior in TDI 6935.1 and 6926.1, and the issue persists. My questions are:  How do primary keys work with table inserts? Should they prevent duplicates, or do they not function as expected when using table inserts? Can incremental load be achieved using table inserts? If so, what are the necessary

​Hi,I’m sorry for this somewhat lengthy post but I have a problem that I don’t really know how to solve and I’m just looking to see if anyone have run into the same issue, or maybe already solved it.We are going to fetch data from a time management system that have a SOAP API and all the information I find in the community refers to RSD files, that is not to be used anymore. I don’t know how I would solve the problem that I have even with an RSD file, but that’s somewhat beside the point here. 😊The issue is that we must take the request XML, encrypt the whole thing using AES, into a fingerprint that in turn will need to be inserted into the actual request that is then sent to the API.I have received some code in C# that does the following steps:Generates a timestamp and an MD5 hash of the request XML Generates the fingerprint XML using the timestamp and the newly created MD5 hash with the schema like in the attached picture fingerprint_schema Encrypts the fingerprint XML with AES, usi

I am trying to replace the CDATA Dynamics365 adapter with the TX REST connector (8.0.0) and I am having some issues where empty values from the API is interpreted as empty strings rather than NULL and as a result creates tables with incorrect datatypesHow do i set up a way to replace these empty strings with NULL values and keep the correct data types as wellI have tried with various combination of “Search and replace" on the endpoint as well as the individual nodes in the table builder without any luck.I also have the “Empty fields as null” option enabled.  

Dear Sir,In TX version 20 you had a configuration setting Direct Read.I cannot find this setting in the latest and the greatest version in TimeXtender.Question: Is Direct Read still available ? How can I set this up ?Thanx in advance for looking into this. The "Direct Read" option in TimeXtender is particularly useful in scenarios where you need to transfer data between different data areas within the same physical database but using different schemas. Here are some specific scenarios where you might use the Direct Read option:  Large Data Volumes:  When dealing with large tables, Direct Read can be more efficient as it allows for direct data transfer within the same database, reducing the overhead associated with external data movement tools like SSIS or ADO.NET.  Incremental Loads:  Direct Read is beneficial for incremental loads where only a subset of data needs to be transferred based on specific criteria (e.g., timestamp or modified date). This can help in optimizing performanc

This is the error I am facing when executing any Prepare package (no issue with executing Ingest packages) from the Orchestration portal: ​​​​​​Error I have tried resolving the problem with troubleshooting below, but I still get the same error:  Suggested troubleshootingAny further suggestions on how to handle the issue?We run Data Integration 6935.1 and Ingest 6935.1 Thanks