Posts Tagged ‘SQL Server 2012’

Smith MBA Long Term Schedule Planning with PowerPivot

07/23/2012 1 comment

In this post I am trying to solve a real world analytical problem using Microsoft PowerPivot. Before I get started, let me introduce a few terms here:

Long Term Plan: A customized package of R.H.Smith business school courses spread across five campuses offered in various combinations by area of expertise under specific departments.

Students at the Smith School of Business use the Long Term Schedule Planner to plan their long term schedule based on the courses that will be offered, the number of credits, the campus location, term, etc. and then select and register for those courses. And I am sure that there are many students out there who return to this page often to to see what other options are available and if there were any changes to the long term schedule.

The Problem

The current Long Term Schedule Planning page delivers its purpose only to an extent; it drives the user\student to pick courses based on a specific option set i.e. Term –> Campus –> Department. Only then can you look at the courses and proceed further. Personally I am not a big fan of analytical processes forcing me to think in one direction. It should be, as I see it, a flexible application where students can use any ‘perspective’ to look at their schedule, have a way of planning and saving their schedule, have the ability to drill down and apply filters by department or for that matter just a course and see which semesters is it offered and plan accordingly.

‘a’ Solution: Microsoft PowerPivot

So, as I was trying to build my customized long term plan and got tired of going back and forgetting where I started; I got an idea to get all the data required for long term planning into a Microsoft SQL Server 2012 database and create a PowerPivot model. For me not only was this was the simplest and the easiest way of analyzing data but also was a powerful way of visualizing the data. The solution was implemented in the cloud. I am making a copy of the Excel workbook with the data and making it available for download.

For those who are new to PowerPivot, “PowerPivot is a powerful data mashup and data exploration tool based on xVelocity in-memory technologies providing unmatched analytical performance to process billions of rows at the speed of thought” [MS PowerPivot]. It is available for free. Works as an add-in in MS Excel.

Software Requirements

To use the workbook the software required is:

1. Microsoft Excel 2010 (have not tried it with 07)

2. Microsoft PowerPivot (Free Download)

Screenshots of the developed PowerPivot worksheet
SmithLongTermPlanner     SmithLongTermPlanner
How to get the PowerPivot workbook ?

Download it here. (Make sure that the above mentioned software is installed before using this workbook).

Note: This is the first version of the workbook and may need some fine tuning. Please let me know how you want to improve this and I will make it happen. Any feedback is appreciated.

Concluding Comments

This was a very simple example of a “self service” BI (Business Intelligence) application. I would highly recommend trying out PowerPivot for your advanced analysis tasks at work. You will not be disappointed with this small but effective BI move. As a matter of fact, Microsoft BI has already made its way into many companies as the leading BI player in the industry. [Gartner Report]

If you are interested in learning more about Microsoft BI products or have a BI effort that you would like to solve or for that matter have any potential BI solutions that you think will give your company the strategic edge and competitive advantage, please do not hesitate to contact me.

About AIS

AIS is a privately held, technical services company headquartered in Reston, Virginia. Over the past 30 years, AIS has provided custom solutions to government agencies and businesses worldwide. We’ve built a reputation for delivering completed projects to our clients that are on time, within budget and exceed expectations. Today, AIS provides long-term career opportunities to more than 300 talented individuals and open additional offices in Maryland, North Carolina, Ohio, Texas and India.

Thanks for reading,

Vishal Gamji