Calc Manager Date Difference issue - Surprises in Calc Manager Part II

Well this one was an interesting surprise.

For those who are interested Vicks_ reported an issue in OTN forum, that the date difference calculation in Calc Manager are not showing up correctly.

For the dates given below

Start Date: 11/29/14
End Date: 03/09/15

It shows Number of days as 100 where it is really 101.

For those who are not aware of some of the date functions in Calc Manager, you can use the following functions to arrive at this result

@CalcMgrdatediff("EndDate","Start date","Day")
@HSPDateDiff("EndDate","Start Date")
@CalcMgrDaysBetween("EndDate","Start Date")

Now when I looked at the question I remembered about an issue with Octal numbers (I got burned by this when I was using a windows batch to update subvars)

I was at a client where the substitution variables were created from System date and I wrote a batch file to automate the process. There is one (among many, Quarters, Months,...I guess there were 21 subvars in total) sub var which gives the previous day.





What happens is when you add a 0 in front of a single digit number windows treats that as an octal number, however 08 and 09 are not valid Octal numbers. I was using unix date command to return date as
However after this I changed the day component to %%-d.
So first thing that came to my mind was since 9 was involved was this, however further testing revealed that was not the case.

The next thought was could it be a leap second issue, the way the day difference is calculated is convert the start and end date to milliseconds, find the difference and divide the difference with 86400000 (24*60*60*1000)

Now if you add a second then the result will not a whole number, however I found that this was not the case either. (Leap second addition is rare and the dates in question was not within that timeframe)

What else could be causing it, well if you said "Day Light Saving" you are absolutely correct!!!!

Now another reason to hate DST.

So let's look at it.

So anything after March 8 will have an issue and that why 100 instead of 101. The same will happen each year.


Now the good thing was Calc Manager team released a quick patch (they changed the way the date difference were calculated)and the issue was confirmed to be solved. Didn't I tell you that Calc Manager team is awesome....

Update Smart View options for all Users

Second post of the day. I'm working too hard ;) You all might have done this already at your client, however if you have not then read on.

All of us might have done this one way or the other. Maybe you gave a deck to all Smart View users and made them set the options for Suppression, Update the Shared Services URL and so on.

You don't need to do all these steps now. It can all be done by a bat file and SmartView.exe

Some things that might interest you are.

Shared Connections URL is coming from a file!!!

Yup if you look under %APPDATA%\Oracle\SmartView you'll see these files.


Open properties.xml


The default url is under the overrideWorkspaceUrl and all previous ones are under previousURLList

Now if you look at the Options, there are fed from a registry key.


Look for a key called CAOptionsXML under HKCU\Software\Hyperion Solutions\HyperionSmartView\Options, yup it is a XML (no surprises there)

Now all you've to do is to set the Global options from an Admin laptop, copy the value of CAOptionsXML.

Find and replace all " with """

Create a Install.cmd file as given below

ziExpand is nothing but your Zoom in Level (rest of them I guess is self explanatory)

Tada, that's it. I've even added the timeout setting with that script.

Using Attributes along with Suppress Missing options in PBCS

KScope16 was great. I should admit that I couldn't attend many sessions and I'm definitely going to watch the recorded videos.

I met old friends, made some new friends. 

I would like to thank all of you for the nice words about my blog. I'm glad that someone is reading it and it is making your life easier.

As I was at KScope enjoying the speaker reception there was a question about using Attribute dimension on the forms. As you are aware you can now use Attribute dimensions on forms, use that in Page/Row/Columns.

I know (and Oracle) too that there is a bug which prevents to use Suppress missing (rows/columns) when attributes are present in the form. Now that is a deal breaker isn't it.

Chris Rothermel and me were so interesting in trying out a suggestion given by Shankar Viswanathan (I just happen to have an early release of 16.07 in June). People were staring at us and made fun of us for working during a reception, but hey that's what we do isn't it ;)

So here is the nice trick to use Attributes along with suppress missing options in PBCS 16.07

1. Create a User variable and select the Attribute dimension as "Dimension"


2. Use the User variable in Row/Column, as you can see I've suppression turned on. (also you can see now attribute dimensions can be selected just like any other dimension)


3. Enable dynamic user variables


That's it you are all set. I can choose an attribute and edit my numbers!!!


You need to perform the above given steps, if you are entering data using attributes. You don't need any of these if you are just looking at data for analysis purposes. 

Get Text IDs from PBCS

I always had issue with the way OutLineLoad extracts data, it can called as a sort of one dimensional (or two) export.

Only one dimension in row and one dimension in column. Now you might say why use that when you've DATAEXPORT. I agree and at the same time disagree with you.

I agree DATAEXPORT can work if you are extracting numbers
I disagree when you are using DATAEXPORT for text and Smart List, because that is just going to give you numbers.

Well, now if you are the smart one (which I know you are and that's why you asked me to use DATAEXPORT) you can load the extracted data to a table, perform a lookup against HSP_TEXT_CELL_VALUE table (ID column) and return the text.


Yes that is perfectly possible in an On-Prem world, what about PBCS? :)

You cannot see the backend table, so there is no possibility of looking at HSP_TEXT_CELL_VALUE table.

Back to the Future (hmm no, Forward to the Future)

Let's take a look at the Migration options, shall we.


Oh, what do we see here, oooh Text Values, Cell Texts (lot of other stuff, which is not important right now)

Perform a "Migration" of the artifact and let's look at the file.


It is an XML file (pity!!!) with the text value and id, this is the same id that gets stored in Essbase.

Now let's create a DATAEXPORT script (now you are going to ask me what file path will I give in PBCS, hold on, dear reader :))


What do you see?!!!! (It took a long time to know that I could use /u03/lcm (I was using /u03/lcm/import/<LCMName>, thanks to Chris Rothermel and my older brother from completely different set of parents, Cameron Lackpour for sharing this wisdom on a N54 post)

Well now I know and you know that
/u03/lcm is the PBCS Inbox/Outbox location
/u03/lcm/import is the Migration aka LCM location
/u03/inbox/data is the Integrations aka Data Management aka FDMEE lite Inbox location

There we are I got my extract in PBCS inbox location.


File before conversion

Steps that you should perform
EPMAutomate to run the rule
EPMAutomate to exportsnapshot
EPMAutomate to download both files.
Extract the XML file from LCM.zip
Use the below given groovy script (Well I'm still learning, so you might find more faster, less number of line code to do the same job) to convert Essbase output to Planning Text output.

File after conversion

Cameron is going to kill me for teasing him, with all these new stuff that I'm talking about. Older brother forgive me ;)

There you go a faster way to get Text data from Planning. Now I'm lazy enough to write the entire code, back to sleep now.

Configuring PBCS to Federate with Microsoft Azure Active Directory Base version

Some you might know that Oracle PBCS now supports Single Sign-On.

If not here you go,
Oracle Planning and Budgeting Cloud November Update RCD
Administering Oracle Cloud Identity Management
Configuring Active Directory Federation Services 3.0 as an Identity Provider with Oracle Cloud as Service Provider

I was at a customer who has Office 365 and with that they get Azure Active Directory and I had to configure Single Sign using Azure Base version.

I wrote an article on that for OTN and here is the link

Configuring Azure AD Base Version as an Identity Provider with Oracle Planning and Budgeting Cloud Service

Views discussed in this blog are my personal views.
They do not represent the view of organizations, businesses or institutions that I'm part of.

Blog Archive