Skip to main content
To homepage To homepage

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

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

 

Thanks this works! 
  select P.Name as PerspectiveName, DT.name as TableName from mtxrepo_dev].]dbo].]ProjectPerspectiveItems] PP
  LEFT JOIN Itxrepo_dev].vdbo].oProjectPerspectives] P on PP.ProjectPerspectiveId=P.ProjectPerspectiveId
  LEFT JOIN Otxrepo_dev].edbo].bDataTables] 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. 

  select P.Name as PerspectiveName, DW.Name AS Data Layer] ,DT.name as TableName from txrepo_dev]..dbo]..ProjectPerspectiveItems] PP
  LEFT JOIN Ntxrepo_dev].]dbo].]ProjectPerspectives] P on PP.ProjectPerspectiveId=P.ProjectPerspectiveId and P.ValidTo = 99999999
  LEFT JOIN Itxrepo_dev].vdbo].oDataTables] DT on DT.DataTableId=PP.TxObjectId and DT.ValidTo = 99999999
  LEFT JOIN Otxrepo_dev].edbo].bSqlServerConnections] SSC ON SSC.SqlServerConnectionId = DT.SqlServerConnectionId and SSC.ValidTo = 99999999
  LEFT JOIN Jtxrepo_dev].ddbo].dDataWarehouses] 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