Hi, Experts.

I have a question about calculating inventory numbers from circulation data.
Say I have the following smaple table shown at the bottom. It records the
bookid, the checkin date and the check out date of the books. I want to build
a cube that will give me 3 numbers for any given time period ( day, week,
month etc.)
Number of books checked in
Number of books checked out
Number of books in the library

The first two measures are just counts. But I'm having trouble with the 3rd
measure. I'm trying to define the measure as a calculated memebr

[Number of books check in ] - [Number of books checked out]

So far, I haven't found a way of doing this in AS2005.

Please help.

Thanks

KC
################################################## ###

Sample table


BookID Checkin Date Checkout Date
43664 07/01/01 07/08/01
43674 07/01/01 07/08/01
43684 07/01/01 07/08/01
43694 07/01/01 07/08/01
43704 07/02/01 07/09/01
43714 07/05/01 07/12/01
43724 07/07/01 07/14/01
43734 07/10/01 07/17/01
43744 07/12/01 07/19/01
43754 07/15/01 07/22/01
43764 07/17/01 07/24/01
43774 07/19/01 07/26/01
43784 07/22/01 07/29/01
43794 07/22/01 07/29/01
43804 07/25/01 08/01/01
43814 07/27/01 08/03/01
43824 07/28/01 08/04/01
43834 07/30/01 08/06/01
43844 08/01/01 08/08/01
43854 08/01/01 08/08/01
43864 08/01/01 08/08/01
43874 08/01/01 08/08/01
43884 08/01/01 08/08/01
43894 08/01/01 08/08/01
43904 08/01/01 08/08/01

KC

I am not an expert but you might try...

[Measures].[Number of books check in ] - [Measures].[Number of books
checked out]

I am not sure exactly where you are designing your calculated measure.
I design mine on the calculations tab of the "cube designer" in the SQL
Server BI Development Studio.

Willy


KC wrote:

The problem is that I can not put the two measures at the same place. I have
two time dimensions, ChecckInDate and CheckOutDate, related to the
CheckinDate and ChekoutDate respectively. The measure is count of the books.
To get the [number of books checked out], I use the CheckoutDate dimension;
to get the [number of books checked in], I use the CheckInDate dimension.
But, I can not put the two measures at same place because they use diferent
time dimensions, let alone getting the difference between the two.



"willy" wrote:

You probably should consolidate your two dates. I don't see the need to
have both of them, since your measure names will dictate the meaning of dates.

"KC" wrote:

Not sure waht do you mean by "consolidate the two dates". I have one time
dimension that has two relationships to the fact table. One relates to the
checkin date, the otherr relates to the checkout date. To get the number of
checkedin books, I need to use the checkin date. Likewise, I use the
checkout date to ge the number of books checked out. I'm trying to get the
inventory be substracting the number of checkout books from the number of
chckedin books, but I couldn't find a way put the two numbers side by side.
I was able to do this with AS2000 by createing two cubes, one for checkin
and one for chekout, and combime with with a virtual cube.




"yongli" wrote:

KC:

I think i would try to get something like this

Date CountofCheckIn CountofCheckOut
1/2/2006 35 14
1/3/2006 23 56

Something like this fact table should work. You want your aggregation
to be count...

if you had a fact table

date checkin checkout

1/2/2006 58766 null
1/2/2006 null 78564
1/2/2006 null 78654
1/2/2006 64758 null
1/3/2006 98763 null
1/3/2006 63425 null
1/3/2006 null 98765
1/3/2006 47565 null

Then you would only need one dimension for date

and you could have a calculated member difference =
[measures].[checkin] - [measures].[checkout]

There is a better way based on this...but I don't have the time to
figure it out...

1/2/2006 56789 In
1/2/2006 87465 Out
1/3/2006 98475 Out


Willy

KC wrote:

Assume that there is a count measure on the fact table like [BookCount],
and there is a [Date] dimension in 2 roles - [CheckinDate] and
[CheckoutDate]. For end-user selection, [Date] dimension can be added in
a 3rd role like [ReportDate], without any relationship to the fact
table. Then calculated measures can be created like:

[Measures].[BooksCheckedIn]:
Sum(PeriodsToDate([CheckinDate].[Calendar].[(All)],
LinkMember([ReportDate].[Calendar].CurrentMember,
[CheckinDate].[Calendar]),
[Measures].[BookCount])

[Measures].[BooksCheckedOut]:
Sum(PeriodsToDate([CheckoutDate].[Calendar].[(All)],
LinkMember([ReportDate].[Calendar].CurrentMember,
[CheckoutDate].[Calendar]),
[Measures].[BookCount])

[Measures].[BooksInLibrary]:
[Measures].[BooksCheckedIn] -
[Measures].[BooksCheckedOut]


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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