Why Does Adding a UDF or Code Truncates the # of Resources in List?
- by Jeffrey McDaniel
Go to the Primavera - Resource Assignment History subject area. Go under Resources, General and add fields Resource Id, Resource Name and Current Flag. Because this is using a historical subject area with Type II slowly changing dimensions for Resources you may get multiple rows for each resource if there have been any changes on the resource. You may see a few records with current flags = 0, and you will see a row with current flag = 1 for all resources. Current flag = 1 represents this is the most up to
date row for this resource. In this query the OBI server is only querying the W_RESOURCE_HD dimension.
(Query from nqquery log)
select distinct 0 as c1,
D1.c1 as c2,
D1.c2 as c3,
D1.c3 as c4
from
(select distinct T10745.CURRENT_FLAG as c1,
T10745.RESOURCE_ID as c2,
T10745.RESOURCE_NAME as c3
from
W_RESOURCE_HD T10745 /* Dim_W_RESOURCE_HD_Resource */
where ( T10745.LAST_RUN_PER_DAY_FLAG = 1 )
) D1
If you add a resource code to the query now it is forcing the OBI server to include data from W_RESOURCE_HD, W_CODES_RESOURCE_HD, as well as W_ASSIGNMENT_SPREAD_HF. Because the Resource and Resource Codes are in different dimensions they must be joined through a common fact table. So if at anytime you are pulling data from different dimensions it will ALWAYS pass through the fact table in that subject areas.
One rule is if there is no fact value related to that dimensional data then nothing will show. In this case if you have a list of 100 resources when you query just Resource Id, Resource Name and Current Flag but when you add a Resource Code the list drops to 60 it could be because those resources exist at a dictionary level but are not assigned to any activities and therefore have no facts. As discussed in a previous blog, its all about the facts.
Here is a look at the query returned from the OBI server when trying to query Resource Id, Resource Name, Current Flag and a Resource Code. You'll see in the query there is an actual fact included (AT_COMPLETION_UNITS) even though it is never returned when viewing the data through the Analysis.
select distinct 0 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4,
D1.c5 as c5,
D1.c1 as c6
from
(select sum(T10754.AT_COMPLETION_UNITS) as c1,
T10706.CODE_VALUE_02 as c2,
T10745.CURRENT_FLAG as c3,
T10745.RESOURCE_ID as c4,
T10745.RESOURCE_NAME as c5
from
W_RESOURCE_HD T10745 /* Dim_W_RESOURCE_HD_Resource */ ,
W_CODES_RESOURCE_HD T10706 /* Dim_W_CODES_RESOURCE_HD_Resource_Codes_HD */ ,
W_ASSIGNMENT_SPREAD_HF T10754 /* Fact_W_ASSIGNMENT_SPREAD_HF_Assignment_Spread */
where ( T10706.RESOURCE_OBJECT_ID = T10754.RESOURCE_OBJECT_ID and T10706.LAST_RUN_PER_DAY_FLAG = 1 and T10745.ROW_WID = T10754.RESOURCE_WID and T10745.LAST_RUN_PER_DAY_FLAG = 1 and T10754.LAST_RUN_PER_DAY_FLAG = 1 )
group by T10706.CODE_VALUE_02, T10745.RESOURCE_ID, T10745.RESOURCE_NAME, T10745.CURRENT_FLAG
) D1
order by c4, c5, c3, c2
When querying in any subject area and you cross different dimensions, especially Type II slowly changing dimensions, if the result set appears to be short the first place to look is to see if that object has associated facts.
