Skip to main content
To homepage To homepage

Prepare

Ask questions about Prepare Instances in TimeXtender Data Integration

  • 224 Topics
  • 1,013 Replies
224 Topics
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.

14795
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 made Thanks for your helpMichael

1896
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

392
Christian Hauggaard
Community Manager
sierd.zuiderveld
Contributor
sierd.zuiderveldContributor
asked in Prepare
SCD Surrogate Hash Key is non-deterministic.

Hi, I obtain a deployment error when I try to make my table historic. Computed column 'SCD Surrogate Hash Key' in table ' <mytable>' cannot be persisted because the column is non-deterministic. I think this has to do with the excessive amount of columns in the table: 695 columns, all varchar(2000). What confuses me is that when I set the hashing algorithms to debug, I still get the same error I don't have any transformations on this table, its a straight copy from to ODX. Is there a limit on the amount of fields a History table can have?

1495
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

532
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_TransportOrders Both 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.

693
tld
Contributor
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?

664
bas.hopstaken
TimeXtender Xpert
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.

492
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)

894
kerry_bespoke
Contributor
K
Contributor
KCMTContributor
asked in Prepare
SQL Case When Statement not working correctly with TimeXtender

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.

3592
K
Contributor
E
Contributor
ebayContributor
asked in Prepare
ETL Api

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.

854
E
Contributor
E
Contributor
ebayContributor
asked in Prepare
Connecting Rest API with changed parameter in RSD file

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.

1292
Christian Hauggaard
Community Manager
rvgfox
Problem Solver
rvgfoxProblem Solver
asked in Prepare
How to know the age of data in the ODX storage

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 DSA How can I do this?

514
Christian Hauggaard
Community Manager
rory.smith
TimeXtender Xpert
rory.smithTimeXtender Xpert
asked in Prepare
Is TimeXtender compatible with SQL Server 2022?

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.

2294
harishkm
Community Manager
rvgfox
Problem Solver
rvgfoxProblem Solver
asked in Prepare
Custom Editor

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?

573
S
Contributor
E
Contributor
ebayContributor
asked in Prepare
Connect to Excel files

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?

3367
fwagner
Employee
p.ruijter
Contributor
p.ruijterContributor
asked in Prepare
Table Inserts and Object Dependencies

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?

2412
Christian Hauggaard
Community Manager
daniel
TimeXtender Xpert
danielTimeXtender Xpert
asked in Prepare
Guarding Views in data warehouse

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!

962
daniel
TimeXtender Xpert
Jan Willem Kuper
Starter
Jan Willem KuperStarter
asked in Prepare
New field not available in Data Movement

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 MDW Select the mapping of the table The field is not available in the Data Movement Add a new field in the MDW tableAdd a transformation The field is available in the Data Fields Why is it not available in the Data Movement?

1192
daniel
TimeXtender Xpert
A
Contributor
alain.thireContributor
asked in Prepare
Updating table values - history settings

I have loaded a table with some initial data (== initial stoock). The key is quiet simple Company, Warehouse and Item Now I want to update the amounts every day so that it reflects the actual stock level of data day The 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

752
A
Contributor
anders.e.jonsson
Contributor
anders.e.jonssonContributor
asked in Prepare
Adding new schemas to a Data Area. What are the pros and cons?

Hi, It’s possible to create new schemas to set rules for access. Are there other reasons, beyond that, for creating new schemas?What are the pros and cons of extra schemas in a Data Areas in version 21 (6xxx)? All knowledge and experience in the field is gratefully received BRAnders

471
rory.smith
TimeXtender Xpert
K
Contributor
KajEmergoContributor
asked in Prepare
Audit trail modified GDPR-related objects

Hello, In order to be able to answer all related questions regarding GDPR, we are designing a TX solution that allows us to track all tables/columns that contains sensitive information. Most importantly, we make use of tags so we can track the columns throughout the DWH. An additional question we got is if it is possible to keep track of changes in the TimeXtender projects. In other words, who added which column (with sensitive data) to the project and when? What is the best way to keep track of these things and is there a solution in TimeXtender available that we can use to provide this information? Probably we should use the data in the TX Repository, we were curious if you have had this question before.

792
K
Contributor
rvgfox
Problem Solver
rvgfoxProblem Solver
asked in Prepare
Incremental load without primary key

I’ve some tables in my data source without primary key, but I’ve a MODIFIEDDATETIME field for all the records, that it can be used to incremental load, but TX doesn’t allow it. Some workaround?

3194
Thomas Lind
Community Manager
rvgfox
Problem Solver
rvgfoxProblem Solver
asked in Prepare
Setup TX for multiple developers

What it’s the recomended setup in the new version to support multiple developers and multiple projects? How many ODX instances, MDW instances must we would use?

823
Thomas Lind
Community Manager
V
Participant
VictorParticipant
asked in Prepare
Incremental load with Table Inserts and Views

Hello, Current Setup: TimeXtender 20.10.31.64 and ODX 20.10.31. I need help setting up incremental load on a rather different type of data flow from the standard one, that has table inserts and views inbetween tables. The flow is as follows: Table A (has incremental load today) => View1 => TableB => Table C(MDW) once directly but also through View2. Both views are necessary so we cannot remove them. My question is how to setup incremental load in timextender on this solution, if it is even possible. I am not sure how to do it through a view. Currently there is only an incremental load set up on Table A and then we fully load table B and C. Thank you

2463
Christian Hauggaard
Community Manager

Most helpful members this week

Badge winners

  • Ingest Expert
    nmohas earned the badge Ingest Expert
  • Ingest Expert
    oscarclevehas earned the badge Ingest Expert
  • Ingest Expert
    fwagnerhas earned the badge Ingest Expert
  • Innovator
    woutersmikehas earned the badge Innovator
  • Innovator
    Roy Vhas earned the badge Innovator
Show all badges

Popular tags

Terms and ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings