Project Online Absolute Priority Ratings

When using project online to prioritize your project against drivers. It can be useful to know how it comes up with the priority calculation

If we create some drivers with manual weights

Then analyse our projects against those drivers

We can replace the weights of Low / Strong /  Extreme etc to have the following number represenations

So now our analysis would look like this

If you then multiply these number by the driver weighting you have given them eg. For Project 1 / Driver 1 we woud do 6 (strong rating) * 10% (manual weighting for driver). So we would get a score of 0.6

So then to get your absolute priority for the project you can just add up the individual driver scores

Then you can go from absolute priority to the project online priority score by turning the absolute priority into a percentage of the total 

Adding JIRA Custom Fields into Power BI report

Steps

1. Go to view custom fields in JIRA
   https://<yourdomainhere>.atlassian.net/secure/admin/ViewCustomFields.jspa
2. Find your custom field and choose settings > Screens
   
3. Note down the custom field name JIRA uses to store the information
   
4. Open PowerBI desktop and open the JIRA project
   (If you dont have a JIRA project to start with you can get one from here which is a good start https://onedrive.live.com/?authkey=%21AH4_6OHXy6siE74&cid=E7E2C1CD5556023F&id=E7E2C1CD5556023F%21116639&parId=E7E2C1CD5556023F%21116638&action=locate )
5. Choose edit Queries
   
6. Wait for the query to load up
7. On the right side under applied steps click the cog icon
   
8. Click to select the custom field using the name that we found in the JIRA url (step 3)
   
9. Rename the field you just added if you want it friendlier in the PowerBI report later
   
   
   
10. Click Close & Apply. This will run the query again which might take a little while
11. You new field should be available to use under the GetIssues data set under fields

Querying SSIS Parameter information from SSISDB

On your SSIS SQL Server you can run the following code against the SSISDB database to investigate parameter information stored in the SSIS catalog

There are multiple versions or the parameter stored in the interal.object_parameters table so this will get only the latest version of the each parameter/connection

The Query

/*
    Check package parameters and connection string settings
    for a project
*/
WITH projectVersions AS
(
    SELECT p2.name,p2.project_id, MAX(p1.project_version_lsn) versionlsn
    FROM internal.object_parameters p1
    INNER JOIN internal.projects p2 ON  p1.project_id = p2.project_id
    GROUP BY p2.name,p2.project_id
)
SELECT  CASE LEFT(params.parameter_name,3) 
        WHEN 'CM.' THEN
            'Connection Manager'
        ELSE
            'Package Parameter'     
        END as Area ,  
        params.object_name ,
        params.parameter_name ,
        params.parameter_data_type ,
        params.design_default_value ,
        params.default_value
FROM    SSISDB.internal.object_parameters params
        INNER JOIN projectVersions proj ON params.project_id = proj.project_id
WHERE   proj.versionlsn = params.project_version_lsn
--AND proj.name = 'EDI' -- change for another ssis project
ORDER BY 1

Example of the output

Area	object_name	parameter_name	parameter_data_type	design_default_value	default_value
Package Parameter	EDI	CommandString	String	C:\SSIS\	C:\SSIS\EDI
Package Parameter	EDI	ServerName	String	StockSource	NULL
Connection Manager	EDI	CM.EDI.ConnectionString	String	Data Source=x;Initial Catalog=x;Provider=SQLNCLI11;Integrated Security=SSPI;Auto Translate=False;	NULL