PBCS Backups with PowerShell: Part 1

I’ve spent a lot of time lately on homelab topics, so I thought I would take a break and put together a post on an EPM topic!  Today we’ll be talking about PBCS backups.

Why Do We Need PBCS Backups

You might be thinking, “Why do I need PBCS backups when Oracle does that for me?”  That’s an excellent question.  The problem is that while Oracle does perform nightly backups of PBCS, they overwrite that backup each night.  So at any given time I only have one backup.  To make things even worse, Oracle has a size limit on your PBCS instance.  That limit is 150GB.  This means that even if we had multiple backups on our pod, we’ll eventually start losing them to the data retention policies.

So what do we do?  We generate a new backup every night and download it to a local server.  The good news is that you almost certainly have a local server already running EPM Automate.  EPM Automate is the automation tool for Oracle’s EPM Cloud suite.  You can use EPM Automate to load data, execute calculations, update meta-data, and…perform backups.  So, we’ve established that we likely need more than a single night of backups, but how many do we need?  This will depend on a few things like the size of your applications and the frequency of change.  For our example, we will keep 30 days of daily backups.

Batch vs. PowerShell

Now that we have determined what we are backing up and how many backups we need to keep, we need to move on to actually performing the backups.  With EPM Automate, we have two commonly used options.  First, we have the old-school method of a batch file.  Batch files are great because they just work and you can find a ton of information on the web about how to do things.  Batch are, however, very limited in their ability to do things like e-mail notifications and remote calls without external tools.  That brings us to PowerShell.  PowerShell is essentially a batch that has the full set of .NET programming capability along with other goodies not directly from .NET.  What does that mean exactly?  That means there is very little I can’t do in PowerShell.

Directory Configuration

Before we configure anything, we need to get a folder structure put together to support scripting, logging, and the actual backup files.  You may already have a structure for your automation processes, but for our example, it will look something like this:

  • C:\Oracle
    • C:\Oracle\Automation
      • C:\Oracle\Automation\Backup
      • C:\Oracle\Automation\Log

EPM Automate Configuration

EPM Automate is a great tool, but we do need to perform a little bit of setup to get going.  For instance, while EPM Automate supports plain text passwords, that wouldn’t pass muster with most IT security groups.  So before we get into PowerShell, let’s encrypt our password.  This is a fairly easy process.  We’ll start up a command prompt and change directory to our EPM Automate bin directory:

cd\
cd Oracle\EPM_Automate\bin

Once we are in the right directory, we can encrypt our password:

epmautomate.bat encrypt YourPasswordGoesHere PickYourKey c:\Oracle\Automation\password.epw

Here are the parameters:

  • Command – the command EPM Automate will execute
    • encrypt
  • Password – the password of the account you plan to use
    • YourPasswordGoesHere
  • Key – you specify anything you want to use to encrypt the password
    • PickYourKey
  • Password File – The full path and file name of the password file that will be generated
    • c:\Oracle\Automation\password.epw

Once we execute the command, we should have our password file so that we can continue.  It should look something like this:

 

Backing Up PBCS with PowerShell

For our first part of this mini-series, we’ll stick with just a basic backup that deletes older backups.  In our next part of the series, we’ll go deeper into error handling and notifications.  Here’s the code..

Path Variables

#Path Variables
$EpmAutomatePath = "C:\Oracle\EPM_Automate\bin\epmautomate.bat"
$AutomationPath = "C:\Oracle\Automation"
$LogPath = "C:\Oracle\Automation\Log"
$BackupPath = "C:\Oracle\Automation\Backup"

We’ll start by defining our path variables.  This will include paths to EPM Automate, our main automation directory, our log path, and our backup path.

Date Variables

#Date Variables
$DaysToKeep = "-30"
$CurrentDate = Get-Date
$DatetoDelete = $CurrentDate.AddDays($DaysToKeep)
$TimeStamp = Get-Date -format "yyyyMMddHHmm"
$LogFileName = "Backup" + $TimeStamp + ".log"

Next we’ll define all of our data related variables.  This includes our days to keep (which is negative on purpose as we are going back in time), our current date, the math that gets us back to our deletion period, a timestamp that will be used for various things, and finally our log file name based on that timestamp.

PBCS Variables

#PBCS Variables
$PBCSdomain = "yourdomain"
$PBCSurl = "https://usaadmin-test-yourdomain.pbcs.us2.oraclecloud.com"
$PBCSuser = "yourusername"
$PBCSpass = "c:\Oracle\Automation\password.epw"

Now we need to set our PBCS variables.  This will include our domain, the URL to our instance of PBCS, the username we’ll use to log in, and the path to the password file that we just finished generating.

Snapshot Variables

#Snapshot Variables
$PBCSExportName = "Artifact Snapshot"
$PBCSExportDownloadName = $PBCSExportName + ".zip"
$PBCSExportRename = $PBCSExportName + $TimeStamp + ".zip"

We’re nearing the end of variables as we define our snapshot specific variables.  These variables will tell us the name of our export, the name of the file that we are downloading based on that name, and the new name of our snapshot that will include our timestamp.

Start Logging

#Start Logging
Start-Transcript -path $LogPath\$LogFileName

I like to log everything so that if something does go wrong, we have a chance to figure it out after the fact.  This uses the combination of our log path and log file name variables.

Log Into PBCS

#Log into PBCS
Write-Host ([System.String]::Format("Login to source: {0}", [System.DateTime]::Now))
&$EpmAutomatePath "login" $PBCSuser $PBCSpass $PBCSurl $PBCSdomain

We can finally log into PBCS!  We’ll start by displaying our action and the current system time.  This way we can see how long things take when we look at the log file.  We’ll then issue the login command using all of our variables.

Create the Snapshot

#Create PBCS snapshot
Write-Host ([System.String]::Format("Export snapshot from source: {0}", [System.DateTime]::Now))
&$EpmAutomatePath exportsnapshot $PBCSExportName

Again we’ll display our action and current system time.  We then kick off the snapshot process.  We do this because we want to ensure that we have the most recent snapshot for our archiving purposes.

Download the Snapshot

#Download PBCS snapshot
Write-Host ([System.String]::Format("Download snapshot from source: {0}", [System.DateTime]::Now))
&$EpmAutomatePath downloadfile $PBCSExportName

Once the snapshot has been created, we’ll move on to downloading the snapshot after we display our action and current system time.

Archive the Snapshot

#Rename the file using the timestamp and move the file to the backup path
Write-Host ([System.String]::Format("Rename downloaded file: {0}", [System.DateTime]::Now))
Move-Item $AutomationPath\$PBCSExportDownloadName $BackupPath\$PBCSExportRename

Once the file has been downloaded, we can then archive the snapshot to our backup folder as we rename the file.

Delete Old Snapshots

#Delete snapshots older than $DaysToKeep
Write-Host ([System.String]::Format("Delete old snapshots: {0}", [System.DateTime]::Now))
Get-ChildItem $BackupPath -Recurse | Where-Object { $_.LastWriteTime -lt $DatetoDelete } | Remove-Item

Now that we have everything archived, we just need to delete anything older than our DateToDelete variable.

Log Out of PBCS

#Log out of PBCS
Write-Host ([System.String]::Format("Logout of source: {0}", [System.DateTime]::Now))
&$EpmAutomatePath "logout"

We’re almost done and we can now log out of PBCS.

Stop Logging

#Stop Logging
Stop-Transcript

Now that we have completed our process, we’ll stop logging

The Whole Shebang

#Path Variables
$EpmAutomatePath = "C:\Oracle\EPM_Automate\bin\epmautomate.bat"
$AutomationPath = "C:\Oracle\Automation"
$LogPath = "C:\Oracle\Automation\Log"
$BackupPath = "C:\Oracle\Automation\Backup"

#Date Variables
$DaysToKeep = "-30"
$CurrentDate = Get-Date
$DatetoDelete = $CurrentDate.AddDays($DaysToKeep)
$TimeStamp = Get-Date -format "yyyyMMddHHmm"
$LogFileName = "Backup" + $TimeStamp + ".log"

#PBCS Variables
$PBCSdomain = "yourdomain"
$PBCSurl = "https://usaadmin-test-yourdomain.pbcs.us2.oraclecloud.com"
$PBCSuser = "yourusername"
$PBCSpass = "c:\Oracle\Automation\password.epw"

#Snapshot Variables
$PBCSExportName = "Artifact Snapshot"
$PBCSExportDownloadName = $PBCSExportName + ".zip"
$PBCSExportRename = $PBCSExportName + $TimeStamp + ".zip"

#Start Logging
Start-Transcript -path $LogPath\$LogFileName

#Log into PBCS
Write-Host ([System.String]::Format("Login to source: {0}", [System.DateTime]::Now))
&$EpmAutomatePath "login" $PBCSuser $PBCSpass $PBCSurl $PBCSdomain

#Create PBCS snapshot
Write-Host ([System.String]::Format("Export snapshot from source: {0}", [System.DateTime]::Now))
&$EpmAutomatePath exportsnapshot $PBCSExportName

#Download PBCS snapshot
Write-Host ([System.String]::Format("Download snapshot from source: {0}", [System.DateTime]::Now))
&$EpmAutomatePath downloadfile $PBCSExportName

#Rename the file using the timestamp and move the file to the backup path
Write-Host ([System.String]::Format("Rename downloaded file: {0}", [System.DateTime]::Now))
Move-Item $AutomationPath\$PBCSExportDownloadName $BackupPath\$PBCSExportRename

#Delete snapshots older than $DaysToKeep
Write-Host ([System.String]::Format("Delete old snapshots: {0}", [System.DateTime]::Now))
Get-ChildItem $BackupPath -Recurse | Where-Object { $_.LastWriteTime -lt $DatetoDelete } | Remove-Item

#Log out of PBCS
Write-Host ([System.String]::Format("Logout of source: {0}", [System.DateTime]::Now))
&$EpmAutomatePath "logout"

#Stop Logging
Stop-Transcript

The Results

Once you execute the PowerShell script, you should see something like this:

Conclusion

There we have it…a full process for backing up your PBCS instance.  The last step would be to set up a scheduled task to execute once a day avoiding your maintenance window.


PBCS vs. On-Premise Hyperion Planning: New Features

As we all know, Oracle has put virtually all of their development efforts into the cloud.  This is especially true for the EPM Suite of products (PBCS, FCCS, ARCS, etc.).  As a result, PBCS keeps getting great new features that we may never see in on-premise Hyperion Planning.  I was talking to Jake Turrell today and we were comparing notes on the new functionality that we have used in PBCS on projects recently.  That conversation devolved into us making a rather long list of new features.  Special thanks to Jake for helping me make this list, as I wouldn’t have thought of a good portion of the things on it without his help.  So what new functionality has been added to PBCS that will likey never make it to on-premise Hyperion Planning?

Forms

Hyperion Planning has existed for over 15 years now, so you might think that the form design capabilities would be fully-baked by now.  For the most part, this is a true statement.  But, there have been some pretty big holes that PBCS has finally filled.  Two new additions in particular make for a better form design experience for developers:  Exclusions and Ranges.

Exclusions

In Planning, when we attempt to select members, that’s the only option…select members.  In PBCS, they have added the ability to edit the selection (our old select members option) and the ability to add exclusions.  Exclusions give us an easy way to take, for example, inclusive descendants of our entity dimension while excluding a specific list.  This is particularly useful when we are referencing a substitution variable or a user variable.  We don’t know the full extent of what could be returns, but we do know what we definitely don’t want.

Ranges

When you do monthly forecasting, nothing has been more annoying in form design than the inability to easy specify a range of members.  In Planning, I can’t just ask the form to give me Jan through &CurrentMonth in one column and &CurrentMonth through Dec in another column.  This means to really make my forms dynamic, I need more substitution variables than I’m comfortable with and a form that has a ton of columns with the combinations.  In PBCS, I now have four new member selection functions that allow me to put together a range:

  • Left Siblings
  • Left Sibling (inc)
  • Right Siblings
  • Right Siblings (inc)

Finally!  I can do a range of members with just two columns and a single substitution variable!

Formatting

We can now format our forms!  You can change colors, font styles, add lines, along with other formatting options.  These options will show up in Excel and in the Simplified Interface.  This does not work in Workspace…but who cares, it’s officially dead in PBCS anyway as of the February release coming out shortly.

Smart Forms

Not to be confused with regular forms…we have Smart Forms.  This is an exciting new feature that allows you to take an ad hoc form, add formulas, and save them to the actual form!  While this is cool for a demo, I’m not necessarily a fan in practice.  While it is much better than building formulas in an actual form, which is painful, it still presents a problem.  Why are you doing form math?  In general I try to put math back in the Essbase model rather than having formulas on multiple forms.

Periods

In Planning, if I want to add periods to just a single plan type, I’m totally out of luck.  The boxes are all grey and there’s no way around it.  In PBCS, I can now simply un-check the plan types from which I would like to exclude the member.  This is a simple feature, but makes a massive difference in the flexibility in our designs.

Years

For literally years I’ve helped companies add and delete years from Planning applications.  There are a few ways to do this, but none of them are supported or in the interface.  In PBCS, if I want to delete a year, I simply select the year and click the delete button.  Again, this is super-simple, but so very nice to have.  Additionally, if I want to add years in the past, I can now do this in the interface!  Simply add the number of years you wish to add, and when PBCS asks if you would like to add them to the end, click no.  Now you have years years in the past.  This feature is a little more obfuscated, but still pretty simple.

Data Maps

On-Premise planning does have the idea of a reporting cube and it does give you the ability to create some level of mapping.  But it definitely doesn’t do what PBCS does.  PBCS has the ability to map and move data on the fly and then it takes it a step further:  Smart Push.  Smart Push is one of the most amazing features that they have added to PBCS.  For many applications, it gives us the ability to have an ASO cube with live data from our BSO cube with no crazy partitions or really any work at all beyond the mapping.  So as long as we input to our BSO cube and report from our ASO cube, I may never need to aggregate my BSO cube again.

It is fair to mention that while this functionality is not baked into Planning, if you really need it, you can build it from the ground up with some fancy scripting on the back end.  Even still, it doesn’t hold a candle to the ease of use and stability of Smart Push.

Valid Intersections

I’ve been demoing Planning and Essbase for a very long time.  When people ask what benefit Essbase might have over Planning, there are very few good answers.  One of those answers however has always been that Essbase can support what we call matrix security.  This is essentially the ability to allow a user to have write access to a cross dimensional set of intersections.  For instance, for Entity A I can modify Account 1000 while for Entity B I can modify Account 2000.  Planning simply doesn’t support that.  I have to give a user Entity A, Entity B, Account 1000 and Account 2000.  That user will be able to modify all combinations.

PBCS fixes this.  With valid intersections, I can create a set of intersections as defined above and limit the user’s ability to write back to invalid intersections.  From a security perspective, they still have access, but with valid intersections, they lose it.  Many people wanted valid intersections to give us the ability to cascade member selections across dimensions, which would be cool, but this functionality is just as useful.

SmartLists

I know what you’re thinking, Planning has SmartLists.  But PBCS has SmartLists that can be dynamically created directly from a dimension.  This means that I can provide the user with a list of accounts.  Big deal…who cares, right?  I care if I add an account.  With this new functionality, when an account is added, the SmartList is updated automagically.  Ok…that is a big deal.  Not content with this already amazing feature, Oracle took it a step further.  You can also reference the value of a SmartList in a calculation.  This means that I can use the selection in a SmartList to truly manipulate data.  Basically a new alias is created that references the OBJECT_ID.  That OBJECT_ID is also used as the value stored in Essbase for the SmartList selection.  Combined, I can easily reference the member that the SmartList is linked to.  Like I said…big deal.  Huuuuuge even.

Attribute Dimensions

This is another item that has some support in Planning, but missed the point.  I can technically add attribute dimensions to a Planning application and I can use them in a variety of ways.  But the two ways I need to be able to use them are missing.  They can’t be used in a form.  They can’t be used in Smart View.  I can technically use an Essbase connection directly and use them for analysis, but that only works on BSO and doesn’t work at all on ASO Plan Types.

PBCS fixes both of these issues.  I can layer in attribute dimensions easily on forms.  It also fixes the Smart View issues by allowing for attribute dimension selection in the Planning Ad Hoc connector.  We’ve only been asking for this in Planning for a decade.  The chances seem so very slim that we actually ever see it given the list ten years.

Navigation Flows

Technically speaking, the simplified interface is available in 11.1.2.4.  But I don’t think it could possibly be any worse than it is.  It’s essentially there for dashboards and everything partially works.  The simplified interface in PBCS on the other hand is pretty great.  It may require 100 extra clicks for a variety of administrative functions, but for end-users, I would consider it an upgrade.

One of the reasons I believe this is the addition of navigation flows.  I can create my own customized tile interface for my application and assign it to a user.  This means I can really create a user-specific interface tailored for a specific set of business processes.  This helps me put together a pretty awesome demo and makes end-users feel like it is a more truly customized application.

But wait, there is a downside.  I love navigation flows.  And if your users are primarily in the web-based interface, they are amazing.  If the majority of your users are in Excel however…they will totally be out of luck.  Navigation flows haven’t made it over there yet.  I’m not even sure if they can without  a major interface overhaul.

Dashboards

While we are on the topic of the simplified interface, let’s discuss dashboards.  They do exist, like the simplified interface, in 11.1.2.4.  But, much like the entire simplified interface in 11.1.2.4, they aren’t great.  PBCS has also added a variety of new visualization types:

  • Combination Graphs (seriously, how is this not in on-premise)
  • Funnel
  • Radar
  • Tile

While I believe PBCS dashboards are fantastic, they do have at least one major downside.  Again, they don’t work in Smart View.  But, it’s a dashboard, so I’ll give Oracle a free pass.

Browser Support and Mobile Support

For a very long time, Internet Explorer was it with Hyperion.  Finally, Oracle finally brought Firefox into the fold.  Now, with PBCS, it really doesn’t matter what platform you work on.  The simplified interface is fully compatible with Internet Explorer, Firefox, Chrome, and Safari.  This is of particular importance given how easily I can access PBCS from my phone or tablet.  The interface is great on mobile devices.  This is an area where dashboards can really shine.  To get mobile access in Planning, I have to bribe somebody in IT to open ports on the firewall.  And frankly, I don’t think any of us have enough money to afford the bribe necessary for that to happen.

Localization

If you haven’t done a lot of international applications, you probably don’t care about this at all.  But companies with users all over the world, PBCS has made life much, much better.  First is the ability for PBCS to automatically detect your language settings in your browser and to automatically translate everything that’s built in.  Oracle has taken this a giant leap further and added something called Artifact Labels.  Essentially I can add languages and labels to all of my objects now.  Instead of a form being Revenue Input for all of my languages, I can now label that form in any language.  This is pretty impressive compared to Planning.

Application Reporting

No, not financial reports, but reports about the application.  Planning essentially provide nothing in the way of reporting.  You can get a variety of information out of the repository, but that’s just painful.  PBCS has added a wealth of reporting options.  Here’s a quick list:

  • User Login Report – When and how often are users in the system?
  • Form Definition Report – Great for documentation, this produces a PDF of selected forms with the entire definition in a nice set of tables.  Rows, column, POV, page, business rules, etc.
  • Approval Status Report – How can I tell where everyone is on their approvals?  This will produce a report providing just that in a variety of formats including XLSX and HTML.
  • Access Control Report – See how everyone is provisioned.  It will show either explicitly assigned rights or effective rights.  Pretty convenient.
  • Activity Reports – Check out what your users are up to.
  • Access Logs – Get the full picture of everything that happened.
  • Audit Report – Finally, I don’t have to query the HSP_AUDIT_RECORDS table.  I also don’t have to go to the specific cell.  I can run a quick export to Excel.  Not perfect, but I’ll take it.

Groovy Business Rules

With EPBCS, I can now write business rules in Groovy.  These rules can go far beyond the simple bounds of Essbase data.  They can pull context from the application itself.  I am sad that this feature has not yet and will likely not ever make it into regular PBCS.  Here’s hoping.

LCM Maturity

I’ve been using LCM for a long, long time.  I can’t point to specific things in LCM that are better, but I can describe LCM in PBCS as more “mature.”  It just feels more stable and seems to work better.  This could just be in my head (and Jake’s)…

Academy

I know, on-premise applications have a ton of documentation.  But, there’s something to be said for easy access to what I’m looking for.  There is a ton of content on the Academy and much if it is especially useful for new users.  Planning for new users are basically on their own.

No Infrastructure Needs

For those of you that do infrastructure, this is not a plus.  But for the rest of us, not needing to install and configure the system is just easy.  I don’t have to worry about something in IT getting messed up.  I don’t have to worry about applying patches.  Having said that, you do lose control of your infrastructure.  But hey, it’s the cloud.

No VPN Necessary

I mentioned earlier that I can finally access my PBCS application with my mobile devices.  The cloud makes this so much easier.  Not only that, but if you need to give your consultant access to the system, it takes 5 minutes and doesn’t require hours of paperwork and begging of IT.  I love not needing yet another VPN connection just to modify a form.

Free FDMEE!

Okay, so it isn’t FDMEE.  But for most client, it does more than enough.  And again…it is free.  So stop complaining that it only loads text files.

Conclusion

Having said all of that, and it was a lot, PBCS still isn’t for everyone.  But as time passes and development continues for PBCS while it stands still for Planning, it is becoming more and more difficult to ask the question why PBCS?  Instead we really have to ask why NOT PBCS?


Drill-Through in PBCS and Hyperion Planning Without FDMEE

While recently debugging an issue with FDMEE, I needed to test drill-through in Hyperion Planning without using FDMEE.  But wait…can you even do that?  I had always planned on showing how to use Drillbridge with Hyperion Planning, but as I was talking with Jason, he mentioned we could even get it working in PBCS.  So how does this work?

Let’s start easy with Hyperion Planning.  If you happen to read Francisco’s blog, you may have already read this post about FDMEE drill-through.  It actually tells us that FDMEE just uses the Essbase drill-through definitions.  This happens to be the exact functionality that Drillbridge uses.  As it happens, if we set up drill-through on an Essbase cube that supports Planning, it just works.  See…easy.  But let’s try it with the Vision cube:

I’ll spare all of the details of the Drillbridge setup, but we’ll cover a few specifics.  First we’ll set up a test deployment specification:

PlanningPBCSDrillthrough01

So you can use all of your regular functions here, but I wanted to keep it super simple for testing purposes.  Next, we need to setup a connection to the Essbase database:

PlanningPBCSDrillthrough02

Once we deploy the report, we can take a peek at what it actually produces in Essbase:

PlanningPBCSDrillthrough03

So what happens in Planning?

Here we can see that the cell is enabled for drill-through:

PlanningPBCSDrillthrough04

When we right-click we have to click drill-through to see our choices:

PlanningPBCSDrillthrough05Once we click Drill Through we should see a list of all of the valid reports for that intersection.  Just like in Essbase, we can see multiple reports if multiple reports are defined:

PlanningPBCSDrillthrough06

Finally, we can click on the link and we are redirected to our report in Drillbridge:

PlanningPBCSDrillthrough07

So there we have it…drill-through without the use of FDMEE.  The coolest part is that this works everywhere in Planning.  Planning Web Forms, Planning Ad Hoc Grids, Planning Web Forms in Smart View, Planning Ad Hoc in Smart View, and Financial Reports.

But what about PBCS?  As it happens, PBCS works basically the same way as Planning.  The difference is, we can’t directly deploy the drill-through definition through Drillbridge.  So how do we do this?  If we look at the drill-through region defined, all we really need to do is create one manually that will point back to Drillbridge.  We’ll fire up PBCS and find a way…

Without EAS, Oracle has moved many of the features we would normally find there to Calculation Manager.  Open calculation manager:

PlanningPBCSDrillthrough08

Click on the small Essbase Properties icon:

PlanningPBCSDrillthrough09

Find the application to which you wish to add drill-through and click on Drill Through Definitions:

PlanningPBCSDrillthrough10If the database isn’t started, you will get a dialog like this:

PlanningPBCSDrillthrough11

Once the Drill Through Definitions dialog is displayed, click the plus sign:

PlanningPBCSDrillthrough12

We’ll start by entering a name and the XML contents.  I copied and pasted my on-prem XML from EAS.  Then click Add Region:

PlanningPBCSDrillthrough13

Next we add our region (copy and pasted from EAS, changing year to FY14 for PBCS Vision) and click Save:

PlanningPBCSDrillthrough14

Now let’s go see what happened:PlanningPBCSDrillthrough15

Enabled for drill-through!  Now let’s right-click and take it for a spin:

PlanningPBCSDrillthrough16

And let’s click Drill Through:

PlanningPBCSDrillthrough17

And just like on-prem we see our drill-through report name.  Now let’s click on it and…oh no!

PlanningPBCSDrillthrough18

Okay, that might be a little dramatic.  The one downside to this approach is that we are leaving PBCS to come back to an on-prem server.  So it let’s us know that this might not be secure.  Let’s just click continue and see our data!

PlanningPBCSDrillthrough19

And there we have it…working drill-through to on-prem using something other than FDMEE.  Our very own Hybrid approach.  The one thing to note from above is that the circled area will not function properly.  I’m re-using a report I created for my on-prem version of Vision, so it does show numbers, but in PBCS you will need to turn off anything that references the API.  This also means that upper level drill-through won’t work…yet.  The REST API does give us what we need to enable upper-level drill-through, so I expect this feature to be added in the future.

At the end of the day, we have basically three options for Planning drill-through:

FDMEE

Pros:

  • 100% Oracle Product with Oracle Support
  • Standard integration tool for the EPM stack
  • Loads data, audits loads, and provides drill-through

Cons:

  • Requires an additional license from Oracle
  • Does not support drill-through above level 0
  • Can’t bolt it onto an existing application without reloading data
  • The drillable content must exist in FDMEE
  • No ability to change the way the drill-through looks

Drillbridge

Pros:

  • Bolts onto any existing application
  • Insanely fast time to implement
  • Allows for full customization of the drill-through report
  • Data does not technically have to live in Essbase
  • Drill at any level, not just level 0

Cons:

  • Not an Oracle product, though supported by Applied OLAP, this can be a deal-breaker for some companies

Custom Drill-through

Pros:

  • You get total control over how you enable drill-through

Cons:

  • You get to do a mega-ton more work

For Planning, I think Drillbridge is a great alternative to FDMEE.  This is especially true for companies that don’t actually own FDMEE.  And for those of you that need upper-level drill-through, it really is the only choice short of hiring a developer to build you a custom solution.

PBCS is a little bit trickier.  There are still three options available:

FDMEE

Pros:

  • 100% Oracle Product with Oracle Support
  • Standard integration tool for the EPM stack and you likely loaded data to PBCS using it
  • Loads data, audits loads, and provides drill-through
  • You can use on-premise now, or the built-in version

Cons:

  • Does not support drill-through above level 0
  • The drillable content must exist in FDMEE
  • FDMEE on PBCS has a limited number of fields, and those fields have a character limit
  • No ability to change the way the drill-through looks

Drillbridge

Pros:

  • Bolts onto any existing application
  • Insanely fast time to implement
  • Allows for full customization of the drill-through report
  • Data does not technically have to live in PBCS

Cons:

  • Not an Oracle product, though supported by Applied OLAP, this can be a deal-breaker for some companies
  • Does not yet support upper-level drill-through (more on this later)

Custom Drill-through

Pros:

  • You get total control over how you enable drill-through

Cons:

  • You get to do a mega-ton more work

For PBCS, right now, I would generally stick with FDMEE.  Most of us are using it to load data into Planning anyway, so adding additional detail to the import format for drill-through isn’t much in the way of additional work.  However…if you need upper-level drill-through, you are out of luck…for now.  I fully expect that we will see a future release of Drillbridge that includes REST API integration.  This means that at a minimum, it should allow for upper-level drill-through.  When that happens…Drillbridge becomes a more powerful tool for drill-through for PBCS than even FDMEE.


PBCS April 2016 Update: FDMEE On-Premise Is Here, Kinda…

Another month…another update to PBCS.  This month we have a few very interesting items.  First, we have a new version of the EPM Automate Utility with a few new commands:

  • copysnapshotfrominstance – This command can essentially replicate one instance to another.  This could make migrations a bit faster and more streamlined and possible less error prone.
  • provisionreport – This provides a report of which roles are assigned to which users.  The cool part here is that it also shows the “sequence of inheritance”.
  • userauditreport – In case you wonder who is actually using PBCS, this report should tell you who logs in and when.
  • setsubstvars – Similar to the MaxL command that performs the same function, we can now use the EPM Automate tool to create and set substitution variables.

Now let’s talk about what really makes this release interesting.  PBCS now supports integration with on-premise FDMEE!  This is great news that we have all been waiting for.  But wait…it requires FDMEE 11.1.2.4.200.  This confirms the rumors we’ve been hearing.  So what’s the problem?  11.1.2.4.200 of FDMEE isn’t actually available yet.  On the bright side, hopefully this means it will be released by the end of April…before the May update of PBCS.

Of course we also have the long list of defects fixed in this release.  I’ll let you look at the PDF for the list:

RCD_PBCS_April


Public Service Announcement: No More Deleting Plan Types (11.1.2.4 and PBCS)

Someone asked me today…how do I easily delete Plan Type in Hyperion Planning.  I thought to myself…why would they ask that?  In 11.1.2.3 they added a cool new Plan Type Manager that allows you to add, delete, and rename Plan Types.  But hey, I’ll fire up Planning and take some screenshots.

First, let’s look at the 11.1.2.3 to make sure that Brian hasn’t lost his mind:

PlanTypeDelete01a

Fantastic…I may be losing my mind, but at least I’m not quite there yet.  Clearly you can rename and delete Plan Types.  So then I fired up 11.1.2.4:

PlanTypeDelete02

That looks totally different!  First, there are no text boxes to rename Plan Types.  Second, there is no delete button at all.  Interesting.  For grins, let’s look at PBCS:

PlanTypeDelete03

I guess I shouldn’t be surprised that they look the same…  So now let’s try to add a new Plan Type:

PlanTypeDelete04

It seems there is no reason to check the documentation.  They seem to have removed this functionality on purpose.  While I’m sure they had their reasons, I’m not a fan of removing functionality.  Just for fun, here are the release notes for 11.1.2.3 where they specifically call out the additional new features:

https://docs.oracle.com/cd/E40248_01/epm.1112/planning_new_features/planning_new_features.html

And if you scroll way down you should see this:

PlanTypeDelete05

When I have some more time, I’ll be posting an article on deleting Plan Types using the Planning repository.  And then when I have even more time…I’ll show you how to do it on PBCS.  In the meantime, I’m off to delete a Plan Type the hard way.


PBCS March 2016 Update: FR Web Studio Is Almost Here

If you are a PBCS user, you should have gotten or will be getting an e-mail from Kash with the March release of PBCS. The headline to this release is the new Financial Reporting Web Studio. I’ll have a full post once the patch hits my PBCS test instance (very soon). In the meantime, have a look at the docs:

http://www.oracle.com/technetwork/middleware/bi-foundation/fr-webstudio-2874377.pdf

While we were all expecting the new web-based FR studio, I didn’t realize they were overhauling the charting engine.  It’s about time!  Here’s another doc on that:

http://www.oracle.com/technetwork/middleware/bi-foundation/fr-charting-2874649.pdf

One thing that I was hoping for but see no mention of…Attribute Dimensions.  I’m guessing we are waiting for a significant Smart View update for this to become a reality.  So we’ll continue waiting.  And just in case you haven’t gotten the e-mail yet or are just curious, here’s the actual release documentation from Oracle:

RCD_PBCS_March_2016