Derived values from date dimension....

#1

Hello,

I'm coming across an extremely challenging problem (as far as my technical
knowledge goes) on a cube. This report that I'm building now requires the
following rules

1. Number of goods bought on Sales day 1 and Paid
2. Number of goods bought on Sales day 1 and paid 6 days later

Here's what I did
Measures:
1. Number of goods bought and paid

Dimensions
1. Sales day
2. Day processed

I'm finding it extremely difficult to try to count the 6 days based on the
sales day and processed day. This cube is historical in nature. Can I create
a derived dimension which allows me to do that?

Any advice is appreciated.

Thanks

#2

I managed to find a solution to my problem is am now facing another issue
with MDX...

To make things simple..what I'm trying to do is to create calculated measure
that allows me to aggegate data from the start of the sales date till now...
and another measure that display the most recent total

If the sales launch date is 01/04/2005, this is how it'll look like for
03/04/2005

Date Dimension: 03/04/2005
Actual Measure: (data from 01/04/2005 to now)
Aggregate Measure: (data from Sales day 1 till 03/04/2005)

I managed to use the aggegrate function to get our aggregated measure and
also separated is able to get the values for the actual measure...but I
can't put them together under cube editor's Calculated Measure's Value
Expression...

I don't thikn I can do both under MDX since the slicer will slice both
actual and aggregated measure hence giving me the same results....

Anyone got a better way to do it?

Thanks.

"SQL McOLAP" <SQLMcOLAP (AT) discussions (DOT) microsoft.com> wrote

Quote:

Not knowing the details of your fact data, I'll go on the assumption that
your fact data is typical sales/invoice line data. My suggestion here is
based upont the fact that the data is like this, with
date/customer/product
info denormalized onto a fact row.

One way to approach this is in the SQL table with a flag (a new column,
with
Y or N if you like) showing that a product was bought and paid for six
days
later. SQL processing would have to be run to update this flag. (writing
SQL set operations or a cursor could do this) This flag would then act as
a
new dimension. The question is, do you want the number of line items that
were bought and paid for 6 days later, or the sum of the product qty that
was
bought and paid for 6 days later. If it's the former, you'll want a line
count (simple as SELECT 1 as Line_Count in a fact view) so that they'll
sum
up by this new "bought and paid for 6 days later" flag. If it's the
latter,
the qty on the line will just sum up automatically for this new flag.

More info would be necessary if your fact data is not like this, or if
this
is not how you want the sum of products bought and paid for withing 6 days
to
be represeneted.

Good luck.

"Nestor" wrote:

Hello,

I'm coming across an extremely challenging problem (as far as my
technical
knowledge goes) on a cube. This report that I'm building now requires
the
following rules

1. Number of goods bought on Sales day 1 and Paid
2. Number of goods bought on Sales day 1 and paid 6 days later

Here's what I did
Measures:
1. Number of goods bought and paid

Dimensions
1. Sales day
2. Day processed

I'm finding it extremely difficult to try to count the 6 days based on
the
sales day and processed day. This cube is historical in nature. Can I
create
a derived dimension which allows me to do that?

Any advice is appreciated.

Thanks

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Derived values from date dimension....

microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap

Derived values from date dimension.... - 04-13-2005 , 09:07 PM

Re: Derived values from date dimension.... - 04-14-2005 , 08:55 AM