Want to hire me? For availability and scheduling please email info@arcepm.com

27 November 2011

AWS tricks #2 -- Treating that AWS instance as a real server


Introduction

I really like Amazon Web Services for development purposes.  It’s fast (enough), big (more than enough), and flexible (ah, just right) for my purposes.  What do you do with it?  Have you given any thought to treating that AWS EC2 instance (that’s an Amazon Web Services Elastic Computing Cloud virtual machine for the uninitiated) as a full-fledged server?

Splendid isolation

One approach, per the my earlier post, is to install all client software, including Microsoft Excel, on the server and just use Remote Desktop Connection (RDC) to connect to that EPM cloud instance.  This is a great approach if you are the only user of the instance (like me in my development/kick the tires/play with the product till you understand it usage).  But what happens when you want to collaborate with someone else or have multiple users check out the awesomeness that is your Essbase/Planning/HFM/Profitability/Close Management/Profitability/Studio/ODI/etc./etc./etc. work?

No geek is an island

Amazon Web Services (AWS) is predicated on servers, and after all the EPM software is server-based – why not treat your instance like a server and your PC like a client?  While connecting as a client to your EPM instance in the cloud is slightly harder than connecting to a server down the hall, it is easier than you think and requires just a few changes to the AWS Security Group.

 

And of course there is a huge benefit – more than one person, e.g., your customers, be they internal or external, can connect to the EPM server.  You do like to share, right?

Requirements

You must have:

  • A valid AWS id
  • An already created security group
  • A PC with Excel 2007 or 2010 (2003 is also supported but the examples show 2007/2010’s ribbon interface)
  • Already read the most-read (Google Analytics tells all) post I have ever written
  • Microsoft’s Remote Desktop Connection (RDC).  This client is standard in Windows; Macintosh users go here:  http://www.microsoft.com/mac/remote-desktop-client)
  • Time and a willingness to experiment  :)

 

Make sure you do not have Smart View installed as its download and installation is covered below.

Understanding AWS Security Groups

Ports

Security groups are used to define the open ports between an Amazon Web Services (AWS) instance and its clients.  By default all server ports are closed by Amazon’s firewall.  These ports must be opened for a client to connect to the instance.

 

As was illustrated in the “Yeah, yeah 11.1.2.1 is here, so what?” instructions, managing the AWS Windows instance required port 3389 to be opened to allow RDC to connect.

IP addresses

AWS provides a powerful method to limit unauthorized connection to an instance – internet protocol (IP) address filtering.  In essence, AWS allows you to do define which client external IP addresses will connect to a given instance.  It can be the whole world, or it can be just the EPM users in a given location or company.

 

This external IP address is not the same as your PC’s internal address on the LAN – it is the address the outside world sees and interacts with; usually one address is shared for a given organization or location.  Sophisticated AWS security is beyond the scope of this post (not to mention my abilities) but to get a taste of what is possible, investigate Amazon’s Virtual Private Cloud and the Elastic IP component of Elastic Compute Cloud for more information. 

 

For simplicity, this document will use a 0.0.0.0 IP address, as did the 11.1.2.1 instructions.  In AWS, a source IP address of 0.0.0.0 allows connectivity from and to all client IP addresses on the internet, including the ones belonging to those nasty people who do nothing but troll the ‘net looking for vulnerable servers.  You do not want to use this completely open IP address range for anything other than training purposes.  Figuring out your public IP address is easy; here is one site that provides this free service:  http://whatismyipaddress.com/ip-lookup

 

One final note on wide open IP addresses -- when the authors connect to AWS, whether it be from work or home, they always use their public IP address, never 0.0.0.0. 

Opening up the ports

A good test of true client/server connectivity is Smart View against Essbase.  Smart View uses ports 19000 for shared connections and 13080 for Essbase private connections.  If you set up your security group as described in the post-I-am-mentioning-yet-again, the only open port in your AWS security group will be Remote Desktop’s 3389.

Modifying the Security Group

You will need to expand the open ports by first logging in to AWS’s Elastic Compute Cloud (EC2) console and then clicking on the Security Groups link: 

 


Once in the group, in this case it is called KScopeTest, click on the Inbound tab:


Adding port 19000

You will see port 3389 already open.  To add port 19000, type in the port number in the Port range field and then the Add Rule button:

Adding port 13080

Do the same for the private connections on port 13080:


You can click on the Apply Rule Changes button at any time to commit the new port number; you must do so at the end of the port addition steps.

Knowing when to connect

As you know, it can be hard to determine when an AWS instance has started up after launch from an Amazon Machine Image (AMI).  The AWS console instance information is limited to four states:  terminated, stopped, pending, and started.  Started means just what it says – the instance is started, but all that really means initially is that the server has been turned on, not that you can connect.

 

When an instance has not fully booted, it cannot accept connections; the only way to know if you can connect or not is to try.

Ping comes to the rescue

One way to gain some insight into whether an instance can be connected to is to see if basic networking protocols can be used, e.g, pinging the box using the ping utility.  However, by default AWS instances can not be pinged because the protocol that allows ping and traceroute is disabled.  Thus a further modification of the security group is required to allow  ping from your client to reach the AWS instance. 

 

To do this, click on the Create an new rule dropdown and select Custom ICMP rule:


 

When you select Custom ICMP rule, the Type dropdown automatically switches to All.

 

Click on the Add Rule button to add the ICMP ports.  You must also click on the Apply Rule Changes button to commit the port changes.


 

Once committed, the Security Group should look like the following:


Port review

Port

Purpose

ICMP/All

Allow ping, traceroute, etc.

3389

Allow Remote Desktop connection

13080

Smart View private connections to Essbase

19000

Smart View shared connections, Workspace, etc.

 

We have only opened the ports required for connection:  ICMP, 3389, 13080, and 19000. 

 

As an example of port access, if your EPM instance were to support the classic Essbase Excel add-in, add port 1423 to allow connecting to Essbase and ports 32768 through 32778 to allow connections to the Essbase agent and databases.

 

As a general rule of firewall security, only open the ports needed for your work.

Launch that instance

The detail and rationale behind the steps required to launch an instance were covered in the post-whose-name-must-not-be-mentioned-lest-we-all-go-mad; refer to it for background.  If you dare.

Which one?

For the purposes of this example, John Booth’s free and public EPM 11.1.2.1 AMI will be used.  You can find out all about the AMI at http://www.metavero.com.

3…2…1…launch!

To find and then launch that AMI, enter the AMI id ami-de2bd4b7 in the filtering textbox in the AMI section of the EC2 console and click on the Refresh button.

 

Once it appears, right click on the AMI name and select Launch Instance.   Make sure you use the Security Group KScopeTest you previously created.

 

 

Follow the Launch Instance wizard (see the below for the kinds of selections you should make) and at the Review step start the instance by clicking on the Launch button.


Two ways to see if the instance is really ready

Approach #1 – Check System Log

Right click on the instance and select “Get System Log”.


Review the log

Remember, you are firing up a Windows 2008 server.  It is not going to be fast.  Give yourself time to get a cup of coffee.  In fact, you can likely dump the grounds, scrub the filter basket, put in a new filter, measure out the coffee grounds, put in fresh water, and hit the start button and wait for that delicious pot of Life Blood.  In other words, there is likely a 10 to 15 minute wait ahead of you. 

 

You will eventually see something like the below; you may need to check back several times.  When you do see it, the log is telling you the truth – you can now connect via RDC.


Approach #2 – Ping the server

Checking the system log is all very well, but it can require multiple tests to ascertain if the instance is strated. 

 

Instead, the familiar ping command can be used to go against the instance – when Windows has booted, the ping command will be echoed back to your client.

Get the DNS name

Once the AMI is launched, grab the public DNS name by trying to connect to the Instance.  Yes, that instance will almost certainly not be ready to connect to – you are just going through the motions to get the address.

 

Right click on the instance name and select Connect.

 

The AWS console will show the DNS name.  Copy that to the clipboard.


Ping from the comand line

Start up a command window, and then enter ping DNSnameIn the case of the above DNS address (every launched instance will have a different one and yours will not match the above), the command would be ping ec2-184-73-120-11.compute-1.amazonaws.com

 

Although a successful reply from the instance is not complete proof that Windows is ready receive Remote Desktop connections, it is a good gauge that the EC2 instance and related Elastic Block Storage (EBS) drive have been created from the AMI and that the OS is in the process of booting up.


 

This is one of those differences between a GUI and a command line – the GUI requires you to act again and again, while the command line just keeps chugging away until it receives a reply.  Which would you rather do?

Connect via Remote Desktop

Once you get a reply from your ping command or from the Get System Log menu, connecti via Remote Desktop by going back to that AWS dialog box and then clicking on the Download shortcut file link.

 

In Firefox, Remote Desktop will open the .rdp (a config file for RDC) file.

 

A scary message will ensue.  Ignore it – this has nothing to do with AWS and is intead a feature of Windows 7/Remote Desktop.



Enter the password “epmtestdrive”.


 

Another scary message pops up – again this can be ignored by clicking on the Yes button.


 

When you have connected to your instance, minimize your Remote Desktop window as you will now connect from your client PC.

Connecting to an EC2 EPM image from your PC

Prove that Workspace is running

On your client PC, launch a browser instance (this example shows Internet Explorer as Workspace is optimized for that browser) and create a url to launch Workspace.

 

The normal Workspace url is:

http://servername:19000/workspace/index.jsp

 

The public DNS name of this instance is ec2-184-73-120-11.compute-1.amazonaws.com

 

Incorporate the DNS name into the url as follows (remember, in your case the DNS name will be whatever Amazon assigns to the instance):

http://ec2-184-73-120-11.compute-1.amazonaws.com:19000/workspace/index.jsp

 

Type that url into the browser’s address bar and hit the Enter key.  If all of the services are running on the AWS instance, you should see the below Workspace login screen.


 

Enter a username of admin and a password of epmtestdrive and you will see the familiar Workspace home page.


Download SmartView

The goal is to connect Smart View, not Workspace.  Navigate to the Workspace Tools menu, select Install, and then Smart View.  This will begin the download to your client PC.

 

NB – This guide assumes that Smart View is not installed.  If it is, unistall it to follow the remaining steps.

 

Internet Explorer will prompt for a save location for Smart View.exe.

Install Smart View

Once saved, navigate to the same location and right click on SmartView.exe and select Run as administrator.


 

Vista and Windows 7’s User Account Control will ask if you really want to run this file – you do.

 

Click on Run and the installation process will begin.


Pick a language – in the case of this guide, that is English (United States) and click on OK.  The installation process will begin.

 

The installation process begins…

 

Follow the wizard all the way through to the end.

 

Launch your PC’s local copy of Excel – you should see the Smart View ribbon.

Defining the Application Provider Services (APS) address

After going to the Smart View ribbon, click on the Open button and then the Shared Connections link in the Connection Manager.  Smart View will surface a dialog box asking you to modify the url for Shared Services.  Smart View connects to Essbase through APS – this is the address you are really providing.

 

In the case of this EPM instance, all services are installed on a single server.

 

 

The default url is:  http://server:port/workspace/Smart ViewProviders

 

Using the same DNS name that was used to ping the server and to connect to Workspace, change that URL to (again, your DNS name will differ from the below):

http://ec2-184-73-120-11.compute-1.amazonaws.com:19000/workspace/Smart ViewProviders

 

Paste it into the Shared Connections URL dialog box in Smart View’s Options dialog box.

 

Smart View will then show the Connect to Data Source dialog box.  Enter the same username and password of admin and epmtestdrive and then click on Connect.

 

Now go into Connection Manager and click on the server drop down to define an Essbase server.


 

Then click on the Add new server dropdown.


 

Paste in the public DNS name of: ec2-184-73-120-11.compute-1.amazonaws.com and click on OK.

 

Smart View will show you the Essbase service in your instance.  Below are the sample applications that come with Essbase.

 

 

From here on out, you can connect to Essbase just as if the server was in your data center.

 

It’s the same Essbase you know and love, only in the cloud.  It really is that easy.


When you are done

AWS instances are billed by the hour – if there is no usage, nor is it anticipated, it might be in your best interest to stop the server. 

 

If you want to retain your work you have two choices:

1)      Stop (not terminate) your server via the EC2 console.  This is akin to shutting down your server.  You can bring it up at any time and will incur small storage costs based on size of $0.10 per gigabyte month.

2)      Create an AMI (coming soon on this blog) from the instance and Terminate the instance.  Charges for AMI storage are slightly less than EC2’s EBS model, but mean that you have to launch the AMI and manage multiple AMI copies.

 

If this was throwaway work, simply terminate the instance.  Remember, there is no going back to the data once the instance has been terminated.

DNS changes

Once you stop (or create an AMI and terminate the source instance) the instance, the public DNS name will be lost.  On subsequent startup, you will need to rename Smart View’s APS server name. 

 

One way around this is to use AWS’ Elastic IP service; find out more about it here:  http://aws.amazon.com/ec2/  Elastic IP will allow you define a static IP address and AWS will map the dynamic IP address to that static one so that clients do not have to change their server names; there are further configuration steps beyond the scope of this guide – search the web for more information.

 

Two alternate DNS providers are:

http://www.no-ip.com/

http://www.dyndns.com/services/dns/dyndns/

Conclusion

What do you now have?

  • A fully functioning Essbase server in the cloud that you, and your colleagues, can use for any purpose Essbase might fill.
  • A test server that can be created in minutes for experimentation when your development server is unavailable.
  • A platform for learning the features of the very latest EPM release.
  • A server that can be almost instantly adjusted, whether it be the number of CPUs, the amount oF RAM, or the hard drive size.

 

All for mere pennies an hour.

 

 


19 November 2011

Eye Sea Sea Bee Are Sea Eye

Introduction

Everyone has heard and/or used phonetic alphabets even if you haven’t been in the Services.  You know, it’s the word that sounds like the letter you’re trying to say, e.g., A-Alpha, B-Bravo, C-Charlie, etc.  

Pendant alert – according to Wikipedia (and we all know that an encyclopedia that anyone can contribute to is of the highest possible quality) it turns out that these alphabets aren’t phonetic but instead are acrophonic, which makes them the very opposite of phonetic.  But I digress yet again.  

While I personally plan on adopting the Royal Navy’s standard of 1917 so no-one ever figures out what I’m up to, thus ensuring that my evil plans for world domination (which, admittedly, are progressing somewhat behind my original schedule as I am in control of precisely nothing – I do however own a cat, or perhaps he owns me) can continue unabated.

A language for Essbase geeks

But perhaps I’m wrong in adopting the standard of Jack Tar in the Great War.  Essbase geeks do their own thing, in their own way.  C’mon, we’re unique.  And awesome.  Aren’t we?  Please say yes.

And if we’re awesome, we surely need our own acrophonetic alphabet.  Guess what?  We have one, courtesy of the ASO wizard, Dino, aka Dan Pressman.

Without further ado, here is his mad genius.
Letter
Pronunciation
Example
A
R
How are you?
C
Q
Pool cue
D
W
Double U
E
I
Eye
F
Weigh
“There’s no F’in way”
G
N
Gnat
H
Ah
Hour
I
E
Iwo Jima
J
H
Jose
K
N
Know
L
Y
Llama
M
N
Mnemonic
O
W
One
P
N
Pneumonia (or Swimming – the silent P)
Q
Key
Quay
S
C
Sea
T
Z
Tse-Tse
W
Y
Why
Y
U
You


Why Tse-Tse There’s No F’in Way

One man’s madness is another man’s genius.  I think it’s absolutely brilliant.  Your reaction may be “Whisky Tango Foxtrot.”

14 November 2011

Stupid Planning queries #8 -- More text queries than you can shake a stick at

Introduction

Thanks to a thread over on LinkedIn, I’ve been inspired to dust off, update, and generally tweak some Planning queries for getting text out of the application repository.

One of the things that Planning does rather better than Essbase (Gasp, did I just write that?  Sigh, I did, as it’s true) is integrating textual information with the numbers in Essbase.  Planning does this by storing said text in the Planning application repository and then marrying that up via web forms, Smart View (when using a Planning data source), and Financial Reports (ditto on the Planning data source).


Of course life wouldn’t be complete if there wasn’t a completely unauthorized, totally unsupported, and generally cool (Cool?  Really?  For whom?  Oh well, I think it’s cool.) series of queries to get this out.

As always with these queries, they are 100% unsupported by Oracle and there’s a tremendous chance that I’ve gotten them wrong, so test, test, test and remember that you are hacking the tables and if anything blows up you are completely on your own.  Got it?  Good, let’s begin.

And oh yes, we’ll go into some detail about how Planning handles text Accounts as that is handled somewhat differently than the other text types.

What does it look like in Planning?

This is 11.1.1.3 (I am too lazy to fire up my cloud instance and I have 11.1.1.3  at hand, there’s no difference in functionality or the back end), so not the latest and greatest, and it illustrates three Account annotations, cell text, and attached documents) out of the five different kinds of textual information, but this will do as a start.  I’ll get to text Accounts and Planning Unit/Form Instructions (believe it or not, they’re in the same table).  So just a start, be patient, and all will be revealed in due course.
Tiny little icons that really matter
Planning tells you that there’s cell text and/or cell-level documents by putting a tiny little blue (cell text) or red (cell-level document) in the upper right hand corner of the cell in question.  You can also hover over the cell to see what’s in a given cell.  The above shows that in PA, DVD Recorder, Plan, Working, FY10, Price, and Feb there is both cell text and a cell-level document.  The Supporting Detail is for free, and will be the subject of another blog post.

What does Oracle consider cell text?

Per the help:
If you have read access to a cell, you can add annotations called cell text to the cell at any level. You can add cell text at the summary time period level and across multiple dimensions at any level. You can also add cell text for non-level 0 members (bottom-up versions), calculated cells (dynamic calc), and read-only cells. For example, you can add explanations for data analysis of variances and rolling forecasts.

Cell text

It’s a bit difficult to see, but look at the icon highlighted in red:


Click on a cell, then click on that ABC icon, and you will see:

There is a limit of 2,000 characters for cell text.  How do I know?  I took a look at the table HSP_CELL_NOTE_ITEM and see that the field CONTENTS has that length.  See, SQL is good for you, or at least poking around and trying to figure things out is.  See if you can find that limit in the docs – nope, it isn’t there.  You’re welcome, no thanks are necessary.  :)

Extracting cell text

--    Purpose:    Extract Cell Text from Planning
--    Modified:    11 November 2011, Cameron Lackpour
--    Notes:        LEFT OUTER JOINS aren't necessary in a
--                single Plan Type app, but might be when
--                there's more than one.
SELECT  
    P.TYPE_NAME AS 'Plan Type',
    O1.OBJECT_NAME AS 'Scenario',
    O2.OBJECT_NAME AS 'Account',
    O3.OBJECT_NAME AS 'Entity',
    O4.OBJECT_NAME AS 'Period',
    O5.OBJECT_NAME AS 'Version',
    O6.OBJECT_NAME AS 'Currency',
    O7.OBJECT_NAME AS 'Year',
    O8.OBJECT_NAME AS 'Segments',
    I.CONTENTS AS 'Cell Text'
FROM HSP_CELL_NOTE N
INNER JOIN HSP_PLAN_TYPE P
    ON N.PLAN_TYPE = P.PLAN_TYPE
INNER JOIN HSP_OBJECT O1
    ON N.DIM1 = O1.OBJECT_ID
INNER JOIN HSP_OBJECT O2
    ON N.DIM2 = O2.OBJECT_ID
LEFT OUTER JOIN HSP_OBJECT O3
    ON N.DIM3 = O3.OBJECT_ID
INNER JOIN HSP_OBJECT O4
    ON N.DIM4 = O4.OBJECT_ID
INNER JOIN HSP_OBJECT O5
    ON N.DIM5 = O5.OBJECT_ID
INNER JOIN HSP_OBJECT O6
    ON N.DIM6 = O6.OBJECT_ID
LEFT OUTER JOIN HSP_OBJECT O7
    ON N.DIM7 = O7.OBJECT_ID
LEFT OUTER JOIN HSP_OBJECT O8
    ON N.DIM8 = O8.OBJECT_ID
LEFT OUTER JOIN HSP_CELL_NOTE_ITEM I
    ON N.NOTE_ID = I.NOTE_ID
SELECT * FROM HSP_CELL_NOTE

Cell text result

Enabling text in forms

Excuse the not quite the last version of Planning – the settings are the same in the latest and greatest, but they do look somewhat different.
Cell-level documents
What’s that URL doing in the text output (for those of you without the eyes of eagles, it’s in the second row)?  It turns out that Planning stores the links to the cell-level documents alongside cell text.  

Documents can be attached to cells via an icon the right of the ABC cell text button.
 According to the help docs:
If your administrator selects the Enable Cell-Level Document property for the data form, from data form cells, you can add, replace, and view EPM Workspace documents. These documents can be a Web site or any file type (for example, an .XLS or .PDF file). For example, you could associate a cell with a document that explains your assumptions behind the cell's sales data.

Did you catch the important bit?  The bit that makes the link to this fantastic blog (ahem) completely pointless?  The help reiterates the point:
Before you add a cell-level document, the document must be added to the Workspace repository. See Oracle Enterprise Performance Management Workspace User's Online Help.

Ohhhhhh.  Bummer.  The docs have to be imported into Workspace to be accessible.  

However, I’ve discovered that if you have a URL link in that on the Open Document icon, Planning will in fact open a new browser window.  Huzzah, For he’s a jolly good fellow, & c.  So you see, that link isn’t pointless after all.  But you still can’t link seamlessly to, oh, say an Excel document.  I don’t know what to make out of non-documented functionality, or even if it works in other releases.  Proceed With Caution.

And of course you can still pull the references via the query.  Do you feel a little better?

Account annotations

Again, these have to be enabled for a given form, but once enabled, click on a row, go to View->Edit Account Annotations and type away.

Extracting Account annotations

--    Purpose:    Extract Account annotations
--    Modified:    11 November 2011, Cameron Lackpour
--    Notes:   
SELECT
    O1.OBJECT_NAME AS 'Scenario',
    O2.OBJECT_NAME AS 'Version',
    O3.OBJECT_NAME AS 'Entity',
  ISNULL((SELECT OA.OBJECT_NAME
      FROM HSP_ALIAS A
      INNER JOIN HSP_OBJECT OA
      ON A.MEMBER_ID = D.ENTITY_ID AND
      OA.OBJECT_ID = A.ALIAS_ID), '') AS 'Entity Alias',
    O4.OBJECT_NAME AS 'Account',
  ISNULL((SELECT OA.OBJECT_NAME
      FROM HSP_ALIAS A
      INNER JOIN HSP_OBJECT OA
      ON A.MEMBER_ID = D.ACCOUNT_ID AND
      OA.OBJECT_ID = A.ALIAS_ID), '') AS 'Account Alias',
    D.CONTENTS AS 'Account Annotation'
FROM HSP_ACCOUNT_DESC D
INNER JOIN HSP_OBJECT O1
    ON D.SCENARIO_ID = O1.OBJECT_ID
INNER JOIN HSP_OBJECT O2
    ON D.VERSION_ID = O2.OBJECT_ID
INNER JOIN HSP_OBJECT O3
    ON D.ENTITY_ID = O3.OBJECT_ID
INNER JOIN HSP_OBJECT O4
    ON D.ACCOUNT_ID = O4.OBJECT_ID

Account annotation results

ScenarioVersionEntityEntity AliasAccountAccount AliasAccount Annotation
PlanWorkingE01_101_1130UnitsAn Account annotation for Units
PlanWorkingE01_101_1130PriceThis is an account annotation.



Where oh where are the other dimensions

There are lots of dimensions *not* displayed.  I’m not being stingy with the dimensions, Scenario, Version, Entity, and Account define where an Account annotation is stored and displayed.  So these annotations would hold for FY09, FY11, etc., for instance.  Switching to FY09 supports this:
Where oh where are my aliases?
I will share with you that I spent quite a bit of time trying to figure out where the heck the aliases were for E01_101_1130 which should be PA, aka the Keystone State and Price are.

Shared members and no alias

The E01_101_1130 in this form is shared.  Shared members don’t have an alias.

Here’s the form definition:


And here’s what it looks like in the Classic (the one and only) dimension editor:


I won’t embarrass myself by stating how long it took me to figure it out.  I was sure my code was wrong.  Nope, I just forgot Planning 101.  <blush>

Text Accounts

It’s easy to create a text Account:

 The text member gets surfaced in EAS:
 
The “+” doesn’t make a lot of sense – I would tag it as Never Share – “^” because as we’ll see in a minute, there’s a numeric value stored there.  Oh yes, interesting.

What does it look like in Planning?  Oh, beautiful.
 And in Essbase (not in Smart View with a Planning data source) there is data, but not of a textual nature.  Planning seems to be adding a kind of counter to the cell that is pushed to Essbase.  I wonder why they bothered:
 There’s a sting in the tail
What happens when you delete a text value?  It’s gone, right?  

It looks that way in Planning:
And Essbase:
 What happens when you add another text value back into Feb?
 5?  What’s going on?  Planning is incrementing the text counter.  Number 2 is dead and buried.  Or is it?

So what’s the big deal?

The query to get this stuff out is as easy as 22/7:
--    Purpose:    Extract text Accounts
--    Modified:    11 November 2011, Cameron Lackpour
--    Notes:       
SELECT
    *
FROM HSP_TEXT_CELL_VALUE

Text account result



Text Account data is like the Roach Motel – Planners can enter textual information but it will never, ever, ever come out.  No big deal with small amounts of data, but definitely something to bear in mind if you use this functionality a lot.  There is no TRUNCATE function.

2nd big deal

Did you note that there are two fields in this query?  How do you know what the intersection to the data is?  You know, all of the dimensions?  It is more than possible that I have this wrong, but I’ve taken a few passes through the tables – I can’t see it.  AFAIK, this is handled in the application layer by Magick, I think.  Please show me the error of my ways and I’ll update the post.

Planning Unit annotations and form instructions

Planning Unit annotations and form instructions together?  Yes, that surprised me as well.  I guess this is sort of like the way cell text and cell-level documents are stored together?   

Form instructions


Planning Unit annotations

Extracting annotations
--    Purpose:    Extract Planning Unit and Form Instructions
--    Modified:    11 November 2011, Cameron Lackpour
--    Notes:   
SELECT  
    O1.OBJECT_NAME AS 'Object',
    O2.OBJECT_NAME AS 'User',
    A.CREATED AS 'Date',
    A.TITLE AS 'Annotation',
    A.CONTENTS AS 'Contents'
FROM HSP_ANNOTATION A
INNER JOIN HSP_OBJECT O1
    ON A.OBJECT_ID = O1.OBJECT_ID
INNER JOIN HSP_OBJECT O2
    ON A.AUTHOR_ID = O2.OBJECT_ID
ORDER BY 'Date', 'User', 'Contents'

Annotations result


It would be nice if the Planning Unit annotations included the items that make up the Planning Unit – Entity, Scenario, and Version – but it doesn’t seem to be there in the table.  Is this again something that’s handled in the application layer?  Correct me via the comment function and I’ll improve the query.

Oooh, I just had a thought -- could the "PU52602" (which I did *not* enter) be a key with PU = Planning Unit and 52602 being the OBJECT_ID?  I will look into this for a future blog post -- I've spent enough of my free time on this post for this week.

Conclusion

What do we now know about Planning and text?
1)  Planning does text better than Essbase.  Groan, but it’s true.
2)  A few simple queries can pull out that textual information.
3)  Some Planning text data seems to get associated with other members in the application layer or I am too stupid to see where that dimensionality resides.  The truth is likely the latter rather than the former.  So tell me where I’ve gone wrong and I’ll fix it.

You can’t say I haven’t taken you on a trip down the rabbit hole.  Happy Planning hacking!