Register
Submit a solution
The challenge is finished.

Challenge Overview

Basic requirements for this challenge:
  • Continue updating ASP.Net MVC Core Web API based on new workflow changes
  • Remain support Unit Testing that covered 90% or above
  • Update sql for sample data based on storyboard content
  • Update Postman based on new workflow changes

Technology Stack
  • .Net Core API for backend framework
  • Database: MS SQL Server

Deployment environment requirements
  • localhost
  • AWS EC2

Application Background
Our organization is structured by Value Streams after the SAFe industry model. A Value Stream represents an organizational sleeve that has a specific business focus with dedicated resources. For example, in our Insurance business we have multiple value streams such as Product, Inforce Operations, Marketing and Distribution etc. These values streams have roadmaps with target strategies and KPIs (Key Performance Indicators) they’re accountable to deliver on. The roadmaps are a rolling 4-6 Quarters of incremental outcomes representing the delivery of some type of business value. These outcomes ultimately achieve the strategy as measured by these KPIs.

We are looking for a solution to manage these roadmaps. This solution would be for a data entry UI , database repository, that will enable future Power BI data visualizations. This solution will enable multiple personas to access and input roadmap data and demonstrate how a Value Stream is achieving its strategy as measured by KPIs and delivery of incremental outcomes. Eventually, a simple integration between the UI and JH’s Backlog Management Tool (Rally) will be used to synch a few fields. This solution should enable an export of data that can be easily uploaded to a data visualization tool.

Problem
The primary goals for this UI and Database is to get users out of using excel spreadsheets and establish consistency in data being tracked across the division. Additionally, this solution will enable Value Streams to extract data in a format that’s indigestible by a Data Visualization tool reducing time spent creating manual PowerPoint presentations to demonstrate how strategy is progressing. Benefits are as follows:
  • Improved user experience tracking roadmap outcomes, KPIs and strategy progression.
  • Good usability and efficiency for entering data.
  • Consistency of roadmap data being tracked across all Value Streams in the U.S. Division with one source of truth.

Ability to easily export data and upload into a visualization tool enabling multiple personas to generate various data views.

Challenge Assets:
Individual Requirements
  • You will continue updating REST API for Costs page changes as explained below
  • IMPORTANT: Detail description below are just starting point. Feel free to suggest better practice to structurize the data and endpoints.
  • Make sure your REST API possible to consume for the frontend app
  • Pay attention for request body and response format based on Swagger file
  • Let’s discuss if you find any things need adjusted when build the REST API
  • Test the updated source with no error and pass the unit testing
1). Roadmap Export
  • Current endpoint:
    • GET {{url}}/roadmap/export?format=Excel&roadmapIds=1,2,3
    • GET {{url}}/roadmap/export?format=CSV&roadmapIds=4,5,6
  • After updated structure on previous backend challenge, roadmap export using XLS and CSV not display any data.
  • We need you suggest proper column based on frontend look
  • On previous version, we listed data from the same Roadmap to separate row
  • For this updated version, we need each row contains 1 id with their descendants data properly grouped
  • For the CSV, need remain use Comma separated. Suggest proper format
  • Let’s discuss any questions about this roadmap export
2). Resource Type
  • This is NEW endpoint that contains Resource Type
  • Fields need created are:
    • id
    • Name
  • Required endpoints to build are:
    • DELETE /api/ResourceType/{id}
    • GET /api/ResourceType/{id}
    • PUT /api/ResourceType/{id}
    • GET /api/ResourceType (return ALL)
    • POST /api/ResourceType
3). Role Rates
  • This is NEW endpoint that contains Role Rates
  • We need accept array of Role Rates data to submit/updated at a time.
  • Fields need created are:
    • Resource Type Id
    • Daily Loaded Labor Rate
  • Required endpoints to build are:
    • DELETE /api/RoleRates/{id}
    • GET /api/RoleRates/{id}
    • PUT /api/RoleRates/{id}
    • GET /api/RoleRates (return ALL)
    • POST /api/RoleRates
4). Monthly Days
  • This is NEW endpoint that contains all Monthly Days
  • We need accept array of months data to submit/updated at a time.
  • Fields need created are:
    • Year (We need able to store for different year, by default w/o include the year value need pull the latest year data)
      • Month (List all 12 full months)
        • Total Days
  • Required endpoints to build are:
    • DELETE /api/MonthlyDays/{year}/{month}
    • PUT /api/MonthlyDays/{year}/{month}
    • GET /api/MonthlyDays/{year} (return ALL)
    • POST /api/MonthlyDays/{year}/{month}
      • For all endpoints above need accept parameters for Year and Month
      • Each Year and month must only have 1 value

5). Value Stream Data Management
Resources
  • This is the endpoints that contains Resources for related Portfolio id and Value Stream Id
  • We need accept array of resource data to submit/updated at a time.
  • Fields need created are:
    • Id
    • Portfolio Id
    • Value Stream Id
    • Resource Name
    • Resource Type Id
    • Resource Rate (initial value need displayed based on related rate, but user need able to change the value. This endpoint need store its own value)
  • Required endpoints to build are:
    • DELETE /api/VSDataResource/{id}
    • GET /api/VSDataResource/{id}
    • PUT /api/VSDataResource/{id}
    • GET /api/VSDataResource (return ALL)
    • POST /api/VSDataResource
    • POST /api/VSDataResource/search
      • Need accept parameters for Portfolio id and Value Stream Id
      • Return all Resources data

Cost Center
  • This is the endpoints that contains Resources for related Portfolio id and Value Stream Id
  • We need accept array of cost center data to submit/updated at a time.
  • Fields need created are:
    • Id
    • Portfolio Id
    • Value Stream Id
    • Work Type
    • Cost Center
  • Required endpoints to build are:
    • DELETE /api/VSDataCostCenter/{id}
    • GET /api/VSDataCostCenter/{id}
    • PUT /api/VSDataCostCenter/{id}
    • GET /api/VSDataCostCenter (return ALL)
    • POST /api/VSDataCostCenter
    • POST /api/VSDataCostCenter/search
      • Need accept parameters for Portfolio id and Value Stream Id
      • Return all Cost Center data

6). Resource Allocation
  • This is the endpoints that contains Resource Allocation for related Portfolio id and Value Stream Id & VS Budget ($)
  • VS Budget ($) filter on top.
  • Fields need created are:
    • Resource Allocation Id
    • Portfolio Id
    • Value Stream Id
    • VS Budget ($)
    • Year
      • Value Stream Allocation (Need able to add more than 1 Value Stream Allocation)
        • Resource Id (This is linking from previous Resource, need pull the resource name, rate type and rate value)
          • Value Stream Work Allocated To (by default need to select the initial Value Stream for the selected resources, but dropdown should contains all Value Stream from all portfolio. This field need use Value Stream Id)
          • Work Type (This is linking from previous Work Type, need pull work type and cost)
          • Month (Note: we need support full 12 months for each year)
            • Allocation % = User need fill this value manually
            • Allocation Cost = The 'Allocation Cost' should be a calculated field. The formula would be the Rate value for that selected resource X (Total Days for that month) X (Allocation %).
          • Total Monthly Allocation % = Total Allocation is the SUM of the Allocation % rows for a resource for the related month.
          • Total Monthly Cost = Total Cost is the SUM of the Allocated Cost rows for a resource for related month.
  • Required endpoints to build are:
    • DELETE /api/ResourceAllocation/{id}
    • GET /api/ResourceAllocation/{id}
    • PUT /api/ResourceAllocation/{id}
    • GET /api/ResourceAllocation (return ALL)
    • POST /api/ResourceAllocation
    • GET /api/ResourceAllocation/search
      • Need accept parameters for Portfolio id, Value Stream Id & VS Budget
      • Return related data for Resource Allocation
    • GET /api/DataAllocation/export
      • Need able to export as XLS and CSV
      • Suggest proper columns for XLS and CSV based on storyboard look. Information need readable for each Value Roadmap.
      • Need accept parameters for Portfolio id, Value Stream Id, VS Budget and file format (XLS and CSV)

Unit Testing
All of your code should be test covered. You must cover at least +90% of the code.
You need to test all cases and in test case assertion you need to assert accuracy of all fields.
 

Final Submission Guidelines

Deliverables
- All updated source code.
- Updated Postman collections and configuration
- Updated Sample init data sql (00.init.sql, 01.DDL.sql & 02.TestData_Integration_WithTestRoadmaps.sql)

Review style

Final Review

Community Review Board

Approval

User Sign-Off

ID: 30100207