To homepage To homepage

TimeXtender Desktop Q&A

Ask questions and find answers about the TimeXtender Desktop Application

507 Topics

A
Explorer
ajiExplorer
asked in Data Warehouse

Implementing Scalar Function with COALESCE in TX

Hi TX Support,I wanna know how to implementing scalar function with COALESCE in TX ? Here I attach the function, could you please tell me how to do that?USE [QAD_DATA]GO/****** Object: UserDefinedFunction [dbo].[GetAliasesByWo1] Script Date: 03/20/2018 16:15:04 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER FUNCTION [dbo].[GetAliasesByWo1](@item nvarchar(max),@length float,@catego nvarchar(max),@opt nvarchar(max))RETURNS varchar(max)ASBEGINdeclare @output varchar(max)select @output = COALESCE(@output + ', ', '') + wo_lot + '=' + convert(nvarchar, wo_qty_ord)FROM wo_mstr_datawhere wo_part = @item andwo__dec01 = @length andwo__chr01 = @catego andRIGHT(wo_rmks,1) = @opt andwo_status = 'R' andSUBSTRING(wo_routing,1,6) = 'REWORK'return @outputEND 

5
A
6 years ago
M
Explorer
mchinskyExplorer
asked in Data Warehouse

Sorting a Hierarchy differently

I'm trying to create a hierarchy of GL accounts and the category they belong to. (i.e. Assets, Liabilities, Equity, Income Expenses).However the Hierarchy is displaying alphabetically.  If it were to be sorted by the first GL account it's associated with, it would sort correctly since GL accounts are numbered in the proper sequence.However, when I tell the Dimension, "Parent Category" (which is the Assets, Liabilities etc) to sort by Attribute Key, when I deploy I get an error saying it can't be used as an attribute because it is not related.Is there a workaround to this?

1
J
Contributor
5 years ago
R
Starter
r.miltenburgStarter
asked in ODX

GML field

In one of my single text files is a field which is actually a GML field. When i'm trying to deploy and execute this field as varchar(4000) it gives me the following error: 'string or binary data would be truncated' So I wanted to override data type vut this seems to be impossible when your data source is a single text file. So I tried something else, i made an Excel file of my csv file and read objects from data source as Excel, but then my GML field isn't even visible in the data selection tab. is there another possible way to insert my GML file in TimeXtender without getting an error?

6
J
Contributor
5 years ago
H
Contributor
hgrContributor
asked in Data Warehouse

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)

1
JTreadwell
Employee
4 years ago
G
Visitor
ghazyVisitor
asked in Data Warehouse

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,

2
JTreadwell
Employee
4 years ago
S
Starter
sbanerjee1Starter
asked in Data Warehouse

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. 

4
A
4 years ago
S
Starter
sbanerjee1Starter
asked in Data Warehouse

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.

4
JTreadwell
Employee
4 years ago
J
Starter
jose.hoyosStarter
asked in Data Warehouse

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

3
J
Starter
4 years ago
R
Starter
rluytenStarter
asked in Jobs & Executions

Execution blocking access to cubes

Hello, Since a couple of days I created a second execution package to execute +- 10 tables, some dimension and 1 cube. After this execution the other cubes are not reachable, I need to deploy and execute them all to get it solved. Any clue why an execution pack would block access ? Thanks, Ronny

3
E
Contributor
4 years ago
E
Starter
erwin.dekreukStarter
asked in Semantic Models

Could not connect to the Tabular server.

Getting the following error while connect to AASCould not connect to the Tabular server.Could not load file or assembly 'Microsoft.AnalysisServices.Tabular, Version=15.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.Is there a way to solve this?

2
M
Starter
4 years ago
R
Starter
r.brummansStarter
asked in ODX

Delete data due to privacy legislation

We want to delete the data in TimeXtender after a period of 5 years because of the GDPR legislation . Is there a possibility to get this handled by TimeXtender?

1
JTreadwell
Employee
4 years ago
D
Contributor
dsvartzmanContributor
asked in Data Warehouse

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  

6
D
Contributor
4 years ago
S
Starter
stefan.timmermanStarter
asked in ODX

csv filename changed after load

When I am using the Single text file and I am loading a csv file, TX replaces and removes the file to the Backup folder as specified. The filename before load:  E:\BAG\CoordinatenBAG2019.csv And the filename after loading:  E:\BAG\CoordinatenBAG2019__20191105_163142_ Now I have to rename the file every time i want to do a reload. How can I prevent this?  

2
S
Starter
4 years ago
H
Contributor
hgrContributor
asked in Jobs & Executions

Incremental Load not Delivering Data

Hello,I have the following setup for an incrementally loaded table:I would expect, that it loads all rows, with either an id greater than max id - 1000 or datum_post greater than max datum_post - one week.In fact, when I execute the query, it is not returning any rows. This is interesting, because removing the datum_post condition returns rows.Other tables with such a double condition (id + date) also work. I am wondering, whether it is necessary to have an index on datum_post in the source table? The queries can take quite a while on the datum_post field. The example query takes 2 minutes on the source database, but TimeXtender finishes after 30 seconds. TimeXtender says it is finished with "Success", but there are no rows.Also the following query yields results when querying the source database directly: I think this is the equivalent query to what TimeXtender should load.I am using Discovery Hub 19.6.7.64 and the source database is using (an old) mysql version: I am connecting with

1
H
Contributor
4 years ago
H
Contributor
hgrContributor
asked in Jobs & Executions

Manually reloading recent data

Just for anybody interested: I just found out that you just update the _I table to some older values, to reload all data greater than the value you set. Previously, I always reloaded the full table in these cases. This is not necessary and saves me some time from now on.

3
JTreadwell
Employee
4 years ago
D
Starter
dritzStarter
asked in Data Warehouse

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

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

1
A
4 years ago
P
Visitor
Picanova BIVisitor
asked in Jobs & Executions

Transfer Error - Invalid column name - Incremental Execution MDW

Hello,one of several incremental tables won't execute. upon execution the transfer fails stating "invalid column name " & name of the incremental column.The problem occurred in Version 19.2.7.64 and is still present after an upgrade and redeployment to Version 19.11.7.64.What I tried so far. I tried to change the column from Incremntal_date to id.Redeployed the full table configuration.Upgraded to Version 19.11.7.64.Help is much appreciated. Best regardsPeter 

1
JTreadwell
Employee
4 years ago
S
Explorer
Steven KoppenolExplorer
asked in Data Warehouse

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?

5
peter.jensen
TimeXtender Xpert
4 years ago
M
Explorer
mchinskyExplorer
asked in Jobs & Executions

Trying to Understand Incremental Performance

So based on what I'm reading about TX, it seems to me that even if only say 5 records changed on a source file, TX loads the entire source table into a Raw table (all 40 gigs of it) and then finds the 5 records.Is there a way to get it to just pull the 5 changed records into TX?  My incremental executions seem to take about 50% of the time of a full execution even though there is little to no data change.

2
E
Contributor
4 years ago
M
Starter
mads.gratheStarter
asked in Semantic Models

Semantic Models with no Endpoint

Is it not possible to create a semantic model with no endpoint? For me, the beauty of the semantic models is that I can quickly create views for 'data marts' where I can do data selection rules without having to write a single line of code.  In previous versions of TX I could use the Qlik (Deprecated) semantic model for this because it didn't need an endpoint in order to deploy the views. But the newest version(s) of TX does not support this. So what if I'm moving entire corporation's data visualizations to Qlik SaaS (basically Qlik Sense in the cloud)? Can I no longer use semantic models? 

2
M
Starter
3 years ago
J
Starter
jnortonStarter
asked in Data Warehouse

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 

9
M
Contributor
3 years ago
S
Visitor
sander.koekVisitor
asked in ODX

warning: table folder used by another table

Hello,I'm relatively new to TX.I'm getting the following warning when trying to load tables from an external source:The table folder 'Y' is used by another table with the id xxxx.How do I fix this?

1
JTreadwell
Employee
3 years ago
M
Contributor
martin.d.larssonContributor
asked in Semantic Models

Error while processing SSAS Tabular cube

I am able to deploy the semantic model but when I execute it I get the following error message: The current operation was cancelled because another operation in the transaction failed. Details: The current operation was cancelled because another operation in the transaction failed.System.Exception Not much do work with Im sorry to say but this is what I get. This is the TXDictionary project and it has worked before i moved it to a new server. The rest of the project executes fine (ODX, DSA and MDW).

3
M
Contributor
3 years ago
G
Starter
glaymonStarter
asked in Semantic Models

Automating Data Export to generate a file per value in value in control table

Essentially, I am currently creating csv files from extracts of many TimeXtender tables.  Let suppose the data in a table is a column which identifies each row by country and the table contains many rows for each country, then I would need to create a file for each ISO country that existed in the table. I know how to do this by hand, but am wondering if there is a clever way of automating this in TX without having to build an SSIS package?Thank,Greg

1
J
Contributor
2 years ago
M
Starter
malose.motlhasediStarter
asked in Jobs & Executions

TimeXtender 20.10.15.64 Performance

Good Morning, We have recently upgraded TimeXtender to version 20.10.15.64. Both in QA and PROD servers. We have TX ETL jobs that extract data from Oracle database server to both server every night. They run at different times. However, the PROD ETL job takes longer to extract data. QA ETL job takes quicker. 1. QA TX on average takes 3hours to extract data from PROD oracle. 2. PROD TX on average takes 5 hours or more to extract the same information from PROD Oracle. Sometimes this take longer than this. 3.PROD TX sometimes hangs the whole night. 4. PROD TX has higher specs than QA. 5. We have checked the Performance Indicators  on PROD server while PROD TX is running. The CPU, Memory and other indicators seems well. 6. We trying to figure out why performance on PROD is slow during the data extraction from Oracle. We have checked all necessary indicators to and we still don't have answers. Is there anything that you can advice us to check so we can understand why PROD is slower than QA

1
S
Contributor
2 years ago

Badge winners

  • Certified Solutions Architect
    Karankadamhas earned the badge Certified Solutions Architect
  • TimeXtender Xpert
    Muddihas earned the badge TimeXtender Xpert
  • Innovator
    andrew.gebhardhas earned the badge Innovator
  • Certified Solutions Architect
    mello.bogaartshas earned the badge Certified Solutions Architect
  • Innovator
    ekw9has earned the badge Innovator
Show all badges
Terms and ConditionsCookie settings