Analytics Software

Hi,

I'm trying to write some sql code that will return the member, parent, alias, datastorage and consolidation (plan1) from the BPMA backend database (this will form part of the dimension build process, as we are using 'replace' rather than 'merge' dimensions).

So far I can return the member, parent and alias, but I can't find where the DATASTORAGE and CONSOLIDATION(PLAN1) is stored! I've spent days going through all the tables in the BPMA database and cannot find it! Can anyone point me in the correct direction? Anybody know of anywhere other than the BPMA database it could be stored? ( I don't want to use the Planning backend databases).

The code I have so far is:

Select
Entity,
Parent.C_Member_name as Parent,
alias.C_value as [Alias: Default]
From
(
select
mem.i_member_id as EntityID,
mem.C_Member_name as Entity,
rel.i_parent_member_id as ParentID,
mem.i_dimension_id as DimensionID,
mem.i_library_id as LibraryID
from
BPMA.dbo.DS_Member mem
left join BPMA.dbo.DS_Relationship rel
on rel.i_child_member_id = mem.i_member_id
and rel.i_dimension_id = mem.i_dimension_id
and rel.i_library_id = mem.i_library_id
where
mem.i_dimension_id = '114' -- Balsheet Entity
and mem.i_library_id = '1'
) a

left join
BPMA.dbo.DS_Member Parent -- Determine the parent name
on a.ParentID = Parent.i_member_id
and a.DimensionID = Parent.i_dimension_id
and a.LibraryID = Parent.i_library_id

left join
BPMA.dbo.DS_Property_Member_Array alias -- Determine the Alias
on a.EntityID = alias.i_member_id
and a.DimensionID = alias.i_dimension_id
and a.LibraryID = alias.i_library_id
and alias.I_Property_id = '111' -- Alias code

Thanks,

Jo.