Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Purchase Order Credit Limit and Total of Purchase Lines

2 posters

Go down

Purchase Order Credit Limit and Total of Purchase Lines Empty Purchase Order Credit Limit and Total of Purchase Lines

Post  PugMaster 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

Back to top Go down

Purchase Order Credit Limit and Total of Purchase Lines Empty Re: Purchase Order Credit Limit and Total of Purchase Lines

Post  ngalemmo Thu Oct 10, 2013 9:17 am

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

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

http://aginity.com

Back to top Go down

Purchase Order Credit Limit and Total of Purchase Lines Empty Re: Purchase Order Credit Limit and Total of Purchase Lines

Post  PugMaster 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

Back to top Go down

Purchase Order Credit Limit and Total of Purchase Lines Empty Re: Purchase Order Credit Limit and Total of Purchase Lines

Post  ngalemmo Thu Oct 10, 2013 2:40 pm

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

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

http://aginity.com

Back to top Go down

Purchase Order Credit Limit and Total of Purchase Lines Empty Re: Purchase Order Credit Limit and Total of Purchase Lines

Post  PugMaster 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

Back to top Go down

Purchase Order Credit Limit and Total of Purchase Lines Empty Re: Purchase Order Credit Limit and Total of Purchase Lines

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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