Prepare

We are implementing a data warehouse with data from Dynamics 365 F&O and I’m thinking that we will have money data from UK in pounds and from Spain in euros and we will need currency conversion (based on time) to resolve this situation. Some body has considered this situation? How must it be implemented?

If i try to add a dataselection rule my tables stay empty. Tried allready the selection rule on the table and with an without ‘ ‘ If i do the same in the query tool it looks good. Is it a bug? Using version 6143.1

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

The following article describes several types of data masking: https://legacysupport.timextender.com/hc/en-us/articles/5463363539101-Dynamic-Data-Masking .Does TimeXtender allow custom data masking as well? The example table shown at the top of this article is exactly what I am trying to accomplish. However, the article doesn't describe this type of masking, does it?

I have a transaction table in odx with the following fields (simplified) Key,Value, transactiondate Example 100, V100, 2023-01-01100, V100A,2023-01-31 09100, V100B,2023-01-31 10 Each day there are multiple transactions added , of course with another transaction date.Note in the example I have only shown the date part (not hours, minutes, seconds) What I need is a table with the latest update in DSA I created the same table. Set primary key to KEY and added an incremental selection rule on transactiondate . Added history settings to update the Value and transaction date based on the key Where it goes wrong is if we get in one day multiple transactions 100, V100A,2023-01-31 09100, V100B,2023-01-31 10 I traced down the cleansing procedure and Tx detects that there are twice the same key. So far so good.Next it puts the ID of the latest transcation into <table>_L and only processes the ID’s that ar not in <table>_L The result is that I get 100, V100A,2023-01-31 09 which is incorrect ! Look

Dear Community, I like to build my data estates with supernatural keys but in lager datasets the data cleansing starts to take very, very long. Do you happen to have the same issues? Is there a way to make the supernatural keys load faster? Even with incremental loading it begins to be super slow:I've ran a test on 435,397 records. This is on a Azure SQL with 10 vCores1. is a full load on the table with 7 supernatural keys.2. is a full load on the same table without the supernatural keys. 1 has data cleansing of 1 second. 2 has a data cleansing of 104 seconds! Second I've done a test on the same tables but now incremental loads: 1. incremental load with 7 supernatural keys2. incremental load without supernatural keys 1 has a data cleansing time 1,6 seconds and 2 a data cleansing of a whopping 129 seconds! I'm not so sure I want to keep using the supernatural keys. What do you guys do? Take care = Daniel

Hi, I have a list that maps product_codes to product_ID's. What I would like to create is a Key Store that, when fed a product_code from the list, produces the same product_ID and when fed a new code (so not in list) produces a supernatural_key as usual. Is it possible to force the keystore to create the ID's as shown above?

The setup phases are confusing, can anyone explain where exactly they run? The Pre script runs previously to insert the records in the valid table, it’s correct in my case, but the post script that must be run after the records inset, doesn’t run. My script it’s:

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 would like to run a job only if the number of records in the raw table it’s diferent of the number of records in the valid table wit a condition. How can I do it?

Data in the context of GDPR or sensitive to stock markets require attention in the datawarehouse with regard to securing the data, in some cases this also to the people developing the datawarehouse. We have been looking into several options, and now plan to do a proof of concept on transparent data encryption. Anybody experience with securing sensitive data in the datawarehouse, either with this setting or in other ways?

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!!!

The autoindexes doesn’t created in the physical table

Did anyone notice that these functions do not seem to work directly on a field? I always have to create a new one to use trim function on the original filed. Am I missing something? I would like to avoid the repetitive process of manually creating additional fields when I have a table which needs TRIM.

Hi. I have a question for you all: I have a table for example link this filed_name:Format Product_id: Integer Delivery_metod: varchar(50) Inventory_id: Integer Inventory_Entries: JSON Inventory_entries can contain one or several entries in JSON format like this: [{"SKU":"A","Price":"100"},{"SKU":"B","Price":""00"}] Instead off one line with the json entries nested into one single field i would like to Normalize the tabel into several lines, one for each SKU. Any ideas?

Hi, with this snippet: I get an error in deploy: The sippet it’s correct as you can see in:

Hi, Our customer uses the new TimeXtender version with ODX (Azure Datalake). MDW (Azure SQL Database) and also uses the NAV connector. We experience issues when adding data selection rules in MDW layer on existing tables. System.AggregateException: One or more errors occurred. ---> System.AggregateException: Pipeline execution failed ---> System.Exception: { "errorCode": "ActionFailed", "message": "Activity failed because an inner activity failed; Inner activity name: TIMEXTENDER COPY DATA, Error: The request failed with status code '\"BadRequest\"'.", "failureType": "UserError", "target": "TIMEXTENDER COPY DATA LOOP", "details": ""} --- End of inner exception stack trace --- at ODX.DataFactory.DataFactoryResourceHelper.ValidatePipelineRunResult(PipelineRun pipelineRun, DataFactoryConnectionModel dataFactoryConnection) at ODX.DataFactory.DataFactoryResourceHelper.ExecutePipeline(Dictionary`2 pipelineParameters, DataFactoryConnectionModel dataFactoryConnection, String pipelineName) at O

When you add tables to a DWH data area (from an Azure Data Lake backed ODX) and subsequently perform a Deploy & Execute, data is loaded normally as expected. In this example I am adding AW2014's Person.Person and Sales.SalesPerson tables. I subsequently want to add a field where I add a transformation to generate the full name for a person (Firstname + ' ’ + Lastname). When I add this field with the transformation and perform a Deploy & Execute, the field is not populated. The reason for this is probably the same reason under: https://legacysupport.timextender.com/hc/en-us/requests/25792 I would expect changes to ETL to trigger a full reload without needing to either change the Table Settings > Data Extraction > Full Load or perform a cumbersome full reload through D&E > Review tasks. Given that this issue seems generic and not only limited to Historiy tables, it is probably a good idea to solve this generally as well.

When using the Data Profile function on the Person.Person table in the AdventureWorks2014 database I see strange results for the highest and lowest occurence in the LastName field: Diaz is the highest and Billstrom is the lowest. In reality Abbas is the lowest when sorting by LastName ASC where count(*) == 1 I am not entirely sure that 'Highest occurence’ is clear to everyone that is not a natvie speaker: you could also assume this should be the highest sorted value.

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

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

Dear CommunityIf I'm clicking on Report->Errors or Warnings then I see a fulll list of them. Where are those records stored? In which table can I find them ? My problem is that once forgot to check the "Error" manually, therefore I want to create a script which regularly should check if errors exists. But wo far, I couldn't the table/view/place where this Infos are stored. Any ideas? Regards Alex

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 enabl