Data Warehouse

Previous Paragraph. This one is super long though so you can see the difference when paragraphs span multiple line breaks.  This one is super long though so you can see the difference when paragraphs span multiple line breaks.  This one is super long though so you can see the difference when paragraphs span multiple line breaks.  This one is super long though so you can see the difference when paragraphs span multiple line breaks.  This one is super long though so you can see the difference when paragraphs span multiple line breaks.  This one is super long though so you can see the difference when paragraphs span multiple line breaks.  This one is super long though so you can see the difference when paragraphs span multiple line breaks.  This one is super long though so you can see the difference when paragraphs span multiple line breaks.Heading OneNext Paragraph. This one is super long though so you can see the difference when paragraphs span multiple line breaks. Next Paragraph. This

Hi all,  I need to create an Identity column, How could I create it in TX? Thanks Ignacio

Hi, Let's assume I am importing from CSV file. The Data is very dirty and somehow I have stings in my int filed "ID" so the filed is interpreted as NVARCHAR(MAX). If I change data type of the field (right click > Edit Field) from string back to integer I receive a runtime error during the execution. What I was expecting was to see was corresponding entries in the _M and _L tables. Is this not part of data cleansing after all? We have this problem all over and could not find a better solution than to build custom views with TRY_CAST etc. where we lose lineage etc. What is the best way to clean up such data (keep valid rows, write errors/warnings on invalid rows)? We are talking 100+ tables so I'm looking for a highly scalable solution here not a one time work around. Thanks and BR, Tobias

Does anyone know whether or not the rules you put in the over ride data types area are applied sequentially like they would be in a SQL case statement (i.e. once a field evaluates to TRUE it ignores the subsequent logic)?

I am new to TX and data lakes.  I have a common problem that others have commented on and would like to know if: 1) The functionality now exists, or if it does not 2) How are others getting around the issue? The issue is that I need to be able to segregate data sources in the data lake.  I am pulling in data from multiple SQL sources where the source schema and table names are the same, and I need to differentiate those tables in the data lake.  By schema would be best, but if that is not possible, I need some other ideas.  Thanks!

Hello, I have a problem that i am struggling with. I have a table that contains field called [Period] and it stores month dates in following int format: 2201, 2202, 2203 which i need to translate to 2022-01-01, 2022-02-01, 2022-03-01 etc. to date format (always 1st day of month) The problem is i can convert the source format to date format in the view, but when i am trying to generate a table from that view, i always get an error that [Period] cannot be converted to date format. I have different options and nothing has worked as timextender always fails to convert to date format. Everything that failed on timextender, worked perfectly on MSSQL so i am not sure what is wrong. Thanks for your help

Hi, I'm searching for a way to get a table insert incremental. I have a table (about 30 million records). This has a row for each hour of the day for 10.000 machines, so this table is getting bigger very quickly. I do a lot of transformations in this table. This resulted in a load time of over 10 hours. I've discussed this with our TimeXtender Solution Specialist and we've decided to split the table into multiple smaller tables.  Now I do have 1 table (loaded incrementally from ODX), this table has a lot of lookups and simple transformations. This table is loading quickly because it's incrementally loaded. The result of this table is inserted in a new table (Raw table), using a table insert. This table is converting cumulative numbers to noncumulative, using the SQL functions LAG, OVER, PARTITION BY and ORDER BY. Because it is a table insert I have no idea how to do this incremental. I've set NonClusted Indexes (also on Raw table) on all fields used in the PARTION BY function. And enab

Hi all, I'll try to find out how to get rows in the _L table when I use a field validation.   For field validation I can decide if I get a warning or an error information. But I only get a message in the _M like this: How can I get the rows that don't match the field validation criterion to get an entry in the _L table? 

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 = 2 Table 2 = Relationpersonid = 1personid = 2 Table 3 id = 1, personid = 1, personid = 2id 2 , null          

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. 

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 ? 

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?

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

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)

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

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.  SELECT SCHEMA_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 0 end                       AS bit) AS [IsSystemObject] FROM sys.tables AS tbl ORDER BY [Schema] ASC,[Name] ASC

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 rec

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

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    

Hello!!Please help to figuer out this error "The given value of type String from the data source cannot be converted to type nvarchar of the specified target column" while pulling data from source to stage.Anyone Who Can Help!!!

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

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

After manually executing a package, where do I find a log that shows the # of rows processed per table?  I'm trying to verify my incremental loads are working properly

Hi, We use TX as a part of our value chain of systems using data. We need to programatically be able to check if the execution is done, so the following jobs can start.I know TX can send an email when completed, but we really do not want to build the check on emails. Does anyone have a good idea on how to do this? Hope you can help. Best regards Michael Vaisgaard

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