Working with Strings in ASO

I needed a break from the Planning repository and recently had a colleague inquire about getting the name of a member as a string value in ASO.  This is a relatively simple process, but it occurred to me that when working with strings in Essbase, sometimes it helps to actually see what you are working with.  So how do we display text in Essbase based on an MDX formula?

First, we have to turn on typed measures.  In Essbase, the idea of typed measures are things like dates and text lists.  Typed measures also give us the ability to display formula-based text from Essbase in Smart View.  I specify Smart View because this functionality does not work in the Excel Add-In.  So let’s start by enabling typed measures:

  1. Make sure you really want to do this, because there is no undo.
  2. Open your outline (I’m using ASOsamp in this example) and go to the properties tab.
  3. Change Typed measures enabled to True.MemberName1
  4. Click OK when prompted.MemberName2

Now that we have typed measures enabled, we can try to display some text.  First let’s open the Measures dimension and add a member.  I chose ProductName as my member.  Once we have that member added, we can follow a few more steps and be ready for a retrieve:

  1. Open the Properties of the newly created member (in my case, ProductName).
  2. On the information tab, modify the Associate Format String to the following: MdxFormat([Products].CurrentMember.Member_Name)MemberName3
  3. Next we need to associate a formula for the member.  We do this because Essbase will not display a format string for a missing member.  I just used a literal zero to ensure we always have a value.MemberName4
  4. Save and restructure your cube.

We should be ready to see some text in Smart View:MemberName5

Now that we have some text, we can start to manipulate that text and see things.  First let’s do something simple, like get the length of our member name:

Set our format string: MdxFormat(NumToStr(Len([Products].CurrentMember.Member_Name)))

And in Smart View:MemberName6

Quick side-note…notice that we use the NumToStr function because the MdxFormat function requires a string parameter.

As one last sample, here’s how we use a text function like Left:

Set our format string:

MdxFormat(Left([Products].CurrentMember.Member_Name,5))

And in Smart View:MemberName7

There we have it.  Text manipulation that you can see.  Once you get your formula just right, you can use the strings in other formulas.  You can use all of your regular functions and go nuts with strings.


The Planning Repository: HSP_MEMBER (Part 1 – The Table)

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

In our last post, we covered the HSP_DIMENSION table.  HSP_DIMENSION is the starting point for all things meta-data, but no actual members live there.  Enter the HSP_MEMBER table.  Any member added to your application will be added to this table, regardless of the dimension.  This table contains all of the properties common to all dimensions whether they are built-in (Account, Entity, Etc.) or custom.  This will be part one of a two-part series related to members.  There are too many other tables to get into for just one post.  Let’s take a look at the table structure:

Field NameSQL Server TypeOracle TypeDescription
MEMBER_IDintNUMBER(38,0)The OBJECT_ID of the member.
DIM_IDintNUMBER(38,0)The OBJECT_ID of the dimension to which the member belongs.
DATA_STORAGEsmallintNUMBER(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.
TWOPASS_CALCsmallintNUMBER(38,0)The Essbase two-pass calculation property.
0 = not two-pass
1 = two-pass
CONSOL_OPbigintNUMBERThe 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
1 = Subtract
2 = Multiply
3 = Divide
4 = Percentage
5 = Ignore
6 = Never
USED_FOR_CONSOLsmallintNUMBER(38,0)No longer used.
HAS_MBR_FXsmallintNUMBER(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
1 = formula
BASE_MBRIDintNUMBER(38,0)If the member is shared, then this contains the prototype member ID from the HSP_OBJECT table.
ENABLED_FOR_PMsmallintNUMBER(38,0)Tells us if the member has been enabled for process management (workflow).
0 = not process management enabled
1 = process management enabled (table default)
PS_MEMBER_IDintNUMBER(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).
DATA_TYPEintNUMBER(38,0)The data type of the members.
0 = unspecified
1 = currency
2 = non-currency
3 = percentage
4 = smart list
5 = date
6 = text
ENUMERATION_IDintNUMBER(38,0)The ID of the smart list for the members. This links back to HSP_OBJECT and HSP_ENUMERATION.
USED_INintNUMBER(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_TYPEintNUMBER(38,0)This is a new column that is supposed to pertain to ASO plan types. I'm working on understanding what it means.

If you have ever looked at this table in past releases, you will notice a pretty big difference.  The new field CONSOL_OP has replaced all of the CONSOL_OP# fields.  We’ll touch on that in a bit.  The other change you will see is that they added a new field named HIERARCHY_TYPE.  This field was added to support ASO plan types as the hierarchies in ASO can be either Stored or Dynamic.  What you won’t see in this table are some important items: the actual member name, the member name of the parent, aliases, attributes, UDA’s, formulas, and properties related to accounts, entities, scenarios, and versions.  Given that almost everything I just listed is in another table of its very own…this is a multi-part post.

Let’s start by querying just our basic member information that every member in Planning will have.  To do this we just need two tables: HSP_MEMBER (for the member information) and HSP_OBJECT (for the member name and the member name of the parent).  So let’s start with an easy query to get the member name, parent name, and a few of the simple to decode properties:

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'

So if we look at the query, there are several things of note.  First, we have three joins, all of which are the HSP_OBJECT table.  The first join is on MEMBER_ID.  This join provides us with access to the member name and the OBJECT_ID of the parent member.  The second joins the parents OBJECT_ID (PARENT_ID) from the first join to get the actual member name of the parent.  And the third joins the dimension’s OBJECT_ID (DIM_ID) from the HSP_MEMBER table to give us the actual name of the dimension.

After we get everything joined, we just need to decode a few of the simple fields.  We use simple case statements for DATA_STORAGE and DATA_TYPE.  It should also be noted that the DATA_STORAGE here is not necessarily that useful.  Because Planning allows us to have a different data storage for each plan type, we will have another table to look at this later.  This is the main setting from the member properties screen only.

So let’s look at the results.

SQL Server (Management Studio):

HSP_MEMBER_SIMPLE_M

Oracle (SQL Developer):

HSP_MEMBER_SIMPLE_O

That was easy…what’s the big deal?  Oh wait…we still don’t have consolidation operators.  In 11.1.2.2 and earlier, this was a simple decode.  There were separate columns for each plan type.  Starting in 11.1.2.3, they changed the structure of HSP_MEMBER.  We now have just one CONSOL_OP field and it uses a bitmask (again).  Even worse, this bitmask contains multiple values, because figuring out one value wasn’t painful enough!

So how do we store multiple values in one integer?  In this instance, they are again using the bitmask using three bits per value.  So to store a decimal 1, it becomes 001.  As we combine multiple values, we do so going from right to left.  So if I want to store decimal 1 and decimal 2 in that order, the value becomes 010001.  This value is then stored as an integer.  I’ve noticed that the integer stored is actually quite a bit bigger than just the boxes I’ve checked, but for our purposes today, we don’t care.  So how do we actually determine the consolidation operator for each of our plan types?

For the first plan type, this is an easy operation.  We can just use our bitwise operator or BITAND function to check the value without any trouble.  The real difficulty comes in when we try to do this for the second position and beyond.  The good news is we know that each value is stored in 3 bits.  This means we just need to shift over three bits to check the next value.  To do that, we just multiply the number by 2 to the power of the number of positions we want to shift.  So for the second plan type, we shift 2 to the power of 3.  For our third plan type we shift 2 to the power of 6.  But one last thing.  Before we do this, we should check the USED_IN field to verify that we even need to check the operator.  So here goes:

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'

As you can see, for the first plan type, we just use a regular & or BITAND to determine the consolidation operator.  For the second and third plan types we use the power function with our base number of 2 and the number of positions we want to move to the left (if we wanted to go to the right, we would divide by the power function). 

Before we look at the results, we have just a few other important notes.  Because a negative test by both the & and BITAND function returns a 0, we can’t test for the value of 0, which is of course our most common operator (+).  Instead we have to exclude all other options that we can test for.  Assuming all of our other options fail, it must be 0 (+). 

Another similar issue results in our reverse order case statement.  Because case statement stop evaluating at the first successful case, we have to look at our bigger numbers first.  For instance, if 3 (011) or 5 (101) are compared to 1 (001) first, it would always evaluate to true.  Technically, 001 will fit into 011 and 101.  Of course that’s not what we’re looking for, so I just chose to test from biggest to smallest.  I haven’t been a DBA in a long…long time.  So for those of you out there smarter than me, let me know if there’s a better way!

And finally…let’s look at the results:

SQL Server (Management Studio):

HSP_MEMBER_COMPLEX_MOracle (SQL Developer):

HSP_MEMBER_COMPLEX_OWorks great!  You can add as many plan types as you want to this query with the examples above.  And that’s enough for one post.  In part two, we’ll go find all of those other important things that are missing from the HSP_MEMBER table.


The Planning Repository: HSP_DIMENSION

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

In our last post, we covered the HSP_OBJECT table.  Now that we’ve covered the basics, let’s dig a little deeper and start to look at how Planning manages meta-data inside of our applications.  The starting point for this is the HSP_DIMENSION table.  This table will have a pretty light set of data (just a few more rows than the number of dimensions in your app) but has quite a few properties.  Each of the properties for the dimensions that you set in the GUI will be found here.  Let’s take a look at the table structure:

Field NameSQL Server TypeOracle TypeDescription
DIM_IDintNUMBER(38,0)The OBJECT_ID of the dimension.
ENFORCE_SECURITYintNUMBER(38,0)Determines if security is being enforced on this dimension.
0 = Don't Enforce Security
1 = Enforce Security
DIM_TYPEsmallintNUMBER(38,0)The type of dimension.
0 = None
1 = Account
2 = Time
3 = Entity
7 = Entity Hierarchy
DENSITYintNUMBER(38,0)No longer used.
USED_INintNUMBER(38,0)The plan type usage for the dimension This is a bitmask that tells us which plan types are used in a single integer.
DIM_EDITORsmallintNUMBER(38,0)Determines if the standard dimension editor can be used.
0 = Non-standard dimension, like period, scenario, etc.
1 = Standard dimension that can be modified with the standard editor , like account, entity, and custom dimensions
DENSITY1smallintNUMBER(38,0)The Essbase setting for density of the dimension.
0 = Dense
1 = Sparse
DENSITY2smallintNUMBER(38,0)See above
DENSITY3smallintNUMBER(38,0)See above
DENSITY4smallintNUMBER(38,0)See above
DENSITY5smallintNUMBER(38,0)See above
DENSITY6smallintNUMBER(38,0)See above
DENSITY7smallintNUMBER(38,0)See above
POSITION1smallintNUMBER(38,0)The Essbase setting for the position of the dimension. The position hear should line up with the number in the performance section of the GUI.
POSITION2smallintNUMBER(38,0)See above
POSITION3smallintNUMBER(38,0)See above
POSITION4smallintNUMBER(38,0)See above
POSITION5smallintNUMBER(38,0)See above
POSITION6smallintNUMBER(38,0)See above
POSITION7smallintNUMBER(38,0)See above
POSITION8smallintNUMBER(38,0)See above
POSITION9smallintNUMBER(38,0)See above
POSITION10smallintNUMBER(38,0)See above
POSITION11smallintNUMBER(38,0)See above
POSITION12smallintNUMBER(38,0)See above
POSITION13smallintNUMBER(38,0)See above
POSITION14smallintNUMBER(38,0)See above
POSITION15smallintNUMBER(38,0)See above
ENUM_ORDER1smallintNUMBER(38,0)The evaluation order of the dimension. The enum name comes Smart Lists which are stored in the HSP_ENUMERATION and HSP_ENUMERATION_ENTRY tables.
ENUM_ORDER2smallintNUMBER(38,0)See above
ENUM_ORDER3smallintNUMBER(38,0)See above
ENUM_ORDER4smallintNUMBER(38,0)See above
ENUM_ORDER5smallintNUMBER(38,0)See above
ENUM_ORDER6smallintNUMBER(38,0)See above
ENUM_ORDER7smallintNUMBER(38,0)See above
ENUM_ORDER8smallintNUMBER(38,0)See above
ENUM_ORDER9smallintNUMBER(38,0)See above
ENUM_ORDER10smallintNUMBER(38,0)See above
ENUM_ORDER11smallintNUMBER(38,0)See above
ENUM_ORDER12smallintNUMBER(38,0)See above
ENUM_ORDER13smallintNUMBER(38,0)See above
ENUM_ORDER14smallintNUMBER(38,0)See above
ENUM_ORDER15smallintNUMBER(38,0)See above
DRIVER_DIM_IDintNUMBER(38,0)The OBJECT_ID of the dimension configured for data loads in Planning.
DISPLAY_OPTIONintNUMBER(38,0)The display option selected for the dimension.
0 or null = Member name
1 = Alias
2 = Member name and Alias
3 = Alias and Member name
4 = Member count (I think this legacy)
5 = Member name and Description (I think this is legacy)

If you’ve looked at this table in the past, not too much has changed.  There are a few interesting things to mention.  First let’s look at the USED_IN field.  This field is a bitmask.  So what does that mean?  It means that Oracle is the devil.  That’s what it means.  Bitmasks are among the most annoying things you can run across when decoding someone else’s database.  But here we are, so let’s take a look.  The developers of Planning have chosen to use a single integer field to tell us all of the plan types that a specific dimension can be part of.  This means that we have up to 13 different pieces of information that are stored in a single integer.  So how do they do this?  The easiest way to illustrate this is with a table.  Here we see how we would represent all 13 plan types numerically:

Plan TypeIntegerExample
Plan Type 11Plan1
Plan Type 22Plan2
Plan Type 34Plan3
Plan Type 48Wrkforce
Plan Type 516Capex
Plan Type 632Project
Plan Type 764PlanASO1
Plan Type 8128PlanASO2
Plan Type 9256PlanASO3
Plan Type 10512PlanASO4
Plan Type 111024PlanASO5
Plan Type 122048PlanASO6
Plan Type 134096PlanASO7

So how do all of these become a single integer?  Simple  math.  Add up the ones that the dimension should be used in.  So let’s run through a few examples:

Plan Types 1 and 3:  1 + 4 = 5

Plan Types 1, 2, and 3:  1 + 2 + 4 = 7

Plan Types 1 – 13: 1 + 2 + 4 + 8 + 16 + 32 + 64 + 128 + 256 + 1048 + 2048 + 4096 = 8191

So that’s all great, but how do I determine if the plan type is used without having to build a giant set of logic?  The bitwise operator!  Basically we ask if the number is included in our sum.  For instance, is if my sum is 5, is Plan Type 2 included:

SELECT 5 & 2

This will return a 0 because 5 is a combination of 1 and 4 and does not include 2.

Let’s try a different example that returns a different an affirmative response.  If my sum is 7, is Plan Type 2 included:

SELECT 7 & 2

This will return 2.  It returns 2 because 7 does include 2.  So we can basically assume if I use the bitwise & operator and the result is not 0, then its included.

Wow…this post is getting long.  But hey, let’s keep going anyway, if you are still awake now, you must really want or need to know about how this bitwise thing works.

So how do we use this in a practical way?  Let’s take a look at the HSP_PLAN_TYPE table.  I won’t waste any space showing the structure of this table because it’s basically the same as my sample above.  The plan types by name and their numerical equivalent.  So here’s a practical example of how we can determine all of the plan types that a dimension belongs to.

SQL Server:

SELECT 
	TYPE_NAME
	,PLAN_TYPE
	,CASE WHEN (SELECT USED_IN FROM HSP_DIMENSION WHERE DIM_ID = 31) & PLAN_TYPE = 0 THEN 'Not Used' ELSE 'Used' END AS UsedDecode
FROM
	HSP_PLAN_TYPE

Oracle:

SELECT 
	TYPE_NAME
	,PLAN_TYPE
	,CASE WHEN BITAND((SELECT USED_IN FROM HSP_DIMENSION WHERE DIM_ID = 53702),PLAN_TYPE) = 0 THEN 'Not Used' ELSE 'Used' END AS UsedDecode
FROM
	HSP_PLAN_TYPE

Both queries should return a list of all of our Plan Types and whether or not the dimension in our where clause is used:

SQL Server (Management Studio):

HSP_PLAN_TYPE_M

Oracle (SQL Developer):

HSP_PLAN_TYPE_O

So now that we have made it past that diversion, let’s keep looking at our table.  The rest of the dimension table is pretty well described by the table at the very beginning of this post (does anyone remember that far back?).  But I do want to note a few interesting things.  First, you will notice that we have only 7 density fields but for position and enumeration we have 15.  Why?  I scratched my head for a minute on this one, but then it hit me…ASO doesn’t care.  So basically the repository support 7 BSO plan types and and additional 8 ASO plan types.  Now if we look at the actual limitations in Planning, we can max out Planning with 13 plan types (which you can see in our sample table above).  So we basically have an extra BSO plan type and an extra ASO plan type.  It’s also possible that they use these extra fields for some other system purpose that I’m not aware of.  If you know, drop me a comment!

So there you have it…an unexpected dive into some pretty technical workings of SQL code just to look at our dimensions.  Now that we’ve had a look at the dimensions themselves, we can start looking at the members…in our next marathon post!