Hi All

My fact table has quantity and currency amounts, the currencies can be
USD, CAD, AUD, EUR, GBP and the units have a unit of measure and can
be like - LB, PC, KG, Tonne,etc. This is how my fact table for units
appear:

================================
Material QtySold Units
================================
ABC 10000 PC
DEF 12 LB
================================


Similarly the sales volumes are stored as follows:

================================
Material Sales Currency
================================
ABC 275 CAD
DEF 800 USD
950 950 GBP
================================

Can you please let me know how I can model this in a MS OLAP
cube which will enable me to display by Sales Volume as
follows:

ABC 8800 USD
DEF 1200 CAD

Similarly I want to display the Unit of measure for the units
sold as follows:

ABC 1200 LB
DEF 880 UNITS
GHI 1200 Tonnes

Currently I am storing the currency as Currency dimension and the unit
of measure as a Unit dimension in the MS OLAP cube.

Also how can I alternatively display currency as a prefix
notation like:
CAD 275
USD 800
GBP 950

Please advice how I can display my currencies and unit of
measure from the cube into a single Excel cell if possible
also how should I model this to view the sales value with
the currency key coupled to it similarly how can I view the
qty with the Unit of measure attached to it.

Thanks
Belinda

The short answer is that currency becomes a dimension. You can read a
discussion of currency conversion within an artical about calculated cells
by Tom Chester at http://www.sqlpass.org/news/02May/index.cfm.

- Matt Carroll
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"Belinda" <belindacur (AT) yahoo (DOT) com> wrote

Hi Matt

Many thanks for the link it is highly appreciated. More than currency
conversion I am really after trying to display the Currency and Unit
of Measure after the Measure value appreciate if somebody can suggest
a workaround in MDX or whatever to accomplish the same.

Thanks
Belinda

"Matt Carroll [MS]" <MattCar (AT) online (DOT) microsoft.com> wrote

I've heard of situations where people have used calc cells as a way to
creatively format cells.

public @ the domain below
www.tomchester.net


"Belinda" <belindacur (AT) yahoo (DOT) com> wrote

Hi Tom

Many thanks for your feedback can you kindly throw some light
how I can accomplish displaying Currency keys like USD, CAD
and Unit of Measure like KG, LB,etc to be combined and displayed
using calculated cells.


Thanks
Belinda

"Tom Chester" <publicNOSPAM (AT) tomchester (DOT) net> wrote

The calc cell would have a formula something like this:

str(calculationpassvalue(Measures.CurrentMember,-1)) + " $"

Except don't hardwire currency. I'd test it out using your front-end(s)
before going whole hog.

public @ the domain below
www.tomchester.net


"Belinda" <belindacur (AT) yahoo (DOT) com> wrote