Hi all,

I am using Excel Pivot table for OLAP.

I just wanted to ask about the general opinions about how to design this
particular cube structure.

January
--------
Budget
-------
John $1200
Jake $1300


Actuals
-------
John $1400
Jake $1200


Variance
---------
John ($200)
Jake $100


How many measures do I have to declare? If I declare 3 measures (budget,
actuals, variance), Excel would group those 3 measures
for each employee and the report wont appear in the above format.

If I just have one measure called "amount" that has budget / actuals /
variance all populated in to it, I can design a report like this.
But in that case Pivot charting would be difficult as I wont be able to
segregate budget / actuals / variance as bars.

Any ideas / suggestions?

Thanks,
John

Perhaps you should use another domension, called version,
to suuport this kind of things.
So you gonna have 4 dimensions: measure, time, person and version.
Hope this help.

Indra

"John Thomas" <ptjohn (AT) hotmail (DOT) com> wrote

Hi John,

You should create 2 cubes:
1. Actuals
Measure: Actual
2. Budget
Measure: Budget

Shared Dimensions: Time, Person

And then create a Virtual cube Actuals vs. Budget based on Actuals abd
budget Cubes with a variance Measure...

Hope this helps

Poch

"M Indra Bangsawan" <indra (AT) gmx (DOT) fr> wrote

Agreed, a version, aka "scenario" dimension is critical.

I recommend that you have only one measure. If you need to plan different
items, use a Chart of Accounts dimension.

tom @ the domain below
www.tomchester.net


"M Indra Bangsawan" <indra (AT) gmx (DOT) fr> wrote

Thanks Guys !!

I was able to construct the cube atlast. I added 2 calculated members for
Variance to the cube
(one for Fees and one for Expense).

Right now it is appearing like this:

Project Scenario
-------- ---------
ProjectName Fees [Plan] $2000
Fees[Actuals] $1300
Expense[Plan] $800
Expense[Actuals] $1400
Fees [Variance] $2000 <== Calculated
member
Expense[Variance] $1300 <== Calculated member


Now I want to position the "Fees [Variance]" calculated member in the right
place like this:

Project Scenario
-------- ---------
ProjectName Fees [Plan] $2000
Fees[Actuals] $1300
Fees [Variance] $2000 <== Calculated
member
Expense[Plan] $800
Expense[Actuals] $1400
Expense[Variance] $1300 <== Calculated member

Any suggestions ?


Thanks!!


---------------------------------------------------------------


I usually build this kind of cubes like this:

Dimension Scenario
Actual
Budget
Variance - calulated as [Scenario].[Budget] - [Scenario].[Actual]

Dimension TimeFrame (If anyone have a better name suggestion - please let me
know....)
Current Period Data on this member
Year-to-date General calulatuon for each month
IIf([Period].Currentmember.name = 'Jan' .....
Total (Total year - for budgets only)

Dimension Period
Jan
Feb
Mar.....

Dimension Year
2003
2002
2001....

Dimension Accounts
Any measure like accounts, KPI's etc

Measures
Value or Amount in $

Actual solution may depends of your front end:

Excel Only: Meassures are treated more or
less as any other dimension
OWC Office Web Components: Mesures will always be treated as data and does
always work as expcted in dimension-over-dimension
reporting. Measures can
only be show as rows or columns. Many meassures thus somewhat limits the
reporting functionaliy in
OWC
Most other client tools: Measures are treated more or less as
any other dimension

If you are planning to use the OWC client the above approach works nice -
otherwise the timeframe dimension can be converted to measures
as this cube only have 1 meassure.


RE
Bjørn T










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