Creating a Business Intelligence Dashboard with R and ASP.NET MVC: Part 1

Creating a Business Intelligence Dashboard with R and ASP.NET MVC: Part 1.

Computers and Internet

Effectively Tracking Projects/Releases using Scrum, SharePoint, VSTS (Visual Studio Team Foundation Server) and Excel

Note : The below article may not be appropriate for Latest version of  VSTS since it provides web interface for all the tasks mentioned in this  article, due to non-availability of older version of studio some of the  snips/screenshots are from VS 2010.




Opportunity: Most of  the users in our Organization use Visual Studio for creating Requirements, work  Items etc. Currently Developers, Testers and Program Managers/Solution Managers  use VSTS as a Source code control and a repository for maintaining and tracking  Requirements, Tasks, Risks, Issues.

SharePoint is a common tool for collaboration and its primarily being  used as a document repository. since these tools can be connected seamlessly  with out of box feature and zero coding effort. There is an opportunity to use  these for effectively surfacing the data in VSTS using Excel on  SharePoint.

These  tools can be used for creating dashboards accessible over web browser to  audience across the organization. It would be help Project Stakeholder  effectively track an Agile Project.

An  ideal dash board can be built with following features contain the  following

·         Executive Summary :

·         high  level description of current status of the project. Status indicated in Red,  Green, Yellow colors and summary of any Risks that need immediate attention  (beyond teams control that might need external or higher management  intervention/help)

·         High  level project plan (this is useful in case of a project having multiple sprints,  each sprint ending with a specific done criteria that helps in starting next  sprint)

·         Current  week Highlights/Low lights

·         Scrum Section

·         Burn  down

·         Sprint  Backlog – should include the estimated and actual hours

·         Sprint  Dates

·         Daily  Scrum Note (Very important in case several teams are working across geographies  in parallel sprints)

Below  is a mock-up of a Dash board in discussion

Description: Description: C:\Users\nadattap\AppData\Local\Temp\msohtmlclip12\clip_image001.jpg

High  level solution approach

·         Create  Requirements/Backlog in TFS

·         Using  Excel TFS data access tab, pull all the work-items (Requirements/Tasks etc) to  Excel

·         Excel  is hosted on to  SharePoint as Web Part.

·   Each  sheet in excel can be hosted as separate Web Part and represents one section in  the above dashboard

·         Developers, Testers will update the VSTS work-items as they progress  during the sprint

·         Sprint  Burndown chart is prepared in Excel

·         Using  refresh feature in Excel the data is refreshed in Excel on a day to day basis  and available for global view every day. Scrum Master can refresh the excel on a  daily basis to provide updated view of data.

·         A List  web part in SharePoint can be added to site to Update daily Scrum  Notes.

·         The  excel work book that is uploaded to SharePoint can have one sheet exclusively  for Capacity and team details.

Description: Description: Description: Machine generated alternative text: - Excel buit-in TFS data adaptor product Owner Visual Studio Team Foundation Server ç 111E111 — I riq I Pr.ic,eicog I Iun Downi Senior Management Global Audience Chicken(s)


·         Existing infrastructure like Visual Studio, Visual Studio Team  Foundation Server, SharePoint, and Excel that are currently discretely used. Can  be integrated with minimal our of box features without coding

·         No  custom application is required. Graphs and calculations feature in Excel can  used to prepare Daily Burn Down Chart

·         SharePoint can be use for Hosting and provides access over intranet.  User Access feature of SharePoint can be used for controlling  access.

·         No  additional training is required since Developers and tester will update their  Tasks as they do in Visual Studio

Solution Details

·         Product  Backlog :

·         VSTS provides an ideal way to capture  requirements. Subsequently the requirements can be linked to Epic, Stories,  UserScenarios, UseCases, Code check-ins and Test Cases. This helps maintain  requirement matrix in a clean way.

All the Requirements are created in VSTS as work Item  with type Requirement  by the  Product Owner, all necessary documentation and wireframes are attached to the  requirements. Each of these requirements are decomposed to a Use cases. ,  this helps in fine grain understanding of the functionality that is being built  into the system.  VSTS provides a way to create Use Cases and linked to  parent User Scenarios and Requirements. Below is a the traceability  matrix  that can be enabled using VSTS

Description: Description: Description: Machine generated alternative text: On-going activity — Refined Every sprint Product Backlog Created as work Item type=Requirement in VSTS. Ideally would also have estimates Created during Sprint Planning Performed and updated during Sprint

·         Against each of the Use Case, Development, Test  and Infrastructure (Build, Documentation, Server Setup) VSTS Work items with  type  Task are created, each task not exceeding Eight  Hours  . Having granularity of the tasks at eight hours not only  helps in better tracking on a day to day basis but also helps in getting a  meaning full burndown.

·         Built in features of VSTS help maintain history  and tracking, easy access to external apps.

Area Path attribute is used to maintain the Functional  version  and Iteration Path is used to maintain the Sprint version of the  project. Work items, scope can be segregated and maintained for use by different  Sprint teams by using Iteration path attribute.

Benefits :

§  A clean Requirements traceability matrix,  along with data required for tracking the sprint will be available.

§  Having the above matrix also helps Geographically  disparate teams have visibility to respective owners of Tasks

§  Helps Product Owners to know the parts of  requirements that are getting implemented and  provides decision making  ability on what to be added.

·         Sprint Backlog and Sprint Planning  Meeting:

·         All the requirements along with their type,  sub-type can be pulled into an excel sheet during Sprint Planning meeting, this  helps Product Owner to pick the right scope for the Sprint.  The Estimates  for the requirements are  directly retrieved from Baseline Work attribute of Task Work  Item of VSTS and need not be added to excel manually

Description: Description: Description: Machine generated alternative text: 439477 Everest: Portal Rebranding Change .CSS 4 0ev 439478 Everest: Portal Rebrandrng Changing Logos 4 0ev 439479 Everest: Portal Rebranthng Heading changes in Admin pages 4 Dey 439480 Everest Portal RebrandFng Verify Rebranding Color on each page 8 SUA 439481 Everest: Portal Rebrandng Verify rebranding Fonts on header and footer of each page 6 SUA 4394.82 Everest: Porta[ Rebranthng Need to cross check branding logo/textj image exactly fit where ever change happens. 8 SUA 439483 Everest: Portal RebrancUng Verify Alignment of page 8 SUA 439484 Everest: Portal RebrancUng Verify existing localization should not change with Rehranding changes 6 SUA 439485 Everest: Portal Rebrandung Need to check if inner link is given for logos/Headers and Footers and it works properly. 8 SUA 388294 Agreement Numbers are available in Data Stage but not flowing down to Everest. Change DataStaçe proc for Filter condition 2 0ev ________ ___________________________________________________________________________ 2 SCiA availableforClaim 4 SUA 434932 Difference in Price for a deal in VolumeLicense and Saleorder table Change to spc_uploadDataStageSaleData 2 0ev 434933 Difference n Price for a deal in VolumeLicense and Saleorder table Applying new claim logic for Post 20th March Agreements 1 Dey . . . . . Verify aggregated price in saleorder against volumelicense for Existing and New 434934 Difference in Price for a deal in VolumeLicense and Saleorder table 8 Agreements SUA wise estimates ( 17 _58 o ‘I Common Tasks I I 89 20 Total Esitmates r L 46 20 J 388295 388296 Agreement Numbers are available in DataStage but not flowing down to Everest Agreement Numbers are available n DataStage but not flowing down to Everest Verify the data consistency between DataStage and VolumeLicense for 2 Day5 Verify future PurchaseOrderRecieveo ate flows into VolumeLlcense, SaleOrder and

·         Capacity Vs Estimates : Since available  capacity is already available in one of the sheets in the excel. Graphs similar  to below can provide visual representation of Capacity to Estimates

Description: Description: Machine generated alternative text: Sprint 2 Capcity (Hours) Sprint 2 Work(Hours) • Dey •SQA • PM • Dey • SQA • PM

All the estimates are entered in the Baseline  Work  field for Task WorkItem in VSTS . This forms the  basis for calculating the overall estimates for the sprint

Description: Description: Machine generated alternative text: New Task 4*  New User Scenario 3* New Use Case 2* New Requirement 1* Start Page Save Workltem I -_i  IL’’ New Task 4 : TF20012: Field Title cannot be empty. Title <Required> TaskID O Created By Narasimha Rao Dattappa Created Date Classification Status Application State Active — Reason Accepted — Areapath <Required> Assignedto <Required> — Type <Required> Iteration path <Required> — Severity 3-Medium Source Discipline Priority 2 Start Date [J II’ Global Stack Rank Finish (Due) Date [J Fi” Blocking No DescriptionjHistoryjResolved/Closed DatalAttachments All Linkj! Estimates [SME5 Jimpact External Estimates Analysis VROM Analysis ROM Analysis Actual Cost PDM VROM PDM ROM PDM Actual Cost DevVROM DevROM DevActualCost Test VROM Test ROM Test Actual Cost Support VROM Support ROM Support Actual Cost Schedule Baseline Work Remaining Work Completed Work


§  One single view team can view during the sprint  planning meeting

§  Easy identification of available capacity and  required estimates will helps Product owners in choosing right scope.

§  Helps Geographically disparate teams have  visibility of scope of a particular Scrum team

§  Built in features to capture estimates and day to  day features

·         Burn down: All the team members would be updating  their respective Tasks in VSTS with Remaining Work and Completed work. These two fields along with Baseline Work Field help in  building the Daily BurnDown.

·         Cumulative Baseline work : Data for this trend  line is retrieve from the Baseline Work field highlighted in the  above  picture

·         Cumulative Completed work : Data for this trend  line is retrieve from the Completed Work field highlighted in the  above  picture

·         Cumulative Remaining work : Data for this trend  line is retrieve from the Remaining Work field highlighted in the  above  picture

Above data is pulled into one of the sheets of excel   hosted on SharePoint. Using Graphs feature of excel a burndown graph is created.  Once in a day data refresh by scrum master keeps the data upto date from VSTS to  Excel.


·         Helps Geographically disparate teams have  visibility to Scrum Team’s velocity

·         No additional coding required since this can be  accomplished using built in features of excel

·         Tracking progress and identifying risk  situation :

      • if Green line and Red line the graph are getting closer       that means the sprint is not on track
      • if Green line deviates of from the linear Blue trend line       in the graph would mean cumulative remaining work would take more time       than anticipated.

·         Scrum Meeting: All the Scrum notes can be  maintained in free form text box on the sharepoint. Notes can be added on a day  to day basis.

·         Caution : team  may be tempted not to attend the meeting but directly update notes, this should  be discouraged since the intent of scrum is not about sharing status updates but  for building collaboration.

·         Helps Geographically disparate teams have  visibility to Scrum Meeting details especially if teams work during different  timezones. Sending emails can be avoided.

·         No additional coding required since this can be  accomplished using built in features of SharePoint

Efficiency aspect

·         Teams spend considerable time preparing excel  sheets for sprint planning meeting, sending burndown emails, sending scrum notes  if the several teams are located in geographically disparate locations. This  solution provides a time saving as solution with minimal or no additional cost  (since internal tools are used without any additional coding  effort).

·         For scaling projects, an additional section can  be added per scrum team on the SharePoint to get multiple Scrum teams  view.



Moving from being a programmer to project manager

There were some interesting aspects that i came to know when i moved from a programmer to a software project manager.

As a software programmer, i would always think
“had i got that additional day, i can add this additional feature”
“oh why the hell is this so urgent..when it was like this for last so many years”..

Switch gear..as a Project Manager
–need to stick to the scope, schedule, budget
–its urgent because, it hurts users productivity..


Better photos

Suggested exposure value settings
■Bright sunlight coming over the back of you when taking a photo: -0.3 or -0.7 EV compensation
■For shots with strong light coming behind the subject (back lit): +0.7 or +1.0 EV
■Scene with bright sun: 0 to -2 EV
■Snow, beach or highly reflected water: -2/3 to -2 EV
■Close-up of white or yellow flower: -1/3 to -1 EV
■Dimly lit night sky: 0 to +2 EV
■Land or seascape taken just prior to dusk: 0 EV to +2/3
■Very dark or black objects: + 2/3 to +1 1/3

Hanlding Locale

if you want the C# code to consider the locale specified as part of browser settings and ignore what ever has been set for you desktop while running datetime.parse code
the following parameter needs to be used along with datetime.parse