Ignoring data in #SQL Scripts

Greetings from Miami :-)

Sometimes we want some source data to be ignored during the export step but being kept in FDMEE.
There can be many reasons that take us to that decision:

• We may want not to stop the process when unmapped members exist (automap)
• Invalid data coming from your ERP that you want to import but not to export
• We just want to use it as audit information
• ...

How can we achieve this? there is a special value you can set in your mappings as target: IGNORE.

Today I want to provide you with a bit of insight about how ignoring data works, especially when you want to create a SQL mapping script to set IGNORE as target value.

As usually, let's see an example.

I will be direct, I want to ignore GL data for Building account. In order to make it simple, I can create an explicit mapping as follows:

Having the mapping rule as explicit ensures it will be executed at first place before any other rule.

What happens when data is imported? We will see that the target account has been mapped to IGNORE:

Although we can see the data in FDMEE, that line will not be included in the DAT file for my target application.

We all know that FDMEE is really cool for filtering and searching. Actually there is a new feature to show in the grid ignored data only:

Even if you can use Query by Example to filter values in the grid (just type IGNORE in any target column) with this feature you will see all ignored data regardless the dimension you had the mapping for.

That's easy, right? there is not so much to add, or is there?

Is "ignore" the same as "IGNORE"?

Like Saint Thomas said, if I don't see it, I won't believe it.

Target value for ignore mapping is not case sensitive, at least when using SQL Server with no case sensitive collation :-)

In any case, as best practice, I recommend using upper case always.

What's happening in TDATASEG table?

Besides having the corresponding target column as "IGNORE" (ACCOUNTX, ICPX, etc.), there is a flag in TDATASEG which indicates the status of the row. That column is named VALID_FLAG and can have three different values:

Actually this is the column the Show option uses to filter data in the grid.

Can I set target value to IGNORE in a SQL mapping script?

Sure, let's see an example where data must be ignored if amount is less than €5000:

We run our Data Load Rule after we create our mapping but we get an intersection check report (good I enabled it), why? how can this be working properly with the explicit mapping but not with the SQL one?

The report above is basically saying "account IGNORE is an invalid member in HFM" (you would get an export error when loading into HP/Essbase)

Time for troubleshooting, one of my favourist task. First thing I would do is to select Show > Ignored data:

Mmm, no data is shown... was it actually ignored? It seems it wasn't.

What is FDMEE doing when applying the mapping? I always suggest to set log level to 5 and see what's happening there. Best way to learn.

As you can see above, mapping was correctly applied but we don't see anything about the VALID_FLAG column. Actually I would expect this to be set as "I". Let's check in the database:

Hold on, so target account is set to "IGNORE" but valid flag column is set to "Y". How can this happen? We do expect value "I".

I already raised this a bug and luckily this will be fixed in 11.1.2.4.100 (coming soon).

However, if you want to have SQL mappings to ignore data then you can create your SQL script as follows:

After re-applying the mapping we can see how valid flat column is now set to "I". Good! we have what we wanted!

And that's making it work as expected:

One of the main features of #SQL Scripts is that you can update more than one TDATASEG_T column in the same script (that's the table where mappings happen. So basically SOURCE > TDATASEG_T > Mappings happen > Data moved to TDATASEG). Therefore we have used that capability for our workaround :-)

Quick update: if you want to avoid updating VALID_FLAG in the mapping script, you can have an event script AftProcMap in order to update that column in TDATASEG when any of the target dimensions are "IGNORE".

Enjoy!