DEV EPM

Hey guys, how are you? Continuing the how to integrate PBCS seamlessly using you existing ODI environment series (Part 4 Here), today we’ll talk about Importing Metadata Jobs.

As you can imagine, import Metadata will also be simple in PBCS, we just need to pay attention in the file format and that’s it.

To import Metadata, we need:

As usual, first we need to select our inbox, then the name of the files for each dimension, the delimiter for each dimension and if you want to clear the members before load the new members you just need to check Clear Members.

That’s it, all dimensions will be load at once, but we need separated files, one for each dimension. These files can also be in zip format that PBCS will automatically unzip for us.

Now the important part, the file format. This are all properties that PBCS expect when loading metadata.

That’s it. With that we finally finished all the setups we need in PBCS for our ODI jobs to work. One import thing that I need to point out is that Oracle will update PBCS with new versions and the file format can change over time. If that happens, you’ll need also to update your ODI Jobs.

It happened to me during more than one project and is not a big deal, but you need to be aware that if your job starts to fail, this is what can be happening.

I hope you guys enjoy it, stay safe and see you soon.

Hey guys, how are you? Continuing the how to integrate PBCS seamlessly using you existing ODI environment series (Part 3 Here), today we’ll talk about Outbound Jobs.

In the same ways as the Inbound Jobs, the we need to create a job to extract both data and metadata for ODI to consume and populate our DW as well as use the Metadata for validation.

So, to extract data from PBCS is also easy. First, we need to choose the outbox location to enable save as job (Local does the same as for the Inbound job and enable you an once execution only).

For the outbound we need to choose the plan type, that means, we’ll need at least one job for each plan type. We also need to choose the delimiter (I always like pipeline because is easy to see and is not used in any command) and if you use smart list you can choose if you want to export the labels or the names.

And finally, you set the POV to be exported. You can use essbase substitution variables here if you want to and as you can see the export will also have the same format as planning import, accounts on the rows, periods on the columns and the POV (the plan type as well).

You can change the format if you wish, but I advise to maintain the consistency between the jobs for the sake of dynamic components.

After running this job, PBCS will generate a zip file in his outbox, we just need to go there and download it.

For the Metadata export the idea is the same but a little bit simpler than the others, we just need to select our outbox, the dimensions you want to export and the delimiter and that’s it. PBCS will create one zip file per dimension in our outbox.

That’s it for today. I hope you guys enjoy it, stay safe and see you soon.

Hey guys, how are you? Continuing the how to integrate PBCS seamlessly using you existing ODI environment series (Part 1 Here), today we’ll talk about PBCS itself.

For us to export or import data from PBCS, we need to prepare our PBCS environment to receive and send all the data we need. Let’s take a look on this.

PBCS has 2 very different interface:

• The regular interface, same as planning
• And the Simplified interface (That planning also have) but in PBCS it’s way more useful

The regular interface is almost exactly like Planning one, but the important thing is that we can do anything we need to configure the environment on it. We need to use the simplified interface.

The simplified interface has some new features like be optimized to a tablet or a smartphone but the main feature we are looking for is the inbox/outbox. The inbox/outbox is the only point of contact with the external word and everything that need to be loaded needs to be uploaded to the inbox first and everything that is extracted from PBCS will be available in the outbox to be downloaded.

Knowing this we’ll need to setup for our design:

• Data and Metadata load
  • Upload the file with the data to the inbox.
  • Create a job to import the data.
  • Execute the job.
• Data and Metadata Export:
  • Create an export job (For data export is possible to create a Business Rules to export the data directly to the inbox folder: /u03/lcm/)
  • Download the files
  • Unzip and import the file with ODI

For Data/metadata load we need first to upload a file to the inbox, then we need to have an import data/metadata job created and finally we need to run the job to load.

The file can be in .zip format that PBCS automatically unzip the file. This is very good since we may move some big files around

For Data/Metadata extract, we need first to create an export job and run it (you can do data export using the business rules, same old dataexport from essbase, and the path to export would be /u02/lcm/), download the file and finally use ODI to unzip it and load it to our tables.

But what are jobs? Jobs are basically templates with information about what you want to do. For example, a data export Job will contain the POV of the data you want to export, and every time you ran the job that POV will be exported.

In the console menu we have everything we need to create and monitoring our jobs. All data Jobs are created clicking in the Actions menu, Export and Import options and to check the job results you can go to inbox/outbox explorer.

And that’s it for today. I hope you guys enjoy and next post we’ll create all the jobs we need. Stay safe and see you soon.

Hey guys, how are you? Today we’ll going to talk about how to integrate PBCS seamlessly using you existing ODI environment.

I said PBCS but this approach can be user for any cloud application available out there (with just changing the way of connect/upload data/API that needs to be used to integrate). Other than that, everything else is valid.

Let’s think a little bit about PBCS. PBCS is a close box that can only be accessed by HTML, SFTP, REST API or EPM Automate. Then for integration we basically have 2 options, REST API and EPM Automate since everything else is way too manual to be called integration.

Both will work in almost the same way, then to keep it simple we’ll be using EPM Automate here. In our case we have a ODI on premise designed like this:

• We have a lot of different sources that are loaded in our stage area.
• In the stage area we do whatever transformation we need in the data before we load it to the DW schema
• During the load into our DW we validate all the data POV against the metadata in Planning or Essbase.

From everything described here the only two changes we need to do is the source of the metadata for validation and the target of the data load.

For the metadata, instead of reading the Planning repository or extract the metadata from Essbase, we’ll export from PBCS the metadata and load it to a table, and this is what we’ll use for validating the data before load.

This step is very important because we don’t want to get errors during data load and slow down the process we are creating. Also, it gives the business a fallout report that they can work to fix the invalid members in PBCS.

For the data load, instead of loading this data directly to Planning we’ll be exporting the data in the right format to a txt file, zip them, and use EPMAutomate to load it to PBCS.

Remember, you chose the REST API to do so, the only thing you need to do differently is a little bit of programing, but the logic will be the same.

Then in the end we’ll have a design like this:

As I said, we can see by this schema that any cloud app can be integrated in kind of the same way. We can have everything we have until today and we just need to figure out the bridge we’ll have between our on-premises DB and our target app.

If the target app is VPN enable, everything get’s way easier since for ODI, this will be just another source, in fact, we could even have DB Links between our environments.

Then keep your mind open for the approach here more than the content we are talking about (PBCS).

I hope your guys enjoy this quick start of our series and see you soon.

Hey guys, how are you? Continuing the Essbase Statistics DW: How to automatically administrate Essbase using ODI series (Part 9 Here), today we’ll talk about the analyzes we can do with all the data we have so far.

With all this information in our DW we can do a lot of things. We could automatically set the caches using ODI and Maxl or Esscmd but change settings in Essbase is more complicated than just an update.

We need to restart the applications, we need to take in consideration the design of each cube, we need the feeling and knowledge of the administrators, but can be done.

In fact, we can create thresholds to see if the cache is 20% off what it supposed to be and then apply the changes at night. We can also start to record the sessions (be aware that the tables get very big very quick), and analyze the retrieve/calculation times to see if the performance is getting better or not and them send a alert in case it was not after a automatic chance or something like that.

But what we can really do is provide precise information about each cube. We send a daily email for each administrator saying the actual stats for each cube, the actual setting of each cache, the recommended size, and we also send a table with more information about that application.

Attached we can add an excel with the raw data for further analysis.

This helps a lot the administrators to have a better understanding of our cubes and to decide and plan better maintenance strategies.

Of course, we can have a dashboard (and this would be the best approach) with all the statistics there showing the growing of the cube, projections, alerts of when the cubes will reach the maximum cache size, well, anything that could be needed.

The important thing here is to realize how useful this DW would be for keep our Essbase cubes healthy. And keep in mind that is a lot of other stats that can be gathered from Essbase that can do a lot for us.

For example, we can get the dimension settings and number of members and use ODI (or any language like java) to manipulate the outline and test the best Sparse/Dense settings for our cubes.

I hope you guys enjoy our series, be safe and see you soon.