Element Mappings using Lookup Cube

[Doro]

Hi guys,

I have the following business model in which i have to compute Maintenance Cost for a large number of Buildings. There are 2 suppliers providing Maintenance Services and they each charge a fee/sqm. the Supplier:Buildings relationships is 1:n.

The way i designed the model is the following:

-> Cube 1: A cube containing the Fee/sqm for each supplier
-> Cube 2: A cube containing the Square Meters of each Buildings
-> Cube 3: A lookup cube containing a map between Building and Supplier
-> Cube 4: A summary cube which computes the total cost per building by multiplying the fee/sqm from Cube 1 with the Square Meters from Cube 2 taking in account the supplier for each Building from Cube 3

I can't manage to make use of the map in Cube 3. Is there any way to reference this by using rules?

Thanx,

[lotsaram]

I can't manage to make use of the map in Cube 3. Is there any way to reference this by using rules?

Of course there is. Presumably you created cube 3 because the supplier-building relationship can be time bound? Otherwise supplier could be a attribute of building.

If you are looking for help, follow the request for assistance guidelines. Unless you show the structure of the cubes and the rules you have done no one can help you.

[Doro]

Thank you for the fast response,

lotsaram wrote:
Presumably you created cube 3 because the supplier-building relationship can be time bound? Otherwise supplier could be a attribute of building.

Yes, it is time bound.

So the structure of the cubes is the following:

Cube 1:
- Buildings Maintenance Measures
- Maintenance Price/sqm
- Buildings Maintenance Vendors
- Scenario
- Year
- Month

Cube 2:
- Cost Center
- Buildings Maintenance Measures
- Surface (sqm)
- Scenario
- Year
- Month

Cube 3:
- Cost Center
- Buildings Maintenance Vendor
- Scenario
- Year
- Month

Cube 4:
- Cost Center
- Buildings Maintenance Cost Types
- Preventive Maintenance Cost
- Buildings Maintenance Vendor
- Scenario
- Year
- Month

And the rule for Cube 4:

Code: Select all

['Preventive Maintenance Cost' ] =N:DB('Cube 2', !Cost Center, 'Surface (sqm)', !Scenario, !Year, !Month)*DB('Cube 1', 'Maintenance Price/sqm', !Buildings Maintenance Vendors, !Scenario, !Year, !Month);

The Cost Center dimension represents the buildings.

How do i fit Cube 3 here?

[lotsaram]

I don't see any measure dimension in cube 3. How is the building/vendor relationship being stored? As a value of 1 at the intersection of scenario/building/vendor/time? To be useful in the calculation you need to be able to lookup the scenario/building/time intersection and return a string corresponding to the vendor name. You need cube 3 to look something more like this:
- Cost Center
- Scenario
- Year
- Month
- Building Lookup Measures
where Building Lookup Measures would contain a string measure 'Vendor'

The rule in Cube 4 would then look something like:

Code: Select all

['Preventive Maintenance Cost' ] = N: 
DB('Cube 2', !Cost Center, 'Surface (sqm)', !Scenario, !Year, !Month) * 
DB('Cube 1', 'Maintenance Price/sqm', DB('cube 3', !Cost Center, !Scenario, !Year, !Month, 'Vendor'), !Scenario, !Year, !Month);

[Doro]

That's exactly how cube 3 looks and how the relationship is defined, there's a string element inside the "Building Maintenance Vendor" dimension which contains the Vendor.

I wrote the rule exactly as you posted and i get the following result: The prices are retrieved correctly in Cube 4 but the value is calculated for all the vendors, not only for the one assigned in Cube 3.

So, for example: if my vendors are {Vendor1, Vendor2} with the prices/sqm {10, 20} and Building1 has 100sqm with Vendor1 supplying the services.

The result should be:

Building1 - Vendor1: 10*100=1000
Building1 - Vendor2: 0

but instead it's:

Building1 - Vendor1: 10*100=1000
Building1 - Vendor2: 10*100=1000

The same result is available also if the vendor is Vendor2 or other vendor..the price is correct (20 for Vendor2 in my example) and it's multiplied with whatever sqm of the building and calculated also for all the other vendors.

Any ideas?

[lotsaram]

There are more elegant ways of doing it (I would have a numeric lookup evaluating to 1 or 0 and multiply by that) but a simple boolean test will do.

Code: Select all

['Preventive Maintenance Cost' ] = N: 
IF( !Buildings Maintenance Vendor @= DB('cube 3', !Cost Center, !Scenario, !Year, !Month, 'Vendor'),
   DB('Cube 2', !Cost Center, 'Surface (sqm)', !Scenario, !Year, !Month) * 
   DB('Cube 1', 'Maintenance Price/sqm', DB('cube 3', !Cost Center, !Scenario, !Year, !Month, 'Vendor'), !Scenario, !Year, !Month),
   0
);

[Doro]

That did the trick, it works perfect now

Thank you very much,