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:
- A SQLConnectionID –the login to SQL defined in the previous step and selectable through a dropdown control
- A DataTableName – a virtual table that receives the contents of the query results – name it whatever you like
- 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. :)
