4 MDX Experts Part 2

[Monte]

(sorry for double posting, but it seems to me, the first thread got lost
within newer ones.)
Hello,

the Problem is that OWC is displaying wrong Total results, when setting the
filter (in OCW Pivottable manually) on time dimension (eg. only Quarters Q1,
Q2, Q3 in 2004). This applies to Previous Year Values calculated via:
([MEASURES].[Sales], ParalellPeriode([TIME].[YEAR],1))

Time Dimension in this scenario consists of (Year, Quarter, Month, Day).
Data is loaded always on day level and aggregated up.

> "the Filter set in current year"
Meant that All Level of Time dimension has been disabled and includedmembers
(filter) has been set to [2004].[Q1], [2004].[Q2], [2004].[Q3] .

So actual Sales Sum for the total Level (2004) Sums up the first 3 included
Quarters (as shown in the example).

Example assuming sales for Q1=1000, Q2=2000 and so on, in both of the years

SALES S_YTD S_PREV S_PREV_YTD
2004
Q1 1000 1000 1000 1000
Q2 2000 3000 2000 2000
Q3 3000 6000 3000 3000
SUM 2004 6000 6000 10000 10000
==== ====

10000 is wrong in this case, it also should display 6000, summing up Q1-Q3
in the last year. The Previous Year Value should also care about the filter,
displaying the parallel of only the filtered quarters in 2004.

This seems to me to be a OWC Bug which I urgently have to fix.

Deepak and Ohjoo came up with really good solutions, which unfortunately
only work, when Time dimension is in Rows (and furthermore not combined with
any other dimension).

I believe that much more user experience the same problem/bug (as previous
year values should be a common business scenario) and I'd like to ask the
community if there is already a solution or if anyone can think of a solution
for this where it doesn't matter if time dimension is in filter, row or
colums.

Best regards
Monte

[Chris Webb]

Can you try the following approach?
- Delete any calculated members etc you created on your cube for the
previous solution.
- Create a new *real* (ie not calculated) measure and call it S_PREV
- For the Source Column property, enter the value 0.
- Create a new calculated cell and call it anything you like
- For the new calculated cell, enter the Calculation Subcube as
{[Measures].[S_PREV]}, [Time].[Month].MEMBERS
- For the Calculation Value, enter calculationpassvalue(
(measures.[sales], parallelperiod(time.[year], 1)), -1)

Hopefully this will work for you! By creating a real measure rather than a
calculated measure you ensure that the VISUALTOTALS functionality which Excel
and OWC uses to calculate subtotals works as you want; the calculated cell
just inserts the previous period values over the dummy value of 0 in this
measure.

It's not an elegant solution and carries the risk of a) increased cube size,
and b) reduced query performance, as a result of the calculated cell, but if
your cube is fairly simple then with a bit of luck these shouldn't be issues
for you.

HTH,

Chris

[Chris Webb]

Sorry, if the lowest level on your Time dimension is day, the calculation
subcube should be {[Measures].[S_PREV]}, [Time].[Day].MEMBERS}

[Chris Webb]

Honestly, I can't type today - that last } shouldn't have crept in there! The

[Monte]

I tried out your approach and it worked perfectly well on my Dev-Server!

But I checked the license version of my customers SQL-Server and just as I
was afraid of ... it is a just standard edition ... so I cannot use
calculated cells to work this around :-|

Any other clues?

Best regards!

[Ohjoo Kwon]

Hi Chris,

You said your idea is not an elegant solution, but it's very wonderful idea
indeed!

Do you have any more information on visual totals with calculated members?

I have a curiosity to know that this issue is dependent on just client tools
or so-called by-design of analysis services.

Ohjoo Kwon

"Chris Webb" <OnlyForPostin...@hotmail.com> wrote in message
news:AFBCF3D5-B5E7-4FA4...@microsoft.com...

[Deepak Puri]

Here's an approach that seems to work with the Foodmart Warehouse cube;
but it involves setting up a 2nd cube and a virtual cube:

- Create a view of the fact table where 1 Year is added to the Date key
(for Foodmart, 365 is added to time_id).

- Create a second cube, copied from first, with this view as fact
table. Retain only measures whose previous year values are required,
renaming them (like SalesPrevYear).

- Combine the 2 cubes into a virtual cube, with all original dimensions
and measures + the PrevYear measures.

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

[Chris Webb]

No, I don't think there are going to be any other solutions that what Deepak
has suggested, which is to physically put the values into the measure rather
than use calculated cells.

[Chris Webb]

Well, I think the best place to start is probably the section on VisualTotals
in this white paper:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/anserddl.mspx
What the paper calls 'Visual Totals Mode' is switched on by using the
'Default MDX Visual Mode' connection string property, and this is what Excel
uses to calculate its subtotals. So once you know that the subtotals are
really just parent members with their values overwritten, it's clear that
calculated measures are going to be evaluated after this has taken place and
so aren't going to be affected - and that's why, to get the behaviour needed
in this case, it was necessary to create a real measure and get the desired
values into it somehow.

Chris

[Monte]

The approach to double all fact tables with a deferral of one year is the
only working solution in this case I see, and btw. a simple but clever
solution!

I'll have to check how data-volume growth and above all query performance is
affected by this step, but I'm confident that it will even response more
quickly than complex mdx.

Above all I'd like to thank the community especially all those who have
answered my posts, really going into my case, trying hard to find various
very decent solutions!!

Thanks for your time and thoughts!

Best regards!

[geodeep...@gmail.com]

After some weeks of thinking I was mad, after reading hundreds of posts on dozens of forums finally I found what seems a workable solution!

Honestly, I'm stonished. OLAP cubes are good at time analysis and Excel is a widely used tool easy for most users. I cannot think of any simple serious business analysis where comparing periods is not needed (be it years, semester, quarter, whatever). But the reality is that you cannot use Excel for time dimensions! Unbelievable.

WHen you define calculated members for previous periods ...
1. Time dimension cannot be used as filters.
Ok, one can deal with that telling the users to put the time dimension on rows. Not nice, but it works.
2. Subtotals adds up filtered members.
Crazy enough as Excel 2013 has a specific option to consider them or not, but only works for base measures or calculated members with no time involved. No way to make users accept that!
3. No way to use other dimensions
Once time dimension is on a row (point 1.) all other row dimensions also add up all filtered members, so useless to have the time dimension!

I wonder if OLAP, MDX and the like are just toys for us, IT people, to show other people how clever we are defining complex things! But no one can seriously use them :(

[end of rant mode :) ]

I was trying to use the duplicate cube approach, but I cannot either find a correct solution. Our time dimension has two hierachies, Year-Month-Day and Year-Week-Weekday.
A single fact data row is in fact the "previous" period of two diferent future days, depending on the hierarchie.

1/1/2015 is "previous" period for 1/1/2016 on Year-Month-Day ... but
1/1/2015 is "previous" period for 7/1/2016 on Year-Week-Weekday (4th day of 1st week)

So I would need three diferent cubes plus a Virtual! And define [Measures].[PrevSales] with some kind of syntax to pick up data from one cube or the other according to the selected Hierarchie. Unsure if it can be done.

___________

Cannot find the way to use Chris Webb suggestion, Source Column only admits selecting an existing column, no way to enter '0' as its value. No way to find either Calculation Subcube and Calculated Value.
I'm using MS SQL SAS Version 11.0.5058.0
Where should I look?

___________

Any comment will be appreciated, this is killing me! :)

Thks