Notification on "logical" errors in Data warehouse load

This one is really important now that the TX Dictionary project is no longer functional. Instead of needing to build something to monitor the raw _L and _M tables, having notifications would be great.

I agree, It might also be nice to have the ability to add these tables to a semantic model rightaway in TimeXtender, to add them to an endpoint. Now this is only possible when connecting to the specific database with e.g. PowerBI or Qlik.

I’ve created this code to control that tables:

-- Variable con consulta a ejecutar
DECLARE @Esquema NVARCHAR(4)='DSA'
DECLARE 
	  @SQL NVARCHAR(MAX)
SELECT 
	  @SQL='SELECT 
				''<TableName>'' AS TableName
				, t1.ErrorText
				, COUNT(*)	 AS Registros
		   FROM   ' + @Esquema + '.<ErrorTable> AS t1
			 INNER JOIN ' + @Esquema + '.<ControlTable> AS t2
				ON t1.ErrorNumber = t2.ErrorNumber
			 GROUP BY t1.ErrorNumber
				    , t1.Severity
				    , t1.ErrorText';
WITH TablasErrores
	AS (SELECT 
			 SUBSTRING(t.NAME, 1, LEN(t.NAME) - 2) AS TableName
		    , t.NAME AS                                ControlTable
		    , REPLACE(t.NAME, '_L', '_M') AS           ErrorTable
		    , SUM(p.rows) AS                           RowCounts
	    FROM   sys.tables AS t
			 INNER JOIN sys.indexes AS i
			   ON t.OBJECT_ID = i.object_id
			 INNER JOIN sys.partitions AS p
			   ON i.object_id = p.OBJECT_ID
				 AND i.index_id = p.index_id
	    WHERE  SCHEMA_NAME(t.schema_id) = @Esquema
			 AND RIGHT(t.NAME, 2) = '_L'
			 AND i.OBJECT_ID > 255
			 AND i.index_id IN(0, 1)
	    GROUP BY 
			   t.NAME
	    HAVING SUM(p.rows) > 0)
	SELECT 
		  @SQL=STRING_AGG(REPLACE(REPLACE(REPLACE(@SQL, '<TableName>', TableName), '<ErrorTable>', ErrorTable), '<ControlTable>', ControlTable), ' UNION ')
	FROM   TablasErrores
EXEC sp_executesql 
	@SQL