A BI project doesn’t need to be complex. You can start off small.
Here is an example using a construction company with project managers and schedulers:
Imagine you’re a project manager (PM) and your boss comes to you asking why it takes so long for a lot of small projects to be reviewed, approved, and scheduled after they’re submitted to your team. You know you’ve been under a lot of pressure, wearing several hats in your job, putting out a lot of fires. And you kind of knew this was an issue, but you didn’t know what the real cause of the issue was.
How do you find the answer? Ultimately, you know you need to pull and clean a lot of data, analyze it, and then start fixing the parts business processes once the analyses point you in the right direction. This can be completely overwhelming, and potentially expensive if you looked at the situation as a whole. And if it were expensive, your boss might not give you the budget approvals to start anyway.
Instead, take it in small bites. Try to narrow the situation to a short statement of the problem, your theory or hypothesis of the cause. Like this:
Problem: Once we get a project, it takes us a long time to review, approve, and schedule it. We need to do it faster without negatively impacting the quality of our work.
Theory: I believe it is because Fred doesn’t understand how to review our road-building projects and takes too long, and that’s 70% of the work we do.
Then, get the data for that one specific situation and throw it into Excel PowerPivot (no need to get expensive software tools, keep it lean and affordable).
Pull the related data from your project management software and export it to an Excel file. Include fields that define the date it was received, reviewed, approved, and scheduled. Also pull data on who received it, who reviewed, who approved, who scheduled. Finally, pull the fields that define the various types of projects, sizes of projects and other useful attributes.
Use Microsoft Power Query to clean up the data as needed (dirty data doesn’t give clean results). Power Query has a lot of capabilities.
Sample this post to understand one of many ways to clean and transform your data
Once cleansed, then create some new columns to give you more data to enrich the analysis.
Add columns which calculate the number of hours or days between the various events of receiving, reviewing, approving, and scheduling projects.
Then create a few simple pivot tables using Power Pivot to understand the data.
Create a simple pivot table of the average time from receiving and reviewing by project type. Then a similar table by person doing the reviewing and by project type. Maybe another table by another attribute of the project like location of the project or size of the project. Where is most of the time spent for reviewing projects? Anything look odd there? Are small projects taking longer to review than larger projects? Is Fred taking longer than everyone else on the road-building projects? You can also easily insert a simple chart to if seeing bars help you more easily identify unusual situations in the data. If you can narrow in on definitive causes you can now start coming up with solutions within the business.
Once you have discovered the root cause(s) of the issue, you can come up with a solution, and implement it. Then, it’s a good idea to keep an eye on the situation over time. Did the solution work? Or not?
Maybe once a month follow the same process as before to prepare the data, and run the pivot tables and charts. How is Fred doing now on the projects he’s more comfortable with? Is his mean time to review a project faster? Are the road projects which were given to someone else being reviewed faster? Then start looking for the next low-hanging-fruit problem to solve.
Up to this point, you don’t even need to use Power Pivot if you don’t want to. You could use Excel and pivot tables. Though you will eventually be limited to the extent you can analyze and visualize the data.
If you haven’t used Power BI up to this point but want to continue using it to analyze the data, here are some examples of how you could use it:
- Use Power Query to set up data sources to pull from your database or Excel files that are used to track projects. Then you can pull and cleanse the data at the click of a button.
- Create a dates/calendar table and add it to the Power Pivot data model to make it easier to analyze data with various types of dates and date calculations.
- Add data from tables of a SQL Server database (assuming that’s where the software’s data lives) to the Power Pivot data model to provide more useful data for the analysis.
- Read and learn more about the capabilities of these tools. They are incredibly powerful. A great place to start is the blog and training courses (online and in-person) of our good friends at PowerPivotPro.
Ectobox is helping a company through this process right now. They have a department that is really busy and constantly changing how it does its work as a result of rapid growth within the company. This department is finding out how difficult growth can be. Their old business processes are causing bottlenecks, but they’re not exactly sure why or where those bottlenecks exist.
We’re starting off simple by using Excel PowerPivot, pulling data from a single SQL Server database, creating a small data model for only the tables needed for this specific situation, creating only a few charts, giving the whole Excel PowerPivot file, and connecting it to the SQL Server database. So, we’re building the fishing net, so to speak, and then we’ll teach them how to fish.
If this process takes hold and our client finds value in analyzing this data, they may start to realize the power and value of the data they have. This could lead eventually to the development of a truly data-driven culture, giving them unlimited opportunities for increasing profitability.