KScope19 – Deep-Dive into Data Integration

There were a lot of big announcements about the future direction data management is taking, and except for the proposed loss of our favourite FDMEE fish, they were all absolutely fantastic, and there is so much to be excited about. I’m going to give my views on the biggest announcements and the direction Data Integration is taking, and I’ll be getting some tutorials together once these features are available. But first:

Jargon Buster

• FDMEE – on-premise Financial Data Management Extended Edition, amazing ETL tool for mappings, data import and export.
• Data Management – the cloud version of FDMEE, built into PBCS etc
• Data Integration – the shiny, fishless facelift of Data Management

Data Integration

Be ready, the new world is coming, and eventually it will replace the Data Management we all know and love. Data Integration functions the same as Data Management in the background, but it’s been designed to be a lot more user friendly. For example, rather than creating a source, target, then location, then import format, then load rule you will in the future just create an integration which encompasses most of those steps in one place.

They’ve applied the same logic to the options, which currently are spread all over the place but will now be accessible in one window. There are also some great new features coming with the facelift, such as Target Expressions which will allow you to apply several mappings at the load stage, including some mappings that were much more complicated to perform before. Target Expressions are complicated enough to deserve their own tutorial blog, so I’ll dive into those later, but another great feature is an official processing order for data maps, so no more alphabetical processing of data maps, you can get a like mapping to process first and then have explicit maps after if you want. This is a big Quality-Of-Life (QOL) update for admins and really shows Oracle are listening to feedback when it comes to data integrations.

You can also choose to skip the workbench stage, which can massively speed up integrations for day-to-day running if drill-through isn’t required, although you can then switch it back on and re-run an integration to troubleshoot any weirdness. I can see customers using this feature a lot. This is coming in version 19.05 for cloud.

You can start using Data Integration right now in PBCS, although full parity with Data Management doesn’t exist yet. As a result, you can create an integration in Data Integration and then jump into the more familiar Data Management interface to see how it all works in the background, and you can run an integration in either interface with no issues.

Data Export Updates

A recent highlight update for Data Management was the massive upgrade to flat file exports, which has given us several great options. These include:

• Import a file template to create a flat file export
• Include/Exclude the file header
• Sort the data in a file export
• Attributes can be exported into flat files
• Easy reordering of output columns
• Pivot specific dimensions into the columns
• Choosing the accumulate (aggregate) data or not based on preference
• Changing the data file parameter (we love a | in our csv files)

John Goodwin has blogged about it, so you know it’s great (check that out here) and there’s no point covering the same ground, so let’s move on!

Future Integrations to follow NetSuite to PBCS model

For those that haven’t read me gushing about the NetSuite integration, check my tutorial out here, but Mike Casey has confirmed that future direct integrations are planned to follow the NetSuite model. This means that they will support designing a query in the source system, pulling that specific query over to Data Management and mapping that query individually from other queries. This is AMAZING for importing metadata vs importing data which usually need completely different queries, all in the cloud, all on demand, all SIMPLY DELIGHTFUL

On-Premises Agent

The new on-premises agent will allow direct connections to on-premises systems, with a variety of configurable options to ensure all system admins can get on board with it. It will offer synchronous mode which is tougher to set up and requires more IT involvement but is constantly listening for requests, or asynchronous mode which can be run on a schedule and will execute any queued requests when it’s run on the server-side. Asynchronous doesn’t need a network port or any additional network infrastructure than EPM Automate needs now, so it will be less of a headache to set up.

Direct connections to on-prem databases from the cloud have been a massive request for a long time and it’s going to be a game-changer when this delightful tool comes out. It sounds seriously powerful!

Other Roadmap

Below is the roadmap which was shown at KScope19, under the usual Oracle Safe Harbour statement, so this represents their planned direction but no official commitment to release this stuff. Regardless, there’s plenty to keep guys like me excited and busy in here!

There was so much new and exciting content in the Data Management roadmap, that I haven’t even got around to discussing the direct HCM Cloud integration, including write-back, or the fact that data integration has been upgraded to allow a 5 million row limit!

Expect plenty of blogs giving blow-by-blow walkthroughs of the above once they’re released, but until next time, adios!

Mike

KScope19 – Thoughts on the EPM Strategy Symposium

I had the great opportunity to head to KScope this year, and had a fantastic time meeting the crème de la crème of the EPM blogging world. My colleagues made some great presentations (and Gui and Lydia actually won an award for theirs!) and it was great engaging with Oracle in the partner forums and the roadmap update sessions.

I focused mainly on EPM and Data Integration, being my two favourites, so here’s the headlines from an interesting week!

EPM Pricing Changes

Huge changes in the EPM Pricing space, with some massive opportunities going forward. The key one is the new SKU, Enterprise EPM Cloud. This offers unlimited applications for any of the EPM Cloud suite, which have been renamed from the acronyms to their actual purpose:

• Planning (EPBCS on steroids)
• Financial Consolidation and Close (FCCS)
• Account Reconciliation (ARCS)
• Narrative Reporting (EPRCS)
• Enterprise Data Management (based on ODI)
• Profitability and Cost Management (NEW!)
• Tax Reporting
• Data Integration (facelift for Data Management based on FDMEE)
• Management Reporting (facelift for Financial Reporting)

Unlimited truly means unlimited here. You can have 10 planning apps with one of each of the others, for example, and each planning app will now support 6 custom BSO and 6 custom ASO cubes, as well as the EPBCS frameworks (now called modules). This is huge for bigger customers who want to leverage the entire cloud who could save a lot of money by moving to this SKU. The only problem is the base price, a whopping $500 per user per month, but Oracle are bound to offer discounts.

The less said about the other offering, Standard EPM Cloud, the better. It’s half the price at $250 per user per month, but each extra app costs $2500, and you’re limited to just one custom BSO and ASO cube. You can leverage the EPBCS modules as well, but these can be clunky and really suppresses the capability to lift-and-shift.

The good news is that existing PBCS and EPBCS customers can renew without changing their deal, and you can also buy PBCS through NetSuite for the usual price, meaning customers who just want a great planning solution can get that for a more reasonable price.

Free Form Planning

Holy smokes this thing looks fantastic. It’s basically Planning with no restrictions on dimensions at all, so you can create whatever crazy cubes you want. Don’t want Version? That’s fine. Want a single time dimension instead of Period/Year? That works too! This is the solution to creating Essbase style cubes on the cloud.

The main benefit is that you can also leverage all the usual great planning functions like forms, business rules and the rest on top, with the caveat that if you don’t have a scenario dimensions, you won’t be able to use workflow. The available features will be automatically assigned based on your dimensionality.

Free Form apps can also be created entirely in Excel templates, and maintains all the great Essbase features like multi-grid on one sheet, aliases in different columns and in-sheet POV’s.

On-Prem Support until 2030

This is a cloud blog, but on-premise is getting a big upgrade to version 11.2 and will continue to receive new features and be supported at least until 2030. 11.2 does require a fresh install however, which could be a pain point for big organisations.

Updates to tired features

Financial Reporting has gotten a facelift and big upgrade, and soon Management Reporting will replace it, with the big bonus that MR can have grids from various sources (ERP/EPM) all in the same report. I didn’t get to play with it too much but I understand key features like batch bursting and report scheduling are coming, and it will be the driving force behind the Narrative Reporting tool.

Machine Learning makes its way into EPM

A module named IPM (Intelligent Performance Management) is being actively developed to add machine learning style predictions to the EPM Cloud. The idea is that Forecasts will be auto-filled with a machine-predicted figure which users can then change if needed. It seems like a great future feature, but it’s not quite up to industry standards just yet.

Finally, some RIPs:

• EPMA has died a death, finally (owners will get a restricted version of DRM to replace it)
• On-Prem Workforce/Capex/Project template are dead
• The FDMEE Fish are dead (in the new Data Integration facelift for Data Management)

I'll be publishing a deeper dive into Planning and Data Integration soon and my thoughts on a bright future for the EPM world!

Until next time,

Mike

Customise and Simplify Drill-Through Regions in PBCS

We recently set up a demanding FDMEE schedule for a global business who required half-hourly updates from their source system (JD Edwards in this case) 24/7. This requires running five different FDMEE rules sequentially on a half-hourly basis which, as you can imagine, is a tight schedule and identifying any small efficiencies you can is essential.

One source of consternation for us was the drill-region – we had five rules constantly recreating drill-regions which contained thousands of dimension members between them, and whenever an error was encountered (such as a new project needing to be added) the entire process was being held up due to the drill-through process crashing. There had to be a better way, we thought.

Fortunately, it turns out that there is.

You can access your drill-regions in PBCS by opening the Calculation Manager and selecting the Database Properties button. If you haven’t discovered this great tool before, you can think of it as the closest thing to Essbase Administration Services (EAS) that PBCS has. You can play with all the great functionality in this window on your own time, we’re on the clock here people!

Once you’re in Database Properties, you need to right-click your database of choice and select Drill-Through Definitions. Once you’re in this window, you should hopefully see an existing drill-through definition. If you’ve run an FDMEE load before, then you will. If not, then why are you reading this blog? I’ve written tons of blogs about FDMEE automation through EPM Automate, go and trawl through those first!

Edit the existing region, because it’s an enormous pain to set up one from scratch - think of all that pointless XML you don't have to write!

You should see an XML box and a Regions box. The XML box is automatically generated by PBCS and deals with the nuts and bolts of connecting your drill-through command with your browser and the passing through the correct members so for Christ’s sake don’t change it.

The Essbase-savvy among you may recognise that the region box looks remarkably like a simple @LIST formula that contains all the members in your data load one-by-one. How inefficient and lengthy. Turns out that @LIST works exactly the same as an @LIST formula in Calculation manager or a calc script. Which means it accepts @DESCENDANTS and @RELATIVE formulae.

So, we can define the exact intersection of cells that we want to be drillable. All this drill region really does is add the little green “drill-through” box around cells in Smart View. The drill-region automatically doesn’t show up if the cell contains missing values, and if you drill a cell with no background FDMEE transactions, it comes up with a blank drill-through screen, which helps you identify any ghost data from when you’ve changed data mappings for example.

You can control whether the drill-through definition appears for level-0 only or not using the Level-0 Flag checkbox. What we do is set the drill region to level 0, and use the following formula:

@LIST("Actual","Working",@Descendants(Project),@DESCENDANTS("Input Currencies”))

This automatically creates the drill-through box on all level-0 actuals in any period. Our customer only loads data to their actual scenario from their source transactional system, so it works for them. However, this could easily be amended to work for your business.

The final step is to turn off the auto-creation of drill-region in your regularly run FDMEE rules, and the whole process will be sped up and deal with errors much more easily. In Data Load Rule -> Target Options you will find the option you need.

This formulaic method of generating a drill-region can be used to power a higher level drill-region setup, which is explored in more detail in this Oracle white paper. To save you a click, you basically run two rules, a normal one and one which maps data directly into parent members, then amend the drill region to contain both. Suddenly your users can drill through from Total Revenue and see all the transactions which rolled into it. Pretty high maintenance in my opinion as mappings would need to be amended every time the chart of accounts rollup changes… but that’s just my view.

I hope the technique above saves you some time. Let me know in the comments. As ever, check out the rest of my blog posts on http://stay-ahead-in-the-cloud.blogspot.com/ for more innovative FDMEE techniques to keep your PBCS implementation ahead of the rest!

Until next time,

Mike