Browse Tag

FDM

PBCS Data Loading Glitch

PBCS Data Loading Glitch

While building out data load automations for PBCS and FCCS recently, I ran into a somewhat annoying issue when loading data to PBCS. Even more surprising is that Oracle claims it works as expected….. This post will explain the issue and provide a really simple work around until this gets properly addressed.

The Problem

Anyone who has worked with FDM Classic, FDMEE, or Data Management for any amount of time has run into at least one failed data load due to invalid dimension member(s) in their exported data. While the data load process should ensure that the target application metadata is current before loading data, this doesn’t always happen. (e.g. last minute source ERP updates that do not get properly communicated to the EPM team)

While a data load failure isn’t optimal, typically this failure is easy enough to identify and fix based off of the feedback provided from FDM/FDMEE/Data Management and the target application.

FDMEE / PBCS Merge Load Failure

FDMEE / Planning Merge Load Failure

cid:image010.png@01D1E6E3.8124EAE0

Data Management / FCCS Merge Load Failure

Unfortunately, when you perform a Replace export to a PBCS target application, under some circumstances you will get a quite unhelpful response:

Data Management / PBCS Replace Load Failure

What Happened?

When performing a Merge export, FDMEE/Data Management simply passes the generated data file to the target application for loading without any pre-processing. When performing a Replace export, a data clear script is dynamically generated based on the members contained in the export data.

The script will clear data for every intersection of Scenario, Year, Period, Entity, and Value dimensions. In the event that one of those members is missing in the target application, the dynamic clear script will contain an invalid member resulting in the error shown above. While the error shown above is completely legitimate, it isn’t meaningful enough to allow you to locate the member easily.

Oddly, if you recreate this scenario when loading to FCCS, you will have a much different result! When performing a replace load to FCCS, invalid members will result in meaningful errors that include the invalid member name(s)! Even more odd, Oracle Support will tell you that both are working as designed. It would seem that PBCS should behave similarly to FCCS to simplify troubleshooting efforts. (Perhaps if enough people raise this as an issue, they will address it?)

Work Around

The easiest way to work around this issue is to perform two data exports to PBCS for Replace operations. The first export should be a Merge load. As the dynamic clear script will not be generated for a Merge, this will result allow you to receive specific errors in the event that there is a data load failure. After have a successful Merge load, then perform the Replace load to ensure that all data gets cleared, etc.

Job Output from Merge/Replace Load Automation Process

Synchronizing FDM (9.3.1) Applications w/ T-SQL

At my current location we have 1 main and 2 supporting FDM (9.3.1) applications. The main app is used to load production G&L data, and the supporting apps are used for loading our Budget and Forecast data and all are separated to ensure that a mistake in a multi-load template is minimized.
The problem we have with this setup is that we have to do 3x the maintenance work when it comes to users and locations.

While the users and locations are the exact same in each application, there are some differences; however: a.) Data Maps in Budget / Forecast are simply * to * as we use multi-load templates that have hte actual HFM account/entity names in them whereas production maps account for each company’s GL b.) Categories are different as we limit each FDM app to only the exact Category (Scenarios) required. c.) Periods are different as we limit each FDM app to only the needed periods required.

The solution for me was to implement a SQL direct copy routine for the information I need to move. This helps as I can : a.) automate this task b.) copy only what I want c.) Perform the task relatively quickly.

While the Workbench offers the ability to import / export components, I ran into trouble with it not correctly importing all of the data and it also restrcted my flexibility. Copying the entire database was not viable either as there were pieces I did not want to update….
The script below illustrates how to perform a copy via SQL. *bold*Please note that if you want to use this (or base a script off of this) that there’s absolutely no warranty and its use at your own risk.bold I’m fairly comfortable that this works fine (as I’ve tested this in our Dev), but you should test it on your own as well.

Also note, that this script does not copy ALL tables as I didn’t need them all for my purpose.

The script performs the following :

– Clears data from Target Database – Data Archives – Data Maps – Data
– Logs – Import Groups/Items – Validation Groups/Entities – Users / Partition Security – Partition Hierarchies / Links – Partitions (locations)
– Copies data from Source Database: – Import Groups / Items – Validation Groups / Items – Partitions (locations) – Partition Hierarchy / Links – Users / Partition Security
– Updates Parent Location setting on all Data Load locations (This is since I want all of my data load locations in the Budget / Forecast apps to use a * to * map. I added a new location in the Prod Database which is defined with the * to * mappings and when I copy everything over, I want the locations to use this map instead of their production G&L map.

-=-=-==-=-=-===-=-=-=-=-=-=-=-===-=-=-=-=-=-=-=-=-=-

-- FDM Database 'Auto Copy'
-- Author : Charles Beyer
-- Date : 7/1/2010
-- Description : This script is used to automatically / manually sync up database between a SOURCE FDM application and a 
--                DESTINATION FDM application.
-- NOTES
-- ##TARGETDB## - Replace this with the name of the Database that you want to SYNC 
-- ##SOURCEDB## - Replace this with the name of the Database that is the data source

-- disable referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

--Clear User Security
TRUNCATE TABLE ##TARGETDB##.dbo.tSecUserPartition
TRUNCATE TABLE ##TARGETDB##.dbo.tStructPartitionLinks
delete from ##TARGETDB##.dbo.tStructPartitionHierarchy
TRUNCATE TABLE ##TARGETDB##.dbo.tDataArchive
TRUNCATE TABLE ##TARGETDB##.dbo.tDataCheck

--Attempt to clear out the tDataMapSeg tables
EXEC sp_MSForEachTable '
  DECLARE @TableName VarChar(100)
  Set @TableName = PARSENAME(''?'',1)
  IF  left(@TableName,8) = ''tDataMap''
      TRUNCATE TABLE ##TARGETDB##.? 
'  
GO

--Attempt to clear ou the tDataSeg tables
EXEC sp_MSForEachTable '
  DECLARE @TableName VarChar(100)
  Set @TableName = PARSENAME(''?'',1)
  IF  left(@TableName,8) = ''tDataSeg''
      TRUNCATE TABLE ##TARGETDB##.? 
'  
GO

TRUNCATE TABLE ##TARGETDB##.dbo.tLogActivity
TRUNCATE TABLE ##TARGETDB##.dbo.tLogProcess
TRUNCATE TABLE ##TARGETDB##.dbo.tDataArchive
delete from ##TARGETDB##.dbo.tSecUser

delete from ##TARGETDB##.dbo.tPOVPartition

TRUNCATE TABLE ##TARGETDB##.dbo.tBhvValEntItem
delete from ##TARGETDB##.dbo.tBhvValEntGroup

TRUNCATE TABLE ##TARGETDB##.dbo.tBhvImpItemFile
delete from ##TARGETDB##.dbo.tBhvImpGroup

-- RECOPY Data from Prod DB to Budget DB
insert into ##TARGETDB##.dbo.tBhvImpGroup
   select * from ##SOURCEDB##.dbo.tBhvImpGroup

insert into ##TARGETDB##.dbo.tBhvValEntGroup
   select * from ##SOURCEDB##.dbo.tBhvValEntGroup

insert into ##TARGETDB##.dbo.tBhvValEntItem
   select * from ##SOURCEDB##.dbo.tBhvValEntItem

insert into ##TARGETDB##.dbo.tBhvImpItemFile
   select * from ##SOURCEDB##.dbo.tBhvImpItemFile

insert into ##TARGETDB##.dbo.tPOVPartition
   select * from ##SOURCEDB##.dbo.tPOVPartition

insert into ##	TARGETDB##.dbo.tStructPartitionHierarchy
   select * from ##SOURCEDB##.dbo.tStructPartitionHierarchy

insert into ##TARGETDB##.dbo.tStructPartitionLinks
   select * from ##SOURCEDB##.dbo.tStructPartitionLinks

insert into ##TARGETDB##.dbo.tSecUser
   select * from ##SOURCEDB##.dbo.tSecUser

insert into ##TARGETDB##.dbo.tSecUserPartition
   select * from ##SOURCEDB##.dbo.tSecUserPartition

--Attempt to import data
SET IDENTITY_INSERT ##TARGETDB##.dbo.tDataMap ON
insert into ##TARGETDB##.dbo.tDataMap (PartitionKey, DimName, SrcKey, SrcDesc, TargKey, WhereClauseType, WhereClauseValue, 
      ChangeSign, Sequence, DataKey, VBScript)
   select PartitionKey, DimName, SrcKey, SrcDesc, TargKey, WhereClauseType, WhereClauseValue, ChangeSign, Sequence, DataKey, 
       VBScript from ##SOURCEDB##.dbo.tDataMap
SET IDENTITY_INSERT ##TARGETDB##.dbo.tDataMap OFF

-- Update parent locations ...
update ##TARGETDB##.dbo.tPOVPartition
  set PartParent = 'BudgetTemplateLoc'
 where 
   PartName  'BudgetTemplateLoc' and PartControlsType = 1

-- enable referential integrity again
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO