Dave's Adventures in Business Intelligence

Last week I signed up with an account at ondemand.com, a “software-as-a-service” offering from SAP BusinessObjects. I wanted to play around with Explorer for a bit, and we don’t yet have that product installed in house. It seemed like a good time to take advantage of their free demo account. I have no idea what is required to set up Explorer, or what sort of data issues I might encounter along the way. I am going into this experiment completely devoid of any knowledge of the product other than what I have seen in demonstrations. One of the selling points of the tool is that there are no training classes for this product; it’s supposed to be just that intuitive. It will be interesting to see how I do.

I first looked all over the site to find out what the restrictions are. Since I was going to be using a free trial account, I didn’t expect to be able to upload my entire two billion row fact table, nor would I want to. 🙂 However, I did want to stretch the system to show of what Explorer can do. I could not find anything, anywhere, that detailed the specific limits of the free account. I even tried to find out what pricing would be in the event that I decided to host more data, and I could not find that anywhere either. If anyone finds that information online, please post a link.

Other than these site issues, how did my initial exploration go?

Upload Limits

I created an extract with two years of data from one of my fact tables. I denormalized (flattened) the data to get it into a single CSV file, which I then attempted to upload to the OnDemand service. After the upload completed I found out what I was looking for before… it seems there is a 3MB limit on upload file sizes. So at least I was able to cross that question off of my list. It took me a while to decide what to drop out of my extract to get down to that limit, but finally I got a 2.9MB file that I was able to upload and save.

Of course the first thing I tried to do after that was open Explorer. The site gave me an error, so I logged out and logged back in. Still got the error. At this point I had to move on to something else so I tabled my efforts and came back the next day. That time Explorer launched just fine.

Initial Exploration

The data set I uploaded included one measure, two time facets (Year and Period), two geographical facets (Region and Area), two product facets (Brand and Line), and three location facets (Category, Channel, and Segment). Explorer uses the term “Facet” to describe a collection of values. I uploaded approximately 30,000 rows of data.

As I mentioned above, one of the selling points for Explorer is that there are no training classes for the product. It’s supposed to be point-click-insight easy. 🙂 I failed the test. The first thing I wanted to do was compare current year to prior year results. I figured out how to make my graph show a trend from last year to current year, but only if I included the year on the graph. What I wanted to be able to do was show period or region or product comparison of sales from last year to this year. With the data I uploaded I was not able to do that. I am going to go back and separate out my files by year, so I have 2008 and 2009 data as separate uploads. My thinking is that by doing two uploads I will have two measures, and that way can compare the two years. I will report back on that after I get a chance to try that experiment.

Other Frustrations

One of the data points I uploaded was Period. We have 13 periods in our fiscal calendar. Explorer tried showing me the “top ten” periods sorted by my measure, and then grouped the remaining periods as “Other” with an aggregate total. This was not what I wanted. I figured out how to change the sort to chronological order, and I also figured out how to get Explorer to show me 13 instead of 10 values + Other. But as I continued to explore my data it went back to showing only ten periods. I could not figure out how to make my change “sticky” so that it would retain my settings.

Along the way I clicked the “Create Visualization” button a couple of times. Later on when I tried to remove my data set, I was presented with an error message that said my data set could not be deleted. I figured out that my data set was protected because of the visualizations that I had generated, so I switched to the proper tab and removed all of my visualizations. Problem solved? Not quite. I had to find the “filters” that were used to drive the visualizations, and remove them too. I could not figure out a way to remove them both at once, so I had to remove them one at a time. Again, there may be a way to clean things up easier but I didn’t find it right away.

Ultimately I was able to remove my data set. It would have been nice to be presented with a list of object dependencies and asked if I wanted to delete my data set and cascade the delete to all dependent objects. Instead I had to clean up manually.

Exploration Results

After my initial attempt I’m not ready to rush out and do a demonstration for any executives just yet. I need to learn more about the proper way to model data for Explorer (assuming that there is such a thing). I need to understand more about how I can configure / control certain aspects of the interface, such as what I outlined for Period above. I may have to even <gasp> try to find some documentation in order to find out how to implement some of the analysis I want to make.

Stay tuned for more details.