Aggr Function?

[darrellbutler]

Hi, I have a fact table containing invoices. Each invoice has an invoice ID, which is then linked to a dimension table on Invoice ID. My dimension table includes site (an invoice can be split across many sites) .

When I design a straight table with site as the dimension and the sum of invoices I get the grand total against each site, when in fact I need the sites total the sum of which should equal the grand total.

Any advice greatly appreciated.

rgds

[derekjones]

Hi FirstMilk

Bit confused, if your 'site' is in the dimension table, and there are multiple sites to an invoice, if the invoice table contains the invoice value but no recogonition of what site that particular invoice value is related to, it will be impossible to sum invoice value on site.

If you can provide a simple example of you data structure, I may understand better what you are trying to achieve.

Thanks

Derek

[darrellbutler]

Derek your analysis is spot on, I thought by linking on invoice_id it would understand the relationship between amount and site.

Perhaps I should bring site into the fact table ?

[derekjones]

Yes, I'm afraid so, there's no way to split up invoice values by site if you cannot identify the site of an invoice in the fact table.

Good luck.

Derek