Purchase Order Credit Limit and Total of Purchase Lines

View previous topic View next topic Go down

Purchase Order Credit Limit and Total of Purchase Lines

Post  PugMaster on Thu Oct 10, 2013 3:42 am

Hi

I have a Purchase Order fact table with the granularity set to the purchase order line so there may be many lines in the fact table with the same identifier of "PO Number", there is also a "CreditLimit" column which logically would be set against the whole purchase order not just the line but because of the granularity it is repeated on each purchase order line, what I need to do is total up the "LineTotals" column and check it is not greater then the "CreditLimit" if it is that raises an issue.

Is there a way of doing this with my current structure or do I need to create a purchase order header fact table or a header dimension with the calculation already done in the ETL? I am using SSAS 2008 R2.

Any help much appreciated.

Kind Regards
paul

PugMaster

Posts : 21
Join date : 2010-07-07

View user profile

Back to top Go down

Re: Purchase Order Credit Limit and Total of Purchase Lines

Post  ngalemmo on Thu Oct 10, 2013 9:17 am

You could compare the sum of the line amounts with the average of the credit limit.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Purchase Order Credit Limit and Total of Purchase Lines

Post  PugMaster on Thu Oct 10, 2013 12:40 pm

Hi ngalemmo

Many thanks for your reply, so are you suggesting I do that in the ETL then adding a flag to the Purchase Order dimension. Or is there a way of doing this in the Cube using a calculation or KPI (sorry my MDX is not good, still stuck thinking SQL) and if so how would I limit it to showing only at the individual PO level.

Kind Regards
Paul


PugMaster

Posts : 21
Join date : 2010-07-07

View user profile

Back to top Go down

Re: Purchase Order Credit Limit and Total of Purchase Lines

Post  ngalemmo on Thu Oct 10, 2013 2:40 pm

You could create a view or a header level aggregate fact.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Purchase Order Credit Limit and Total of Purchase Lines

Post  PugMaster on Fri Oct 11, 2013 3:02 pm

Hi

I have built header level aggregate fact table (view) within the dsv and it is identified as a fact table in the cube but How do define the relationship in the cube so it shows correctly as I drill down through, currently it only shows the very top level figures which is were it means the least.

Currently I have it defined as regular because that is the only option i seem to have available.

Kind Regards
Paul

PugMaster

Posts : 21
Join date : 2010-07-07

View user profile

Back to top Go down

Re: Purchase Order Credit Limit and Total of Purchase Lines

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum