Skip to main content
To homepage To homepage

Prepare

Ask questions about Prepare Instances in TimeXtender Data Integration

  • 230 Topics
  • 1,052 Replies
230 Topics
R
Explorer
roland.peetersExplorer
asked in Prepare
Relationship between 2 tables (left outer join)

When I have two tables, how can i make a left outer join between 2 tables. Or do i always need to use a custom view for this situation. Table 1 = Adressid = 1id = 2Table 2 = Relationpersonid = 1personid = 2Table 3 id = 1, personid = 1, personid = 2id 2 , null

2041
Thomas Lind
Community Manager
B
Starter
ben.vd.helmStarter
asked in Prepare
Default column value for simple tables in BU / ODX Server

Is there an option to set a default value for a simple table such that we can fill it with some value without doing any transformations. It then also should be possible to add fields for simple tables, but that you can only fill it with a default value. It would also be helpful in the DSA. 

625
A
Starter
R
Explorer
roland.peetersExplorer
asked in Prepare
Automate : Add suggested constraints.

I am busy with de TimeXtender Learn (TimeXtender Optimization) en there is an exercise :  Right click DSA node and choose Automate > Add Suggested Constraints.  De instruction is on TX 20.5.6.64, we work with 20.10.23.64. I can't find this function. Is that correct ? 

264
R
Explorer
M
Contributor
martin.d.larssonContributor
asked in Prepare
Add related records, no relation between tables

I am unable to use the Add related records on a table. I get the error message "There is no relation between table-A and the table table-B". I have added a condition where the key in both tables should be equal. I have also tried creating a relation between the two tables in both directions but no luck. There is no mention in the docs that it is needed to add a relation before adding related records. I am getting a Warning that the source table is executed after the destination table. What am I doing wrong?

813
Thomas Lind
Community Manager
M
Starter
mark.ferrisStarter
asked in Prepare
Use IS NOT NULL as a join condition on lookup

Is there a way to use IS NOT NULL as one of the join conditions on a lookup? I want to get the TOP (1) record where a field is not null, ordered by date. Currently I have to edit the _Clean stored procedure as custom code to add it, which is obviously not the best way to do things. Thanks

27515
J
Contributor
tkp
Visitor
tkpVisitor
asked in Prepare
Primary key violation - which record is then valid ?

When you set up primary key behaviour to 'error' you elegantly force all valid data to be unique combinations of your selected primary keys. But, how do you control what individual dataset is regarded as valid and what is discarded as error ? Is there a way to let the primary key violation error handling know, which record you consider the valid one ?  (e.g. based on min/max values in non-primary key data fields)

601
S
Contributor
K
Visitor
keith.williamsVisitor
asked in Prepare
Two datasources into one table

Hello,So I am loading historical data into tables in TX created of Excel files. One file for property data, one file for casualty data. After some time the file format changes and they combine these two separate files into one file and add new fields.Can I create a new data source to load the new file but still use the original worksheet tab names such that the data will still load into the existing tables?Thanks in advance.P.S. I am using Discovery Hub 17.12.13.64

2167
JTreadwell
Employee
J
Starter
jnortonStarter
asked in Prepare
Deadlock on sys.tables

I have been running into a problem where my execution package keeps failing due to a deadlock between an index rebuild and a select statement on sys.tables. TimeXtender is running both process' and one eventually gets killed and fails the entire job. in subsequent executions it has always failed on an index rebuild, but not the same index. Has anyone run into this issue or have any solutions? here's the query.SELECTSCHEMA_NAME(tbl.schema_id) AS [Schema],tbl.name AS [Name],tbl.object_id AS [ID],CAST(case    when tbl.is_ms_shipped = 1 then 1    when (        select            major_id        from            sys.extended_properties        where            major_id = tbl.object_id and            minor_id = 0 and            class = 1 and            name = N'microsoft_database_tools_support')        is not null then 1    else 0end                      AS bit) AS [IsSystemObject]FROMsys.tables AS tblORDER BY[Schema] ASC,[Name] ASC 

3149
M
Contributor
G
Starter
glaymonStarter
asked in Prepare
Working with Table Inserts

I have a table with fields defined (call it Table B), but no mapping.  I am inserting into this by using Table Insert (from Table A).  Think Table A -> inserted into -> Table B.  In the picture below Table A is Projects from D365 at the bottom of the picture.  Table B is everything else in the image.   DataAreaId is a field defined on Table B and is populated successfully from the table insert.  I would like to have a field, Field C, that simply does a custom transformation which places the value of DataAreaID into Field C.  While the Table B is fully deployed and executed, I am finding that Field C is unpopulated.  In fact all fields in the image that take values not from the Table Insert directly are unpopulated meaning I also can't assign constants.    So, all of the DimensionDisplayValue - xxxx fields in the picture are empty after executing.  I have also noticed to my surprise that the raw table is empty.  Not sure if this is by design or if something on Table B can be recon

3092
G
Starter
A
Visitor
atgVisitor
asked in Prepare
Partition Scheme

Hi, I have a large FACT-tables in my DWH (copy from a view in stage). I have tried to add partitions. I didn't help that much, so I removed it again.But since I removed it, the table can't be deployed. I get the below error. I can re-add the partition, but still the same error: An error occurred during drop partition function. See exception details for the failing objectAn error occurred during drop user defined function. See exception details for the failing object: Drop failed for PartitionFunction 'FactSalesOrder_History_PartitionFunction'.An exception occurred while executing a Transact-SQL statement or batch.Partition function 'FactSalesOrder_History_PartitionFunction' is being used by one or more partition schemes. Details: SQL Server: '.'SQL Procedure: ''SQL Line Number: 2SQL Error Number: 7706 I'm not sure how to find and drop what-ever TX is complaining about.Tried this:DROP PARTITION FUNCTION FactSalesOrder_History_PartitionFunction and then get this:Partition function 'FactS

623
J
Contributor
M
Starter
mark.ferrisStarter
asked in Prepare
_L and _M Table Behavior

I am using the Field Validation to test our ETL. Using this as source data:And these rules in the DSA:And this query to check for errors:I get the following result:The _L table is linking to the records in the _R table that were removed when they failed the validation step on FIRST_NAME. Also the Warning level notification is not present in the table.As it stands the _L, _R, and _M tables as joined above are showing incorrect or at least confusing information. Am I using them as they were intended to be used, and has anyone done anything to get around this?Thanks,Mark

702
M
Starter
H
Visitor
huub.thuijlsVisitor
asked in Prepare
Column-store index

Is it possible to create a clustered column-store index in TimeXtender 20.103.64?

451
Thomas Lind
Community Manager
A
Starter
alexander.hauensteinStarter
asked in Prepare
Table classification

How does "Table classification" in Table Settings affect a table ?Can't find any information in the user guide or google.For which cases "Dimension- /Fact Table (normal + large) are use full ?Many thanks for your help.Cheers Alex

1253
JTreadwell
Employee
P
Starter
per.schefferStarter
asked in Prepare
Clustered columnstore index

How do I tell TimeXtender to use a clustered columnstore index for my fact table in Azure Synapse?

762
P
Starter
A
Contributor
alain.thireContributor
asked in Prepare
Executing stored procedure

How can I execute a stored procedure once a load of a table is done. I know it should be possible. I just don't know where I should configure it  

861
JTreadwell
Employee
M
Starter
mcvStarter
asked in Prepare
Implementing SQL "CASE WHEN" Statement using native TX functionality

Hi, I have to do something like this in TX: If productno > 0 then 'Product A' else lookup in table productnames I cannot figure out how to do this in TX. Hope someone can help. Best regards Michael Vaisgaard

1222
M
Starter
S
Visitor
sai.mamidiVisitor
asked in Prepare
SQL 'Case'

How to write 'case' in Custom SQL, please let me know with example. Here is the situation (SQL query) that I need to perform on Date table & likes to update 'IND' column where DateValue is default column in Date table selectcase when GETDATE()<=DateValue then 'N' else 'Y' end as 'IND' ,* from <table_name> order by DateValue desc

461
JTreadwell
Employee
S
Explorer
Steven KoppenolExplorer
asked in Prepare
How to create a transformation that is different for each mapping going to the same table

Example scenario:Table DimProject has 2 mappings, each from a different source:Mapping 1 = SourceA.ProjectMapping 2 = SourceB.ProjectNow both systems will have the same project number with different meanings.My question is how do I make a transformation that returns 'A' + [project number] in mapping 1 and 'B' + [project number] in mapping 2.The solution needs be rely on explicitly meaningful conditions, no implicit conditions that drive people crazy trying to decipher what they are for somewhere in the future.Any ideas?

355
peter.jensen
TimeXtender Xpert
D
Starter
dritzStarter
asked in Prepare
How can a parent-child hierarchy be historicized using parent DW_Ids?

How can a parent-child hierarchy be historicized using parent DW_Ids?

471
D
Starter
D
Contributor
dsvartzmanContributor
asked in Prepare
database diagram: visualize your data model

Hi, i've setup a MDW layer with proper relations between tables. At this point I wanted to create a relation diagriam to visualise to my client the relation between his entities. my tool of choice was SQL Management Studio. Only then i've realised that TimeXtender only create DW_Id as physical primary key and the relations between tables are managed internally Is there a way in TimeXtender to visualize the relations between the tables? I've already put my vote here  

856
D
Contributor
J
Starter
jose.hoyosStarter
asked in Prepare
Many to many Relations and conditional joins

Hi,I am trying to build a transformation process in TX based on the following SQL code.------------------------------------------SELECT distinct[BusinessEntityID],B.MonthKey,[StartDate],[EndDate],[DepartmentName]FROM [TX_DSA].[dbo].[EmployeeDepartmentHistory] Ajoin dbo.Calendar B ON B.Date >= A.StartDatewhere b.DateValue >= a.StartDate AND (a.EndDate is null or a.EndDate <= b.DateValue)----------------------------------------------------I want intentionally to expand the grain of table A. I tried different Joins and conditional lookups but I just can't reproduce the output.How would you solve this?Thanks in advancegrJose

553
J
Starter
S
Starter
sbanerjee1Starter
asked in Prepare
Using Collate on Join conditions

I am joining 2 tables T1 and T2 based on a condition T1.A = T2.BT1.A have values like 'wNFq' and T2.B have values like 'WNFQ', 'wnfQ', 'WnFq', 'wNFq'For a regular join a particular row of 'wNFq' from T1 gets joined with all values: 'WNFQ', 'wnfQ','WnFq' and 'wNFq' of T2My intention is to join only 'wNFq' from T1 to 'wNFq'.I achieved the same using custom SQL insert and using the below  COLLATE function to join based on case sensitive sequencing using:T1.A COLLATE Latin1_General_CS_AS_KS_WS = T2.B COLLATE Latin_1_General_CS_AS_KS_WSWandering if there is a different way to achieve this avoiding custom SQL.Appreciate any help on the same. 

954
S
Starter
S
Starter
sbanerjee1Starter
asked in Prepare
Filtering on the Look up fields

Created a conditional lookup based on following : T1 joined to T2 where T1.A = T2.B Looked up T2.C based on the above join condition and stored the value in T1.D Now I need to filter the resultant data set which is on T1 based on certain values of T1.D Unable to put that filter in the Table Data Selection Rule. Even I tried to copy T1.D(look up filed) to T1.E (a separate filed created just to hold the value of T1.D) and filter on T1.E, even that did not work. Appreciate help on the same.

1214
JTreadwell
Employee
H
Contributor
hgrContributor
asked in Prepare
Conditional Lookup multiple values

I have the following set of tables: A (key1, key2) B (key2, key3) In that case, every key1 can have multiple key2 attributes in table A and every key2 can have multiple key3 attribues in table B. When I would use a conditional lookup field for table A to get key3, it would only give me one of the key3 attribues, is it possible to take all values? I know I could do it with a simple custom view left joining on key2, but is there are recommended way to do it in Discovery Hub without custom views? So the goal table should be like C (key1, key2, key3)

911
JTreadwell
Employee
G
Visitor
ghazyVisitor
asked in Prepare
How to RoundUp or RoundDown Decimal

Dear TimeXtender's Team,What is choice to setting rounding in TimeXtender if we want to set up decimal ? Like example .. in Original Data is $12,3456, we just want the result is $12,34 (2 decimal)because in TX, it will be $12,35Thank you,

312
JTreadwell
Employee

Badge winners

  • Certified Solutions Architect
    hay71has earned the badge Certified Solutions Architect
  • Certified Solutions Architect
    Himanshu Chhas earned the badge Certified Solutions Architect
  • Innovator
    oscarclevehas earned the badge Innovator
  • Certified Solutions Architect
    leo.noharethas earned the badge Certified Solutions Architect
  • Innovator
    Bernarddbhas earned the badge Innovator
Show all badges
Terms and ConditionsCookie settings