The Planning Repository: HSP_ACCOUNT

// Brian Marshall // Hyperion Planning, Planning Repository

Not to sound like a broken record, but…a few disclaimers:

  • This is the fun stuff…that voids your warranty.  Not that you have to tell Oracle that you poke around here, but if you do, they will blame anything and everything that’s wrong on you for being in there.
  • The content here has been tested and validated against 11.1.2.3.500 and 11.1.2.4.
  • The content here has been tested and validated against Microsoft SQL Server and Oracle 11g.
  • The content here is based on the Vision sample application.
  • The content here is totally unsupported by Oracle (in case you missed the first bullet).

So it’s been a while since we took at look at the Planning Repository.  Today we’ll move past the HSP_MEMBER table (and associated tables) to look at the first of our built-in Planning dimensions:  HSP_ACCOUNT.  As the name indicates, the HSP_ACCOUNT table contains properties related to the account dimension in Planning.  Let’s take a look at the table structure:

Field NameSQL Server TypeOracle TypeDescription
ACCOUNT_IDintNUMBER(38,0)The OBJECT_ID of the member.
USE_445smallintNUMBER(38,0)If you application is set to use weekly distribution, this field will be populated when the Distribution checkbox is checked.
0=don't adjust values
1=445 spread
2=454 spread
3=544 spread
TIME_BALANCEsmallintNUMBER(38,0)Determines the Essbase time balance attribute.
0=none
1=first
2=last
3=average
SKIP_VALUEsmallintNUMBER(38,0)Determines the Essbase skip attribute.
0=none
1=skip missing values
2=skip zero values
3=skip missing and zero values
ACCOUNT_TYPEsmallintNUMBER(38,0)Determines the account type.
1=expense
2=revenue
3=asset
4=liability
5=equity
6=statistical
7=saved assumption
VARIANCE_REPsmallintNUMBER(38,0)Determines the Essbase variance reporting attribute.
1=expense
2=non-expense
CURRENCY_RATEsmallintNUMBER(38,0)If your application is set to use the built-in currency functionality, this determines which rate to use for conversion.
0=none
1=average
2=ending
3=historical
PLANNING_METHODsmallintNUMBER(38,0)No idea on this one.
USED_INsmallintNUMBER(38,0)The plan type usage for the member. This is also a bitmask that tells us which plan types are used in a single integer. This field also appears in the HSP_MEMBER table and appears to stay in sync.
DATA_TYPEsmallintNUMBER(38,0)This field is also found in the HSP_MEMBER table. It is populated in HSP_MEMBER but is always NULL in this table.
SRC_PLAN_TYPEintNUMBER(38,0)"Indicates the source of data value for account." - Direct from Oracle.
SUB_ACCOUNT_TYPEintNUMBER(38,0)"Identifies the type of account and how its useds" - Direct from Oracle.

Overall, this table is pretty simple.  We could probably stop there, but hey, how about a quick query:

SQL
54 lines
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
SELECT
     po.OBJECT_NAME AS Parent
    ,o.OBJECT_NAME AS Account
    ,CASE a.USE_445
        WHEN 0 THEN 'None'
        WHEN 1 THEN '445'
        WHEN 2 THEN '454'
        WHEN 3 THEN '544'
        ELSE ''
     END AS WeeklySpread
    ,CASE a.TIME_BALANCE
        WHEN 0 THEN 'None'
        WHEN 1 THEN 'First'
        WHEN 2 THEN 'Last'
        WHEN 3 THEN 'Average'
        ELSE ''
     END AS TimeBalance
    ,CASE a.SKIP_VALUE
        WHEN 0 THEN 'None'
        WHEN 1 THEN 'Skip Missing'
        WHEN 2 THEN 'Skip Zeros'
        WHEN 3 THEN 'Skip Missing and Zeros'
        ELSE ''
     END AS SkipValue
    ,CASE a.ACCOUNT_TYPE
        WHEN 1 THEN 'Expense'
        WHEN 2 THEN 'Revenue'
        WHEN 3 THEN 'Asset'
        WHEN 4 THEN 'Liability'
        WHEN 5 THEN 'Equity'
        WHEN 6 THEN 'Statistical'
        WHEN 7 THEN 'Saved Assumption'
        ELSE ''
     END AS AccountType
    ,CASE a.VARIANCE_REP
        WHEN 1 THEN 'Expense'
        WHEN 2 THEN 'Non-expense'
        ELSE ''
     END AS VarianceReporting
    ,CASE a.CURRENCY_RATE
        WHEN 0 THEN 'None'
        WHEN 1 THEN 'Average'
        WHEN 2 THEN 'Ending'
        WHEN 3 THEN 'Historical'
        ELSE ''
     END AS CurrencyRate
FROM 
    HSP_ACCOUNT a
INNER JOIN
    HSP_OBJECT o ON a.ACCOUNT_ID = o.OBJECT_ID
INNER JOIN
    HSP_OBJECT po ON o.PARENT_ID = po.OBJECT_ID
ORDER BY 
    o.POSITION
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

This is basically a decode of all of the different fields.  Here are the results:

SQL Server:

HSP_ACCOUNT_MOracle:

HSP_ACCOUNT_O

We’ll be keeping it simple for the next few posts, focusing on the built-in dimensions that we have left.  That’s all for now!

1 Response

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.