The best member selectors you’ve ever seen #2A
We’re back to the best front end tool, ever, ever, ever. Why the delay? The most awesome Oracle EPM conference ever, semi-interesting stuff such as I changed companies (back on my own, and loving it) and laptops, 11.1.2 installation struggles and other lame excuses.
Who cares about excuses, it’s time for some pretty cool stuff in Dodeca. As it’s been a while, so long, in fact, that Applied OLAP came out with another release since I last wrote, and some of the SQL functionality has changed, this post will highlight the new way to do SQL and a really cool piece of built-in Dodeca functionality. The really cool and semi-complicated stuff will go in the next and (hopefully) final post on member selectors where I’ll cover Workbook Scripts.
I am trying, albeit somewhat unsuccessfully as I review this post, trying to make these beasts a little smaller. If you think they take a long time to read, imagine how long they take to write.
What are we trying to do?
The next subject of combining Essbase, Dodeca metadata, and SQL to build report and user specific METAREAD filters without ever touching Essbase.sec, gets a little, mind, just a little, involved and I want to keep you focused...
To reiterate, Dodeca is going to produce user and report specific METAREAD filters using a simple SQL table, the Essbase username, and the name of the report. In other words, depending on who you are, and what report you’re in, you’ll see different views of the same dimension.
Esoteric? Not at all – think about the times you’ve wanted to limit, say a Planning form to just a few members for different people but wanted the dimension wide open on other forms. How would you do that? The best I can think of is multiple copies of the same form with the dimension tied to user security and limited members on the form itself. This would necessitate multiple copies of the form, form security (can’t have CallMeTex see Biff’s metadata and/or form and vice versa), and a maintenance headache. And that’s just one form.
We’re going to do this in a decidedly simpler (and may I add, significantly cooler) fashion.
Remember, in this post, we’re just going to essentially duplicate a METAREAD using the username as that is built into Dodeca. While this is cool, there is better stuff to come.
METAREADs by Username
This is so easy to do that, I actually forgot that I didn’t show you how to do this. It’s all built into Dodeca so I guess you could call this Yet Another Cool Dodeca Feature For Free (YACDFFF or yak duff – hmm, that doesn’t sound appetizing but I promise this is good stuff).
I’ve already reviewed how to create a SQL Passthrough DataSet in Dodeca. As this is a new laptop (VM actually) and a new release of Dodeca we’ll do a fast review. For the sake of speed/my sanity/my sleep, I am not going to go through every step of creating this side of the SQL as it all pretty adequately covered in that previous post.
A simple SQL table
Note that the Report field is NULL – we’re going to ignore it for now; it will come into play in the next post.
A simple SQL connection
These are now defined in a separate menu selection instead of being buried in the SQL Passthrough DataSet. This allows you to reuse the data connection instead of having to define it multiple times and maintain it multiple times. Do you get the idea that Applied OLAP thinks about their product?
Go to the Admin menu and select SQL Connections.
And here it is:
Notice how much simpler this approach is than the way it was in Dodeca 4.x as shown below:
Dodeca only gets better.
Backwards compatibility
If you want to (I can’t imagine why you would except if you have legacy SQL Passthrough DataSets) you can still define the connection in the DataSet itself as shown in the red box:
A simple SQL Passthrough DataSet
This works the same way as Dodeca 4.x.
The SQL DataSet
And here it is.
Query properties
Now click on the query ellipses to see the more details behind the query. There are specific SQL properties (queries) for the four actions: DELETE, INSERT, SELECT, and UPDATE. Are you getting the idea that Dodeca speaks SQL pretty well? It does.
The query
And finally, click on that last ellipses button to see the actual query. Here it is in all of its glory.
That bit highlighted in red is very important. Recall that there is no row in the EssbaseUser column that has a value of USERNAME. Why hardcode that into a query, especially when the desire is drive selections by Essbase username? Here’s a hint, that isn’t a hardcoded value, even though it looks like one.
Simple Selector List
Now go to the Selector List to change what looks like a hardcoded value to a parameter driven one.
EssbaseUserNameVariable
Dodeca kindly keeps track of who is logged in, and can pass that information to the SQL Passthrough DataSet. When it does that, it replaces whatever the matching string is in the SQL statement.
A gotcha
Watch out, I originally created a field called Username.
This:
SELECT Market from hMarket WHERE Username = 'USERNAME'
Became this:
SELECT Market from hMarket WHERE hypadmin = 'hypadmin'
Dodeca treats the whole string as available for substitution. That was a bit of a head scratcher till I sussed it out.
I annoyed Tim with this, and he helpfully pointed out that changing the token to something like %%USERNAME%% to avoid this issue.
Magic ensues
Let’s log in as Biff, our effete East coaster who whines about the price of arugula at his yuppie market.
And what does our preppy friend see?
Now we’ll log in as CallMeTex, a rugged individualist from somewhere west of the Pecos.
And is the treeview different? Oh yes indeed-y.
A TINCR moment
Think about what you’ve just seen: you have driven, by username, what dimensionality shows up in Dodeca. This is essentially duplicating a METAREAD filter. Big deal you say.
Ah, but remember:
There.
Is.
No.
Code.
Required.
Unless you count a SQL statement as code that is so easy that even I could write it.
Dodeca did the rest.
Next time
As I wrote in the introduction, we’re going to dive into Workbook Scripts to grab the report name and then tie the username to the report to come up with those report/username METAREADs that aren’t available in any other tool.
Hacking Essbase indeed.
No comments:
Post a Comment