lookups in Bex????

Hi Experts,

We have a scenario, where in i need to populate results of a query using lookups.

There are some BPC cubes on which I need to build queries using BEx Designer.

Fields in BPC Cube - ENTITY, COMPANY, COUNTRY ... etc

Data in cube:

ENTTIY COMPANY COUNTRY KEYFIG

BU_ABC 1234 CON_IN 75.52

BU_XYZ 3456 CON_US 75.36

BU_ABC 23456 CON_US 78.52

BU_ABC 23456 CON_IN 78.52

The fields Entity , Company , Country are dimensions and have data stored in it.

Query Output.

BU PU SU COUNTRY KEYFIG

ABC ABCD AB IN 75.52

XYZ XZ X US 75.36

Query Output should be based on look ups from Dimensions like ENTITY, COUNTRY.

Below is a sample data available in ENTITY Dimension.

ENTITY dimension:

ID EC_ENTITY PU SU

BU_ABC ABC PU_ABCD SU_AB

PU_ABCD ABCD 0 0

SU_AB AB 0 0

BU_XYZ XYZ PU_XYZ2 SU_X

SU_X X 0 0

SU_XYZ2 XZ 0 0

For eg: in Cube we have the first record entiy as BU_ABC.

For this it has to lookup in Entity dimention. In ID Column it finds BU_ABC , corresponding to this the value from EC_ENTITY should be populated for BU field in Query output.

For PU: - Check the PU value in ENTITY DIMENSION for BU_ABC. (it is PU_ABCD).

Do a search in the ID Column of ENTITY Dimension and enter the corresponding EC_ENTITY value in Query output. (here value is ABCD.)

For SU:- Check the SU Value in ENTITY DIMENSION for BU_ABC. (it is SU_AB).

Do a search in the ID Column of ENTITY Dimension and enter the corresponding EC_ENTITY value in Query output. (Here value is AB).

Similarly this procedure needs to be followed for all the records and all dimensions in the cube.

How can we achieve this in BEx or WAD?

is there any other way to achieve this.?

Thanks in advance.