Breaking down information silos – Part 2

May 26, 2009

This was previously posted on our blogspot and has been migrated to this blog.  Enjoy! – The Vorsite Team

————————————————————————————

Let’s continue…

Forgot to mention I took over mid-project. By then, the customer has agreed to leave direct data integration out of the project scope and adopt a POC approach to see if we could surface the data. That certainly made life easier.

As we dived deeper and found out more about how the business users created and consumed the reports, we discovered that another BI Project was running in parallel. So, the question is: “Why Vorsite?” BI is not our core competency and we have been upfront to the customers. However, we are experts in hooking MOSS up to Enterprise Content Management systems so we understand how important it is for end users to go to a single point of entry to access the information they need to do their jobs. After all, the biggest pain point (for the existing desktop tool) was the multiple source/location/copy of the “same” reports.

So, we really need to deliver a solution which will be perceived as value-add as well as make sure that it is scalable and extensible.

The first Excel report contained the group’s overall financial report. PerformancePoint Monitoring Server came into the picture. We built a couple of scorecards that provided at-a-glance insights into non-performers. These scorecards were based on flat data which has been imported into a SQL database and a cube. This addressed the need of top leadership so that they could take actions. Depending on needs, we planned to add analytic reports/graphs to present the data in different views in future phase(s).
 
For the Business Unit Managers, they needed details and made profit center accountable.

In order for profit center managers to follow the money trail, we needed to integrate the second set of Excel reports which were pulling data directly from a cube owned by a different group. We decided to use Excel Services and create an Excel Services report in the Dashboard Designer. There was no reason to rebuild something which worked plus we did not have write access to the cube to manipulate the hierarchy of the dimensions and produce an analytic report with all the columns our customers wanted to see.

Finally, we created a page in MOSS. When I saw the PerformancePoint Dashboard item Web Part, I honestly thought that was the coolest thing ever existed. A fellow colleague who is a developer will probably yell at me for simplifying the complexity in pulling everything together but it really did seem easy: just add and drop the filter, scorecards and reports you need! (That’s definitely the PM talking.)

Thanks to the hard work of a dedicated team, the above was successfully pushed out to production. We generated so much excitement in the super user group that we were approached to look into how we could convert an Excel forecast management tool into a web-based tool and integrate that into the new platform!

Previouly Posted by Pui Chee Chan

Breaking down information silos – Part 1

May 26, 2009
This was previously posted on our blogspot and has been migrated to this blog.  Enjoy! – The Vorsite Team
 
——————————————————————-
 
What do you do when the customer gives you a couple of Excel Spreadsheets and you are asked to “expose the data” in SharePoint using PerformancePoint? Make things happen!

Previously Posted by Pui Chee Chan

As a Program Manager at Vorsite Corp., I was recently asked to run this project. I pretty much walked away from the initial internal project briefing wondering how to translate “make it pretty”; “wow me!” etc. into user requirements. But, it’s my job to scope the requirements and see structure in “chaos” – should be a walk in the park, right?

Here are the facts:
– Two Excel Spreadsheets containing data pulled from different data source. Several hidden worksheets with columns performing vlookup/complex if-else statements to derive values for use in multiple pivot tables representing the data in different views

So, what’s the problem with using the Excel spreadsheets?

I like to approach problem using a scenario. So, picture this:

You are a new hire in the Marketing group. As a Business Unit Manager, You are responsible to track budget spend and keep management informed of how you spend the money. Because of the nature of the business, you have to collaborate with several other business units/external vendors to run a full marketing campaign. As a result, you distribute your budget among several individuals. So, you have a group of people who commit to spending X amount of your money but you have no visibility to the actual amount until the invoices go through the financial systems. You quickly realize that you need to look at multiple systems to follow the money trail. The monthly meeting is tomorrow – where do you look? Jim, your analyst, said the latest Excel reports are stored in the shared network drive – access is open to all – can you trust the data? Where do you start? Over 10 campaigns and it’s already 2 in the morning. Do you even need to be concerned at all? Or maybe you can simply pray that forecast always equal actual in real life.

Don’t take me wrong. I love Excel. And I marvel at the complexity of these two reports. I just think that it should be easier when you need more information. Plus, nothing prevents me from tweaking the figures to my advantage. If everybody brings a different copy of the report to the meeting, how can decision makers gain true insights into business performance?

If I got you hooked so far – stay tuned for more…


%d bloggers like this: