Dear All,

I have a Period dimension with this structure:

Period (dimension)
[-] Time (Hierarchy)
[+] Year
[+] Semester
[+] Quarter
[+] Month
[+] Week
[+] Day


I have an Invoice_Fact with this structure:

invoice_date
cust_id
product_id
due_date
amount_per_product

I relate Invoice_Fact with Period dimension by invoice_date and the key
of Day level that is full_date.

When the user choose a specific date in Period dimension, I want to
calculate the due days of the invoice. To achieve that I have to get
access to invoice due_date. But the problem is, I do not know how to do
that. Would you all please give me some advice?

The purpose why I need to calculate the due days is because I want to
group and sum the amount of invoice by 1-15 days due, 15-60 days due,
and > 60 days due also the not due invoice.

Thanks in advance for the help.

If you're using AS 2005, you can associate the Period dimension to the
due_date as well, using role-playing:

http://msdn2.microsoft.com/en-us/lib...7(SQL.90).aspx

You can add a role-playing dimension to a cube by using the Dimension
Usage view in Cube Designer, which you can access from Business
Intelligence Development Studio. A role-playing dimension is a dimension
that is used in a cube more than one time, each time for a different
purpose. For example, you might add a Time dimension to a cube three
times to track the times that products are ordered, shipped, and
received. Each role-playing dimension is joined to a fact table on a
different foreign key.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Hi Deepak,

I have tried to add the role playing dimension but I do not know how to
get the due date field so I can calculate the due days from the
selectec time period (the real one) that get selected by the user, I am
still a newbie in this business.


Thanks in advance.

Since the sample Adventure Works cube already has Date in multiple
roles, here's a query to illustrate how to sum the Sales Amount due to
be delivered 1-15 days following the Order Date (which is Jan 16, 2002
for illustration):

Sum({LinkMember([Date].[Calendar].CurrentMember,
[Delivery Date].[Calendar]).Lead(1)
:LinkMember([Date].[Calendar].CurrentMember,
[Delivery Date].[Calendar]).Lead(15)},
[Measures].[Internet Sales Amount])

select {[Measures].[Internet Sales Amount],
[Measures].[SalesDueIn15Days]}on 0,
{[Delivery Date].[Calendar].[Date].&[201]:
[Delivery Date].[Calendar].[Date].&[215]} on 1
from [Adventure Works]
where [Date].[Calendar].[Date].&[200]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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