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
+5Hi
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 [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.
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
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
