Hi Gurus,
I have one scenario with two cubes, both share about the same characteristics and only the key figures are different. What i need to do is, the financial cube is the MAIN cube, and i want get ONLY the production no from the Production Cube by mapping the records based on what Financial Cube has.
Take note with the conditions:
1. I need to give user the flexibility to filter the data by 4D code, Plant and Region at query level
2. IF user filter only 4D code, take the total of the production no. Eg. If user filter no matter what PH50, or PH 51, derive the production no as "50 + 80" = 130.
3, IF 4D code = PH50, Plant = Plant 001, Production no = 50
4. IF 4D code = PH51 , Plant = Plant 002, Production no = 80
5. IF 4D code = PH50, Region = XXX, Production no = 50+100 = 150
Problem: the query is not able to cater for the different logics for different filtering.
How do u apply the above different rules at the cube level or query level in order to get the correct Production No, the crucial part. You can use any methods, as long you can get the correct production number for the final calculation, which is Financial No / Production No.
Financial Cube
Cube A:
| 4D code | Plant | Region | Financial No. (key figure)
| PH50 | Plant 001 | XXX | 100
| PH51 | Plant 002 | YYY | 200
| PH52 | Plant 001 | XXX | 300
Production Cube
Cube B:
| 4D code | Plant | Region | Production No. (key figure)
| 11FF | Plant 001 | XXX | 50
| 11FF | Plant 002 | YYY | 80
| 11FF | Plant 003 | XXX | 100