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.