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.
1. Select the Setup tab and then System Settings.
2. Select System Settings –> Other.
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.
4. Select Workflow –> Data Load Mapping.
5. Select the Explicit Map Type.
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.
7. Update the Source Value to record a change and then select Save.
8. Finally, delete the map by selecting the Source Value and then Delete. Once the Delete Confirmation occurs, select Yes.
9. Select Report Execution.
10. Select Audit Reports.
11. Two reports are provided: Map Monitor Report by Location and Map Monitor by User. Select Map Monitor Report by Location and then Execute.
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.
13. Key the appropriate Start Date and End Date in the date format specified and then select OK.
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.
15. Select Map Monitor Report by User and then Execute.
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.
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.
18. Select Setup –> Report Definition.
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.
20. Select Query Definition.
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.
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”.
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.
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.
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.
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.
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.