We have two tables Loans (Parent) & LoanIndicators (Child).

LoanIndicators stores the Loan status (ex Outstanding Amount) date wise.
This table is updated with a new record only when the Outstanding Amount
Changes , until that time the last record holds the status.

Ex :
Loan Table
Id LoanId LoanAmount
1 L1 1000
2 L2 2000


LoanIndicator Table

Id LoanId IndicatorDate Outstanding Amount
1 L1 1/1/2005 100
2 L1 1/13/2005 90

So in the above case if i want to know the Outstanding amount on 1/10/2005
of L1 i.e the record dated 1/1/2005 gives me the outstanding amount of 100.

To Create a Cube on this I create a View linking Loan & LoanIndicators i.e
the view holds all the Parent + Child Records of each of the Loans.
Also the cube has a dimension on IndicatorDate.

Now in the Cube the Total AMount of Loan disbursed is 4000 but it shouold be
3000.
It is 4000 because it is summing L1 twice as the view has two records for L1.
1) How do we prevent this summing of L1 twice ?

2) If I want to know the Outstanding Amount on 1/10/2005 for L1 , how do i
get it (It should be 100 as the position only changes on 1/13/2005).

TIA

Charu Gupta

Hi

To prevent the Loan's measure (Amount disbursed) from being summed
twice, you'll have to separate the data into two cubes - e.g. Loans and
LoanIndicators - and then combine them into a virtual cube. Make sure
the LoanIndicators cube has all the Loan cube's dimensions (only the
LoanIndicator cube should have dimension IndicatorDate).

On your second question: I think you'd need to use a MDX query to get
this result - as you might see from my two recent posts I'm very new to
MDX; but hopefully some of the other people here can help you.

cheers

Seb

Hi,

Tried creating the virtual cube as you have suggested, but the problem is
that now if on a particular indicator date I want to see the loan account
status, the data from Loans cube goes off because it does not find the
records of that indicator date in Loans table.

Thanks
Charu

"sebt" wrote:

Try creating a calculated measure in your virtual cube using the
ValidMeasure() function and using that instead of the real Loan amount
measure.

from BOL:
dimensions to their top level.
I think you would set up the measure something like the following:

ValidMeasure(Measures.LoanAmount)

HTH

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <B87A66A3-3955-405A-92E0-DE07F3ECB574 (AT) microsoft (DOT) com>,
CharuGupta (AT) discussions (DOT) microsoft.com says...