We are looking at some BI Platform, but probably won't have anything usable for the purpose until late 2008. So I hope to get some tips on using some more common software or websites for the immediate need.

Basically, I need to find a way for my team to effectively and efficiently create and analyze monthly progress reports for their products.

In addition to the data they have direct access to, each product manager will have to collect these data from marketing managers of 6-8 brands that sell their products. Data include quantitative like sales, profits, year-over-year growth; as well as qualitative like what promotion campaigns were carried out.

On top of that, once we have all these info for each product, I will have to compile/slice/dice the data to prepare for the director/VP level meeting with each of the brand.

We have about 20 key products and 13 brands. So it can be a lot of work. Ideally, I'd like each product manager & brand manager to enter their data into a web (internet or intranet) tool, and then have access to only the data that are relevant to them. (Some brands are direct competitors.)

As we are investing in a major BI tool, we don't want to spend money on a temporary solution. I am thinking about looking into what Survey Monkey and Microsoft Sharepoint can do.

Any suggestions?

jwyckoff's picture


Seriously, Excel is powerful, and can do a lot more than you might think.

If you issue is with the gathering of data (vs the reporting and analysis), Excel begins to lose some steam. If you have multiple folks capturing data for your, use Access as your database, and use either Excel or Access for your reports/analysis. The next step of sophistication/complexity after Access is to include InfoPath/SharePoint into the mix.

gilz's picture

When I collect metrics I use Excel, either manually, or exporting data from other systems into it. This for analyzing multiple projects data. And Excel has the magnificent pivot table and charts, so the slicing and dicing is kept to minimum.

With a little scripting (within Excel), you can pull all the data from different files into a compiled one.

I know that "little scripting" may sound frightening, but usually the investment is worth the time, and returns itself pretty quickly, especially for recurring tasks like monthly reports. Just need someone with a little software experience. Maybe your IT group can help.

While Sharepoint is a good basis for development of these kind of things, the development (and maintenance) cost is much greater. However, if you want to cross-section the data over time, than I agree that you should go for some kind of a database (Access, Foxpro, etc.)

hchan's picture

Thank you both.

Connick -
My issue is with gathering data from multiple people. So the suggestion of using Access is great, especially as I will be able to import it into Excel and pivot to my heart's content.

I don't know much about setting up Access database, so I'll ask Professor Google today.

We actually have Sharepoint, but unfortunately, trying to get help from IT means a lot of Red Tape and can be a long wait. So I'm not going that route unless I absolutely have to. (I wish they would listen to the cast about Jumpstart the Relationship with Your Internal Customer!)

By "little scripting", do you mean VBA? I have never used it but I self-taught SQL enough to pull reports I need (easier than going to IT). So I can probably figure it out. Will give it a try as well.

Thanks for the ideas, please keep them coming. Also, if you have good resources (e.g. Good Microsoft MVP sites), let me know.

gilz's picture


VBA is correct.

The great thing about Excel, is that you can record a macro (i.e. ,record your actions) and it generates the script for you and saves it for repeating. Then, if you need to, you can modify it.

Actually, Excel has one more way to script, its own formula language, but I found it too cumbersome. If you taught yourself SQL, modifying excel scripts should be easy.

Good luck, and let me know if you need any help.

bflynn's picture

I'll mention a technique that I've seen in the past - use a portable wiki that sends a RSS stream to collect data in a central location. The wiki is consolidated in a single html file, so there is only one piece to distribute. The idea is that you can have a single page containing the report, edited by the direct and it rolls up at each higher level as the RSS feed runs. Development should be short, maybe two weeks tops.

I have no direct experience at using it, but when someone showed it to me, I found the technique very compelling for status reports. Look up something called TiddlyWiki for an example. I'm afraid I can't suggest much more.


jhack's picture

Whatever you do, make sure it's going to be aligned with that BI project coming down the pike. If it's MS Performance Point, use MS technologies in the short run. If it's a proprietary OLAP tool, try to organize your data multidimensionally so that conversion is easier.

Gathering the data (including reconciling/verifying it, and storing it in a way that makes analysis easy) is indeed the challenge. What you don't spend on software will be spent on people's time. The front end should probably be Excel: everyone knows how to use it, and it's a powerful tool that includes the ability to slice and dice.

One technique that might help you focus your efforts: create a core set of sample reports that look like the ones you need. Make sure anything you come up with can create those reports.

(Full disclosure: I develop performance management software for one of the major vendors, not the one HQ'd in Redmond) (Another side note: 18 months for a BI project!? hmmmm.....)


hchan's picture

Thank you for all your advice.

I am taking this all in and will do a bit more research before talking to my boss about the best route. I am not a developer or database professional, and I am seeing signs that this project may be bigger than I first thought. I was kind of looking for "Solutions for Dummies" sort of things. (Yeah, I am a total dummy, esp. in the midst of professionals on this board. ;-) )

Anyway, jhack, the long lead time is not the software company's fault at all. It's just that we have a lot of business units in our company, and everyone has been waiting for the upgrade. And with all the enhancement/development that we have on the list, it can be a while before my "idea" dashboard will happen.

bflynn - Thanks for the advice. Your concept is interesting... I'll bring that up when discussing the options with my boss as well.

Thanks again. This is a great community.