Hi , mdx experts!

I have a cube PricesChanges with dimensions: Date, Product, Store and
measure Price. This cube shows when the price was changed for the specified
product/Store.

Example
Product 1/Store 1

2004 Sept 10 9.99$
2004 Sept 15 9.89$
2004 Oct 10 9.99$
2004 Dec 01 9.99$
2004 Dec 15 10.09$

Is it possible to show the price in every day.

Product 1/Store 1

2004 Sept 10 9.99$
2004 Sept 11 9.99$
2004 Sept 12 9.99$
2004 Sept 13 9.99$
2004 Sept 14 9.99$
2004 Sept 15 9.89$
....
2004 Sept Total 9.89$
....
2004 Oct Total 9.99$
....
2004 Nov Total 9.99$
...
2004 Dec Total 10.09$
2004 Total 10.09$

Thank you in advance!

Ramunas Balukonis

Ramunas -

I'm trying to think of a way this can be done natively in the cube and
nothing jumps out at me. Is there a pattern, like a specific day of the
month a price can change for an item? Or can it happen at any point? If it
can happen at any point, this will be difficult in MDX. LastSibling and
LastChild and ClosingPeriod only work for the last member or child of a
member. If these prices can change at anytime, it might be difficult.

My first suggestion, while trying to figure out an OLAP solution, would be
to create a column in your SQL table or view called "last valid price" and
populate it accordingly with SQL, using MAX(date_column) for that product and
store to get the price. You would then make this a new measure in your cube.

I'll keep thinking about a way it can be done directly within the cube.

- Phil

"Ramunas Balukonis" wrote:

Right after I wrote this, I saw an answer Pras had for a similar problem for
inventory. Doing a tail of a filtered set could do this:

Tail(Filter(Descendants([Time].currentmember),([Measures].[Price])>0),1).item(0)

...although it could be slow if there are a lot of records.

Good luck.

(and thanks Pras!!!)

- Phil

"SQL McOLAP" wrote:

Yes of course it has a hit if you have very large volume of data and
the data for the product is not collected often, On top of it if the
cube data is stored in MOLAP storage it would not be a hit at the
performance.

The Price of the product is Non-additive in nature you can also use the
time series functions like Closingperiod,OpeningPeriod to find the
ClosingPrice or Operning price for the month,Quarter, Year
it depends on how you want to show the end user the data

Regards,
Prasanna (pras)

Phil,

thanks you for you answer. But this solution does not work for me .
Answer to your prev notes: the price can change when ever, several times in
month, or it could not change for a months. If the price did not change in
current month or day, calc member must show the last valid price.
The logic should be like:

if the Price is not null, show me exactly this value, else show the prev not
null value.

Additional help would be very appreciated.

Ramunas


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

m(0)

You can do this using a recursive calculation, it might take a while to
run, but it should be possible to do using something like

WITH MEMVER [Measures].[LastPrice] as 'iif([Measures].[Price]=0,
(Time.PrevMember,[Measures].[LastPrice]),Measures.[Price])

Note that the member refers to itself in the formula, this is what makes
the recursion work.

This is a very basic example, if you need this calculation to work over
all the hierarchies of the time dimension there are other things you
would need to add to get this to work, but this should solve the issue
you outlined in your original post.

--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1126617339.420846 (AT) loger (DOT) vpmarket.int>, ramblk2 (AT) hotmail (DOT) com
says...

Thanks, Daren

worked. As my cube is small (~ 2 mln of rows), recursive calculation run
fast. Greetings.

Ramunas Balukonis


"Darren Gosbell" <dgosbell_at_yahoo_dot_com> wrote