Friday, June 27, 2014

Bye bye KScope...

Back to Spain, ,back to jet lag, few days more working and then vacation :-)

I would really like to say thanks very much to all ODTUG people (Cameron, Opal, Natalie, Tony ... all of you) You really do a great job!
I really enjoyed meeting people there and attending sessions. Nice to meet personally other FDMEE guys (Doug, Tony, etc.)

Kscope has also been a great event to learn lot of things. It's great to see how people communicate and share knowledge with the audience. And not all is work...I discovered really good dancers @ EMP museum.

I've been thinking about the blog and I'm going to restructure it a little bit in order to make it more useful.

I'm going to include different post series that hopefully will help you not to be scared about FDMEE and especially Jython. Initially I will try to cover:

  • Jythoning with FDMEE (my new word...)
  • Migrating from FDM Classic
  • Tips & Tricks
  • FDMEE basics
  • Troubleshooting
  • Using Source Adapters
Any other suggestions?

Hasta pronto!

Sunday, June 22, 2014

KScope14 at Seattle - here we go!!!


KScope started today! After couple of days enjoying Seattle and San Juan Islands... it's kScope Time!
I will be attending and quite happy that I can meet lot of people.
If you want to have a chat, you can find me at Booth 312

PS. It seems that I brought nice weather from Spain :-)

Tuesday, June 17, 2014

Migrating from Classic FDM to FDMEE - Episode 0

Migrating or not migrating, that's the question :-)

I have been discussing with many customers about FDMEE migration and I would like to share my feedback with you.
First thing we need to bear in mind is that we are migrating to a "new" product based on different platform and providing several new functionality that will help us to get a better performance in our data load process.
Typical questions will be:

  • Do we have a migration tool (next > next > next ...)?
  • Can I reuse all metadata defined in my Classic FDM application (locations, import formats, etc.)?
  • What about my mappings? can I migrate them?
  • My application has lots of customization using VBScript, can I migrate them to FDMEE?
  • I have built some custom reports in FDM, can I reuse them in FDMEE?
  • What about Batch Loader?
  • So how can I migrate then?
  • and thousands more...
For me, there are two migration steps:
  1. Technical migration
  2. Functional migration
Let me clarify this. 
During the technical migration we will install FDMEE in our new architecture. We will register our source and target systems, configure credentials for ERP systems in ODI if needed, etc...
Why do I scope the rest of the migration as functional? Because I consider this migration a kind of once-in-a-lifetime opportunity to review your application design and get the best performance. Why would my application need to be reviewed? Here you have some reasons:
  • Classic FDM has some limitations that are now covered by FDMEE. For example, when multi-dimensional mappings were needed, most of the people used mapping scripts which don't perform so well. In FDMEE, we have a new mapping type called Multi-Dimensional mapping. This new mapping type will probably replace all your mapping scripts. In addition, you can use look-up dimensions in FDMEE to support your multi-dimensional mappings.
  • The more locations you have, the more you will have to maintain. With new data load rules you may simplify your location structure. Two locations for two different file types may be replaced by one location and two data load rules having each one import format.
  • Import process is almost always the bottle neck in Classic FDM. Mapping process is performed during the import process and 90% of the customers having performance issues during import follow this rule: Wrong mapping logic design -> poor performance during import (I have seen source files with 1000 rows taking more than 30 minutes...) It's a perfect chance to review your mapping logic in order to get the best performance. Make use of new mapping types!!! multi-dimensional, #SQL, and #FORMAT mask mappings.
  • You will have to re-write your scripts (import, mapping, event, and custom). Even if you keep VB for event and custom scripts, you will have to do some changes as VB Script is replaced by VB .NET. On the other hand, if you decide to use event and custom scripts in Jython...you will have to completely re-write them. Import scripts (dump) support Jython only. Integration scripts are not supported anymore as import scripts so they need to be implemented in BefImport event script (Jython or VB .NET). And lastly, VB mapping scripts are not supported anymore so you will have to re-write them as Jython or SQL mapping scripts (or replace by multi-dimensional mappings). Being said that, you will have a great opportunity to review your code which sometimes is not as efficient as you think. Also, if you forgot commenting your code, don't forget to do it now :-)
Some answers...

  • Do we have a migration tool (next > next > next ...)? Not in 11.1.2.3.x. It will be probably be included in 11.1.2.4 but we don't know what will be available for migration yet. The tool will be mainly for FDM metadata (locations, import formats, mappings...)
  • Can I reuse all metadata defined in my Classic FDM application (locations, import formats, etc.)? Yes you can. Some of them have been slightly re-designed but the core functionality is in FDMEE as well. 
  • What about my mappings? can I migrate them? Explicit, In, Between, Like mappings also exist in FDMEE. You will have to re-write your mapping scripts.
  • My application has lots of customization using VBScript, can I migrate them to FDMEE? Already answered above:-)
  • I have built some custom reports in FDM, can I reuse them in FDMEE? Crystal reports have been replaced by RTF templates created in BI Publisher. You can reuse the SQL queries but will have to rebuild the report in RTF format. I don't think migration utility will cover reports.
  • What about Batch Loader? Open batches are now available as a type of batch definition. Same functionality so you will not have many problems migrating them. 
  • So how can I migrate then? There is no unique answer for this question. It all depends on your application(s). In some cases it will be better to start from scratch in FDMEE so you can improve your design. You can also build custom SQL process to move objects such as import formats or locations between FDM and FDMEE databases.

Some advice...
  • Please, take your time for the migration
  • Review your design. Poor design in FDM will be a poor design in FDMEE so don't expect a big performance improvement if you keep it
  • Make use of new FDMEE functionality. It may take some time to rebuild your application but you will be happy with results
  • Find an experienced resource for application assessment. I would suggest someone not involved in the design of your current application. If I built the application I will hardly find any design issue
  • Work from high level to detailed level. That will help you to have a better understanding of your integration flow
  • Do you need 10 days? I would not be worry if you need some time to assess/review/migrate. Performing well during these stage will make you save lot of time/money :-)
Which is your feedback? Please comment!

Wednesday, May 21, 2014

Additional libraries needed in PSU510 for running FDMEE Scripts in IDEs

Hi all,

I have received some emails regarding issues when running FDMEE scripts either from Eclipse or Notepad++ after PSU510 was applied.

We have already discussed about how to run FDMEE scripts in different IDES:


Ok, if you try to run your FDMEE script which has the following initialization code:
You will get an error :-(

java.lang.NoClassDefFoundError: java.lang.NoClassDefFoundError: oracle/core/ojdl/logging/ODLLogger

Let's see it when executing from Notepad++: 
It seems that we need an additional library apart from the two stated in the documentation:
(\EPM_ORACLE_HOME\products\FinancialDataQuality\lib)
  • aif-apis.jar
  • aif-custom.jar

Which is that library? after googling we go it: ojdl.jar
We can search for it in our EPM environment and we will notice there are many libraries with that name.
I just picked up the one located in:

E:\Oracle\Middleware\EPMSystem11R1\common\loggers\ODL\1.0.0\lib

Good, library is added so I run it again...woops...another error!

java.lang.NoClassDefFoundError: java.lang.NoClassDefFoundError: org/apache/log4j/Layout

It seems that log4j logger is missing as well. Library can be also found in:
E:\Oracle\Middleware\EPMSystem11R1\common\loggers\Log4j\1.2.14\lib

Finally, after adding the two libraries, it's working again :-)
If you are using Notepad++:
Or if you are Eclipse fan:

So it seems that PSU510 does not include references to loggers so add them and make it work!

Regards



Tuesday, May 13, 2014

A deeper insight into drill-through from Planning & Essbase

Drill-through is a word you should be familiar if you are either FDM or ERPI/FDMEE fan.
I remember trying to explain classic FDM customers differences between drill-through, drill-back, drill-down...all getting crazy with so many drills!

What's drill-through?
FDMEE admin guide defines it as "the navigation from one value in one data source to corresponding data in another source"
Or in other words:
Navigate from my EPM application's data to corresponding source data
The formula would be Data(EPM) > Data(FDMEE) > Data(Source) and the relationship between source data values and target data values is usually N to 1 so multiple source transactions (lines in flat files, rows in tables, line item details in ERP...) aggregate to one target value in our EPM application. But definitions or relationships apart, we basically want to trace our data from source to target or target to source.

Another concept you will be familiar with is landing page. When drilling through from our EPM application, a page will be displayed. That page is the landing page.
When drilling from EPM application to FDMEE we will have the FDMEE landing page opened as new tab in Workspace. From this page we will continue with the drilling to the source system where landing page will be normally opened in another navigator page.

With drill-through functionality FDMEE users can get answer to questions like "Where is this $1000000 coming?" By drilling through we can determine if the root cause are wrong mappings, wrong source data, or any other cause who will let us blame someone else :-)

In this post, I will be focused on drill-through from Planning/Essbase. If you want to know more details about drilling through to source systems I strongly recommend you the following document from Oracle Support.

How does drill-through work?
Firstly, we must say that, although it is recommendable, drill-through from EPM application to FDMEE is optional. We have to specify in the target application options that we want to load drill regions (Setup > Register > Target Application):
When drill regions are enabled, they are loaded into the target application after data is loaded and consolidated. A cell is drillable in the target application if it belongs to the drill regions defined in the application.
What must be clear is the following: you will be only drilling successfully to your FDMEE data if the same intersection was loaded through FDMEE... and you did not clear data in FDMEE!
For example, if you loaded $1000 to Entity1;Account1 intersection, you will not be able to drill-through from Entity1;ParentAccount1 as you did not loaded data for that intersection, even if you see drill-through icon like happens in HFM. Drilling from parents and seein detailed data for all children would be nice but it is not possible yet.

What are drill regions in Planning/Essbase?
We are talking about drill regions but we did not explain what they are, where they are stored, etc.
Drill regions are defined by combination of dimension members. They will say from where we can drill to source. When we define a drill-through URL in Essbase (do not confuse with drill-through concept used by EIS or Essbase Studio), it is stored in the Essbase database file as metadata.
The URL has 4 components:
  • URL Name
  • URL XML
  • List of Drillable Regions
  • Level-0 Boolean Flat
Don't worry about creating drill-through definitions, we are working with FDMEE, so FDMEE will create it for you including drill regions.
If you want to learn more about drill-through and drill regions in HP/Essbase, take a look to the following Essbase Addendum Document.

How are drill regions loaded by FDMEE?
For HFM, a file with extension .DRL will be created and exported. This will happen after data is successfully loaded and consolidated (optional). Drill URL and drill regions will be stored in two HFM database tables (HFMApp_ERPI_URL and HFMApp_ERPI):
For HP/Essbase, FDMEE will load drill regions directly into Essbase with no file being exported.
Technically, FDMEE uses Essbase API although that will be transparent for end user. You will see how FDMEE consumes [HPLService] and [EssbaseService] to create drill regions:

Which dimensions are included in the drill region loaded from FDMEE?
The main difference between Planning and Essbase is that for Essbase, you can select which dimensions are included in the drill region (Setup > Register > Target Application) while Planning includes Entity/Account/Scenario/Year/Period.
Having this flexibility for Essbase lets you define the granularity for the drill region. For example, if we select only the Product dimension, we can drill on a cell that at least does not have a null for year.

How are drill regions generated by FDMEE?
I consider this part the most important because is what will let us know how FDMEE works.

  • If your source system is EBS or PSFT and you have defined metadata rules for them, drill regions are created based on the metadata rule
  • For all other source like flat files, drill regions are created based on the target members defined in the data load mappings
  • For Year, Period, and Scenario, FDMEE uses audit information to create drill regions
I know this can generate some controversy so let's analyze each of the three points above.
  • If you have metadata rules, drill regions will be only created for dimension members loaded from your metadata rules. If you need to create additional drill regions you would have to create them via FDMEE script.
  • For the other two points I will show you an example you will understand all details.
Understanding how drill regions are created when no Metadata rules exist
I have successfully loaded my data to Planning/Essbase through FDMEE:

No what?
I navigate to my HP form and check data has been loaded:
I see my data but no cells are drillable! so gold fish + data in the form + no drillable cells = weird.
Did I check process details in FDMEE?
Ok, I can see that there was an error creating drill regions in Essbase. Let's ask your best friend in FDMEE: the process log (with log level set to 5 of course)
The log shows an error in [REGION DEFINITION] so I will analyze the region definition :-)
It seems that there is an account A00090000 which is not familiar to me. I know accounts included in the drill region will come from my data load mapping definition so I should have a look to it:
Rule 30_Group.090 is not correct. Account A00090000 does not exist in Planning/Essbase Cube. However the account was included in the region definition as it is explicitly defined in the data load mapping tables. That made drill region creation failed.
In this support document you will find details about solving your issues with drill regions in Essbase.
I can remove that mapping rule as I don't need it for audit purposes. So after removing the mapping and loading data again...I still see the error!
I then go for plan B, let's have a look to Audit table AIF_APPL_LOAD_AUDIT:
I can see that there are some entries for old executions which failed due to my issue. Probably they have some data related in TDATASEG, so if I remove these entries, that residual data will be removed as well.
Done, now reload again and...
Drill regions created!
I can now navigate to my form and drill to FDMEE to see details:
And my landing page is displayed in Workspace as expected: 

 What can I conclude?
  • Drill regions will be created for dimension members even if you are not loading data to them. If they are explicitly defined as target values in the mappings, then they will be included in the drill region definition (as you can see in the process log shown above)
  • Drill regions will include only dimension members which are defined as explicit target vales (no wildcards) in any kind of mapping type (Explicit of course, In, Between, and Like) For example,  if you have a LIKE mapping rule defined as A100 > HP*, it will not include account HPA100 in the drill region. On the other hand if you have an IN mapping rule defined as "1000,2000,3000" > ACC123 theb ACC123 will be included innthe drill region regardless data is loaded to that account or not.
  • Therefore you must be careful when using wildcards in your mappings like * to *
  • Review your audit tables. It can also happen that you create a mapping for a non-existing target period in your Period Mappings, making this FDMEE fail when creating the drill  regions. For example, if you create a period mapping for "March" but the valid one is "Mar", the audit table will have an entry for the wrong period after data is processed. Fixing the typo will not help, you have to remove wrong entries from audit tables.

Classic FDM Vs. FDMEE
In classic FDM, drill regions were created for all intersections with POV members being loaded:
Having explicit, in, between, or like mappings using wildcards did not impact.
Let's say that we load one file with one data row for specific account:
We can see data loaded in Planning and the drillable cell:
If we enter data manually in the other 3 cells:
Voilà! you can also drill from these cells...can does not mean you will be able to successfully do it as you did not loaded data from FDM to these 3 intersections!
You will be only successfully drilling to intersections loaded through FDM:

As far as I know, the way drill regions are generated may be changed in 11.1.2.4 so they are created based dimension members where data is loaded. Therefore the creation process will be looking more to target values after conversion rather than mapping definitions. In any case, this is something I cannot confirm 100%.

And finally, a bit more about Drill-through ... 

Where can I see drill-through definitions in Essbase?
You can use EAS Console to see drill-through definitions (right click on cube > Edit > Drill through definitions):

Woops, you will not see drill-through definitions created by FDMEE. Why? there is bug in Essbase related to displaying definitions having special character like ":" in the URL name. You can check drill-through definitions exist using MAXL command:
By default the URL name of the drill-through definition created by FDMEE is Year:Scenario

Drill-through from ASO Plan Type
Starting with the 11.1.2.3 release, Planning customers having a full use Essbase license can now have plan types which use aggregate storage databases:
When working with ASO plan types in Planning, you will not be able to drill-through from Forms due to product limitation:
It is only possible to drill-through from Smart View/Financial Reporting using the Essbase Provider:
BTW, if you want to load data to an ASO plan type you will have to do it by registering the Essbase cube and not by loading to the plan type as you normally do when loading to Planning.

I'm sure these two functionality will be introduced in future releases.

Drilling through from where?
We can drill-through from Planning/HFM forms, Smart View, and Financial Reports.
If you want to drill from SV or FR bear in mind that it will only work if the data source is HFM, HP or Essbase. So if you try to open a Financial Report in Smart View you will not see any option to drill.

I noticed the post is bit long. Sorry for that :-)

Enjoy!


Friday, April 25, 2014

#FORMAT Mask Mapping for Target Values

It's good to see that FDMEE is being enhanced. For those who come from legacy FDM we were not used to new features since years!

As I described earlier in previous post, version 11.1.2.3.510 brings something new:
FORMAT MASK MAPPING

So what we have here? Think about the following example:

Source Account Value: 1001-2000-D123
Target Account Value: 11-Sales-123Cons

My source account has 3 different segments delimited by '-', and I want to define a mapping rule as follow (Power to the imagination!):
  • Ignore 2nd and 3rd characters of 1st segment
  • 2nd segment value starting with 2 indicates Sales so we have to replace the segment value by "Sales"
  • Third segment indicates subaccount. We have to ignore first character and add prefix Cons to subaccount code
So first mapping rule I can think would be to create a mapping script? maybe new #SQL script?

Yup, it could be but what about the following target value?
#FORMAT("?##?-Sales-#???Cons","-")

What the h*** is that? 假借字 形聲字
Chinese? Let's give a try in FMDEE!

My first Format Mask Mapping
My source file will be a very basic one:
I have highlighted source account value: 1001-2000-D123

Let's create a mapping rule using new Format Mask Mapping as follow:
Note: source value? 1001-2* is fine for this example.

Let's run the Data Load Rule and check target account value:
We got the desired target account: 11-Sales-123Cons

Time for explanations:

#FORMAT(<Format Mask>,<Segment Delimiter>)

  • #FORMAT - indicates mapping of type Format Mask in the target member
  • <Format Mask>
    • ? - Basically take from the source value the character in same position as ?
    • #Basically skip/drop the from the source value the character in same position as #
    • Character -  Include character (or string) as it is
    • * - Include all characters from source segment or source
  • <Segment Delimiter> - Character used to delimit segments in source and target members. It must be the same character for both source and target. Delimiter is optional as source value may not represent delimited segments.
#FORMAT("?##?-Sales-#???Cons","-")
  • 1st Segment mapping: ?##?
    • Take 1st and 4th source characters. Skip 2nd and 3rd > 11
  • 2nd Segment mapping: Sales
    • Replace the entire source segment by "Sales" > Sales
  • 3rd Segment mapping: #???Cons
    • Skip 1st character, take 2nd, 3rd, and 4th, and add suffix "Cons"  > 123Cons
 When special characters are used in conjunction there are some particularities:

Use Cases
When integrating with ARM (Account Reconciliation Manager), we need to load data to specific ARM profile. A profile is defined by a combination of segments (Ex: Account-Company-Department). Profiles are defined as part of the ARM configuration based on business requirements.

However this new feature can be also useful for non-ARM integration either using or not delimiters as part of target member definition.

More examples
Here you have few more examples:
Have a look to mapping rules 05 and 06.
Let's say that we have XXABCDEYY as source value.
Rule 05 would return ABCDEYY. Why? When using "*" before "#", FDMEE will take all remaining characters. In other words, "*" overrides "#".
Rule 06 fixes this issue and remove prefix and suffix successfully,
I leave source and target accounts so you can analyze each mapping applied: 
 
To conclude, #FORMAT completes the three # mapping types for FDMEE: #FORMAT, #SCRIPT, and #SQL
Has it limitations? I'm sure once people start using it more use cases will come up. In any case this a very good starting point for sure.

Enjoy!

Friday, April 4, 2014

Validating mapping tables in FDMEE

One of the nicest enhancements in FDMEE is the capability of validating target members from mapping tables.
What is that? to better understand I will go back to Classic FDM...
Once upon a time...
When you created a mapping in FDM (I said created so not imported) and your target was explicit member (no wildcards), that target member had to be valid (exists in the dimension of your target application)
For example, I want to create a explicit mapping that maps source value BostonSales to target member EastSales. After typing manually EastSales in Target Entity then new row is added when we click Update Grid:
We could also have browsed for the target member instead of typing it which ensures our member is valid as we did not have type it avoiding potential typos:
But what happened if we had a typo? Let's type EsstSales:
Happens that after trying to update the grid we got an error as EsstSales is an invalid member of Entity dimension.
So after adding mappings, explicit target members were validated when updating the grid.
Obviously, mappings where target member had any wildcard character were not validated against target dimensions:
What about importing mappings? Classic FDM did not validate mappings (any type) when importing, only new mappings were added...
In the following example you can see how an explicit mapping for NYSales with an invalid target entity (EsstSales) is imported with no errors:
What about FDMEE?
Let's reproduce the same example in FDMEE:
 
Hmm, not validation error anymore...the new mapping rule was successfully added. It did not check my target entity EsstSales like Classic FDM did:
 
Wait a minute, what is that button labeled as Validate? Let's click on it :-)
So I can add mappings and then validate them by simply clicking a button, nice!
It will validate all target members in my mappings:
All? the same as Classic FDM, it will not validate target members having wildcard characters neither. The same as Classic FDM, obvious :-)
I can still add target members by browsing target dimensions:
I know, Descriptions are empty. As far as I know this is expected behavior as this functionality was not implemented yet. I know, weird.
 
Let's play a game: Find 1 difference. Easy one :-) 
Did you find it? I hope you have not wasted more than 1 minute :-) Any way I have to give you the solution, otherwise I cannot continue with this post!
 
You're right, Validate button is missing for Multi-Dimension mapping. Why? don`t worry, your explorer is OK, it's a bug.
 
So how I Validate my Multi-dimensional mappings?
I have been thinking in a workaround, just to play a little bit. In FDMEE you have now 2 import modes (Merge and Replace) and the option to validate mappings while they are imported (nice enhancement):
So I thought, why just create my mappings in TXT (or Excel) and then import them by selecting Validate option?
 
1. I created a multi-dimension mapping as follows:
2. Then I exported mappings for Entity dimension to TXT file:
We can see how mappings are exported to the text file:
3. I tried importing mappings for Entity dimension:
Select Merge and Validate options:
But file was imported with no errors!!!
4. Ok, then I thought... I will add an invalid mapping line to my file and import again (DummyMap):
As DummyValue does not exist it should raise a validation error:
Which one?
Ok, so the new line is validated but my multi-dimension mapping is not yet...
 
5. Let's give another try. I will change the target value for my dummy mapping to EsstSales:
After importing it again:
2 errors? Let's see them!
Woops, my multi-dimension mapping is now validated.
So what happened? to me the validation process takes all explicit target members (like EsstSales), validate them, and then mark as invalid rows any having an invalid target member. As our dummy mapping has an invalid target member, collaterally our multi-dimension mapping is marked as invalid too.
What do you think?
 
Do you have 100 multi-dimension mappings with explicit target member? create 100 explicit mappings, put all together in a file, import and validate...you can later remove the explicit mappings...
 
Enjoy!