Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
02-18-2018 04:57 AM - last edited 04-02-2018 07:20 AM
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,-Projec...
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-ref...
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-Appl...)
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
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
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
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, 😎 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.
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
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.
Date function uses 'column1' but then it can't find it!!!!
Any ideas?
Thanks
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/
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!
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!
No I've not seen that.
Has it just started happening or have you never been able to refresh?
Do all the previews work in Query Editor?
If not try refreshing the one at a time.
Do you know which query gives the error?
I've tested all the queries in PowerBI desktop, it's the Date component that fails with this error. If I provide values for the date function it works, but none of the charts load. I can see some data, currently running jobs for example, but overall I've never been able to get this to work.
Which version are you using?
In the latest version 0.82 I changed to dynamically use the Execution dates.
Try manually refreshing the Execuction Query.
Check that you have Executions in the last X days. This can be changed in the Parameters.
Then the Date Range (should show the first/last date for the executions) and then the Date.
Hi I'm using .82, I've attached a screenshot showing what's happening in the query editor...
For MaxDaysData I'm using 3.
If I change:
let
StartDate = DateRange[MinDate]{0},
EndDate = DateRange[MaxDate]{0},
/*Source = DateFunction(#date(2015, 1, 1), #date(2019, 12, 31), null),*/
/*Source = DateFunction(#"Date Dim Start Date", #"Date Dim End Date", null),*/
Source = DateFunction(StartDate, EndDate, null),
#"Removed Columns" = Table.RemoveColumns(Source,{"DateInt"})
in
#"Removed Columns"
to
let
StartDate = DateRange[MinDate]{0},
EndDate = DateRange[MaxDate]{0},
Source = DateFunction(#date(2015, 1, 1), #date(2019, 12, 31), null),
/*Source = DateFunction(#"Date Dim Start Date", #"Date Dim End Date", null),*/
/*Source = DateFunction(StartDate, EndDate, null),*/
#"Removed Columns" = Table.RemoveColumns(Source,{"DateInt"})
in
#"Removed Columns"
I get a valid result out of the Date function, but it doesn't seem to work.
OK. Odd.
Try changing it to so there is no dependancy on the DateRange Query
let /*StartDate = DateRange[MinDate]{0},*/ /*EndDate = DateRange[MaxDate]{0},*/ Source = DateFunction(#date(2015, 1, 1), #date(2019, 12, 31), null), /*Source = DateFunction(#"Date Dim Start Date", #"Date Dim End Date", null),*/ /*Source = DateFunction(StartDate, EndDate, null),*/ #"Removed Columns" = Table.RemoveColumns(Source,{"DateInt"}) in #"Removed Columns"
Do you see any Executions?
You could also try version 0.78 as this version had fixed data ranges.
I'll add some error handling into this area to default dates if it has problems getting them from a Query. Still don't know whats causing the problem. Ill try and reproduce it.
You can also try the Currently running version 0.81 see if that works its a lot less complex.
I've reproduce your error when I have no executions.
Check that your server and SSISDB name is correct and you have access. Also check you have executions in the last 3 days. If not open it up to a longer time.
You'll need access to the SSISDB Catalog tables.
To test in SQL Mgmt Studio try this SQL.
use ssisdb select Top 10 * FROM [catalog].[executions] ex order by 1 desc;
This code for Date will handle errors and use the MaxDaysData not Execution data.
let StartDate = try Date.AddDays(Date.From(DateTime.LocalNow()),(-1 * MaxDaysData)-1) otherwise #date(2017, 1, 1), EndDate = Date.From(DateTime.LocalNow()), Source = DateFunction(StartDate, EndDate, null), #"Removed Columns" = Table.RemoveColumns(Source,{"DateInt"}) in #"Removed Columns"
Turned out it was select permission on the views....my user only have RO access to the SSISDB database. The queries that hit the views didn't error out, just returned an empty result set, which masked the issue,
Thanks for your help!
Good.
The code will be better with some error handling and less dependancies.