Prepare

CREATE VIEW [Demo].[INVC_T] AS SELECT R.[ACCT_DT], R.[SPLIT_FROM_INVC_NO], R.[REMIT_TO_ADDR_NO], R.[USER_INVC_NO], R.[UPDT_PERS_NO], R.[UPDT_DT_TIME], R.[TOTALS_UPDT_DT_TIME], R.[SHIP_TO_ADDR_NO], R.[SEG_NO], R.[RECUR_INVC_NO], R.[PRINT_INVC_STATUS], R.[PMT_TERMS_CD], R.[JE_NO], R.[INVC_TYPE_CD], R.[INVC_STATUS_CD], R.[INVC_SRC_CD], R.[INVC_NO], R.[INVC_DUE_DT], CAST( ([INVC_DT]) >= DATEADD(week, -10, GETDATE()) AS datetime) AS [INVC_DT], R.[INSUR_AMT], R.[FRGHT_AMT], R.[CUST_TYPE_CD], R.[CURR_RATE], R.[CURR_CD], R.[CLOSE_DT], R.[BILL_TO_CUST_NO], R.[ACCT_YR], R.[ACCT_PER], R.[PMT_METH_CD], R.[CREDIT_CARD_FLAG], [DW_Id], [DW_Batch], [DW_SourceCode], [DW_TimeStamp], [DW_ODXBatchNumber], [DW_ODXBatchId] FROM [Demo].[INVC_R] R GO above script error is what im getting please let me know what is wrong or where im getting it incorrect,below is snippet which im using, (FIELD) >= DATEADD(week, -10, GETDATE())

We are just started to use ODX server as a source for our countries that are migrated to Business Central in Azure. On our DEV environment i want to limit the data to only get entries after a certain date. Normally i would use a data selection rule on Posting Date Greater or Equal 2021-01-01 When i want to do that on ODX server as a data source i get: Conversion failed when converting date and/or time from character string. I have tried every date notation i can think of but i always get this same error. Anyone any idea on how to filter dates from the ODX Server?

I Have Invoice date column in my table, i want to export data of last 10 weeks, what are ways to do it in timextender

Dear sir, In TX version 20 you can easily go back in time to an earlier version of your TX project. I am looking for going back in time to an earlier version of the MDW/SSL layer in TX version 21.I am running version TimeXtender 6143.1. Is this possible in the latest 6143.1 TX version ? Regards, Arthur

Below is error message when trying to create custom view, how can i sove it @Christian Hauggaard

Hello, For two weeks my clients Power Bi report was refreshed daily without issues. Last week this changed after the TimeXtender Execution server stopped running. I ran the TimeXtender ODX Server Configuration on the desktop and started the TimeXtender execution server again. The next days the execution jobs where triggered again but I would like to know why TimeXtender Execution stopped running in the first place. How could this have happened?

I’ve read the artical Enum and page method but we still have a question how the json response and the RSD file should be. It would be great if anyone provide a full example (json response data and the RSD) so that we can fully test. Thanks for advance!

Hello, we're using Timextender with ADF for the data movement between our Data Lake storage and SQL Database. Running the execute from TimeXtender of rerun it within ADF the performance is poor (slow!). Our setup at the moment, regarding to the specs within the topic "Create a Demo Template Project TX SaaS”. We wanted to start basic and then tweak the settings for better performance. Executing the first SQL Database Service tier: General Purpose Compute tier: Serverless Hardware: Standard (Gen5), Min/Max vCores 10 and 30GB Min/Max Memory Auto-pause delay: 1 hour Integration Runtime - type Azure Computer Size: small We tried using a different Computer Size on the IR and also changed the SQL DB Tier for more DTU. No performance improvement after changing. Looking at one of the jobs we are transferring 2048 rows from our Data Lake to our Azure SQL. The Queue is taking most of the time. We did some research on the support page of TimeXtender (No topics) and also at the Microsoft community.

In v20 and before you can define multiple statements in a query tool window. This can be handy when doing comparisons or dealing with wide tables. v20 statement 1 v20 statement 2 In v21 the second statement is simply ignored for SQL DWH instances and results in an error in Snowflake: v21 SQL v21 Snowflake ; separating statements Was this taken out of v21 or should this actually work like in v20?

Hi all, I recently created a bunch of SQL logins in TimeXtender to use in our Database Roles we set. After I did that I found out I made a mistake and I removed those SQL logins from the database. However, when I try to edit a database role and try to Add a login, I still see the SQL logins I removed. To make sure there is nog caching or something involved I waited a few days before checking if they were still there. Does someone have a solution to remove those logins from the list? I don't want to create new names for my SQL logins just to avoid having the same name as the previous one. Although, if that is the only option I am willing to accept that as well. Kind regards, Carlo

Hi all, We ran into an issue with history settings in combination with the DW_SourceCode. We need the DW_SourceCode in our primary key as there is overlap in the sources and this ensures uniqueness. In the history settings we cannot choose the DW_SourceCode to be the natural key: Without this, history does seem to work, but when we enable Create new record when a deleted record reappears in the source We obtain the error Error during Data Processing for Table: Maintenance.Verkoopinfo at: Maintenance The column name 'DW_SourceCode' is specified more than once in the SET clause or column list of an INSERT. A column cannot be assigned more than one value in the same clause. Modify the clause to make sure that a column is updated only once. If this statement updates or inserts columns into a view, column aliasing can conceal the duplication in your code. I've looked into the stored procedure that produces this error and this snippet produces the error: -- History: handle deleted records IN

Hello I need the creation date and time (createdAt) of a CSV file within the MDW table as a column. How can I access this information? Best Regards

We have created several jobs on our ODX and several jobs on our DataWarehouse. The ODX jobs are running correctly based on schedule, but the Datawarehouse jobs never run since there is a schedule. We've also deployed and saved our project, so that should not be the problem.

Hi! I beg your pardon for this somewhat lengthy post, but I try to be thorough to avoid confusion. :) I have a problem with updating an SCD. We are moving an old adress history table from another system into a SCD in TX. It has gone well so far since the old system made one insert per person per day regardless of whether there was an update or not, which has made us go from about 108M rows to about 275k rows with actual changes. So far so good. :) The problem now is that have a hard time to get this moved table to update correctly from a new source. The setup looks like this at the moment: The MDW table KIR_Persons is the correct and prepared historical data from the old system where I have fixed the From- and To-dates along with the “Is Current” flag. The “break date” for the data from the old system is 2023-10-16 which is when I made the final export. The new source for the address data is the DSA table KI0VAA0POPULATIONPERSON_QT which is the result of a query table. I’m using the DS

Hi In one of my projects, I created a model by transferring the table I received from DSA to MDW using view. When I wanted to create the relation diagram of this model, I saw that it could not establish a connection with the view and DSA table. How do I ensure the connection of all tables at the end of the project? There is no problem with Data Linage. Best Regards

Hello to the community 😀 Currently I started the TimeXtender Training. I am at the point where I have to create the instances. Adding the ODX or SSL instance was no problem. But adding the MDW instance didn’t work out for me. The problem is that i can’t select any storage in the “Server storage type” field. It just says “No items found”. (While adding the ODX instance it preselects “Azure Data Lake” and I can just switch to SQL database”, but while adding the MDW instance it doesn’t give me these options) Does this problem occur to anyone else? Do I maybe have to change any configuration before the “add data warehouse step”? Thank you in advance. Kind regards, Tobias

Hi, In my DSA layer I succesfully added a mapping set for which the include rule has identifed two tables. All of the columns in my DSA table have been renamed so none of them align with the original names in the ODX. As a result, the mapping set tells me all of the columns are missing: This specific table was first added to the DSA for a single ODX table, then the columns got renamed and at a later moment I wanted to replace the mapping to a mapping set. Should the mapping set be able to cope with this? In essence, it would be just like doing it manually and using the ‘smart synchronize’ option. But the mapping set does not seem to work like that. Best regards, Kaj

Hello community, Is it possible to do RegEx with TimeXtender? Or use Python code somewhere in TX that supports the library re? Our business case:- We have a database with text reviews of varying lengths, where personal information is sometimes available. We want to read the database in and anonimise the values which contain personally identifiable information. For example: "oh no, ZuzaGlog doesn't know how TimeXtender works” and we want to make it "oh no, XXXXXX doesn't know how TimeXtender works”. We currently do it in Python with the library re, and a list of possible personally identifiable information. We search through the strings with reviews using the keywords from the list, and replace hits with XXXXXX. Now we want to build this solution in TimeXtender. In what way would this be possible?

Our client has a question about a historical table in TimeXtender. They would like to know if they can make a onetime update from SSMS to populate historical records. More details on the scenario below. Scenario: Table in question is in MDW An ID merge has been done to collapse users that have created multiple accounts into on true account. Logic is done in DSA and flows to MDW. The new merged id was inserted as a new field in the MDW table, the old id was renamed. New merged id field is null for historical records where old id is populated. This makes historical queries fail. Question: Can they run a one time stored procedure in SSMS to fill in the historical records to the new merged id field? What are the potential drawbacks of this solution?

I’m testing out the new version of TimeXtender. I’ve downloaded the newest version of SQL Server that was suggested in the training material. I’ve created ODX and MDW instance, when I try to deploy a table in the MDW instance I get the following error: Not supported in current SQL version Version: Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) Oct 8 2022 05:58:25 Copyright (C) 2022 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 22621: ) (Hypervisor)

In the release notes for 6429.1 it says that conditional lookup fields are available “in preview” for deployments in Snowflake. What does this mean?

Hi, I want to use mapping set to map several ODX tables with the same table structure to a single DW table. The problem is that I also want to create additional “custom fields” in the DW table, and since these fields do not exist in any of the ODX tables, the mapping set excludes all the ODX tables and I am left with an empty DW table. Is there a work around to this? or am I perhaps doing something wrong? This is my DW table: The bolded fields with descriptions do not have mappings to them, they instead have custom transformations. I have used “convert to mapping set” to get the mapping set, and then adjusted it so both tables match the mapping set rules. As seen below, both tables are then excluded since neither have the custom fields. Is it necessary that all fields have at least one mapping to them?

I’ve two tables, A and B, and I want a full outter join to obtain a table C like in the image: In T-SQL it would be: SELECT COALESCE(A.Id, B.Id) AS Id ,A.ValueA AS ValueA ,B.ValueB AS ValueB FROM TableA AS A FULL OUTTER JOIN TableB AS B ON A.Id=B.Id Is it the only way to use a custom view?

Hello,We just upgraded our sandbox to release 6429.We created environments, and Copied from Dev to Test without problems in the portal. Both MDW and SSL. SSL was mapped to the correct MDW.But after we copied from Test to Prod this error came when we tried to deploye Data Area DSA in MDW Prod. Error details in attached text file. BRAnders

Hi, I believe this is a bug but perhaps intentional. When a field in the DSA is set to ‘Raw’ , you can still map it to the MDW. When you do this the field is not mapped (as it is impossible), however the table does become dirty (red). Then, when you refresh the instance, the field is actually added in the MDW, but not mapped. I attached a GIF that shows this.