Easy relational drill through with Dodeca

Introduction to the introduction

This thread on relational drill through on Network54 just won’t die.  Is it a zombie?  Nope, just an extremely popular subject.  With that in mind, and as I’m the one that suggested that my fellow Developing Essbase Applications author Joe Aultman ask the initial question over a year ago, an overview into how easy drill through in Dodeca can be is long overdue.


One other note – this is a long post, but that isn’t because doing relational drill through in Dodeca is hard, but because I want to document each and every step so you can see how easy it is.  FWIW, I went from thinking it couldn’t be hard, to realizing I had no idea how to do this, to actually reading the documentation, to doing it for the very first time in about a day.  And now you don’t even have to read the docs.  You’re welcome.

How easy does Dodeca have to be?

Tim Tow has covered on his blog how easy it is to bring relational drill through to your Essbase-based application.


That’s fine you say (These voices, voices, VOICES, ahhhhh!!!!!!!!  Oh, you really are saying that?  Okay, nothing to see, move along folks.), but Tim is well…Tim.  And you (or would that be me?) are not a superstar, but a mere Essbase hacker.  It’s easy for him, but isn’t his effortlessness more a product of his intimate familiarity with Dodeca, his knowledge level (I just spent a week in the patented Tim Tow Java-coding-is-so-cool reality distortion field and let me say this – if you think you’re smart, you really need to spend some time with Tim because you – or is that me? –, umm, aren’t), and maybe, just maybe, isn’t there a soupçon of demo spin behind what he does?

I’m here to tell you I am no Geek Idol, nor am I particularly smart (note the comment about being around Tim), nor am I even remotely capable of spin.  I wish I were capable of the last trait as I would likely be a lot richer.  

Nope, you’ve got old honest Cameron to take you through a simple and quick tour of how to incorporate SQL drill through from your Essbase database today with a minimum of effort.  Do you need EIS or Studio to do drill through?  Nope.  The long-obsolete drill through package for Essbase?  Nope.  A programming talent the size of a 1965 Buick Electra 225?  Nope.  Just little old Sample.Basic and Dodeca.  

Make Sample.Basic more Basic

Long time readers, all three of you, of my blog know that My Very Favorite Essbase Database In The Whole Wide World is none other than Sample.Basic, which I shall henceforth refer to as MVFEDITWWW.  For those who care, it is pronounced “mfffeditwaa,” which I believe is akin to the initial sound made upon dropping a 14 pound sledgehammer on one’s foot.  Other, less family-friendly sounds can then occur – imagine away.  

My future plans

One day I shall discover a company called The Beverage Company, they will sell soda pop (Michigan influence bubbling up there) in a few oddly distributed states, and will have a remarkably limited view of their chart of accounts.  When this happy day occurs, I am going to be the most productive consultant ever.  Interested packaged goods IT managers can contact me care of this blog.

Back to reality

Love everlasting for Sample.Basic?  Yes, but regardless I’m going to take the shears to my beloved MVFEDITWWW and remove most of the Product dimension (but not clear out the upper level data) as that is what we are going to drill from.  A silly example as low level product information is already there in Essbase but hey, it’s a demo.

Product in MVFEDITWWW

Product in Sample.DrilThru

Note the absence of the Caffeinated, Intro Date, Ounces, and Pkg Type attribute dimensions – they cannot be used because the corresponding base members in the Product dimension no longer exist.

Base data

Here’s the data I extracted out of the database-that-shall-not-be-named:

I wanted each record to represent all dimensions with a data column at the end.  I could have run two Essbase report scripts, one for Actual, one for Budget, back to back by placing an explanation mark “!” between the two report scripts  but I am feeling all kinds of SQL so I used my child-like skills to do it in SQL Server and besides I wanted the member names “Actual” and “Budget” in the record.  Yes, you can likely write this better than I can and I invite your suggestions – I will update this post to show a better way.

Here’s the SQL I used to create the drill through table:
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
     WHERE TABLE_NAME = 'dDrilThru')
  DROP TABLE dDrilThru
GO
USE Blog_Dodeca
CREATE TABLE dDrilThru
    (
        Product nvarchar(80),
        Market nvarchar(80),
        Measure nvarchar(80),
        [Year] nvarchar(80),
        Scenario nvarchar(80),
        [Data] float
    )
INSERT INTO dDrilThru
SELECT
    Product,
    Market,
    LTRIM(Measure),
    [Year],
    'Actual' AS "Scenario",
    Actual AS "Data"
FROM
    dImportSampleBasic
WHERE Actual IS NOT NULL AND
    Actual <> 0
UNION
SELECT
    Product,
    Market,
    LTRIM(Measure),
    [Year],
    'Budget' AS "Scenario",
    Budget AS "Data"
FROM
    dImportSampleBasic
WHERE Budget IS NOT NULL AND
    Budget <> 0
ORDER BY
    Product, Market, LTRIM(Measure), [Year]

And that gives me data that looks like this:

Supporting tables

hProductLookup

I also set up a table called hProductLookup that defined the relationship between the now-shortened Product dimension and the level 0 Products in dDrilThru.  Yes, I could have used the leading three character member names but I wanted to take that dependency out.

hProductDescription

Oh, I suppose I could have put the description for the formerly level 0 Products into hProductLookup, but I also wanted to completely normalize (maybe overly normalize) the tables and also I was going to need a relational description of what were the the level 1 Products, so I jammed that all into hProductDescription.

 What does that mean?
The above table, dDrilThru, is now ready to be drilled into.  That wasn’t hard, was it?

Retrieving SQL in Dodeca

There are three components to a SQL retrieve in Dodeca:  a SQL connection (what server/database is Dodeca querying/inserting/updating/deleting), the actual SQL statement, and its application in a Dodeca report.  That’s it – it is all pretty straightforward and even a SQL idiot like yr. obdnt. srvnt. can figure it out.

SQL Connections

All that the SQL Connection defines (btw, I named mine Blog_Dodeca because I am imagination-challenged – you can call it anything you like) is the Dodeca name of the connection, the JDBC url to the database, and the SQL username/password.

SQL Passthrough Data Sets

I really like the name Applied OLAP came up with for the query – “SQL Passthrough Data Set”.  I guess it could have just been called “SQL Query” but you can do so much more than just a simple SELECT as we shall see.  


In any case, we name it, and then we define a query to go with the object.  That query will produce a DataTable that in turn will, when the View is defined, be related to a physical range in the target sheet.

Defining the query

Things get a wee bit more complicated here, but only a tiny bit.  

For the query to work, it needs:

1. A SQLConnectionID –the login to SQL defined in the previous step and selectable through a dropdown control
2. A DataTableName – a virtual table that receives the contents of the query results – name it whatever you like
3. SelectSQL –all four (Delete, Insert, Select, and Update) queries could be defined; in our case, we just want a SELECT statement

What does that query look like?

This is where Dodeca shows off one of its many ultra super powers.

Here’s a query that pulls all of the product information for product family 100.
SELECT
    H.ProductFamily AS 'Product',
    H.Product AS 'SKU',
    PD.Description AS 'SKU Desc',
    D.Market,
    D.Measure,
    D.[Year] AS 'Month',
    D.Scenario,
    D.Data
FROM
    dDrilThru D
INNER JOIN hProductLookup H ON
    D.Product = H.Product
INNER JOIN hProductDescription PD ON
    D.Product = PD.Product
INNER JOIN hMonthSort M ON
    D.[Year] = M.MonthDesc
WHERE H.ProductFamily = '100'

That returns a result set that looks like this:

But it does that for 10,704 rows.  That’s a little bit more than I will want to go through on a drill through sheet (just because there’s lots and lots and lots of data doesn’t mean that I want to view all of it at the same time).  Let’s put a bit more specificity around the query:
SELECT
    H.ProductFamily AS 'Product',
    H.Product AS 'SKU',
    PD.Description AS 'SKU Desc',
    D.Market,
    D.Measure,
    D.[Year] AS 'Month',
    D.Scenario,
    D.Data
FROM
    dDrilThru D
INNER JOIN hProductLookup H ON
    D.Product = H.Product
INNER JOIN hProductDescription PD ON
    D.Product = PD.Product
INNER JOIN hMonthSort M ON
    D.[Year] = M.MonthDesc
WHERE H.ProductFamily = '100'
    AND D.Market ='New York'
    AND D.Measure = 'Sales'
    AND D.Scenario = 'Actual'
ORDER BY H.Product, D.Market, D.Measure, D.Scenario, M.MonthNo

That query returns this:

12 records is a lot more like it.  I want detail, but not too much.  YMMV.

What’s the problem?

Well, one big problem with the query is that I’ve hardcoded the ProductFamily, Market, Measure, and Scenario dimensions.  That isn’t exactly going to lead to drill through, is it?  Unless of course I only ever queried 100, New York, Sales, and Actual and then why bother with drill through at all?

Tokens to the rescue

Nope, not the ones you (used to) use on the NYC subway.  I mean strings within the SQL statement that can be replaced with selected values from Essbase member selections.  Of course (Of course?  Of course I mean of course.) Dodeca already does this for the purposes of Essbase retrieves.  Here’s what they look like in the  Selectors editor:

To make the SQL statement pick up these tokens, all we need to do is change the hardcoded WHERE clause to one that is tokenized.  The query ends up looking like this:
SELECT
    H.ProductFamily AS 'Product',
    H.Product AS 'SKU',
    PD.Description AS 'SKU Desc',
    D.Market,
    D.Measure,
    D.[Year] AS 'Month',
    D.Scenario,
    D.Data
FROM
    dDrilThru D
INNER JOIN hProductLookup H ON
    D.Product = H.Product
INNER JOIN hProductDescription PD ON
    D.Product = PD.Product
INNER JOIN hMonthSort M ON
    D.[Year] = M.MonthDesc
WHERE H.ProductFamily = '[T.Product]'
    AND D.Market ='[T.Market]'
    AND D.Measure = '[T.Measures]'
    AND D.Scenario = '[T.Scenario]'
ORDER BY H.Product, D.Market, D.Measure, D.Scenario, M.MonthNo

When we set up the Dodeca View correctly, it will pass [T.Product], [T.Market], [T.Measures], and [T.Scenario] right to the query and return only the items selected in the Essbase selectors.  Sounds like drill through, doesn’t it?  

Setting up the query

Before Dodeca can retrieve anything to a SQL sheet, we need to define the data source (that’s done in via the SQL Connection already described) and then the query(ies) for that connection.  A SQL Passthrough Dataset is the place to define that.  Note that a dataset can have multiple queries – we are defining only one and it’s just a SELECT.

Here’s the definition of the dataset:

Click on the ellipses button to get the query window to pop up:

Define the SQLConnectionID via a dropdown control, the name of the virtual table to contain the results of the query (that is not the same as the name of the source table(s) and will be important in a bit), and the SelectSQL statement which is going to look awfully familiar:

That query needs to get tied to a base report that drives those tokens that in turn gets driven from an Essbase sheet.

Base SQL retrieve in Dodeca

Did you know that Dodeca is a really good SQL query and reporting tool?  Yep, it is pretty impressive, and easy.

SQL Setup in the View

Excel template

Setting up a SQL retrieve is very similar to the process for Essbase.  The main difference from an Excel template perspective is that the retrieve range is called DataRange.1 (it could actually be called Rutabaga, but DataRange.1 makes a lot more sense – the .1 is to help identify the SQL source as you can easily have multiple SQL source retrieves on a sheet) instead of Ess.Retrieve.Range.1.  

NB – It’s a good idea to make ALL range names sheet level instead of workbook.

You will notice that the columns in this retrieve range look an awful lot like the query you saw above.  That is not accidental.

SQL View properties

The SQL View is similar to the Essbase View but with a few minor differences, the most important being the DataSetRanges property.  The purpose behind a DataSetRange is to associate a SQL Passthrough DataSet with a named range on the retrieve sheet.

Click on the ellipsis button to define the DataSet Range.  

DataSet Range

Once in the editor, select the SQLPassthroughDataSetID that defines the connection and the query via a dropdown control.

Then define where the results of the query will be sent when the user builds the view by clicking on the ellipsis button.

DataTable Range

Define the name of the range that will receive the query results – that’s the DataSheetRangeName.

Also define the name of the DataTable – that virtual table name is defined in the Query definition and its contents gets passed to the DataSheetRangeName on retrieval.

Once all of this has been input, confirm your way out of the DataTable Range Editor and the DataSet Range Editor and Commit the View changes.

Run that SQL report

And that’s it!  Let’s retrieve some data and see what it looks like:

Note the selectors at the top – we can make selections (New York, 100, Sales, Actual) and get all of the data associated with those parameters.  Now it’s time to hook it up to Essbase and drill into this report.

Base Essbase retrieve in Dodeca

This is pretty simple stuff in Dodeca – the Essbase source sheet looks like this:


Do you see the missing token?  That’s right, [T.Product] isn’t on the sheet.  That’s because we’re going to grab it from Colas, Root Beer, Cream Soda (ugh, I never liked that stuff), and Fruit Soda (A few years ago I tried a Faygo Red Pop on a trip back to Michigan.  I LOVED it as a kid – now, I think I’d rather drink dishwashing detergent.  But I digress yet again.) on the drill through.


Once the above template is imported into Dodeca, a simple View needs to be set up:

When that view is retrieved in Dodeca – magic redux.  Note the dimension selectors at the top of the screen whose values replaced the tokens on the retrieve sheet.  Sort of, kind of, the same as the SQL retrieve sheet.

Drill on through to the other side

Yes, yes, you had to read 2,416 words of my idiotic blathering to get to what you really wanted to see.  Sorry, but I had to show world+dog all of the setup steps.  It gets very simple, and very fast from now on.

To define the data drill through from Essbase to SQL, go to the Essbase View’s Data DrillThrough settings block.  

Data Drillthrough Settings

DataCellDoubleClickMemberFilters

I set up a filter on the Measures dimension to prevent drill through on upper level members like Profit, Margin, etc. as there are no corresponding values in my SQL drill through source.  It’s really quite easy to put in and prevents drill through at those upper levels.

DataCellDoubleClickPolicy

This property defines how the drill through will act.  In this example, I wanted to open that SQL view I detailed above.  There are however lots of options as you can see.

DataCellDrillthroughMemberToTokenConversionRules

Dodeca doesn’t just have to pass the bottom level member as per this example.  This is really pretty interesting as it means the drill through could grab a bunch of related SQL data as well if desired.

DataDrillthroughSheetName

This sheet name only makes sense when the DataCellDoubleClickPolicy is set to AddSheetForDrillthrough.  As this example opens a separate View, it stays blank.

DataDrillthroughSheetNameMaxLength

The maximum length of a sheet name (that little tab at the bottom of an Excel workbook is 31 characters.  Dodeca can support longer sheet names (huzzah! as that 31 character limit is…limiting) but you the Dodeca developer (you are all going to be Dodeca developers, right?) have to figure out if your Dodeca drill through awesomeness is ever going to be exported to Excel or not.  If not, set this to zero (assuming you are using a new sheet) and it can be whatever length you like.  Again, I am using a separate View, so it doesn’t apply.

DataDrilltrhoughViewID

The name of the target View, i.e., Sample DrillThru SQL Results.  Note that the tokens used in Essbase are automatically passed to the SQL View based on the cell used for drill through.  Pretty damn cool is what I call that.

EnforceEssbaseFilterOnOpenViewForDrillthrough

If I had any Essbase security on this application, this setting would enforce Essbase security on the drill through.  In other words, if I didn’t have access to a data cell in Essbase, I would continue to not have it in the drill through sheet, and if I did, I would see it in the drill through sheet.  Of course since this is running off of my laptop, all of the above isn’t happening, but it’s nice to know that it’s there.

It’s demo time!

I am going to try something a little different with this blog post, and may rip it out if it doesn’t work.

If a picture is worth a thousand words, how many words is a movie worth?

For those not content to watch the drill through in video form, here’s a sample of how this works.

Good for Essbase, bad for the drill through

I am purposely drilling through from Margin.  Recall that I put a level zero filter on the Measures dimension.

So what does an invalid drill through do when I double-click on cell B6?

Nothing to see, folks

Look down in the lower left hand corner of the picture – Dodeca has forbidden the drill through, as specified.

A valid drill through

Now I will select Sales and double click on Qtr1 Colas.

Isn’t it beautiful?

Ta da, drill through Nice n’ Easy.

Is that it?

What, you want more?  Hire me if you want that much of my time.  

I think I’ve shown that you can drill from an Essbase database with very little work (ignore the length of this post – I wanted to show you every single step but this is one of those “It takes longer to explain than to do” kinds of things).


Drill through to a source, as per FDM?  Yep.


Drill through to a predefined table or series of tables as in Studio?  Yep.


Drill through to anything you want, with less work than the above two tools (and a bunch I haven’t mentioned)?  Yep.


Dodeca wins (is anyone surprised?) again.  I love this product.  Hmm, maybe I should see a doctor about that – I’m sure there’s a syndrome in the DSM that matches this somewhat unnatural, but completely understandable affection.  :)