June 23, 2016

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:

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:

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.

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:

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:

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:

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:

Multiple retrieval ranges: use the infinite flexibility of Excel to arrange data exactly how you want it, not some arbitrarily limited data arrangement
Use Excel formulas (and our existing Excel knowledge) to format the report exactly how we want, dynamically
Instantly build at the press of a button: no connections, selections, individual retrievals, row hiding, or formatting to deal with
Printer-ready output if we want a hard copy
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!

June 22, 2016

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:

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:

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:

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

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:

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.

June 21, 2016

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!

June 21, 2016

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.

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:

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.

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

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:

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:

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:

In the code you can see the following happen:

A connection details object is created
A connection to PBCS is made
Ask for the list of available apps
Print out the list of available apps
Get a reference to a particular app (“Vision”)
Call the refreshCube method on the app reference to refresh the cube
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.

June 20, 2016

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:

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

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.

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:

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:

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:

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!

June 20, 2016

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:

Dodeca Spreadsheet Management System
Dodeca Excel Add-in
Drillbridge (the easy way to implement drill-through for Essbase)
Next Generation Outline Extractor
PBJ (high-quality open source Java library for working with PBCS REST API)

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!

June 13, 2016

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!

May 22, 2016

Drillable Columns in Drillbridge

One of the more often requests that Drillbridge users have is to be able to have drillable columns. That is, they’d like to drill from a column in a Drillbridge report by clicking on the column value and pulling up another report, using the point-of-view (POV) of the original cell. This is most often used in a few different situations:

Drill into yet more detail for a given transaction
Drill to a PDF or image related to the current item
Drill to a custom URL, such as a Sharepoint URL

Drillbridge has supported this feature for quite some time, and it’s pretty easy to setup. Today I’m going to walk through the steps for setting up drillable columns in the latest version of Drillbridge Enterprise.

First of all, let’s take a look at a generic Drillbridge report that executes a query by converting the member from the Years dimension from something like “FY12” into the value of “2012” and then executes a query:

Note that for readability I have broken the token up across several lines. This works just fine in the latest versions too and is a nice way to increase readability. Let’s see what this query generates by going over to the Test screen:

And building the report:

Everything looks good so far – note that the drillable column hasn’t been setup yet so we shouldn’t be expecting any special links to appear yet.

Now let’s create a new report definition. This new report type will be a special report type known as a “Forwarding Link Report”. If you think about a normal Drillbridge report, it builds a SQL query by taking the POV and plugging it into a template. A Forwarding Link Report works in much the same way, except instead of building a SQL query, it builds a URL and forwards the browser to it. In this case I’m just going to build a simple link over to Wikipedia since they have a nice simple URL structure that is good for illustrative purposes.

Here’s our link definition:

Note the label above the text box for editing the link definition. Instead of saying it’s a SQL query, it refers to it specifically as the Link Template. This is because the report type itself has been changed (which is needed to make this type of report work). To change the report type we go to the Advanced menu and the click on Class, bringing up this editor:

Note that in this case, the types available are JDBC drill-through report (the typical report type), Forwarding Link Report, or MDX report (yes, Drillbridge can execute MDX queries too!). The Drillbridge report system is completely modular and developers can add their own report types using a simple Java plugin.

Custom report plugins can also easily “advertise” their available options to Drillbridge so that Drillbridge can provide a nice UI for configuring them. For example, in the case of the forwarding link report, there is just one option available (as opposed to the 20 or so options available on a typical report) that let’s us choose the HTTP “verb” type to use:

Let’s head over to the Test tab for this new link report and see what happens:

Let’s build it and see what happens:

As expected, the given input was used to build a link and our browser was redirected there immediately. This is one of the nice things about doing drill-through in a web browser: you can do pretty much anything you want.

Now, what we’d like to do is make one the columns in our first report drill over to the results of this new link report we just setup. To do that, let’s go back to the original report, then go to the Advanced menu, then Drillable Columns:

Then click on New Drillable Column. The editor will be brought up:

Here we have a chance to give this drillable column a name, tell the report which column should be drillable, and then choose the target report. We can put in anything we want for the name. What’s important in the column index and target. The column indices start at 0 for the first column and then count up. In this case we want the third column to be a link, so this is column index 2 (0 = first column, 1 = second column, 2 = third column, etc.). Then we select our new report as the target report, which I named “Wikipedia”.

Let’s go and test this source report again and see that the third column has links now:

And sure enough, clicking on one of the links brings up the Wikipedia report – which doesn’t bring up a report at all, per se, it just executes the report, which uses the POV to build a URL and automatically redirect the web browser. That’s it!

Report POV vs. row POV

It’s important to consider what the POV handed to our target report will be. The POV is actually the combination of two things, out of necessity: the POV of the source report merged with the POV of the row itself. Whereas the normal report POV might be something like Scenario = Actual, Year = FY12, Time = June, the POV handed to the target report will be all of those things, plus keys and values from the current row. For example, if the original report only considered a given account but showed details for transactions, and there was a column called TRANSACTION_NUM, then the POV would also include a key named TRANSACTION_NUM with a value of whatever the value from that row is. This is critical for users that want to link to something or execute another query using data from the row.

Drillable Columns is one of my favorite features of Drillbridge because it’s so easy but powerful, but more importantly, it saves people time, and that’s what Drillbridge is all about.

May 16, 2016

Dependent Selectors in Dodeca

The dependent selectors feature in Dodeca is one of my favorite “simple” features. I say “simple” because the implementation is very straightforward. I like it so much because the crux of this feature is about getting users to their data and reports faster, with the most streamlined UI possible. Not to get on my architectural high horse too much, but I believe that solutions – whether it’s a cube, a view, software, an ODI solutions, or whatnot – should be elegant, maintainable, and intuitive. Perhaps in another life I was a UI or UX designer. Advanced software such as Dodeca and its more niche cousin Drillbridge strive to make users and administrators alike as productive as possible.

To that end, dependent selectors are a way to provide an enhanced report/view build experience. In a typical Dodeca report, you typically select values from one or more selectors: choose a time period, choose a year, choose a location, build the report. You might choose multiple selections such as multiple time periods – it all just depends on how the selector list has been configured. A dependent selector is one whose value changes depending on one or more of the other selectors.

As a straightforward example, consider a view where the user must select a state and a city to build a report. The available states to select are, say, the 50 states in the US. And the available cities may span all of these states. But let’s say in this example that when we choose a state, we want to choose from a list of cities in that state using another selector. By doing this we get people to their data faster and improve the user experiencce.

This can be accomplished easily with a dependent selector. Let me show you how, starting with some simple tables modeling the states and the cities.

Here we have the table STATES for states and STATE_CITIES for cities. Also note that even though this is a bit of a “quick and dirty” example, I have nevertheless given the STATES table a primary key of STATE_ID (the two letter postal state code), and a STATE_NAME column with the “nice name” of the state. This will come into play more a little bit later when I talk about selectors and the difference between an ID and a value. Next, over in the STATE_CITIES table, we have a compound primary key of the combination of the STATE_ID (a foreign key into our STATES table), and the city name. These data constraints ensure that every state/city combination is unique, and that every city is associated with a particular state that exists in our states table.

The tables we have designed so far will be used as the basis of a Dodeca selector. Dodeca is very flexible with respect to how selectors are populated, with two of the primary methods of populating them being from Essbase dimensions or generated with SQL. In this case we’ll use the SQL Passthrough DataSet editor to create a query that pulls back the list of states from this table:

We can quickly and easily test the selector by using the Test Data Set button in the interface, showing that the query does indeed work (note that I only populated the table with a few states):

Now things get a little more interesting where we go to define the query for our city selector. We write a normal query, but in place of a value for the WHERE STATE_ID clause, we put in our states token (written as [T.States] in this case. Come runtime, the value of [T.States] will be replaced with value from our state selector, based on what the user has highlighted.

One of the newer features in Dodeca is the ability to save and edit test tokens so we can test the query out immediately instead of having to build a view first. Pressing the Edit TestTokens button brings up the following editor:

Let’s plugin a value of WA for [T.States] so we can test out which cities come back when the state is Washington. Note that my tokens value corresponds to the ID of the state in this case. I’ve configured the selector to use an ID value in this case, but I could have used the state name if I wanted to. Wherever possible I prefer to use the “core” or identifying value of a piece of data. Let’s test out the query to verify that the query works and the correct data comes back:

For good measure let’s swing by the Selectors editor and ensure that we have created selectors for [T.States] and [T.State_Cities]:

Now let’s look at the Selector List that corresponds to our State from the Selectors configuration. In particular note that the type of the selector list is a SQLPassthroughDataSet where we have chosen the associated data set ID:

Now let’s go over to the city selector list, where things get a little more interesting. Here we can choose the DependentOnSelectorIDs value and tell this selector which other selectors will influence it. We simply choose States:

With the selectors all configured, we can now include them in our view by including them as we normally would. For those of you not familiar with Dodeca, a view is like a report template, and when we administrators design the view for users, we can control an incredibly large amount of its behavior very easily. Each view can have its selectors configured, which will influence the options that a user gets to select from in terms of configuring and building the view. So by going to the Selectors configuration for a particular view/report, we get to choose which selectors should be associated with the view. In this case, it’s the State and State_Cities selectors. We could easily have more (and usually would, such as time periods, years, and other things).

With that all set, let’s commit our changes and go take a look at the view. Note how I have selected Washington from the State selector in the top right of the window. In real-time when I select Washington, the City selector refreshes instantaneously, showing just those cities from Washington:

Since I have Auto Build turned on for this report, once I have selected a city the report refreshes using my selection:

The report shown is just an incredibly minimal report for testing purposes that simply uses the city as part of a simple retrieve, but an arbitrarily complex report could also have been designed and used.

Selector ID vs. Display Value

I mentioned earlier that while we were showing the full name of the states, we were actually using the state code under the hood, so to speak. This is somewhat analogous to member names and aliases in Essbase, where we have a core name (one that is often a little less “friendly” to the user) and a display name that is more friendly. As a configurable option, Dodeca let’s you use a value for display and an internal value for the ID – if you want. In this case it came in handy for the SQL because the query I wanted to write to fetch the cities was simplest if it got to use the state ID rather than the name. Without this feature I might have had to just show terse IDs to the user or rewrite the SQL query to join against another table so I could dereference the full state name. I think the design win here is that we got to show the “nice” name to the user but not have to make any sacrifices in the design (in terms of enduring additional complexity).

March 24, 2016

Kscope16 sessions I’m looking forward to

I was looking through the list of sessions coming up for Kscope16 to get an initial idea of what I want to attend. And, wow. There are so many incredible sounding presentations this year. I seem to say this every year, but the content this year seems just especially strong.

That said, a few sessions jumped right out at me as things I absolutely want to attend:

How ADT Gained User Acceptance in Its Delivery of Essbase and Oracle General Ledger Data to Business Users
Should be a very solid real-world example of using the Dodeca Spreadsheet Management System (disclosure: I am an Applied OLAP employee… but I’d be attending this anyway!).

Torn Between Two Worlds: Is Essbase a Business or an IT Tool
Joe always does a nice job on presentations, and I’m looking forward to one that might be more philosophical than technical, especially as the nuance of where Essbase resides (and is therefore managed, used, and more) is a very important issue that a lot of companies struggle with. As a developer, the most successful Essbase environment I have participated one is where Essbase was ‘owned’ by Finance or a line of business. On the other hand, I have done work for organizations where Essbase was managed (and sometimes mismanaged) by IT. But the reverse has also been true…

Essbase Does It, but Dodeca Makes It Easy
Dodeca’s success in the enterprise has resulted in an unprecedented number of Kscope sessions this year as more customers adopt this powerful technology. This presentation is by a customer – St. Jude Medical – and I am very curious to hear about their successes, particularly with respect to being long-time Essbase users.

A Stomp Through the Tulips of Essbase Cloud Service
Steve Liebermensch of Oracle always puts on good, information-packed presentations and I am confident this will be another one that I won’t want to miss.

Case Study: Reduce the Kroger Essbase Footprint without Sacrificing ANYTHING! And Have Room for Growth…
Kroger is near and dear to my heart. It’s where I started (literally) – in a small division, bagging groceries – and eventually worked up to finance where I helped deploy Essbase across the entire enterprise. This presentation immediately caught my eye, owing to its co-presenter. The main presenter is Kadee Rodriguez, who I never had the pleasure of working with directly). The co-presenter is Christine Blea. During my time at Kroger, Christine administered Essbase for another division, and it wasn’t uncommon for us to call or email and share some knowledge, calc script, formulas, or whatnot. If I’m not mistaken, this is Christina’s first time presenting (or at least co-presenting!) at Kscope. In any case, I just gotta go see what my favorite grocer is up to and cheer on the team.

And I’ll definitely be attending my own sessions:

Drillbridge: The Easy Way to Implement Hyperion Drill-through
Drilbridge is back for its second Kscope presentation. Last year I did a “soup to nuts” live demo of Drillbridge – starting at literally downloading the ZIP file from the web, unzipping it, installing Drillbridge, making a custom report, deploying it to Essbase, and using drill-through from Smart View – in exactly 14 minutes. Let’s just say that Drillbridge has learned a few new tricks since then.

Hey Mom! Look What I Built with the Essbase Java API!
This session is ostensibly presented by Tim Tow and Harry Gates as co-presenter. Which I guess makes me the co-co-presenter. In any case, this is a bit of a hybrid format presentation were we can talk about the Essbase Java API and some of the interesting things we’re doing with it.

On-Premises Planning vs. PBCS: Common Administrative Tasks Compared, Contrasted, and Recommended
Cameron and I will take a look at on-prem versus PBCS. My angle on this will be more on the REST API for PBCS and how its usage compares to traditional ways of implementing things.

jason's hyperion blog

essbase from the trenches

Author Archives: jason