Using Power BI to Visualize Data from VSTS

Using Power BI to Visualize Data from VSTS Using REST APIs to Better Understand User Stories 16 August 2018 Schlumberger-Private

Glen Accardo § Business Intelligence Lead § Schlumberger, Katy Drilling Software Center § Currently working Drilling Operations software § Linked. In https: //www. linkedin. com/in/glenaccardo/ § Power BI User Group https: //www. pbiusergroup. com/network/members/profile? User. K ey=bb 8 cc 6 ed-1 a 59 -4014 -9095 -30174 d 0 ab 448 Schlumberger-Private

Some of the Data in Visual Studio Team Services Initiative Requirement 1 Requirement 2 User Story 1 -1 User Story 2 -1 User Story 1 -2 User Story 2 -2 Schlumberger-Private Requirement 3 User Story 3 -1

More Details About User Stories Logical View Execution View Test Plan User Story Bug 1 Test Case 1 Bug 2 Test Case 3 Test Suite 1 Test Suite 2 Test Case 1 Test Case 2 Test Case 3 Schlumberger-Private Results View Test Run Test Results Test. Results

Basic Problem - VSTS gives a way to query bugs, user stories, etc. - VSTS is built for software developers - Many of our users aren’t software developers Schlumberger-Private

Basic Solution - Power BI allows cross linking between user stories, bugs, test cases, etc. - Then we can have a “product” view of user stories instead of a bunch of “developer” views. - Each user story has acceptance criteria. - Has test cases to verify the acceptance criteria - Everyone agrees that the test cases fully vet the acceptance criteria - All test cases run - tests pass - bugs are within acceptable limits - And All The Details with some nice arithmetic!!!! Schlumberger-Private

Data Model Two distinct groups of information: current and trend - Current Work. Item State - Bug and User Story Trend Very different organization to both Schlumberger-Private

Work Item Current State - Is not a data cube in any real sense - Is a relational model, almost all of the relations are parent/child - Power BI processes it very well Schlumberger-Private

Work Item State Goal • Use a shared query to get information • Get detailed information about test cases, bugs, user stories, etc. Running a WIQL query using VSTS • Doesn’t give the results of the query • Does give a list of Work Item Ids • The list of Ids need to be grouped for calls to another API endpoint for details • The starting point for getting this data is here • Don’t expect a lot of flexibility! Schlumberger-Private

Important Stuff to Know about VSTS. Acount. Contents() • Refreshing the data in the desktop application is not the same as refreshing the data online. Publish/refresh often! • When building a URL for a web API, segregate everything into two parts: • A completely static string for a URL • Variable string(s) as an Options parameter or Relative. Path parameter • APIs often build on others, for example, get a list of something, then get the details of those things. • Step one of doing this is to build a single large table with everything. • Step two is to break that table into smaller, more useful tables. Schlumberger-Private

Three Ways to Call VSTS. Account. Contents() VSTS. Account. Contents (URL) • Use this form when URL is always constant VSTS. Account. Contents (URL, [Query=[id=#"GUID"]]) • Use this form if the URL has a variable such as • • ? id=12345 ? expand. All=true VSTS. Account. Contents (URL, [Relative. Path=[Test. Run. Id] & "/results"] • Use this form if one of the levels of the URL is a variable. https: //server. visualstudio. com/project/_apis/test/runs/{run. Id}/Statistics Schlumberger-Private

Bug and User Story Trend VSTS odata API • The API works exactly as advertised and documented • The Power BI functions aren’t even close to working Bugs • https: //community. powerbi. com/t 5/Service/VSTS-odata-refresh-issue/m-p/440082#M 46717 • https: //stackoverflow. com/questions/51025134/using-vsts-feed-in-power-bi-to-access-odata • https: //developercommunity. visualstudio. com/content/problem/259275/crash-when-refreshing-vsts-odatafeed-in-appspower. html Schlumberger-Private

I Wish I Knew These Things When I Started… • Refreshing the data in the desktop application is not the same as refreshing the data online. • When building a URL for a web API, segregate everything into two parts: • A completely static string for a URL • Variable string(s) as an Options parameter or Relative. Path parameter • Expect some redundancy in building tables • Stay true to the data model of the underlying application, Power BI can likely deal with it. • Notepad++ has language definitions for DAX and Power Query. Simple highlighting and formatting makes a world of difference! Schlumberger-Private

Some DAX Stuff Bug = FILTER( 'All Data', AND( 'All Data'[Work. Item. Type]="Bug", 'All Data'[State] <> "Removed" ) ) Requirement = FILTER( 'All Data', AND( 'All Data'[Work. Item. Type]="Requirement", 'All Data'[State] <> "Removed" ) ) Schlumberger-Private

Some DAX Stuff Test Case Outcome Summary by Parent = SUMMARIZE( FILTER ('Test Result', 'Test Result'[test. Case. id] <> BLANK() ), 'Test Result'[test. Case. id], 'Test Result'[Outcome], "Result Count", COUNT('Test Result'[Outcome]), "Last Completed Date", MAXA('Test Result'[completed. Date]), "First Completed Date", MINA ('Test Result'[completed. Date]) ) Schlumberger-Private

Some DAX Stuff Test Case Most Recent Result = SUMMARIZE ( FILTER ( 'Test Case Outcome Summary by Parent', 'Test Case Outcome Summary by Parent'[Outcome] <> BLANK () ), [Last Completed Date] <> BLANK () ), 'Test Case Outcome Summary by Parent'[test. Case. id], "Last Completed Date", MAXA('Test Case Outcome Summary by Parent'[Last Completed Date]) ) Last Run Date = DATE ( YEAR ('Test Case Most Recent Result'[Last Completed Date]), MONTH('Test Case Most Recent Result'[Last Completed Date]), DAY('Test Case Most Recent Result'[Last Completed Date]) ) Schlumberger-Private

Some DAX Stuff Outcome = SELECTCOLUMNS( FILTER( 'Test Case Outcome Summary by Parent', 'Test Case Outcome Summary by Parent'[Last Completed Date] = 'Test Case Most Recent Result'[Last Completed Date] ), [test. Case. id] = 'Test Case Most Recent Result'[test. Case. id] ), "Outcome", [Outcome] ) Schlumberger-Private

Some DAX Stuff Readiness Factor 1 = IF ('User Story'[Has Acceptance Criteria? ], 1, 0 ) Readiness Factor 2 = IF ('User Story'[Readiness Factor 1] = 1 && 'User Story'[Count of related tests] >= 1, 2, 'User Story'[Readiness Factor 1] ) Readiness Factor 3 = IF (('User Story'[Readiness Factor 2] = 2) && 'User Story'[Is TC Covered? ], 3, 'User Story'[Readiness Factor 2] ) Schlumberger-Private

Some DAX Stuff RF Indicator 1 = IF( [Readiness Factor 1] = 1, "▲", "▼" ) RF Indicator 2 = SWITCH ( [Readiness Factor 2], 2, "▲", 1, "▼", "–" ) Schlumberger-Private

//------------------------------------------------------------// Inputs: VSTS Server, VSTS Project, VSTS Test Query GUID Parameters // Output: A single flat list of information about each work item in the test query specified. // The query must return a heirchy of test plans, test suites, and test cases. The output of // this query will be split into the test plan, test suite, and test case tables. // The All Test Data table is not used in the report views, and is not linked in the data model. //------------------------------------------------------------let // Build the base tables that will be combined for the final test data table. Open. Edit. URL = "https: //" & #"VSTS Server" & ". visualstudio. com/" & #"VSTS Project" & "/_queries? id=", Parents = Get. Work. Item. Ids(#"Test Query GUID"), Source = Get. Work. Items(Parents[Column 1]), // Expand the Source table into rows and columns with the stuff read from VSTS. #"Expanded Work. Items" = Table. Expand. Record. Column(Source, "Work. Items", {"value"}, {"Work. Items. value"}), #"Expanded Work. Items. value" = Table. Expand. List. Column(#"Expanded Work. Items", "Work. Items. value"), #"Expanded Work. Items. value 1" = Table. Expand. Record. Column(#"Expanded Work. Items. value", "Work. Items. value", {"id", "fields", "relations", "url"}, {"Work. Items. value. id", "Work. Items. value. fields", "relations", "Work. Items. value. url"}), #"Expanded Work. Items. fields" = Table. Expand. Record. Column(#"Expanded Work. Items. value 1", "Work. Items. value. fields", {"System. Area. Path", "System. Iteration. Path", "System. Work. Item. Type", "System. State", "System. Assigned. To", "System. Created. Date", "Microsoft. VSTS. Common. Resolved. Date", "Microsoft. VSTS. Common. Closed. Date", "System. Title", "Slb. SWT. User. Impact", "System. Description", "Microsoft. VSTS. Common. Acceptance. Criteria", "Microsoft. VSTS. Scheduling. Story. Points"}, {"Work. Items. value. fields. System. Area. Path", "Iteration Path", "Work. Items. value. fields. System. Work. Item. Type", "Work. Items. value. fields. System. State", "Work. Items. value. fields. System. Assigned. To", "Work. Items. value. fields. System. Created. Date", "Work. Items. value. fields. System. Resolved. Date", "Work. Items. value. fields. System. Closed. Date", "Work. Items. value. fields. System. Title", "Work. Items. value. fields. Slb. SWT. User. Impact", "Description", "Acceptance Criteria", "Story Points"}), // Rename columns, change data types, clean up description and accepatance criteria. #"Renamed Columns" = Table. Rename. Columns(#"Expanded Work. Items. fields", {{"Work. Items. value. fields. System. Area. Path", "Area. Path"}, {"Work. Items. value. fields. System. Work. Item. Type", "Work. Item. Type"}, {"Work. Items. value. fields. System. State", "State"}, {"Work. Items. value. fields. System. Created. Date", "Created. Date"}, {"Work. Items. value. fields. System. Title", "Title"}, {"Work. Items. value. fields. Slb. SWT. User. Impact", "User. Impact"}, {"Work. Items. value. url", "Url"}, {"Work. Items. value. id", "Work. Item. Id"}, {"Work. Items. value. fields. System. Assigned. To", "Assigned To"}, {"Work. Items. value. fields. System. Resolved. Date", "Resolved. Date"}, {"Work. Items. value. fields. System. Closed. Date", "Closed. Date"}}), #"Changed Type" = Table. Transform. Column. Types(#"Renamed Columns", {{"Created. Date", type datetimezone}, {"Resolved. Date", type datetimezone}, {"Closed. Date", type datetimezone}, {"Work. Item. Id", type text}}), #"Replaced Value" = Table. Replace. Value(#"Changed Type", "Narrative (one or two sentences describing the story). Acceptance Criteria: (presented as Scenarios) Scenario 1: Title Given [context] When [event] Then [outcome] And [another outcome]. . . ", "", Replacer. Replace. Value, {"Description"}), #"Replaced Value 1" = Table. Replace. Value(#"Replaced Value", null, "", Replacer. Replace. Value, {"Description"}), // Make a URL that can be used to open/edit the work item. #"Removed Columns" = Table. Remove. Columns(#"Replaced Value 1", {"Column 1", "Url"}), Make. URL = Table. Add. Column(#"Removed Columns", "Url", each Open. Edit. URL & Text. From ([Work. Item. Id])), // Use the Parents table created earlier to insert a Parent Id for each item Add. Parents = Table. Join(Make. URL, {"Work. Item. Id"}, Parents, {"Column 1"}), #"Removed Columns 1" = Table. Remove. Columns(Add. Parents, {"Column 1"}), #"Renamed Columns 1" = Table. Rename. Columns(#"Removed Columns 1", {{"Column 2", "Parent. Id"}, {"Work. Item. Id", "Id"}}), #"Added Custom" = Table. Add. Column(#"Renamed Columns 1", "New Title", each try Randomize. String([Title]) otherwise ""), #"Removed Columns 2" = Table. Remove. Columns(#"Added Custom", {"Title"}), #"Renamed Columns 2" = Table. Rename. Columns(#"Removed Columns 2", {{"New Title", "Title"}}) in #"Renamed Columns 2" Schlumberger-Private

More Power Query Stuff //------------------------------------------------------------// This function comes from a blog post by James Broome. // https: //blogs. endjin. com/2016/07/querying-the-vsts-work-items-api-directly-from-power-bi/ //------------------------------------------------------------let Get. Work. Item. Ids = (GUID as text) => let Source = Json. Document(VSTS. Account. Contents("https: //" & #"VSTS Server" & ". visualstudio. com/Default. Collection/" & #"VSTS Project" & "/_apis/wit/wiql? api-version=" & #"VSTS API Version", [Query=[id=#"GUID"]])), work. Item. Relations = Source[work. Item. Relations], #"Converted to Table" = Table. From. List(work. Item. Relations, Splitter. Split. By. Nothing(), null, Extra. Values. Error), #"Expanded Column 1" = Table. Expand. Record. Column(#"Converted to Table", "Column 1", {"target", "rel", "source"}, {"Column 1. target", "Column 1. rel", "Column 1. source"}), #"Removed Columns" = Table. Remove. Columns(#"Expanded Column 1", {"Column 1. rel"}), #"Expanded Column 1. target" = Table. Expand. Record. Column(#"Removed Columns", "Column 1. target", {"id", "url"}, {"Column 1. target. id", "Column 1. target. url"}), #"Expanded Column 1. source" = Table. Expand. Record. Column(#"Expanded Column 1. target", "Column 1. source", {"id", "url"}, {"Column 1. source. id", "Column 1. source. url"}), #"Ids and Parents" = Table. Rename. Columns(#"Expanded Column 1. source", {{"Column 1. target. id", "Work. Item. Id"}, {"Column 1. source. id", "Parent. Id"}}), #"Changed Type" = Table. Transform. Column. Types(#"Ids and Parents", {{"Work. Item. Id", type text}}), #"Changed Type 2" = Table. Transform. Column. Types(#"Changed Type", {{"Parent. Id", type text}}), Ids. List = Table. From. Columns({Table. Column(#"Changed Type 2", "Work. Item. Id"), Table. Column(#"Changed Type 2", "Parent. Id")}) in #"Ids. List" in Get. Work. Item. Ids //------------------------------------------------------------// This function comes from a blog post by James Broome. // https: //blogs. endjin. com/2016/07/querying-the-vsts-work-items-api-directly-from-power-bi/ //------------------------------------------------------------let Get. Work. Items = (ids as list) => let Pages = Get. Work. Items. Pages(ids), #"Converted to Table" = Table. From. List(Pages, Splitter. Split. By. Nothing(), null, Extra. Values. Error), #"Results" = Table. Add. Column(#"Converted to Table", "Work. Items", each Json. Document(VSTS. Account. Contents("https: //" & #"VSTS Server" & ". visualstudio. com/Default. Collection/_apis/wit/work. Items? $expand=all&api-version=" & #"VSTS API Version", [Query=[ids=[Column 1]]]))) in #"Results" in Get. Work. Items Schlumberger-Private

Some Power Query Stuff //------------------------------------------------------------// This function comes from a blog post by James Broome. // https: //blogs. endjin. com/2016/07/querying-the-vsts-work-items-api-directly-from-power-bi/ //------------------------------------------------------------let Get. Work. Items. Pages = (ids as list) => let Pages = List. Numbers(0, Number. Round. Up(List. Count(ids) /200)), #"Convert to Table" = Table. From. List(Pages, Splitter. Split. By. Nothing(), null, Extra. Values. Error), #"Split List to CSV" = Table. Add. Column(#"Convert to Table", "Custom", each Text. Combine(List. Transform(List. Range(ids, [Column 1]*200, 200), each Text. From(_)), ", ")), #"Removed Columns" = Table. Remove. Columns(#"Split List to CSV", {"Column 1"}), Custom = #"Removed Columns"[Custom] in Custom in Get. Work. Items. Pages Schlumberger-Private