Prepare

Anyone know in what TX repo table I can find the field and value for “Guard on deployement” and “Guard on execution?I know where the setting is in the UI, but need to look up the value in the repo db.

Hi can you add a diagram or something like that to understand when and where that scripts are executed? 

v20.10.35.64A while ago I was looking for a solution on how to conditionally fill down/flash fill values with certain criteria. Unfortunately the answers in this ticket didn't do the trick for me, as I kept getting NULL values with all solutions I've tried. How can I conditionally fill down/flash fill NULL values with previous values based on certain criteria? | Community (timextender.com)Now I seem to get it working with a Self Lookup view and a Lookup Field (Top first-non value). However, I have to repeat this process for almost 10 columns. The loading of this table went from 06:00 to 30:00 minutes. The lookup field looks as follows. What it does is:Look if column Route Bron is empty, If Route Bron is not empty, just show Route Bron. If Route Bron is empty, lookup the TOP first non empty Route Bron Value with the same Source, Project, Cardcode where project is not empty and not 999999 Is there a way how I can optimize this process? Thanks in advance!

In fact tables we are using subrogate keys from the dimension tables using supernatural keys.The relationship between the fact table and the dimension table it’s using the bussines key and getting the subrogate key from the dimension table into fact table. But some times it doesn’t exists, and I’m using -1 to put that value.What it’s the best way to include the unknow member (-1) in the dimension table?

In Custom data insert i have one field which is varbinary(8000) data type. When i try to insert a column i get an error: The given value of type String from the data source cannot be converted to type varbinary of the specified target column.Failed to convert parameter value from a String to a Byte[].Invalid cast from 'System.String' to 'System.Byte[]'.Details:Invalid cast from 'System.String' to 'System.Byte[]'.Module: mscorlibSystem.InvalidCastExceptionat System.Convert.DefaultToType(IConvertible value, Type targetType, IFormatProvider provider)at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming)Failed to convert parameter value from a String to a Byte[].Module: System.DataSystem.InvalidCastExceptionat System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming

Hi community,A customer is using a specific role that is allowed to select data from specific views in SQL. When one of these views are deployed, the Object Security Setup  isn't deployed. The Database Role/Security Settings needs to be redeployed manually.Is it possible to automatically deploy the Object Security Setup, if the a view/table is deployed that is using security settings?  

I see this advanced menu option in all the tables but I don’t know how can it be used. 

Hi,I am working on a column that evaluates a date against GetDate() and returns a 1 or 0. The table can be loaded incrementally. I assumed that if I turned on "Keep field values up-to-date", this column would always be recalculated at loadtime, but it seems that this is not true.Is there a way to make these two work together or do I have to give up on incremental loading?Thanks

Hey,I face this issue quite frequently and have not found out how to solve it yet so I’m hoping someone on here can tell me how to do this.I have a REST data source in which I make a selection of columns. I drag that table into the DSA layer, rename some columns and voila, I have my table. Now I found out that I need some additional columns from the data source so I go into my ODX and in the table selection I include those columns as well. The transfer is succesful and I have my data with additional columns. Now here comes my problem.I want to sync my table with the data source and add those additional columns to my existing table. When I right-click-drag the table into the DSA layer I can sync, smart sync and sync only with existing fields. The first options adds all columns to the bottom of my table (including the ones I already have). The other two options only sync the columns that I already have…Hope that someone can help me out as I face this more often in development.

Hi TX Community!I get data from an external-database everyday.Since we have an aim of incremental-loading the data that we extract, we are doing some query-tables from the datasource to create a “incremental-load”-key.In that matter we are experiencing two issues:1- TX cannot read the date-formats that are extracted from the database. This is the format that we get out: Of course, we can right-click on the field and edit the datatype, but that we would have to do everytime we syncronize the datasource because everytime we syncronize the datasource all the date-fields are back to the “unknown”-format and therefore we would have to right-click on each field and edit the data-type. We’ve tried to use the “Data type overrides” but it doesnt seem that we can convert from an “unknown”-format. How can we solve this problem? As mentioned, the tables are query tables and therefore we would like to think that the date-formatting could be solved with a CAST or a CONVERT function. Any ideas?2- In

Hi Friends, I contact you because I have to migrate a Dim Table with SCD 2 from the Old SQL DW to TX and after that set the TX Dim to the following working, Is there any process to do that? Thanks  for your help Ignacio

HelloI currently have a column with the following file name in one of my tables. C:\Users\John\OneDrive - Sales Solutions\Desktop\TimeXtender\TMX-DataSamples\RLZ\BESTERS - BESTERS POINT - BESTERS IND_DEC 22_101602_0.txtIs there a way to remove the path name in the column to only have the file name?I have a file for each month and have merged the files so all the data is in one table. For example, remove this piece: C:\Users\John\OneDrive - Sales Solutions\Desktop\TimeXtender\TMX-DataSamples\RLZ\and only show this piece:BESTERS - BESTERS POINT - BESTERS IND_DEC 22 as the file name.I also need to have the Month Year (DEC 22) of each file copied into a new column called “Date”. Is this at all possible?Thank you 

How can I solve extension _R tables failing to create? I am working on the old project Now.This project was copied from another project repository.Environment: SandboxVersion: 20.10.36.64Data Source: Business unitProvider: SQL Server Data SourceThe connection with the Data source is good and synchronized well.When I am deploying the tables in ODX storage getting an Error.An error occurred during create a table. See exception details for the failing object: Create failed for Table 'BSA.BSA_dbo_Inspection_R'.An exception occurred while executing a Transact-SQL statement or batch.The specified schema name "BSA" either does not exist or you do not have permission to use it.    

Did anyone try to connect azure sql database using Azure AD Integrated Authentication? I notice MFA was missing as an Auth type but AD Integrated was failing with error. Let me know if anyone resolved this error.    One or more errors occurred.Could not discover endpoint for Integrate Windows Authentication. Check your ADFS settings. It should support Integrate Widows Authentication for WS-Trust 1.3 or WS-Trust 2005. Details: Could not discover endpoint for Integrate Windows Authentication. Check your ADFS settings. It should support Integrate Widows Authentication for WS-Trust 1.3 or WS-Trust 2005.

Hi,I can’t find a way to update my table with the new field [Color] that has been added to the view the table insert is based on. Any suggestions?BRAnders

On clean procedures generated by TX, they seem to have a RECOMPILE embedded into the procedure itself as a general rule. I think this is to keep them fresh to other changes in a project that get introduced. But do we know the exact reason why, and is there a way to remove other that doing a customized change table by table? 

Dear Support,The reload in TimeXtender is giving the error: An item with the same key has already been added.It seems to be that one column is mapped on two different columns in the same table.Is there a quick solution to find the column which is causing this error? Thanks in advance! Christian 

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

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.   

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

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

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

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.

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. 

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)