I am designing a query on a MultiProvider that combines a cube and ODS. the common characterisitics in the cube & ODS are Material, fiscal period, country.
I have 2 rows & 1 column and several free characterisitics (mostly navigational attributes of Material).
Rows: KF1 (from cube) & KF2 (from ODS)
Column: Fiscal Period
I have done all the selections & Identification in the multiprovider and set the ODS for BW reporting.
I am not able to get data from ODS with query on MultiProvider.
And i need to add another row that calculates the product of KF1 & KF2. KF1 is sales quantity and KF2 is price identified uniquely by Material/Country combination. When we report at Country level wihtout any material selection the output revenue should be SUM of individual products between KF1 & KF2 (if there are 3 materials (A) sold that have a price of X and 2 materials (B) sold that have a price of Y, the answer should be 3X + 2Y instead of (32) * (XY)/2.
and also i have geographical hierarchy for 0country which is used in ODS & cube. can i use the geographical hierarchy for ODS as well for aggregation of values?