Outline Export from Planning DB

Extracting metadata from the Hyperion Planning repository database.

Many times I need to do analysis based on the metadata loaded to Planning and this query has been very useful for me so I’d like to share it and maybe break it down for others to take a look at.
Most of the hard information here I actual learned from reading this blog:

http://www.hyperionepm.com/the-planning-repository-hsp_member-part-1-of-2/

The entire series on that site is highly useful I definitely recommend reading the whole thing.
What I’ve done is maybe add a few bells and whistles that suited my specific needs. Unfortunately this was written only for Oracle DB which is what I have to work with but most of these features can probably be ported to TSQL without too much difficulty.

To begin one of the items I like to have for analysis is the levels. It helps for doing queries for example, on all level 2 members that are under X department.
This may not be the most effective SQL way to getting a level table but I wanted to make sure that it was dynamic and would grow with the levels if more are added.

WITH CTE_LEV_TABLE_1 AS (
	SELECT
		OD.OBJECT_NAME AS DIMNAME,
		SYS_CONNECT_BY_PATH(O.OBJECT_NAME, '|') AS PATH,
		LEVEL AS LEV_NUM,
		O.OBJECT_NAME AS MEMBER_NAME,
		OP.OBJECT_NAME AS PARENT_NAME
	FROM
		HSP_OBJECT O
		INNER JOIN HSP_MEMBER M
			ON O.OBJECT_ID = M.MEMBER_ID
		INNER JOIN HSP_OBJECT OD
			ON M.DIM_ID = OD.OBJECT_ID
			AND OD.OBJECT_TYPE = 2
		INNER JOIN HSP_OBJECT OP
			ON O.PARENT_ID = OP.OBJECT_ID
	START WITH
		O.HAS_CHILDREN = 0
	CONNECT BY NOCYCLE PRIOR
		O.PARENT_ID = O.OBJECT_ID
)

This uses what are called Hierarchical Queries and I recommend you read up on them. They are pretty cool.

Here is an example of the output produced:

Doesn’t look that pretty yet right? But wait until we clean it up. This becomes a lot more useful. I like the LISTAGG function for combining these levels into a single member. It cuts down on duplicates and you can do analysis on levels. Just have to remember to use LIKE with wildcards.

WITH CTE_LEV_TABLE_1 AS (
	SELECT
		OD.OBJECT_NAME AS DIMNAME,
		SYS_CONNECT_BY_PATH(O.OBJECT_NAME, '|') AS PATH,
		LEVEL AS LEV_NUM,
		O.OBJECT_NAME AS MEMBER_NAME,
		OP.OBJECT_NAME AS PARENT_NAME
	FROM
		HSP_OBJECT O
		INNER JOIN HSP_MEMBER M
			ON O.OBJECT_ID = M.MEMBER_ID
		INNER JOIN HSP_OBJECT OD
			ON M.DIM_ID = OD.OBJECT_ID
			AND OD.OBJECT_TYPE = 2
		INNER JOIN HSP_OBJECT OP
			ON O.PARENT_ID = OP.OBJECT_ID
	START WITH
		O.HAS_CHILDREN = 0
	CONNECT BY NOCYCLE PRIOR
		O.PARENT_ID = O.OBJECT_ID
),
CTE_LEV_TABLE_2 AS (
	SELECT
		DISTINCT
		DIMNAME,
		MEMBER_NAME,
		PARENT_NAME,
		--NEED TO REDUCE THE NUMBER BY 1 BECAUSE
		-- ORACLE AND ESSBASE DEFINE LEVEL DIFFERENTLY
		LEV_NUM - 1 AS LEV_NUM
	FROM CTE_LEV_TABLE_1
),
CTE_LEV_TABLE_PIVOTED AS (
    SELECT
        DIMNAME,
        MEMBER_NAME,
        PARENT_NAME,
        LISTAGG(LEV_NUM,',') WITHIN GROUP (ORDER BY LEV_NUM) AS LEV_NUM
    FROM
        CTE_LEV_TABLE_2
    GROUP BY
        DIMNAME,
        MEMBER_NAME,
        PARENT_NAME
)

We’ll also have to pull out all the ATTRIBUTES and UDAs.

The both follow the same basic idea of LISTAGG as such:

ATTRIBUTE:

WITH CTE_ATTR AS (
    SELECT
        mta.MEMBER_ID, LISTAGG(o.OBJECT_NAME,', ') WITHIN GROUP (ORDER BY o.OBJECT_ID) AS ATTRIBUTE_LIST
    FROM
        HSP_MEMBER_TO_ATTRIBUTE mta
        INNER JOIN HSP_OBJECT o
            ON mta.ATTR_MEM_ID = o.OBJECT_ID
    GROUP BY
        mta.MEMBER_ID
)

UDA:

WITH  CTE_UDA AS (
    SELECT m.MEMBER_ID, LISTAGG(u.UDA_VALUE,', ') WITHIN GROUP (ORDER BY u.UDA_ID) AS UDA_LIST
    FROM
        HSP_MEMBER m
        LEFT OUTER JOIN HSP_MEMBER_TO_UDA mu
            ON m.MEMBER_ID = mu.MEMBER_ID
        LEFT OUTER JOIN HSP_OBJECT od
            ON M.DIM_ID = od.OBJECT_ID
        LEFT OUTER JOIN HSP_UDA u
            ON mu.UDA_ID = u.UDA_ID
    GROUP BY m.MEMBER_ID
)

Once we have the UDA and Attributes we’ll also need to pull out the plan types that have custom data storage. This is when we crack open the HSP_MEMBER_FORMULA table. If a plan type has a custom data storage that doesn’t match the default you’ll find it here.

We’re going to use a PIVOT here so that we can use make sure the data matches the columnar format used in SmartView or the OLU.

WITH CTE_PLAN_TYPE AS (
    SELECT
        PLAN_TYPE, TYPE_NAME, ROW_NUMBER() OVER (ORDER BY PLAN_TYPE) AS ORDNUM
    FROM
        HSP_PLAN_TYPE
),
CTE_UNPIVOTED_MF AS (
    SELECT
        MF.MEMBER_ID,
        ORDNUM AS PLAN_NUM,
        CASE MF.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 Calc'
        END AS DATA_STORAGE
    FROM
        HSP_MEMBER_FORMULA MF
        INNER JOIN CTE_PLAN_TYPE PT
            ON MF.PLAN_TYPE = PT.PLAN_TYPE
),
CTE_OVERRIDE_DS AS (
    SELECT
        MEMBER_ID, PLAN_1_DATA_STORAGE, PLAN_2_DATA_STORAGE, PLAN_3_DATA_STORAGE, PLAN_4_DATA_STORAGE, PLAN_5_DATA_STORAGE, PLAN_6_DATA_STORAGE
    FROM
        CTE_UNPIVOTED_MF MF
        PIVOT (
            MAX(DATA_STORAGE)
            FOR PLAN_NUM IN ('0' AS DEFAULT_DATA_STORAGE, '1' AS PLAN_1_DATA_STORAGE, '2' AS PLAN_2_DATA_STORAGE, '3' AS PLAN_3_DATA_STORAGE, '4' AS PLAN_4_DATA_STORAGE, '5' AS PLAN_5_DATA_STORAGE, '6' AS PLAN_6_DATA_STORAGE)
        )
)

SELECT * FROM CTE_OVERRIDE_DS

The last piece that was tricky was understanding how the CONSOL_OP is used to pack all the aggregation formulas for all the plan types. I would never have figured this out without the blog listed above but I did notice a pattern here.

Do you see it? Remember CTE_PLAN_TYPES? We used it above to pivot out the datastorage. Now it’s going to do double duty and help us separate the consolidation operators used by plan type.

Using this you can pull all your plan types in an ordered list Plan1 Plan2 etc.

And now you can use it here to pull the aggregations.

Here is the first one:

And the second:

You get the gist, repeat for as many plan types as you’d like. In my sample code below I’ve set it up for 6. If you have more than 6 plan types in use you can just increment the number by 1 and you’ll be good to go.

And finally we tie it all together.

The whole enchilada:

WITH CTE_LEV_TABLE_1 AS (
	SELECT
		OD.OBJECT_NAME AS DIMNAME,
		SYS_CONNECT_BY_PATH(O.OBJECT_NAME, '|') AS PATH,
		LEVEL AS LEV_NUM,
		O.OBJECT_NAME AS MEMBER_NAME,
		OP.OBJECT_NAME AS PARENT_NAME
	FROM
		HSP_OBJECT O
		INNER JOIN HSP_MEMBER M
			ON O.OBJECT_ID = M.MEMBER_ID
		INNER JOIN HSP_OBJECT OD
			ON M.DIM_ID = OD.OBJECT_ID
			AND OD.OBJECT_TYPE = 2
		INNER JOIN HSP_OBJECT OP
			ON O.PARENT_ID = OP.OBJECT_ID
	START WITH
		O.HAS_CHILDREN = 0
	CONNECT BY NOCYCLE PRIOR
		O.PARENT_ID = O.OBJECT_ID
),
CTE_LEV_TABLE_2 AS (
	SELECT
		DISTINCT
		DIMNAME,
		MEMBER_NAME,
		PARENT_NAME,
		LEV_NUM - 1 AS LEV_NUM
	FROM CTE_LEV_TABLE_1
),
CTE_LEV_TABLE_PIVOTED AS (
    SELECT
        DIMNAME,
        MEMBER_NAME,
        PARENT_NAME,
        LISTAGG(LEV_NUM,',') WITHIN GROUP (ORDER BY LEV_NUM) AS LEV_NUM
    FROM
        CTE_LEV_TABLE_2
    GROUP BY
        DIMNAME,
        MEMBER_NAME,
        PARENT_NAME
),
CTE_ATTR AS (
    SELECT
        mta.MEMBER_ID, LISTAGG(o.OBJECT_NAME,', ') WITHIN GROUP (ORDER BY o.OBJECT_ID) AS ATTRIBUTE_LIST
    FROM
        HSP_MEMBER_TO_ATTRIBUTE mta
        INNER JOIN HSP_OBJECT o
            ON mta.ATTR_MEM_ID = o.OBJECT_ID
    GROUP BY
        mta.MEMBER_ID
),
CTE_UDA AS (
    SELECT m.MEMBER_ID, LISTAGG(u.UDA_VALUE,', ') WITHIN GROUP (ORDER BY u.UDA_ID) AS UDA_LIST
    FROM
        HSP_MEMBER m
        LEFT OUTER JOIN HSP_MEMBER_TO_UDA mu
            ON m.MEMBER_ID = mu.MEMBER_ID
        LEFT OUTER JOIN HSP_OBJECT od
            ON M.DIM_ID = od.OBJECT_ID
        LEFT OUTER JOIN HSP_UDA u
            ON mu.UDA_ID = u.UDA_ID
    GROUP BY m.MEMBER_ID
),
CTE_PLAN_TYPE AS (
    SELECT
        PLAN_TYPE, TYPE_NAME, ROW_NUMBER() OVER (ORDER BY PLAN_TYPE) AS ORDNUM
    FROM
        HSP_PLAN_TYPE
),
CTE_UNPIVOTED_MF AS (
    SELECT
        MF.MEMBER_ID,
        ORDNUM AS PLAN_NUM,
        CASE MF.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 Calc'
        END AS DATA_STORAGE
    FROM
        HSP_MEMBER_FORMULA MF
        INNER JOIN CTE_PLAN_TYPE PT
            ON MF.PLAN_TYPE = PT.PLAN_TYPE
),
CTE_OVERRIDE_DS AS (
    SELECT
        MEMBER_ID, PLAN_1_DATA_STORAGE, PLAN_2_DATA_STORAGE, PLAN_3_DATA_STORAGE, PLAN_4_DATA_STORAGE, PLAN_5_DATA_STORAGE, PLAN_6_DATA_STORAGE
    FROM
        CTE_UNPIVOTED_MF MF
        PIVOT (
            MAX(DATA_STORAGE)
            FOR PLAN_NUM IN ('0' AS DEFAULT_DATA_STORAGE, '1' AS PLAN_1_DATA_STORAGE, '2' AS PLAN_2_DATA_STORAGE, '3' AS PLAN_3_DATA_STORAGE, '4' AS PLAN_4_DATA_STORAGE, '5' AS PLAN_5_DATA_STORAGE, '6' AS PLAN_6_DATA_STORAGE)
        )
),
CTE_FINAL AS (
    SELECT
        od.OBJECT_NAME AS DIMENSION,
        o.OBJECT_NAME AS MEMBER_NAME,
        o.OBJECT_ID AS MEMBER_ID,
        op.OBJECT_NAME AS PARENT_NAME,
        oa.OBJECT_NAME AS ALIAS_DEFAULT,
        o.POSITION,
        sq_uda.UDA_LIST,
        sq_attr.ATTRIBUTE_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 Calc'
        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,
        mf.FORMULA,
        (SELECT TYPE_NAME FROM CTE_PLAN_TYPE WHERE ORDNUM = 1) AS PLAN_1_NAME,
        CASE WHEN BITAND(m.USED_IN,(SELECT PLAN_TYPE FROM CTE_PLAN_TYPE WHERE ORDNUM = 1)) = 0 THEN 'false' ELSE 'true' END AS PLAN_1_VALID,
        CASE
            WHEN BITAND(m.USED_IN,(SELECT PLAN_TYPE FROM CTE_PLAN_TYPE WHERE ORDNUM = 1)) = (SELECT PLAN_TYPE FROM CTE_PLAN_TYPE WHERE ORDNUM = 1) THEN
                CASE
                    WHEN BITAND(m.CONSOL_OP,6*POWER(2,(3*(1-1)))) = 6*POWER(2,(3*(1-1))) THEN '^'
                    WHEN BITAND(m.CONSOL_OP,5*POWER(2,(3*(1-1)))) = 5*POWER(2,(3*(1-1))) THEN '~'
                    WHEN BITAND(m.CONSOL_OP,4*POWER(2,(3*(1-1)))) = 4*POWER(2,(3*(1-1))) THEN '%'
                    WHEN BITAND(m.CONSOL_OP,3*POWER(2,(3*(1-1)))) = 3*POWER(2,(3*(1-1))) THEN '/'
                    WHEN BITAND(m.CONSOL_OP,2*POWER(2,(3*(1-1)))) = 2*POWER(2,(3*(1-1))) THEN '*'
                    WHEN BITAND(m.CONSOL_OP,1*POWER(2,(3*(1-1)))) = 1*POWER(2,(3*(1-1))) THEN '-'
                    ELSE '+'
                END
            ELSE NULL
        END AS PLAN_1_AGGREGATION,
        COALESCE(
            ODS.PLAN_1_DATA_STORAGE,
            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 Calc'
            END
        ) AS PLAN_1_DATA_STORAGE,
        (SELECT TYPE_NAME FROM CTE_PLAN_TYPE WHERE ORDNUM = 2) AS PLAN_2_NAME,
        CASE WHEN BITAND(m.USED_IN,(SELECT PLAN_TYPE FROM CTE_PLAN_TYPE WHERE ORDNUM = 2)) = 0 THEN 'false' ELSE 'true' END AS PLAN_2_VALID,
        CASE
            WHEN BITAND(m.USED_IN,(SELECT PLAN_TYPE FROM CTE_PLAN_TYPE WHERE ORDNUM = 2)) = (SELECT PLAN_TYPE FROM CTE_PLAN_TYPE WHERE ORDNUM = 2) THEN
                CASE
                    WHEN BITAND(m.CONSOL_OP,6*POWER(2,(3*(2-1)))) = 6*POWER(2,(3*(2-1))) THEN '^'
                    WHEN BITAND(m.CONSOL_OP,5*POWER(2,(3*(2-1)))) = 5*POWER(2,(3*(2-1))) THEN '~'
                    WHEN BITAND(m.CONSOL_OP,4*POWER(2,(3*(2-1)))) = 4*POWER(2,(3*(2-1))) THEN '%'
                    WHEN BITAND(m.CONSOL_OP,3*POWER(2,(3*(2-1)))) = 3*POWER(2,(3*(2-1))) THEN '/'
                    WHEN BITAND(m.CONSOL_OP,2*POWER(2,(3*(2-1)))) = 2*POWER(2,(3*(2-1))) THEN '*'
                    WHEN BITAND(m.CONSOL_OP,1*POWER(2,(3*(2-1)))) = 1*POWER(2,(3*(2-1))) THEN '-'
                    ELSE '+'
                END
            ELSE NULL
        END AS PLAN_2_AGGREGATION,
        COALESCE(
            ODS.PLAN_2_DATA_STORAGE,
            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 Calc'
            END
        ) AS PLAN_2_DATA_STORAGE,
        (SELECT TYPE_NAME FROM CTE_PLAN_TYPE WHERE ORDNUM = 3) AS PLAN_3_NAME,
        CASE WHEN BITAND(m.USED_IN,(SELECT PLAN_TYPE FROM CTE_PLAN_TYPE WHERE ORDNUM = 3)) = 0 THEN 'false' ELSE 'true' END AS PLAN_3_VALID,
        CASE
            WHEN BITAND(m.USED_IN,(SELECT PLAN_TYPE FROM CTE_PLAN_TYPE WHERE ORDNUM = 3)) = (SELECT PLAN_TYPE FROM CTE_PLAN_TYPE WHERE ORDNUM = 3) THEN
                CASE
                    WHEN BITAND(m.CONSOL_OP,6*POWER(2,(3*(3-1)))) = 6*POWER(2,(3*(3-1))) THEN '^'
                    WHEN BITAND(m.CONSOL_OP,5*POWER(2,(3*(3-1)))) = 5*POWER(2,(3*(3-1))) THEN '~'
                    WHEN BITAND(m.CONSOL_OP,4*POWER(2,(3*(3-1)))) = 4*POWER(2,(3*(3-1))) THEN '%'
                    WHEN BITAND(m.CONSOL_OP,3*POWER(2,(3*(3-1)))) = 3*POWER(2,(3*(3-1))) THEN '/'
                    WHEN BITAND(m.CONSOL_OP,2*POWER(2,(3*(3-1)))) = 2*POWER(2,(3*(3-1))) THEN '*'
                    WHEN BITAND(m.CONSOL_OP,1*POWER(2,(3*(3-1)))) = 1*POWER(2,(3*(3-1))) THEN '-'
                    ELSE '+'
                END
            ELSE NULL
        END AS PLAN_3_AGGREGATION,
        COALESCE(
            ODS.PLAN_3_DATA_STORAGE,
            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 Calc'
            END
        ) AS PLAN_3_DATA_STORAGE,
        (SELECT TYPE_NAME FROM CTE_PLAN_TYPE WHERE ORDNUM = 4) AS PLAN_4_NAME,
        CASE WHEN BITAND(m.USED_IN,(SELECT PLAN_TYPE FROM CTE_PLAN_TYPE WHERE ORDNUM = 4)) = 0 THEN 'false' ELSE 'true' END AS PLAN_4_VALID,
        CASE
            WHEN BITAND(m.USED_IN,(SELECT PLAN_TYPE FROM CTE_PLAN_TYPE WHERE ORDNUM = 4)) = (SELECT PLAN_TYPE FROM CTE_PLAN_TYPE WHERE ORDNUM = 4) THEN
                CASE
                    WHEN BITAND(m.CONSOL_OP,6*POWER(2,(3*(4-1)))) = 6*POWER(2,(3*(4-1))) THEN '^'
                    WHEN BITAND(m.CONSOL_OP,5*POWER(2,(3*(4-1)))) = 5*POWER(2,(3*(4-1))) THEN '~'
                    WHEN BITAND(m.CONSOL_OP,4*POWER(2,(3*(4-1)))) = 4*POWER(2,(3*(4-1))) THEN '%'
                    WHEN BITAND(m.CONSOL_OP,3*POWER(2,(3*(4-1)))) = 3*POWER(2,(3*(4-1))) THEN '/'
                    WHEN BITAND(m.CONSOL_OP,2*POWER(2,(3*(4-1)))) = 2*POWER(2,(3*(4-1))) THEN '*'
                    WHEN BITAND(m.CONSOL_OP,1*POWER(2,(3*(4-1)))) = 1*POWER(2,(3*(4-1))) THEN '-'
                    ELSE '+'
                END
            ELSE NULL
        END AS PLAN_4_AGGREGATION,
        COALESCE(
            ODS.PLAN_4_DATA_STORAGE,
            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 Calc'
            END
        ) AS PLAN_4_DATA_STORAGE,
        (SELECT TYPE_NAME FROM CTE_PLAN_TYPE WHERE ORDNUM = 5) AS PLAN_5_NAME,
        CASE WHEN BITAND(m.USED_IN,(SELECT PLAN_TYPE FROM CTE_PLAN_TYPE WHERE ORDNUM = 5)) = 0 THEN 'false' ELSE 'true' END AS PLAN_5_VALID,
        CASE
            WHEN BITAND(m.USED_IN,(SELECT PLAN_TYPE FROM CTE_PLAN_TYPE WHERE ORDNUM = 5)) = (SELECT PLAN_TYPE FROM CTE_PLAN_TYPE WHERE ORDNUM = 5) THEN
                CASE
                    WHEN BITAND(m.CONSOL_OP,6*POWER(2,(3*(5-1)))) = 6*POWER(2,(3*(5-1))) THEN '^'
                    WHEN BITAND(m.CONSOL_OP,5*POWER(2,(3*(5-1)))) = 5*POWER(2,(3*(5-1))) THEN '~'
                    WHEN BITAND(m.CONSOL_OP,4*POWER(2,(3*(5-1)))) = 4*POWER(2,(3*(5-1))) THEN '%'
                    WHEN BITAND(m.CONSOL_OP,3*POWER(2,(3*(5-1)))) = 3*POWER(2,(3*(5-1))) THEN '/'
                    WHEN BITAND(m.CONSOL_OP,2*POWER(2,(3*(5-1)))) = 2*POWER(2,(3*(5-1))) THEN '*'
                    WHEN BITAND(m.CONSOL_OP,1*POWER(2,(3*(5-1)))) = 1*POWER(2,(3*(5-1))) THEN '-'
                    ELSE '+'
                END
            ELSE NULL
        END AS PLAN_5_AGGREGATION,
        COALESCE(
            ODS.PLAN_5_DATA_STORAGE,
            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 Calc'
            END
        ) AS PLAN_5_DATA_STORAGE,
        (SELECT TYPE_NAME FROM CTE_PLAN_TYPE WHERE ORDNUM = 6) AS PLAN_6_NAME,
        CASE WHEN BITAND(m.USED_IN,(SELECT PLAN_TYPE FROM CTE_PLAN_TYPE WHERE ORDNUM = 6)) = 0 THEN 'false' ELSE 'true' END AS PLAN_6_VALID,
        CASE
            WHEN BITAND(m.USED_IN,(SELECT PLAN_TYPE FROM CTE_PLAN_TYPE WHERE ORDNUM = 6)) = (SELECT PLAN_TYPE FROM CTE_PLAN_TYPE WHERE ORDNUM = 6) THEN
                CASE
                    WHEN BITAND(m.CONSOL_OP,6*POWER(2,(3*(6-1)))) = 6*POWER(2,(3*(6-1))) THEN '^'
                    WHEN BITAND(m.CONSOL_OP,5*POWER(2,(3*(6-1)))) = 5*POWER(2,(3*(6-1))) THEN '~'
                    WHEN BITAND(m.CONSOL_OP,4*POWER(2,(3*(6-1)))) = 4*POWER(2,(3*(6-1))) THEN '%'
                    WHEN BITAND(m.CONSOL_OP,3*POWER(2,(3*(6-1)))) = 3*POWER(2,(3*(6-1))) THEN '/'
                    WHEN BITAND(m.CONSOL_OP,2*POWER(2,(3*(6-1)))) = 2*POWER(2,(3*(6-1))) THEN '*'
                    WHEN BITAND(m.CONSOL_OP,1*POWER(2,(3*(6-1)))) = 1*POWER(2,(3*(6-1))) THEN '-'
                    ELSE '+'
                END
            ELSE NULL
        END AS PLAN_6_AGGREGATION,
        COALESCE(
            ODS.PLAN_6_DATA_STORAGE,
            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 Calc'
            END
        ) AS PLAN_6_DATA_STORAGE,
        o.GENERATION,
        sq_lev_num.LEV_NUM
    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 OUTER JOIN HSP_ALIAS a
            ON m.MEMBER_ID = a.MEMBER_ID
        LEFT OUTER JOIN HSP_OBJECT oa
            ON A.ALIAS_ID = oa.OBJECT_ID
        LEFT OUTER JOIN CTE_UDA sq_uda
            ON m.MEMBER_ID = sq_uda.MEMBER_ID
        LEFT OUTER JOIN HSP_MEMBER_FORMULA mf
            ON m.MEMBER_ID = mf.MEMBER_ID
            AND mf.PLAN_TYPE = 0
        LEFT OUTER JOIN HSP_MEMBER_TO_ATTRIBUTE mta
            ON m.MEMBER_ID = mta.MEMBER_ID
        LEFT OUTER JOIN CTE_ATTR sq_attr
            on m.MEMBER_ID = sq_attr.MEMBER_ID
        LEFT OUTER JOIN CTE_LEV_TABLE_PIVOTED sq_lev_num
            on o.OBJECT_NAME = sq_lev_num.MEMBER_NAME
            AND op.OBJECT_NAME = sq_lev_num.PARENT_NAME
            AND od.OBJECT_NAME = sq_lev_num.DIMNAME
        LEFT OUTER JOIN CTE_OVERRIDE_DS ODS
            ON m.MEMBER_ID = ODS.MEMBER_ID
    WHERE
        1=1

    ORDER BY
        o.GENERATION, o.POSITION
)

SELECT
    DIMENSION,
    POSITION,
    PARENT_NAME,
    MEMBER_NAME,
    MEMBER_ID,
    ALIAS_DEFAULT,
    UDA_LIST,
    ATTRIBUTE_LIST,
    DATA_STORAGE,
    DATA_TYPE,
    FORMULA,
    PLAN_1_NAME,
    PLAN_1_VALID,
    PLAN_1_AGGREGATION,
    PLAN_1_DATA_STORAGE,
    PLAN_2_NAME,
    PLAN_2_VALID,
    PLAN_2_AGGREGATION,
    PLAN_2_DATA_STORAGE,
    PLAN_3_NAME,
    PLAN_3_VALID,
    PLAN_3_AGGREGATION,
    PLAN_3_DATA_STORAGE,
    PLAN_4_NAME,
    PLAN_4_VALID,
    PLAN_4_AGGREGATION,
    PLAN_4_DATA_STORAGE,
    PLAN_5_NAME,
    PLAN_5_VALID,
    PLAN_5_AGGREGATION,
    PLAN_5_DATA_STORAGE,
    PLAN_6_NAME,
    PLAN_6_VALID,
    PLAN_6_AGGREGATION,
    PLAN_6_DATA_STORAGE,
    GENERATION,
    LEV_NUM
FROM
    CTE_FINAL
WHERE
    1=1
    AND DIMENSION = 'Period'
    --AND LEV_NUM LIKE '%1%'
ORDER BY
    POSITION

Hope this comes in handy for you, I know it’s one of the few perks of On Premise that you can get database access and perform all of these fun queries.

Maybe my next step will be automating this data via Groovy. We shall see.

One thought on “Outline Export from Planning DB

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s