December 22, 2012 at 10:58 pm
Hi All,
I have a ETL design which has 58 Tasks that it does every day. Each ETL is assigned with a unique WorkQueueRunGroupRunID and stored in a table.
If anyone would like to see the runtimes of individual runtimes of all 58 Task 50 days ago from now, we would select the StartDateTime of particular WorkQueueRunGroupRunID which matches to getdate-50, as below
SELECT TP.TransformationPackageName,WQ.WorkQueueRunGroupRunID,WQ.WorkQueueStartWorkDate,WQ.WorkQueueEndWorkDate,
cast((datediff (ss,WQ.WorkQueueStartWorkDate,WQ.WorkQueueEndWorkDate)) as decimal(8,0))/60.0 [RunTime(Minutes)]
,wq.workqueuestatus
FROM CloudBIMetaData.dbo.WorkQueue WQ WITH (NOLOCK)
JOIN CloudBIMetaData.dbo.WorkQueueRunGroupTransformationPackage WQRGTP WITH (NOLOCK)
ON WQ.WorkQueueRunGroupTransformationPackageID = WQRGTP.WorkQueueRunGroupTransformationPackageID
JOIN CloudBIMetaData.dbo.WorkQueueRunGroup WQRG WITH (NOLOCK)
ON WQRG.WorkQueueRunGroupID = WQRGTP.WorkQueueRunGroupID
JOIN CloudBIMetaData.dbo.TransformationPackage TP WITH (NOLOCK)
ON WQRGTP.TransformationPackageID = TP.TransformationPackageID
--WHERE WQ.WorkQueueRunGroupRunID = '38DBAF7A-0A14-47C4-BD6E-608A94AE34DB' --Copy the WorkQueueRunGroupRunID Here
ORDER BY WQ.WorkQueueStartWorkDate DESC
GO
and then see Give appropriate WorkQueueRunGroupRunID to see runtimes of all 58 tasks. as below
SELECT TP.TransformationPackageName,WQ.WorkQueueRunGroupRunID,WQ.WorkQueueStartWorkDate,WQ.WorkQueueEndWorkDate,
cast((datediff (ss,WQ.WorkQueueStartWorkDate,WQ.WorkQueueEndWorkDate)) as decimal(8,0))/60.0 [RunTime(Minutes)]
,wq.workqueuestatus
FROM CloudBIMetaData.dbo.WorkQueue WQ WITH (NOLOCK)
JOIN CloudBIMetaData.dbo.WorkQueueRunGroupTransformationPackage WQRGTP WITH (NOLOCK)
ON WQ.WorkQueueRunGroupTransformationPackageID = WQRGTP.WorkQueueRunGroupTransformationPackageID
JOIN CloudBIMetaData.dbo.WorkQueueRunGroup WQRG WITH (NOLOCK)
ON WQRG.WorkQueueRunGroupID = WQRGTP.WorkQueueRunGroupID
JOIN CloudBIMetaData.dbo.TransformationPackage TP WITH (NOLOCK)
ON WQRGTP.TransformationPackageID = TP.TransformationPackageID
WHERE WQ.WorkQueueRunGroupRunID = '38DBAF7A-0A14-47C4-BD6E-608A94AE34DB' --Copy the WorkQueueRunGroupRunID Here
ORDER BY WQ.WorkQueueStartWorkDate DESC
GO
Now, I want to make a SSRS report which would automate this process. Please help me what to put as the parameter in this case. and get runtimes of 58 tasks.
All I want is a filter in the report, where users can choose their date, then when they push the ViewReport option they should see all the 58 sessions of that one particular day.
December 24, 2012 at 8:29 am
Mac1986 (12/22/2012)
All I want is a filter in the report, where users can choose their date, then when they push the ViewReport option they should see all the 58 sessions of that one particular day.
Create a begin and end date parameter in your report and add to your WHERE clause like:
WHERE WorkQueueStartWorkDate BETWEEN @StartDate AND @EndDate
HTH,
Rob
December 27, 2012 at 1:52 am
Thanks Rob..Is there a way to that user selects a date in the top filter and in the respective WQ.WorkQueueRunGroupRunID assigned to that date gets selected in the where clause.????
I asking this because the results are 100% accurate if we use the Unique Column 'WQ.WorkQueueRunGroupRunID'
December 27, 2012 at 6:33 am
Mac1986 (12/27/2012)
Thanks Rob..Is there a way to that user selects a date in the top filter and in the respective WQ.WorkQueueRunGroupRunID assigned to that date gets selected in the where clause.????I asking this because the results are 100% accurate if we use the Unique Column 'WQ.WorkQueueRunGroupRunID'
I'm not sure what you're asking, but the WHERE clause I showed you will give you all records with the WorkQueueStartWorkDate between the start and end dates (inclusive). Is the problem because your jobs cross midnight (12am) and span multiple days? If so, just add the time element so that you're only getting the records you're after.
HTH,
Rob
December 27, 2012 at 5:40 pm
I can see why you might not want to use the @StartDate and @EndDate suggestion because you probably only want your users to have to pick one date and have it show all the tasks on that date. A slight modification to that might be
WQ.WorkQueueStartWorkDate between @StartDate and DateAdd(d,1,@StartDate)
It's still the easiest suggestion but there are some complicating questions that need to be answered:
Does the WorkQueueStartWorkDate have a time component? (Or is it just midnight)
Is it possible for there to be more than one run per day? (How does your ETL logging handle multiple runs?)
The answer to those two questions could complicate the way you want to do it, but the following may help. If your DW is huge and you have an index on the WorkQueueRunGroupRunID then this might be a bit faster but other than that there is no benefit over the previous suggestion.
1) create a parameter @RunID
2) Create a dataset for the parameter:
Select
WQ.WorkQueueStartWorkDate
WQ.WorkQueueRunGroupRunID
FROM CloudBIMetaData.dbo.WorkQueue WQ
Order by WQ.WorkQueueStartWorkDate Desc
Note - If you have a master table of dates and run id's then use that instead.
3) Edit the properties of the parameter and set the Available values to "get values from a query" and use the dataset created in step 2.
Use the WorkQueueRunGroupRunID as the Value Field
Use the WorkQueueStartWorkDate as the Label. If the date has a time component you can format it to show the date only.
Note - If you have multiple runs per date then you will need to add another differentiator for the label so you know which run you are selecting for that day. GUID's won't be in order of run time.
4) Edit your main report Dataset to add:
Where WQ.WorkQueueRunGroupRunID = @RunID
Note: this will need some tweaking if you potentially have more than one run per day, because you might see multiples of each of your tasks. Even if you don't think you will have more than one ETL run per day, you probably will one day when it breaks and you need to re-run it.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply