topic Re: SSIS Catalog DB Dashboard in Data Stories Gallery

SSIS Catalog DB Dashboard

stretcharm — Mon, 28 Oct 2019 22:47:49 GMT

Hi,

This is not so much a Data Story but a template for a dashboard for looking at your own SSIS activity.

I use SSIS extensively with my job and over the years have used various tools to help me manage the packages and triage issues.
These include my own SSIS logs, the out of the box SSMS reports, Jamie Thompson's report pack and a number of SSIS catalog queries.

I had a quite good single query SSIS PowerBI Dashboard, but it was slow on large catalogs and limited in its use.

I started working on new dashboard that provided all of the features from all my past tools and around the time I saw a post from Chris Schmidt (link below) and so decided to use this as a starting point.

My dashboard is still not finished, but after lots of iterations I think it's going to be useful for others who need to monitor and understand what their SSIS packages are doing.

I first posted this a in 2017 and I've just given it an overhaul to support some of the new feature now supported by the August 2018 Desktop & Report server versions. Please give it a try and let me know if you spot any problems or things that could be improved.

I bring in and summarise the data is at a variety of levels

Execution (Execution & Execution Summary) The Package that is Executed First which I've named RootPackageName. This includes the Project & Folder
Package (Executable Package Stats) Package Summary. I group Packages by Types e.g. Master/Dimension/Fact/Stage/PostProcess. You can customised these in the PackageTypes Table. Click Advanced Editor to change it.
Executable (Executable Stats) Items inside the Package e.g. Tasks/Data Flows/Sequence. I also call this level Package Steps
I categorise packages and task into types using a keyword lookup. e.g. Package starting Fact for loading Fact tables.

The Task type names are based on common SSIS naming conventions.

This dashboard also provides details of any package errors and maps them to the Microsoft IS Error Reference Names.

Individual Executions can be viewed as a text based Gantt chart or as a matrix of executions by time slices.

Finally I extracted the Hierarchy from the execution paths to make a Network diagram and Sankey to visualise the structure and levels in an SSIS project.

Whilst developing this dashboard I managed to combine and try out a number PowerBI techniques such as;

Dax Measure to display a Gantt chart (Based on Chris Webb's Star Ratings)
Turn Package Paths into a Network diagram & Sankey Diagram
Scatter Chart to show hour and day matrix
Mini Gantt Timeline with Matrix
Mini Gantt Timeline with Scatter Chart
Dax Tables for Summary data
Vlookup function to use Package names to provide a type. This can do contains matches and use Start and End Indicators.
M Language tables for reference data
Parameterised SQL to Configure the Days to Load
Standard Deviation for tolerances.

Bookmarks & Selections

Report Tooltips

I've tried to keep the custom visuals to a minimum but I have used Sankey/Network Navigator as well as the OKViz Bullet and Sparklines.

The PowerBI is blank with that needs the server parameter setting to your SSIS db server before applying the changes and granting permissions.

The next network and rows can take a while to load wiht a large Catalog db so I have a small version that excludes these.

Templates and sample screen shots are available here https://github.com/stretcharm/SSIS-DB-Dashboard

Release Notes

v1.00

Improved query on Exectutions and Executables

Added Include fields for the Package types to configure the Master & Package Page filtering.
Edit PackageTypesUse Query to configure.

Changed Package filters

Add Date, Start, Execution ID Hierarchy to the Master and Package Pages

Added Preserve Layers Bookmark to help avoid window coming to the front

Remove Optional Job Server Code
Add Icon Ribbon and Help Page.
Minor Fixes

Thanks to the providers of the following pages that I've used to help in the making of this dashboard.

https://blogs.msdn.microsoft.com/sql_pfe_blog/2017/04/18/ssisdb-reporting-with-power-bi/
Chris Schmidt

https://www.excelguru.ca/blog/2015/01/28/creating-a-vlookup-function-in-power-query/
KenPuls

Star Ratings Quick Measure
http://community.powerbi.com/t5/Quick-Measures-Gallery/Star-Ratings/m-p/166903#M12

cwebb

https://ssisreportingpack.codeplex.com/
Jamie Thomson

Lots of Dax help and great OK Vis PowerBI Visualisations
http://www.sqlbi.com/

SSIS Catalog DB
https://docs.microsoft.com/en-us/sql/integration-services/service/ssis-catalog

Reza Rad's Article on the SSIS Catalog
http://www.rad.pasfu.com/index.php?/archives/75-SSIS-Catalog-Part-3-Folder-Hierarchy;-Folder,-Projects-and-Packages.html
DB Diagram
http://www.rad.pasfu.com/ssis/ssiscatalogpart3/5.png

SSIS Error Codes
https://docs.microsoft.com/en-us/sql/integration-services/integration-services-error-and-message-reference

Enjoy

Phil

Follow me on twitter https://twitter.com/StretchArm1 for updates

Other gallery entries

PowerBI/SQL Server and Microsoft Business Application Related YouTube Videos
https://community.powerbi.com/t5/Data-Stories-Gallery/PowerBI-SQL-Server-and-Microsoft-Business-Application-Related/m-p/743806#M2779)

SQL Saturday Sessions https://community.powerbi.com/t5/Data-Stories-Gallery/Pass-SQLSaturday-Dashboard/m-p/489529#M2153

Microsoft Bus App Summit & SQL Bits videos

https://community.powerbi.com/t5/Data-Stories-Gallery/Search-Microsoft-based-Business-Application-videos-and/m-p/714344#M2679

and PowerBI Custom Visual

https://community.powerbi.com/t5/Data-Stories-Gallery/PowerBI-Custom-Visuals/td-p/724185

Most code is also here https://github.com/stretcharm

eyJrIjoiZDM0N2NmMjMtZmE2NS00MDE1LWJkOWQtZmFmZmZkMzI4ZDBkIiwidCI6ImEwYTcyYzIzLTdlMWEtNGYxOC05NDU4LTlhNzUyYTEzMTg1NCJ9

Re: SSIS Catalog DB Dashboard

stretcharm — Mon, 04 Sep 2017 15:22:05 GMT

Just noticed that changing servers can cause the Execution to Date Join to be dropped.

Just add it back from ExecutionStartDate to Date

Re: SSIS Catalog DB Dashboard

stretcharm — Wed, 27 Sep 2017 20:41:00 GMT

Updated version 0.77 templates (blank docs as I cannot upload templates)

Changed to use Ribbon charts
Fixed Gantt Calculation
Added Some Drillthroughs

Fixed Date join issue
Improved query performance on large SSIS DBs

Small template doesn't include the rows or network diagrams that can be slow.

I've also added a post in the Quick Measures for the Text Gantt Dax

http://community.powerbi.com/t5/Quick-Measures-Gallery/Text-Gantt-Chart/m-p/253466

Re: SSIS Catalog DB Dashboard

stretcharm — Mon, 06 Nov 2017 19:00:11 GMT

Updated version 0.78 templates (blank docs as I cannot upload templates).

Use the Server Name Parameter and Apply. Then do a refresh. If you want the Rows manually Refresh Execution Stats Rows as it's not included in the refresh.

Changes.

Added Percentage completion estimates to the Currently Running Tab
Improved query performance on large SSIS DBs
Execution Stats has commented out the time filter as its quicker on my server. If you have a large ssisdb you may want to uncomment.
Improved query for Execution Hierarchy and it's now included in the Refereses
Improved query for Execution Stats Rows. Still not not included in the refresh as it can be slow, but you can refresh manually.
Add a Execution Comparison tab
Add a Execution Over Time tab using scatter play axis

Here are some new screen shots

Re: SSIS Catalog DB Dashboard

wcrockett — Wed, 20 Dec 2017 17:57:31 GMT

This is amazing!

We have an SSIS environment that runs all packages from file system and we do not have an SSISDB. Is this still able to work? I was unable to get it set up because of the missing SSISDB. Packages are all on file system.

Thanks for contributing this!

-Wes

Re: SSIS Catalog DB Dashboard

stretcharm — Wed, 20 Dec 2017 20:30:04 GMT

Thanks.

I'm afraid no it only works with SSISDB,

I recommend if your using SQL 2012 or above you try deploying to the catalog as you get all the SSISDB logging for free and it's very good.

In theory you could use file based ssis and create similar logs your self, but it would be lots of work. If they logs are the same as the SSISDB tables you could adapt the powerbi to use files instead of a db.

Is there a reason why your not using the SSIS Catalog?

Phil

Re: SSIS Catalog DB Dashboard

wcrockett — Wed, 20 Dec 2017 20:42:18 GMT

Thanks for the info. All of our packages are from a vendor solution so we don't have a whole lot of control over how they are deployed and updated.

Re: SSIS Catalog DB Dashboard

stretcharm — Wed, 20 Dec 2017 21:02:41 GMT

OK. You could suggest they get with the times. :smileylol:

If they give you any execution logs you could try building your own dashboard from them.

PowerBI is also very good at loading data from files.

Most of my dashboard is driven from the execution statistic data.

Re: SSIS Catalog DB Dashboard

wcrockett — Wed, 20 Dec 2017 21:05:51 GMT

I sent the inquiry today as to whether this is something on their roadmap. I will have to look in to exection logs. The vendor does have their own dashboard for tracking, configuring, and managing the product from an ETL/DW perspective, but they don't have any analytics built in to show trend, day over day, etc. Theorhetically, I could learn their DB well enough to build off of.

Re: SSIS Catalog DB Dashboard

stretcharm — Wed, 20 Dec 2017 21:33:34 GMT

Sounds promising. I doubt they have done anything too radically different.

However as long as you can get the package start and ends you've got the basics needed to do the trends.

Re: SSIS Catalog DB Dashboard

sjt003 — Mon, 12 Feb 2018 18:57:32 GMT

Hi,

This looks awesome. I wasn't able to run it though as we seem to have too much data (though we only keep 14 days in the catalog). The notes mention a "small" version, but I couldn't figure out where the file for that is in in the download. Is it in a different version?

I'm also getting a duplicate ExecutionID key on the Execution query. I checked the table though and it only exists once. Maybe being duplicated by a join?

Thanks,

Scott

Re: SSIS Catalog DB Dashboard

stretcharm — Mon, 12 Feb 2018 20:49:35 GMT

Thanks.

It can sometimes take a bit of getting going on the initials loads. Try ensuring each of the queries work in the query editor.

If you get any errors referring to Formula.Firewall: then try turning off privacy in the options. This can also help performance.

How many projects, ssis executions do you have a day? I have about 180 executions with 8700 steps on a day and can easily load 30 days worth of history. However I tend to use 10 days.

You may want to test the indiviual queries run ok on your environment in Mgmt Studio. Also running index maintainance on the SSIS Catalog db can help. I've tried to optimise based on my data.

There is a small version in the post on the v77. I stopped creating it as I improved the query speed so it was not as bad and it was a pain to keep it up to date.

However it's easy to may a small version, just disable the Load for Execution Path Map. Execution Hierarchy and Execuction Statistics Rows. They are not essential and the queries are slow, especially the rows as it does string maniplation on the messages.. The Network, Sankey and Rows tabs will not be updated.

The Execution Key should be unqiue however check what projects and folders you have. Maybe there is a combination that causes duplciates. If so let me know and I can update the joins.

SELECT  ex.[execution_id] AS ExecutionID
FROM    [catalog].[executions] ex
        JOIN [catalog].[packages] p ON p.name = ex.package_name
        JOIN catalog.projects pr ON p.project_id = pr.project_id
                                    AND ex.[project_name] = pr.name
        JOIN catalog.folders f ON pr.folder_id = f.folder_id    
WHERE ex.[start_time] > DATEADD(DAY, -1* 3, CONVERT(DATE, GETDATE()));

I've also been working on a very cut down version that is for currently running ssis and errors from today which add when I add then next update.

Re: SSIS Catalog DB Dashboard

sjt003 — Tue, 13 Feb 2018 21:41:25 GMT

Hi,

We have about 600 executions a day in production, some packages may generate a lot of messages based on their steps, looping or if there were memory errors (not usual but has happened). The event_messages view has about 20 million rows.

I was able to get the queries to start refreshing in test, which has much less activity. That's where I got the duplicate execution key error. It looks like the problem is that I have some situations where packages with the same name exist in multiple places. In one case, I have two folders with virtually the same projects and packages underneath because there were some variations in the packages requiring different code but otherwise the naming still made sense. I think your join from catalog.executions to catalog.packages should be different. It's on package name, which doesn't need to be unique. Perhaps it could be on execution.object_type and execution.object_id. My object_type is always = 20

SELECT				ex.execution_id
FROM				[catalog].[executions] ex 
JOIN				[catalog].[packages] p 
					ON p.package_id = ex.object_id 
					AND ex.object_type = 30 -- Package 
JOIN				catalog.projects pr ON p.project_id = pr.project_id AND ex.[project_name] = pr.name 
JOIN				catalog.folders f ON pr.folder_id = f.folder_id 
WHERE				ex.[start_time] > DATEADD(DAY, -1* 3, CONVERT(DATE, GETDATE()))

UNION ALL

SELECT				ex.execution_id
FROM				[catalog].[executions] ex 
JOIN				catalog.projects pr 
					ON pr.project_id = ex.object_id
					AND ex.object_type = 20 -- Project
JOIN				catalog.folders f ON pr.folder_id = f.folder_id 
WHERE				ex.[start_time] > DATEADD(DAY, -1* 3, CONVERT(DATE, GETDATE()))

Thanks,

Scott

Re: SSIS Catalog DB Dashboard

sjt003 — Tue, 13 Feb 2018 21:56:28 GMT

Actually, it looks like you need the package GUID in your query, so can't just skip over the package view. Maybe this would work (I'm still getting familiar with the SSISDB objects):

SELECT				ex.execution_id
FROM				[catalog].[executions] ex 
JOIN				catalog.projects pr 
					ON pr.project_id = ex.object_id
					AND ex.object_type = 20 -- Project
JOIN				catalog.folders f ON pr.folder_id = f.folder_id 
JOIN				[catalog].[packages] p 
					ON p.project_id = pr.project_id 
					AND ex.package_name = p.name

Re: SSIS Catalog DB Dashboard

stretcharm — Sun, 18 Feb 2018 13:02:43 GMT

HI.

I don't use the PackageGUID or PacakgeFormat Version for anything so I think they can be removed along with the join to the Package

Try changing the Execution Query to this. (note this from the 0.81 query, but I just removed references to the [catalog].[packages])

        --[package_guid] PackageGUID ,
        --[package_format_version] PackageFormatVersion ,
        CONVERT(DATE, pr.last_deployed_time) AS LastDeployed,
		DATEDIFF(MS, ex.start_time, ex.end_time)/ 1000 / 60 as DurationInMinutes,
		SubString(Convert(Varchar(20),ex.[start_time]), 6, 11) + ' (' + Convert(Varchar,ex.[execution_id]) + ')' as Execution,
		ex.[project_name] + ' : '+ f.name as ProjectAndFolder,
		ex.[project_name] + ' : '+ f.name + ' : ' + ex.[package_name] as ExecutionRootKey,
		ex.[package_name] + ' (' + Convert(varchar,pr.project_id) + ')' as ExecutionRootShortKey,
		CASE WHEN ex.[status] = 4 THEN 1 ELSE 0 END as FailedExecutions,
		SubString(Convert(Varchar(20),ex.[start_time]), 6, 8) as ShortStart
FROM    [catalog].[executions] ex
        --JOIN [catalog].[packages] p ON p.name = ex.package_name
        JOIN catalog.projects pr ON --p.project_id = pr.project_id      AND 
									ex.[project_name] = pr.name
        JOIN catalog.folders f ON pr.folder_id = f.folder_id	
WHERE ex.[start_time] > DATEADD(DAY, -1* 3, CONVERT(DATE, GETDATE()));

I need to do some testing, but if this works I'll remove the fields in the next version.

I also spotted a duplicate with the currently running queries so I've got a new version which fixes this and a few other tweaks.

Re: SSIS Catalog DB Dashboard

stretcharm — Mon, 02 Apr 2018 14:20:33 GMT

New Version v.0.81

New cut down version designed for monitoring Currently running packages and todays errors
Fix Join to Currently Running Packages
Dynamic Date Dimension
Move Key Calcs into SQL from Computed Calcs to improve load and compression
Improve Execution Stats Query

Drilldown Only Filter for Execution & Execution Gantt

Blank docs attached

Templates also available at

https://github.com/stretcharm/SSIS-DB-Dashboard

Re: SSIS Catalog DB Dashboard

stretcharm — Fri, 06 Apr 2018 17:39:48 GMT

New Version v.0.82b

Improved load time and model size.

Fixed Execution Package Duration for Master Packages. Use Start and End duration. Minutes is correct. MS is better but looses precision.
Use Synced slicers for March PowerBI RS Release.
Mark Date Query as the Date Table.
Update fnVLOOKUP to do a ordered match.
Switch Stats stare/end date times to Date and Seconds from Start Date to improve the model storage. Datetime is now a dax expression.
Removed Rows as I don't use it and its slow and the key consumes lots of model space.

Remove test code for Execution stats

No New Currently Running Version

Blank doc attached

Templates also available at https://github.com/stretcharm/SSIS-DB-Dashboard

Next version I plan to bookmarkise.

Re: SSIS Catalog DB Dashboard

lotusnotes — Fri, 13 Apr 2018 14:58:12 GMT

Date function uses 'column1' but then it can't find it!!!!

Any ideas?

Thanks

Re: SSIS Catalog DB Dashboard

stretcharm — Sat, 14 Apr 2018 09:59:58 GMT

Not sure why your seeing this error.

The data function is generic and column1 is a temp column as it's building a list. It is passed a start and end date.

The latest versions of the dashboard use a dynamic date range so check that your Server and DB are set correctly in the parameters and you have at least one execution in the last days (again set by parameter.

They check execution and data range queries return a preview in the query editor.

If refresh the preview for date and it should show a list of dates that match the executions using the date function.

If your just trying to use the date function in your own pbi doc then check this post by mattmasson

https://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/

Re: SSIS Catalog DB Dashboard

kdrouillard — Mon, 30 Apr 2018 19:57:00 GMT

I keep getting " There weren't enough elements in the enumeration to complete the operation." when I try to run this in PowerBI desktop. Have you come across this before?

Thanks!