TimeXtender Desktop Q&A

Hi,I normally use Custom transformation (CASE statements) when I need to populate a field based on multiple conditions, in this case multiple “OR” conditions (Logical disjunction) . It was brought to my attention not to use CASE statements in Custom transformations. So I have below an example using a fixed value transformation but I want to add two conditions to populate only when it is ‘FL’ or ‘OL’. When I add these conditions the conditions are on separate lines but still TX makes an “AND” logic of it so instead of (like ‘FL%’ or like ‘OL%’) it translate it to (like ‘FL%’ & like ‘OL%’). How can I get the “OR” instead of “AND”?   

We are facing an issue with one of our clients in which we built out a UAT environment to promote proper development but have come across an issue where we have a fully built TX Semantic Layer on the production environment, but we do not have it built out for the TX UAT environment. The reason behind this was because the test Qlik Sense environment was not available initially, and we could not successfully execute packages on the TX uat environment, so we decided to work without it on TX uat. Now that we have an operational test Qlik Sense environment, we want to export the semantic layer from TX production to TX uat environment. Is there a way for us to do this in TimeXtender? We are trying to avoid large amount of manual work to build this out on the TX uat environment and we dont want to export project from Prod to UAT as that will override all our work in UAT environment. Prod TX Semantic LayerUAT TX Semantic Layer  

Hello,I have a history enabled table with a lookup field. The lookup field is marked as type I (update record). However, the record is only updated with the new values in the table from which the lookup field is retrieved, if the record is available in the raw variant of the history table. If the record has been updated in the mean time, the lookup field is never triggered to be updated.Is there a way to have the lookup fields be updated in a history enable table without the record being present in the raw table?I have currently created a work around by not truncating the raw table, but this causes an unnecessary increase in the size of the raw table and produces many primary key violation errors.Looking forward to your answer!

I was interested in this link.Data directly from Data lake to MDW (without ODX) | Community (timextender.com) I am not able to access this link which was posted in the below of the message.  https://support.timextender.com/timextender-product-focus-group-123/virtual-odx-capability-to-read-directly-from-existing-data-lake-container-no-second-copy-614 Please advice,  

Hello.I have a scheduled execution package which takes data from ODX, transforms data in DSA database and loads final tables to MDW. This execution package basically runs a perspective containing this process.I need to setup following logic:If data in any source table (ODX) of this execution package is empty (i.e. at least 1 empty table) Do not execute the package If data in all source tables (ODX) of this execution package contain any data (i.e. all tables non-empty) Execute the package Is there any way how i can setup this? Maybe even create additional “control” execution package? The version of TX i am running is 20.10.34.64 Thank you!

Hi all,I have a client that has a datalake with parquet files in an Azure Storage Account Blob container (Datalake Gen2). This datalake has not been created by ODX server, but has been created by Synapse (scripted, delta lake parquet files). Since we want to load multiple tables from multiple folders we use the CData Parquet connector v2023 (TX 20.10.41). The issue we have is that we can’t find the right settings for the connector to recognize that the folder we’re connecting to contains multiple tables and that the parquet files are in a subfolder of the table folder. For the connection we use a Shared Access Signature and the connection is working:The name of the container is ‘development’ which we configure in the Base URL. In the URI we point to the folder of the bronze layer, the system and the database: The database folder contains a folder for each table. In each table folder some tables have a a partition folder followed by the actual parquet file. Since the connector needs to

Hi all!We have created an external executable to run a python script, basically looking like this: cd F:\Data\Start-Process python GetData.pyexitNext we have added this to an execution package. If we run the package it will open a prompt and run the python script no problem.However if we add the package to a job and schedule or manually run that job nothing happens. How can we get this to work?cheers,Maarten

I need to make a field transformation such that if the field value starts with “DOK”, then I want to replace the value with “1001”. I tried to set up this custom field transformation using something along the lines of CASE WHEN …, but didn’t manage to get this to work. How would I write this code in TX?

We have a historic table and we use Dw_id as an unique indentifier. But when we update the record the DW_Id will stay the same, so we have duplicate values in the table on that field then. the original DW_id field is changing but the transformantion field isn’t.  Because we work with custom data aswell that we want to have there own value we work with the custom transformation. I will  provide some screens from the issue:   Can you provide me a way so the field eenheidssleutel will be unique over time and is equal to the DW_ID now and in the past of the historic table. 

I have below the API request for Power Shell. Now I want to set it up in TX.  How to set up (Basic) Authentication for a request with a token retrieve?    # Force the use of SSL [System.Net.ServicePointManager]::SecurityProtocol = [System.Net.SecurityProtocolType]::Tls12 ####################### # SETTINGS                                                                       # ####################### # REST API SETTINGS # $API_key = "HIDDEN" $API_username = "HIDDEN" $API_password = "HIDDEN" $API_company = "HIDDEN" $API_URL_Login = "https://secure.xxxxxxxxxxxxx.eu/ta/rest/v1/login" ####################### # LOGIN AND RETRIEVE TOKEN FOR UKG Ready                                         # #######################          Write-Host "### Retrieving token"            $headers_login = @{   'Content-Type' = 'application/json';         'Accept' = 'application/json';         'API-Key' = $API_key;      }     Write-Host "- Headers prepared"         $request_login = '{"credentials": {"username": "'

Hi, I've been trying to add new excel file from a new sharepoint group from the same company link to the same ODX server by using CData connector. Is there any setting to add both sharepoint links or make it visible? Now, I can only see the link with group1.sharepoint page examples;https://company.sharepoint.com/sites/group1https://company.sharepoint.com/sites/group2Best!

Anyone know in what TX repo table I can find the field and value for “Guard on deployement” and “Guard on execution?I know where the setting is in the UI, but need to look up the value in the repo db.

Hi can you add a diagram or something like that to understand when and where that scripts are executed? 

v20.10.35.64A while ago I was looking for a solution on how to conditionally fill down/flash fill values with certain criteria. Unfortunately the answers in this ticket didn't do the trick for me, as I kept getting NULL values with all solutions I've tried. How can I conditionally fill down/flash fill NULL values with previous values based on certain criteria? | Community (timextender.com)Now I seem to get it working with a Self Lookup view and a Lookup Field (Top first-non value). However, I have to repeat this process for almost 10 columns. The loading of this table went from 06:00 to 30:00 minutes. The lookup field looks as follows. What it does is:Look if column Route Bron is empty, If Route Bron is not empty, just show Route Bron. If Route Bron is empty, lookup the TOP first non empty Route Bron Value with the same Source, Project, Cardcode where project is not empty and not 999999 Is there a way how I can optimize this process? Thanks in advance!

Hi,I'm using the CData ADO.NET Provider for OData 2022 (22.0.8322.0) to extract Topdesk data.Due to performance reasons Topdesk made some changes in their feed. Resulting in that, since 19th june 2023, 2 larger tables (IncidentDurationBlocks and IncidentSnapshots) cannot longer be extracted without filtering on a date to make the dataset smaller.How can I add a filter for only one table in my extract?Can I create an OData RSD file?I get this error when I try to extract:System.Data.CData.OData.ODataException (0x80004005): [500] Could not execute the specified command: Unexpected response status code: 503.  ---> rds220I.IC: Unexpected response status code: 503

In fact tables we are using subrogate keys from the dimension tables using supernatural keys.The relationship between the fact table and the dimension table it’s using the bussines key and getting the subrogate key from the dimension table into fact table. But some times it doesn’t exists, and I’m using -1 to put that value.What it’s the best way to include the unknow member (-1) in the dimension table?

When we creating a numeric field in a MDW table, TX suggets use a numeric(38,6) for the data type.When the field it’s translated to the semantic model it’s detected in TX:But in the model:And one of the best practice rules says “Do not use floating point data types”.How can I follow this rule?   

Hi,we have a customer running incremental loads multiple times a day in the ODX Server (using Data Lake Gen2).  We’re reaching the 10.000th incremental run.I’m wondering how the ODX server will handle that 10.000th increment (since the naming of the parquet files allows a number up to 9999).BR.Peter

Hi community, The CDATA Provider for LinkedIn 2022 contains a bug/error which causes it to call an invalid API URI for the connector’s table ‘OrganizationFollowerCount’. The appropriate URI for this table according to Organization Network Size - LinkedIn | Microsoft Learn is:https://api.linkedin.com/v2/networkSizes/urn:li:organization:1234567?edgeType=CompanyFollowedByMemberHowever, CDATA is calling the following:2023-06-12T11:31:21.793+02:00 2 [5|Q-Id] [HTTP|Req: 3] GET https://api.linkedin.com/rest/networkSizes/1234567?edgeType=CompanyFollowedByMemberWhich results in the following error message:2023-06-12T11:31:22.172+02:00    3    [5|Q-Id]    [HTTP|Res: 3] {"message":"Key parameter value '1234567' is invalid","status":400}2023-06-12T11:31:22.172+02:00    2    [5|Q-Id]    [HTTP|Res: 3] HTTP/1.1 400 Bad Request, 66 Bytes Transferred2023-06-12T11:31:22.194+02:00    1    [5|Q-Id]    [EXEC|Messag] Executed query: [SELECT [FirstDegreeSize], 0 AS [odx_batch_number] FROM [LinkedIn].[Organiz

In Custom data insert i have one field which is varbinary(8000) data type. When i try to insert a column i get an error: The given value of type String from the data source cannot be converted to type varbinary of the specified target column.Failed to convert parameter value from a String to a Byte[].Invalid cast from 'System.String' to 'System.Byte[]'.Details:Invalid cast from 'System.String' to 'System.Byte[]'.Module: mscorlibSystem.InvalidCastExceptionat System.Convert.DefaultToType(IConvertible value, Type targetType, IFormatProvider provider)at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming)Failed to convert parameter value from a String to a Byte[].Module: System.DataSystem.InvalidCastExceptionat System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming

I have deleted a schedule on a execution task but it still executes. There is no schedule and no execution plan.How to solve this?

Hi community,A customer is using a specific role that is allowed to select data from specific views in SQL. When one of these views are deployed, the Object Security Setup  isn't deployed. The Database Role/Security Settings needs to be redeployed manually.Is it possible to automatically deploy the Object Security Setup, if the a view/table is deployed that is using security settings?  

Hi all,We’re trying to troubleshoot some thorny networking issues, and I need to know more about how ADO.net data transfer works behind the scenes - specifically for old-school business unit SQL Server data sources, if that makes a difference. I know for sure is that data is read from the data source and written into the destination table in the staging database, but I’m fuzzier on what happens between the two places. To start off with, I have two key questions: We’re using a tool server to run TimeXtender, and a different server to host SQL Server and the TimeXtender databases. Is data routed through the that tool server when it is read via the ADO.net package? I assume that it is, but we need to know for sure. The other question I have is what happens after the data is pulled from the data source? Is it pulled into memory somewhere? If so, where? The source server, the tool server, the destination data server, or some combination of the three? Is the data ever written to disk anywher

I see this advanced menu option in all the tables but I don’t know how can it be used. 

Hi,I am working on a column that evaluates a date against GetDate() and returns a 1 or 0. The table can be loaded incrementally. I assumed that if I turned on "Keep field values up-to-date", this column would always be recalculated at loadtime, but it seems that this is not true.Is there a way to make these two work together or do I have to give up on incremental loading?Thanks