Understanding how drill through works in FDMEE

In this blog, I would be talking about how the drill through process works in FDMEE. Understanding this takes some basic knowledge of relational databases and lots of patience. Now, the motivation for this was that I had a PBCS instance in which the drill through was not working between Planning and FDMEE. In order to debug it, I spent quite some time understating how it actually works.
Now the next snapshot shows the data in a Planning application that has been loaded using FDMEE.

Drilling through to the source gives me the mapping list of the data and how it came about, as shown in the below snapshot.

The drill through log for this data set is shown in the below snapshot.

The drill through log in text is as shown in the below snapshot.
2016-09-16 00:59:05,623 INFO  [AIF]: attribute[0] = system.ds.planning
2016-09-16 00:59:05,623 INFO  [AIF]: attribute[1] = app.ds.FINREP
2016-09-16 00:59:05,623 INFO  [AIF]: attribute[2] = cluster.ds.PLANNING_LWA
2016-09-16 00:59:05,700 INFO  [AIF]: attribute[3] = database.ds.Plan1
2016-09-16 00:59:05,700 INFO  [AIF]: attribute[4] = Account.id.PL1500
2016-09-16 00:59:05,700 INFO  [AIF]: attribute[5] = Period.id.Jan
2016-09-16 00:59:05,700 INFO  [AIF]: attribute[6] = Scenario.id.ACT
2016-09-16 00:59:05,700 INFO  [AIF]: attribute[7] = Version.id.Working
2016-09-16 00:59:05,700 INFO  [AIF]: attribute[8] = Entity.id.E-A
2016-09-16 00:59:05,700 INFO  [AIF]: attribute[9] = Years.id.FY16
2016-09-16 00:59:05,700 INFO  [AIF]: Application Name:FINREP
2016-09-16 00:59:05,700 INFO  [AIF]: Target Application Type:HPL
2016-09-16 00:59:05,700 INFO  [AIF]: Database Name:Plan1
2016-09-16 00:59:05,810 INFO  [AIF]: ERPI Target Application Name:FINREP
2016-09-16 00:59:05,856 INFO  [AIF]: ERPI Plan Type:PLAN1
2016-09-16 00:59:05,997 INFO  [AIF]: Year Target:FY16
2016-09-16 00:59:05,997 INFO  [AIF]: Period Target:Jan
2016-09-16 00:59:06,123 INFO  [AIF]: SELECT PARAMS:[4, 1, 2016-01-01]
2016-09-16 00:59:06,123 INFO  [AIF]: DIM PARAMS:[E-A, PL1500, Working]
2016-09-16 00:59:06,123 INFO  [AIF]: drill down query:SELECT
 abdv.partitionKey ,abdv.catKey ,abdv.periodKey,
 tpovpartition.partname as LocationName, tpovcategory.catname,  COALESCE(TPOVPERIOD.periodDesc,abdv.ATTR5) period,
 UD1X Dummycol,
 ENTITYX
,ACCOUNTX
,NULL ICPX
,UD1X
,NULL UD2X
,NULL UD3X
,NULL UD4X
,NULL UD5X
,NULL UD6X
,NULL UD7X
,NULL UD8X
,NULL UD9X
,NULL UD10X
,NULL UD11X
,NULL UD12X
,NULL UD13X
,NULL UD14X
,NULL UD15X
,NULL UD16X
,NULL UD17X
,NULL UD18X
,NULL UD19X
,NULL UD20X
,
 SUM(AMOUNTX)
, AIF_BALANCE_RULES.RULE_NAME,ABDV.CURKEY
 FROM AIF_TDATASEG_ALL_V abdv
 JOIN tpovpartition on
 abdv.partitionkey = tpovpartition.partitionkey
 JOIN tpovcategory on
 tpovcategory.catKey = abdv.catKey
 JOIN AIF_BALANCE_RULES on
 AIF_BALANCE_RULES.RULE_ID = abdv.rule_id
 LEFT OUTER JOIN TPOVPERIOD
   ON TPOVPERIOD.PERIODKEY = abdv.PERIODKEY
 WHERE 1=1
 AND tpovpartition.partitionkey in (
 ?
 )
 AND tpovcategory.catKey IN (
 ?
 )
 AND TPOVPERIOD.periodKey IN (
 ?
 )

 AND ENTITYX = ?
 AND ACCOUNTX = ?
 AND UD1X = ?
 GROUP BY
 ENTITYX
,ACCOUNTX
,UD1X
,
 abdv.partitionKey, abdv.catKey, abdv.periodKey,
 tpovpartition.partname, tpovcategory.catname, COALESCE(TPOVPERIOD.periodDesc,abdv.ATTR5), AIF_BALANCE_RULES.RULE_NAME, ABDV.CURKEY
Now, this is the path where Java comes into play. Java JDBC has a type of statement called as a PreparedStatement that is precompiled and executed at runtime by substituting parameters. In order to identify placeholders “?” is used. If you check the above query snapshot, you will see that there are a couple of “?” scattered through the query. I am highlighting just them in the below snapshot.

AND tpovpartition.partitionkey in (
 ?
 )
 AND tpovcategory.catKey IN (
 ?
 )
 AND TPOVPERIOD.periodKey IN (
 ?
 )

 AND ENTITYX = ?
 AND ACCOUNTX = ?
 AND UD1X = ?
Now, if you check the drill through log, you will observe a section of SELECT params as well. This is as shown in the below snippet.

2016-09-16 00:59:06,123 INFO  [AIF]: SELECT PARAMS:[4, 1, 2016-01-01]
2016-09-16 00:59:06,123 INFO  [AIF]: DIM PARAMS:[E-A, PL1500, Working]
Now, while I will be showing in detail how this works later on, the SELECT params refer to the FDMEE system level tables like TPOVCATEGORY, TPOVPERIOD and so on and the DIMPARAMS refer to the dimensions fields in the TDATASEG table.

The contents of the TPOVPARTITION table is as shown in the below snapshot. Observe that the PARTITIONKEY value of 4 for FinrepActuals is there in SYSTEM PARAMS since I am loading data into the FINREP_Actuals application using FDMEE.

The next snapshot shows the select parameters in the log file.

The following snapshot shows me updating the select params in the drill through query.

• TPOVPARTITION.PARTITIONKEY to indicate the application to which it has to be loaded.
• TPOVCATEGORY.catKey to indicate the Category to which data is to be loaded. (1 is Actuals in the system)
• TPOVPERIOD.periodKey updated to be equal to 2016-01-01

The dimension fields are updated as shown in the below snapshot, similar to the substitution that was done above.

On running the updated query, it fails because of the cast function for the TPOVPERIOD.periodKey.

I comment out that section as shown in the next snapshot. (Remember that since I have only one month data in FDMEE, I can ignore this field and run the query. In real-scenarios, you would have to get the CAST function running. Else you may get different results.)

On running the updated query, I get the data as shown in the below snapshot.

Observe that the data 3367 is similar to the one that was present in the Planning form.

The updated query that was run for drill through is as shown in the below snapshot.
SELECT
 abdv.partitionKey ,abdv.catKey ,abdv.periodKey,
 tpovpartition.partname as LocationName, tpovcategory.catname,  COALESCE(TPOVPERIOD.periodDesc,abdv.ATTR5) period,
 UD1X Dummycol,
 ENTITYX
,ACCOUNTX
,NULL ICPX
,UD1X
,NULL UD2X
,NULL UD3X
,NULL UD4X
,NULL UD5X
,NULL UD6X
,NULL UD7X
,NULL UD8X
,NULL UD9X
,NULL UD10X
,NULL UD11X
,NULL UD12X
,NULL UD13X
,NULL UD14X
,NULL UD15X
,NULL UD16X
,NULL UD17X
,NULL UD18X
,NULL UD19X
,NULL UD20X
,
 SUM(AMOUNTX)
, AIF_BALANCE_RULES.RULE_NAME,ABDV.CURKEY
 FROM AIF_TDATASEG_ALL_V abdv
 JOIN tpovpartition on
 abdv.partitionkey = tpovpartition.partitionkey
 JOIN tpovcategory on
 tpovcategory.catKey = abdv.catKey
 JOIN AIF_BALANCE_RULES on
 AIF_BALANCE_RULES.RULE_ID = abdv.rule_id
 LEFT OUTER JOIN TPOVPERIOD
   ON TPOVPERIOD.PERIODKEY = abdv.PERIODKEY
 WHERE 1=1
 AND tpovpartition.partitionkey in (
 4
 )
 AND tpovcategory.catKey IN (
 1
 )/* AND to_date(TPOVPERIOD.periodKey,'yyyy-mm-dd') IN (
 '2016-01-01'
 )
*/
 AND ENTITYX = 'E-A'
 AND ACCOUNTX = 'PL1500'
 AND UD1X = 'Working' GROUP BY
 ENTITYX
,ACCOUNTX
,UD1X
,
 abdv.partitionKey, abdv.catKey, abdv.periodKey,
 tpovpartition.partname, tpovcategory.catname, COALESCE(TPOVPERIOD.periodDesc,abdv.ATTR5), AIF_BALANCE_RULES.RULE_NAME, ABDV.CURKEY

While the drill through was primarily concerned with going back to the source of the data that was loaded into an application using FDMEE, it refers to the TDATASEG table that stores all the data that a user loaded and has the mappings and transformations as well. Also, the above query will be similar to what is used for pushing data from FDMEE to the application. Logically, push of data from FDMEE to the application and drill back to FDMEE are two sides of the same coin. Will try to cover the FDMEE TDATASEG table in some other blog.