Self-drilling and finite state machines, does your OLAP tool support them?

Self-drilling, drilling down to itself, is an interesting concept in business intelligence reports. Among many things, it allows your OLAP tool to turn into a simulation tool much like Crystal Xcelsius. See the following video:



In the video, I'm working with the Voyant OLAP tool and a line chart. I click several times a meter control = the simulated change percent for the last three months of the year (between -100% and +100%). When clicking, the forecast (green line) moves accordingly.

How did I do it? Self-drilling is the key.

Step-by-step (please try it with your OLAP tool):

1. Create a new table: CREATE TABLE sim_change_percent (change_percent INTEGER)

2. Populate the table with integers between -100 and 100 = 201 rows.

3. Define a query for your report: SELECT choice.change_percent "Choice", display.change_percent "Display" FROM sim_change_percent choice, sim_change_percent display WHERE choice.change_percent <> display.change_percent

Please note that the two instances of the sim_change_percent table "choice" and "display" are joined by <> operator. The query produces 201 x 200 rows = 40 200 rows.

4. Create a stacked column graph where you use:

• Legend of the chart = measure dimension = COUNT(*)
• Category axis = Display column of the query
• Menu choice/filter = Choice column of the query

The report looks like this:



Note that every column height is one (that's because of COUNT(*)). Also note that when you have chosen a zero percent from the menu choice/filter above the report, the corresponding column is missing from the chart (that's because of the <> operator).

5. Add a formula/calculation to the measure dimension: 1-[count]

This fills the gap in the stacked column chart. Because there are two members in the legend, you can use different colors for them to show which one is selected. The next image shows colors changed and boundary lines removed.

6. Now to self-drilling: Whichever way it's done in your OLAP tool, specify that clicking a column drills down to the menu choice/filter of the same report.

In Voyant, you do this by specifying interaction options.

7. Change the visual display by removing the legend and both axes. Specify the gap width between columns to 0%. Add explanatory texts (titles, subtitles, footers, etc) around the meter control to make it more comprehensible. Also add a tool tip (called a chart tip in Voyant) to display a change percentage near the mouse cursor.

Thanks to self-drilling, the stacked column chart now looks and performs like a meter control.

Let's continue to the line chart with Actual, Forecast, and Budget:

8. Create a new table: CREATE TABLE sim_money (month INTEGER, actual DOUBLE PRECISION, forecast DOUBLE PRECISION, budget DOUBLE PRECISION)

9. Populate the sim_money table with the following numbers (12 rows):



10. Define a query for your line chart report: SELECT month, change_percent, actual, forecast, budget FROM sim_money, sim_change_percent

The idea of the query above is to not join at all the sim_money and sim_change_percent tables. This cartesian product gives us 12 x 201 rows = 2412 rows.

11. Specify a line chart by:

• Legend of the chart = measure dimension = SUM(actual), SUM(forecast*(100+change_percent)/100), SUM(budget)
• Category axis = month column of the query
• Menu choice/filter = change_percent column of the query

After enhancing the visual display the line chart might look like this:



12. Position the two charts as in the following image, hide the menus, and make them synchronized.



Synchronization means that both reports share a global menu choice/filter (change percentage with 201 choices). Neither of the menus/filters need to be visible, because the meter control enables you choose by clicking.

The simulation model is ready. Please leave a comment, if your OLAP tool is able to do it.

Additional notes about self-drilling:

• When using self-drilling, you always need a query with a cartesian product.
• Self-drilling makes simulation models available in OLAP tools. If a report uses two simulation variables, you need two cartesian products in the query, etc.
• In a larger perspective, self-drilling allows you to create finite state machine models. Transitions between states are performed by mouse clicks.
• A finite-state-machine way of thinking allows you to create versatile navigational systems. This includes a simple expert system, where a series of questions and answers help a user solve a specific problem. For example, rows of a sheet contain different choices (answers) that take you to the next state (the next question and the next set of answers).
• Because of one or more cartesian products that exist behind self-drilling systems, the number of rows included in calculation grow easily very large. Thus, OLAP tools that store all the data in RAM become more easily out of action. Self-drilling works best with online data access from a database.

PS. Self-drilling as an idea and as a term are my independent inventions, as well as the ways to utilize the concept. Please leave a comment, if you know somebody uses a different terminology about the matter.