Want to know The Truth About CPM?

30 October 2011

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

UserVariableMemberAlias
BiffAllocation Expense330000Minority Interest Income
BiffAllocation QuarterQ1
BiffCompareScenarioForecast
BiffCompareVersionWorking
BiffCurrent VersionWorking
BiffCurrentScenarioForecast
BiffMy RegionE01_0North America Corporate
BiffMy SegmentBASBookshelf Audio System
BiffRevenue Measure411100Operating Revenue
hypadminAllocation Expense312100Interest Income
hypadminAllocation QuarterQ4
hypadminCompareScenarioPlan
hypadminCompareVersionFinal
hypadminCurrent VersionWorking
hypadminCurrentScenarioForecast
hypadminMy RegionE01_101_1110MA
hypadminMy SegmentSeg01Electronics
hypadminRevenue Measure400000Gross 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.


1 comment:

Daphne Vasu said...

Can we update the oracle table with the user variables listed? The reason I am asking is, I deleted the dimension members and the user variables went hay along with that. How do I get them back? I was thinking - do an export
before deleting the members and then importing back the data to tables...