Stupid Planning queries #7 -- User Variables

Introduction

You use User Variables, right?  That’s the functionality typically (only?) used in Planning forms to allow the user to select the member he wants to drive the form.  I use these when dimensional security doesn’t work or when the form gets too large.

Set and forget

It’s easy to set them by going to File->Preferences->Planning->User Variable Options as shown below.

How do you use them in forms?

They look just like Essbase Substitution Variables, right down to the “&” in front of the User Variable name.  

NB – I like to stick a “uv” in front of the name to differentiate them from Substitution Variables, so instead of &CompareScenario it would be &uvCompareScenario.  This isn’t a big deal when you’re building the form, but aids in identifying what’s what later on.  Or when you bounce from client to client, and app to app, and barely know what state you’re in.  The glamor of consulting.  :)

In any case, as this is the Planning Sample Application, I’m not changing it.  But you did get a Cameron Good Practice suggestion there for free.  

So how do I know who has what?

There’s really no good way to know in Planning what’s been defined by user except by asking said user.  As that could be a trifle exhausting (and inaccurate) in anything other than a teensy-weensy application, SQL comes to the rescue again.

Standard disclaimer

As always with these queries, they are 100% unsupported by Oracle and there’s a tremendous chance that I’ve gotten them wrong, so test, test, test and remember that you are hacking the tables and if anything blows up you are completely on your own.  Got it?  Good, let’s begin.

User variables query

--User variables
--    Purpose:    Get the User Variables in a Planning application
--    Modified:    30 October 2011, first write
--    Notes:        This is a simple one, isn't it?
SELECT
    O.OBJECT_NAME AS 'User',
    U.VARIABLE_NAME AS 'Variable',
    O2.OBJECT_NAME AS 'Member',
   -- Use SQL Subquery to get aliases.
   -- NB -- The overall SELECT from HSP_MEMBER ensures that members with
   -- and without an alias are selected.
   -- ISNULL puts in zero length string in place of NULL
   ISNULL((SELECT OA.OBJECT_NAME
       FROM HSP_ALIAS A
       INNER JOIN HSP_OBJECT OA
       ON A.MEMBER_ID = O2.OBJECT_ID AND
       OA.OBJECT_ID = A.ALIAS_ID), '') AS 'Alias'
FROM HSP_USER_VARIABLE_VALUE V
LEFT OUTER JOIN HSP_OBJECT O
    ON V.USER_ID = O.OBJECT_ID
LEFT OUTER JOIN HSP_OBJECT O2
    ON V.MEMBER_ID = O2.OBJECT_ID
LEFT OUTER JOIN HSP_USER_VARIABLE U
    ON V.VARIABLE_ID = U.VARIABLE_ID
ORDER BY 1, 2

And the output

User	Variable	Member	Alias
Biff	Allocation Expense	330000	Minority Interest Income
Biff	Allocation Quarter	Q1
Biff	CompareScenario	Forecast
Biff	CompareVersion	Working
Biff	Current Version	Working
Biff	CurrentScenario	Forecast
Biff	My Region	E01_0	North America Corporate
Biff	My Segment	BAS	Bookshelf Audio System
Biff	Revenue Measure	411100	Operating Revenue
hypadmin	Allocation Expense	312100	Interest Income
hypadmin	Allocation Quarter	Q4
hypadmin	CompareScenario	Plan
hypadmin	CompareVersion	Final
hypadmin	Current Version	Working
hypadmin	CurrentScenario	Forecast
hypadmin	My Region	E01_101_1110	MA
hypadmin	My Segment	Seg01	Electronics
hypadmin	Revenue Measure	400000	Gross Profit

 

Conclusion

Yet Another Planning Query (YAPQ), pronounced Yap Que, hits the dust.  I know there are other people there doing these.  I wonder why I never hear from them.  Am I doing something akin to an arch-villain exposing the inner works of the Guild of Calamitous Intent?  This stuff is so easy, even a Planning consultant can do it?  No matter, I forge onwards.

AWS tricks #1 -- How to get rid of terminated volumes

Introduction

I spent this weekend figuring out how to install ODI 11.1.1.5 using SQL Server 2008 not Oracle 11g as the database back end and I now know two things:

1. I really ought to be doing this on Oracle instead of SQL Server as all of the blogs seem to cover the settings for Oracle.  I find it interesting that the 10.1.3.x blog posts seemed to be oriented around SQL Server and 11.1.1.x around Oracle.  However, as my environment was SQL Server, not Oracle, I was stuck with figuring it out.
2. I am not very good at installations.   This may not come as a shock to anyone, especially me.  :)

In an upcoming blog post, once I am convinced that I have everything working tickety-boo, I will document the settings and required downloads to make ODI and ODI Studio work in a 64-bit Windows server context.

Putting aside the additional grey hairs and general agita installing ODI caused me, this exercise got me back into the Amazon Web Services (AWS) cloud and that was a life-saver.  Just like a virtual machine, I could spin up as many instances of the server as I needed to blunder my way through the install and could snapshot it when I had something halfway working.  Unlike a virtual machine running on my laptop, I had 17+ gigabytes of RAM, super-fast networking (you haven’t lived till you switch from DSL to whatever AWS runs – I was getting 39 megabit per second downloads from Oracle’s website), and real server power.  

However, as I was looking at my AWS account, I saw that I had a bunch of AMIs, snapshots, and volumes that I couldn’t really account for.  Oh, I created them all right, but I hadn’t used them in ages and I was getting billed each month for them.  A mass culling ensued but it occurred to me that this is  a good time for some basic definitions.

AWS definitions

One of the (many) things that confuse me about AWS is the concept of “volumes”.  Simply put, a volume is a hard drive.  

Then there are these things called “snapshots”.  Snapshots in AWS’ Elastic Block Storage (EBS) world are just what they sound like – snapshots of volumes that you can restore back to at any time.

It is very important to note that the hard drive of an Amazon Machine Image (AMI) (a predefined server you can start) is ephemeral, i.e., your laptop’s hard drive this is not – when the instance is killed, the hard drive is gone.  It’s very easy to blow away all of your work if you don’t understand how AWS treats volumes.

This post will cover what a volume is, and how, when, and why you would get rid of a volume (you get charged for each one) as they have a way of piling up when least expected.

What’s ephemeral, and what’s not

A really good explanation of AWS’ drive ephemerality can be found here:  http://shlomoswidler.com/2009/07/ec2-instance-life-cycle.html.  If you cannot be bothered to jump to a well written blog, the gist is that EBS-backed instances have volumes that persist as long as the instance is not terminated.  

And how does that affect you?

Well, when you stop an instance (Stopping an instance is akin to powering down a server) the volume is still extant.  It makes sense (to me at least) that a volume hangs around when a server instance is stopped.

When you terminate (Terminating an instance is like you shut down the server, ripped it out of your data center, and naively took it to a recycling center which then had the hardware shipped tout de suite to Liberia for completely unsafe disposal.) an instance, there is a chance the volume will still hang around.  But why is it still there when an instance is terminated?  Doesn’t that destruction of the instance sort of imply that the hard drive volume should be trashed as well?

Remember, if you started your stopped instance back up, whatever changes you made to your boot drive are still there.  

But what about the volume that is the product of a terminated instance?  If you reattached it to a new instance, are your changes there?  Nope, all of the changes you made are gone.

An example

Let’s pretend that you fired up John Booth’s EPM 11.1.2.1 AMI (go to http://www.metavero.com), did cool stuff, and then terminated the instance.  Guess what – that C: drive is still around, and you’re getting billed for it.

Not deleted till it’s detached

Here’s what the web console to my AWS account looks like with four volumes attached to stopped instances and one 100 gigabyte volume from a terminated server instance.  
What is that volume doing there?  Did I really terminate that instance?  

As I wrote, I’m being charged (not very much, but still) for storing that 100 gigabytes.  I want to make that unloved hard drive go away.

It’s dead, Jim

That instance is deader than dead.  That drive volume is in AWS purgatory.

What’s going on?

That volume is going to hang around till you delete it.

Why?  A little searching of the tells us that the AMI must be set up to delete the volume on termination.  If it’s not, then the volume must be manually deleted.

NB – A future blog post on launching an AMI from a command prompt will explain how to launch an AMI with that parameter; if the AMI has been set up to not delete the volume on termination, you must manually delete the volume as shown below.

How to delete a volume

Luckily, this is an easy process.  Simply right click on the available volume and select “Delete Volume”.

You get one more chance to change your mind.

AWS will take a short time to delete the volume:

If you get impatient (it can take a while to delete a volume and the bigger the virtual drive, the slower the delete), you can click on the Refresh button to see the current status.

Until the volume is finally gone.

Conclusion

If the AMI is not set up to delete the volume on termination, you must do so manually, or modify the AMI via the command line interface to delete upon termination.

Set up your AMIs to delete volumes on termination or remember to check and delete available volumes – remember, you are being charged to store drives you no longer use.

Stupid Planning queries #6 -- Security reports

Introduction

Ever wanted to get a security report out of Planning?  You know, who has access to what.  And maybe you’d like to be able to tell the difference between group (You are using groups to assign security, right?  Well, maybe it’s on your list.) and individual assignments.  And of course you need to know that for members, forms, folders, etc.

Of course you simply use Planning’s great security reporting features, right?  Hmm, that seems to be a bit difficult, as there aren’t any in-built security reports.  Why oh why oh why oh why we shall likely never know.  No one at Oracle has told me, but I reckon it’s because they want us to hone our SQL skills – after all, they do sort of have this product they seem to sell a few copies of here and there.  

All kidding aside, if you’ve been following this series, you can see how powerful being able to dive into the back-end tables can be.  It is a good idea to get your feet wet in these kinds of queries – I find all sorts of uses for them as I go from client to client.  If you are good at SQL, you’ve probably noted that I’m not the greatest in the world at this stuff.  Within the context of Planning, that’s okay because the data set is so small.  This is an Essbase (okay, or Planning) hacker’s dream – do useful stuff, learn cool things, don’t blow things up in the process.  I hope it’s your dream as well.  No?  You want to be King-Emperor of the world?  Sorry, wrong blog.  Go search for nut+megalomaniac+world domination.

Standard disclaimer

As always with these queries, they are 100% unsupported by Oracle and there’s a tremendous chance that I’ve gotten them wrong, so test, test, test and remember that you are hacking the tables and if anything blows up you are completely on your own.  Got it?  Good, let’s begin.

Credit where it’s due

One last note – these queries originated with my good buddies Dave Farnsworth and Natalie Delemar.  What?  You think I write all of this stuff?  Sometimes.  Otherwise I modify/steal/improve/mangle code just like everyone else.

Security by object

Let’s pull all of the security from my sample Planning application.  No, there’s not a lot of security here – apply this to your real application and you’ll see quite a bit more.

First query

--    Purpose:    Security report by object
--    Modified:   14 October 2011
--    Notes:      Security by object
--      
SELECT
    O.OBJECT_NAME AS 'Object Name',
    -- Subquery to get user or group name
    (SELECT OA.OBJECT_NAME
        FROM HSP_OBJECT OA
        WHERE OA.OBJECT_ID = AC.USER_ID) AS 'User/Group Name',
    CASE
    -- Subquery to get user or group type
        (SELECT OA.OBJECT_TYPE
            FROM HSP_OBJECT OA
        WHERE OA.OBJECT_ID = AC.USER_ID)
        WHEN 5 THEN 'User'
        WHEN 6 THEN 'Group'
    END AS 'Security Type',
    CASE AC.ACCESS_MODE
        WHEN 1 THEN 'Read'
        WHEN 3 THEN 'Write'
        WHEN -1 THEN 'Deny'
    END AS 'Read/Write',
    CASE AC.FLAGS
        WHEN 0 THEN 'Member'
        WHEN 5 THEN 'Children'
        WHEN 6 THEN 'IChildren'
        WHEN 8 THEN 'Descendants'
        WHEN 9 THEN 'IDescendants'
        END AS 'Hierarchy function',
    OT.TYPE_NAME AS 'Object Type'
FROM HSP_OBJECT O
INNER JOIN HSP_ACCESS_CONTROL AC
    ON O.OBJECT_ID = AC.OBJECT_ID
INNER JOIN HSP_OBJECT_TYPE OT
    ON O.OBJECT_TYPE = OT.OBJECT_TYPE
-- Sort on Object name, object type
ORDER BY 6, 1

First output

Object Name	User/Group Name	Security Type	Read/Write	Hierarchy function	Object type
IncomeStatement	CMSamp	Group	Read	Member	Account
Function	CMSamp	Group	Read	IDescendants	Entity
TotalGeography	CMSamp	Group	Write	IDescendants	Entity
Financials	CMSamp	Group	Read	IDescendants	Folder
Allocation - Expense	Hypadmin	User	Write	Member	Form
Allocation - Facilities Expense	Hypadmin	User	Write	Member	Form
Allocation - IT	Hypadmin	User	Write	Member	Form
Allocation - Marketing	Hypadmin	User	Write	Member	Form
Allocation - Segment	Hypadmin	User	Write	Member	Form
Balance Sheet	Hypadmin	User	Write	Member	Form
Cash Flow	Hypadmin	User	Write	Member	Form
Financials Summary	Hypadmin	User	Write	Member	Form
Income Statement	Hypadmin	User	Write	Member	Form
Plan Department Expenses	Hypadmin	User	Write	Member	Form
Plan Facilities Expenses	Hypadmin	User	Write	Member	Form
Plan Operating Expenses	Hypadmin	User	Write	Member	Form
Plan Revenue - All Years	Hypadmin	User	Write	Member	Form
Plan Revenue - Assumptions	Hypadmin	User	Write	Member	Form
Plan Revenue - COS	Hypadmin	User	Write	Member	Form
Plan Revenue - Detail by Year	Hypadmin	User	Write	Member	Form
Review G&A Expenses	Hypadmin	User	Write	Member	Form

Security by group

You see the CMSamp group above.  What’s the problem with this?  How do I know that a given user actually has access to IncomeStatement member in the Account dimension?  Try the below query.

Second query

--    Purpose:    Security report by object, group, and user
--    Modified:   14 October 2011
--    Notes:      Remove comment at penultimate line to change object type
--                See HSP_OBJECT_YPE for all object types.
--      
SELECT
    O.OBJECT_NAME AS 'Object Name',
    O3.OBJECT_NAME AS 'Group Name',
    O2.OBJECT_NAME AS 'User Name',
    (CASE AC.ACCESS_MODE
        WHEN 1 THEN 'Read'
        WHEN 3 THEN 'Write'
        WHEN -1 THEN 'Deny'
    END) AS 'Read/Write',
    (CASE AC.FLAGS
        WHEN 0 THEN 'Member'
        WHEN 5 THEN 'Children'
        WHEN 6 THEN 'IChildren'
        WHEN 8 THEN 'Descendants'
        WHEN 9 THEN 'IDescendants'
    END) AS 'Hierarchy function',
    OT.TYPE_NAME AS 'Object Type'
FROM
    HSP_OBJECT O, HSP_ACCESS_CONTROL AC,
    HSP_OBJECT_TYPE OT, HSP_USERSINGROUP UG,
    HSP_USERS U, HSP_OBJECT O2, HSP_OBJECT O3,
    HSP_OBJECT_TYPE O4
WHERE O.OBJECT_ID = AC.OBJECT_ID AND

O.OBJECT_TYPE = OT.OBJECT_TYPE AND

O3.OBJECT_TYPE=O4.OBJECT_TYPE AND

O3.OBJECT_ID = UG.GROUP_ID AND

UG.USER_ID=U.USER_ID AND

U.USER_ID=O2.OBJECT_ID AND

--OT.TYPE_NAME like '%Folder%' AND

AC.USER_ID = UG.GROUP_ID

Second output

Object Name	Group name	User name	Read/Write	Hierarchy function	Object type
Financials	CMSamp	Biff	Read	IDescendants	Folder
Financials	CMSamp	CallMeTex	Read	IDescendants	Folder
Financials	CMSamp	Test	Read	IDescendants	Folder
IncomeStatement	CMSamp	Biff	Read	Member	Account
IncomeStatement	CMSamp	CallMeTex	Read	Member	Account
IncomeStatement	CMSamp	Test	Read	Member	Account
Function	CMSamp	Biff	Read	IDescendants	Entity
TotalGeography	CMSamp	Biff	Write	IDescendants	Entity
Function	CMSamp	CallMeTex	Read	IDescendants	Entity
TotalGeography	CMSamp	CallMeTex	Write	IDescendants	Entity
Function	CMSamp	Test	Read	IDescendants	Entity
TotalGeography	CMSamp	Test	Write	IDescendants	Entity

Conclusion

Ain’t the above a kick in the head?  Sadly, Dino never sang an ode to SQL queries.  Why?

Putting aside insane digressions, we now have (thanks, Dave and Natalie) two pretty useful security reports out of Planning.  Go forth and amaze other Planning administrators with your back-end SQL hacking skills.

A coda

NB – If you feel the burning need to share some cool snippet of code (it doesn’t need to be Planning) with world+dog, feel free to drop me a line.  As always, you will get all credit, attribution, etc.