OBS Query

[Owen_R]

Hi all and happy new year to you

I need some help querying the live OBS tables.

We have a resource OBS which has a structure like this: Top | Professional Family | Country | Site

All resources are mapped to at least country, but typically site level. For a given resource I want to be able to retrieve the value at the Professional Family level and not the lowest level.

Ordinarily I would go straight to NBI_RESOURCE_CURRENT_FACTS / NBI_DIM_OBS and get the value from LEVEL2_NAME but in this case I want the data to be real-time not datamart based.

Has anyone got any SQL that allows me to pick the data from a higher level of the OBS for a given resource, in real time? I'm struggling trying to query PRJ_OBS_UNITS, PRJ_OBS_UNITS_FLAT and OBS_UNITS_FLAT_BY_MODE but I'm assuming the answers are in here somewhere.

Thanks

Owen

[Anon Anon]

This sort of thing....

       SELECT unit.name, unit.depth
       FROM   prj_obs_associations assoc,
              prj_obs_units_flat flat,
              prj_obs_units unit,
              prj_obs_types types
       WHERE  assoc.record_id     = [color=#ff0000]<< your resource internal ID (i.e. srm_resources.id) goes here >>[/color]
       AND    assoc.unit_id       = flat.unit_id
       AND    flat.branch_unit_id = unit.id
       AND    unit.type_id        = types.id
       AND    types.unique_name  = '[color=#ff0000]<< the ID (as seen in the admin tool) of your OBS type goes here >>[/color]'
       AND    assoc.table_name   = 'SRM_RESOURCES'
       AND    [color=#ff0000]unit.depth = 2[/color]

Obviously you can simply vary the logic for other association types / depths etc.

[Owen_R]

Yep exactly that sort of thing! Couldn't see the wood for the trees and had not noticed the Depth column. Much appreciated.

[Deactivated User]

You could also use something like

select
-- only resources with a defined link to an OBS unit
-- displays the full OBS unit path
srm_resources.id,
srm_resources.unique_name,
srm_resources.full_name,
prj_obs_types.id Type_id,
PRJ_obs_types.unique_name Type_unique,
PRJ_obs_types.name OBS_type_name,
prj_obs_units.ID OBS_ID,
prj_obs_units.Unique_name OBS_unique,
prj_obs_units.name OBS_name,
OBS_path.OBS_full_PATH
from
srm_resources,
prj_obs_units,
prj_obs_associations,
prj_obs_types,
(SELECT

prj_obs_types.name OBS_type_nAME,
(
ISNULL(Parent10.name,'') + ISNULL(Parent9.name + '/','') + ISNULL(Parent8.name+'/' ,'') + ISNULL(Parent7.name + '/','') +
ISNULL(Parent6.name + '/','') ISNULL(Parent5.name  '/','') + ISNULL(Parent4.name + '/','') + ISNULL(Parent3.name + '/','') +
ISNULL(Parent2.name + '/','') + ISNULL(Parent1.name + '/','') + prj_obs_units.name) OBS_full_PATH, prj_obs_units.id OBS_unit_id

from

prj_obs_types,
prj_obs_units left join prj_obs_units Parent1 on prj_obs_units.parent_id=Parent1.id
left join prj_obs_units Parent2 on Parent1.parent_id=Parent2.id
left join prj_obs_units Parent3 on Parent2.parent_id=Parent3.id
left join prj_obs_units Parent4 on Parent3.parent_id=Parent4.id
left join prj_obs_units Parent5 on Parent4.parent_id=Parent5.id
left join prj_obs_units Parent6 on Parent5.parent_id=Parent6.id
left join prj_obs_units Parent7 on Parent6.parent_id=Parent7.id
left join prj_obs_units Parent8 on Parent7.parent_id=Parent8.id
left join prj_obs_units Parent9 on Parent8.parent_id=Parent9.id
left join prj_obs_units Parent10 on Parent9.parent_id=Parent10.id
where
prj_OBS_units.type_id=prj_obs_types.id
) as OBS_path
where
prj_obs_associations.record_id=srm_resources.id
AND prj_obs_associations.UNIT_ID=prj_obs_units.id
and prj_obs_associations.table_name= 'SRM_RESOURCES'
and prj_obs_types.id=prj_obs_units.type_id

and OBS_path.OBS_unit_id=prj_obs_units.id
and OBS_path.OBS_type_nAME=PRJ_obs_types.name

-- and srm_resources.is_active = 1
-- and prj_resources.prisopen = 1
-- and prj_resources.prtrackmode = 2

Order by OBS_ID desc, srm_resources.full_name


for MS SQL and pick your level from there

there is some more under
SQL to Add OBS of Resources
5732792

Martti K.

[Owen_R]

And thanks for that suggestion too Martti - our posts crossed I think.

[Deactivated User]

That has happened before.
The time stamps are the same and yours is earlier so you must be closer to the server ;-)

Martti K.