I have a problem wich should be not very exotic, but I could not find a good
solution up to now.
The scenario:
Fact table contains a start date and an end date (product
installed/deinstalled at customer).
Dates are connected to 2 time dimensions.

To get installed products at a certain date is quite easy by slicing the
cube n both time dimensions accordingly.
start date dimension: min date to questioned date
end date dimension: questioned date to max date (there is a my date for
products stillinstalled).
That works fine, but it is not possible to get values over time, which is a
requirement.

To get this done, I have calculated measures:

CREATE MEMBER CURRENTCUBE.[MEASURES].
[InstalledAtStartDate Cal]
AS ([Measures].[InstalledToStartDate Cal] - [Measures].
[DeinstalledToStartDate Cal]),
SOLVE_ORDER = 1;

CREATE MEMBER CURRENTCUBE.[MEASURES].
[InstalledToStartDate Cal]
AS Sum(
PeriodsToDate([Start Date].[Calendar Year].[(All)],
[Start Date].[Calendar Year].CurrentMember),
([Measures].[Installation Facts Count], [End Date].
[Calendar Year].[All])
),;

CREATE MEMBER CURRENTCUBE.[MEASURES].
[DeinstalledToStartDate Cal]
AS Sum(
PeriodsToDate([End Date].[Calendar Year].[(All)],
Members("[End Date]." + Mid(MemberToStr([Start Date].
[Calendar Year].CurrentMember),14,200))),
([Measures].[Installation Facts Count], [Start Date].
[Calendar Year].[All]) ;

That brings the correct results, but is only good to get totals.
Running a query with customer dimension on one axis and product dimension on
other axis, performance is horrible (no wonder, I think).

Any viable solution to this would be appreciated.

Robert

If the performance problems relate to summing large sets in
PeriodsToDate(), then this earlier newsgroup thread, and entry in Chris
Webb's blog, discuss how to optimize them:

http://groups-beta.google.com/group/...rver.olap/msg/
dacc5ced4823970a
From: "Chris Webb"
Date: Fri, 31 Dec 2004 04:01:01 -0800
Subject: RE: Problem with MDX query

I don't think the query is hanging, I think it's just taking a very long
time
to complete! Summing up all those days in your date range is going to
take a
long time, plus I'll bet that your second query (because it doesn't
mention
the YearMonthDay dimension at all) probably hits aggregations whereas
your
first query doesn't.

Since this is a fairly common problem I've just made it the subject of
the
first entry of my new blog, which you can read here:
http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9I...

In your case, I think the approach of replacing the day members in your
day
range set with common ancestors could solve the problem.
...

http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!
111.entry


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Thank's for your reply, Deepak.
But I don't think the suggested solution can be applied to my problem.
My Facts are only valid from StartDate to EndDate (attributes in the fact
table,
connected to two time dimensions).
I have to know for each cell in the cube the count of valid facts for a
given date.

There should be different scenarios for this problem (employees with start
and end date, service calls with open and close date...) but I can't find any
example for this.

Robert



"Deepak Puri" wrote:

Hi,
I want to thank both of you for posting this message.
I was trying to calculate a member that would give me the year-to-date amout
and after reviewing this posting, I was able to. I had spent a day racking
my brains.

Thanks again...

By the way, do you know where I can find a list of popular calculations?

"rweiss" wrote: