We're defining some KPIs that include trend expressions which use the ParallelPeriod function to calculate the change in a KPI value since a past time period. For example, we calculate the change in the KPI value
When we use an MDX SELECT with a WHERE clause that specifies a time slice, the expression evaluates correctly.
However, when we use CREATE SUBCUBE to do the filter, or use BI Studio (which creates a subcube), the result is incorrect. In particular, the there's an IsEmpty check for the parallel period of the KPI value, so we're not doing bogus math when there's no data for the previous period. With the WHERE clause, that works fine, but with the subcube, the ParallelPeriod always evaluates to empty.
As a point of comparison, I tested with the Adventure Works cube, usign the "Internet Revenue" KPI, which includes a similar type of KPI trend (revenue for Current Time Member - Revenue for previous Fiscal Year). I see the same behavior there. This:
select
{KPIValue("Internet Revenue"), KPIGoal("Internet Revenue"), KPIStatus("Internet Revenue"), KPITrend("Internet Revenue")}
on columns
from
[Adventure Works]
where {[Date].[Fiscal Year].&[2004]}
returns 1 for the trend, but this:
CREATE SUBCUBE [Adventure Works] AS ( SELECT ( { [Date].[Fiscal Year].&[2004] } ) ON COLUMNS FROM [Adventure Works])
select
{KPIValue("Internet Revenue"), KPIGoal("Internet Revenue"), KPIStatus("Internet Revenue"), KPITrend("Internet Revenue")}
on columns
from
[Adventure Works]
returns 0.
So after lots of reading, I THOUGHT that SP2 fixed this problem. However, we installed the December CTP of SP2, and it doesn't seem to.Since subselects and CREATE SUBCUBE do not set current coordinate on non-aggregatable attributes, functions which look at them (such as ParallelPeriod, PrevMember, Lag, Parent, LastChild etc) won't apply. There were no change in SP2 in this respect. You can still use WHERE clause to set the current coordinate though.|||Ouch. I'm not sure how I came to believe this was being fixed in SP2.
Does Microsoft consider this a bug? Design limitation? Any plans to address it at some point?
Since we have no control over how query tools generate their MDX, it seems difficult to design a cube that uses these functions and have confidence that it will work correctly for our customers.|||
This is not a bug, but rather a design decision. Perhaps it can be reconsidered in the future versions. I don't know which tool you use, but at least Excel 2007 wihch makes extensive use of subselects, does use WHERE clause when the slice is a single member (and ParallelPeriod won't work on multi-select anyways).
HTH,
Mosha.
|||I was quite concerned about Excel 2007, as I saw lots of mentions that Excel uses subselects and subcubes out on the web. However, I did some quick testing, and as you say, it does seem to use the where clause in this case. That makes me feel a little better. Our QA people have mostly been testing with the BI Studio KPI browser so far, which exhibits the problem. The other client we need to test is Sharepoint 2007.
Can you share any insight about the rationale for SSAS work this way? In my Google I've seen numerous other people discussing this as a problem, so I hope MS seriously considers addressing it in a future release. We're trying very hard to stay query-tool agnostic in our designs - this definitely makes that more difficult.
Thanks,
Kevin|||
> In my Google I've seen numerous other people discussing this as a problem, so I hope MS seriously considers addressing it in a future release.
First, I do not use Google, I use Microsoft Live Search (www.live.com) instead. Subselects were originally designed to solve different class of problems, but the actual usage proved that people wanted to use them as more functionality rich WHERE clause, although we have tried to separate the functionalities of subselects and WHERE. This obviously didn't work out well, therefore SP2 brought many changes that made subselects and WHERE to be closer. It is correct to assume, that Microsoft seriously considers addressing this issue in the future release.
|||>First, I do not use Google, I use Microsoft Live Search
Naturally. But MS Live Search is so much harder to turn into a verb. ;)
>Microsoft seriously considers addressing this issue in the future release.
When you say "future release" - is this something that could theoretically be addressed in a service pack, were there sufficient demand? Or is it too significant a change for that, and we should expect to have to wait for the next full version of SSAS for a fix?
|||We never discuss the future plans or features that may be added in the future. All I said was that Microsoft is aware of the issue and seriously considers it. When and whether something will be done about it - is up to the product team, and will be disclosed through appropriate channels.|||
Thanks for the posting. I have been testing using both BIDS and the MOSS KPI webpart. For KPI's, BIDS implements an explicit CREATE SUBCUBE for slicing while Sharepoint 2007 seems to use an in-line subselect in the FROM clause to apply the selection from the filter webpart. In both cases a KPI based on a non-calculated measure works fine while any calculated measure based on a ParallelPeriod returns a null. Given the behavior in SP2 is there any available method for defining trend-type KPI's that depend on ParallelPeriod (or similar) functionality? Many of our KPI's depend on calculating the % change from the prior period.
Thanks
John
|||BIDS KPI browser is not end-user client tool, and therefore can be safely ignored here. As far MOSS KPI webpart, I suggest you report this issue to Sharepoint team, and ask them to use WHERE clause instead of subselect (or in addition to subselect if they choose so).|||
I have posted a question on the Sharepoint forum to ask about the KPI webpart.
As for BIDS, there's a point I don't understand. The profiler shows BIDS issuing a CREATE SUBCUBE statement as the slicer before the KPI query is excuted. We're using SQL Server 2005 SP2 and the updated BOL from December contains the following text in the description of the CREATE SUBCUBE statement:
"Also, explicit references to cells outside the subcube return cell values that are evaluated in the context of the whole cube. For example, you create a subcube that is limited to the current year. You then use the ParallelPeriod function to compare the current year to the previous year. The difference in values will be returned even though the previous year’s value lies outside the subcube."
Is this statement correct? Should a ParallelPeriod that defines a member outside the current context as specified by a CREATE SUBCUBE still return a value?
Thanks
|||Yes the statement in BOL is correct. The issue here is that subselect doesn't change current coordinate, so you are looking at ParallelPeriod of All member, which is NULL.
Jeffrey Wang wrote: