Skip to main content
To homepage To homepage
Solved

Metadata TimeXtender Repository

  • May 9, 2023
  • 3 replies
  • 219 views
R
Starter

I am looking for a way to find all tables in a perspective in a way I can analyse the differences between perspectives.

In SQL in the metadata repository I would like to join ProjectPerspectives to the Datatables to find all Perspectives with certain tables, find which tables are not in a perspective and are therefor missed while loading data etc.

I do not see an identifier thats Unique between the two so I am hoping one of you does :). 

 

Thanks in advance, 

Remco

Best answer by remco

  select P.Name as PerspectiveName, DW.Name AS [Data Layer] ,DT.name as TableName from [txrepo_dev].[dbo].[ProjectPerspectiveItems] PP
  LEFT JOIN [txrepo_dev].[dbo].[ProjectPerspectives] P on PP.ProjectPerspectiveId=P.ProjectPerspectiveId and P.ValidTo = 99999999
  LEFT JOIN [txrepo_dev].[dbo].[DataTables] DT on DT.DataTableId=PP.TxObjectId and DT.ValidTo = 99999999
  LEFT JOIN [txrepo_dev].[dbo].[SqlServerConnections] SSC ON SSC.SqlServerConnectionId = DT.SqlServerConnectionId and SSC.ValidTo = 99999999
  LEFT JOIN [txrepo_dev].[dbo].[DataWarehouses] DW ON DW.DataWarehouseId = SSC.DataWarehouseId and DW.ValidTo = 99999999 
  where PP.ValidTo = 99999999

  ORDER BY PerspectiveName

Small update to include the data warehouses

3 replies

Christian Hauggaard
Community Manager
Forum|alt.badge.img+5

Hi @remco 

Please try executing this query

  select PP.*, P.Projectid, P.Name as PerspectiveName, DT.name as TableName, SLM.Name as model from [TX_Repository].[dbo].[ProjectPerspectiveItems] PP
  LEFT JOIN [TX_Repository].[dbo].[ProjectPerspectives] P on PP.ProjectPerspectiveId=P.ProjectPerspectiveId
  LEFT JOIN [TX_Repository].[dbo].[DataTables] DT on DT.DataTableId=PP.TxObjectId and DT.ValidTo=99999999
  LEFT JOIN [TX_Repository].[dbo].[SemanticLayerEndpoints] SLE on SLE.[SemanticLayerModelId]=PP.TxObjectId and SLE.ValidTo=99999999
  LEFT JOIN [TX_Repository].[dbo].[SemanticLayerModels] SLM on SLM.SemanticLayerModelId=SLE.SemanticLayerModelId and SLM.ValidTo=99999999
  where PP.ValidTo=99999999

 

harishkm
R
Starter
  • remco
  • Author
  • Starter
  • May 12, 2023

Thanks this works! 
  select P.Name as PerspectiveName, DT.name as TableName from [txrepo_dev].[dbo].[ProjectPerspectiveItems] PP
  LEFT JOIN [txrepo_dev].[dbo].[ProjectPerspectives] P on PP.ProjectPerspectiveId=P.ProjectPerspectiveId
  LEFT JOIN [txrepo_dev].[dbo].[DataTables] DT on DT.DataTableId=PP.TxObjectId and DT.ValidTo=99999999
  where PP.ValidTo=99999999

is my final query as I am not using semantic layers. 

R
Starter
  • remco
  • Author
  • Starter
  • Answer
  • May 12, 2023

  select P.Name as PerspectiveName, DW.Name AS [Data Layer] ,DT.name as TableName from [txrepo_dev].[dbo].[ProjectPerspectiveItems] PP
  LEFT JOIN [txrepo_dev].[dbo].[ProjectPerspectives] P on PP.ProjectPerspectiveId=P.ProjectPerspectiveId and P.ValidTo = 99999999
  LEFT JOIN [txrepo_dev].[dbo].[DataTables] DT on DT.DataTableId=PP.TxObjectId and DT.ValidTo = 99999999
  LEFT JOIN [txrepo_dev].[dbo].[SqlServerConnections] SSC ON SSC.SqlServerConnectionId = DT.SqlServerConnectionId and SSC.ValidTo = 99999999
  LEFT JOIN [txrepo_dev].[dbo].[DataWarehouses] DW ON DW.DataWarehouseId = SSC.DataWarehouseId and DW.ValidTo = 99999999 
  where PP.ValidTo = 99999999

  ORDER BY PerspectiveName

Small update to include the data warehouses

harishkm
Terms and ConditionsCookie settingsAccessibility statement