by Victor Fagundo
The argument over why Businesses should abandon Excel in favor of more structured tools has been raging for as long as I have had more than a casual exposure to Oracle products. From the standpoint of an IT user Excel appears to be a simplistic, flat-file-based, error-prone tool that careless people use, despite its obvious flaws. Petabytes of duplicative Excel spreadsheets clog network drives across the globe; we as IT users know it, and it drives us crazy. Why, oh why, can’t these analysts, project managers, and accountants not grasp the elegant beauty of a centralized database solution that ensures data integrity, security, and has the chops to handle gobs of data, and abandon their silly Excel sheets?
I’ll tell you why: Excel is better. Excel the most flexible and feature-rich tool for organizing and analyzing data. Ever. Period.
For the past few years I have lived in a hybrid Finance/IT role, and in coming from IT, I was shocked at how much Excel was used, for everything. But after working with Excel on a daily basis for several years, I am a convert. An adept Excel user can out-develop any tool ( BI, Apex, Hyperion, Crystal Reports ) handily. (when dataset size is not an issue). Microsoft has done too much work on Excel, made it too extendable, too intuitive, built in so much, that no structured tool like BI, APEX, SAP, Hyperion will EVER catch up to its usability/flexibility.
Take this real-world example that came across my desk a few months ago: for a retail chain define a by-week, by-unit sales target, and create a report that compares actual sales to this target. Oh, and the weekly sales targets get adjusted each quarter based on current financial outlook.
How quickly could you turn around a DW/BI solution to this problem? What would it involve?
• Create table to house targets
• Create ETL process to load new targets
• Define BMM/Presentation Layers to expose targets
• Develop / test / publish report.
A day? Maybe? If one person handled all steps (unlikely, since the DB layers and RPD layers are probably handled by different people.)
I can tell you how long it took me in Excel: 3 hours (OBIEE driven data-dump, married with target sheet supplied to me). I love OBIEE, but Excel was still miles faster/more efficient for this task. And I could regurgitate 6 other examples like this one off at a moment’s notice.
Case in point: 95% of the data that C-level executives use to make strategic decisions is Excel based.
If you’ve ever sat in on a presentation to a CEO or other C-level executive at any medium to large sized company, you know that people are not bringing up dashboards, or any other applications. They are presenting PowerPoints with a few (less than 7) carefully massaged facts on them. If you trace the source of these numbers back down the rabbit hole, your first stop is always Excel. Within these Excel workbooks you will find “guesses” and “plugs” that fill gaps in solid data, to arrive at an actionable bit of information. It’s these “guesses” and “plugs” that are very hard to code for in an environment like OBIEE (or any other application). Can it be done? Yes, of course, with gobs of time and money. And during the fitful and tense development, the creditably of the application is going to take major hits.
Given the above, the usefulness of OBIEE might seem bleak. But I strongly feel that applications such as OBIEE do have a proper place in the upper organizational layers of modern business: Facilitating the Tactical business layers, and providing data-dumps to the Strategic Business layers.
Since this post is mainly about Excel, I will focus on how OBIEE can support the analyses that are inevitably going to be done in Excel.
Data Formatting, Data Formatting, FORMATTING!! I can’t stress this enough. For an analyst, having to re-format numbers that come out of an export so that you can properly display them or drive calcs off them in Excel is infuriating, and wasteful. My favorite examples: in a BI environment I worked with percentages were exported as TEXT, so while they looked fine in the application, as soon as you exported them to Excel and built calcs off them, your answer was overstated by a factor of 100 (Excel understood “75%” to be the number 75 with a text character appended, not the number 0.75).
Ask your users how they would like to SEE a fact in Excel: with decimals or not? With commas or not? Ensure that when exported to Excel, facts and attributes function correctly.
“Pull” refreshes of information sources in Excel. In the finance world, most Excel workbooks are low to medium complexity financial models, based off a data-dump from a reporting system. When the user wants to refresh the model, they refresh the data-dump, and the Excel calculations do the rest. OBIEE currently forces a user to “push” a new data-dump by manually running/exporting from OBIEE and then pasting the data into the data-dump tab in the workbook. What an Excel user really desires is to have a data dump that can be refreshed automatedly, using values that exist on other parts of the workbook to define filters of data-dump. Then all the user needs to do is trigger a “pull” and everything else is automated. Currently OBIEE has no solution to this problem that is elegant enough for the common Excel user. (Smartview must have its filters defined explicitly in the Smartview UI each time an analysis is pulled.)
The important part to take away from these 2 suggestions, and this entire post, is that to maximize the audience of OBIEE, we must acknowledge that Excel is the preferred tool of the Finance department, due to its flexibility, and support friendly exports to Excel as a best practice. We must also understand that accounting for this flexibility in OBIEE is daunting, and probably not the best use of the tool. If your users are asking for a highly complex attribute or fact, that is fraught with exceptions and estimations, chances are they are going to be much happier if what you give them is reliable information in a data-dump form, and allow them to handle the exceptions and estimations in Excel.
