The Parallel Cubiverse
Posted by Michael Bowen on Fri, 04/22/2011 - 02:53
This little piece of work will give you all of the level 0 members of a parent child table no matter what level you pass in as your parameter. In this case, ‘USA’. Cool, huh? Now embed that into a subselect and you have one leg of your multidimensional context. The following code will do it with a two dimensional context model.
select
dt.field_one, dt.field_two, dt.geo, dt.product, dt.measure
from
drill_through_detail_fact dt
join (select
join (select
Are you starting to get the picture? Well we did it with 13 dimensional contexts, which is why I have no fingernails and a new prescription for Xanax. Now if you’re mind isn’t completely bent at this point you have probably noticed a couple things. The first is that I actually am using a parent child construction for my drill through. So that’s kinda special. The second is that I’m not using the $$Hierarchy-COLUMN$$ parameter. There’s no need for it. Hint. Don’t hit the ‘validate’ button on the SQL definition dialog box in Studio, it will crank for billable fractions of an hour looking for the word ‘column’. The other is a bit more subtle. What if the member parameter passed in from Smartview is not a parent?
join (select
a.child acct
from
dim_acct a
start with parent = '$$ACCT$$'
connect by prior child = parent
UNION
select
a.child acct
from
dim_acct a
where a.child = '$$ACCT$$'
)
on dt.acct_cd = acct
That’s more like it. One of the two selects within the subselect is going to bring back data.
But wait, there’s more. You will note that it just might happen to be that your application uses aliases. That means that the rows in your your dim_prod and dim_geo tables will need to be multiplied for each alias set you use.
After all of these subtle but heartbreaking realizations, we ended up making drill through logic tables separate and distinct from the dimension tables we used to build our actual dimension hierarchies. With enough time and SQL, all things are possible.
There are actually more tricks that we had to employ in order to make our parallel cubiverse work, but those are more specific to our particular application and distract from the bigger picture. The bigger picture is that we now have an ASO Essbase cube on which you can execute drill through reports at any level, provided you have the patience to wait out the query. Two ways of getting at the same data. Parallel.
Speaking of timeouts, there’s one more little trick that’s absolutely necessary aside from the row governor, and that is the following .reg file that adjusts the TCP/IP session parameters for your Windows machine.
Windows Registry Editor Version 5.00
[HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings]
"ReceiveTimeout"=dword:00dbba00
"KeepAliveTimeout"=dword:00180000
"ServerInfoTimeout"=dword:00180000
Using Essbase Studio to build two applications at once. Drill through becomes drill across when you use parent child constructions.
So what I have been doing over the past several months is an introduction to an idea that I've never quite seen before, and although we staggered into it, now that it's mostly done I'm seeing some greater beauty in it.
Imagine if you will, that you are rolling out a single reporting system to hundreds of users who have never used Essbase before. What is the best way to help them understand multidimensionality when all they have ever done is You build two systems instead of one and link them together via Essbase Studio. Huh? What? Yes. You build an ASO cube with some ungodly number of dimensions and a data staging area to support it, and at the same time you build a detailed fact table for querying the same data as if Essbase didn't exist. You use Smartview to look at the ASO cube and to drill through reports of the same data with a twist. The drill through 'reports' are built like a parallel general query facility that just happens to use Essbase as a jump off point. You build drill through reports with contextual links from every dimension and allow people to drill down to detail from levels above level zero.
Above level zero? Yes, in fact at every level. In other words, you build two applications, one with aggregations and one without.
The better way to build such an application is as a straight detail reporting application with n dimensional filters and then build the Essbase application later, but we did it the hard way by building the Essbase application first. As you might imagine, reconciling two applications can be a little bit difficult when it comes to UAT time. But if you have a really good ETL guy to work with, it can be done.
The essential trick of building the parallel cubiverse is found in the custom SQL that you write into the drill through definitions for Essbase Studio. Here's what you need.
The first thing you need, obviously, are dimension tables themselves, and of course there are three ways to build dimensions as we all know.You can use parent child, levels or generations. For our case we decided to use parent child and generations. My first bold claim was to be that I would build drill through in Essbase Studio 11.1.1.3 using parent child which, according to Oracle’s documentation, is not supported and presumably cannot be accomplished. But I hedged that bet and kept both sets of metadata. In our case we sourced these with EPMA, thereby defying another law of Essbase Studio 11.1.1.3 which says that drill through against EPMA is not allowed. Well, we just used one degree of separation by automating a rather interesting interface between EPMA, LCM, Informatica and Peoplesoft. I'd like to tell you how that works another day, like the day I figure out how it actually works. Besides, if I told you today, your neurons would collapse.
The second thing you need is a special device to link your high level drill throughs to your low level detail. The device is built right into PL/SQL. It's the Connect by clause.
select
g.child
from
dim_geo g
start with parent = 'USA'
connect by prior child = parent
This little piece of work will give you all of the level 0 members of a parent child table no matter what level you pass in as your parameter. In this case, ‘USA’. Cool, huh? Now embed that into a subselect and you have one leg of your multidimensional context. The following code will do it with a two dimensional context model.
select
dt.field_one, dt.field_two, dt.geo, dt.product, dt.measure
from
drill_through_detail_fact dt
join (select
g.child
from
dim_geo g
start with parent = $$GeoHierarchy-VALUE$$
connect by prior child = parent)
on (g.child = dt.geo)join (select
p.child
from
dim_prod p
start with parent = $$ProdHierarchy-VALUE$$
connect by prior child = parent)
on (p.child = dt.geo)Are you starting to get the picture? Well we did it with 13 dimensional contexts, which is why I have no fingernails and a new prescription for Xanax. Now if you’re mind isn’t completely bent at this point you have probably noticed a couple things. The first is that I actually am using a parent child construction for my drill through. So that’s kinda special. The second is that I’m not using the $$Hierarchy-COLUMN$$ parameter. There’s no need for it. Hint. Don’t hit the ‘validate’ button on the SQL definition dialog box in Studio, it will crank for billable fractions of an hour looking for the word ‘column’. The other is a bit more subtle. What if the member parameter passed in from Smartview is not a parent?
join (select
a.child acct
from
dim_acct a
start with parent = '$$ACCT$$'
connect by prior child = parent
UNION
select
a.child acct
from
dim_acct a
where a.child = '$$ACCT$$'
)
on dt.acct_cd = acct
That’s more like it. One of the two selects within the subselect is going to bring back data.
But wait, there’s more. You will note that it just might happen to be that your application uses aliases. That means that the rows in your your dim_prod and dim_geo tables will need to be multiplied for each alias set you use.
After all of these subtle but heartbreaking realizations, we ended up making drill through logic tables separate and distinct from the dimension tables we used to build our actual dimension hierarchies. With enough time and SQL, all things are possible.
There are actually more tricks that we had to employ in order to make our parallel cubiverse work, but those are more specific to our particular application and distract from the bigger picture. The bigger picture is that we now have an ASO Essbase cube on which you can execute drill through reports at any level, provided you have the patience to wait out the query. Two ways of getting at the same data. Parallel.
Speaking of timeouts, there’s one more little trick that’s absolutely necessary aside from the row governor, and that is the following .reg file that adjusts the TCP/IP session parameters for your Windows machine.
Windows Registry Editor Version 5.00
[HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings]
"ReceiveTimeout"=dword:00dbba00
"KeepAliveTimeout"=dword:00180000
"ServerInfoTimeout"=dword:00180000
Recent blog posts
- Starting From Scratch
- HowTo: Use the Google Authenticator as a virtual MFA Device on the Amazon AWS Console
- More Cloud, More Chef
- The Parallel Cubiverse
- Integrating AWS CloudFormation and Chef
- We Are Hiring! Oracle Hyperion EPM Ninjas
- Does SaaS BI Save Money? Questions you need to ask.
- 2010 Mid-Year Full 360 Update!
- The Agile Data Warehouse™ Trademark Controversy
- Use Cases for the Agile Data Warehouse
Syndicate
Comments
Post new comment