Multiple Retrieves in Dodeca

Continuing on in this weeks blog series and lead up to Kscope16, I’m going to show off one of the fundamental concepts in Dodeca with regard to building reports: multiple retrievals in the same sheet. This is a core feature of Dodeca that has been around since its earliest days.

I would guess that the vast majority of spreadsheets built to retrieve Essbase data are just a single retrieve. But quite frequently we want to have a sheet with data from multiple retrieval ranges on it. These other retrieval ranges might be from the same database/cube or from another data source. In a pure Excel environment, the process of updating a sheet with multiple retrieval ranges is straightforward, if a bit tedious: select the first retrieval range, retrieve it, select the second, retrieve it, and so on.

For one or two retrieves, this isn’t so bad. But for a report that is regularly run, has some formatting, and possibly some other tweaks needed, the effort becomes a bit tedious (and time consuming).

This is where Dodeca’s support for multiple retrieves come into play. We can configure a sheet with absolutely any Excel formatting we want and an arbitrary number of retrieval ranges. For example, consider our good ole friend Sample/Basic. We’d like to setup a report that shows measures for a given product and year for various markets. We can setup a simple Dodeca template like this:

dodeca-multi-retrieve-01-template

There’s nothing too fancy just yet. Take note of how the Year (time period) and Product are tokenized with [T.Product] and [T.Year]. As with before, this means that the selection(s) from the user will be plugged in for these tokens and then a normal Essbase retrieve will be performed.

The plan here is that we’re going to actually have multiple boxes like this on our sheet for all sorts of different markets. That said, we don’t really need to show the time period and product in every single box, since they will be the same for each box on the sheet. So it’s a good candidate to show at the top of the page. So let’s do that:

dodeca-multi-retrieve-02-template-title

Note the formula in cell B2: it’s referencing cells C6 and C5. Sometimes Dodeca forces us to think temporally with regard to how we construct our spreadsheets: during template development, we just see the token names. But remember, when the sheet gets built, the token will be replaced with the value of the selector (in this case), and the value shown in our title will update because it’s dynamic. This is an incredibly foundational element in Dodeca templates. We can use any Excel formula too: so if we need to trim a string, concatenate, or whatever, that’s all in our toolbox.

As a quick aside, things like this (with formulas) tend to be one of the reasons that Dodeca resonates so strongly with users and power users in finance at various organizations: because an incredible amount of existing Excel knowledge is directly applicable and useful. It shortens the learning curve. A lot.

Now that we have a basic format our our “measures box” that we like, we can simply copy and paste it to create additional boxes with a fixed market. Remember, while the user can choose the product and time period for this report, we’re going to give them a fixed list of markets for now. As you can see in the screenshot, I have updated the Market to Oregon and Washington in the new boxes. Additionally, I have hidden the time period and product from each box (since the boxes are all aligned I just wound up hiding two rows and that affected each box). Even though the rows are hidden, they will still be part of the retrieval range. Speaking of retrieval ranges, we have three of them now: named ranges that correspond to the retrieval to be performed for each data box.

dodeca-multi-retrieve-03-built

Just for fun, and to continue on with the theme of “your Excel knowledge is applicable and useful”, let’s adjust the page footer a little bit, using the common page header/footer editing dialog box, and place in the current date in the lower right corner and a note about confidentiality in the lower left:

dodeca-multi-retrieve-04-footer-configuration

Lastly, I’ll adjust the print range, page orientation, and do a print preview to see what this report is going to look like when it’s printed out:

dodeca-multi-retrieve-05-print-preview

Not shown here, but I also went into the options and turned off row/column headers and gridlines to clean the appearance of the report up a little bit. These are just easily toggled with a simple option. Everything is looking good. Now let’s run the view, select a product and time period, and build it:

dodeca-multi-retrieve-06-cleaned-up

And there we have it: a nicely formatted report that users can build with any product and time period, at the click of a button, and get nice print-quality output if they want a hardcopy. I want to recap some of the fun things going on here:

  1. Multiple retrieval ranges: use the infinite flexibility of Excel to arrange data exactly how you want it, not some arbitrarily limited data arrangement
  2. Use Excel formulas (and our existing Excel knowledge) to format the report exactly how we want, dynamically
  3. Instantly build at the press of a button: no connections, selections, individual retrievals, row hiding, or formatting to deal with
  4. Printer-ready output if we want a hard copy
  5. No off-by-one errors or other innocent mistakes involved in the process of generating the report: get the right data, every time

In the coming weeks I’ll show off some more advanced examples that combine multiple data sources, multiple types of data sources (SQL data anyone?), and more. Stay tuned and don’t forget to swing by the Applied OLAP booth at Kscope and say hello!

Cascading Report Summary Sheets in Dodeca

Earlier this week I looked at setting up report cascading with Dodeca. With that foundation in mind, today I’d like to look at elaborating on it a little bit by adding on a summary sheet with Dodeca’s built-in summary sheet functionality.

What is a Summary Sheet?

A summary sheet is a sheet in our workbook that is derived from the other sheets generated from a report cascade. For example, let’s say that in my earlier cascade example, I chose two products and two markets: Colas and Diet Colas as my products, and Washington and Oregon as my markets. This would result in four sheets/tabs being generated. Now, what Dodeca allows me to do pretty easily is turn on a summary sheet, which will automatically generate a tab for me (this will be a fifth tab in this example), and contain formulas I designate that will sum up the sheets generated from the cascade.

This is really useful for a couple of reasons. Now, in a perfect world, you’d be able to get summary data from the outline itself, for example, a parent rollup in the Product, Market, and Time dimensions. That’s the whole point of Essbase, right? But sometimes we need arbitrary groupings of things. Frequently this is accomplished with alternate hierarchies. But what if we want the user to be able to create a report with a summary value that is neither in the primary hierarchy nor any alternate hierarchies?

One way we can provide this functionality to our users is to turn on a summary sheet. Here’s a look at the summary sheet options, they are located under the Cascade option group on our Essbase Excel view:

dodeca-summary-sheet-01-options

In particular, note that SummarySheetAdd is set to True. Additionally, I have specified a name for the sheet (SummarySheetName = Summary), specified a SummarySheetPosition (I can have my sheet be the first or the last sheet), and a range of cells to summarize (SummarySheetSummaryRangeAddress). Per best practices, I have created a defined name in my workbook so that I can refer to it easily. Additionally, if I adjust the rows and columns in my template, I don’t need to come back to these options and worry about updating some arbitrary range name.

Let’s go take a look at the simple source template:

dodeca-summary-sheet-02-template

As you can see in the template, I have defined a fairly bread and butter Dodeca Excel template, where I have tokenized the Year, Product, and Market, meaning that users will be able to make choices from the selectors and build the report with whatever data they want.

Remember the summary range I mentioned earlier? Here it is, highlighted, showing the cell ranges that will be added up on the summary tab:

dodeca-summary-sheet-03-defined-name

Just for completeness, check out the retrieval range that will be refreshed when the view is built:

dodeca-summary-sheet-04-retrieve-range

Remember, Dodeca can do multiple retrievals in a single sheet and from multiple, disparate data sources. In this case we just have a simple retrieve from the classic Sample/Basic app, but this could just as easily be relational data from SQL Server, Oracle, or all of the above.

Lastly, having made selections from multiple dimensions, let’s build the view and check out our new summary sheet. Note the Summary tab that appears first, just as we wanted:

dodeca-summary-sheet-05-built-summary

There we have it: with just a few additional options to configure, I have a nice, dynamic summary sheet in this book that users can easily use. What’s even more interesting, is that I gave a user the ability to dynamically sum up data that doesn’t have a member or alternate hierarchy to do the math for me. In this case, it’s the arbitrary sum of Colas and Root Beer for January and February.

Saving Everyone Time

Not to belabor the point, but I think this example shows off some really interesting ways that we provided flexibility to the user and saved the user and administrator time. Without such dynamic summing capabilities, our user might have resorted to pulling data down to Excel and summing things up by hand/formulas, or otherwise going out of their way to get to data that wasn’t directly in the cube. As a one off it’s not so bad, but if it’s a way of common way of seeing the data, then you are potentially saving yourself a minute here and a minute there on a repeated basis.

Our administrator saved time because this might represent an alternate member/group that would be nice to have in the outline but not necessary to have. And in today’s complex metadata management world, that might just represent time not spent marshaling a member request through a byzantine approval process.

In Summary (Pun Intended)

Summary sheets are a very easy to utilize feature in the Dodeca Spreadsheet Management System and can offer your users a convenient way of looking at data that might otherwise be inconvenient (or worse, error prone!) to look at. As with so many other Dodeca features, they can quickly add value to an existing solution and provide your users with that little bit of extra utility that takes your overall solution from good to great.

PBJ PBCS Client GitHub repository

For those of you wanting to play with PBJ, the full code-base is available over on GitHub in the PBJ repository. Just to be clear, this is for people that want or need to edit, view, or make contributions back to the code. You don’t need to do anything with Git/GitHub if you just want to consume the library out of Maven or otherwise include the JAR file in your projects.

That said, there are a few things missing from the library that are available in the PBCS REST API, such as some items having to do with Planning Units, and some new REST API methods that are going to be available soon (having to do with data slices). But in the meantime, enjoy!

Advanced integration with PBJ Java PBCS REST API library

This week’s blog posts are all about the upcoming Kscope16 conference and relate to the presentations I’m part of. This year I am co-presenting with Cameron Lackpour on on-premise Planning versus PBCS and talking about some different use cases. My particular focus for this presentation is how you might use the PBCS REST API with Java.

Over the last year I have put together a Java library that works with the PBCS REST API. It has the following characteristics:

  • Open Source (Apache Software License version 2.0)
  • Doesn’t depend on any Oracle libraries or code
  • High-quality, readable, fluent API

In my opinion, all of these goals have been met. Additionally, as of today, PBJ (PBCS Java Client) is available in Maven Central. What this means is that if you or your team programs in Java and use Maven for dependency management, you are just a few clicks away from being able to use this library.

For an overview of why you might want to use PBJ and how it compares to other scripting languages you might want to use instead (such as Groovy, Python, and more), come check out the presentation!

Today, however, I want to show how easy it is to incorporate PBJ into a Java program and do something quasi-practical. So the rest of this article will be oriented towards programmers, but for those of you that have employees or teammates that would be more likely to do the programming aspects of things, keep them in mind and send them a link.

First, let’s assume we’ve already created a new empty Maven project in Eclipse. Your experience will vary if you use IntelliJ IDEA or some other IDE. In this example I happen to be using Springsource Tool Suite (STS), which is pretty much the same as Eclipse.

pbj-up-and-running-01-empty-project

Next we need to open up our pom.xml (project definition file) to add a new dependency. You can do this manually by editing the XML file itself, but there’s a nice enough GUI in Eclipse that makes things even easier:

pbj-up-and-running-02-pom-editor

Next we need to go find the PBJ library. As I mentioned earlier, PBJ is available in the global Maven Central repository. If you have Maven set to update its index periodically or upon startup of your IDE, then you should be up to date and can find the PBJ library. As of right now the version of PBJ is just 1.0.1.

pbj-up-and-running-03-pbj-maven-dependency

Select the library, click OK, and then save the file. The PBJ library and its dependencies are added to your project.

pbj-up-and-running-04-pbj-added

Now we can create a new main class to test things out. At this point it’s just life as normal for the Java developer:

pbj-up-and-running-05-new-main-class

Just for good measure (and tradition!) let’s put in a simple code to print out “Hello world”, and run it. Note the output in the bottom middle pane:
pbj-up-and-running-06-hello-world

At this point we have project setup, we have all of the necessary PBJ files (and some additional transitive dependencies), and we are ready to write some Java code that uses methods in the PBJ library. The code to write is shown below:

pbj-up-and-running-07-refresh-cube

In the code you can see the following happen:

  1. A connection details object is created
  2. A connection to PBCS is made
  3. Ask for the list of available apps
  4. Print out the list of available apps
  5. Get a reference to a particular app (“Vision”)
  6. Call the refreshCube method on the app reference to refresh the cube
  7. Print message after cube refresh

It’s hard to imagine this code being much simpler. If might look like greek if you’re not familiar with programming or Java, but to a Java programmer, this will be readily comprehensible and its intent obvious. PBJ supports most of the REST API – importing data, metadata, business rules, and more.

To see a specific use-case and hear wry commentary from myself and Cameron, please swing by our presentation. We’ll cover an example of PBJ (don’t worry, it’s higher level than this!) but more generally some facets of administration of on-prem versus PBCS will be discussed as well. I think the presentation will really appeal to many different user groups.

Cascading Reports with Dodeca

One of Dodeca’s banner features is its incredibly advanced support for cascading reports. In case you’re not familiar, cascading reports are where multiple views or reports are built for all different permutations of given parameters. How this generally relates to the world of reporting is that we may want to choose multiple items across several different dimensions, then generate a report or view for each different combination.

For example, consider an Income Summary report where a user would want to choose a particular location, scenario, and product combination. In Dodeca, these would each be a selector associated with a report. As an administrator, when I am designing my report, I  associate these selectors with my report. When an end-user of the report goes to build it, they will be presented with choices from each dimension to simply choose from.

Of particular note is that the end-user doesn’t really need to know what a selector is, how it’s configured, where its data comes from, or anything. All they need to know is that they can click on whatever they want and nicely formatted data will be presented to them in a familiar, intuitive format. Incidentally, quite often the format of the report that comes back is their own design – some gnarly spreadsheet that used to take minutes or hours to painstakingly refresh, and now comes back reliably and quickly, every time, with the push of a button.

Let’s take a look at how this plays out for a user, using a simple Income Summary report. First, let’s load the view by clicking on it:

dodeca-cascades-01-unconfigured-view

The report has not run yet, we must choose selections for each selector, then run it.

dodeca-cascades-02-selectors-configured

Now we have selections for each selector: notice that we want Colas, Cola, and Diet Cola from the product dimension, New York, Oregon, and Washington for the Location, and Actual for the scenario. The number of permutations of report is 9: 3 locations times 3 products times 1 scenario. Now that we have selections made, we can run the report.

dodeca-cascades-03-built-report

Notice the different tabs at the bottom of the sheet. Let’s collapse the selectors to get a little more real estate for the view and the tabs:

dodeca-cascades-04-tab-view

As you can see, a custom tab name based on the value of the selector for that tab was generated. The first tab is Colas – New York – Actual. This is one of those times when something simple goes a long way for the user: instead of some terse or generic name, the exact description is used. The text of the tab is even configurable (of course), if we need something a little more custom than just the names of the items.

Before I delve into the administrator configuration of cascading, I want to take a brief moment to wax technical on some of the power and flexibility we’re seeing.

Dodeca reports aren’t limited to a single datasource, a single type of data source, or even a single version of data sources. Dodeca’s flexible and robust spreadsheet paradigm allows it to arbitrarily combine, use, and build data from any number of data sources, even on a single sheet. For example, we could build a view that pulls some data from a relational database, and then uses it to build several retrievals from multiple Essbase cubes, all on the same sheet. So consider this: we can give our users the ability to build such a report, with disparate and heterogenous data sources, and have the report be built dynamically for every given permutation of selections – selections which, by the way, themselves may be from multiple data sources. The selections in the above screenshots all happened to come from the dimensionality of an Essbase cube but they could have just as easily come from a dynamic SQL query.

Under the Hood with Cascade Configuration

Now that I’ve shown an example of a cascading report, let’s talk a little about how it’s setup under the hood by the Dodeca administrator or report builder. Of the numerous properties we can use to configure and fine-tune the behavior of a view, there is a dedicated section for the cascading options:

dodeca-cascades-05-cascade-configuration

Generally we are okay with the defaults for the cascade options so we can leave them alone. The main thing we need to configure is to tell Dodeca which selectors will participate in the cascade. We can further configure options for Cascade Sources:

dodeca-cascades-06-cascade-sources-configuration

That’s it – that’s all there is to it. Keep in mind that in Dodeca, selectors are highly reusable components that are used across multiple views/reports. So we didn’t really have to do any special one-off setup for these selectors, just grab and go.

Stay Tuned

Stay tuned for more this week as I do a daily blog post about a particular feature of software in the Applied OLAP software suite. More to come!

Kscope16 is almost here!

Here we are again, just less than a week before Kscope16. I say this every year but this year’s Kscope promises to be the best yet, and I’m starting to get really pumped. There are a lot of exciting things going on in the greater Essbase world right now, and even my own little corner of this world is quite exciting (to me at least), including such fun things:

All of these items will be present at the conference, in one form or another. Dodeca will be featured in several presentations this year as customers talk about their implementation successes. Drillbridge has its own presentation again this year, given by yours truly, the outline extractor has a session put on by Tim, and the PBJ library will make up about half of a presentation with Cameron talking about on-prem versus PBCS.

That all said, this week I’m going to blog daily about some of the cool things in the world of Dodeca, Drillbridge, the Outline Extractor, and more. Stay tuned for a busy week!

Towards Spreadsheet Management

I follow quite a few Hyperion related blogs. Years ago there weren’t too many of them but now I’m quite pleased to be able to follow dozens of them with my RSS reader of choice. The other day I read an article posted by Edward Roske titled “7 Signs Your EPM Is Lagging Behind Your Competition”. Edward has been working with Hyperion for a long time and runs one of the more well known consulting firms, so he has seen quite a bit. And his thoughts are insightful. While I was reading through the article I couldn’t help but read it with a “Dodeca colored lens”, if you will.

For example, one of the signs is that strategy is planned verbally or in spreadsheets. The interesting thing here is that the spreadsheet modeling paradigm itself is a robust and essential. Where things go sideways is with how the files themselves are managed (and mismanaged).

For example, consider a typical analyst or a power user analyst that creates a spreadsheet: some pulls from Essbase, perhaps some relational data pasted in, some formulas, multiple tabs, formatting, and all that fun stuff. So far so good (well, not really, but let’s say it is). Now they email it out. Some feedback comes in from the CFO. Now the sheet Profitability Q3 2016 becomes something like Profitability Q3 2016 – Revised. More feedback comes in. More meetings. Now it’s Profitability Q3 2016 (2). Teresa down the hall needs a copy, so the analyst copes it to the LAN. Teresa makes some changes but the analyst has a lock on the file, so she saves Profitability Q3 2016 (2) – Teresa. I’ve seen some pretty heinous file management in my time.

This is an all too common scenario in the world of spreadsheets. Things get ugly quickly. And this is to say nothing of links across tabs and sheets and a host of other bad practices that make the situation even more error prone and hard to manage.

Another sign from Edward’s blog is that Excel is the key enabling technology in your FP&A department. I agree completely with the sentiment here. Note that Excel is specifically mentioned – not the spreadsheet paradigm itself. Excel as the enabler of FP&A is yesterday; the flexibility of spreadsheets combined with the power of Essbase is tomorrow.

Some of Edward’s other points such as there is only one version of the budget and budgets favor precision over timeliness are also spot on in terms of their accuracy.

Enter Dodeca

To reiterate from the beginning of this post, I couldn’t help but read this blog article while thinking about Dodeca. It’s because Dodeca takes all of the best things about spreadsheets: their power, their expressiveness, their familiarity to so many finance users, leverages the power of Essbase, leverages data from relational databases, and marries it all up in one cohesive interface. It’s saving numerous people and companies a lot of time: time that is not spent laboriously refreshing report decks, time that is not spent copying files around, time that is not spent emailing files, time that is not spent posting things to SharePoint or the LAN, and even a fair bit of time on end-user training.

I’m really looking forward to this year’s Kscope in Chicago. There are multiple presentations by Applied OLAP customers on how they built solutions with Dodeca while increasing productivity and reducing risk. And of course, we at Applied OLAP will have a booth. Please swing by and say hello!