February 9, 2006 at 8:13 am
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
Shuchi Agarwal
February 9, 2006 at 9:17 am
Have you tried creating a dimension based on the the Loan ID?
Don't know for sure if it will work, but it may be best just to re-structure the view so you can do what you want with it. I've worked with some peculiar DBs before, and I've found one of the best ways to resolve issues is to make sure the views present the data the way you want to handle it in MSAS...
without misrepresenting the data, of course.
February 9, 2006 at 4:03 pm
You may want to check out Kimballs Data Warehouse Lifecycle (toolkit) books. It sounds like you're wanting to produce a 'stock on hand' type model, where your measures are semi-additive (ie the balances shouldn't add up over time, they're snapshots).
You can deploy these in AS, I've seen them done in two ways (there could be others??), one where they hold a record for every (say) day for every product for every store, this does get big but is slightly easier to implement in the cube. The other is where they store only the delta records, in this case the cube implementation was slightly more complex in that they 'dragged' the measures forward, so had calculated members for the emasures that allocated the last value to all future dates. This also checked for a new measure values, so for where there were txns on the 1/1 and then 5/1 and then 15/1, the value for the 1/1 would be displayed until the 5/1 which would then have the new value, again dragged forward to the 15th which then supplied the latest value.
Steve.
February 10, 2006 at 4:36 am
Hi Steve,
Can you please let me know how can we actually apply the second method i.e drag the measures that you have suggested. That is exactly what we want to do.
Thanks
Shuchi
March 5, 2006 at 6:13 am
I received the following back from the guy who implemented this:
"They entered a balance for each stock item as the first entry into the DW eg. beginning of a financial yr. then we recorded every stock movement, up or down. the cube then used the ytd function or periodtodate to calc the stock balance.
Each new year requires the balance of stock to be entered in some way.
this worked well and gave fast response time."
HTH,
Steve.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply