To homepage To homepage
Solved

Source table dependency

  • 11 January 2024
  • 5 replies
  • 109 views
L
Rank

Hi,

We use BU's and some sources are created in another TX project.
How can I verify / check the dependency of a source in this case a SQL table?
For example I have in the BU a SQL table (using global database) that is created in another TX project.
How can I check which TX project has created this table?

And also in general, how can I create a dependency list of a TX projects (dependency with other TX projects)?
Is this stored in the repository? 

Regards,

Lorenzo 

icon

Best answer by Thomas Lind 15 January 2024, 10:09

View original

5 replies

Thomas Lind
Rank
Userlevel 6
Badge +5

Hi @Lorenzo 

As mentioned in other locations, you can use the repository database to find what you look for.

Why are you using another project as a source to a project, why can’t this part be in this project?

You can add external execution packages from the other project and use that to run before the tables that uses this source is executed. You can’t schedule any deploy operations, so you will have to do this manually in both projects.

 

Thomas Lind
Rank
Userlevel 6
Badge +5

For reference this was the suggested query

The following query can be run on the repository to show which project a data table is associated with.

SELECT TOP (1000) [DataTableId]
      ,dt.[DataSourceId]
      ,p.[ProjectId]
      ,bu.[BusinessUnitId]
      ,ds.[Name] AS DataSourceName
      ,dt.[Name] AS DataTableName
      ,bu.[Name] AS BusinessUnitName
      ,p.[Name] AS ProjectName
      ,[SchemaName]
      ,[StagingName]
      ,[OrigName]
      ,[IsChecked]
      ,[TableType]
      ,[ExternalProperties]
      ,dt.[ValidFrom]
      ,dt.[ValidTo]
  FROM [TxRepository].[dbo].[DataTables] dt
  LEFT OUTER JOIN [dbo].[DataSources] ds ON ds.[DataSourceId] = dt.[DataSourceId]
  LEFT OUTER JOIN [dbo].[BusinessUnits] bu ON ds.[BusinessUnitId] = bu.[BusinessUnitId]
  LEFT OUTER JOIN [dbo].[Projects] p ON p.[ProjectId] = bu.[ProjectId]
  -- WHERE dt.[StagingName] like '%%'
  ORDER BY dt.[StagingName]

You can uncomment out the WHERE statement and enter a name to search for a specific data table.

L
Rank

Hi Thomas,

Unfortunately this will not work.
I can find the link to the DataSource in DataTables (TableType = ‘DataSource’) but I can't find the relation in which Project this table is created.

Probably this should be the row with TableType = ‘DataWarehouse’ but the SchemaName does not match in my case and can't find any repository table that stores the relation between the table created in the Datawarehouse of a specific project and the above mentioned table found in the DataTables.
See below query:
SELECT DataTableId, DataSourceId, SqlServerConnectionId
, [Name], SchemaName, StagingName, OrigName, TableType
FROM [dbo].[DataTables] dt
WHERE ValidTo = 99999999
 AND (dt.[StagingName] like '%FreshdeskTickets%'
 OR dt.[OrigName] like '%FreshdeskTickets%' 
OR dt.Name like '%FreshdeskTickets%')

Remark: ValidTo is needed else you have to many matches

Regarding your question why it is not created in one project, we have some legacy where some tables are created and are useful to use as a source in another project (which are completely different topics)  and we would not want to use the same logic in two different projects. 
To be able to find where the source is created I would automatically want to retrieve the info instead of manually check the projects.

Christian Hauggaard
Rank
Userlevel 6
Badge +5

Hi @Lorenzo can you please provide a table of the resulting columns that you would like to be returned by the query with example rows so we can better understand the desired result?

Christian Hauggaard
Rank
Userlevel 6
Badge +5

Hi @Lorenzo do you have an update on this? Could you please provide a table of the resulting columns that you would like to be returned by the query with example rows so we can better understand the desired result? Thanks!

Reply


Terms and ConditionsCookie settings