TimeXtender Desktop Q&A

Hi I am just testing our V21. I have started with something simple, a 2 column CSV file with an integer key field and a text group code field. There are 18 rows.I can load it into the ODX fine (running on local SQL Server db) and I can preview the contents in the ODX “Select Tables” Pane.I can drag it to a Data Warehouse Data Area and it creates and maps the table fine with appropriate field settings.When I deploy the table in the Data Area that works fine.When Executing I get an error on the transfer which does not tell me what is happening. The error I get returned is as follows: Transfer table failedDetails:Transfer table failedModule: timeXtenderSystem.Exception   at TimeXtender.DataManager.StepTransferExecute.DoDataWarehouse(IStepSetup stepSetup, List`1 mergeODXTransferInfromation, VariableResolveObject dynamicResolveObject)   at TimeXtender.DataManager.StepTransferExecute.DoAbstractStep(IStepSetup stepSetup) That means nothing to me and I cannot determine what I can do to correct

I am curious why system fields like SCD is Current & SCD is TombStone are datatype bigint.  Is there a way to change them to tinyint? As far as I know they can only be 0 or 1. 

I am setting up a table relation where the tables join based on a date range (see screenshot below)In setting this up in TimeXtender, I noticed that I can only set this up on the lookup field and not as the default relation between the tables.Does anyone know of a way this can be setup on the default table relation? Or can we had as an enhancement for TX?(NOTE: I have edited the table names and columns for this post) 

In my DSA I have my Business Partner Master Data. I want to add a field that named Intercompany that shows ‘Intercompany’ when BP Group Code = 104 or 109 and else show Extern. BP Group Code has a smallint datatype. I added a custom field with the CASE WHEN statement. However, the extra field does not show the right results.When executing the CASE statements SMSS everything works fine, but when I implement it in my DSA, it does not work correctly.I tried all kinds of CASE statements with e.g. Like,OR statements, strings, however it does not show the desired result, but rather tons of nulls.The screenshot below shows the intercompany field with the Nulls and some tests CASE statements.   

Hi all,Too many parameters in nasa api data available year and month. I changed their name via RSD file because  and have only one table like below;  I have to change this columns in this dataset from this  station WS10_MAX201901 WS10_MAX201902 WS10_MAX201903 …..    WS10_MIN201901 WS10_MIN201902 WS10_MIN201903 …... to this station      MM.YYYY       WS_10_MAX         WS_10_MIN  How can I do it? I tried Custom Table Insert but doesnt work. I tried also Junk Dimension but I had to clone my dataset 12 time to be able to print  for example 12 months 2019 data consecutively.  

I'm following the steps explained on this page (https://legacysupport.timextender.com/hc/en-us/articles/360034695651-Connect-to-a-REST-API), but I can't figure out.  Situtation: I have to get for all station weather data from DW. An example for stationnummer 10184 like this:https://s3.eu-central-1.amazonaws.com/app-prod-static.warnwetter.de/v16/forecast_mosmix_10184.json You can find the all station list here:  https://www.dwd.de/DE/leistungen/met_verfahren_mosmix/mosmix_stationskatalog.cfg?view=nasPublication&nn=16102  What I did?first I set up a dataset for the sample station 10184 and got the ‘days.rsd’ file. Then I made some changes in ‘days_changed’ file(it doesnt work). And I coudnt generate it.    

When associating instances to an Execution Server in 6221.1 I noticed that the message displaying locked instances has a slight grammar mistake: “The following instances that currently locked by another machine” should probably be something like “The following instances are currently locked by another machine”.   

Hi community,I have a client attempting to load a postgres database into ODX Server (TX version 20.10.x). The table contains a field with dates that are invalid for SQL but valid in Postgres, for example 20011/01/01. Upon transfer, the task completes with errors that it is unable to parse datetime value. All datetime fields have been converted to VARCHAR(MAX) using Override Datatypes and this is confirmed by looking in the ODX Storage in SQL Server Management studio. Since the error still presents itself, it seems likely that the error is coming a step earlier, during the CDATA conversion. Unfortunately, it seems that there is no way to generate RSD/schema files from the CDATA Postgres provider. Normally I would change the datatype there in these situations, since it seems to be going wrong in CDATA somewhere. How can I load this table? Kind regards,Andrew - E-mergo

Currently it’s not possible to create a job to concatenate the transfer task of data source (d365 f&o) to ODX and from this to DSA.I would need to get the age of the data in ODX to transfer that value to somewhere in DSAHow can I do this?

While running trials of TimeXtender it is fairly common to run against SQL Server Developer Edition. As SQL Server 2022 has been GA since last November, this is the version people will generally be installing.Finding 2019 is fairly hard. I have noticed that the 20.10.37 ODX Server is perfectly happy, but that TimeXtender 20.10.39 gave me an “Unsupported version of SQL” error, even with databases running in SQL 2019 compatibility level. Is this supposed to be the case?Azure SQL DB is 2022 under the hood, even if SELECT @@VERSION states something else.

When I opened the project today ODX showed this error.Another ODX service has the lock to the project. Please run the configuration tool to take over the lock.What could it be the reason?

We have set up a db2 connector in an installation with the new version of Timextender. The connection is ok.When we make a search to select tables, nothing turns up. Both the schema and table filters are set to <All>.It seems that Timextender searches the schema [sys_tables], but the server uses the schema [systables]. Without the underscore.The schema [sys_tables] exists on the server, but contains no data. I created a Query Table (Select * from [sys_tables]). It was executed succesfully in a Transfer task, but only returned empty columns.Is it possible to change the script so that the query uses the schema systables?I couldn't find anything about it in CData's online manualThe Timextender version is 6143.1, and the CData DB2 version is 22.0.8389.9

Hi Community,I'm trying to connect the database from CBS (in both old and new version of TX). The original request URI gives an error response that the query exceeds 10.000 rows at test connection. This is a well-known issue with data from CBS, so I adapted the URI to include a filter. I tested this new query both in Postman and Qlik Sense Desktop and there the filter works and I retrieve aprox. 2.500 rows, from 2020 until 2022. Using this query in TX does not work unfortunately. The error that normally encounters when testing connection does not pop-up, however, the table still retrieves 10.000 rows. Probably just random from all available years (1995 t/m 2022).I am using below request URI:https://opendata.cbs.nl/ODataFeed/OData/70072NED/TypedDataSet?$filter=substring(Perioden,0,4) ge '2020' and substring(Perioden,4,2) eq 'JJ'with custom header: accept:application/jsonAny ideas what causes this? Thanks!

At one of our customers we have a separate cloud for testing software / infrastructure changes. We are running 20.10.35 and want to evaluate 20.10.40. I would like to be able to upgrade the ODX Cloud repository for only one of the projects/environments instead of upgrading both to 20.10.40 and needing to risk production issues. Is this possble / can this be requested?

I'm following the steps explained on this page https://support.timextender.com/data-sources-112/connect-to-excel-files-with-excel-online-628#Connect+with+the+Excel+Online+CData+provider, but I couldnt get it. I used Microsoft SharePoint Excel Provider.I have a 48 Excel file. I uploaded them to my company's onedrive. I then gave OAuth to Cdata. But I took this error. ‘You must specify an Excel file: Set the FILE property to an .xlsx file.’ The file url address where the excel files are located like this:https://companyname-my.sharepoint.com/:f:/g/personal/xxx_companyname_onmicrosoft_com/xxxxxxxxxxxxxxxxxxxx Now I have written this above address in the 'URL' section under 'Authentication'. Is this true? Or should I specify in the 'Folder' and 'File' sections in the 'Connection' section? Which Auth Scheme should I choose?

Dear Sir,For one of our customers we need to connect with TX to a Datalake (CSI Cloud).Our customer is able to connect with PowerBI to their Datalake with a JDBC → ODBC Bridge piece of software from ZappySys JDBC Bridge Driver. This works fine.Now we need to connect with TimeXtender without this third party JDBC Bridge Driver software to save our client some ZappySys license money.So I checked with the CData component in TimeXtender, but it seems their is no JDBC → ODBC Bridge Driver available...Question: Is their a way to do this with the best Data Management Platform in the world ?Hope you can shine a light on this.Thanx in advance. Regards from Amsterdam,ArthurFourpoints TimeXtender Partner

Hi,We dont always get the notification e-mails when execution fails. Do you know what could be causing?Thanks.Kerem 

I have a table with a table Insert from a view (actually multiple views) underneath it. The problem is that this table sometimes is empty because the Data Lineage is wrong. It seems that we need to have Object Dependencies for Table Inserts. 2 Questions about that:  When creating a "Table Insert" get a message that "Object Dependencies" are required? Is it enough to check the next child view (in my case) in the "Object Dependencies" if you have one table insert? Or should it be the first child table? Or should all underlying views/tables be included?

Hi community,  I have to design the following issue, every day, I have a new CVS file in the Azure storage account. These file’s name change with a new day, for exampleFormaExtract.net_new_commission_2023-03-01.csvFormaExtract.net_new_commission_2023-03-02.csvFormaExtract.net_new_commission_2023-03-03.csvHow I could create an automatic process to load a new file to the table in DSA? Now I will do it manually adding every day a new file an process the data.ODX DSA Another requirement is to create a new column SourceMainTable in the DSA table with the file’s name. RegardsIgnacio

We have a Dynamics 365 Finance and Operations system, where we have the Data-lake export running (for over 1 year). THe setup is similar to Josephs sketch solution for the following question:Dynamics 365 F&O Data Lake as a TX source | Community (timextender.com)We have it running quite stable on one of our boxes, but are struggling with stability on a pre-production box.The goal is to use Azure AD Integrated Authentication, and the user running the scheduler has been given access to the Serverless SQL Database (tested in SSMS)First we experienced some missing prerequisites when using Azure AD auth:Execute ODX d365fo_dl.ACOJournalTable_BR ADO.NET Transfer:Error:Failed      -Execute ODX d365fo_dl.ACOJournalTable_BR ADO.NET Transfer 'Failed'          Unable to load adalsql.dll (Authentication=ActiveDirectoryIntegrated). Error code: 0x2. For more information, see http://go.microsoft.com/fwlink/?LinkID=513072                    Details:                    SQL Server: 'import-d365fo-data

Dear Reader,Dynamic Row Level Security (DRLS) can be implemented by defining a table that contains 1. values for the column to be secured; 2. defining the users email-addresses.With this specific case, two DRLS columns are defined.This is shown as depicted in the first picture below:The first column (_Retailer_Key).The second column to be secured is also dynamically defined (_CountryAccess_Key):This works, however suppose I have 4 retailer country combinationsCountry Retailer UK YourOwn NL YourOwn UK Theirs GE Unsere  And the _CountryAccess_Key that was defined by DRLS was “UK” and the Retailer was YourOwn, then the Result is that the rows with the red font is returned. This is just (_CountryAccess_Key OR _Retailer_Key). However that’s not the result required. The result should be (_CountryAccess_Key AND _Retailer_Key) as depicted below.Now the result only returns one row (depicted in the red font) Country Retailer UK YourOwn NL YourOwn UK Theirs GE Unsere

Dear all,Do any of you have a way of guarding Views in the data warehouse?I have some views which I for archiving purposes rather not delete, but this is giving me issues when I want to deploy the project as a whole.Thanks!

In my data source a new field [geregistreerd] is added.After synchronizing the data source and a transfer, the field is available in the ODX Go to the MDWSelect the mapping of the table  The field is not available in the Data Movement  Add a new field in the MDW tableAdd a transformationThe field is available in the Data Fields   Why is it not available in the Data Movement?

I'm trying to setup ODX critical error notification. I've used the same settings as the regular TX notifications, but the test email never arrives. I do notice that in the ODX I do not have the option to enable SSL/TLS, which is needed. I'm using smtp.office365.com.

I have loaded a table with some initial data  (== initial stoock). The key is quiet simple  Company, Warehouse and ItemNow I want to update the amounts every day so that it reflects the actual stock level of data dayThe initial load was done from one source , the updates come from another sources History settings  have natural jkey set , and all non lookup fields are updsated  (type 1)Nevertheless the record is not updated Any idea ‘why’ the update is not working