Hybrid Essbase: Rapidly Make Parents Dynamic Calc

Hybrid Essbase is the biggest advancement in Essbase technology since ASO was released.  It truly takes Essbase to another level when it comes to getting the best out of both ASO and BSO technology.  Converting your application from BSO to Hybrid can be a long process.  You have to make sure that all of your calculations still work the way they should.  You have to make sure that your users don’t break Hybrid mode.  You have to update the storage settings for all of your sparse dimensions.

I can’t help you with the first items, they just take time and effort.  What I can help you with is the time required to update your sparse dimensions.  I spend a lot of time hacking around in the Planning repository.  I suddenly found a new use for all of that time spent with the repository…getting a good list of all of the upper level members in a dimension.  If we just export a dimension, we get a good list, but we have to do a lot of work to really figure out which members are parents and which are not.  Luckily, the HSP_OBJECT table has column that tells us just that: HAS_CHILDREN.

Microsoft SQL Server

The query to do this is very, very simple.  The process for updating your dimensions using the query takes a little bit more explanation.  We’ll start with SQL Server since that happens to be where I’m the most comfortable.  I’m going to assume you are using SQL Server Management Studio…because why wouldn’t you?  It’s awesome.  Before we even get to the query, we first need to make a configuration change.  Open Management Studio and click on Tools, then Options.

HybridEssbase01

Expand Query Results, then expand SQL Server, and then click on Results to Grid:

HybridEssbase02

Check the box titled Include column headers when copying or saving the results and click OK.  Why did we start here?  Because we have to restart Management Studio for the new setting to actually take affect.  So do that next…

Now that we have Management Studio ready to go, we can get down to the query.  Here it is in all of its simplicity:

SELECT
	o.OBJECT_NAME AS Product
	,po.OBJECT_NAME AS Parent
	,'dynamic calc' AS [Data Storage (Plan1)]
FROM
	HSP_OBJECT o
INNER JOIN
	HSP_MEMBER m ON m.MEMBER_ID = o.OBJECT_ID
INNER JOIN
	HSP_DIMENSION d ON m.DIM_ID = d.DIM_ID
INNER JOIN
	HSP_OBJECT do ON do.OBJECT_ID = d.DIM_ID
INNER JOIN
	HSP_OBJECT po ON po.OBJECT_ID = o.PARENT_ID
WHERE
	do.OBJECT_NAME = 'Product'
	AND o.HAS_CHILDREN = 1

We have a few joins and a very simple where clause.  As always, I’m using my handy-dandy Vision demo application.  A quick look at the results shows us that there are very few parents in the Product dimension:

HybridEssbase03

Now we just need to get this into a format that we can easily import back into Planning.  All we have to do it right-click anywhere in the results and click on Save Results As….  Enter a file name and click Save.

HybridEssbase05

Now we should have a usable format for a simple Import to update our dimension settings.  Let’s head to workspace and give it a shot.  Fire up your Planning application and click on Administration, then Import and Export, and finally Import Metadata from File:

HybridEssbase06

Select your dimension from the list and then browse to find your file.  Once the file has uploaded, click the Validate button.  This will at least tell us if we have a properly formatted CSV:

HybridEssbase07

That looks like a good start.  Let’s go ahead and complete the import and see what happens:

HybridEssbase08

This looks…troubling.  One rejected record.  Let’s take a look at our logs to see why the record was rejected:

HybridEssbase09

As we can see, nothing to worry about.  The top-level member of the dimension is rejected because there is no valid parent.  We can ignore this and go check to see if our changes took affect.

HybridEssbase10

At first it looks like we may have failed.  But wait!  Again, nothing to worry about yet.  We didn’t update the default data storage.  We only updated Plan1.  So let’s look at the data storage property for Plan1:

HybridEssbase11

That’s more like it!

Oracle Database

But wait…I have an Oracle DB for my repository.  Not to worry.  Let’s check out how to do this with Oracle and SQL Developer.  First, let’s take a look at the query:

SELECT
	o.OBJECT_NAME AS Product
	,po.OBJECT_NAME AS Parent
	,'dynamic calc' AS "Data Storage (Plan1)"
FROM
	HSP_OBJECT o
INNER JOIN
	HSP_MEMBER m ON m.MEMBER_ID = o.OBJECT_ID
INNER JOIN
	HSP_DIMENSION d ON m.DIM_ID = d.DIM_ID
INNER JOIN
	HSP_OBJECT do ON do.OBJECT_ID = d.DIM_ID
INNER JOIN
	HSP_OBJECT po ON po.OBJECT_ID = o.PARENT_ID
WHERE
	do.OBJECT_NAME = 'Product'
	AND o.HAS_CHILDREN = 1

This is very, very similar to the SQL Server query.  The only real difference is the use of double quotes instead of brackets around our third column name.  A small, yet important distinction.  Let’s again look at the results:

HybridEssbase12

The Oracle results look just like the SQL Server results…which is a good thing.  Now we just have to get the results into a usable CSV format for import.  This will take a few more steps, but it still very easy.  Right click on the result set and click Export:

HybridEssbase13

Change the export format to csv, choose a location and file name, and then click Next.

HybridEssbase14

Click Finish and we should have our CSV file ready to go.  Let’s fire up our Planning application and click on Administration, then Import and Export, and finally Import Metadata from File:

HybridEssbase06

Select your dimension from the list and then browse to find your file.  Once the file has uploaded, click the Validate button.  This will at least tell us if we have a properly formatted CSV:

HybridEssbase15

 

Much like the SQL Server file, this looks like a good start.  Let’s go ahead and complete the import and see what happens:

HybridEssbase16

Again, much like SQL Server, we have the same single rejected record.  Let’s make sure that the same error message is present:

HybridEssbase09

As we can see, still nothing to worry about.  The top-level member of the dimension is rejected because there is no valid parent.  We can ignore this and go check to see if our changes took affect.

HybridEssbase10

As with SQL Server, we did not update the default data storage property, only Plan1.  So let’s look at the data storage property for Plan1:

HybridEssbase11

And just like that…we have a sparse dimension ready for Hybrid Essbase.  Be sure to refresh your database back to Essbase.  You can simply enter a different dimension name in the query and follow the same process to update the remaining sparse dimensions.

Celvin also has an excellent Essbase utility that will do this for you, but it makes use of the API and Java, and generally is a bit more complicated than this method if you have access to the repository.  So what happens if you can’t use the API and you can’t access the repository?  We have another option.  We’ll save that for another day, this blog post is already long enough!


Changing the Planning Repository without Restarting Planning

One of the long-running tenets of working with the Planning repository is that you must restart Planning to see your changes.  I’ve always heard that there were ways around this, but Oracle hasn’t ever been forthcoming with the specifics of how to make that happen.  Finally, at Kscope16 during my presentation on the Planning Repository, someone in the audience by the name of Tjien Lie had the code from Oracle to make this happen.  Before I get to that, let’s start with a primer on the HSP_ACTION table.  I would provide one, but John Goodwin did such a great job, so I’ll just point you to his post here.

<<<wait for the reader to go to John Goodwin’s site and read the information and come back, assuming they don’t get distracted by his wealth of amazing content>>>

Ok…now that you understand what the HSP_ACTION table does, how do we use it differently than John uses it?  By differently, I mean I don’t wan to insert specific rows and update specific things.  That seems like a lot of work.  Instead, why not just have the HSP_ACTION table update the entire cache for us?  Let’s give it a shot.  First I’m going to go add a dimension to my Vision application:

UpdateCache01

Now let’s make sure the dimension shows up:

UpdateCache02

Any now let’s delete it:

DELETE FROM HSP_MRU_MEMBERS WHERE DIM_ID IN (SELECT OBJECT_ID FROM HSP_OBJECT WHERE OBJECT_NAME = 'ToBeDeleted')

DELETE 
FROM 
	HSP_UNIQUE_NAMES 
WHERE 
	OBJECT_NAME IN (
SELECT
	OBJECT_NAME
FROM
	HSP_MEMBER m
INNER JOIN
	HSP_OBJECT o ON o.OBJECT_ID = m.MEMBER_ID
WHERE
	DIM_ID IN (SELECT OBJECT_ID FROM HSP_OBJECT WHERE OBJECT_NAME = 'ToBeDeleted'))

SELECT
	OBJECT_ID INTO #DeleteChildren
FROM
	HSP_MEMBER m
INNER JOIN
	HSP_OBJECT o ON o.OBJECT_ID = m.MEMBER_ID
WHERE
	DIM_ID IN (SELECT OBJECT_ID FROM HSP_OBJECT WHERE OBJECT_NAME = 'ToBeDeleted')	
	AND o.HAS_CHILDREN = 0

SELECT
	OBJECT_ID INTO #DeleteParents
FROM
	HSP_MEMBER m
INNER JOIN
	HSP_OBJECT o ON o.OBJECT_ID = m.MEMBER_ID
WHERE
	DIM_ID IN (SELECT OBJECT_ID FROM HSP_OBJECT WHERE OBJECT_NAME = 'ToBeDeleted')	
	AND o.HAS_CHILDREN = 1

DELETE
FROM
	HSP_MEMBER
WHERE
	MEMBER_ID IN (SELECT OBJECT_ID FROM #DeleteChildren)

DELETE
FROM
	HSP_MEMBER
WHERE
	MEMBER_ID IN (SELECT OBJECT_ID FROM #DeleteParents)

DELETE
	d
FROM
	HSP_DIMENSION d
INNER JOIN
	HSP_OBJECT o ON d.DIM_ID = o.OBJECT_ID
WHERE
	o.OBJECT_NAME = 'ToBeDeleted'

DELETE
FROM
	HSP_OBJECT
WHERE
	OBJECT_ID IN (SELECT OBJECT_ID FROM #DeleteChildren)

DELETE
FROM
	HSP_OBJECT
WHERE
	OBJECT_ID IN (SELECT OBJECT_ID FROM #DeleteParents)

I’ll have a post on that command at some point, but basically it deletes the dimension from the repository.  Now let’s go look again at our dimension list and make sure that it still shows up:

UpdateCache03

Still there…as expected.  Now let’s try this little query, courtesy of Tjien Lie:

INSERT INTO HSP_ACTION (FROM_ID, TO_ID, ACTION_ID, OBJECT_TYPE, MESSAGE, ACTION_TIME) VALUES (0, 0, 2, -999, 'CACHE RESET',GETDATE())

And let’s take a look at the HSP_ACTION table and make sure that we have the row inserted:

UpdateCache04

We can also check out this table to see if our cache has been updated yet.  As long as the row is here, we know that the cache hasn’t yet been updated.  After a little while, I checked the table again:

UpdateCache05

Now that our table is empty, Planning will tell us that it did in fact refresh the cache:

UpdateCache06

That takes the guess work out of it!  So how about our dimension…is it gone?

UpdateCache07

And just like that, the dimension is gone.  I can make all of the change that I want and I no longer need to restart Planning.  Special thanks to Tjien Lie from The Four Seasons for providing the code.  Information exchange like this is why I love Kscope and can’t wait to see everyone in my home state of Texas next year!  That’s it for now!


Brian @ Kscope16

It’s that time of year again…Kscope!  Unfortunately, Kscope always occurs during my busy season.  As a result, much like last year, this will be a very quick in and out trip.  I’ll be there Tuesday, June 28th only.  Luckily, both of my presentations happen to be on that day, so it worked out nicely for me.  I also have a few meetings with Oracle that I’m not allowed to talk about that occur on that day as well.  Here are my presentations:

PBCS is Still Hyperion Planning

Jun 28, 2016, Session 9, 11:15 am – 12:15 pm

With Oracle’s release of PBCS, many service companies have started releasing their one-size-fits-all implementations. The unfortunate truth is that PBCS is still Hyperion Planning. This presentation discusses the best practices around implementing PBCS and how to avoid the pitfalls of implementations offered at insanely cheap (and underestimated) pricing. Attend this session if you don’t want to have your PBCS project “land and expand.”

The Planning Repository Revisited: Beyond the Basics

Jun 28, 20165, Session 10, 2:00 pm – 3:00 pm

If you’ve enjoyed my past presentations on the Planning repository, you should enjoy this presentation even more. We’ll take a step beyond the basics and provide a whole new set of examples that take a leap into real-world use. Whether it’s synchronizing metadata across applications or deleting dimensions, this presentation will dive deeper than ever before into the repository. But wait…there’s more. This presentation will have full samples in both Transact SQL for SQL Server users and PL/SQL for Oracle users. That’s two languages for the price of one (shipping and handling not included).

And of course, you can find me on the Kscope16 site here.

I hope everyone enjoys the content!


The Planning Repository: HSP_ACCOUNT

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

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

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

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

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

SELECT
	 po.OBJECT_NAME AS Parent
	,o.OBJECT_NAME AS Account
	,CASE a.USE_445
		WHEN 0 THEN 'None'
		WHEN 1 THEN '445'
		WHEN 2 THEN '454'
		WHEN 3 THEN '544'
		ELSE ''
	 END AS WeeklySpread
	,CASE a.TIME_BALANCE
		WHEN 0 THEN 'None'
		WHEN 1 THEN 'First'
		WHEN 2 THEN 'Last'
		WHEN 3 THEN 'Average'
		ELSE ''
	 END AS TimeBalance
	,CASE a.SKIP_VALUE
		WHEN 0 THEN 'None'
		WHEN 1 THEN 'Skip Missing'
		WHEN 2 THEN 'Skip Zeros'
		WHEN 3 THEN 'Skip Missing and Zeros'
		ELSE ''
	 END AS SkipValue
	,CASE a.ACCOUNT_TYPE
		WHEN 1 THEN 'Expense'
		WHEN 2 THEN 'Revenue'
		WHEN 3 THEN 'Asset'
		WHEN 4 THEN 'Liability'
		WHEN 5 THEN 'Equity'
		WHEN 6 THEN 'Statistical'
		WHEN 7 THEN 'Saved Assumption'
		ELSE ''
	 END AS AccountType
	,CASE a.VARIANCE_REP
		WHEN 1 THEN 'Expense'
		WHEN 2 THEN 'Non-expense'
		ELSE ''
	 END AS VarianceReporting
	,CASE a.CURRENCY_RATE
		WHEN 0 THEN 'None'
		WHEN 1 THEN 'Average'
		WHEN 2 THEN 'Ending'
		WHEN 3 THEN 'Historical'
		ELSE ''
	 END AS CurrencyRate
FROM 
	HSP_ACCOUNT a
INNER JOIN
	HSP_OBJECT o ON a.ACCOUNT_ID = o.OBJECT_ID
INNER JOIN
	HSP_OBJECT po ON o.PARENT_ID = po.OBJECT_ID
ORDER BY 
	o.POSITION

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

SQL Server:

HSP_ACCOUNT_MOracle:

HSP_ACCOUNT_O

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


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

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:

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'

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:

HSP_MEMBER_ALL_M

 

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:

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

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:

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'

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:

HSP_MEMBER_ALL_O

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)

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 NameSQL Server TypeOracle TypeDescription
MEMBER_IDintNUMBER(38,0)The OBJECT_ID of the member associated with the attribute member.
ATTR_IDintNUMBER(38,0)The OBJECT_ID of the attribute dimension.
ATTR_MEM_IDintNUMBER(38,0)The OBJECT_ID of the attribute dimension member associated with the member.
PERSPECTIVE1intNUMBER(38,0)I have not been able to figure out what this is used for. It seems to always be 0 in my testing.
PERSPECTIVE2intNUMBER(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:

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'

And here we see the results:

HSP_MEMBER_TO_ATTRIBUTE_1

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:

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'

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:

HSP_MEMBER_TO_ATTRIBUTE_2

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

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 NameSQL Server TypeOracle TypeDescription
ATTR_IDintNUMBER(38,0)The OBJECT_ID of the attribute.
DIM_IDintNUMBER(38,0)The OBJECT_ID of the associated dimension for the attribute.
ATTR_TYPEsmallintNUMBER(38,0)The type of attribute dimension.
0 = Text
1 = Numeric
2 = Boolean
3 = Date
PERSPECTIVE1intNUMBER(38,0)No ideaÉmay not even ben in use. Let me know if you have a clue.
PERSPECTIVE2intNUMBER(38,0)No ideaÉmay not even ben in use. Let me know if you have a clue.

HSP_ATTRIBUTE_MEMBER:

Field NameSQL Server TypeOracle TypeDescription
ATTR_MEM_IDintNUMBER(38,0)The OBJECT_ID of the attribute dimension member.
ATTR_IDintNUMBER(38,0)The OBJECT_ID of the attribute dimension.
LEVEL0smallintNUMBER(38,0)This is supposed to tell us if the member is a leaf. It doesn't actually do that.
TEXT_VALvarchar(32)VARCHAR(32 CHAR)In theory this holds a text value for the member. Instead it is always null.
NUMBER_VALdecimal(18,0)NUMBER(38,0)In theory this holds a numeric value for the member. Again, always null.
BOOLEAN_VALsmallintNUMBER(38,0)There's a pattern forming here...always null.
DATE_VALdatetimeDATEYou 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:

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

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:

HSP_ATTRIBUTE_DIM

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:

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

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:

HSP_ATTRIBUTE_MEMBER

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)

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 NameSQL Server TypeOracle TypeDescription
MEMBER_IDintNUMBER(38,0)The OBJECT_ID of the member.
PLAN_TYPEintNUMBER(38,0)The plan type.
DATA_STORAGEintNUMBER(38,0)The data storage type to be used in Essbase for the specific plan type.
SOLVE_ORDERintNUMBER(38,0)The plan type specific data storage for the member.
FORMULAntextCLOBThe 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:

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 

Oracle:

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

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

HSP_MEMBER_FORMULA_O

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:

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

Oracle:

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

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

HSP_MEMBER_FORMULA_2M

Oracle (SQL Developer):

HSP_MEMBER_FORMULA_2O

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)

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 NameSQL Server TypeOracle TypeDescription
UDA_IDintNUMBER(38,0)The unique ID of the UDA.
DIM_IDintNUMBER(38,0)The OBJECT_ID of the dimension that the UDA belongs to.
UDA_VALUEvarchar(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 NameSQL Server TypeOracle TypeDescription
MEMBER_IDintNUMBER(38,0)The OBJECT_ID of the member associated with the UDA.
UDA_IDintNUMBER(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:

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'

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:

HSP_MEMBER_TO_UDA_MOracle:

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

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:

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

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:

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'

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

HSP_MEMBER_ALIAS_TABLE

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 NameSQL Server TypeOracle TypeDescription
MEMBER_IDintNUMBER(38,0)The OBJECT_ID of the member.
ALIASTBL_IDintNUMBER(38,0)The OBJECT_ID of the alias table.
ALIAS_IDintNUMBER(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:

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'

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:

HSP_ALIAS

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…