Purchase Order Credit Limit and Total of Purchase Lines

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

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

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

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

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