Monday, September 29, 2025

Happy 40th Birthday to You, Excel!

I thought about calling this post, “The Meaning of Life: Excel Edition”, as Excel has played such an important part of my life.

My lifelong journey with spreadsheets began in college. Back then, I assigned portions of assignments to different members of my group and asked them to give me their work in SuperCalc format. I then assembled and integrated everything into a single project. That was my first real glimpse of how powerful spreadsheets could be, a skill I expanded in my first job at Deloitte.

It was the late 1980s, and I quickly discovered that working with spreadsheets was more fun than doing normal accounting work. I drafted audit schedules and even learned to program using macros in Lotus 1-2-3. That curiosity led me to my first true spreadsheet job in Huntsville, Alabama, at Intergraph, where I handled international consolidations in SuperCalc 5. In that role, I both leveraged and expanded my spreadsheet skills. Within a year, I had automated all spreadsheet reporting and took on additional responsibilities for automating some reports for our corporate reporting department.

At Intergraph, I saw my first graphical spreadsheet—Wingz from Informix—and I thought it was terrible. Keyboard support was non-existent, which slowed everything down compared to the “slash” commands used in the DOS spreadsheet products.

That perception completely changed the first time I used Excel. I led the effort to standardize on Excel at Intergraph, where I managed international consolidations for 45 subsidiaries. In those days, I had the very first true PC in the company. It was on a cart that was supposed to be shared by the entire Finance Department—but it lived in my office. That’s where I got my first real hands-on experience with Excel, starting with version 4.0. From then on, Excel was central to my career.

Initially, I learned macros in Excel using the old XLM language, which felt natural after my experience with SuperCalc. But when Excel 5 introduced VBA in 1994, I dove in headfirst and started doing things that had never been done before. I was probably the first person to hook up Excel to Oracle Financials in a pivot table during the Excel 5 beta program! That also gave me a glimpse of the possibilities spreadsheets opened up. It also exposed some of the complexities, as the SQL query for that pivot table required a nine-table join.

My passion for Excel only grew stronger when I attended Microsoft TechEd in 1993 and met some of my Excel heroes. Around the same time, I started answering questions in the CompuServe Excel forum, which became my first real experience with community support. I’d read questions in the evening, experiment at work the next day, and then return with answers.

Before long, Microsoft recognized a few of us CompuServe forum contributors as MVPs, or “Most Valuable Professionals.” At the time, none of us really knew what that meant. All we knew was that we were getting T-shirts and trinkets for doing what we loved: helping people use Excel better. Still, it was quite an honor, as there were only five of us “Charter MVPs” in the entire world.

Excel opened doors I couldn’t have imagined. In late 1994, I left Intergraph to do Excel consulting, building Excel-based applications for companies, including Microsoft itself. Some of those applications were used personally by Bill Gates.

It was during this time that I encountered Essbase, which used Excel as its original interface. I wrote the first demoware for Essbase, the “Open Budget Pak,” a project funded by Microsoft to demonstrate Excel as a user interface for a database. After that product was complete, Arbor Software, the inventors of Essbase, sent me around the country working on the user interface side of high-profile Essbase projects. That experience was the spark that eventually led me to start Applied OLAP.

The first customer of Applied OLAP was a company in the telecommunications industry that used Essbase for reporting, but had an Excel-based budgeting system comprised of 1,200 linked Excel workbooks. That system was impossible to maintain, so I engineered and built a custom budgeting system for them using Visual Basic. The maze of linked workbooks I encountered there inspired me to create a product. Once I finished my work, I began conceptualizing our first product, ActiveOLAP, which was later adopted by numerous companies in the early 2000s.

Our flagship product, Dodeca, was born out of this journey. The idea came from decades of working with companies that used spreadsheets not just as tools, but as full-fledged, mission-critical systems. With Dodeca, we set out to standardize, automate, and streamline spreadsheet processes—reducing errors, saving time, and turning Excel into a true enterprise platform.

Forty years after its release, Excel has made a profound impact on my life. It gave me a career, introduced me to a global community, and led me to create products that continue to shape how businesses use data today.

If not for Excel, I honestly don’t know where I’d be. What I do know is that my life wouldn’t have been nearly as rich as it has been so far. I’m grateful that Microsoft invented Excel, and I’m proud of the path it set me on.

Let’s go back to the start of this post and my comment on Excel and the meaning of life. Look again at the formula and say, in English, what this formula is calculating.

That’s right—it reads, ‘To be, or not to be’! Ironically, no matter what numeric or Boolean value you place into cell $B$2, it always evaluates to TRUE. So, according to Excel, the ‘meaning of life’ is simply… truth.

Happy birthday, Excel—here’s to the next 40 years.

Time to Resurrect This Blog!

It’s been more than seven years since I last posted here, and I think it’s time to get back to writing.

There are a number of reasons I’ve been away from the blog. The biggest was a significant healthcare challenge within my family. My wife was diagnosed with early-onset Alzheimer’s disease, and for several years I was her full-time caregiver. Alzheimer’s is a terrible disease, and sadly, my wife passed away a couple of years ago. My experience is something I hope nobody reading this will ever have to endure.

During this time, I continued to run my company, but I had no energy left for writing. Those years were exhausting, and much of my creative energy went into the business itself. Now, though, I am recovering from the experience and have once again found myself with things I want to share. That’s why I’m bringing this blog back.

You may notice some visual updates here over time, such as a refreshed look and updated links, but more importantly, I hope to post new content on a more regular basis. I already have some ideas lined up, and my next post should be coming within the next day or two.

Thank you for returning to the blog. Over time, I plan to share not only my thoughts and opinions but also content related to Dodeca, Excel, Oracle Cloud EPM, Essbase, and Oracle ADW. I hope you continue to find useful insights here.

Sunday, March 18, 2018

Happy 20th Anniversary to Applied OLAP!


Today, March 18, 2018, is the 20 year anniversary of our incorporation! I has been a long journey since that time; here are some of the highlights:
  • 1998 - We were a one-man shop and wrote a reporting and budgeting application for a customer in New York. I spent about 150 nights that year on the road.
  • 1999 - ActiveOLAP for Essbase was released and we earned the trust of two of our long-term customers. It was during the next couple of years that I traveled to the West Coast about 35 times in one year.
  • 2003 - Portions of our web-service technology was acquired by Hyperion Solutions and we wrote the Hyperion Objects product based on that technology.
  • 2007 - The Dodeca Spreadsheet Management System was released.
  • 2014 - We hired our first resource to focus solely on sales. Prior to that, we marketed our software via 'word of mouth'.
  • 2016 - The Dodeca Excel Add-In for Essbase was released and we acquired the DrillBridge product.
During this time, we have grown the company organically without outside investment. While this strategy meant we had slower growth, the benefit is that it has allowed us to focus solely on the needs of our customers and not the needs of 'investors'. It also meant that we 'ate a lot of beans' in the early days. Those were great lessons in the value of a dollar that we carry with us today in the value of the software we provide to our customers.

Thank you to all of our customers. We feel lucky to work with each and every one of you and we continue to learn from each of you. We pledge to continue working hard to make your companies successful.

Tim Tow
Founder and President
Applied OLAP, Inc

Thursday, January 11, 2018

Using MDX for Generated Members in Essbase Reports


There are times when Essbase users may need to see an ad-hoc collection of members aggregated together in Essbase, and that isn’t always an easy task.  If it were an aggregation that is needed on a recurring basis, the Essbase administrator may add an alternate hierarchy to assist.  Other times, users might just create a spreadsheet with the desired members in different rows or columns and use Excel formulas to add them together.  In this blog post, I will cover a third option, the use of MDX to create dynamically-generated members, how to run them in Smart View, and how to make them much easier to use in Dodeca.

In order to illustrate how dynamically-generated members can be used, let’s consider an example using the Sample Basic database.  Here is a simple quarterly income statement query that I will use as the basis for this blog post:

SELECT
    {[Year].Children, Year} on COLUMNS,
    Hierarchize(Descendants([Profit]), POST) ON ROWS
FROM 
    Sample.Basic
WHERE 
    ([Market].[New York], [Product].[Colas], Actual)

The results from this simple query look like this:



This MDX is pretty straightforward, but what if you wanted to see how New York and Connecticut would look if they were combined?  This is the question that a generated member can return for you.

Generated members in MDX are created using the WITH MEMBER clause.  Moreover, the generated member can then be used anywhere a normal member can be used, even in a slicer dimension (or what we would call a ‘page field’ in the classic Essbase add-in or a point-of-view in Smart View).  Here is the query modified to use the new generated member:

WITH MEMBER
    [Market].[SelectedMarkets] AS 'SUM({[New York], [Connecticut]})'
SELECT
    {[Year].Children, Year} on COLUMNS,
    Hierarchize(Descendants([Profit]), POST) ON ROWS
FROM
    Sample.Basic
WHERE
    ([Market].[SelectedMarkets], Colas, Actual)

The results from this query look like this:


So far, so good, but there are a couple of things to note.  First, the member displayed in the POV is not a real member; that is to be expected.  This leads to the second thing in that you cannot refresh the query as an ad-hoc analysis; the dynamically generated member name will be replaced with the dimension member name in its place.

To go even further, what if you want to have multiple generated members?  In that case, the syntax is easy as you just continue with another MEMBER clause:

WITH MEMBER
    [Market].[SelectedMarkets] AS 'SUM({[New York], [Connecticut]})'
MEMBER
  [Product].[SelectedProducts] AS 'SUM({[Colas], [Grape]})'
SELECT
    {[Year].Children, Year} on COLUMNS,
    Hierarchize(Descendants([Profit]), POST) ON ROWS
FROM
    Sample.Basic
WHERE
    ([Market].[SelectedMarkets], [Product].[SelectedProducts], Actual)

The results of this query look like this:



The syntax for creating and using generated members is not that difficult, but there are a couple of things that make it a bit more difficult than it should be for end users to use this approach.

First, any time end users start having to deal with scripts of any kind, the level of complexity goes up exponentially.  As one of my mentors used to say, “The difference between zero lines of code and one line of code is much greater than the difference between one line of code and a hundred lines of code”.  In other words, it is hard to get users to deal with code of any kind.

Second, once an end user has to ‘write a line of code’, or script in this case, then they assume the responsibility for it being correct.  As there are differing levels of comfort and skill among users, the risk of error goes up.

Finally, when users use a script like the one used in this example, they have to type in the correct member names or, again, risk error. Here is the new MDX dialog in Smart View 11.1.2.5.720 showing where users type in the MDX including the member names.



To make it much easier for end users, Dodeca does a couple of things.  First, Dodeca developers can configure reports to use MDX without the end user ever having to know that MDX is powering the report ‘under-the-covers’.  Further, Dodeca has flexible Point-of-View selectors that allow the end user to simply pick which members they want to use in the query.

Dodeca report developers use tokens as a sort of substitution variables in the script.  The tokens are replaced in the script at run-time by the members selected by end users.  Here is the same script with tokens in place of the hard-coded values:

WITH MEMBER
  [Market].[SelectedMarkets] AS 'SUM({[T.Market]})'
MEMBER
    [Product].[SelectedProducts] AS 'SUM({[T.Product]})' 
SELECT
    {[Year].Children, Year} on COLUMNS,
    Hierarchize(Descendants([Profit]), POST) ON ROWS
FROM
    Sample.Basic
WHERE
    ([Market].[SelectedMarkets], [Product].[SelectedProducts], Actual)

The Dodeca Essbase Scripts editor has tools to help the report developer create and test MDX scripts.  Here are the Test Tokens available in the editor that allow developers to simulate the values plugged in by the Point-of-View selectors:


And the script itself in the scripts editor which has built-in testing facilities:



Finally, here is a Dodeca view that utilizes the tokenized MDX query and allows users to easily select the members they want dynamically aggregated and the report is produced without the risk of error.



Let me know if you would like to learn more about Dodeca and how it could help your company.


Thursday, January 4, 2018

Windows 10 Update Killed Essbase On My Laptop!


Like many Essbase consultants and developers, I run Essbase server on my Windows 10 laptop. It was a lengthy ‘Creator’s Update’ Windows update and, once it was complete, Essbase was dead on my machine. So, what do I do? First, I didn’t panic; us pilots have a way of not panicking when things don’t go as planned. We have several people internally who had this happen to them over the past several months and we fixed it each time, so there was nothing to worry about.

The root cause was that my OPMN service, which runs Essbase, was gone. This happened on the other machines we have that experienced that in the past, so I went to talk with one of our resident infrastructure gurus, Jay Zuercher. I remembered there was a command that I could run to recreate the service; Jay had the command filed away somewhere and within a couple of minutes, he sent it to me:

SC CREATE "OracleProcessManager_epmsystem1" binPath="C:\oracle\middleware\epmsystem11r1\opmn\bin\opmn.exe -S -I c:\oracle\middleware\user_projects\epmsystem1”

I ran this command – as an administrator – and then went into services to set the service to start automatically and start the service running. That did not, however, result in Essbase coming back to life. Next, I looked at the Essbase logs and noted several issues having to do with security. Initially, I thought there may have been due to an issue with Shared Services, but then I remembered about the fairly common Essbase issue regarding a corrupted essbase.sec file. I don’t know if the corruption was related to the Windows Update, but the timing sure was suspect. I replaced the essbase.sec file with a backup copy and I was back in business.

Hopefully this doesn’t happen to you when you update Windows but, if it does, perhaps this blog post will make your recovery quick and painless.

Thursday, July 13, 2017

Possible solution for TLS 1.2 issues between Windows 10 and Oracle EPM Weblogic


We have recently seen some users of both Dodeca and Hyperion products where Windows 10 machines have issues connecting to the Weblogic servers shipped with Oracle EPM due to the absence of the TLS 1.2 protocol.  The underlying issue is that Windows 10 is an evolution of technology whereas Oracle EPM Weblogic, and more specifically the Java version tested and shipped with it, are stuck in the stone age.  Java 1.6 started its journey to "end of life" in late 2013 and, though it continues to be covered under Extended Support, the EPM team has not delivered an update for their server.  Ironically, there is even a Java 1.6 version, Update 121, that now supports TLS 1.2; EPM is on Update 35.

So, what do you do?  I would be very hesitant to upgrade the Java version delivered with the EPM System.  After all, Oracle spent a lot of time working to certify on that version of Java.  One of our Senior Support Engineers, Jay Zuercher, did find something that appears to work - it hasn't yet been widely tested but may be worth a try.  Here are the steps he followed:

  1. Login to the Weblogic console.
  2. Navigate to Environment->Servers->AnalyticProviderServices0 (or to the server in which you are attempting to connect).
  3. Click on the SSL tab and expand the Advanced section at the bottom.
  4. Enable the “Use JSSE SSL” checkbox.
  5. Save changes.
  6. Navigate to the Server Start tab.
  7. Add the following string to the “Arguments” box:
    1. -Dweblogic.security.SSL.protocolVersion=TLS1
  8. Save changes.
  9. Activate all changes.
  10. Restart the applicable service. 
These steps are furnished with no guarantees, but hopefully you will find them helpful.


Tuesday, May 23, 2017

ACE Alumni

Today, I asked Oracle to move me from Oracle ACE Director status to Oracle ACE Alumni status.  There are a number of reasons why I decided to change status.  When I started answering questions on internet forums years ago, I did it to share what I had learned in order to help others.  The same goes for this blog which I originally started so that I could give better and more complete answers to questions on the forums.

After the Hyperion acquisition by Oracle, I was contacted by Oracle who asked if I would be interested in becoming an "Oracle ACE".  It was an honor.  But over time, things have changed.  As more people found out about the ACE program, more people wanted to become an ACE.  If you have ever monitored the OTN Essbase and Smart View forums, they have become cluttered with copy and paste posts from people obviously trying to increase their points.  As the ACE program grew, it also become harder for the OTN team to manage and now require a formal activity reporting - a time report if you will - to track contributions to the community.  As I am already extremely pressed for time, I decided that tracking my contributions to the community - in exchange for a free pass to Open World, just didn't make sense.

All of that being said, just because I have moved to Oracle ACE Alumni status doesn't mean that I will stop contributing to the community.  My company will continue to provide free downloads and support for the Next Generation (Essbase) Outline Extractor and the Outline Viewer along with free downloads of Drillbridge Community Edition.  And maybe, just maybe, I will finally have time to write some new blog posts (maybe even some posts on some new Dodeca features inspired by our work with Oracle Analytics Cloud / Essbase Cloud!)