HSP_MEMBER（第1部分–表）

提示：Hyperion planning 维度信息通过SQL提取

转载国外博主的，经过自己验证，希望你们能用到哦哈哈哈

QQ 1121622088

开始正文吧，骚年，心之所向，所向披靡

文章目录

规划资料库：HSP_MEMBER（第1部分–表）

一、表结构

Field Name	SQL Server Type	Oracle Type	Description	描述
MEMBER_ID	int	NUMBER(38,0)	The OBJECT_ID of the member.	成员的OBJECT_ID。
DIM_ID	int	NUMBER(38,0)	The OBJECT_ID of the dimension to which the member belongs.	成员所属维度的OBJECT_ID。
DATA_STORAGE	smallint	NUMBER(38,0)	The data storage type to be used in Essbase for members without formulas (members with formulas have another table). See the reference table below for details.	Essbase中用于不带公式的成员的数据存储类型（具有公式的成员具有另一个表）。有关详细信息，请参见下面的参考表。
TWOPASS_CALC	smallint	NUMBER(38,0)	The Essbase two-pass calculation property.	Essbase两遍计算属性。
			0 = not two-pass	0 =不通过
			1 = two-pass	两次1 =两次通过
CONSOL_OP	bigint	NUMBER	The consolidation operator for the member. This is actually a bitmask that allows for the storage of multiple operators across plan types in a single integer.	成员的合并运算符。实际上，这是一个位掩码，可以在单个整数中跨计划类型存储多个运算符。
			0 = Add	0 =加
			1 = Subtract	1 =减
			2 = Multiply	2 =乘
			3 = Divide	3 =除
			4 = Percentage	4 =百分比
			5 = Ignore	5 =忽略
			6 = Never	6 =永不
USED_FOR_CONSOL	smallint	NUMBER(38,0)	No longer used.	不再使用。
HAS_MBR_FX	smallint	NUMBER(38,0)	Tells us if the member has a formula. The formula itself does not exist in this table as members can have more than one formula.	告诉我们成员是否有公式。该表本身不存在公式，因为成员可以具有多个公式。
			0 = no formula	0 =无公式
			1 = formula	1 =公式
BASE_MBRID	int	NUMBER(38,0)	If the member is shared, then this contains the prototype member ID from the HSP_OBJECT table.	如果成员是共享的，则它包含HSP_OBJECT表中的原型成员ID。
ENABLED_FOR_PM	smallint	NUMBER(38,0)	Tells us if the member has been enabled for process management (workflow).	告诉我们是否已为成员启用流程管理（工作流）。
			0 = not process management enabled	0 =未启用进程管理
			1 = process management enabled (table default)	1 =已启用进程管理（表默认）
PS_MEMBER_ID	int	NUMBER(38,0)	Planning specific member ID that identifies predefined members used for custom components such as workforce (direct from OracleÉstill not exactly sure what this is for).	计划特定的成员ID，该ID标识用于自定义组件（如劳动力）的预定义成员（直接从OracleÉ仍不确定这是做什么的）。
DATA_TYPE	int	NUMBER(38,0)	The data type of the members.	成员的数据类型。
			0 = unspecified	0 =未指定
			1 = currency	1 =货币
			2 = non-currency	2 =非货币
			3 = percentage	3 =百分比
			4 = smart list	4 =智能列表
			5 = date	5 =日期
			6 = text	6 =文本
ENUMERATION_ID	int	NUMBER(38,0)	The ID of the smart list for the members. This links back to HSP_OBJECT and HSP_ENUMERATION.	成员的智能列表的ID。这链接回到HSP_OBJECT和HSP_ENUMERATION。
USED_IN	int	NUMBER(38,0)	The plan type usage for the member. This is also a bitmask that tells us which plan types are used in a single integer.	成员的计划类型用法。这也是一个位掩码，可告诉我们单个整数中使用了哪些计划类型。
HIERARCHY_TYPE	int	NUMBER(38,0)	This is a new column that is supposed to pertain to ASO plan types. I'm working on understanding what it means.	这是应该与ASO计划类型有关的新列。我正在努力理解其含义。

二、提取简易属性

我们仅查询Planning中每个成员将拥有的基本成员信息开始。需要两个表：HSP_MEMBER（用于成员信息）和HSP_OBJECT（用于父成员的成员名称和成员名称）。从一个简单的查询开始，以获取成员名称，父名称和解码的属性：

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

执行结果

获取维度权限

--QUERY 4:Member Access 
--The query returns Planning for members and forms.
SELECT O.OBJECT_NAME MEMBER,
       (select OA.OBJECT_NAME
          from HSP_OBJECT OA
         where OA.OBJECT_ID = AC.USER_ID) LOGIN_ID,
       Case AC.ACCESS_MODE
         When 1 then
          'READ'
         When 3 then
          'WRITE'
         When -1 then
          'DENY'
       End as ACCESS_MODE,
       Case AC.FLAGS
         When 0 then
          'MEMBER'
         When 5 then
          'CHILDREN'
         When 6 then
          'ICHILREN'
         When 8 then
          'DESCENDANTS'
         When 9 then
          'IDESCENDANTS'
       End as ACCESS_LEVEL,
       OT.TYPE_NAME
  From HSP_OBJECT O, HSP_ACCESS_CONTROL AC, HSP_OBJECT_TYPE OT
 where O.OBJECT_ID = AC.OBJECT_ID
   and O.OBJECT_TYPE = OT.OBJECT_TYPE

 按照维度获取

SELECT O.OBJECT_ID,
       O.Object_Type,
       O.OBJECT_NAME MEMBER_NAME,
       (select oa.object_name
          from HSP_ALIAS A, HSP_OBJECT OA
         where a.member_id = o.object_id
           and oa.object_id = a.alias_id) Alias_name, -- begin case statement 
       case M.consol_op3
         When 0 then
          '+'
         When 1 then
          '-'
         When 2 then
          '*'
         When 3 then
          '\'
         When 4 then
          '%'
         When 5 then
          '^'
         When 6 then
          '~'
       end as consol_value, -- end of case 
       PO.PARENT_ID,
       PO.OBJECT_NAME PARENT_NAME,
       PO.OBJECT_TYPE
  FROM HSP_OBJECT PO, HSP_OBJECT O, HSP_MEMBER M
 where PO.OBJECT_ID = O.PARENT_ID
   and M.member_id = O.object_id
   and O.Object_Type = 33  --33 代表一个维度类型

---

三、详细SQL-带加减乘除

在11.1.2.2和更早版本中，这是一个简单的解码。每种计划类型都有单独的列。从11.1.2.3开始，他们更改了HSP_MEMBER的结构。现在，我们只有一个CONSOL_OP字段，它再次使用位掩码。更糟糕的是，该位掩码包含多个值，因为找出一个值还不够痛苦！那么我们如何将多个值存储在一个整数中呢？

在这种情况下，他们再次使用位掩码，每个值使用三个位。因此，要存储一个十进制数1，它将变为001。当我们组合多个值时，我们将从右向左移动。因此，如果我要以此顺序存储小数点1和小数点2，则该值将变为010001。然后，此值将存储为整数。存储的整数实际上比我检查过的盒子要大很多，但是出于我们今天的目的，我们不在乎。

那么，我们如何实际确定每种计划类型的合并运算符？对于第一种计划类型，这是一个简单的操作。我们可以只使用我们的按位运算符或BITAND函数来检查该值而没有任何麻烦。当我们尝试在第二位置及以后的位置执行此操作时，就会遇到真正的困难。我们知道每个值都存储在3位中。这意味着我们只需要移三位即可检查下一个值。为此，我们只需将数字乘以我们要平移的头寸数量的乘方即可。因此，对于第二种计划类型，我们将2移至3的幂。对于第三种计划类型，我们将2移至6的幂。但是最后一件事。在执行此操作之前，我们应该检查USED_IN字段以确认我们甚至需要检查操作员。因此，这里去：

SQL SERVER

SELECT
    o.OBJECT_NAME AS MEMBER_NAME
	,op.OBJECT_NAME AS PARENT_NAME
	,od.OBJECT_NAME AS DIMENSION_NAME
    ,CASE m.DATA_STORAGE
		WHEN 0 THEN 'Store Data'
		WHEN 1 THEN 'Never Share'
		WHEN 2 THEN 'Label Only'
		WHEN 3 THEN 'Shared Member'
		WHEN 4 THEN 'Dynamic Calc and Store'
		WHEN 5 THEN 'Dynamic'
	 END AS DATA_STORAGE
	 ,CASE m.DATA_TYPE
		WHEN 0 THEN 'Unspecified'
		WHEN 1 THEN 'Currency'
		WHEN 2 THEN 'Non-currency'
		WHEN 3 THEN 'Percentage'
		WHEN 4 THEN 'Enum'
		WHEN 5 THEN 'Date'
		WHEN 6 THEN 'Text'
		ELSE 'Unspecified'
	 END AS DATA_TYPE
	 ,CONSOL_OP
	,CASE WHEN m.USED_IN & 1 = 1 THEN 
		CASE 
			WHEN m.CONSOL_OP & 6 = 6 THEN '^' 
			WHEN m.CONSOL_OP & 5 = 5 THEN '~' 
			WHEN m.CONSOL_OP & 4 = 4 THEN '%' 
			WHEN m.CONSOL_OP & 3 = 3 THEN '/' 
			WHEN m.CONSOL_OP & 2 = 2 THEN '*' 
			WHEN m.CONSOL_OP & 1 = 1 THEN '-' 
			ELSE '+' END
		ELSE
			NULL
		END AS CONSOL1_OP
	,CASE WHEN m.USED_IN & 2 = 2 THEN 
		CASE 
			WHEN m.CONSOL_OP & POWER(2,3)*6 = POWER(2,3)*6 THEN '^' 
			WHEN m.CONSOL_OP & POWER(2,3)*5 = POWER(2,3)*5 THEN '~' 
			WHEN m.CONSOL_OP & POWER(2,3)*4 = POWER(2,3)*4 THEN '%' 
			WHEN m.CONSOL_OP & POWER(2,3)*3 = POWER(2,3)*3 THEN '/' 
			WHEN m.CONSOL_OP & POWER(2,3)*2 = POWER(2,3)*2 THEN '*' 
			WHEN m.CONSOL_OP & POWER(2,3)*1 = POWER(2,3)*1 THEN '-' 
			ELSE '+' END
		ELSE
			NULL
		END AS CONSOL2_OP
	,CASE WHEN m.USED_IN & 4 = 4 THEN 
		CASE 
			WHEN m.CONSOL_OP & POWER(2,6)*6 = POWER(2,6)*6 THEN '^' 
			WHEN m.CONSOL_OP & POWER(2,6)*5 = POWER(2,6)*5 THEN '~' 
			WHEN m.CONSOL_OP & POWER(2,6)*4 = POWER(2,6)*4 THEN '%' 
			WHEN m.CONSOL_OP & POWER(2,6)*3 = POWER(2,6)*3 THEN '/'
			WHEN m.CONSOL_OP & POWER(2,6)*2 = POWER(2,6)*2 THEN '*' 
			WHEN m.CONSOL_OP & POWER(2,6)*1 = POWER(2,6)*1 THEN '-' 
			ELSE '+' END
		ELSE
			NULL
		END AS CONSOL3_OP
FROM
	HSP_MEMBER m
INNER JOIN 
	HSP_OBJECT o ON m.MEMBER_ID = o.OBJECT_ID
INNER JOIN
	HSP_OBJECT op ON o.PARENT_ID = op.OBJECT_ID
INNER JOIN
	HSP_OBJECT od ON m.DIM_ID = od.OBJECT_ID
WHERE
	od.OBJECT_NAME = 'Account'

Oracle 

SELECT
  o.OBJECT_NAME AS MEMBER_NAME
  ,op.OBJECT_NAME AS PARENT_NAME
  ,od.OBJECT_NAME AS DIMENSION_NAME
  ,CASE m.DATA_STORAGE
    WHEN 0 THEN 'Store Data'
    WHEN 1 THEN 'Never Share'
    WHEN 2 THEN 'Label Only'
    WHEN 3 THEN 'Shared Member'
    WHEN 4 THEN 'Dynamic Calc and Store'
    WHEN 5 THEN 'Dynamic'
    END AS DATA_STORAGE
  ,CASE m.DATA_TYPE
    WHEN 0 THEN 'Unspecified'
    WHEN 1 THEN 'Currency'
    WHEN 2 THEN 'Non-currency'
    WHEN 3 THEN 'Percentage'
    WHEN 4 THEN 'Enum'
    WHEN 5 THEN 'Date'
    WHEN 6 THEN 'Text'
    ELSE 'Unspecified'
    END AS DATA_TYPE
  ,CONSOL_OP
  ,CASE WHEN BITAND(m.USED_IN,1) = 1 THEN 
    CASE 
      WHEN BITAND(m.CONSOL_OP,6) = 6 THEN '^' 
      WHEN BITAND(m.CONSOL_OP,5) = 5 THEN '~' 
      WHEN BITAND(m.CONSOL_OP,4) = 4 THEN '%' 
      WHEN BITAND(m.CONSOL_OP,3) = 3 THEN '/' 
      WHEN BITAND(m.CONSOL_OP,2) = 2 THEN '*' 
      WHEN BITAND(m.CONSOL_OP,1) = 1 THEN '-' 
      ELSE '+' END
    ELSE
      NULL
    END AS CONSOL1_OP
  ,CASE WHEN BITAND(m.USED_IN,2) = 2 THEN 
    CASE 
      WHEN BITAND(m.CONSOL_OP,POWER(2,3)*6) = POWER(2,3)*6 THEN '^' 
      WHEN BITAND(m.CONSOL_OP,POWER(2,3)*5) = POWER(2,3)*5 THEN '~' 
      WHEN BITAND(m.CONSOL_OP,POWER(2,3)*4) = POWER(2,3)*4 THEN '%' 
      WHEN BITAND(m.CONSOL_OP,POWER(2,3)*3) = POWER(2,3)*3 THEN '/' 
      WHEN BITAND(m.CONSOL_OP,POWER(2,3)*2) = POWER(2,3)*2 THEN '*' 
      WHEN BITAND(m.CONSOL_OP,POWER(2,3)*1) = POWER(2,3)*1 THEN '-' 
      ELSE '+' END
    ELSE
      NULL
    END AS CONSOL2_OP
  ,CASE WHEN BITAND(m.USED_IN,4) = 4 THEN 
  CASE 
    WHEN BITAND(m.CONSOL_OP,POWER(2,6)*6) = POWER(2,6)*6 THEN '^' 
    WHEN BITAND(m.CONSOL_OP,POWER(2,6)*5) = POWER(2,6)*5 THEN '~' 
    WHEN BITAND(m.CONSOL_OP,POWER(2,6)*4) = POWER(2,6)*4 THEN '%' 
    WHEN BITAND(m.CONSOL_OP,POWER(2,6)*3) = POWER(2,6)*3 THEN '/' 
    WHEN BITAND(m.CONSOL_OP,POWER(2,6)*2) = POWER(2,6)*2 THEN '*' 
    WHEN BITAND(m.CONSOL_OP,POWER(2,6)*1) = POWER(2,6)*1 THEN '-' 
    ELSE '+' END
  ELSE
    NULL
  END AS CONSOL3_OP
FROM
	HSP_MEMBER m
INNER JOIN 
	HSP_OBJECT o ON m.MEMBER_ID = o.OBJECT_ID
INNER JOIN
	HSP_OBJECT op ON o.PARENT_ID = op.OBJECT_ID
INNER JOIN
	HSP_OBJECT od ON m.DIM_ID = od.OBJECT_ID
WHERE
	od.OBJECT_NAME = 'Account'

结果

SQL SERVER

ORACLE