Generating a Planning Generation Table with Groovy

If you haven’t read our earlier blog post on exporting the outline you should check it out first.
A generation table has it’s uses but I personally get the most use when I combine it with a fact table and / or a parent/child table which is what we’ve created already.

Normally I wouldn’t resort to using Groovy for something like this. I’d just do it in sql but short of creating a procedure and hoping you have the source and destination on the same database you’ll end up needing a technology that can connect to seperate databases and has the ability to add columns depending on how much you need.
For example, if you’re exporting all dimensions you might need 10 generations, but if you’re only working with a select few you might only need 2. How will you know if you don’t add another generation to your database? If you’re not dynamically generating the table you could accidentally be excluding metadata without even knowing it.

We’ll start with our config file. This file needs to be included with your script. I called mine Config.groovy but the name is really unimportant. All that matters is you add whatever environments you need as well as the connection details actually line up with your source / target database info.

//Config.groovy
environments {
    dev {
        sourceDatabaseConnection {
            url = "jdbc:oracle:thin:@HOSTNAME:1521:DATABASE"
            userName = "USERNAME"
            passWord = 'PASSWORD'
            dataSource = "oracle.jdbc.pool.OracleDataSource"
        }
        targetDatabaseConnection {
            url = "jdbc:oracle:thin:@HOSTNAME:1521:DATABASE"
            userName = "USERNAME"
            passWord = 'PASSWORD'
            dataSource = "oracle.jdbc.pool.OracleDataSource"
        }
    }
}

Next we have to create our groovy script. I called mine ExportGenerationHierarchy.groovy but the name is really unimportant. Just remember what it is. You’ll want to start by defining your connections.

def configEnvironment = "dev"
def configFileName = "Config.groovy"

ConfigObject configObject = new ConfigSlurper(configEnvironment).parse(new File(configFileName).toURI().toURL())

def con_Source = Sql.newInstance(configObject.sourceDatabaseConnection.url,
        configObject.sourceDatabaseConnection.userName,
        configObject.sourceDatabaseConnection.passWord,
        configObject.sourceDatabaseConnection.dataSource)

def con_Destination = Sql.newInstance(configObject.targetDatabaseConnection.url,
        configObject.targetDatabaseConnection.userName,
        configObject.targetDatabaseConnection.passWord,
        configObject.targetDatabaseConnection.dataSource)

We’ll need to get max number of generations. We used a similar technique in the prior article on outline extraction.
The magic is the CONNECT BY PL/SQL which we wrap in a sub-query. Groovy will run this SQL against the source database and get the MAX(GEN_NUM) and return it into a variable.

int max_gen_num = GetMaxGenNum(con_Source, sqlFilter)
def GetMaxGenNum(def con_Source, def sqlFilter) {
    def sql_get_max_gen_num = """
        SELECT
          MAX(GEN_NUM) AS MAX_GEN_NUM
        FROM (
        SELECT
                OD.OBJECT_NAME AS DIMNAME,
                SYS_CONNECT_BY_PATH(O.OBJECT_NAME, '|') AS PATH,
                LEVEL AS GEN_NUM,
                O.OBJECT_NAME AS MEMBER_NAME,
                OP.OBJECT_NAME AS PARENT_NAME,
            O.HAS_CHILDREN
        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
                OD.OBJECT_ID = O.OBJECT_ID
            CONNECT BY NOCYCLE PRIOR
                O.OBJECT_ID = O.PARENT_ID
            ) SQ1
        WHERE HAS_CHILDREN = 0
        """
    sql_get_max_gen_num += sqlFilter

    int max_gen_num

    con_Source.eachRow(sql_get_max_gen_num) {
        max_gen_num = it.MAX_GEN_NUM
    }
    return max_gen_num
}

Next we will drop/create our target table. We want to get notified if there are any issues but we know a specific error we can safely ignore. “Table does not exist” so we’ll catch that.

def CreateTable(int max_gen_num, def con_Destination) {
    println "Dropping Table: TMP_PLANNING_GEN_EXPORT"
    def sql_drop_deleteme_ct = "DROP TABLE TMP_PLANNING_GEN_EXPORT"
    try { con_Destination.execute(sql_drop_deleteme_ct) }
    catch (SQLSyntaxErrorException e) {
        if (e.message.toUpperCase().contains("ORA-00942")) {println "Drop not executed. Table does not exist."}
        else {throw e}
    }

    println "Creating Table: TMP_PLANNING_GEN_EXPORT"
    def sql_create_deleteme_ct = """
        CREATE TABLE TMP_PLANNING_GEN_EXPORT (
            DIMNAME VARCHAR2(80),
            PATH VARCHAR2(4000),
            GEN_NUM VARCHAR2(80)"""
    (1..max_gen_num).each { sql_create_deleteme_ct += ",\r\n    GEN${it} VARCHAR2(80)" }
    sql_create_deleteme_ct += ",\r\n    LEVEL0 VARCHAR2(80)\r\n)"
    con_Destination.execute(sql_create_deleteme_ct)
}

Finally we get to load some data. Two things to note here. We’ll be opening two connections at the same time but we’ll need to avoid trying to load up the entire resultset into memory. We’ll want to use batches on both sides, the reading and the writing.
To acomplish this we use two methods.

def LoadData(def con_Source, def con_Destination, def max_gen_num, def sqlFilter, def bRun, def batchSize) {

    def sql_get_gen_table = """
        SELECT
          DIMNAME,
          PATH,
          GEN_NUM,
        """
    (1..max_gen_num).each { sql_get_gen_table += "REGEXP_SUBSTR (PATH, '[^|]+', 1, ${it}) AS GEN${it}," }
    sql_get_gen_table += """
          MEMBER_NAME AS LEVEL0
        FROM (
        SELECT
                OD.OBJECT_NAME AS DIMNAME,
                SYS_CONNECT_BY_PATH(O.OBJECT_NAME, '|') AS PATH,
                LEVEL AS GEN_NUM,
                O.OBJECT_NAME AS MEMBER_NAME,
                OP.OBJECT_NAME AS PARENT_NAME,
            O.HAS_CHILDREN
        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
                OD.OBJECT_ID = O.OBJECT_ID
            CONNECT BY NOCYCLE PRIOR
                O.OBJECT_ID = O.PARENT_ID
            ) SQ1
        WHERE HAS_CHILDREN = 0
        """
    sql_get_gen_table += sqlFilter

    def sql_insert_deleteme_ct = """INSERT INTO TMP_PLANNING_GEN_EXPORT (DIMNAME, PATH, GEN_NUM,"""
    (1..max_gen_num).each { sql_insert_deleteme_ct += "GEN${it}," }
    sql_insert_deleteme_ct += "LEVEL0) VALUES (?,?,?,"
    (1..max_gen_num).each { sql_insert_deleteme_ct += "?," }
    sql_insert_deleteme_ct += "?)"

    int totalInserts = 0

    if(bRun) {
        con_Source.withStatement { stmt -> stmt.fetchSize = batchSize}
        con_Source.rows(sql_get_gen_table).each { row ->
            totalInserts++
            con_Destination.withBatch(batchSize,sql_insert_deleteme_ct) { stmt ->
                stmt.addBatch(row.values().toArray())
            }
        }
    }
    return totalInserts
}

The withStatement allows us to set a batch side for the reading.

con_Source.withStatement { stmt -> stmt.fetchSize = batchSize}

And withBatch allows us to set a batch size for writing. Combined they make a pretty useful pair.

            con_Destination.withBatch(batchSize,sql_insert_deleteme_ct) { stmt ->
                stmt.addBatch(row.values().toArray())
            }

And finally, here’s what it looks like when we pull it together.

//ExportGenerationHierarchy.groovy
import groovy.sql.Sql
import groovy.time.TimeCategory
import groovy.time.TimeDuration
import java.sql.SQLSyntaxErrorException

def timeStart = new Date()

def configEnvironment = "dev"
def configFileName = "Config.groovy"

ConfigObject configObject = new ConfigSlurper(configEnvironment).parse(new File(configFileName).toURI().toURL())

def con_Source = Sql.newInstance(configObject.sourceDatabaseConnection.url,
        configObject.sourceDatabaseConnection.userName,
        configObject.sourceDatabaseConnection.passWord,
        configObject.sourceDatabaseConnection.dataSource)

def con_Destination = Sql.newInstance(configObject.targetDatabaseConnection.url,
        configObject.targetDatabaseConnection.userName,
        configObject.targetDatabaseConnection.passWord,
        configObject.targetDatabaseConnection.dataSource)

int batchSize = 100
def totalInserts = 0

boolean bRun = true

//def sqlFilter = ""
def sqlFilter = "AND DIMNAME = 'Period'"

int max_gen_num = GetMaxGenNum(con_Source, sqlFilter)

CreateTable(max_gen_num, con_Destination)

totalInserts = LoadData(con_Source, con_Destination, max_gen_num, sqlFilter, bRun, batchSize)

ReportRuntimeStats(timeStart, totalInserts, batchSize)

def ReportRuntimeStats(def timeStart, def totalInserts, def batchSize) {
    def timeStop = new Date()
    TimeDuration duration = TimeCategory.minus(timeStop, timeStart)
    println "Total Inserts: " + totalInserts
    println "Batch Size:    " + batchSize
    println "Duration:      " + duration

}

def CreateTable(int max_gen_num, def con_Destination) {
    println "Dropping Table: TMP_PLANNING_GEN_EXPORT"
    def sql_drop_deleteme_ct = "DROP TABLE TMP_PLANNING_GEN_EXPORT"
    try { con_Destination.execute(sql_drop_deleteme_ct) }
    catch (SQLSyntaxErrorException e) {
        if (e.message.toUpperCase().contains("ORA-00942")) {println "Drop not executed. Table does not exist."}
        else {throw e}
    }

    println "Creating Table: TMP_PLANNING_GEN_EXPORT"
    def sql_create_deleteme_ct = """
        CREATE TABLE TMP_PLANNING_GEN_EXPORT (
            DIMNAME VARCHAR2(80),
            PATH VARCHAR2(4000),
            GEN_NUM VARCHAR2(80)"""
    (1..max_gen_num).each { sql_create_deleteme_ct += ",\r\n    GEN${it} VARCHAR2(80)" }
    sql_create_deleteme_ct += ",\r\n    LEVEL0 VARCHAR2(80)\r\n)"
    con_Destination.execute(sql_create_deleteme_ct)
}

def GetMaxGenNum(def con_Source, def sqlFilter) {
    def sql_get_max_gen_num = """
        SELECT
          MAX(GEN_NUM) AS MAX_GEN_NUM
        FROM (
        SELECT
                OD.OBJECT_NAME AS DIMNAME,
                SYS_CONNECT_BY_PATH(O.OBJECT_NAME, '|') AS PATH,
                LEVEL AS GEN_NUM,
                O.OBJECT_NAME AS MEMBER_NAME,
                OP.OBJECT_NAME AS PARENT_NAME,
            O.HAS_CHILDREN
        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
                OD.OBJECT_ID = O.OBJECT_ID
            CONNECT BY NOCYCLE PRIOR
                O.OBJECT_ID = O.PARENT_ID
            ) SQ1
        WHERE HAS_CHILDREN = 0
        """
    sql_get_max_gen_num += sqlFilter

    int max_gen_num

    con_Source.eachRow(sql_get_max_gen_num) {
        max_gen_num = it.MAX_GEN_NUM
    }

    return max_gen_num

}

def LoadData(def con_Source, def con_Destination, def max_gen_num, def sqlFilter, def bRun, def batchSize) {

    def sql_get_gen_table = """
        SELECT
          DIMNAME,
          PATH,
          GEN_NUM,
        """
    (1..max_gen_num).each { sql_get_gen_table += "REGEXP_SUBSTR (PATH, '[^|]+', 1, ${it}) AS GEN${it}," }
    sql_get_gen_table += """
          MEMBER_NAME AS LEVEL0
        FROM (
        SELECT
                OD.OBJECT_NAME AS DIMNAME,
                SYS_CONNECT_BY_PATH(O.OBJECT_NAME, '|') AS PATH,
                LEVEL AS GEN_NUM,
                O.OBJECT_NAME AS MEMBER_NAME,
                OP.OBJECT_NAME AS PARENT_NAME,
            O.HAS_CHILDREN
        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
                OD.OBJECT_ID = O.OBJECT_ID
            CONNECT BY NOCYCLE PRIOR
                O.OBJECT_ID = O.PARENT_ID
            ) SQ1
        WHERE HAS_CHILDREN = 0
        """
    sql_get_gen_table += sqlFilter

    def sql_insert_deleteme_ct = """INSERT INTO TMP_PLANNING_GEN_EXPORT (DIMNAME, PATH, GEN_NUM,"""
    (1..max_gen_num).each { sql_insert_deleteme_ct += "GEN${it}," }
    sql_insert_deleteme_ct += "LEVEL0) VALUES (?,?,?,"
    (1..max_gen_num).each { sql_insert_deleteme_ct += "?," }
    sql_insert_deleteme_ct += "?)"

    int totalInserts = 0

    if(bRun) {
        con_Source.withStatement { stmt -> stmt.fetchSize = batchSize}
        con_Source.rows(sql_get_gen_table).each { row ->
            totalInserts++
            con_Destination.withBatch(batchSize,sql_insert_deleteme_ct) { stmt ->
                stmt.addBatch(row.values().toArray())
            }
        }
    }

    return totalInserts

}

This is the output we can expect when we set the filter to just one dimension. In this case Period. As we can see there are 4 generations needed for Period

def sqlFilter = "AND DIMNAME = 'Period'"

041517_2349_Generatinga2.png

And only three generations needed for Year. It’s definitely working.

def sqlFilter = "AND DIMNAME = 'Year'"

041517_2349_Generatinga1.png

My favorite part of the exercise is that it showcases a few cool concepts.

  • ETL using Groovy.
  • Using a config file with ConfigSlurper.
  • Dynamic SQL Using Groovy.

And how long does it take for my entire outline? Well I have over 100k members in total but how’s this look?
2017-04-15_213530

But please note that anytime you run custom SQL using groovy you have to make sure you are careful. Don’t want to blow up your database! Don’t say I didn’t warn you.

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.

Two Ways to Search Essbase Exports with PowerShell

Every now and again you may get a request to check for data in Essbase text exports.

We do them nightly so it’s very easy to prove what day someone may have added or deleted some data based on if it exists.

Here are two different ways to look for them.

Method 1: Using .NET

Clear-Host
$search = $null
$rootFolder = "C:\EssbaseRestores"
$searchFilter = "P2000000572"
$files = @()
$found = $false

Write-Output "===========STARTING SEARCH==================="
$search = Get-ChildItem -Path $rootFolder -Recurse |
    Where {$_.PSIsContainer -eq $false} | foreach {
        foreach ($line in [System.IO.File]::ReadLines($_.FullName)) {
            if ($line -match $searchFilter) {
                Write-Output $line
                $found=$true
            }
        }
        if($found) {$files += $_.FullName}
        $found = $false
    }
Write-Output "===========SEARCH RESULTS=================="
if ($search) {
    Write-Output "Search found results!"
    $files
} else {
    Write-Output "Search turned up nothing"
}
Write-Output "===========SEARCH COMPLETED=================="

Method 2: Using the get-content commandlet.

Clear-Host
$search = $null
$rootFolder = "C:\EssbaseRestores"
$searchFilter = "P2000000572"
$files = @()
$found = $false

Write-Output "===========STARTING SEARCH==================="
$search = Get-ChildItem -Path $rootFolder -Recurse |
    Where {$_.PSIsContainer -eq $false} |
    Get-Content -ReadCount 512 |
    Where {$_ -match $searchFilter}
Write-Output "===========SEARCH COMPLETED=================="
if ($search) {
    Write-Output "Search found results!"
    $search | Select PSPath -Unique
} else {
    Write-Output "Search turned up nothing"
}

The second method is more concise but your mileage may vary. I tend to prefer the .Net approach myself.

Installing OBIEE 12c – Step 1: Fusion Middleware Install

Start up an admin command prompt and run the following command.

java.exe -jar ".\fmw_12.2.1.2.0_infrastructure.jar"

*If your system has a JRE installed you’ll need to specify the full path to the JDK version of java.exe.


Click Next to begin.


Make sure Skip Auto Updates is selected and click Next


Enter the Oracle Home in the checklist above and click Next.


Select “Fusion Middleware Infrastructure” and Click Next.


Click Next.


Verify the option “I wish to receive security updates via My Oracle Support” is unselected and Click Next.


Click Yes.


Click Install.
This next screen takes about 10 minutes to complete.


Click Next.


Click Finish.