Patches and Updates:
Shared Services 11.1.2.4.003 has been released. Looks like a small patch that fixes an LCM issue and a task flow issue. Interestingly, this does not appear to be a cumulative patch as it specifies that it can only be applied to 11.1.2.4.002.
HFM 11.1.2.3.702 has been released. This one is cumulative to the 700 patch and has a metric ton of bug fixes.
New Blog Posts:
This week I posted about a bug in Planning that has finally been fixed.
Vijay gives us a refresher course on configuring OBDC connections for Essbase on *nix platforms.
Cameron is up to part 10 on his PBCS series. This time he dives into meta-data management in PBCS.
Justin gives us a quick reference guide for all of the consolidation operators for ASO cubes. This should be handy for future reference.
The Oracle CEAL team gives us a guide to best practices in BSO calculation performance.
Eric gives us a code sample for translations in HFM that “go against the grain”.
Dayalan covered both Planning Unit Hierarchy assignment and DRM/FDMEE integration this week.
John Goodwin brings us part 3 of his FDMEE Hybrid and REST series.
Other News:
There is a North Texas EPM Lunch schedule for Friday, May 16, 2016 at Seasons 52 in Plano. We’ll be there from 11AM to 1PM. Be sure to RSVP if you can make it out:
http://www.meetup.com/Dallas-Oracle-EPM-Professionals-Lunch/events/230687374/
Brian Marshall
April 30, 2016
If you are 11.1.2.4 and you use ASO Plan Types, you may be aware of a bug that prevents drill-through from working properly. Essentially, I can define drill-through in BSO and everything works fine, but ASO just doesn’t work. You can see the intersection as being available for drill-through, but when you attempt to actually drill through…you get this:
Well that sucks. But wait, there is now hope! First, you need 11.1.2.4.004. Then, you need the recently released intermediate patch 23023767. Now if we look at the read-me, we see this:
That is not terribly promising. In fact, it just plainly says absolutely nothing about drill-through. It doesn’t even mention ASO at all. But hey, let’s follow the instructions and patch our system anyway. Flash forward a few minutes, or hours depending on your installation and we should be ready to try it out again:
Now that looks better! Working drill-through from an ASO Plan Type! The cool part is that we can use FDMEE…or something else. Be on the lookout for another post on using DrillBridge with Planning AND PBCS.
Brian Marshall
April 26, 2016
It’s time again to get everyone together for a lunch. We’ll shoot for a Friday in May from 11AM to 1PM. We generally try to stay near the Addison area to keep it somewhat central. I’m personally partial to Friday the 13th. Let me know now if you have a specific conflict *(outside of the NtxHUG). Go join the Meet Up group to get the invite when we finalize details:
http://www.meetup.com/Dallas-Oracle-EPM-Professionals-Lunch/
Brian Marshall
April 25, 2016
This was a pretty busy week. Partly because I was out part of last week and missed several posts, but also because it was just a busy week.
Patches and Updates:
DRM 11.1.2.4.340 has been released. Looks like another pretty good update. DRM continues to be the most updated product in the EPM stack by far.
OBIEE 11.1.1.9.160419 gets the award for the longest patch number I’ve seen from Oracle in a while.
OBIEE 12.2.1.0.160419 ties the version 11 for patch length.
HFM 11.1.2.4.200.UnbrokenSecurity aka patch 23114734 has been released.
New Blog Posts:
This week I got back to the Planning Repository! I started down the path of the built-in dimensions. First up was the HSP_ACCOUNT table.
Opal gives us a review of the new iPad Pro 9.7 from a user’s perspective. Not exactly EPM, but hey…I want one. Though I’m not sure I’ll go for the pink model.
Tim gives us a rundown on the Crossjoin function. For those of us that have used Microsoft’s Analysis Services product, we often wonder why there’s not Crossjoin operator. It is so much better than this function that won’t even take more than two parameters.
John Goodwin has two excellent posts on the new hybrid functionality of FDMEE. You can check out part 1 and part 2.
Speaking of FDMEE and the cloud, Dayalan has a post about that patch itself and then another post about using the hybrid functionality.
Eric gives us a great view of what to expect over the coming months from Oracle in the EPM space.
Doug shows us how to push data to from Hyperion Planning to HFM using FDMEE.
Robert gives us his thoughts on the changing landscape of EPM as more and more of our world heads to the cloud.
Harry has implemented data submission in his very impressive web-based interface for Essbase, giving me even more reason to finally get around to playing with this tool.
Gary has a new blog! Go check it out. Bring on the blogging Gary!
Brian Marshall
April 23, 2016
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).
So it’s been a while since we took at look at the Planning Repository. Today we’ll move past the HSP_MEMBER table (and associated tables) to look at the first of our built-in Planning dimensions: HSP_ACCOUNT. As the name indicates, the HSP_ACCOUNT table contains properties related to the account dimension in Planning. Let’s take a look at the table structure:
| Field Name | SQL Server Type | Oracle Type | Description |
| ACCOUNT_ID | int | NUMBER(38,0) | The OBJECT_ID of the member. |
| USE_445 | smallint | NUMBER(38,0) | If you application is set to use weekly distribution, this field will be populated when the Distribution checkbox is checked.
0=don't adjust values
1=445 spread
2=454 spread
3=544 spread |
| TIME_BALANCE | smallint | NUMBER(38,0) | Determines the Essbase time balance attribute.
0=none
1=first
2=last
3=average |
| SKIP_VALUE | smallint | NUMBER(38,0) | Determines the Essbase skip attribute.
0=none
1=skip missing values
2=skip zero values
3=skip missing and zero values |
| ACCOUNT_TYPE | smallint | NUMBER(38,0) | Determines the account type.
1=expense
2=revenue
3=asset
4=liability
5=equity
6=statistical
7=saved assumption |
| VARIANCE_REP | smallint | NUMBER(38,0) | Determines the Essbase variance reporting attribute.
1=expense
2=non-expense |
| CURRENCY_RATE | smallint | NUMBER(38,0) | If your application is set to use the built-in currency functionality, this determines which rate to use for conversion.
0=none
1=average
2=ending
3=historical |
| PLANNING_METHOD | smallint | NUMBER(38,0) | No idea on this one. |
| USED_IN | smallint | NUMBER(38,0) | The plan type usage for the member. This is also a bitmask that tells us which plan types are used in a single integer. This field also appears in the HSP_MEMBER table and appears to stay in sync. |
| DATA_TYPE | smallint | NUMBER(38,0) | This field is also found in the HSP_MEMBER table. It is populated in HSP_MEMBER but is always NULL in this table. |
| SRC_PLAN_TYPE | int | NUMBER(38,0) | "Indicates the source of data value for account." - Direct from Oracle. |
| SUB_ACCOUNT_TYPE | int | NUMBER(38,0) | "Identifies the type of account and how its useds" - Direct from Oracle. |
Overall, this table is pretty simple. We could probably stop there, but hey, how about a quick query:
SELECT
po.OBJECT_NAME AS Parent
,o.OBJECT_NAME AS Account
,CASE a.USE_445
WHEN 0 THEN 'None'
WHEN 1 THEN '445'
WHEN 2 THEN '454'
WHEN 3 THEN '544'
ELSE ''
END AS WeeklySpread
,CASE a.TIME_BALANCE
WHEN 0 THEN 'None'
WHEN 1 THEN 'First'
WHEN 2 THEN 'Last'
WHEN 3 THEN 'Average'
ELSE ''
END AS TimeBalance
,CASE a.SKIP_VALUE
WHEN 0 THEN 'None'
WHEN 1 THEN 'Skip Missing'
WHEN 2 THEN 'Skip Zeros'
WHEN 3 THEN 'Skip Missing and Zeros'
ELSE ''
END AS SkipValue
,CASE a.ACCOUNT_TYPE
WHEN 1 THEN 'Expense'
WHEN 2 THEN 'Revenue'
WHEN 3 THEN 'Asset'
WHEN 4 THEN 'Liability'
WHEN 5 THEN 'Equity'
WHEN 6 THEN 'Statistical'
WHEN 7 THEN 'Saved Assumption'
ELSE ''
END AS AccountType
,CASE a.VARIANCE_REP
WHEN 1 THEN 'Expense'
WHEN 2 THEN 'Non-expense'
ELSE ''
END AS VarianceReporting
,CASE a.CURRENCY_RATE
WHEN 0 THEN 'None'
WHEN 1 THEN 'Average'
WHEN 2 THEN 'Ending'
WHEN 3 THEN 'Historical'
ELSE ''
END AS CurrencyRate
FROM
HSP_ACCOUNT a
INNER JOIN
HSP_OBJECT o ON a.ACCOUNT_ID = o.OBJECT_ID
INNER JOIN
HSP_OBJECT po ON o.PARENT_ID = po.OBJECT_ID
ORDER BY
o.POSITION
This is basically a decode of all of the different fields. Here are the results:
SQL Server:
Oracle:
We’ll be keeping it simple for the next few posts, focusing on the built-in dimensions that we have left. That’s all for now!
Brian Marshall
April 17, 2016
This was a shorter week for me (vacation), so I may have missed some things. If so, let me know so that I can update this post!
Patches and Updates:
The 11.1.2.4.102 Tax Governance Patch has arrived. This one is totally out of my wheelhouse, so good luck!
Speaking of things out of my wheelhouse, the 11.1.2.3.817 Disclosure Management patch is out as well this week. Again…good luck.
Now back to my wheelhouse. Less than a week following the 11.1.2.4.008 patch, the Essbase 11.1.2.4.009 patch has dropped. This looks to be pretty straight forward. The version reporting seems to be the most consistent thing across the various readme’s. I guess I’ll skip .008 in my lab as I didn’t even have time to deploy it yet.
New Blog Posts:
This week I blogged about nothing! Gasp…we’ll see if I can fix this before the week is over.
Jake shows us an awesome way to set up PBCS test accounts. I didn’t know that gmail could do that!
I mentioned Harry’s web-based interface for Essbase last week. This week he is opening the product up for beta users. E-mail him to get your copy for testing. I know I will.
Summer actually received useful assistance from Oracle Support. Check out her post about run-time prompts and calculate form options. Next up…pink unicorns with rainbows…sorry Oracle. Just kidding. I get help from Oracle Support all the time.
Christian shows us how to install EPM Maestro. I’m not an HFM guy, outside of extracting data from it for nefarious Planning and Essbase purposes, but the post is thorough and well-written. So if HFM is your thing, check it out!
Vinjay shows us how to use external sources for a lookup property in DRM. Because not everything has to exist in DRM.
Tim shows us a method for generating test hierarchies of a certain size. I generally use my handy-dandy SQL data generator for something like this, but this is a cool free way to get there.
Keith explains a method for parallel ODI execution. Cool stuff if ODI is your cup of tea.
The Baroness PM talks a bit about the new ODTUG careers initiative. Check it out if you are interested in a career in EPM (or already have one).
Other News:
Collaborate 16 happened this week. I’ve never actually been, but as a ODTUG member, I believe KScope to be superior. 😉
Opal just published her first book: Look Smarter Than You Are with Oracle Enterprise Planning Cloud. Normally I wouldn’t post a competitor’s website, but since it happens to be Opal…I’ll make an exception. Congrats Opal!
Brian Marshall
April 16, 2016
This was a big week for patch releases and as always, a lot of a great blogging.
Patches and Updates:
The Essbase 11.1.2.4.008 patch finally hit the Proactive Support blog along. Last week we were missing EAS, Studio, and APS. This week we have them! Sadly, this patch does not fix my client’s issue. Oracle just updated my ticket says, and I quote…”sorry”.
More importantly, Oracle’s cloud and on-premises worlds have finally collided! The FDMEE 11.1.2.4.200 patch has finally arrived. This added cloud integration along with a ton of other important bug fixes (including a lot of unmentioned one’s).
New Blog Posts:
This week I blogged about loading SQL data into ASO in parallel.
Harry is working on a pretty awesome web-based Excel-like interface for Essbase. I’ll be installing this when he makes it available.
Christian shows us how to change the supervisor password in ODI. Good article, though I fear that I will have to ask clients for their supervisor password in the future instead of just knowing what it is.
Ricardo and Rodrigo published a great article on OTN exploring Planning Security with ODI.
Glenn explains an undocumented change to the way that Essbase interfaces with SQL Server in 11.1.2.4.
If you’ve ever been fighting your co-workers for RDP access, check out Pete’s guide on enabling multiple users.
The DEVEPM crew shows us how to compress remote files into a ZIP file using ODI.
Sarah shows us how to install OBIEE 12c. Handy for those of us in the EPM space that like to cross-over.
Missing your search-icon’s in the FDMEE POV selection dialog? Terry shows you where to find them.
The Oracle CEAL team has posted a new PDF on integrating OBIEE 12c with HFM. This goes through the entire install process for the drivers and configuring the ADM data source.
Kate shows us around the 11.1.2.4.330 patch for DRM. She pays particular attention to the newly introduced DRM Analytics.
Other News:
Collaborate 16 starts tomorrow in Las Vegas! You can still register on-site here.
Brian Marshall
April 9, 2016
If you have a ASO cube where timing is everything or a cube with multiple large data sets, waiting for data loads to process in series can waste valuable time. Luckily, Essbase supports parallel data loads! Sadly…while the documentation for this isn’t bad, it is inaccurate. I’m sure it was accurate a few (or many) versions ago, but the drive names have changed as have the DLL’s. So first, let’s set up our MaxL rule to execute multiple parallel loads from a single SQL data source. Here’s the code:
import database RetASO.RetASO data
connect as hypservice identified by 'Hyp3r10n!'
using multiple rules_file 'dRev', 'dExp'
to load_buffer_block starting with buffer_id 100
on error write to "e:\\data\\RetASO\\dRev.txt";
So this MaxL code assumes that you have at least two load rule developed. I’ve built a load rule for revenue and a load rule for expense. Each of these rules load roughly 2 million rows of data into my ASO cube. So let’s execute this and see what happens:
If we read the error, it tells us exactly what the problem is. We need a data source that supports multiple connections. For this we can turn to the included Data Direct drivers. Let’s go ahead and create a new data source to use for Essbase that will support multiple connections. We start by firing up our ODBC Data Sources (64-bit) in under Administrative Tools on our Essbase Server.
Click on the System DSN tab:
Now add a new connection:
Scroll down and select the DataDirect 7.1 SQL Server Wire Protocol (on 11.1.2.4) or the DataDirect 7.0 SQL Server Native Wire Protocol (on 11.1.2.3) driver from the list.
We also need to know the driver name, so scroll over to the right and make note of the name of the DDL file. It should be ARSQLS27.DLL (on 11.1.2.4) or ARSQLS26.DLL (on 11.1.2.3).
Once you click Finish you should see a new window that will let you fill in your server and database information. Enter your information and click Test Connect.
Enter your credentials.
Assuming we’ve done everything correctly, it should establish a connection.
Now we have a connection, but it does not support multiple connections by default. This means we need to tell it to allow multiple connections. We follow the docs and we create an esssql.cfg file in the bin directory of our Essbase server:
This is where we have to change it up a little. The docs haven’t changed in a while so they reference a very old version of the driver. Try this out instead:
[
Description "DataDirect 7.1 SQL Server Wire Protocol"
DriverName ARSQLS27
UpperCaseConnection 0
UserId 1
Password 1
Database 1
SingleConnection 0
IsQEDriver 0
]
Or for 11.1.2.3:
[
Description "DataDirect 7.0 SQL Server Native Wire Protocol"
DriverName ARSQLS26
UpperCaseConnection 0
UserId 1
Password 1
Database 1
SingleConnection 0
IsQEDriver 0
]
Once we save our file, we are ready to restart Essbase and give our data load another try.
Success! Parallel data loads are one of the many ways that we can improve our ASO process performance. In the future we’ll cover other ways to address ASO load performance. Hopefully this will help those of you that tried using the docs and did not find success.
Brian Marshall
April 4, 2016
The EPM community never seems to slow down with providing great content.
Patches and Updates:
Essbase 11.1.2.4.008 has been released. It has not yet hit the Proactive Support Blog. So here it is:
Essbase Server 11.1.2.4.008 PSU 22674292
Essbase RTC 11.1.2.4.008 PSU 22674308
Essbase Client 11.1.2.4.008 PSU 22674304
Essbase Client MSI 11.1.2.4.008 PSU 22814465
It says this:
“Essbase Administration Services 11.1.2.4.008, Provider Services 11.1.2.4.008, and Essbase Studio 11.1.2.4.008 are supported for use with Essbase 11.1.2.4.008.”
But…I can’t actually find those. So if you happen to be able to find those, drop me a line. Perhaps this is why it isn’t on the Proactive Support Blog yet?
New Blog Posts:
This week I covered upper-level drill-through using Drillbridge. I really like this product more the more I use it. I’m currently working on several more posts around advanced functionality and comparing the free version to the enterprise version.
Francisco tells us that hard-coding passwords is bad and shows us how to take care of the problem.
Summer tells us about a random date issue in Planning.
Henri shows us how to get back some of the functionality from the HFM Copy Utility in 11.1.2.4.
Cameron continues his series on PBCS in Part 9: EPM Automate.
Interested in a career in ETL? Probably not if you read my blog…but just in case, the DEVEPM guys have a presentation coming up next week on just that topic.
Dayalan shows us how to change the default number of columns and rows loaded in Planning 11.1.2.4 and PBCS.
Celvin give the community a heart-felt thank you after his well-deserved upgrade to Oracle ACE Director status. Personally, if I make it to director level, I’m finding a newspaper that still actually prints papers and taking out a giant advertisement. But his post is far classier.
Doug and Joe demonstrate how easy it is to copy data from HFM to Planning using FDMEE. I’ll be trying this on my home lab soon.
Opal gives us a little more information about FCCS now that it has made its way to Oracle site. Ringing in at $250 per user per month, it is the most expensive of the Oracle EPM Cloud offerings so far.
Brian Marshall
April 2, 2016