BI Solution for Project Management Analysis with Customizable Reports

Summary

As an IT services provider, INNERLUXES needed to carefully monitor the effort and resources spent on projects, including logged and paid time tracking. Recording project information accurately and preparing timely monthly reports was forcing project managers and coordinators to spend considerable time, so INNERLUXES built an analytical system for project management — a fully integrated data warehouse and reporting solution that saves coordinators time and accelerates decision-making.

About the Company

INNERLUXES is an international IT company and an expert in the design, development, and delivery of custom software solutions, IT consulting, and IT outsourcing services.

The Challenge

As an IT services provider, the company needed to carefully monitor all the effort and resources spent on projects, including logged and paid time tracking. To record project information accurately and ensure the timely preparation of monthly reports, project managers and coordinators were forced to spend a considerable amount of time. To facilitate these tracking and reporting processes, the company needed to create an analytical system for project management.

The Solution

INNERLUXES's team developed a Data Warehouse (DWH) database structure and integration packages to load data from a variety of project information sources: a CRM database, Excel workbooks, Active Directory (access control), a task-tracking system (Jira 3 and Jira 4), a SharePoint portal, and an accounting system. To facilitate the project-management process, the system was designed to let project coordinators load data outside their workplace.

To support data-driven decision-making, a variety of reports, metrics, KPIs, and attributes were implemented. A number of tools and techniques are used for building reports and analyzing pooled data:

• Reports built with Microsoft Reporting Services that extract data from the analytical cube and OLTP system.
• PowerPivot models in Excel that don't require an external data connection.
• Online reports integrated into the SharePoint portal.
• A Power View interactive mechanism integrated with the SharePoint portal.

All reports are highly customizable, with plenty of rules that adjust reports to exact user needs. As project-monitoring information tends to change and expand, the team created a variety of customizable tables that record every detail and person around a particular project or customer. Data-security rules were implemented for all reports — information about a particular project can be viewed by a project manager or coordinator with the specific privileges.

The Results

• INNERLUXES created a fully integrated system for project management, analysis, and reporting that saves project coordinators time on their regular responsibilities.
• The system records all information related to the company's projects, accelerating decision-making and facilitating the whole project-management process.
• It merges data from different internal sources into a single storage and builds reports according to specified parameters, with per-customer report customization that increases customer satisfaction.
• The solution was successfully implemented and is now actively used.

Technologies and Tools

Microsoft SQL Server 2008 R2, Microsoft SQL Server 2012, Microsoft Integration Services 2012, Microsoft Analysis Services 2012 Tabular Model, Microsoft Reporting Services, Microsoft SharePoint, Microsoft Excel, Microsoft PowerPivot, Power View, Silverlight, T-SQL, DAX.