Introduction
Oh dear, I seem to have dropped off with my Stupid Planning Tricks. The thing is, I’ve got a gazillion (roughly, there might be some slight exaggeration in that count) of these queries and I simply don’t remember which ones I’ve shared and which ones I haven’t. I tend to get distracted with other technologies and projects…this is all a way of saying I’m a bit scatter-brained.Enough apology, here’s the code, in case you haven’t been able to figure this out from other queries I’ve given away, to pull the Entity dimension from your Planning application.
Some interesting columns
Here’s HSP_ENTITY in SQL Server’s table design mode – don’t worry about the brand of SQL – the table is always the same.
ENTITY_ID is simple – that’s the name of the OBJECT_ID value in Entity. How exactly you could have a NULL OBJECT_ID is beyond my understanding of Planning as each and every object in Planning has a value, but my SQL brain is very small, so I’m not going to worry about it. You shouldn’t worry either – Planning sets the value, not you.
DEFAULT_CURRENCY makes sense if you are using Planning’s multicurrency functionality. If you are working in a single currency application, this field is NULL. It’s easy (as we will see) to go grab the currency descriptions from HSP_CURRENCY.
USED_IN is kind of an interesting field. It is described as a bit masked value, showing what Plan Type the Entity is used in. A bit mapped value is simply an index of the values (in the case of this field) created by either the individual values or the adding together of the values. Huh? Actually, this is really, really easy. Think about Classic Planning’s dimension editor for the Entity dimension. Now think further about the rightmost field. Do you remember those numbers?
The above is a full three Plan Type Planning application with Workforce and Capex thrown in for giggles. Do you see the 1, 2,4, 8, and 16? Those are the values that make up the bitmap. Let’s take a look at what HSP_ENTITY looks like:
There they are. Wait, what’s that 31 value? Well, 1+2+4+8+16 = 31. And yes, that 31 equates to No Entity sitting in all five Plan Types. If we had one that was in Plan Type 1 and 8, it would have a bitmap value of 9 as in Entity6:
Ta da – proof that yr. obdnt. srvnt. can add simple numbers together.
The thing that’s nice is that you can use USED_IN with HSP_PLAN_TYPE to get the descriptions of the Plan Types:
EMPLOYEE_ID and REQUISITION_NO have the respective descriptions of “Id for an employee” and “Requisition number for a to-be-hired”. Hmm, those sound as if they are for Workforce Planning. Know that if you don’t have Workforce, these two fields are NULL. (Actually, I can’t seem to value these fields at all. I must be doing something wrong so figuring this out is on The List of Things to Figure Out and thus a subject for another blog post, someday.)
ENTITY_TYPE is another one of those odd ones. I can see that it’s there. I can see there’s a description in the schema:
0=none (default)
1=employee
2=department-general
3=TBH-input
But I’ve never actually seen the values change from anything other than 0. Again, it looks like this field is used in Workforce. I shall have to experiment for a later blog post.
Putting it all together
So let’s write a query that takes into account all of the above.-- Purpose: Illustrate the querying of a custom (Entity) dimension
-- Modified: 7 April 2012, Cameron Lackpour
-- Notes: Unique to Entity are Currency, Entity Type, and Plan Type
--
SELECT
PO.OBJECT_NAME AS 'Parent',
O.OBJECT_NAME AS 'Child',
-- Use a SQL Subquery to get aliases.
-- NB -- The overall SELECT from HSP_MEMBER ensures that members with
-- and without an alias are selected.
-- ISNULL puts in zero length string in place of NULL
ISNULL((SELECT OA.OBJECT_NAME
FROM HSP_ALIAS A
INNER JOIN HSP_OBJECT OA
ON A.MEMBER_ID = O.OBJECT_ID AND
OA.OBJECT_ID = A.ALIAS_ID), '') AS 'Alias',
-- Use a SQL subquery to get currency tags
ISNULL((SELECT C.SYMBOL
FROM HSP_CURRENCY C
INNER JOIN HSP_ENTITY EN
ON C.CURRENCY_ID = EN.DEFAULT_CURRENCY AND
O.OBJECT_ID = EN.ENTITY_ID), '') AS 'Currency',
-- Remember, there can be up to five plan types per Planning
-- application, so CONSOL_OP1 through CONSOL_OP5 are valid.
CASE M.CONSOL_OP1
WHEN 0 THEN '+'
WHEN 1 THEN '-'
WHEN 2 THEN '*'
WHEN 3 THEN '/'
WHEN 4 THEN '%'
WHEN 5 THEN '~'
WHEN 6 THEN '^'
END AS 'Operator',
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 'Storage',
CASE M.TWOPASS_CALC
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
END AS 'Two Pass',
-- Change the syntax of the CASE statement to support testing for NULL
-- Dave's Unofficial Guide has DATA_TYPE = 0 as Unspecified, but in
-- 11.1.2 that isn't the case.
CASE
WHEN M.DATA_TYPE IS NULL THEN 'Unspecified'
WHEN M.DATA_TYPE = 1 THEN 'Currency'
WHEN M.DATA_TYPE = 2 THEN 'Non Currency'
WHEN M.DATA_TYPE = 3 THEN 'Percentage'
WHEN M.DATA_TYPE = 4 THEN 'SmartList'
WHEN M.DATA_TYPE = 5 THEN 'Date'
WHEN M.DATA_TYPE = 6 THEN 'Text'
END AS 'Data Type',
-- As far as I can tell, this isn't used in Planning, but is still
-- there. Why?
CASE
WHEN M.USED_FOR_CONSOL = 0 THEN 'False'
WHEN M.USED_FOR_CONSOL = 1 THEN 'True'
ELSE 'Undefined'
END AS 'Used for consol',
ISNULL(F.FORMULA, '') AS 'Formula',
-- The subquery will puke if it returns more than one
-- UDA, so do string concateenation using FOR XML PATH
ISNULL((STUFF((SELECT ',' + U.UDA_VALUE
FROM HSP_MEMBER_TO_UDA MU
INNER JOIN HSP_UDA U ON
MU.UDA_ID = U.UDA_ID AND
MU.MEMBER_ID = O.OBJECT_ID
FOR XML PATH ('')),1,1,'')), '') AS 'UDAs',
ISNULL((STUFF((SELECT '; ' +
OAN.OBJECT_NAME + ': ' + OAV.OBJECT_NAME
FROM HSP_MEMBER_TO_ATTRIBUTE M1
INNER JOIN HSP_OBJECT OAN
ON M1.ATTR_ID = OAN.OBJECT_ID AND
M1.MEMBER_ID = O.OBJECT_ID
INNER JOIN HSP_OBJECT OAV
ON M1.ATTR_MEM_ID = OAV.OBJECT_ID
INNER JOIN HSP_MEMBER MB
ON O.OBJECT_ID = MB.MEMBER_ID
FOR XML PATH ('')),1,1,'')), '') AS 'Attribute assignments',
CASE
WHEN E.ENTITY_TYPE = 0 THEN 'None'
WHEN E.ENTITY_TYPE = 1 THEN 'Employee'
WHEN E.ENTITY_TYPE = 2 THEN 'department-general'
WHEN E.ENTITY_TYPE = 3 THEN 'TBH-input'
END AS 'Entity Type',
E.USED_IN AS 'PT #',
-- Use a SQL subquery to get Plan Type tags
ISNULL((SELECT P.TYPE_NAME
FROM HSP_PLAN_TYPE P
INNER JOIN HSP_ENTITY EN
ON P.PLAN_TYPE = EN.USED_IN AND
O.OBJECT_ID = EN.ENTITY_ID), '') AS 'PT Name'
FROM HSP_MEMBER M
INNER JOIN HSP_OBJECT O
ON M.MEMBER_ID = O.OBJECT_ID
INNER JOIN HSP_OBJECT PO
ON O.PARENT_ID = PO.OBJECT_ID
INNER JOIN HSP_ENTITY E
ON M.MEMBER_ID = E.ENTITY_ID
LEFT OUTER JOIN HSP_MEMBER_FORMULA F ON
M.MEMBER_ID = F.MEMBER_ID
-- Entity dimension is ALWAYS DIM_ID 33, but its name
-- can vary, so use the ID
WHERE M.DIM_ID = 33
What does this look like?
How about that multiple Plan Type example?
Unsurprisingly, when an Entity is shared across Plan Types, and the bitmap doesn’t correspond to a PLAN_TYPE value, the above code can’t identify the Plan Type description. I leave it to to you, loyal reader, to figure out how to derive the name. (Hint – it can’t be that hard – use the bitmap, simple addition, and test with CASE.)
