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.


Veeam Backup & Replication Free Edition Experience and Sample

This post is a bit outside of what I normally write about, but I had some trouble getting it working and all of the web searching didn’t really help.  So just in case someone else is having the same problems I was having, Google should provide this for you!

First a quick introduction.  I have a server in my house (I’d like to thank my wife for allowing this) and it runs VMWare ESXi 5.5.  I have a couple of dozen VM’s that I run 24/7 and I’ve given access to a variety of these VM’s to about a dozen colleagues and clients.  My problem has always been backing up the VM’s with this many people in the system.  There is no production data on this system, but I’d prefer that we have a good backup of everyone’s work in case a RAID array in the system fails or in case someone breaks something on accident.

My current environment consists of a single server with a variety of RAID arrays.  All of the VM’s live on SSD storage, most of which is in RAID 0.  So given that I’m running RAID 0, I have even more reason to make sure I have up to date backups.  In addition to the SSD’s, I also have a traditional platter-based RAID 6 array.  This is where my backups will be stored.  Up until now, I have done occasional manual backups using the Veeam Backup & Replication product.  They have a free version that has worked well for one-off backups.  But, until now, it did not support a way of doing automated backups.

That all changed recently with the release of Update 2 to the Version 8 product.  Once I heard this, I of course had to get it running.  So I went to Veeam’s very own blog post on the topic:

Veeam’s Blog Post

So what trouble did I run into?  First, with one VM getting it all setup went perfectly.  It worked just fine on the first attempt and I was pretty excited.  But when I decided to add VM’s to the list, I hit a snag.  On the blog post, it clearly says to separate the list of VM’s with commas, but the sample file they have available for download says to use semi-colons.  So I finally figured that problem out and I was off and running.

The other cool thing that the backup script supports is an e-mail notification at the end.  So not only do I get free backups on an automated schedule, but I don’t have to check in to verify that they ran.  But this is where I hit my second snag.  The provided samples did not take into account that many SMTP servers require authentication to actually send mail.  So, I finally figured out how to get it working with authentication and while I was at it, I went ahead and added a logging section.  Now Veeam has a log of all of the backups, so this may be extraneous, but I like having it.  Too many MaxL scripts over the years…

So here’s my code.  I hope it helps!

# Orignal Author: Vladimir Eremin
# Created Date: 3/24/2015
# http://forums.veeam.com/member31097.html
# Modified By: Brian Marshall
# Modified Date: 7/26/2015

##################################################################
#                   User Defined Variables
##################################################################

# Names of VMs to backup separated by commas (Mandatory)
$VMNames = "Hyperion SS","Hyperion ES","Hyperion PL","Hyperion FM","Hyperion SQL","Hyperion BI","Hyperion DR","Hyperion ET"

# Name of vCenter or standalone host VMs to backup reside on (Mandatory)
$HostName = "HyperionESXI"

# Directory that VM backups should go to (Mandatory; for instance, C:\Backup)
$Directory = "G:\Backup\ESXi"

# Directory that log files should go to
$LogDirectory = "G:\Backup\ESXi\Logs"

# Description of what is being backed up
$BackupDescription = "Hyperion11123500"

# Desired compression level (Optional; Possible values: 0 - None, 4 - Dedupe-friendly, 5 - Optimal, 6 - High, 9 - Extreme) 
$CompressionLevel = "0"

# Quiesce VM when taking snapshot (Optional; VMware Tools are required; Possible values: $True/$False)
$EnableQuiescence = $True

# Protect resulting backup with encryption key (Optional; $True/$False)
$EnableEncryption = $False

# Encryption Key (Optional; path to a secure string)
$EncryptionKey = ""

# Retention settings (Optional; By default, VeeamZIP files are not removed and kept in the specified location for an indefinite period of time. 
# Possible values: Never , Tonight, TomorrowNight, In3days, In1Week, In2Weeks, In1Month)
$Retention = "In2Weeks"

##################################################################
#                   Notification Settings
##################################################################

# Enable notification (Optional)
$EnableNotification = $True

# Email SMTP server
$SMTPServer = "smtp.gmail.com"

# Email SMTP server port
$SMTPPort = "587"

# Email SMTP username
$SMTPUser="yourgmailaccount@gmail.com"

# Email SMTP password
$SMTPPass="youcanthavemypassword"

# Email FROM
$EmailFrom = "Brian Marshall " 

# Email TO
$EmailTo = "yourgmailaccount@gmail.com"

# Email subject
$EmailSubject = "ESXi 11.1.2.3.500 Backup Complete"

##################################################################
#                   Email formatting 
##################################################################

$style = ""

##################################################################
#                   End User Defined Variables
##################################################################

#################### DO NOT MODIFY PAST THIS LINE ################
Asnp VeeamPSSnapin

$Server = Get-VBRServer -name $HostName
$MesssagyBody = @()

foreach ($VMName in $VMNames)
{
  $VM = Find-VBRViEntity -Name $VMName -Server $Server
  
  If ($EnableEncryption)
  {
    $EncryptionKey = Add-VBREncryptionKey -Password (cat $EncryptionKey | ConvertTo-SecureString)
    $ZIPSession = Start-VBRZip -Entity $VM -Folder $Directory -Compression $CompressionLevel -DisableQuiesce:(!$EnableQuiescence) -AutoDelete $Retention -EncryptionKey $EncryptionKey
  }
  
  Else 
  {
    $ZIPSession = Start-VBRZip -Entity $VM -Folder $Directory -Compression $CompressionLevel -DisableQuiesce:(!$EnableQuiescence) -AutoDelete $Retention
  }
  
  If ($EnableNotification) 
  {
    $TaskSessions = $ZIPSession.GetTaskSessions().logger.getlog().updatedrecords
    $FailedSessions =  $TaskSessions | where {$_.status -eq "EWarning" -or $_.Status -eq "EFailed"}
  
  if ($FailedSessions -ne $Null)
  {
    $MesssagyBody = $MesssagyBody + ($ZIPSession | Select-Object @{n="Name";e={($_.name).Substring(0, $_.name.LastIndexOf("("))}} ,@{n="Start Time";e={$_.CreationTime}},@{n="End Time";e={$_.EndTime}},Result,@{n="Details";e={$FailedSessions.Title}})
  }
   
  Else
  {
    $MesssagyBody = $MesssagyBody + ($ZIPSession | Select-Object @{n="Name";e={($_.name).Substring(0, $_.name.LastIndexOf("("))}} ,@{n="Start Time";e={$_.CreationTime}},@{n="End Time";e={$_.EndTime}},Result,@{n="Details";e={($TaskSessions | sort creationtime -Descending | select -first 1).Title}})
  }
  
  }   
}

If ($EnableNotification)
{
$Message = New-Object System.Net.Mail.MailMessage $EmailFrom, $EmailTo
$Message.Subject = $EmailSubject
$Message.IsBodyHTML = $True
$message.Body = $MesssagyBody | ConvertTo-Html -head $style | Out-String
$SMTP = New-Object Net.Mail.SmtpClient($SMTPServer, $SMTPPort)
$SMTP.EnableSsl = $true 
$SMTP.Credentials = New-Object System.Net.NetworkCredential($SMTPUser, $SMTPPass); 
$SMTP.Send($Message)
}

$LogMessage = $MesssagyBody | ConvertTo-Html -head $style | Out-String
$LogMessage | Out-File "$LogDirectory\$BackupDescription-$(get-date -f yyyy-MM-dd-hh-mm).html"

You can find the rest of the setup information at the official blog post by Veeam, so I won’t try to re-invent the wheel here.  I’m no PowerShell expert, but the modifications served my purpose!


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…


It’s a Boy!

Our son was born this past Friday, July 31, 2015 at 12:06 PM.  He weighed 8 pounds and 6 ounces and was 20 inches long.

And now for some pictures:

image

His sister Hillary pushes him around:

image