us-analytics.png
Ask an Expert

Tutorial: FDMEE Map Monitor Reports (Finally!)

One of the features missing in FDMEE to achieve parity with FDM Classic is Map Monitor Reports, which for numerous clients is a key internal / SOX control. Thankfully, a much-needed enhancement included in PSU 11.1.2.4.100 are two Map Monitor Reports.

Specifically, the Readme states, “Two new Map Monitor Reports have been added to the Audit Reports report group: Map Monitor for Location and Map Monitor for User. Map Monitor Reports show changes made to the data load mapping rules using any of the methods data load mappings UI, Excel template imports, text file imports, and Lifecycle Management imports. The Map Monitor Reports do not capture historical data earlier than release 11.1.2.4.100. Map Monitor Reports are enabled only if the Enable Map Audit is set to 'Yes' in System Settings.”

This blog post will demonstrate how to enable the functionality and detail which map types are logged “monitored” based on the type of change and which are not.

eBook: Top 10 FDMEE & ODI Tutorials

 

1. Select the Setup tab and then System Settings.

 Screen_Shot_2016-01-11_at_11.16.00_AM.png

2. Select System Settings –> Other.

Screen_Shot_2016-01-11_at_10.01.45_PM.png

3. Update the Enable Map Audit Value from No (which is the default) to Yes and then select Save, which facilitates logging of map changes from this point forward with exceptions noted later in this post.

Screen_Shot_2016-01-11_at_10.02.27_PM.png

Screen_Shot_2016-01-11_at_10.02.35_PM.png

Screen_Shot_2016-01-11_at_10.02.47_PM.png

4. Select Workflow –> Data Load Mapping.

Screen_Shot_2016-01-11_at_10.04.23_PM.png

5. Select the Explicit Map Type.

Screen_Shot_2016-01-11_at_10.05.14_PM.png

6. As mentioned, the Map Monitor Reports have three options: Add, Change, and Delete. First, create a map by selecting Add, keying the appropriate Source Value and Target Value, and then Selecting Save.

Screen_Shot_2016-01-11_at_10.06.02_PM.png

7. Update the Source Value to record a change and then select Save.

Screen_Shot_2016-01-11_at_10.06.38_PM.png

8. Finally, delete the map by selecting the Source Value and then Delete. Once the Delete Confirmation occurs, select Yes.

Screen_Shot_2016-01-11_at_10.07.12_PM.png

9. Select Report Execution.

Screen_Shot_2016-01-11_at_10.07.55_PM.png

10. Select Audit Reports.

Screen_Shot_2016-01-11_at_10.08.31_PM.png

11. Two reports are provided: Map Monitor Report by Location and Map Monitor by User. Select Map Monitor Report by Location and then Execute.

Screen_Shot_2016-01-11_at_10.09.15_PM.png

12. The Location will default to the POV location. Select the Action Type magnifying glass, which provides the Select Parameter Value dialog box. Select All and then OK.

Screen_Shot_2016-01-11_at_10.09.50_PM.png

Screen_Shot_2016-01-11_at_10.10.21_PM.png

13. Key the appropriate Start Date and End Date in the date format specified and then select OK.

Screen_Shot_2016-01-11_at_10.11.03_PM.png

14. When prompted select Open, which will generate a report with the Delete and Add records but without the Changed record.  Time to check the Map Monitor Report by User for the same records and then investigate.

Screen_Shot_2016-01-11_at_10.11.50_PM.png

Screen_Shot_2016-01-11_at_10.12.22_PM.png

15. Select Map Monitor Report by User and then Execute

Screen_Shot_2016-01-11_at_10.13.10_PM.png

16. The Generate Report prompts are the same between reports with a User prompt replacing the Location prompt, with a notable exception: the User prompt does not have the lookup feature. Therefore, key the appropriate User, Start Date, and End Date in the date format specified and then select OK. Once this is done, select OK and when prompted Open the report.

Screen_Shot_2016-01-11_at_10.13.53_PM.png

Screen_Shot_2016-01-11_at_10.14.05_PM.png

17. Select Open when prompted. The report is rendered with the Add and Delete records also. Curious but not unexpected, as the reports are basically the same as we will learn from a query perspective. Nevertheless, time to investigate further.

Screen_Shot_2016-01-11_at_10.15.16_PM.png

18. Select Setup –> Report Definition.

Screen_Shot_2016-01-11_at_10.16.06_PM.png

19. Restrict the list of reports to the Map Monitor Report(s) by keying Map and then selecting Enter. The Query Definition utilized for both reports is Map Monitor Query. 

Screen_Shot_2016-01-11_at_10.17.06_PM.png

20. Select Query Definition.

Screen_Shot_2016-01-11_at_10.17.46_PM.png

21. Restrict the list of queries to the Map Monitor Query by keying Map and then selecting Enter, which will display two queries:  Map Monitor Action Query and Map Monitor Query.

Screen_Shot_2016-01-11_at_10.18.32_PM.png

22. After keying a handful of enters to adjust formatting, the entire query syntax is visible. The Select and Where Clause sections are the typical syntax. Therefore, the analysis will begin with the one of the two objects “aif_artifact_audit_v” listed after the FROM. Note: The tpovpartition object is the table that stores location detail, which is the reason for starting with “aif_artifact_audit_v”.

Screen_Shot_2016-01-11_at_10.19.08_PM.png

23. For this environment, the relational repository is SQL Server. Therefore, I start SQL Server Management Studio and log into the appropriate database server. The object that we are investigating is a view, so select the object, right click, and script the view as Alter to a New Query Editor Window.

Screen_Shot_2016-01-11_at_10.19.55_PM.png

Screen_Shot_2016-01-11_at_10.20.22_PM.png

24. The view syntax will be displayed and, after a few enters, reformatted for readability. Essentially, the view is a standard select with conversion of LAST_UPDATE_DATE to a VARCHAR(25) datatype and aliases as both CHANGE_TIME and CHANGE_DATE. As the view does not have a WHERE clause, our next step is to review table AIF_ARTIFACT_AUDIT.

Screen_Shot_2016-01-11_at_10.21.18_PM.png

25. The AIF_ARTIFACT_AUDIT table is queried with the WHERE clause specified as ARTIFACT_TYPE = ‘MEMEBER_MAP_AUDIT’ as this table stores other types of audit records, such as System Settings. Once the query is written “SELECT * FROM AIF_ARTIFACT_AUDIT WHERE ARTIFACT_TYPE = ‘MEMBER_MAP_AUDIT”, execute and once again two records are displayed.  Time to test the other map times: In, Between, Multi Dimension, and Like. To alleviate reader boredom, I will skip the creation of the maps and proceed to the results.

Screen_Shot_2016-01-11_at_10.21.50_PM.png

26. Essentially, I added a map for each FDMEE map type and then changed the map. The results of the subsequent Map Monitor By Location is displayed. In summary, the change is recorded for map types Between, In, and Like, but not Multi Dimension.  Table 1 provides further detail.

Screen_Shot_2016-01-11_at_10.22.41_PM.png

Table 1 summarizes the Map Type, the Action (Add or Change), What Changed, and whether the change is Logged.

TABLE 1

Map Type

Action

What Changed

Logged

Explicit

Add

N/A

Y

Explicit

Change

Source Value

N

Explicit

Change

Target Value

N

Explicit

Change

Change Sign

N

Explicit

Change

Description

N

Explicit

Delete

N/A

Y

Between

Add

N/A

Y

Between

Change

Source Value

Y

Between

Change

Target Value

Y

Between

Change

Change Sign

Y

Between

Change

Description

N

Between

Change

Rule Name

Y

Between

Delete

N/A

Y

In

Add

N/A

Y

In

Change

Source Value

Y

In

Change

Target Value

Y

In

Change

Change Sign

Y

In

Change

Description

N

In

Change

Rule Name

Y

In

Delete

N/A

Y

Like

Add

N/A

Y

Like

Change

Source Value

Y

Like

Change

Target Value

Y

Like

Change

Change Sign

Y

Like

Change

Description

N

Like

Change

Rule Name

Y

MultiDimension

Delete

N/A

Y

MultiDimension

Add

N/A

Y

MultiDimension

Change

Source Value

N

MultiDimension

Change

Target Value

Y

MultiDimension

Change

Change Sign

Y

MultiDimension

Change

Description

N

MultiDimension

Change

Rule Name

Y

MultiDimension

Change

Condition from Explicit to Between

N

MultiDimension

Change

Source Value

N

MultiDimension

Change

Target Value

Y

MultiDimension

Change

Change Sign

Y

MultiDimension

Change

Description

N

MultiDimension

Change

Rule Name

Y

MultiDimension

Change

Condition from Explicit to Like

N

MultiDimension

Change

Source Value

N

MultiDimension

Change

Target Value

Y

MultiDimension

Change

Change Sign

Y

MultiDimension

Change

Description

N

MultiDimension

Change

Rule Name

Y

MultiDimension

Change

Condition from Explicit to In

N

MultiDimension

Change

Source Value

N

MultiDimension

Change

Target Value

Y

MultiDimension

Change

Change Sign

Y

MultiDimension

Change

Description

N

MultiDimension

Change

Rule Name

Y

MultiDimension

Delete

N/A

Y

N/A

Delete All Mappings

N/A

Y


When creating this blog post, I opened a SR with Oracle and a Bug # was assigned: Bug 22526857 - AIF_ARTIFACT_AUDIT TABLE NOT LOGGING ANY CHANGES FOR EXPLICIT AND MULTI DIMENSION.

FREE eBook: The Top 10 FDMEE & ODI Tutorials

Ask an EPM/BI Advisor

If you're here, you've got questions — and we've got answers. Book your consultation to ask us about any range of topics, including:

  • Evaluating EPM or BI technologies
  • Comparing on-prem vs. cloud
  • Planning upgrades and migrations
  • Estimating project costs and timeframes
  • And much more — ask us anything!

Let our experts tackle your toughest questions for you.

Let's Talk