Create A Business Intelligence Suite

Many of the organisation I work for are heavily reliant on reports, or dashboards in order to run their businesses, but not all of them can afford to implement data warehouses or business intelligence systems such as Business Objects or Cognos. Some of these systems can run into the thousands and for a lot of businesses they are a bit overkill. As a custom software developer I jumped at the chance to develop a solution that provides reporting functionality and this will form a great example of why using a custom software development company can save you a great deal of money.

If you have read any of my other articles you will have seen that I have covered various aspects of C#, MVC and Entity Framework, but what I’m going to do now is take you through how to employ many of these techniques in order to create a business intelligence solution.

Business Intelligence Suite

It’s probably a rather grand title for what we are going to do, but then again it will be fully functional, vaguely good looking and will be usable and free. You might even learn something too – as might I!

Functionality will be as follows:

  • Ability for users to view reports by department
  • Ability for users to output reports to Excel
  • Ability for users to customise their own dashboard
  • Ability for people with knowledge of SQL to easily create new reports
  • Ability for administrators to know which reports are being used and by who
  • Ability for administrators to restrict access

I will also reserve the right to add anything else if it occurs to me.

C#, MVC & Entity Framework

We are going to use the above to create this application, plus a smattering of all the usual suspects like jQuery, jQueryUI and maybe some SignalR too (I really like SignalR). We are also going to do this with the “code first” Entity Framework method: code migrations alone make this the nest choice for our project.

I’m not going to describe every single step in enormous detail but will make the code available for download. I don’t yet have a GitHub account but will set one up and post the solution up there as we go along.

New MVC Project

Ok, let’s get started. First load up Visual Studio (I’m using Visual Studio 2013) and create a new MVC project. Change the authentication to Windows (Intranet). If you want to create a separate project for the model then please do so but I will not for the purposes of this series.

Go to Nuget Package manager for solution and install Entity Framework 6 and then jQuery. If we do add SignalR functionality we can add that package later (we would most likely use this to have a live dashboard).

Entity Framework Model

We will need the following POCO (Plain Old CLR Objects) model classes:

  • Department
  • Report
  • ConnectionString
  • Group
  • SQLParameter
  • User
  • AuditLog

This should all be fairly self explanatory but if you don’t know how to use data annotation then I would suggest you read up on those as they are pretty cool.

If you are typing this in as you go, or cut and pasting then you will notice that we do not yet have a class file for the connectionstring object yet. But if you right click it in Visual Studio it will give you the option to create one. If you do this please remember to make the class public as but default it will not be.

inconsistent accessibility error

Inconsistent accessibility error

I’m not going to list out all the model classes as they will be included in the solution but I will just do one more and that’s the SQLParameter class.

SQL Parameters

The model classes we have done so far have been pretty much self explanatory, and this one is too, really, but a brief explanation won’t go amiss.

Note: Not to be confused with the C# SqlParameter class

In order for our business intelligence solution to be truly useful our reports need to have some sort of select criteria (at least, in most cases). So this table is for specifying a parameter that we can then use in the SQL query. So for example we would have a parameter like this:

  • Name = @startDate
  • Label = Start Date
  • Type = DateTime

Then in our SQL query we would write:

In later parts of this tutorial you will also use these parameters to build the view.

More to follow….

Leave a Reply

Your email address will not be published. Required fields are marked *