Case Study: Reporting and Planning System BI, ASP MVC and MSBI Stack

Hi,

Is it Reporting…

Is it Planning…

Can you classify, what system is yours….

Well sorry for my sarcasm, but indeed its a question which at times is not very clear in the objectives of the business when they are mentioning their BI systems.

• Reporting: As system which collates data from multiple (or) single source and produces fancy reports, which can be further printed and utilized by business in their decision making is a pure Reporting system.
  
  These reports can be parameter driven and have multiple security combinations in effect.

  Only input in this system of the user is to make selections, of the parameters to run the report required for.

  

• Planning: The system which is first reporting as described above, and then the business ask you
  
  “Can I add comments…, Can I add constants to plan KPI’s…,Can we have the features to control the chart calculations…, Can we input targets… and so more” then I reckon your system in-house, is more a planning system than just reporting.

  

  And many-many organisation these days aren’t happy with just reporting, they are switching to the following two models of planning systems.

  • Top down planning: In this model the planning inputs are plugged into the system at higher level of the business usually CXO/Directors/Managers.
    
    Example: Targets for the team, Company defined constant parameters
  • Bottom up planning: In this model, the planing system is deployed organisation wide, and the regional teams plan on their own (with appropriate training, provide their inputs in the system) and the net planned result at an aggregated levels are visualized and reported at higher levels.
    
    Example: Regional team put their comments in the report, explaining their performance, departments planning future employee recruitment’s impacting allocated budgets
  • Hybrid planing: This is a mode in which I better illustrate with a scenario.
    
    Example: The senior members of the business can set targets for the regions (top-down), and the regions can provide comments on the outcomes (bottom-up)…
    
    The mix of above two…

And to add to above, the system compatibility with multiple devices is another challenge.

Is it an adaptive website, or its native mobile app…

So this post illustrates a similar project, I recently embarked upon which was a Hybrid planning model, where I utilized the following technology stack to deliver the following requirements:

Requirements:

• We would like a reporting system (obvious)
• The system should be able to integrate data from multiple sources, Finance, HR, Excel/CSV and other custom systems
• We need to get charts which have custom computed bands
• The data should be aviable via cubes to the advanced users for cross analysis
• The reporting system should allow users to input their comments in the reports
• The system should allow users to choose traffic light status for reports
• The system should allow users to edit the chart calculation meta paramters, and allow user to enter user targets

Technology:

• ASP MVC 5 C#
• Javascript and so many helpful libraries
• MSSQL 2014 BI Stack SSIS,SSAS (Multidimensional),SSRS

Now rather than too much talking, I will take you through a screen shot tour of my application which would raise your interest towards how I was able to bring the multiple facets of BI under one umbrella

Taadaa…

Some the notable features of the application in the above screen shot is as follows

• The URL driven mechanism of the application, report paramters are embedded in the URL
• The application has a dynamic menu system to provide user access to the multiple reports, cube access, integration system and application security
• The system has a breacrumb bar to keep the user navigation flow seamless
• Embedded SSRS in an ASP MVC application

Some of the featuers to illustrate in the above application is as follows

• The report which has been shown above has been run under the edit mode, look @ switch
• The editable fields for the user are underlined (dashed) and when the user clicks on the same has popups, which are movable and edit the value specific for that report run

• You get the editable dialog when you click upon the traffic light, which is movable in your browser just like windows desktop application (Calculator)

• You get the editable dialog when you click upon the comment area, which is movable in your browser just like windows desktop application (Calculator)

The movable dialog, in the above screen shots is derived from the following library jspanel.de (v2.6.2)

• In the above screen shot, under the chart I have placed a hyperlink to edit the chart comments/targets/ and calculation meta data

And the above dynamic menus bring many and many reports to be browsed in the application… which are Editable !!!

So in the above illustration’s, the combination of SSRS hosted out in ASP MVC application featuring the editable components is made possible by the Action feature of SSRS text boxes calling upon the global java script functions residing in the parent ASP MVC container.

The above sums up the area of editable reports (Planning system), but the other side of the system as you can see in the above screen shots also provide user with the pages which provide business with downloadable ODC to connect to to analysis server (via Excel) and pages allowing users to kick off SSIS packages on demand (in conjunction of features to upload Excel/CSV)

This post highlights the use case of the technological combination which i have leveraged to build a BI system, which addresses all the business needs and custom offerings which as of now cant be provided by any out-of-box features of any product in the market.

Hope it helps.