Hyperion Planning Archives

The Planning Repository: HSP_MEMBER (Part 6 – Putting It All Together)

September 21, 2015 // Brian Marshall // Hyperion Planning, Planning Repository Tags: Hyperion Planning, Planning Repository No Responses

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).

We’ve finally made it to the last part in our series on the HSP_MEMBER table (and all of the many associated tables)! In this post, we’ll take everything that we’ve learned and combine it to get a full extract of a single custom dimension for a single plan type. In our SQL Server query, this is a pretty straight forward operation. We simply combine the queries from the prior five parts of this series.

SQL Server:

SQLServer

155 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
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
SELECT
    o.OBJECT_NAME AS MEMBER_NAME
    ,op.OBJECT_NAME AS PARENT_NAME
    ,a1.ALIAS_NAME AS ALIAS_DEFAULT
    ,a2.ALIAS_NAME AS ALIAS_ENGLISH
    ,REPLACE(REPLACE(REPLACE((
        SELECT
            ou.UDA_VALUE AS UDA
        FROM 
            HSP_MEMBER_TO_UDA u
        INNER JOIN
            HSP_UDA ou ON ou.UDA_ID = u.UDA_ID
        WHERE
            u.MEMBER_ID = m.MEMBER_ID FOR XML Raw)
            , '"/><row UDA="', ', '), '<row UDA="', ''), '"/>', '' )
     AS UDA
    ,CASE m.DATA_STORAGE
        WHEN 0 THEN 'Store Data'
        WHEN 1 THEN 'Never Share'
        WHEN 2 THEN 'Label Only'
        WHEN 3 THEN 'Shared Member'
        WHEN 4 THEN 'Dynamic Calc and Store'
        WHEN 5 THEN 'Dynamic'
     END AS DATA_STORAGE
     ,CASE m.DATA_TYPE
        WHEN 0 THEN 'Unspecified'
        WHEN 1 THEN 'Currency'
        WHEN 2 THEN 'Non-currency'
        WHEN 3 THEN 'Percentage'
        WHEN 4 THEN 'Enum'
        WHEN 5 THEN 'Date'
        WHEN 6 THEN 'Text'
        ELSE 'Unspecified'
     END AS DATA_TYPE
    ,CASE WHEN m.USED_IN & 1 = 1 THEN 
        CASE 
            WHEN m.CONSOL_OP & 6 = 6 THEN '^' 
            WHEN m.CONSOL_OP & 5 = 5 THEN '~' 
            WHEN m.CONSOL_OP & 4 = 4 THEN '%' 
            WHEN m.CONSOL_OP & 3 = 3 THEN '/' 
            WHEN m.CONSOL_OP & 2 = 2 THEN '*' 
            WHEN m.CONSOL_OP & 1 = 1 THEN '-' 
            ELSE '+' END
        ELSE
            NULL
        END AS PLAN1_CONSOL_OP
    ,CASE WHEN mfp1.FORMULA IS NULL THEN mfpd.FORMULA ELSE mfp1.FORMULA END AS PLAN1_FORMULA
    ,CASE WHEN mfp1.DATA_STORAGE IS NULL THEN mfpd.DATA_STORAGE ELSE mfp1.DATA_STORAGE END AS PLAN1_DATA_STORAGE
    ,CASE WHEN mfp1.SOLVE_ORDER IS NULL THEN mfpd.SOLVE_ORDER ELSE mfp1.SOLVE_ORDER END AS PLAN1_SOLVE_ORDER
    ,at.ATTRIBUTE_NAME AS ATTRIBUTE_TEXT
    ,ab.ATTRIBUTE_NAME AS ATTRIBUTE_BOOLEAN
FROM
    HSP_MEMBER m
INNER JOIN 
    HSP_OBJECT o ON m.MEMBER_ID = o.OBJECT_ID
INNER JOIN
    HSP_OBJECT op ON o.PARENT_ID = op.OBJECT_ID
INNER JOIN
    HSP_OBJECT od ON m.DIM_ID = od.OBJECT_ID
LEFT JOIN
    (   SELECT
            oa.OBJECT_NAME AS ALIAS_NAME
            ,a.MEMBER_ID
        FROM
            HSP_ALIAS a
        INNER JOIN
            HSP_OBJECT oa ON a.ALIAS_ID = oa.OBJECT_ID
        INNER JOIN
            HSP_OBJECT oat ON a.ALIASTBL_ID = oat.OBJECT_ID
        WHERE
            oat.OBJECT_NAME = 'Default')
     a1 ON m.MEMBER_ID = a1.MEMBER_ID
LEFT JOIN
    (   SELECT
            oa.OBJECT_NAME AS ALIAS_NAME
            ,a.MEMBER_ID
        FROM
            HSP_ALIAS a
        INNER JOIN
            HSP_OBJECT oa ON a.ALIAS_ID = oa.OBJECT_ID
        INNER JOIN
            HSP_OBJECT oat ON a.ALIASTBL_ID = oat.OBJECT_ID
        WHERE
            oat.OBJECT_NAME = 'English')
     a2 ON m.MEMBER_ID = a2.MEMBER_ID
    LEFT JOIN
    (SELECT
        MEMBER_ID
        ,mf.PLAN_TYPE
        ,CASE DATA_STORAGE
            WHEN 0 THEN 'Store Data'
            WHEN 1 THEN 'Never Share'
            WHEN 2 THEN 'Label Only'
            WHEN 3 THEN 'Shared Member'
            WHEN 4 THEN 'Dynamic Calc and Store'
            WHEN 5 THEN 'Dynamic' END AS DATA_STORAGE
        ,SOLVE_ORDER
        ,FORMULA
    FROM
        HSP_MEMBER_FORMULA mf
    LEFT JOIN
        (SELECT 0 AS PLAN_TYPE,'Default' AS TYPE_NAME UNION ALL 
         SELECT PLAN_TYPE,TYPE_NAME FROM HSP_PLAN_TYPE) p ON mf.PLAN_TYPE = p.PLAN_TYPE
    WHERE
        p.TYPE_NAME = 'Default') mfpd ON m.MEMBER_ID = mfpd.MEMBER_ID
LEFT JOIN
    (SELECT
        MEMBER_ID
        ,mf.PLAN_TYPE
        ,CASE DATA_STORAGE
            WHEN 0 THEN 'Store Data'
            WHEN 1 THEN 'Never Share'
            WHEN 2 THEN 'Label Only'
            WHEN 3 THEN 'Shared Member'
            WHEN 4 THEN 'Dynamic Calc and Store'
            WHEN 5 THEN 'Dynamic' END AS DATA_STORAGE
        ,SOLVE_ORDER
        ,FORMULA
    FROM
        HSP_MEMBER_FORMULA mf
    LEFT JOIN
        (SELECT 0 AS PLAN_TYPE,'Default' AS TYPE_NAME UNION ALL 
         SELECT PLAN_TYPE,TYPE_NAME FROM HSP_PLAN_TYPE) p ON mf.PLAN_TYPE = p.PLAN_TYPE
    WHERE
        p.TYPE_NAME = 'Plan1') mfp1 ON m.MEMBER_ID = mfp1.MEMBER_ID
LEFT JOIN
    (SELECT
        m.MEMBER_ID
        ,amo.OBJECT_NAME AS ATTRIBUTE_NAME
    FROM
        HSP_MEMBER m
    INNER JOIN
        HSP_MEMBER_TO_ATTRIBUTE mta ON m.MEMBER_ID = mta.MEMBER_ID
    INNER JOIN
        HSP_OBJECT ado ON mta.ATTR_ID = ado.OBJECT_ID
    INNER JOIN
        HSP_OBJECT amo ON mta.ATTR_MEM_ID = amo.OBJECT_ID
    WHERE
        ado.OBJECT_NAME = 'AttrText') at ON m.MEMBER_ID = at.MEMBER_ID
LEFT JOIN
    (SELECT
        m.MEMBER_ID
        ,amo.OBJECT_NAME AS ATTRIBUTE_NAME
    FROM
        HSP_MEMBER m
    INNER JOIN
        HSP_MEMBER_TO_ATTRIBUTE mta ON m.MEMBER_ID = mta.MEMBER_ID
    INNER JOIN
        HSP_OBJECT ado ON mta.ATTR_ID = ado.OBJECT_ID
    INNER JOIN
        HSP_OBJECT amo ON mta.ATTR_MEM_ID = amo.OBJECT_ID
    WHERE
        ado.OBJECT_NAME = 'AttrBoolean') ab ON m.MEMBER_ID = ab.MEMBER_ID
WHERE
    od.OBJECT_NAME = 'Product'
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

It makes for a long query…but effective. This query will give us all of our properties for our first plan type. And here are the results:

So SQL Server wasn’t too bad. How about Oracle? Well…that’s a different story. There are two ways to approach this query on the Oracle side. Option 1: combine all of the queries like we did in SQL Server. Sounds easy enough until you remember back to Part 3 of our series where we first used the LISTAGG function. That function makes this process a pain. Instead of just combining the queries together, we have to add everything to our group by as well. Here it is for fun:

SQL

147 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
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
SELECT
  o.OBJECT_NAME AS MEMBER_NAME
  ,op.OBJECT_NAME AS PARENT_NAME
  ,a1.ALIAS_NAME AS ALIAS_DEFAULT
    ,a2.ALIAS_NAME AS ALIAS_ENGLISH
  ,LISTAGG(UDA_VALUE,', ') WITHIN GROUP (ORDER BY UDA_VALUE) "UDA LIST"
  ,CASE m.DATA_STORAGE
    WHEN 0 THEN 'Store Data'
    WHEN 1 THEN 'Never Share'
    WHEN 2 THEN 'Label Only'
    WHEN 3 THEN 'Shared Member'
    WHEN 4 THEN 'Dynamic Calc and Store'
    WHEN 5 THEN 'Dynamic'
    END AS DATA_STORAGE
  ,CASE m.DATA_TYPE
    WHEN 0 THEN 'Unspecified'
    WHEN 1 THEN 'Currency'
    WHEN 2 THEN 'Non-currency'
    WHEN 3 THEN 'Percentage'
    WHEN 4 THEN 'Enum'
    WHEN 5 THEN 'Date'
    WHEN 6 THEN 'Text'
    ELSE 'Unspecified'
    END AS DATA_TYPE
  ,CASE WHEN BITAND(m.USED_IN,1) = 1 THEN 
    CASE 
      WHEN BITAND(m.CONSOL_OP,6) = 6 THEN '^' 
      WHEN BITAND(m.CONSOL_OP,5) = 5 THEN '~' 
      WHEN BITAND(m.CONSOL_OP,4) = 4 THEN '%' 
      WHEN BITAND(m.CONSOL_OP,3) = 3 THEN '/' 
      WHEN BITAND(m.CONSOL_OP,2) = 2 THEN '*' 
      WHEN BITAND(m.CONSOL_OP,1) = 1 THEN '-' 
      ELSE '+' END
    ELSE
      NULL
    END AS PLAN1_CONSOL_OP
  ,dbms_lob.substr(CASE WHEN mfp1.FORMULA IS NULL THEN mfpd.FORMULA ELSE mfp1.FORMULA END,4000,1) AS PLAN1_FORMULA
    ,CASE WHEN mfp1.DATA_STORAGE IS NULL THEN mfpd.DATA_STORAGE ELSE mfp1.DATA_STORAGE END AS PLAN1_DATA_STORAGE
    ,CASE WHEN mfp1.SOLVE_ORDER IS NULL THEN mfpd.SOLVE_ORDER ELSE mfp1.SOLVE_ORDER END AS PLAN1_SOLVE_ORDER
FROM
    HSP_MEMBER m
INNER JOIN 
    HSP_OBJECT o ON m.MEMBER_ID = o.OBJECT_ID
INNER JOIN
    HSP_OBJECT op ON o.PARENT_ID = op.OBJECT_ID
INNER JOIN
    HSP_OBJECT od ON m.DIM_ID = od.OBJECT_ID
LEFT JOIN
    (   SELECT
            oa.OBJECT_NAME AS ALIAS_NAME
            ,a.MEMBER_ID
        FROM
            HSP_ALIAS a
        INNER JOIN
            HSP_OBJECT oa ON a.ALIAS_ID = oa.OBJECT_ID
        INNER JOIN
            HSP_OBJECT oat ON a.ALIASTBL_ID = oat.OBJECT_ID
        WHERE
            oat.OBJECT_NAME = 'Default')
     a1 ON m.MEMBER_ID = a1.MEMBER_ID
LEFT JOIN
    (   SELECT
            oa.OBJECT_NAME AS ALIAS_NAME
            ,a.MEMBER_ID
        FROM
            HSP_ALIAS a
        INNER JOIN
            HSP_OBJECT oa ON a.ALIAS_ID = oa.OBJECT_ID
        INNER JOIN
            HSP_OBJECT oat ON a.ALIASTBL_ID = oat.OBJECT_ID
        WHERE
            oat.OBJECT_NAME = 'English')
     a2 ON m.MEMBER_ID = a2.MEMBER_ID
LEFT JOIN
  ( SELECT 
      UDA_VALUE
      ,MEMBER_ID
    FROM
      HSP_MEMBER_TO_UDA mu
    INNER JOIN
      HSP_UDA u ON mu.UDA_ID = u.UDA_ID
    ) mu ON m.MEMBER_ID = mu.MEMBER_ID
LEFT JOIN
    (SELECT
        MEMBER_ID
        ,mf.PLAN_TYPE
        ,CASE DATA_STORAGE
            WHEN 0 THEN 'Store Data'
            WHEN 1 THEN 'Never Share'
            WHEN 2 THEN 'Label Only'
            WHEN 3 THEN 'Shared Member'
            WHEN 4 THEN 'Dynamic Calc and Store'
            WHEN 5 THEN 'Dynamic' END AS DATA_STORAGE
        ,SOLVE_ORDER
        ,FORMULA
    FROM
        HSP_MEMBER_FORMULA mf
    LEFT JOIN
        (SELECT 0 AS PLAN_TYPE,'Default' AS TYPE_NAME FROM DUAL UNION ALL 
         SELECT PLAN_TYPE,TYPE_NAME FROM HSP_PLAN_TYPE) p ON mf.PLAN_TYPE = p.PLAN_TYPE
    WHERE
        p.TYPE_NAME = 'Default') mfpd ON m.MEMBER_ID = mfpd.MEMBER_ID
LEFT JOIN
    (SELECT
        MEMBER_ID
        ,mf.PLAN_TYPE
        ,CASE DATA_STORAGE
            WHEN 0 THEN 'Store Data'
            WHEN 1 THEN 'Never Share'
            WHEN 2 THEN 'Label Only'
            WHEN 3 THEN 'Shared Member'
            WHEN 4 THEN 'Dynamic Calc and Store'
            WHEN 5 THEN 'Dynamic' END AS DATA_STORAGE
        ,SOLVE_ORDER
        ,FORMULA
    FROM
        HSP_MEMBER_FORMULA mf
    LEFT JOIN
        (SELECT 0 AS PLAN_TYPE,'Default' AS TYPE_NAME FROM DUAL UNION ALL 
         SELECT PLAN_TYPE,TYPE_NAME FROM HSP_PLAN_TYPE) p ON mf.PLAN_TYPE = p.PLAN_TYPE
    WHERE
        p.TYPE_NAME = 'Plan1') mfp1 ON m.MEMBER_ID = mfp1.MEMBER_ID
WHERE
    od.OBJECT_NAME = 'Product'
GROUP BY
  o.OBJECT_NAME
  ,op.OBJECT_NAME
  ,od.OBJECT_NAME
  ,a1.ALIAS_NAME
    ,a2.ALIAS_NAME
  ,m.DATA_STORAGE
  ,m.DATA_TYPE
  ,CASE WHEN BITAND(m.USED_IN,1) = 1 THEN 
    CASE 
      WHEN BITAND(m.CONSOL_OP,6) = 6 THEN '^' 
      WHEN BITAND(m.CONSOL_OP,5) = 5 THEN '~' 
      WHEN BITAND(m.CONSOL_OP,4) = 4 THEN '%' 
      WHEN BITAND(m.CONSOL_OP,3) = 3 THEN '/' 
      WHEN BITAND(m.CONSOL_OP,2) = 2 THEN '*' 
      WHEN BITAND(m.CONSOL_OP,1) = 1 THEN '-' 
      ELSE '+' END
    ELSE
      NULL
    END
  ,dbms_lob.substr(CASE WHEN mfp1.FORMULA IS NULL THEN mfpd.FORMULA ELSE mfp1.FORMULA END,4000,1)
    ,CASE WHEN mfp1.DATA_STORAGE IS NULL THEN mfpd.DATA_STORAGE ELSE mfp1.DATA_STORAGE END
    ,CASE WHEN mfp1.SOLVE_ORDER IS NULL THEN mfpd.SOLVE_ORDER ELSE mfp1.SOLVE_ORDER END
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

It’s ugly, but it works. There was a problem with just adding everything to the GROUP BY. Member formulas are of type CLOB which of course can not be used in a GROUP BY. So what do we do? We instead use a substring function to convert the CLOB to a 4,000 character VARCHAR. This works great…assuming you don’t have any large formulas. So this isn’t a great solution.

So what do we do? Option B. We take a step back and place our LISTAGG function into its own sub-query. This let’s us move the group by to a very small place and still reference our member ID. Here it is:

SQL

121 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
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
SELECT
  o.OBJECT_NAME AS MEMBER_NAME
  ,op.OBJECT_NAME AS PARENT_NAME
  ,a1.ALIAS_NAME AS ALIAS_DEFAULT
    ,a2.ALIAS_NAME AS ALIAS_ENGLISH
  ,( SELECT LISTAGG(UDA_VALUE,', ') WITHIN GROUP (ORDER BY UDA_VALUE) "UDA_LIST" FROM (SELECT 
      UDA_VALUE
      ,MEMBER_ID
    FROM
      HSP_MEMBER_TO_UDA mu
    INNER JOIN
      HSP_UDA u ON mu.UDA_ID = u.UDA_ID) muda WHERE muda.MEMBER_ID = m.MEMBER_ID) AS UDA
  ,CASE m.DATA_STORAGE
    WHEN 0 THEN 'Store Data'
    WHEN 1 THEN 'Never Share'
    WHEN 2 THEN 'Label Only'
    WHEN 3 THEN 'Shared Member'
    WHEN 4 THEN 'Dynamic Calc and Store'
    WHEN 5 THEN 'Dynamic'
    END AS DATA_STORAGE
  ,CASE m.DATA_TYPE
    WHEN 0 THEN 'Unspecified'
    WHEN 1 THEN 'Currency'
    WHEN 2 THEN 'Non-currency'
    WHEN 3 THEN 'Percentage'
    WHEN 4 THEN 'Enum'
    WHEN 5 THEN 'Date'
    WHEN 6 THEN 'Text'
    ELSE 'Unspecified'
    END AS DATA_TYPE
  ,CASE WHEN BITAND(m.USED_IN,1) = 1 THEN 
    CASE 
      WHEN BITAND(m.CONSOL_OP,6) = 6 THEN '^' 
      WHEN BITAND(m.CONSOL_OP,5) = 5 THEN '~' 
      WHEN BITAND(m.CONSOL_OP,4) = 4 THEN '%' 
      WHEN BITAND(m.CONSOL_OP,3) = 3 THEN '/' 
      WHEN BITAND(m.CONSOL_OP,2) = 2 THEN '*' 
      WHEN BITAND(m.CONSOL_OP,1) = 1 THEN '-' 
      ELSE '+' END
    ELSE
      NULL
    END AS PLAN1_CONSOL_OP
  ,CASE WHEN mfp1.FORMULA IS NULL THEN mfpd.FORMULA ELSE mfp1.FORMULA END AS PLAN1_FORMULA
    ,CASE WHEN mfp1.DATA_STORAGE IS NULL THEN mfpd.DATA_STORAGE ELSE mfp1.DATA_STORAGE END AS PLAN1_DATA_STORAGE
    ,CASE WHEN mfp1.SOLVE_ORDER IS NULL THEN mfpd.SOLVE_ORDER ELSE mfp1.SOLVE_ORDER END AS PLAN1_SOLVE_ORDER
FROM
    HSP_MEMBER m
INNER JOIN 
    HSP_OBJECT o ON m.MEMBER_ID = o.OBJECT_ID
INNER JOIN
    HSP_OBJECT op ON o.PARENT_ID = op.OBJECT_ID
INNER JOIN
    HSP_OBJECT od ON m.DIM_ID = od.OBJECT_ID
LEFT JOIN
    (   SELECT
            oa.OBJECT_NAME AS ALIAS_NAME
            ,a.MEMBER_ID
        FROM
            HSP_ALIAS a
        INNER JOIN
            HSP_OBJECT oa ON a.ALIAS_ID = oa.OBJECT_ID
        INNER JOIN
            HSP_OBJECT oat ON a.ALIASTBL_ID = oat.OBJECT_ID
        WHERE
            oat.OBJECT_NAME = 'Default')
     a1 ON m.MEMBER_ID = a1.MEMBER_ID
LEFT JOIN
    (   SELECT
            oa.OBJECT_NAME AS ALIAS_NAME
            ,a.MEMBER_ID
        FROM
            HSP_ALIAS a
        INNER JOIN
            HSP_OBJECT oa ON a.ALIAS_ID = oa.OBJECT_ID
        INNER JOIN
            HSP_OBJECT oat ON a.ALIASTBL_ID = oat.OBJECT_ID
        WHERE
            oat.OBJECT_NAME = 'English')
     a2 ON m.MEMBER_ID = a2.MEMBER_ID
LEFT JOIN
    (SELECT
        MEMBER_ID
        ,mf.PLAN_TYPE
        ,CASE DATA_STORAGE
            WHEN 0 THEN 'Store Data'
            WHEN 1 THEN 'Never Share'
            WHEN 2 THEN 'Label Only'
            WHEN 3 THEN 'Shared Member'
            WHEN 4 THEN 'Dynamic Calc and Store'
            WHEN 5 THEN 'Dynamic' END AS DATA_STORAGE
        ,SOLVE_ORDER
        ,FORMULA
    FROM
        HSP_MEMBER_FORMULA mf
    LEFT JOIN
        (SELECT 0 AS PLAN_TYPE,'Default' AS TYPE_NAME FROM DUAL UNION ALL 
         SELECT PLAN_TYPE,TYPE_NAME FROM HSP_PLAN_TYPE) p ON mf.PLAN_TYPE = p.PLAN_TYPE
    WHERE
        p.TYPE_NAME = 'Default') mfpd ON m.MEMBER_ID = mfpd.MEMBER_ID
LEFT JOIN
    (SELECT
        MEMBER_ID
        ,mf.PLAN_TYPE
        ,CASE DATA_STORAGE
            WHEN 0 THEN 'Store Data'
            WHEN 1 THEN 'Never Share'
            WHEN 2 THEN 'Label Only'
            WHEN 3 THEN 'Shared Member'
            WHEN 4 THEN 'Dynamic Calc and Store'
            WHEN 5 THEN 'Dynamic' END AS DATA_STORAGE
        ,SOLVE_ORDER
        ,FORMULA
    FROM
        HSP_MEMBER_FORMULA mf
    LEFT JOIN
        (SELECT 0 AS PLAN_TYPE,'Default' AS TYPE_NAME FROM DUAL UNION ALL 
         SELECT PLAN_TYPE,TYPE_NAME FROM HSP_PLAN_TYPE) p ON mf.PLAN_TYPE = p.PLAN_TYPE
    WHERE
        p.TYPE_NAME = 'Plan1') mfp1 ON m.MEMBER_ID = mfp1.MEMBER_ID
WHERE
    od.OBJECT_NAME = 'Product'
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

This makes for a much shorter and less complex query. Well, mildly less complex as this requires not just a sub-query, but a nested sub-query. With either query, whether you choose the complex query with the big group by or the far simpler query with the nested sub-query, the results are the same:

And so the series on the HSP_MEMBER table (and all the other tables related) ends. But wait, there are more properties missing? The account dimension has more? The entity dimension has more? Tune in for yet another series, this time on the built-in dimensions.

The Planning Repository: HSP_MEMBER (Part 5 – Attribute Associations)

September 14, 2015 // Brian Marshall // Hyperion Planning, Planning Repository Tags: Hyperion Planning, Planning Repository No Responses

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).

We are finally to the last piece before we put it all together: attribute associations. As with UDA’s and formulas, we have another table to take a look at. Members are associated with attribute members using the HSP_MEMBER_TO_ATTRIBUTE table. This is a pretty simple table, but let’s still take a quick look at the structure:

Field Name	SQL Server Type	Oracle Type	Description
MEMBER_ID	int	NUMBER(38,0)	The OBJECT_ID of the member associated with the attribute member.
ATTR_ID	int	NUMBER(38,0)	The OBJECT_ID of the attribute dimension.
ATTR_MEM_ID	int	NUMBER(38,0)	The OBJECT_ID of the attribute dimension member associated with the member.
PERSPECTIVE1	int	NUMBER(38,0)	I have not been able to figure out what this is used for. It seems to always be 0 in my testing.
PERSPECTIVE2	int	NUMBER(38,0)	I have not been able to figure out what this is used for. It seems to always be 0 in my testing.

Pretty simple. We have the member and the attribute member and also the attribute itself. This means we will have to filter this table to get to specific attribute associations by attribute. We also have two PERSPECTIVE fields that no longer appear to be in use. So let’s do a simple query to take a look at associations for a specific attribute dimension:

SQL

14 lines

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
    o.OBJECT_NAME AS MEMBER_NAME
    ,amo.OBJECT_NAME AS ATTRIBUTE_NAME
FROM
    HSP_MEMBER m
INNER JOIN
    HSP_OBJECT o ON m.MEMBER_ID = o.OBJECT_ID
INNER JOIN
    HSP_MEMBER_TO_ATTRIBUTE mta ON m.MEMBER_ID = mta.MEMBER_ID
INNER JOIN
    HSP_OBJECT ado ON mta.ATTR_ID = ado.OBJECT_ID
INNER JOIN
    HSP_OBJECT amo ON mta.ATTR_MEM_ID = amo.OBJECT_ID
WHERE ado.OBJECT_NAME = 'AttrText'
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

And here we see the results:

So this is great to see all of the members that have a specific attribute associated, but how do we see all of the associations for all of our attribute dimensions? We join to the above query for each of our attributes:

SQL

40 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
SELECT
    o.OBJECT_NAME AS MEMBER_NAME
    ,at.ATTRIBUTE_NAME AS ATTRIBUTE_TEXT
    ,ab.ATTRIBUTE_NAME AS ATTRIBUTE_BOOLEAN
FROM
    HSP_MEMBER m
INNER JOIN 
    HSP_OBJECT o ON m.MEMBER_ID = o.OBJECT_ID
INNER JOIN
    HSP_OBJECT od ON m.DIM_ID = od.OBJECT_ID
LEFT JOIN
    (SELECT
        m.MEMBER_ID
        ,amo.OBJECT_NAME AS ATTRIBUTE_NAME
    FROM
        HSP_MEMBER m
    INNER JOIN
        HSP_MEMBER_TO_ATTRIBUTE mta ON m.MEMBER_ID = mta.MEMBER_ID
    INNER JOIN
        HSP_OBJECT ado ON mta.ATTR_ID = ado.OBJECT_ID
    INNER JOIN
        HSP_OBJECT amo ON mta.ATTR_MEM_ID = amo.OBJECT_ID
    WHERE
        ado.OBJECT_NAME = 'AttrText') at ON m.MEMBER_ID = at.MEMBER_ID
LEFT JOIN
    (SELECT
        m.MEMBER_ID
        ,amo.OBJECT_NAME AS ATTRIBUTE_NAME
    FROM
        HSP_MEMBER m
    INNER JOIN
        HSP_MEMBER_TO_ATTRIBUTE mta ON m.MEMBER_ID = mta.MEMBER_ID
    INNER JOIN
        HSP_OBJECT ado ON mta.ATTR_ID = ado.OBJECT_ID
    INNER JOIN
        HSP_OBJECT amo ON mta.ATTR_MEM_ID = amo.OBJECT_ID
    WHERE
        ado.OBJECT_NAME = 'AttrBoolean') ab ON m.MEMBER_ID = ab.MEMBER_ID
WHERE
    od.OBJECT_NAME = 'Product'
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

This is just an example. If I were to do this in a production environment, each of the left joins would be to views. We would have a view for each of our attribute dimensions. But for an example that you can easily execute, it works just fine. Let’s look at the results:

This gives us a list of all of our product dimension members along with the attribute member association. And with that, we’re ready to put it all together and get a full extract of a custom dimension…in our next post.

The Planning Repository: Attribute Dimensions

September 9, 2015 // Brian Marshall // Hyperion Planning, Planning Repository Tags: Hyperion Planning, Planning Repository No Responses

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).

Today we’ll take a brief break from the HSP_MEMBER related tables to lay the foundation for our next HSP_MEMBER post. I think its important that we take look at our attribute dimensions before we see how they are associated with actual members. There are two main tables that make up attribute dimension in the repository. The first is HSP_ATTRIBUTE_DIM which contains the name, type, and associated dimension of our attribute. The second is HSP_ATTRIBUTE_MEMBER which contains the members of the attribute dimension. So let’s take our traditional look at the table structures for both.

HSP_ATTRIBUTE_DIM:

Field Name	SQL Server Type	Oracle Type	Description
ATTR_ID	int	NUMBER(38,0)	The OBJECT_ID of the attribute.
DIM_ID	int	NUMBER(38,0)	The OBJECT_ID of the associated dimension for the attribute.
ATTR_TYPE	smallint	NUMBER(38,0)	The type of attribute dimension. 0 = Text 1 = Numeric 2 = Boolean 3 = Date
PERSPECTIVE1	int	NUMBER(38,0)	No ideaÉmay not even ben in use. Let me know if you have a clue.
PERSPECTIVE2	int	NUMBER(38,0)	No ideaÉmay not even ben in use. Let me know if you have a clue.

HSP_ATTRIBUTE_MEMBER:

Field Name	SQL Server Type	Oracle Type	Description
ATTR_MEM_ID	int	NUMBER(38,0)	The OBJECT_ID of the attribute dimension member.
ATTR_ID	int	NUMBER(38,0)	The OBJECT_ID of the attribute dimension.
LEVEL0	smallint	NUMBER(38,0)	This is supposed to tell us if the member is a leaf. It doesn't actually do that.
TEXT_VAL	varchar(32)	VARCHAR(32 CHAR)	In theory this holds a text value for the member. Instead it is always null.
NUMBER_VAL	decimal(18,0)	NUMBER(38,0)	In theory this holds a numeric value for the member. Again, always null.
BOOLEAN_VAL	smallint	NUMBER(38,0)	There's a pattern forming here...always null.
DATE_VAL	datetime	DATE	You guessed it...still null.

Obviously we have a few things to talk about. Starting with the HSP_ATTRIBUTE_DIM, things are pretty straight forward. We have a magic decoder ring for the attribute type and two columns that I still can’t actually identify. The comments on the fields read “ID that identifies the 1st independent dimension”. If you have any idea what that actually mean…drop me a line. The good news is that it really doesn’t seem to matter. Let’s have a look at the data:

SQL

14 lines

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
    ATTR_ID
    ,o.OBJECT_NAME AS ATTR_NAME
    ,DIM_ID
    ,od.OBJECT_NAME AS DIM_NAME
    ,ATTR_TYPE
    ,PERSPECTIVE1
    ,PERSPECTIVE2
FROM
    HSP_ATTRIBUTE_DIM ad
INNER JOIN
    HSP_OBJECT o ON ad.ATTR_ID = o.OBJECT_ID
INNER JOIN
    HSP_OBJECT od ON ad.DIM_ID = od.OBJECT_ID
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

This query should return a list of our attribute dimensions. As I’m using the Vision sample application, I had to go through and add attribute dimensions. We join twice to the HSP_OBJECT table to get the names of both the attribute dimension itself and the associated real dimension. Here’s what it looks like with the joins:

How about the HSP_ATTRIBUTE_MEMBER table? This one is more frustrating. From all of my testing and checking, it appears that the only two columns of use are the first two. After that, the columns either aren’t used (the value columns) or are not properly filled out (LEVEL0 is always 1). Instead we ignore the value columns and just use the OBJECT_NAME and HAS_CHILDREN columns from our trusty HSP_OBJECT table. So let’s take a look at a specific attribute dimension:

SQL

24 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
SELECT
    ATTR_MEM_ID
    ,o.OBJECT_NAME AS ATTR_MEM_NAME
    ,op.OBJECT_NAME AS ATTR_PARENT
    ,ATTR_ID
    ,oa.OBJECT_NAME AS ATTR_NAME
    ,LEVEL0
    ,o.HAS_CHILDREN
    ,TEXT_VAL
    ,NUMBER_VAL
    ,BOOLEAN_VAL
    ,DATE_VAL
FROM
    HSP_ATTRIBUTE_MEMBER am
INNER JOIN
    HSP_OBJECT o ON am.ATTR_MEM_ID = o.OBJECT_ID
INNER JOIN
    HSP_OBJECT oa ON am.ATTR_ID = oa.OBJECT_ID
INNER JOIN
    HSP_OBJECT op ON o.PARENT_ID = op.OBJECT_ID
WHERE
    oa.OBJECT_NAME = 'AttrText'
ORDER BY
    o.POSITION
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

This query should return a list of our attributes members. Again, I had to add these, but there is a little bit of hierarchy to make sure that we understand which columns we should use to identify leaf-level members. We have three joins to the HSP_OBJECT table this time. First for the member name, then for the attribute dimension name, and finally for the name of our parent. We also get the HAS_CHILDREN column from HSP_OBJECT table as the LEVEL0 column is always 1. Perhaps when this table originated, the HSP_OBJECT table didn’t include HAS_CHILDREN. So here’s the resulting data:

And that’s it for Attribute Dimensions. We’ll dive into the relationship between members and attribute dimensions in our next post on the repository.

The Planning Repository: HSP_MEMBER (Part 4 – HSP_MEMBER_FORMULA)

September 1, 2015 // Brian Marshall // Hyperion Planning, Planning Repository Tags: Hyperion Planning, Planning Repository No Responses

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).

As we near the end of the HSP_MEMBER series, we’ll take a look at a relatively low-complexity piece next: member formulas. As we mentioned before, formulas are stored in a separate table in Planning. This is done because we can have a different formula for each of our plan types. Our member formulas are stored in the HSP_MEMBER_FORMULA table. Let’s take a look at the structure:

Field Name	SQL Server Type	Oracle Type	Description
MEMBER_ID	int	NUMBER(38,0)	The OBJECT_ID of the member.
PLAN_TYPE	int	NUMBER(38,0)	The plan type.
DATA_STORAGE	int	NUMBER(38,0)	The data storage type to be used in Essbase for the specific plan type.
SOLVE_ORDER	int	NUMBER(38,0)	The plan type specific data storage for the member.
FORMULA	ntext	CLOB	The member formula.

There’s not a lot of new information here. The DATA_STORAGE field decodes just like it did in the HSP_MEMBER table and we just need to join to the HSP_PLAN_TYPE table to get our plan type names and to the HSP_OBJECT table to get our member names. Let’s give it a go:

SQL Server:

SQL

21 lines

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT
    MEMBER_ID
    ,o.OBJECT_NAME AS MEMBER_NAME
    ,mf.PLAN_TYPE
    ,DATA_STORAGE
    ,CASE DATA_STORAGE
        WHEN 0 THEN 'Store Data'
        WHEN 1 THEN 'Never Share'
        WHEN 2 THEN 'Label Only'
        WHEN 3 THEN 'Shared Member'
        WHEN 4 THEN 'Dynamic Calc and Store'
        WHEN 5 THEN 'Dynamic' END AS DATA_STORAGE
    ,SOLVE_ORDER
    ,FORMULA
FROM
  HSP_MEMBER_FORMULA mf
INNER JOIN
    HSP_OBJECT o ON mf.MEMBER_ID = o.OBJECT_ID
LEFT JOIN
    (SELECT 0 AS PLAN_TYPE,'Default' AS TYPE_NAME UNION ALL 
     SELECT PLAN_TYPE,TYPE_NAME FROM HSP_PLAN_TYPE) p ON mf.PLAN_TYPE = p.PLAN_TYPE 
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

Oracle:

SQL

21 lines

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT
    MEMBER_ID
    ,o.OBJECT_NAME AS MEMBER_NAME
    ,mf.PLAN_TYPE
    ,DATA_STORAGE
    ,CASE DATA_STORAGE
        WHEN 0 THEN 'Store Data'
        WHEN 1 THEN 'Never Share'
        WHEN 2 THEN 'Label Only'
        WHEN 3 THEN 'Shared Member'
        WHEN 4 THEN 'Dynamic Calc and Store'
        WHEN 5 THEN 'Dynamic' END AS DATA_STORAGE
    ,SOLVE_ORDER
    ,FORMULA
FROM
  HSP_MEMBER_FORMULA mf
INNER JOIN
    HSP_OBJECT o ON mf.MEMBER_ID = o.OBJECT_ID
LEFT JOIN
    (SELECT 0 AS PLAN_TYPE,'Default' AS TYPE_NAME FROM DUAL UNION ALL 
     SELECT PLAN_TYPE,TYPE_NAME FROM HSP_PLAN_TYPE) p ON mf.PLAN_TYPE = p.PLAN_TYPE
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

Our goal here is to just read out all of the formulas for each plan type. The most interesting thing here is that everything is stored by plan type. Basically, this table tells us which formula to use for which cube inside of the Essbase outline. The slight issue with this is that they also have the default calculation that exists in all used plan types unless another formula is specified for a plan type. So we have a sub-query in our join that gives us the 0 plan type (default) that we then union to our standard plan type query. Here are the results:

SQL Server (Management Studio):

HSP_MEMBER_FORMULA_M

Oracle (SQL Developer):

That gets us a list of all of the formulas for all of our members for all of our plan types and defaults. What if I just want to figure out the formula for a specific plan type. Because many developers just use the default formula, we have a little more logic that goes into it. So we turn to a pair of sub-queries along with a few case statements:

SQL Server:

SQL

49 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
SELECT
    o.OBJECT_NAME AS MEMBER_NAME
    ,CASE WHEN mfp1.FORMULA IS NULL THEN mfpd.FORMULA ELSE mfp1.FORMULA END AS PLAN1_FORMULA
    ,CASE WHEN mfp1.DATA_STORAGE IS NULL THEN mfpd.DATA_STORAGE ELSE mfp1.DATA_STORAGE END AS PLAN1_DATA_STORAGE
    ,CASE WHEN mfp1.SOLVE_ORDER IS NULL THEN mfpd.SOLVE_ORDER ELSE mfp1.SOLVE_ORDER END AS PLAN1_SOLVE_ORDER
FROM
    HSP_MEMBER m
INNER JOIN
    HSP_OBJECT o ON m.MEMBER_ID = o.OBJECT_ID
LEFT JOIN
    (SELECT
        MEMBER_ID
        ,mf.PLAN_TYPE
        ,CASE DATA_STORAGE
            WHEN 0 THEN 'Store Data'
            WHEN 1 THEN 'Never Share'
            WHEN 2 THEN 'Label Only'
            WHEN 3 THEN 'Shared Member'
            WHEN 4 THEN 'Dynamic Calc and Store'
            WHEN 5 THEN 'Dynamic' END AS DATA_STORAGE
        ,SOLVE_ORDER
        ,FORMULA
    FROM
        HSP_MEMBER_FORMULA mf
    LEFT JOIN
        (SELECT 0 AS PLAN_TYPE,'Default' AS TYPE_NAME UNION ALL 
         SELECT PLAN_TYPE,TYPE_NAME FROM HSP_PLAN_TYPE) p ON mf.PLAN_TYPE = p.PLAN_TYPE
    WHERE
        p.TYPE_NAME = 'Default') mfpd ON m.MEMBER_ID = mfpd.MEMBER_ID
LEFT JOIN
    (SELECT
        MEMBER_ID
        ,mf.PLAN_TYPE
        ,CASE DATA_STORAGE
            WHEN 0 THEN 'Store Data'
            WHEN 1 THEN 'Never Share'
            WHEN 2 THEN 'Label Only'
            WHEN 3 THEN 'Shared Member'
            WHEN 4 THEN 'Dynamic Calc and Store'
            WHEN 5 THEN 'Dynamic' END AS DATA_STORAGE
        ,SOLVE_ORDER
        ,FORMULA
    FROM
        HSP_MEMBER_FORMULA mf
    LEFT JOIN
        (SELECT 0 AS PLAN_TYPE,'Default' AS TYPE_NAME UNION ALL 
         SELECT PLAN_TYPE,TYPE_NAME FROM HSP_PLAN_TYPE) p ON mf.PLAN_TYPE = p.PLAN_TYPE
    WHERE
        p.TYPE_NAME = 'Plan1') mfp1 ON m.MEMBER_ID = mfp1.MEMBER_ID
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

Oracle:

SQL

49 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
SELECT
    o.OBJECT_NAME AS MEMBER_NAME
    ,CASE WHEN mfp1.FORMULA IS NULL THEN mfpd.FORMULA ELSE mfp1.FORMULA END AS PLAN1_FORMULA
    ,CASE WHEN mfp1.DATA_STORAGE IS NULL THEN mfpd.DATA_STORAGE ELSE mfp1.DATA_STORAGE END AS PLAN1_DATA_STORAGE
    ,CASE WHEN mfp1.SOLVE_ORDER IS NULL THEN mfpd.SOLVE_ORDER ELSE mfp1.SOLVE_ORDER END AS PLAN1_SOLVE_ORDER
FROM
    HSP_MEMBER m
INNER JOIN
    HSP_OBJECT o ON m.MEMBER_ID = o.OBJECT_ID
LEFT JOIN
    (SELECT
        MEMBER_ID
        ,mf.PLAN_TYPE
        ,CASE DATA_STORAGE
            WHEN 0 THEN 'Store Data'
            WHEN 1 THEN 'Never Share'
            WHEN 2 THEN 'Label Only'
            WHEN 3 THEN 'Shared Member'
            WHEN 4 THEN 'Dynamic Calc and Store'
            WHEN 5 THEN 'Dynamic' END AS DATA_STORAGE
        ,SOLVE_ORDER
        ,FORMULA
    FROM
        HSP_MEMBER_FORMULA mf
    LEFT JOIN
        (SELECT 0 AS PLAN_TYPE,'Default' AS TYPE_NAME FROM DUAL UNION ALL 
         SELECT PLAN_TYPE,TYPE_NAME FROM HSP_PLAN_TYPE) p ON mf.PLAN_TYPE = p.PLAN_TYPE
    WHERE
        p.TYPE_NAME = 'Default') mfpd ON m.MEMBER_ID = mfpd.MEMBER_ID
LEFT JOIN
    (SELECT
        MEMBER_ID
        ,mf.PLAN_TYPE
        ,CASE DATA_STORAGE
            WHEN 0 THEN 'Store Data'
            WHEN 1 THEN 'Never Share'
            WHEN 2 THEN 'Label Only'
            WHEN 3 THEN 'Shared Member'
            WHEN 4 THEN 'Dynamic Calc and Store'
            WHEN 5 THEN 'Dynamic' END AS DATA_STORAGE
        ,SOLVE_ORDER
        ,FORMULA
    FROM
        HSP_MEMBER_FORMULA mf
    LEFT JOIN
        (SELECT 0 AS PLAN_TYPE,'Default' AS TYPE_NAME FROM DUAL UNION ALL 
         SELECT PLAN_TYPE,TYPE_NAME FROM HSP_PLAN_TYPE) p ON mf.PLAN_TYPE = p.PLAN_TYPE
    WHERE
        p.TYPE_NAME = 'Plan1') mfp1 ON m.MEMBER_ID = mfp1.MEMBER_ID
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

That’s a long query just to get member formulas! On the bright side, it is far more usable than what the Outline Load Utility will produce. And I’ve said this before when I’ve used sub-queries…I wouldn’t typically do this as a sub-query. I would instead create views for these queries so that we can do regular joins and have a lot less maintenance should we need these formulas in other queries. With that, let’s look at the results:

SQL Server (Management Studio):

Oracle (SQL Developer):

That’s where we’ll stop for now. We have one last post to go (which will actually be two posts…) before we can put it all together and get one big query for each of our plan types with all of our properties. Stay tuned for attribute associations.

The Planning Repository: HSP_MEMBER (Part 3 – UDA’s)

August 24, 2015 // Brian Marshall // Hyperion Planning, Planning Repository Tags: Hyperion Planning, Planning Repository No Responses

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).

Eventually we will find all of tables required to get all of the important information about members out of the repository. Our next step is to go find where UDA’s are hiding. Much like aliases, Planning allows for an undefined number of UDA’s. So, of course we need another table…or two. The good news is that we actually have tables outside of the HSP_OBJECT table.

There are two tables we will be using to identify the UDA’s associated with members. First, let’s take a look at the HSP_UDA table:

Field Name	SQL Server Type	Oracle Type	Description
UDA_ID	int	NUMBER(38,0)	The unique ID of the UDA.
DIM_ID	int	NUMBER(38,0)	The OBJECT_ID of the dimension that the UDA belongs to.
UDA_VALUE	varchar(80)	VARCHAR2(80)	The actual UDA itself.

As far as Planning tables go, few are more straight forward than this one. Contrary to aliases, UDA’s do not even merit a row in the HSP_OBJECT table. HSP_UDA is it. So how do members get associated with these UDA’s? Another table of course! Let’s take a look at the HSP_MEMBER_TO_UDA table:

Field Name	SQL Server Type	Oracle Type	Description
MEMBER_ID	int	NUMBER(38,0)	The OBJECT_ID of the member associated with the UDA.
UDA_ID	int	NUMBER(38,0)	The unique ID of the UDA.

And I just got done saying that few are more straight forward than HSP_UDA…well, this one is even more straight forward. Given that this one is entirely self-explanatory, let’s go straight to combining this with the the HSP_MEMBER table. This will require a little bit more complexity. The methods are entirely different between Microsoft and Oracle.

SQL Server:

SQL

25 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
SELECT
    o.OBJECT_NAME AS MEMBER_NAME
    ,op.OBJECT_NAME AS PARENT_NAME
    ,REPLACE(REPLACE(REPLACE((
        SELECT
            ou.UDA_VALUE AS UDA
        FROM 
            HSP_MEMBER_TO_UDA u
        INNER JOIN
            HSP_UDA ou ON ou.UDA_ID = u.UDA_ID
        WHERE
            u.MEMBER_ID = m.MEMBER_ID FOR XML Raw)
            , '"/>', '' )
     AS MemberUDA
    ,od.OBJECT_NAME AS DIMENSION_NAME
FROM
    HSP_MEMBER m
INNER JOIN 
    HSP_OBJECT o ON m.MEMBER_ID = o.OBJECT_ID
INNER JOIN
    HSP_OBJECT op ON o.PARENT_ID = op.OBJECT_ID
INNER JOIN
    HSP_OBJECT od ON m.DIM_ID = od.OBJECT_ID
WHERE
    od.OBJECT_NAME = 'Account'
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

Instead of a table join, we have to do a sub-query. Basically, we want to get a list of all of our UDA’s associated with each member without each member having multiple rows. So our sub-query gets the list of members first and then we use the For XML Raw command to put the results of the sub-query into XML format. From there, we replace the XML parts with commas to give us a nice clean result:

Oracle:

SQL

28 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
SELECT
    o.OBJECT_NAME AS MEMBER_NAME
    ,op.OBJECT_NAME AS PARENT_NAME
    ,od.OBJECT_NAME AS DIMENSION_NAME
  ,LISTAGG(UDA_VALUE,', ') WITHIN GROUP (ORDER BY UDA_VALUE) "UDA LIST"
FROM
    HSP_MEMBER m
INNER JOIN 
    HSP_OBJECT o ON m.MEMBER_ID = o.OBJECT_ID
INNER JOIN
    HSP_OBJECT op ON o.PARENT_ID = op.OBJECT_ID
INNER JOIN
    HSP_OBJECT od ON m.DIM_ID = od.OBJECT_ID
LEFT JOIN
  ( SELECT 
      UDA_VALUE
      ,MEMBER_ID
    FROM
      HSP_MEMBER_TO_UDA mu
    INNER JOIN
      HSP_UDA u ON mu.UDA_ID = u.UDA_ID
    ) mu ON m.MEMBER_ID = mu.MEMBER_ID
WHERE
    od.OBJECT_NAME = 'Account'
GROUP BY
  o.OBJECT_NAME
  ,op.OBJECT_NAME
  ,od.OBJECT_NAME
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

In Oracle, we do a little of both. First we do a LEFT JOIN to a sub-query that returns all of our associated UDA’s for each member. If we left it at that, when a member has three UDA’s associated, it would have three rows in the result set. But, we made use of an excellent function to aggregate our list and separate it with commas. The LISTAGG function let’s you choose a value and a separator and then how you want to order the list. The downside to this function is that we have to GROUP BY all of our other columns. So be sure to include anything you add to the query to the GROUP BY as well. Here’s the result:

This is our first major difference in the queries that we execute against the repository. As luck would have it, we can get the same result regardless of our back end technology, we just need the right functions. That’s it for UDA’s! In our next look at the Planning Repository, we’ll dive into member formulas.

The Planning Repository: HSP_MEMBER (Part 2 – HSP_ALIAS)

August 7, 2015 // Brian Marshall // Hyperion Planning, Planning Repository Tags: Hyperion Planning, Planning Repository No Responses

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).

Now that we have the annoying complex pieces of the HSP_MEMBER table figured out (bitmasks), let’s move on to the easier to obtain and far more gratifying information about our meta-data in Hyperion Planning. This is where we leave the HSP_MEMBER table and go looking for everything else. The focus of this post will be the HSP_ALIAS table.

So why aren’t aliases in the HSP_MEMBER table? The answer is simply that Planning allows for an undefined number of alias tables. So why add a large number of columns to the main table? Instead we have an HSP_ALIAS table. Before we get into that table, let’s first determine how many alias tables we are dealing with. For that, we just go to the HSP_ALIAS_TABLE table…oh wait. That doesn’t exist.

Instead, we have to query the HSP_OBJECT table to determine our alias tables. But at least if we have to use the HSP_OBJECT table, we should be able to filter that easily using something like OBJECT_TYPE, right? Of course not…if we do that, aliases have an OBJECT_TYPE of 1 which equates to a folder. So because there are a lot of folders in Planning, that’s not terribly helpful. But…at least there is a parent for us to query. So here goes:

SQL

8 lines

1
2
3
4
5
6
7
8
SELECT
    o.OBJECT_NAME AS ALIAS_TABLE
FROM
    HSP_OBJECT o
INNER JOIN
    HSP_OBJECT op ON o.PARENT_ID = op.OBJECT_ID
WHERE
    op.OBJECT_NAME = 'Aliases'
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

Pretty simple…though it still seems like a round-about way to get our results:

So we can see that we have two alias tables. Now we should be ready to go take a look at the HSP_ALIAS table. Let’s start by looking at the structure:

Field Name	SQL Server Type	Oracle Type	Description
MEMBER_ID	int	NUMBER(38,0)	The OBJECT_ID of the member.
ALIASTBL_ID	int	NUMBER(38,0)	The OBJECT_ID of the alias table.
ALIAS_ID	int	NUMBER(38,0)	The OBJECT_ID of the alias.

So basically, there isn’t much going on here. Probably the most interesting thing about this table is that it just provides a reference back to the HSP_OBJECT table. So each alias also exists in the Planning repository as an object. So now let’s add alias information to our HSP_MEMBER query:

SQL

42 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
SELECT
    o.OBJECT_NAME AS MEMBER_NAME
    ,op.OBJECT_NAME AS PARENT_NAME
    ,a1.ALIAS_NAME AS ALIAS_DEFAULT
    ,a2.ALIAS_NAME AS ALIAS_ENGLISH
    ,od.OBJECT_NAME AS DIMENSION_NAME
FROM
    HSP_MEMBER m
INNER JOIN 
    HSP_OBJECT o ON m.MEMBER_ID = o.OBJECT_ID
INNER JOIN
    HSP_OBJECT op ON o.PARENT_ID = op.OBJECT_ID
INNER JOIN
    HSP_OBJECT od ON m.DIM_ID = od.OBJECT_ID
LEFT JOIN
    (   SELECT
            oa.OBJECT_NAME AS ALIAS_NAME
            ,a.MEMBER_ID
        FROM
            HSP_ALIAS a
        INNER JOIN
            HSP_OBJECT oa ON a.ALIAS_ID = oa.OBJECT_ID
        INNER JOIN
            HSP_OBJECT oat ON a.ALIASTBL_ID = oat.OBJECT_ID
        WHERE
            oat.OBJECT_NAME = 'Default')
     a1 ON m.MEMBER_ID = a1.MEMBER_ID
LEFT JOIN
    (   SELECT
            oa.OBJECT_NAME AS ALIAS_NAME
            ,a.MEMBER_ID
        FROM
            HSP_ALIAS a
        INNER JOIN
            HSP_OBJECT oa ON a.ALIAS_ID = oa.OBJECT_ID
        INNER JOIN
            HSP_OBJECT oat ON a.ALIASTBL_ID = oat.OBJECT_ID
        WHERE
            oat.OBJECT_NAME = 'English')
     a2 ON m.MEMBER_ID = a2.MEMBER_ID
WHERE
    od.OBJECT_NAME = 'Product'
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

I know what you’re thinking. I cheated. And its true, I used a sub-query join for each of the aliases that I knew existed. Not very dynamic, but it gets the job done. So let’s look at the results:

That’s it for aliases. If you plan to use this type of query for other downstream targets, like say an Essbase cube, you may want to consider putting in some code to compare the two aliases. Essbase will not allow non-unique aliases even for the same member. Next up in our series will cover UDA’s. Stay tuned…

The Planning Repository: HSP_MEMBER (Part 1 – The Table)

July 24, 2015 // Brian Marshall // Hyperion Planning, Planning Repository Tags: Hyperion Planning, Planning Repository 1 Response

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).

In our last post, we covered the HSP_DIMENSION table. HSP_DIMENSION is the starting point for all things meta-data, but no actual members live there. Enter the HSP_MEMBER table. Any member added to your application will be added to this table, regardless of the dimension. This table contains all of the properties common to all dimensions whether they are built-in (Account, Entity, Etc.) or custom. This will be part one of a two-part series related to members. There are too many other tables to get into for just one post. Let’s take a look at the table structure:

Field Name	SQL Server Type	Oracle Type	Description
MEMBER_ID	int	NUMBER(38,0)	The OBJECT_ID of the member.
DIM_ID	int	NUMBER(38,0)	The OBJECT_ID of the dimension to which the member belongs.
DATA_STORAGE	smallint	NUMBER(38,0)	The data storage type to be used in Essbase for members without formulas (members with formulas have another table). See the reference table below for details.
TWOPASS_CALC	smallint	NUMBER(38,0)	The Essbase two-pass calculation property. 0 = not two-pass 1 = two-pass
CONSOL_OP	bigint	NUMBER	The consolidation operator for the member. This is actually a bitmask that allows for the storage of multiple operators across plan types in a single integer. 0 = Add 1 = Subtract 2 = Multiply 3 = Divide 4 = Percentage 5 = Ignore 6 = Never
USED_FOR_CONSOL	smallint	NUMBER(38,0)	No longer used.
HAS_MBR_FX	smallint	NUMBER(38,0)	Tells us if the member has a formula. The formula itself does not exist in this table as members can have more than one formula. 0 = no formula 1 = formula
BASE_MBRID	int	NUMBER(38,0)	If the member is shared, then this contains the prototype member ID from the HSP_OBJECT table.
ENABLED_FOR_PM	smallint	NUMBER(38,0)	Tells us if the member has been enabled for process management (workflow). 0 = not process management enabled 1 = process management enabled (table default)
PS_MEMBER_ID	int	NUMBER(38,0)	Planning specific member ID that identifies predefined members used for custom components such as workforce (direct from OracleÉstill not exactly sure what this is for).
DATA_TYPE	int	NUMBER(38,0)	The data type of the members. 0 = unspecified 1 = currency 2 = non-currency 3 = percentage 4 = smart list 5 = date 6 = text
ENUMERATION_ID	int	NUMBER(38,0)	The ID of the smart list for the members. This links back to HSP_OBJECT and HSP_ENUMERATION.
USED_IN	int	NUMBER(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.
HIERARCHY_TYPE	int	NUMBER(38,0)	This is a new column that is supposed to pertain to ASO plan types. I'm working on understanding what it means.

If you have ever looked at this table in past releases, you will notice a pretty big difference. The new field CONSOL_OP has replaced all of the CONSOL_OP# fields. We’ll touch on that in a bit. The other change you will see is that they added a new field named HIERARCHY_TYPE. This field was added to support ASO plan types as the hierarchies in ASO can be either Stored or Dynamic. What you won’t see in this table are some important items: the actual member name, the member name of the parent, aliases, attributes, UDA’s, formulas, and properties related to accounts, entities, scenarios, and versions. Given that almost everything I just listed is in another table of its very own…this is a multi-part post.

Let’s start by querying just our basic member information that every member in Planning will have. To do this we just need two tables: HSP_MEMBER (for the member information) and HSP_OBJECT (for the member name and the member name of the parent). So let’s start with an easy query to get the member name, parent name, and a few of the simple to decode properties:

SQL

32 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
SELECT
    o.OBJECT_NAME AS MEMBER_NAME
    ,op.OBJECT_NAME AS PARENT_NAME
    ,od.OBJECT_NAME AS DIMENSION_NAME
    ,CASE m.DATA_STORAGE
        WHEN 0 THEN 'Store Data'
        WHEN 1 THEN 'Never Share'
        WHEN 2 THEN 'Label Only'
        WHEN 3 THEN 'Shared Member'
        WHEN 4 THEN 'Dynamic Calc and Store'
        WHEN 5 THEN 'Dynamic'
     END AS DATA_STORAGE
     ,CASE m.DATA_TYPE
        WHEN 0 THEN 'Unspecified'
        WHEN 1 THEN 'Currency'
        WHEN 2 THEN 'Non-currency'
        WHEN 3 THEN 'Percentage'
        WHEN 4 THEN 'Enum'
        WHEN 5 THEN 'Date'
        WHEN 6 THEN 'Text'
        ELSE 'Unspecified'
     END AS DATA_TYPE
FROM
    HSP_MEMBER m
INNER JOIN 
    HSP_OBJECT o ON m.MEMBER_ID = o.OBJECT_ID
INNER JOIN
    HSP_OBJECT op ON o.PARENT_ID = op.OBJECT_ID
INNER JOIN
    HSP_OBJECT od ON m.DIM_ID = od.OBJECT_ID
WHERE
    od.OBJECT_NAME = 'Product'
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

So if we look at the query, there are several things of note. First, we have three joins, all of which are the HSP_OBJECT table. The first join is on MEMBER_ID. This join provides us with access to the member name and the OBJECT_ID of the parent member. The second joins the parents OBJECT_ID (PARENT_ID) from the first join to get the actual member name of the parent. And the third joins the dimension’s OBJECT_ID (DIM_ID) from the HSP_MEMBER table to give us the actual name of the dimension.

After we get everything joined, we just need to decode a few of the simple fields. We use simple case statements for DATA_STORAGE and DATA_TYPE. It should also be noted that the DATA_STORAGE here is not necessarily that useful. Because Planning allows us to have a different data storage for each plan type, we will have another table to look at this later. This is the main setting from the member properties screen only.

So let’s look at the results.

SQL Server (Management Studio):

Oracle (SQL Developer):

That was easy…what’s the big deal? Oh wait…we still don’t have consolidation operators. In 11.1.2.2 and earlier, this was a simple decode. There were separate columns for each plan type. Starting in 11.1.2.3, they changed the structure of HSP_MEMBER. We now have just one CONSOL_OP field and it uses a bitmask (again). Even worse, this bitmask contains multiple values, because figuring out one value wasn’t painful enough!

So how do we store multiple values in one integer? In this instance, they are again using the bitmask using three bits per value. So to store a decimal 1, it becomes 001. As we combine multiple values, we do so going from right to left. So if I want to store decimal 1 and decimal 2 in that order, the value becomes 010001. This value is then stored as an integer. I’ve noticed that the integer stored is actually quite a bit bigger than just the boxes I’ve checked, but for our purposes today, we don’t care. So how do we actually determine the consolidation operator for each of our plan types?

For the first plan type, this is an easy operation. We can just use our bitwise operator or BITAND function to check the value without any trouble. The real difficulty comes in when we try to do this for the second position and beyond. The good news is we know that each value is stored in 3 bits. This means we just need to shift over three bits to check the next value. To do that, we just multiply the number by 2 to the power of the number of positions we want to shift. So for the second plan type, we shift 2 to the power of 3. For our third plan type we shift 2 to the power of 6. But one last thing. Before we do this, we should check the USED_IN field to verify that we even need to check the operator. So here goes:

SQL Server:

SQL

69 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
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
SELECT
    o.OBJECT_NAME AS MEMBER_NAME
    ,op.OBJECT_NAME AS PARENT_NAME
    ,od.OBJECT_NAME AS DIMENSION_NAME
    ,CASE m.DATA_STORAGE
        WHEN 0 THEN 'Store Data'
        WHEN 1 THEN 'Never Share'
        WHEN 2 THEN 'Label Only'
        WHEN 3 THEN 'Shared Member'
        WHEN 4 THEN 'Dynamic Calc and Store'
        WHEN 5 THEN 'Dynamic'
     END AS DATA_STORAGE
     ,CASE m.DATA_TYPE
        WHEN 0 THEN 'Unspecified'
        WHEN 1 THEN 'Currency'
        WHEN 2 THEN 'Non-currency'
        WHEN 3 THEN 'Percentage'
        WHEN 4 THEN 'Enum'
        WHEN 5 THEN 'Date'
        WHEN 6 THEN 'Text'
        ELSE 'Unspecified'
     END AS DATA_TYPE
     ,CONSOL_OP
    ,CASE WHEN m.USED_IN & 1 = 1 THEN 
        CASE 
            WHEN m.CONSOL_OP & 6 = 6 THEN '^' 
            WHEN m.CONSOL_OP & 5 = 5 THEN '~' 
            WHEN m.CONSOL_OP & 4 = 4 THEN '%' 
            WHEN m.CONSOL_OP & 3 = 3 THEN '/' 
            WHEN m.CONSOL_OP & 2 = 2 THEN '*' 
            WHEN m.CONSOL_OP & 1 = 1 THEN '-' 
            ELSE '+' END
        ELSE
            NULL
        END AS CONSOL1_OP
    ,CASE WHEN m.USED_IN & 2 = 2 THEN 
        CASE 
            WHEN m.CONSOL_OP & POWER(2,3)*6 = POWER(2,3)*6 THEN '^' 
            WHEN m.CONSOL_OP & POWER(2,3)*5 = POWER(2,3)*5 THEN '~' 
            WHEN m.CONSOL_OP & POWER(2,3)*4 = POWER(2,3)*4 THEN '%' 
            WHEN m.CONSOL_OP & POWER(2,3)*3 = POWER(2,3)*3 THEN '/' 
            WHEN m.CONSOL_OP & POWER(2,3)*2 = POWER(2,3)*2 THEN '*' 
            WHEN m.CONSOL_OP & POWER(2,3)*1 = POWER(2,3)*1 THEN '-' 
            ELSE '+' END
        ELSE
            NULL
        END AS CONSOL2_OP
    ,CASE WHEN m.USED_IN & 4 = 4 THEN 
        CASE 
            WHEN m.CONSOL_OP & POWER(2,6)*6 = POWER(2,6)*6 THEN '^' 
            WHEN m.CONSOL_OP & POWER(2,6)*5 = POWER(2,6)*5 THEN '~' 
            WHEN m.CONSOL_OP & POWER(2,6)*4 = POWER(2,6)*4 THEN '%' 
            WHEN m.CONSOL_OP & POWER(2,6)*3 = POWER(2,6)*3 THEN '/'
            WHEN m.CONSOL_OP & POWER(2,6)*2 = POWER(2,6)*2 THEN '*' 
            WHEN m.CONSOL_OP & POWER(2,6)*1 = POWER(2,6)*1 THEN '-' 
            ELSE '+' END
        ELSE
            NULL
        END AS CONSOL3_OP
FROM
    HSP_MEMBER m
INNER JOIN 
    HSP_OBJECT o ON m.MEMBER_ID = o.OBJECT_ID
INNER JOIN
    HSP_OBJECT op ON o.PARENT_ID = op.OBJECT_ID
INNER JOIN
    HSP_OBJECT od ON m.DIM_ID = od.OBJECT_ID
WHERE
    od.OBJECT_NAME = 'Account'
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

Oracle:

SQL

69 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
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
SELECT
  o.OBJECT_NAME AS MEMBER_NAME
  ,op.OBJECT_NAME AS PARENT_NAME
  ,od.OBJECT_NAME AS DIMENSION_NAME
  ,CASE m.DATA_STORAGE
    WHEN 0 THEN 'Store Data'
    WHEN 1 THEN 'Never Share'
    WHEN 2 THEN 'Label Only'
    WHEN 3 THEN 'Shared Member'
    WHEN 4 THEN 'Dynamic Calc and Store'
    WHEN 5 THEN 'Dynamic'
    END AS DATA_STORAGE
  ,CASE m.DATA_TYPE
    WHEN 0 THEN 'Unspecified'
    WHEN 1 THEN 'Currency'
    WHEN 2 THEN 'Non-currency'
    WHEN 3 THEN 'Percentage'
    WHEN 4 THEN 'Enum'
    WHEN 5 THEN 'Date'
    WHEN 6 THEN 'Text'
    ELSE 'Unspecified'
    END AS DATA_TYPE
  ,CONSOL_OP
  ,CASE WHEN BITAND(m.USED_IN,1) = 1 THEN 
    CASE 
      WHEN BITAND(m.CONSOL_OP,6) = 6 THEN '^' 
      WHEN BITAND(m.CONSOL_OP,5) = 5 THEN '~' 
      WHEN BITAND(m.CONSOL_OP,4) = 4 THEN '%' 
      WHEN BITAND(m.CONSOL_OP,3) = 3 THEN '/' 
      WHEN BITAND(m.CONSOL_OP,2) = 2 THEN '*' 
      WHEN BITAND(m.CONSOL_OP,1) = 1 THEN '-' 
      ELSE '+' END
    ELSE
      NULL
    END AS CONSOL1_OP
  ,CASE WHEN BITAND(m.USED_IN,2) = 2 THEN 
    CASE 
      WHEN BITAND(m.CONSOL_OP,POWER(2,3)*6) = POWER(2,3)*6 THEN '^' 
      WHEN BITAND(m.CONSOL_OP,POWER(2,3)*5) = POWER(2,3)*5 THEN '~' 
      WHEN BITAND(m.CONSOL_OP,POWER(2,3)*4) = POWER(2,3)*4 THEN '%' 
      WHEN BITAND(m.CONSOL_OP,POWER(2,3)*3) = POWER(2,3)*3 THEN '/' 
      WHEN BITAND(m.CONSOL_OP,POWER(2,3)*2) = POWER(2,3)*2 THEN '*' 
      WHEN BITAND(m.CONSOL_OP,POWER(2,3)*1) = POWER(2,3)*1 THEN '-' 
      ELSE '+' END
    ELSE
      NULL
    END AS CONSOL2_OP
  ,CASE WHEN BITAND(m.USED_IN,4) = 4 THEN 
  CASE 
    WHEN BITAND(m.CONSOL_OP,POWER(2,6)*6) = POWER(2,6)*6 THEN '^' 
    WHEN BITAND(m.CONSOL_OP,POWER(2,6)*5) = POWER(2,6)*5 THEN '~' 
    WHEN BITAND(m.CONSOL_OP,POWER(2,6)*4) = POWER(2,6)*4 THEN '%' 
    WHEN BITAND(m.CONSOL_OP,POWER(2,6)*3) = POWER(2,6)*3 THEN '/' 
    WHEN BITAND(m.CONSOL_OP,POWER(2,6)*2) = POWER(2,6)*2 THEN '*' 
    WHEN BITAND(m.CONSOL_OP,POWER(2,6)*1) = POWER(2,6)*1 THEN '-' 
    ELSE '+' END
  ELSE
    NULL
  END AS CONSOL3_OP
FROM
    HSP_MEMBER m
INNER JOIN 
    HSP_OBJECT o ON m.MEMBER_ID = o.OBJECT_ID
INNER JOIN
    HSP_OBJECT op ON o.PARENT_ID = op.OBJECT_ID
INNER JOIN
    HSP_OBJECT od ON m.DIM_ID = od.OBJECT_ID
WHERE
    od.OBJECT_NAME = 'Account'
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

As you can see, for the first plan type, we just use a regular & or BITAND to determine the consolidation operator. For the second and third plan types we use the power function with our base number of 2 and the number of positions we want to move to the left (if we wanted to go to the right, we would divide by the power function).

Before we look at the results, we have just a few other important notes. Because a negative test by both the & and BITAND function returns a 0, we can’t test for the value of 0, which is of course our most common operator (+). Instead we have to exclude all other options that we can test for. Assuming all of our other options fail, it must be 0 (+).

Another similar issue results in our reverse order case statement. Because case statement stop evaluating at the first successful case, we have to look at our bigger numbers first. For instance, if 3 (011) or 5 (101) are compared to 1 (001) first, it would always evaluate to true. Technically, 001 will fit into 011 and 101. Of course that’s not what we’re looking for, so I just chose to test from biggest to smallest. I haven’t been a DBA in a long…long time. So for those of you out there smarter than me, let me know if there’s a better way!

And finally…let’s look at the results:

SQL Server (Management Studio):

Oracle (SQL Developer):

Works great! You can add as many plan types as you want to this query with the examples above. And that’s enough for one post. In part two, we’ll go find all of those other important things that are missing from the HSP_MEMBER table.

The Planning Repository: HSP_DIMENSION

July 5, 2015 // Brian Marshall // Planning Repository Tags: Hyperion Planning, Planning Repository 1 Response

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).

In our last post, we covered the HSP_OBJECT table. Now that we’ve covered the basics, let’s dig a little deeper and start to look at how Planning manages meta-data inside of our applications. The starting point for this is the HSP_DIMENSION table. This table will have a pretty light set of data (just a few more rows than the number of dimensions in your app) but has quite a few properties. Each of the properties for the dimensions that you set in the GUI will be found here. Let’s take a look at the table structure:

Field Name	SQL Server Type	Oracle Type	Description
DIM_ID	int	NUMBER(38,0)	The OBJECT_ID of the dimension.
ENFORCE_SECURITY	int	NUMBER(38,0)	Determines if security is being enforced on this dimension. 0 = Don't Enforce Security 1 = Enforce Security
DIM_TYPE	smallint	NUMBER(38,0)	The type of dimension. 0 = None 1 = Account 2 = Time 3 = Entity 7 = Entity Hierarchy
DENSITY	int	NUMBER(38,0)	No longer used.
USED_IN	int	NUMBER(38,0)	The plan type usage for the dimension This is a bitmask that tells us which plan types are used in a single integer.
DIM_EDITOR	smallint	NUMBER(38,0)	Determines if the standard dimension editor can be used. 0 = Non-standard dimension, like period, scenario, etc. 1 = Standard dimension that can be modified with the standard editor , like account, entity, and custom dimensions
DENSITY1	smallint	NUMBER(38,0)	The Essbase setting for density of the dimension. 0 = Dense 1 = Sparse
DENSITY2	smallint	NUMBER(38,0)	See above
DENSITY3	smallint	NUMBER(38,0)	See above
DENSITY4	smallint	NUMBER(38,0)	See above
DENSITY5	smallint	NUMBER(38,0)	See above
DENSITY6	smallint	NUMBER(38,0)	See above
DENSITY7	smallint	NUMBER(38,0)	See above
POSITION1	smallint	NUMBER(38,0)	The Essbase setting for the position of the dimension. The position hear should line up with the number in the performance section of the GUI.
POSITION2	smallint	NUMBER(38,0)	See above
POSITION3	smallint	NUMBER(38,0)	See above
POSITION4	smallint	NUMBER(38,0)	See above
POSITION5	smallint	NUMBER(38,0)	See above
POSITION6	smallint	NUMBER(38,0)	See above
POSITION7	smallint	NUMBER(38,0)	See above
POSITION8	smallint	NUMBER(38,0)	See above
POSITION9	smallint	NUMBER(38,0)	See above
POSITION10	smallint	NUMBER(38,0)	See above
POSITION11	smallint	NUMBER(38,0)	See above
POSITION12	smallint	NUMBER(38,0)	See above
POSITION13	smallint	NUMBER(38,0)	See above
POSITION14	smallint	NUMBER(38,0)	See above
POSITION15	smallint	NUMBER(38,0)	See above
ENUM_ORDER1	smallint	NUMBER(38,0)	The evaluation order of the dimension. The enum name comes Smart Lists which are stored in the HSP_ENUMERATION and HSP_ENUMERATION_ENTRY tables.
ENUM_ORDER2	smallint	NUMBER(38,0)	See above
ENUM_ORDER3	smallint	NUMBER(38,0)	See above
ENUM_ORDER4	smallint	NUMBER(38,0)	See above
ENUM_ORDER5	smallint	NUMBER(38,0)	See above
ENUM_ORDER6	smallint	NUMBER(38,0)	See above
ENUM_ORDER7	smallint	NUMBER(38,0)	See above
ENUM_ORDER8	smallint	NUMBER(38,0)	See above
ENUM_ORDER9	smallint	NUMBER(38,0)	See above
ENUM_ORDER10	smallint	NUMBER(38,0)	See above
ENUM_ORDER11	smallint	NUMBER(38,0)	See above
ENUM_ORDER12	smallint	NUMBER(38,0)	See above
ENUM_ORDER13	smallint	NUMBER(38,0)	See above
ENUM_ORDER14	smallint	NUMBER(38,0)	See above
ENUM_ORDER15	smallint	NUMBER(38,0)	See above
DRIVER_DIM_ID	int	NUMBER(38,0)	The OBJECT_ID of the dimension configured for data loads in Planning.
DISPLAY_OPTION	int	NUMBER(38,0)	The display option selected for the dimension. 0 or null = Member name 1 = Alias 2 = Member name and Alias 3 = Alias and Member name 4 = Member count (I think this legacy) 5 = Member name and Description (I think this is legacy)

If you’ve looked at this table in the past, not too much has changed. There are a few interesting things to mention. First let’s look at the USED_IN field. This field is a bitmask. So what does that mean? It means that Oracle is the devil. That’s what it means. Bitmasks are among the most annoying things you can run across when decoding someone else’s database. But here we are, so let’s take a look. The developers of Planning have chosen to use a single integer field to tell us all of the plan types that a specific dimension can be part of. This means that we have up to 13 different pieces of information that are stored in a single integer. So how do they do this? The easiest way to illustrate this is with a table. Here we see how we would represent all 13 plan types numerically:

Plan Type	Integer	Example
Plan Type 1	1	Plan1
Plan Type 2	2	Plan2
Plan Type 3	4	Plan3
Plan Type 4	8	Wrkforce
Plan Type 5	16	Capex
Plan Type 6	32	Project
Plan Type 7	64	PlanASO1
Plan Type 8	128	PlanASO2
Plan Type 9	256	PlanASO3
Plan Type 10	512	PlanASO4
Plan Type 11	1024	PlanASO5
Plan Type 12	2048	PlanASO6
Plan Type 13	4096	PlanASO7

So how do all of these become a single integer? Simple math. Add up the ones that the dimension should be used in. So let’s run through a few examples:

Plan Types 1 and 3: 1 + 4 = 5

Plan Types 1, 2, and 3: 1 + 2 + 4 = 7

Plan Types 1 – 13: 1 + 2 + 4 + 8 + 16 + 32 + 64 + 128 + 256 + 1048 + 2048 + 4096 = 8191

So that’s all great, but how do I determine if the plan type is used without having to build a giant set of logic? The bitwise operator! Basically we ask if the number is included in our sum. For instance, is if my sum is 5, is Plan Type 2 included:

SQL

1 lines

1
SELECT 5 & 2
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

This will return a 0 because 5 is a combination of 1 and 4 and does not include 2.

Let’s try a different example that returns a different an affirmative response. If my sum is 7, is Plan Type 2 included:

SQL

1 lines

1
SELECT 7 & 2
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

This will return 2. It returns 2 because 7 does include 2. So we can basically assume if I use the bitwise & operator and the result is not 0, then its included.

Wow…this post is getting long. But hey, let’s keep going anyway, if you are still awake now, you must really want or need to know about how this bitwise thing works.

So how do we use this in a practical way? Let’s take a look at the HSP_PLAN_TYPE table. I won’t waste any space showing the structure of this table because it’s basically the same as my sample above. The plan types by name and their numerical equivalent. So here’s a practical example of how we can determine all of the plan types that a dimension belongs to.

SQL Server:

SQL

6 lines

1
2
3
4
5
6
SELECT 
    TYPE_NAME
    ,PLAN_TYPE
    ,CASE WHEN (SELECT USED_IN FROM HSP_DIMENSION WHERE DIM_ID = 31) & PLAN_TYPE = 0 THEN 'Not Used' ELSE 'Used' END AS UsedDecode
FROM
    HSP_PLAN_TYPE
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

Oracle:

SQL

6 lines

1
2
3
4
5
6
SELECT 
    TYPE_NAME
    ,PLAN_TYPE
    ,CASE WHEN BITAND((SELECT USED_IN FROM HSP_DIMENSION WHERE DIM_ID = 53702),PLAN_TYPE) = 0 THEN 'Not Used' ELSE 'Used' END AS UsedDecode
FROM
    HSP_PLAN_TYPE
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

Both queries should return a list of all of our Plan Types and whether or not the dimension in our where clause is used:

SQL Server (Management Studio):

Oracle (SQL Developer):

So now that we have made it past that diversion, let’s keep looking at our table. The rest of the dimension table is pretty well described by the table at the very beginning of this post (does anyone remember that far back?). But I do want to note a few interesting things. First, you will notice that we have only 7 density fields but for position and enumeration we have 15. Why? I scratched my head for a minute on this one, but then it hit me…ASO doesn’t care. So basically the repository support 7 BSO plan types and and additional 8 ASO plan types. Now if we look at the actual limitations in Planning, we can max out Planning with 13 plan types (which you can see in our sample table above). So we basically have an extra BSO plan type and an extra ASO plan type. It’s also possible that they use these extra fields for some other system purpose that I’m not aware of. If you know, drop me a comment!

So there you have it…an unexpected dive into some pretty technical workings of SQL code just to look at our dimensions. Now that we’ve had a look at the dimensions themselves, we can start looking at the members…in our next marathon post!

The Planning Repository: HSP_OBJECT and HSP_OBJECT_TYPE

March 13, 2015 // Brian Marshall // Hyperion Planning, Planning Repository Tags: Hyperion Planning, Planning Repository 1 Response

Welcome to the first of a long series of posts describing the Planning Repository. The point of this series is to describe many of the tables and their purpose inside of the relational repository that supports your Hyperion Planning applications. Additionally, we’ll take a look at things like deleting a dimension or years from your application.

First…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).

We’ll start the series with the HSP_OBJECT table. The HSP_OBJECT table is essentially the foundation for the repository. Every object in Planning (forms, dimension members, task lists, etc.) will have a record in this table. Let’s start by taking a look at the columns that make up the HSP_OBJECT table:

Field Name	SQL Server Type	Oracle Type	Description
OBJECT_ID	int	NUMBER(38,0)	The unique ID of the object. ÊThis is not an identity or auto-number field, rather it is actually generated internally by Hyperion Planning. ÊThe ID will be referenced throughout the repository in virtually every other table.
OBJECT_NAME	varchar(80)	VARCHAR2(80 CHAR)	The name of the object in Planning.
OBJECT_DESCRIPTION	int	NUMBER(38,0)	The key value from the HSP_STRINGS table that references the description of the object.
OBJECT_TYPE	smallint	NUMBER(38,0)	We'll cover this a little later when we get to the HSP_OBJECT_TYPE table. But in short, this is an integer value representing the type of object for the record (form, member, etc.). Sometimes this integer can be found in the HSP_OBJECT_TYPE table, sometimes it can't. More on this later.
PARENT_ID	int	NUMBER(38,0)	The OBJECT_ID of the parent for object.
OWNER_ID	int	NUMBER(38,0)	The OBJECT_ID of the owner of the object.
GENERATION	int	NUMBER(38,0)	The generation of the object if applicable and 0 if not. Objects like members and Planning Units have generations while things like forms do not.
POSITION	float	FLOAT	The sort-order for the object in relation to other objects in the hierarchy.
HAS_CHILDREN	smallint	NUMBER(38,0)	The flag that determines if a member is a leaf or not. If the member has children and is therefore not a leaf, the value is 1, otherwise it is 0. So if you are looking for leaves, look for 0.
MARKED_FOR_DELETE	smallint	NUMBER(38,0)	Members that have been deleted from Planning but not refreshed to Essbase will be marked for deletion with a value of 1. Otherwise this value should be 0.
CREATED	datetime	DATE	The date and time that the object was created.
MODIFIED	datetime	DATE	The date and time that the object was last modified.
MOVED	datetime	DATE	The date and time that the object was last moved.
OLD_NAME	varchar(80)	VARCHAR2(80 CHAR)	This field is used to maintain data in Essbase when a member is renamed. Prior to refreshing Essbase, the old name of the member will be here. Once the refresh is complete, the OBJECT_NAME and OLD_NAME will be the same.
SECCLASS_ID	int	NUMBER(38,0)	No longer used.
REMOVABLE	smallint	NUMBER(38,0)	The flag that determins if a member can be deleted. If an object can be deleted, the value is 1, otherwise it is 0.
MODIFIED_BY	varchar(80)	VARCHAR2(80 CHAR)	The user ID of the last user to modify the member. If you want any additional auditing of modifications to members, you have to hit the audit tables assuming you have them turned on.

This table, for all of its importance to Planning isn’t all that interesting. There are really only two fields that require a little more information than the table above. The first is OBJECT_TYPE and the second is DESCRIPTION. The OBJECT_TYPE field is of particular interest if you really look at the data. For instance, if we were to check the OBJECT_TYPE contents against the HSP_OBJECT_TYPE table, we’ll find that not all types have made it into the table. I’ve gone through and identified all of the types that I have in my sample application. Here’s the full list:

OBJECT_TYPE	TYPE_NAME from HSP_OBJECT_TYPE	TYPE_NAME Made Up By Brian
1	Folder
2	Dimension
4	Calendar
5	User
6	Group
7	Form
9	Currency
10	Alias
11	Cube
12	Planning Unit
23	NULL	Type for the parent of all task lists
24	NULL	Task List
25	NULL	Task List Item
31	Scenario
32	Account
33	Entity
34	Time Period
35	Version
38	Year
39	NULL	Planning Unit Hierarchy
45	Shared Member
50	User Defined Dimension Member
51	NULL	Planning Unit Selection
103	NULL	Menu
107	NULL	Composite Form
115	NULL	Calculation Manager Rule
116	NULL	Calculation Manager Rule Set
117	NULL	Calculation Manager Variable
118	NULL	Type for the parent of Calculation Manager Rules
119	NULL	Type for the parent of Calculation Manager Rule Sets
120	NULL	Type for the parent of Calculation Manager Variables
143	NULL	Good questionÉsomething related to Business Metrics

Now let’s talk about DESCRIPTION. This is the first occurence of the HSP_STRINGS table. This table itself is pretty simple. It’s just a list of strings used in various places inside of Planning. I believe the idea here was to save space in the HSP_OBJECT table. Descriptions could be quite (up to 255 characters) so why allocate space in a table for long strings when you can separate them out? So how do we actually see the description? A simple join:

SQL Server and Oracle Code:

SQL

23 lines

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT·
    OBJECT_ID
    ,OBJECT_NAME
    --,DESCRIPTION
    ,THE_STRING
    ,OBJECT_TYPE
    ,PARENT_ID
    ,OWNER_ID
    ,GENERATION
    ,POSITION
    ,HAS_CHILDREN
    ,MARKED_FOR_DELETE
    ,CREATED
    ,MODIFIED
    ,MOVED
    ,OLD_NAME
    ,SECCLASS_ID
    ,REMOVABLE
    ,MODIFIED_BY
FROM·
    HSP_OBJECT·o
LEFT·JOIN
    HSP_STRINGS·s·ON·o.DESCRIPTION·=·s.STRING_SEQ
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

The results of the query should render all of our objects along with their description (and every other field in the HSP_OBJECT table):

SQL Server (Management Studio):

Oracle (SQL Developer):

That’s it for the HSP_OBJECT table! Next we’ll start looking at the way Planning stores meta-data (the HSP_DIMENSION table first and then HSP_MEMBER).

Adding Dynamic Members from a Form in 11.1.2.4

February 9, 2015 // Brian Marshall // 11.1.2.4, Hyperion Planning Tags: 11.1.2.4, Hyperion Planning 3 Responses
In my last entry I demonstrated the use of dynamic members in Custom Plan Types. In today’s installment we’ll actually put dynamic members to a more practical use. The main benefit of dynamic members is to give the end-user the ability to add (or remove) their own members. But, if they have to go to the Business Rules section of Planning every time to do so, the process will get old in a hurry. Additionally, if you’ve never used menu’s in Planning, we’ll make excellent use of them today.

The first step in this process is to create out custom menus. Follow these steps to create the necessary menus:
1. Click Administration, then Manage, then Menus.
2. Click the Add Menu button.
3. Enter Manage Entities for the name and click OK.
4. Click on the newly created Manage Entities and click the Edit Menu button.
5. Click on the Add Child button.
6. Enter the following and click Save:
7. Click on the newly added Managed Entities parent menu item and click the Add Child button.
8. Enter the following and click Save (remember we created our business rule in Part 1):
9. Click on the newly added Add Entity child and click the Add Sibling button.
10. Enter the following and click Save:
Once we have our menu ready, we can create our form and add the newly created menus. Follow these steps to create the new form:
1. Create a new form.
2. Enter the following and click Next.
3. Modify your dimension to match the following and click Next.
4. Add Manage Entities to the Selected Menus list and click Finish.
5. Open the form and test out your new right-click menu.
Now you have a form that can be used to allow users to input their own members in a custom plan type!

The Planning Repository: HSP_MEMBER (Part 6 – Putting It All Together)

September 21, 2015 // Brian Marshall // Hyperion Planning, Planning Repository Tags: Hyperion Planning, Planning Repository No Responses

The Planning Repository: HSP_MEMBER (Part 5 – Attribute Associations)

September 14, 2015 // Brian Marshall // Hyperion Planning, Planning Repository Tags: Hyperion Planning, Planning Repository No Responses

The Planning Repository: Attribute Dimensions

September 9, 2015 // Brian Marshall // Hyperion Planning, Planning Repository Tags: Hyperion Planning, Planning Repository No Responses

The Planning Repository: HSP_MEMBER (Part 4 – HSP_MEMBER_FORMULA)

September 1, 2015 // Brian Marshall // Hyperion Planning, Planning Repository Tags: Hyperion Planning, Planning Repository No Responses

The Planning Repository: HSP_MEMBER (Part 3 – UDA’s)

August 24, 2015 // Brian Marshall // Hyperion Planning, Planning Repository Tags: Hyperion Planning, Planning Repository No Responses

The Planning Repository: HSP_MEMBER (Part 2 – HSP_ALIAS)

August 7, 2015 // Brian Marshall // Hyperion Planning, Planning Repository Tags: Hyperion Planning, Planning Repository No Responses

The Planning Repository: HSP_MEMBER (Part 1 – The Table)

July 24, 2015 // Brian Marshall // Hyperion Planning, Planning Repository Tags: Hyperion Planning, Planning Repository 1 Response

The Planning Repository: HSP_DIMENSION

July 5, 2015 // Brian Marshall // Planning Repository Tags: Hyperion Planning, Planning Repository 1 Response

The Planning Repository: HSP_OBJECT and HSP_OBJECT_TYPE

March 13, 2015 // Brian Marshall // Hyperion Planning, Planning Repository Tags: Hyperion Planning, Planning Repository 1 Response

Adding Dynamic Members from a Form in 11.1.2.4

February 9, 2015 // Brian Marshall // 11.1.2.4, Hyperion Planning Tags: 11.1.2.4, Hyperion Planning 3 Responses