Blind Date

We use FDQM to pull data from our various ERP systems into HFM.  Whenever possible we use import scripts to pull data directly from the ERP system or an intermediary SQL datamart rather than from flat files.  In addition to integration scripts that do a complete pull of the data from an external source, FDQM can use data pump scripts which provide a way to customize processing just one field when importing a file.

FDQM scripting uses vbScript so scripting features like file access are available as well as ADODB to make database connections and queries.  FDQM also provides its own API libraries to access native features such as the current POV.  These are available via the objects API (application programming interface), DW (Data Window for database access) and RES (FDQM resources).  These can all be seen in the Object Browser in FDM Workbench.

In our source systems the data is stored with fields that identify the period and year.  The RES.PstrPer object returns the period currently selected in FDQM. This is the value in the Text Description field in the Control Table for Periods

To access the period and year we take appropriate substrings of the RES.PstrPer value.

Dim StrPer 'Uses the Date POV to find the current period
Dim StrYr 'Uses the Date POV to find the current year
strPer = Left(RES.PstrPer,3) 'Retrieve the period from the POV
strYr  = Right(RES.PstrPer,4) 'Retrieve the year from the POV

If the periods are referenced by number instead of name there are a variety of ways to accomplish the translation.  I like finding the position of the period in the full list of periods then doing some math.

' The strAllPers contains the abbreviations of all the periods
'   Search for the position of the current period, subtract 1, divide by 4 and add 1
'   So for Jan we get ((1-1=0)/4=0)+1 = 1
'      for Feb we get ((5-1=4)/4=1)+1 = 2
'   etc.
'   for Dec we get ((45-1=44)/4=11)+1 = 12
'
Dim strPerNum ' Numeric value of period
Dim strAllPers ' All periods for use in index
strAllPers = "Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec"
strPerNum = ((InStr(strAllPers,strPer)-1)/4)+1

Problem

For one of our ERP systems we have to provide the dates for the start and end of the period to a SQL stored procedure.  The RES.PdtePerKey retrieves the end date for the period which is the Period field in the Periods Control Table as shown in the previous screen shot.  Then use the vbScript DateAdd function to calculate the start date of the period.

Dim dtePerKey ' Last date of the current POV period
dtePerKey = RES.PdtePerKey

Dim dteStart ' Start date of the current POV period
dteStart = DateAdd("d",1,DateAdd("m", -1, dtePerKey))

For Sep - 2016 we get 9/30/16 as dtePerKey.  We subtract one month to get the last day of August, then add one day to get the first day of September.  Note that the RES.PdtePerKey returns a date value, not a string, so use appropriate conversion functions as needed.

Our problem came when we converted from calendar year periods to 4-4-5 fiscal periods.  This means we can't guarantee that the period end date is the last day of the month or the period start date is the first of the month.  What's a mother to do?

First we configure the correct period end dates for our fiscal periods in the Periods control table.  Define the period end date and the prior period end date along with the target period and year.

In the Workbench Client there are a number of Accelerators.  Under the section Point-Of-View Information there is an accelerator named Get Period Prior Date Key.

Sweet.  In the FDQM Periods control table we define the correct end dates for the periods, use the accelerator to get the prior period end date, then add one day to get our period start date.  Double-clicking the accelerator adds the following code to your script.

'Declare Local Variables
Dim dtePriorPeriodKey

'Get prior period date key
dtePriorPeriodKey = API.POVMgr.fPeriodKey(API.POVMgr.PPOVPeriod, True).dteDateKey

Easy-peasy.  But when you run the script you get:

Error: An error occurred importing the file.  Detail: Object required: 'API'

Ain't that a kick in the knickers?  It turns out you can only use the calls to API objects in data pump scripts, not integration scripts.

Fortunately we have standard vbScript features available including ADODB which allows access to databases.  Any databases.  Even our FDQM database.  FDQM uses the tPOVPeriod table to store the period data.  We can use ADODB to make a connection to the FDQM database, query the tPOVPeriod table for the period in our POV, get the PriorPeriodKey value, then add one day.

The code I use which includes some error handling looks like this:

' Find the start date of the period.  We can't use the API in the import scripts
'   The tPOVPeriod table in the FDQM database has the period information which includes
'   the prior period end date.  So we make another ADODB connection to that database
'   and find the record for this period.
' Get the PriorPeriodKey field from the result set and add one day
'
Dim fdmSS    ' Connection to FDQM database
Dim fdmRs    ' Records returned from query
Set fdmSS = CreateObject("ADODB.Connection")
Set fdmRs = CreateObject("ADODB.Recordset")
fdmSS.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=FDQM;Data Source=MySQLServer;"

' The query should return one record
'
Dim fdmQ
fdmQ = "SELECT * FROM tPOVPeriod where PeriodDesc = '" & RES.PstrPer & "'"
fdmRS.Open fdmQ, fdmSS

' If we get no records then something is broken
'
If fdmRS.bof And fdmRS.eof Then
RES.PstrActionValue = "No Start date found"
Import_Script=False
Exit Function
Else
Dim dtePriPer  ' Prior period end date as datetime value
dtePriPer = fdmRS.Fields("PriorPeriodKey").Value
dteStart = DateAdd("d",1,dtePriPer)   ' Add 1 day to get first day of current period
End If

While I used this technique to get to the prior period date we can get any fields we want from any FDQM tables.

• tPOVPeriod has the period control table data
• tPOVCategory has the Categories control table data
• tCtrlCurrency has the Currency control table data
• tPOVPartition has the partition/location data
• tDataMap has the mapping tables for all partitions and dimensions
• tLogActivity has the process and error logging data
• tSecUser lists all provisioned users and their security level
• tSecUserPartition lists all users, their provisioned partitions, and default partition

I can't think of a good reason why you would want to get to some of this data in an import script, but it is available if you need it.  

FDQM scripting errors

Financial Data Quality Management (FDQM) is used to move data into the EPM financial applications HFM and Essbase.  It has powerful and flexible facilities for parsing flat files for import.  It can also use scripts to import data, define conditional mappings, or that get triggered at certain events.  The scripts have a .uss extension but use vbScript as the language.  Scripts are stored in the \Data\Scripts folder of the application and can be edited directly or using the FDM Workbench.

Because we have the full power of vbScript there is a lot of sophisticated processing available.  We can also leverage ADO database connections to pull data from any accessible relational data source.

This post focuses on import scripts.  In a recent project I ran into two issues that gave error messages, one of which was not particularly useful and the solution undocumented.

The situation is a working script that pulls data from a SQL datamart for a single entity.  The script generates a SQL command that includes a WHERE clause which uses the FDQM POV to define the location.  The customer wanted another copy of the script to pull data for all entities, then use that new script as the import format for the parent location.

Easy-peasy.  We copy the script, rename it, update our WHERE clause to pull for all entities, create another import format that uses the new script, and assign that format to our parent location.  In this case the initial script was called HFMSQL and our edited copy is called HFMSQL_ALL.  (I'm won't detail the mechanics of managing the import formats and locations since that is well documented.)

Error 1

The first error is reasonably descriptive.

Error: An error occurred importing the file.
Detail: Script filename [HFMSQL_ALL.uss] is different from
the procedure name entered in the script file

The first line of an import script defines a function.  That function has to have the same name as the import script file.

The error was caused by keeping the original the function name HFMSQL for the new HFMSQLscript HFMSQL_ALL.

Making the function name match the script name resolves the error.

Error 2

This is the error that gave me fits.

Error: Import failed. Invalid data or Empty content.

This looks like we aren't getting any data out of our edited query.  A handy troubleshooting tool is to use the vbScript FileSystemObject to dump information to a text file.

Create a FileSystemObject and then use the CreateTextFile method to make the debug file.  The parameter True indicates that we will overwrite an existing file.

Later in the script I write the SQL query string which is generated by the script to the debug file

The WriteLine method writes the SQL string to the debug file.  The next step sends the SQL string to the SQL connection and pulls it into the record set that gets processed later in the script.

I re-ran the import, then opened the debug file and copied the SQL query and ran that directly on the SQL server.  The query returned the records we expected.  So I don't have empty content, but how could the data be invalid?

Just to be sure I added similar debugging statements to the original working script and adjusted the new script to pull from just the location where the working script was attached.  The SQL statements that got generated were identical and running them directly on the SQL server returned the same results set.

Sometimes it helps to bring in a fresh pair of eyes so I worked with an associate who double checked my findings.  We then created another copy of the working script and ran into the issue in Error 1.  While fixing that error when he suggested searching for the original script name.  At the bottom of the script we found this:

This is a standard practice most sample scripts you find.  After the load completes you set the ActionType and ActionValue which gives the success message on completion.  But note the last line which sets the function to return True to signal a successful completion to FDQM.  This variable name has to match the function name which has to match the script name.

So this is another easy fix but it took a while to resolve, partly because we weren't paying attention when we copied the scripts, but mostly because the error message does not indicate the real source of the problem.  Hopefully this post can save someone trouble next time they run into this error.

Moving Maps

Financial Data Quality Management (FDQM, formerly FDM, formerly Upstream, currently FDMEE) is our primary tool for moving data from our general ledger systems into HFM for consolidation and reporting.  Part of the power of FDQM is that it can translate identifiers for accounts, business units, and other dimensions from the source systems so they match your HFM metadata.  This is accomplished through mapping tables.  FDQM structures locations into a logical hierarchy and associates a map and input format with each location (internally called partitions).  If each location has a different GL then you could potentially have a unique map per location but in most instances locations that use the same GL will use the same map.

http://www.xkcd.com/1500/

What if you need to migrate the mapping tables from one system to another, say from production to development?  With the FDQM user console you can export the maps to Excel and then upload them into the target environment.  But you have to do this one dimension at a time for each location.  If you have dozens of locations and many custom dimensions this is tedious and time consuming.

From the FDQM WorkBench you can export everything about your FDQM application to an .xml file.  This can include custom scripts and reports as well as standard items like locations and import formats.  When you select Locations it will select everything associated with the locations such users and privileges.  You must click the Options tab and select Export maps with locations to include the mapping tables in the export.

Exporting locations is mostly an all or nothing affair.  You can choose to not export User Privileges, but if you deselect any other item then Locations is automatically deselected which prevents you from exporting the maps.  From the FDQM perspective this makes sense since you might get an incomplete description of the location which would result in errors on the target system.  But it also means you can't export just the maps.

Problem overview

Our situation is a company that spun off some business units to form a new company.  The HFM metadata was adjusted to remove the now unused entities and accounts.  The locations in FDQM were also updated and reorganized to reflect the new structure of the company without the business units that were spun off.

A test environment was created to facilitate validation of these changes and obtain user acceptance testing.  To accomplish this we needed to ensure the current maps from production were available to the testers.  The problem was that if we imported the locations using the Workbench export from production it would overwrite the updated structure leaving us with the original structure and the discarded locations.

So we needed a way to move just the maps from production to the test environment.

Can we use SQL?

FDQM uses SQL on the back end to hold all of the structural information and some logging.  The table structure isn't well documented and not entirely obvious.  The maps are held in one of the tDataMapSeg tables.

There are 50 tDataMapSeg tables and no documentation to define which table to use.  Notice that tDataMapSeg table includes the fields DataKey, ParitionKey, and CatKey.  These are primary keys into other tables.  So not only would we need complex queries to use SQL to copy the data between environments, we potentially introduce unexpected and unsupportable errors if we tinker with key fields.

Can we use the XML?

The export produced by WorkBench is a well structured .xml file.  There is a section for each item and the members of that item are enumerated within the XML tree along with their properties and values.

In this example the userInfo element defines the users and their attributes and the userPrivileges element defines which partitions (locations) the user can access

The problem with the maps is that they are defined as base64 encoded strings.  While this allows the binary map table to be exported to text there isn't an easy way to parse the maps.

Can we copy the encoded strings?

PowerShell version 3 and later has powerful tools for reading and manipulating XML files.  We can easily traverse the XML tree, access specific attributes and values, and adjust those attributes and values.  (You can actually do a whole lot more but for this situation that is all we need to do.)

If you are unfamiliar with PowerShell there are many excellent resources available on the interwebs for every level of expertise.  My other blog, 2scriptornot2.blogspot.com, has lots of sample code and links to some good resources.

So our strategy is:

1. Export the locations from the production and test environments to .xml
2. Iterate through the locations in the test .xml
3. Find the matching location in production .xml
4. Copy the encoded string for the mapping table from production to test
5. Save the test .xml and import it with WorkBench to update the maps

One wrinkle I ran into is that the map for a location may be broken into several sections and the number of sections didn't always match between the environments.  I added a test for that in the code and skip those locations but note in the output that the location was skipped.  For those locations we exported the dimensions manually from the FDQM console and copied them over.  This only happened for a couple locations so was a reasonable work around.

The commented code is:

# Powershell script to copy just the mapping tables from Source to Target
#    for only the locations that are in Target
#

# Read the .xml files
#
echo 'Reading Source'
[xml]$Source = get-content 'Source.xml'
echo 'Reading Target'
[xml]$Target  = get-content 'Target.xml'

# Process only the Target locations
#
foreach ($L in $Target.weblinkconfiguration.Locations.povPartitions.povPartition) {
    echo $L.PartName

    # Get the matching povPartition from the Source tree
    #
    $SourcePart = $Source.weblinkConfiguration.Locations.povPartitions.povPartition | `
          where {$_.partName -eq $L.partName}

    # There may be more than one map per partition.
    #  Force these to be arrays so we can loop through them
    #
    [array]$SourceMaps = $SourcePart.Maps
    [array]$TargetMaps  = $L.Maps

    # Make sure the counts match.  If not we will migrate those mapping tables manually
    #
    if ($Sourcemaps.count -eq $TargetMaps.Count) {
        for ($i=0; $i -lt $TargetMaps.count; $i++) {

    # The actual mapping table is a base64 blob in the innerText of povPartition node.
    #   Copy that from Source to Target
    #
            $TargetMaps[$i].innerText = $SourceMaps[$i].innerText
        }
    } else {
        echo ("*** " + $L.PartName + "Map counts don't match : Source="+ `
              $SourceMaps.count + " , Target=" + $TargetMaps.count)
    }
}

# Save the results
#
echo 'Saving update'
$Target.Save('NewTarget.xml')

If you use this code replace the Source.xml, Target.xml, and NewTarget.xml with the full path to your export files.

Some things to note are that I can reference a specific element by listing each limb of the tree separated by a periods.  So I can use one line to drill down to the .povPartition element to access the Maps element.  (I include that tree path in the previous screenshot for a visual reference.)  I also use PowerShell typecasting to force the maps into an array even if there is just one element which allows for simpler coding.

By using a script we can repeat the process consistently and reliably over the course of several rounds of testing.  And the whole process from exporting, to copy, to importing can be completed in a few minutes.