To homepage To homepage
Solved

Metadata TimeXtender Repository

  • 9 May 2023
  • 3 replies
  • 137 views
R
Rank

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

icon

Best answer by remco 12 May 2023, 17:25

View original

3 replies

Christian Hauggaard
Rank
Userlevel 6
Badge +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

 

R
Rank

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
Rank

  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

Reply


Terms and ConditionsCookie settings