BACKGROUND

Professional services firms continuously face a challenge to: Understand and manage many simultaneous client initiatives

Require personnel from more than one team of professionals within the firm

Coordinate multiple outside resources

Meet the needs of clients and deliver successful results

The capability to understand “current” initiatives is a very important but it is equally important to understand the “history” of initiatives the firm has undertaken. This allows the firm to more accurately propose and manage new initiatives that are presented.

Recently we worked with a firm facing this challenge and started them on a path to improved management of their client initiatives. This improvement provides the firm a better understanding and insight into progress and performance during the course of a client initiative. In addition, the firm can more accurately and completely communicate with the client about an initiative.

For our client, like many organizations, there was more than one location in which data was stored. For our client the first was an ERP system. This system had detailed revenue and expense data over time for Projects, Clients, Staff and outside Resources. Forecasts, Actuals and Variances were all contained in this system. This information was very confidential and had limited distribution within the firm.

At the same time a “Marketing” system had been built over time in Excel with complex and extensive information about client initiatives. This included very granular data about specific characteristics of an initiative. Generally, this was of a non- financial nature and was much more detailed than similar data contained in the ERP system. It also included initiative opportunities that the firm might not have been selected to undertake.

The combination of the data in the ERP and the Marketing systems provided the firm management with insight into the progress of initiatives with the addition of very detailed characteristics about the project.

Not surprisingly, aggregating and homogenizing the data in the ERP system with the data in the Excel environment was cumbersome, time consuming and prone to error. In addition, the granular data about an initiative that enhanced the value of the Excel data was gathered from a variety of sources over time. There was limited structure to it and it had an almost free form kind of feel to it.All of this made for a less than ideal set of tools with which the firm could manage current initiatives. Moreover, evaluating and responding to a new opportunity was very difficult to do with the free form, ad hoc nature of the Excel platform.

SOLUTION

The Power BI tools offered a great platform to dramatically improve this situation. We shared with the firm a conceptual vision of combining data from the ERP system and the Excel environment. This vision recognized the confidential nature of the ERP system while at the same time offered access to the detail granularity of the Excel data homogenized with the ERP data.

The vision included the use of Q&A to very easily ask and answer ad hoc, spontaneous, questions about ongoing initiatives.

The solution was implemented in a phased approach to allow the firm to understand and internalize the impact that Power BI was providing. Here is a brief outline of the solution approach that was used:

First PhaseConnect to and extract appropriate ERP data into a Power BI data modelExtract all data from the Excel data store into the same Power BI data modelEnhance the data model with additional values to improve insight Create a structure in the data model that insured the confidentiality of dataProvide visuals that allow the firm to become comfortable with Power BI

Second PhaseExtend the use of Power BI by providing more insightful and additional visuals•Provide access via the Power BI Service to more people on more devices Provide a more structured and accessible repository for the current Excel dataStreamline the ERP reports with Power BI so that they take several minutes rather than several days to produce.

The firm now has more insight into an initiative which enables much better use of resources while an initiative is underway. At the same time this insight empowers the firm to communicate more effectively with their client.

Of more value to our client is the ability to use Q&A to ask questions of the data model. Doing this despite the free form nature of the Excel data without losing access to the specifics of the ERP data is very significant.

This Q&A capability also provides a hindsight look into initiatives that equip our client to more effectively and successfully approach new opportunities.

The use of Power BI has provided our client with a much improved environment in which to manage their current business and at the same time has opened a window on what is possible going forward.