/* This script queries tables from the Timextender Project Repository and returns releveant execution information that might otherwise be hard to find. 
		- This query pulls stats about both the Execution Package and the individule steps contained in the package, therefore, the package details may be repeated multiple times.
	Row Counts & Throughput:
		- When "Log Row Counts" is enabled in your execution package, this script also returns [Raw Rows Processed] and [Rows Per Minute] allowing you to review data processing speed.
		- Be careful when aggregating these fields as [Raw Rows Processed] shows the same number of rows for each step, this could potentially exagerate your results when summing. 
	Further Analysis & Use Cases:
		- You may also consider embedding this query into a Database View, allowing you to more easily query or aggregate this data (just be sure to remove the ORDER BY clause at the end).
		- Finally, you could also embed this query as a PowerBI data source to create powerful visualizations on this data (just be sure to remove the ORDER BY clause at the end).  
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

Use [projectRepository] --<<< Insert Project Repository Database Name HERE!
GO

SELECT TOP 1000
[ProjectName] = projects.Name
,[PackageName] = pkg.ExecutionPackageName
,[BatchId]
,[Package Status] = 
	(CASE pkg.EndStatus
		WHEN 1 THEN 'Failed'
		WHEN 0 THEN 'Successful'
		ELSE 'Unknown/Running' 
	END)
,[Package Start] = pkg.[Start]
,[Package End] = pkg.[End]
,[Package Runtime(Min)] = (CAST(DATEDIFF(MS,pkg.Start,ISNULL(pkg.[End],GETDATE()))AS decimal(18,2))/60000)
,[ExecutionMessage]
,[Object Name] = stp.ObjectName
,[Step Type] = 
	(CASE stp.StepId
		WHEN	'D5A3F05A-E587-4F3A-9C5A-FECF123F1C20'	THEN	'Count Rows'
		WHEN	'07460371-2717-45AE-B9A6-F14E92FBCC74'	THEN	'Data Cleansing'
		WHEN	'845DD0FE-36A5-484D-AAF8-138AA0DAFEE4'	THEN	'Data Transfer'
		WHEN	'F613DA8F-AAB6-4E92-82CA-776DC8630D35'	THEN	'Execute Multidimensional Cube'
		WHEN	'0D126BDA-FE6B-4479-8F86-70861DF10100'	THEN	'Execute Dimension'
		WHEN	'59CD1D88-D1CF-41D4-93FC-E7870EBE63F5'	THEN	'Fill Aggregate Table'
		WHEN	'4D5E28A8-2E3D-49AE-AB10-72E5D900C4A7'	THEN	'Fill Custom Data'
		WHEN	'14B23436-0624-4E1A-8666-2E5A1A4F2959'	THEN	'Fill Data Export Table'
		WHEN	'0D337FC1-2077-43F5-85B0-03BA9559F148'	THEN	'Fill Date Table'
		WHEN	'E594E933-6059-4D9A-9A99-1386FF2D1D2D'	THEN	'Fill Hierarchy Table'
		WHEN	'F77750D1-6F71-4DAB-A16E-1751C770B587'	THEN	'Fill Junk Dimension'
		WHEN	'10784A92-D256-4098-B634-1D0E85ADF7A2'	THEN	'Table Insert'
		WHEN	'54CB45BD-87E7-49C6-8749-B8CA6CE0D56A'	THEN	'Add Related Records'
		WHEN	'A6A919B3-FBF3-4FDF-8B63-37A086D831FB'	THEN	'Execute Tabular Endpoint'
		ELSE	'Custom'
	END)
,[Step Status] = 
	(CASE stp.EndStatus
		WHEN 1 THEN 'Failed'
		WHEN 0 THEN 'Successful'
		ELSE 'Unknown/Running'
	END)
,[Step Start] = stp.[Start]
,[Step End] = stp.[End]
,[Step Runtime(Min)] = (CAST(DATEDIFF(MS,stp.Start,ISNULL(stp.[End],GETDATE()))AS decimal(18,2))/60000)
,[Raw Rows Processed] = ISNULL([RawRows].[BigIntValue],0)
,[Rows Per Minute] = 
	(CASE DATEDIFF(MS,stp.Start,ISNULL(stp.[End],GETDATE()))
		WHEN 0 THEN NULL
		ELSE CAST([RawRows].[BigIntValue] / (CAST(DATEDIFF(MS,stp.Start,ISNULL(stp.[End],GETDATE()))AS decimal(18,2))/60000) AS decimal(18,0))
	END)

FROM [ExecutionPackageLogDetails] stp
LEFT JOIN [ExecutionPackageLogs] pkg ON pkg.ExecutionId = stp.ExecutionId
LEFT JOIN [Projects] ON pkg.ProjectId = projects.ProjectId AND projects.ValidTo=99999999
LEFT JOIN [LogRowCounts] [RawRows] ON stp.ExecutionId = RawRows.ExecutionId AND stp.ObjectId = RawRows.ParentId AND RawRows.type = 2

/* ---- Example WHERE clause below, un-comment to use ----
WHERE 
	pkg.ExecutionPackageName = 'Default'	--Enter Package Name Here
	AND
	projects.Name = 'Timextender'			--Enter Project Name Here
*/

ORDER BY stp.[Start] DESC