Skip to main content
To homepage To homepage

TimeXtender Desktop Q&A

Ask questions and find answers about TimeXtender Data Integration and TimeXtender Classic desktop applications

742 Topics
anders.e.jonsson
Contributor
anders.e.jonssonContributor
asked in Jobs & Executions
SSL execution fails when run in a Job

Hi,We have a SSL execution that works properly when started from the SSL Executions panel (right click - Execute).But it fails when executed from a Job. The message in the Job log:The log entry in the SSL Execution panel: Version 6221.1 Any ideas?BRAnders

1227
Christian Hauggaard
Community Manager
sven.debie
Contributor
sven.debie
asked in Prepare
Incremental loading on table using a custom table insert with a union

I have 3 tables in my src data warehouse:src.SalesOrderItemsDelta, this table gets filled every day with a delta (changes today vs yesterday) of our order lines. Via    src.SalesOrderItems, this table gets filled every Saturday night with all the order lines available.   src.preFactSalesOrderItems, this table gets filled via a custom table insert with the following table insert: SELECT [SalesOrderItemID] ,[SapClient] ,[SalesOrderNumber] ,[SalesOrderItemNumber] ,[SalesOrganization] ,[DistributionChannel] ,[Division] ,[FaboryArticleNumber] ,[SoldToCustomerCode] ,[BinCode] ,[CreatedOnDate] ,[CreatedOnDateID] ,[ChangedOnDate] ,[ReasonForRejectionCode] ,[PromisedDeliveryDate] ,[PromisedDeliveryDateID] ,[CommunicatedDeliveryDate] ,[CommunicatedDeliveryDateID] ,[PlantCode] ,[SalesAmount] ,[SalesCurrency] ,[ItemCategoryCode] ,[OrderedQuantity] ,[BinQuantity] ,[SalesOrderCategoryCode] ,[SalesOfficeCode] ,[ConfirmedDeliveryDate] ,[ConfirmedDeliveryDateID] ,[CommittedDeliveryDate] ,[CommittedD

2998
Thomas Lind
Community Manager
E
Participant
EvidaXremParticipant
asked in Jobs & Executions
Environment Transfer, Deploy and Execution dependencies

We have a setup with 3 environments (Dev/Test/Prod) running in the legacy version, with a BU Based ODX, DSA, MDW and Several SSLs. Based on a on-prem SQL Server setup. Fairly common I guess.We are multiple developers on a shared project, making changes daily to the project, and therefore needs some QA process. Our target is to have changes and new functionality running on Test for a week before transfering to production. In addition to out centralised BI org, we support the data needs of analysts in the different departments. For this we have established replica databases of ODX and MDW. The analysts can read these replicas without interfering with the centralised data processing (The primary reason for the replicas). Along with the data read access, we have a database the analysist have the rights create objects in (typically views and stored procedures). The analyst environment will only be exposed on our prod platform.We would like to provide a better SLA for our Analysts for new ta

2555
rory.smith
TimeXtender Xpert
S
Contributor
sguntupalliContributor
asked in Ingest
Duplicate tables in ODX Storage

I am working on Sandbox Environment.I on using ODX 20.10.36.64.I executed tables on 25th and today 27th in ODX DataSource.What I noticed is in my SQL server new tables were created.Do I need to update any settings to resolve this?SQL server 

742
S
Contributor
R
Explorer
roland.peetersExplorer
asked in Ingest
CDATA Exchange Server Connector

Is there someone who has a quick reference card how to create the connection with an exchange server using the CDATA connector.  We tried several settings with the server, but nothing works. 

702
Christian Hauggaard
Community Manager
G
Explorer
gerrit.degleeExplorer
asked in Ingest
Load csv from API

I've got an API that returns JSON data. One of the fields contains a UUE encoded csv file. Is there a way to load this to the ODX without some external pre processing?Thanks,Gerrit

914
Christian Hauggaard
Community Manager
Stefán Gunnar Jóhannsson
TimeXtender Xpert
Stefán Gunnar JóhannssonTimeXtender Xpert
asked in Jobs & Executions
Custom Query not working

I’ve created a query table that suddenly started to fail. When I press Validate everything is fine.I can even preview the table in the sourceI can also run the query on the source. But when I execute the table, with the same user as above I get this message:  Here’s the query:SELECT        t1.[TRANSACTIONCURRENCYAMOUNT]      ,t1.[ACCOUNTINGCURRENCYAMOUNT]      ,t1.[REPORTINGCURRENCYAMOUNT]      ,t1.[QUANTITY]      ,t1.[ALLOCATIONLEVEL]      ,t1.[ISCORRECTION]      ,t1.[ISCREDIT]      ,t1.[TRANSACTIONCURRENCYCODE]      ,t1.[PAYMENTREFERENCE]      ,t1.[POSTINGTYPE]      ,t1.[LEDGERDIMENSION]      ,t1.[GENERALJOURNALENTRY]      ,t1.[TEXT]      ,t1.[REASONREF]      ,t1.[PROJID_SA]      ,t1.[PROJTABLEDATAAREAID]      ,t1.[LEDGERACCOUNT]      ,t1.[HISTORICALEXCHANGERATEDATE]      ,t1.[CREATEDTRANSACTIONID]      ,t1.[RECVERSION]      ,t1.[PARTITION]      ,t1.[RECID]      ,t1.[MAINACCOUNT]      ,t1.[MODIFIEDDATETIME]      ,t1.[CREATEDDATETIME]      ,t2.ACCOUNTINGDATE      ,t2.DOCUMENTNUMBER  

1987
Christian Hauggaard
Community Manager
E
Contributor
ebayContributor
asked in Ingest
The bcp client received an invalid column length for column ID 1.

Hi all, I create a datatable from Json file with REST data source but got an error as below. ‘The bcp client received an invalid column length for column ID 1.’ Since the the column is zero based I look for the data in second column in my json file. I have ensured that the first and second columns are not read in the RSD file. However, the problem still persists. I believe the issue may be due to the data in this column being too large. This is API: https://archive-api.open-meteo.com/v1/archive?latitude=51.51&longitude=-0.13&start_date=2018-01-01&end_date=2023-04-15&models=best_match&daily=weathercode,temperature_2m_max,temperature_2m_min,temperature_2m_mean,apparent_temperature_max,apparent_temperature_min,sunrise,sunset,shortwave_radiation_sum,precipitation_sum,rain_sum,snowfall_sum,precipitation_hours,windspeed_10m_max,windgusts_10m_max,winddirection_10m_dominant,et0_fao_evapotranspiration&timezone=Europe%2FBerlin How can I solve this issue?  Thank you

8459
Thomas Lind
Community Manager
K
Contributor
KCMTContributor
asked in Prepare
How can I conditionally fill down/flash fill NULL values with previous values based on certain criteria?

I would like to be able to flash fill down NULL values in my DSA table with certain conditions.In the table below I have multiple NULL values. Take for example the column ‘CardCode DUAL’.Row 2 with Company key MTW and project 1201121979 shows for CardCode DUAL DB0006. I would like to show value DB0006 also for all other rows where company key = MTW and project = 1201121969.Same for Route Bron column. I would like to fill down NULL values on the most recent NON Blank value for that Company_Key+Project combination.I think it should be possible with a self join or self select, but not sure how.   

16245
Christian Hauggaard
Community Manager
M
Starter
Michael SuppanStarter
asked in Prepare
Duplicates loading data from ODX to MDW

Hi community,we are facing the problem, that we create duplicates when we bring data from an ODX API source to the MDW. We are working with an overlapping sliding window of two days in the schema file (because data can change and there is no last modified date) and only set the primary key on the ODX source.In the MDW (dedicated SQL pool) we enabled the history and set the ID as natural key. All fields are marked as type 1 fields.The execution brings us duplicated ID values - no updates are madeThanks for your helpMichael

1996
Christian Hauggaard
Community Manager
M
Starter
malknStarter
asked in Prepare
Obfuscating data

We are using Business unit to land our data in the DW. We have a few columns where we need to obfuscate some of the data.At what point should a script action be placed to update the field in the raw table prior to the data being moved into the valid table, so our script does not need to include an update to the field in both the raw and valid table. the script uses UPDATE table SET column

452
Christian Hauggaard
Community Manager
K
Contributor
KajEmergoContributor
asked in Prepare
TX crashes when adding 'Additional Connection'

Hello,We have a data source that contains several additional/stacked connections in our Business Unit. We now wanted to add the 4th additional connection (so in total 5 data connections), but TimeXtender crashes when clicking on OK after configuring the connection. I think it tries to connect to the data source and get meta data for the tables it needs to select but somehow this fails. Found this in the Event Viewer:  The errors are as follows:.NET = System.OutOfMemoryExceptoiin Application Error =  Faulting application path: C:\Program Files\TimeXtender\TimeXtender 20.10.38.64\timeXtender.exe Faulting module path: C:\Windows\System32\KERNELBASE.dll Application Error = same as 2.)We are on version 20.10.38, do you have any idea what might be causing this? Why don't we get a proper error message in TX interface?Connection to the data source through SSMS is working without any issues by the way.Best regards,Kaj

552
Christian Hauggaard
Community Manager
K
Contributor
KCMTContributor
asked in Prepare
Most optimal way to check if Value of Table A, Column A is also in Table B, Column A

I’ve got the following user request but I am not sure what the most optimal solution is, as i can think of many possibilities.I’ve got 2 Facts Tables:DSA.Fact_Turnover DSA.Fact_TransportOrdersBoth Facts contain the ‘Project’ column. The user request is to finally get the Turnover for all projects which are in Fact_TransportOrder. What is the most optimal way to get a check column to see if the Project value of Fact_TransportOrder is also present in Fact_Turnover and why? I want to use this filter column to eventually only present him the Turnover for projects which are also present in the Fact_TransportOrder.I’m really curious, as I already have such solutions, but am not sure if there are more easy TimeXtender functions or tricks which i’m not using currently.

703
tld
Contributor
K
Contributor
KCMTContributor
asked in Ingest
ODX SQL Transfer task failed SQL, but Preview & Sync successfull

After connecting successfully to my SQL server and adding the tables & rows I am able to preview the data by Select Tables. The Synchronize task also completed successfully. However, when I execute the Transfer task it fails with 2 errors (see attachments). I can’t figure out the reason why it fails though but the error message suggests something that (The specified path does not exist). I tried to remove the File Container in Azure and sync+transfer again, but it does’nt make a difference.  

1566
K
Contributor
R
Visitor
rholmesVisitor
asked in Jobs & Executions
Triggering job to start after another job completes

We have 2 jobsJob 1 runs at 11pm and normally runs 1 hour. Job 2 runs at 1am and must run after Job 1 completes, this job usually runs for 6 hours. On occasions Job 1 runs 3 hours and this causes job 2 to miss it start window. How can I adjust the schedule so that Job 2 has 2 criteria?   1-Job 1 completes   2-Has to start after 1am

672
AvanWijnen
Employee
bas.hopstaken
TimeXtender Xpert
bas.hopstakenTimeXtender Xpert
asked in Prepare
Object security, allow execute of stored procedures on specific database schema

Hi community,I created a role in the MDW and added Grant permission on one specific database schema. This schema contains some tables and some Stored Procedures. This role need to be able to select data in the tables and execute the stored procedures. I created my Object Security Setup using these settings:  When I login using SSMS (using the user in the role), I see the correct tables and I'm able to select data in the tables. But I'm not able to see the stored procedures. When I check the permissions on the ‘ExcelAddin’ database schema. Only the ‘select’ permission in granted. Is it possible to grant permission for ‘execute’ as well? Using TimeXtender?

674
bas.hopstaken
TimeXtender Xpert
K
Contributor
KajEmergoContributor
posted in Ingest
CData Excel Online not showing shared Excel files

Hi,We are using the Excel Online connector which is authenticated with a service account and uses delegated permissions to access Excel files on Sharepoint. The idea is that all relevant files will be shared with this account and then loaded into our DWH. The required files are visible on Sharepoint when siging in with the user:But for some reason, they are not when using the Excel Online connector (with option Show shared documents = ‘True’). I know that the connector uses the /SharedWithMe (OneDrive) call to fetch shared items, since that is what I inferred from the logging.This call indeed retrieves no results through the Graph Explorer, but the files are visible through another, similar, call on the Graph Explorer (the one from ‘Insights’): Why are files visible on Sharepoint but not on OneDrive? Is there a way to work around this? I have seen use cases where they actually are visible on both Sharepoint and OneDrive and the connector is working properly.

26120
Thomas Lind
Community Manager
rory.smith
TimeXtender Xpert
rory.smithTimeXtender Xpert
posted in Ingest
CData IBM DB2 - extracting results in The column [xxxx] does not exist

TX: 20.10.39ODX: 20.10.37ODX Storage: SQL Server DB on-premDSA/MDW: SQL Server DB on-prem We are successfully able to connect to a DB2 system and synchronize, we can also successfully use the Data Source Explorer to query tables. Every table we try to extract fails with the following error (schema/table/column names obfuscated): Executing table [schema].[table]:failed with error:edo220W.gm: The column [column] does not exist   at edo220W.ai.GetOrdinal(String )   at System.Data.CData.DB2.DB2DataReader.GetOrdinal(String name)   at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServerCommon(Int32 columnCount)   at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServerAsync(Int32 columnCount, CancellationToken ctoken)   at System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader)   at DataStorageEngine.SQL.SQLStorageEngine.TransferWithBulkCopy(SqlCommand destinationCommand, String destinationTableName, IEnumerable`1 columnModels, IDbCommand transferCommand, DataTable transfe

22719
rory.smith
TimeXtender Xpert
P
Contributor
pontus.berglundContributor
asked in Ingest
Connecting to Amazon Timestream Database

Is it possible to connect to an Amazon Timestream database (https://aws.amazon.com/timestream/) with the ODX?

2155
P
Contributor
S
Contributor
sguntupalliContributor
asked in Ingest
REST API

I am trying to get data using REST API Data Source: ODXProvider: CData 2022Authentication: OAuthFormat: JSONOAuth Grant Type: ClientAuthorize OAuth SuccessfulTest Connection getting below error: RSSBus.RSBException.HTTP Protocol error. 400 bad requests    

36912
Christian Hauggaard
Community Manager
peter.jensen
TimeXtender Xpert
peter.jensenTimeXtender Xpert
asked in Jobs & Executions
Refresh Power BI Premium dataset

Hi,We are currently migrating cubes from Power BI report Server ( On-premises) to Power Bi premium.Those cubes are not maintained in TX & refreshes are triggered through a SSIS package called by TX at the end of the ETL execution process.In the new implementation (where the tabular models are still not maintained in TX), we will be using XMLA endpoints & would like to trigger partitions refresh from within TX without using a SSIS package.What would be the best approach to tackle this, taking into account we’re running on an Azure platform (so TX VM and Azure SQL DB for DWH) ?We were thinking of using an ADF pipeline or use REST api calls, but this isn’t supported by TX version 20.10.x.  Any ideas on possible alternatives ? ThanksPeter

36010
Christian Hauggaard
Community Manager
christian.koeken
Contributor
christian.koekenContributor
asked in Ingest
ODX error the numerical value is too large to fit into a 96 bit decimal

Dear Support,I'm running into a weird error when I try to load customer data from the customer table of Navision. The error is: The numerical value is too large to fit into a 96 bit decimal.I use a query table to only get the columns I need. In this query table I use a where clause in which I exclude some customernumbers. But now I found out that I also need these numbers in my customer dimension. But when I clear the WHERE clause, the error comes up again.If I execute the same query in SQL Management studio it runs good and I don't see some strange values in one of the columns.Do you have any idea?

1643
Christian Hauggaard
Community Manager
Paul McLeod Bespoke Analytics
TimeXtender Xpert
Paul McLeod Bespoke AnalyticsTimeXtender Xpert
asked in Ingest
CSV File Load Error to MDW

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

1301
harishkm
Community Manager
sierd.zuiderveld
Contributor
sierd.zuiderveldContributor
asked in Prepare
Why are history system fields BigInt?

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. 

512
Christian Hauggaard
Community Manager
kerry_bespoke
Contributor
kerry_bespokeContributor
asked in Prepare
Table Relations using Greater than or Less than Operator

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) 

954
kerry_bespoke
Contributor

Badge winners

Show all badges
Terms and ConditionsCookie settings