Managed Risk - Dynamic Query System Architecture

Project Overview

The client is looking to build dynamic reports out of database tables of data.  Their current tool allows these reports to be built by a developer, but this requires a normal development cycle to build, test, and release these reports.  They would like to have a system in place that allows them to build these custom reports, joining multiple database objects (tables, views, stored procedures) if necessary, into dynamic data to populate different visualizations.

The closest system to equate it to would be Salesforce Reporting.  In there, you can wire up multiple related objects into a grouped, filtered, and sorted data source.  In this assembly challenge, we need a recommendation, given the environment, as to how we can accomplish this level of configurability and surface it in a way that the end-user can build meaningful reports.

Key Requirements

The wireframe of the app has been provided as the input to the challenge. This wireframe surfaced the issue to the team, because there is no way to do “joins” on data to get combined data across database objects.

If you look at the attached wireframe, when you go to the Custom Report section, you are given a data source to select.  Right now, it’s populated with two example objects, but when the product goes live, that could be as many as 8 or 10 (mostly views but some tables and stored procedures as well).  

We are aware that the wireframe and design cannot support the dynamic nature we’re looking for, so suggestions to update the design would be helpful.  We are planning on having the designer update the design to provide that input for the Prototype.

Competition Task Overview

For this challenge, you are expected to break down the requirements, define the detailed architecture for the whole system, by clearly answering the following questions, but not limited to:

• How would the database be configured to show which object combinations are able to be used in the report.

• What is the ERD for the system to capture and store the configuration of the reports so that it can be edited by the user?

• How is the stored custom report then executed to provide the data to the charts?

Other than answers to those questions, please provide:

• An ERD to store and provide data and configuration for the design

• Please recommend a charting library to display charts as close as possible to the provided design.

Please note that the deliverables produced by this architecture will be used directly into the development phase (assembly, prototype, code challenges), please make sure the details are enough for the following challenges.

Environment Requirements

Platform Requirements

• .NET Framework 4.5

• Visual Studio 2012

• SQL Server 2012

• ADO.NET

• Enterprise Library 6

• You may use NuGet if you are installing packages

• DO NOT Use Entity Framework

Coding Standards

• .NET - https://msdn.microsoft.com/en-us/library/ms229042(v=vs.110).aspx

• SQL Server (for reference) http://blog.sqlauthority.com/2007/06/04/sql-server-database-coding-standards-and-guidelines-part-1/

• http://blog.sqlauthority.com/2007/06/05/sql-server-database-coding-standards-and-guidelines-part-2/

• Readable comments should be placed across the code blocks in both SQL and .NET Web application

Third Party Libraries

3rd party libraries can be leveraged in this challenge as long as they are free for enterprise use (no licensing fees), have no restrictions on distribution, selling and / or profiting from the use or sale of software including the library and do not require the source code of the software including the library to be made available to recipients of the software in binary or compiled form. Requirements to credit the library in the source code that uses it are fine (just be sure to follow any such requirements in your submission).

Challenge Inputs

The following challenge inputs will be provided in the challenge forum:

• Tables of Sample Data

• Wireframes

• Design Challenge Screens

Final Submission Guidelines

Submission Deliverables

• Application Design Specification (ADS) written in an MS Word compatible document.

• Class and sequence diagrams authored using the TopCoder UML tool.

• Detailed Downstream competitions specifications (Assemblies, Code challenges, etc)

• Consolidated Game Plan, which gives an agile approach to carry out the development.

• Data Model for backend data management (such as an Entity Relationship Diagram).