May 10 2010
Exploring with OnDemand.com Part II
A few days ago I posted about my initial experiences with OnDemand.com, the software-as-a-service offering from SAP BusinessObjects. One of my frustrations was I was not able to compare year-to-year performance with the data set that I uploaded. I made the assumption that I could upload two data sets (one from each year) and merge them to obtain the desired result. The answer, I am happy to say, is yes. I made a few stupid mistakes along the way, but the end result is much closer to what I was trying to accomplish with my first attempt. Not only that, but I found out that I can merge data in two different directions.
Merge – Join
As I mentioned, the first thing I wanted to do after my initial exploration of my data was compare prior year to current year results. I could not do that (or figure out a way to do that) because of the way I uploaded my data. I had sent a data set that looked like this:
| Year | Period | Quantity |
|---|---|---|
| 2008 | 1 | 100 |
| 2009 | 1 | 125 |
In Web Intelligence I would have created a crosstab report with the year on the top and the period down the left side and the cases (my measure) in the middle. Explorer does not seem to work that way. So what I did was split my file into two parts. The first part contained 2008 data and the second part contained my 2009 data. Instead of one file I had two files like this:
2008 file
| Period | Quantity |
|---|---|
| 1 | 100 |
2009 file
| Period | Quantity |
|---|---|
| 1 | 125 |
Once I uploaded the two files I started figuring out how to merge the data.
The menu option is quite easy to find. You can also right-click on a data set and select “Combine” from the context menu that pops up. However, there are some restrictions that are not so obvious. Once I selected my two sets I was presented with a screen showing the merged data. It turns out that the OnDemand site merges data sets by finding matching column names. Since both of my data sets came from the same source, that wasn’t a problem. I did, however, have to rename my measure column from “Quantity” to “2008 Quantity” and “2009 Quantity” in order to be able to tell the difference.
How did I know this was how my sets were going to be merged? There was a “more” link on the page. When I clicked it I got this information.
Any changes you make to the two source datasets are not saved back to those datasets; the changes are only propagated into the combined dataset.
The join occurs on columns that have the same name. The maximum size for a created dataset is 20,000 rows and 256 columns.
When you have finished joining the data, press “Next” to continue to the next step of the wizard where you can finalize how the new dataset will appear.
The changes mentioned in the first point related to the fact that I can change column names during the merge process. My source data sets are not changed, only the merged file contains the changes. I’m fine with that. The second point told me how the join was going to be performed, and also let me know that my combined set was going to be limited to 20,000 rows. So as I mentioned last time, there are limits to the free service but they do not appear to be documented anywhere so I could plan ahead. It turns out that my initial uploads were also silently truncated to 20,000 rows as well.
I went ahead with the merge. I was curious if they would tell me how many rows were being truncated, but they did not. After the merge was complete I had a data set with all of my facets (dimensions) and two measures. I was easily able to create a Variance variable on my data set by subtracting 2008 from 2009. Ultimately I was able to create a visualization showing the trend over periods including both 2008 and 2009 data, along with my variance. Here is what the merged data set looked like:
| Period | 2008 Qty | 2009 Qty |
|---|---|---|
| 1 | 100 | 125 |
Merge – Union
Now that I knew how to get 2008 + 2009 data together I wanted to see if I could combine two business units. In this case I would not want new columns added as I did in the prior example, I wanted to see more rows. It turned out to be quite simple. I already had two years of data for one business unit uploaded and merged. I followed the same process for the second. Now I was ready to create a union between the two.
All I had to do was select the same “combine” option and this time make sure that all of the column names were the same, even my quantities (measures). Explorer appeared to merge the sets using a union operation, which was exactly what I wanted.
| Bus Unit | Period | 2008 Qty | 2009 Qty |
|---|---|---|---|
| A | 1 | 100 | 125 |
| B | 1 | 95 | 142 |
I guess now is a good time to point out that some of the features I am talking about are features of the OnDemand.com service, rather than the Explorer product itself. You or your company can opt to use the service online (via the web site) or you can license the entire system (Explorer plus the OnDemand portal interface) for internal use.
Need “Sticky” Settings
I am now able to generate year-over-year comparison charts from multiple different business units (data sources). However, there are still some (not so) sticky points to deal with.
The data I uploaded included the fiscal calendar period number. We use 13 fiscal periods so the numbers range (obviously) from 1 to 13. When I picked the period facet as part of my analysis, Explorer showed me the top 10 + a category for “other” instead of showing all 13 periods. I was eventually able to figure out that I could configure the system to show all 13 periods. I was also able to get them to sort chronologically rather than from “best” to “worst” order by quantity. It wasn’t hard.
It also wasn’t sticky.
What do I mean by that? As I played around with (explored) my data I swapped things in and out. When I removed period and then later brought it back it had forgotten all of the settings I had changed. I was back to 10 periods + other instead. I can see where this would be okay for a lot of things, but it would be nice to be able to make changes to a specific item and have them always come up that way by default.
Story So Far…
Normally I would call the final section of my post “Conclusion” but I am far from that point. While I have been a bit frustrated by the limits of the trial (free) service I can certainly see the potential of the OnDemand framework. Explorer is the core of the system, of course, but they have added a front-end interface that is extremely easy to work with. We have been trying to install Explorer internally to prototype some solutions, but have encountered a number of isssues with the installation. By using OnDemand.com I was able to – in a matter of a few days – upload some data and create some Explorer data sets and then build some visualizations for our review.
I wish they published the limitations for the trial membership somewhere. I definitely would have reduced the complexity of my data in order to avoid getting cut off at 20,000 rows. Fortunately the OnDemand site offers a Feedback link that takes you to a new site where you can make suggestions on how to improve the service as well as vote on existing ideas. I have entered this as a suggestion. I also entered the idea of making changes “sticky” as mentioned above.
I still want to get Explorer up and running internally. But OnDemand.com was certainly easy enough to work with, and it allowed me to get my demonstration up and running well within my time constraints.
It is my understanding that Explorer is meant for Executive (and other non-power user) search and see-type reporting. Having to merge datasets would appear to be, if not above, at least not anywhere near, that paygrade. I would think that if these comparisons were going to be required, you would build separate objects in your Universe and pull those separate objects into the dataset. Has anyone formalized best practices for this yet?
Jamie, my initial impressions of Explorer were similar. I think that’s how it was marketed in the beginning. However, I am seeing more and more folks that say they use it as a preview tool. It lets business users (at any level) interact with their data perhaps even before a universe and formal reports are built. The ability to upload and merge data provides a way for IT to know and understand more about what users are doing with their data.
Think about how many folks come to us to ask for reports that are basically extracts. Then they take that data off and load it into Excel or Access and play with it, and IT has no idea what’s going on. That is until “Joe” or “Jane” analyst gets ready to retire and all of a sudden it becomes a business-critical project to recreate their home-grown Access-based solution because that’s how the business is being run. 😉
By allowing users to upload, merge, fold, spindle, and mutilate the data all in one place (and do it quickly and easily) IT folks can get more insight into what folks area actually doing with the data. That, to me, is a far more compelling use of Explorer and OnDemand than allowing executives to play with the data.
Can you please let me know how you managed to upload 2 files into 1 dataset to merge the files? Have tried your suggestion to right clik, but do not get any options to combine or load 2 files.
I uploaded both files first. Then I clicked on “Datasets” from the left menu, and right-clicked on the data set I wanted to start with.
From there I was able to walk through the process.
Dave, I understand the capabilities of the tool, but do you envision it going to such lengths that many report requirements (ofcourse not all) would get scrapped from projects due to Explorer’s availability and (once configured properly) ease of use to answer many business logic questions that Report writer were helpinging the manger’s to decipher?
kay, I think the answer to that is both yes and no. I have a blog post that I am almost finished with that discusses a slide from SAP showing how to select which tool to use. Explorer is definitely not positioned a report delivery or distribution tool, although it can be the source of the insights that spark the creation of those types of reports. But here are a couple of possible scenarios for you.
Suppose that the business thinks that there is a problem with their manufacturing process. Rather than go through the time-intensive process of gathering requirements, creating technical documents, building, testing, and releasing a set of reports… why not build an Explorer data set instead and let the business play with the data? It might be that a problem is, in fact, discovered. But once it’s fixed, there is no need for any further work. In other words, once the issue is addressed we don’t need to continue to work the issue, let’s move on. Problems can be solved significantly faster and with much less investment.
On the other hand, suppose there is a problem that is identified via data exploration that cannot be permanently fixed by changing a process. In this case, we might take the structure and format (and content) of an Explorer “report” (in whatever format that might be) and use it to create a monitoring report that is ultimately scheduled and distributed.
But in each scenario the business drove the process by understanding and knowing their data, rather than just “guessing” as to what they might want or need.
I have worked on more than one project where folks asked me to “just give me everything, and I will find what I need.” That doesn’t work very well with Web Intelligence, and certainly not Crystal. But Explorer seems to fit that role very well.
Hello Dave,
As per my understanding OnDemand.com is BI SAAS offering and replacement for Crystalreports.com with extended capabilities to accept all BOE content like WebI or CR or SWF files created on various BOE Suite products. But what you are discussing is BO Explorer that is an In-Memory BI Solution like QlikView. So why are you calling blog series as OnDemand.com ? Can you correct me.
Explorer is a component of OnDemand.com as of last year. That’s the tool that I’m using in my experimentation. I am using the title “Exploring with OnDemand.com” to reflect that. Make sense? 🙂
Dave, good analogy, you shed some light on the tool in a different “context” (no pun intended :)), I can see your point. Lets hope this tool makes things better for us, and no hidden pitfalls.
i have been using https://goexplore.ondemand.com which lacks many of features with BI OnDemand, particularly merging two (or more?) datasets. I’ve just started playing with BI OnDemand. What’s the difference?
BTW i’ve been using the SAP Explorer iPhone App for my dataset and it’s really nice. I wonder (can’t find online) if they have a Blackberry app as most of our execs have berrys.
From what I understand, there is no Blackberry app for Explorer yet. There is no Roambi Blackberry either… they don’t like the graphics. 🙂 There are also far more versions of BB than iPhone at this time, which makes it more difficult to support.
The problem that I see is that the OnDemand version and the onsite version do not work the same. I like some of the thing the OnDemand version does where you can upload data sets. If only the real version could do that.
Steve, you can license the complete web “wrapper” that is used for OnDemand.com and implement it internally.
Hi Dave,
I stumbled on your site just recently and I must say it’s been really helpful, thanks. The simplicity with which you explain is also excellent!
I have a question using the example you gave above:
Say for example you had in the 2008 file with a lot of rows with period 1 and you had a similar thing in the 2009 file with a lot of rows with period 1, how would you go about merging these?
I just used your example to ask the question but I actually had a similar issue which I abandoned few weeks ago when I didn’t know how to go about it.
I had two Universes with simiar fields.
Universe A had a field called ‘Book’ and a field called ‘Hours Spent’
Universe B also has field called Book and a field called ‘Hours Spent’
In unverse A, I have quite a lot of rows for a book called ‘ABC’ with different Hours Spent.
Also in univers B, I have a lot of rows for a book called ‘ABC’ with different hours Spent.
I am trying to create a report that shows the total Hours Spent per book.
I thought I should be able to pull the records for unverse A and B on to the same report and then try to merge the results together and all looked fine until I realised that there are more multiple rows with the same book name.
I also thought about using aggregate functions with my SQL knowledge but I didn’t know how to go about it as I am new to Business Objects.
What do you reckon I can do? Cheers