February 18, 2014 at 10:31 am
Hello all,
Could you suggest the best way to structure the following, which I am sure is a common situation.
Ok so I have a monetary value (fact) posted on a day (dim) against an account (dim). This account has a grouping level associated with it e.g. (Revenue, Cost of Sales, Supervision Costs, Overheads).
I need to be able to group these transactions by a derived type, so:
Revenue & Cost of sales = 'Gross Profit Before Supervision'
Revenue, Cost of sales, Supervision Costs = 'Gross Profit'
Revenue, Cost of sales, Supervision Costs, Overheads = 'EBIT'
So my problem comes about as the figure that gets posted against and account will only appear once in the fact table under say Cost of Sales. But then I need to include this figure in all 3 of the transaction types dervied above.
Any ideas on how best to design for this?
February 28, 2014 at 2:08 pm
Are you trying to create a conditional sum?
March 2, 2014 at 7:24 pm
aaa121 (2/18/2014)
Hello all,Could you suggest the best way to structure the following, which I am sure is a common situation.
Ok so I have a monetary value (fact) posted on a day (dim) against an account (dim). This account has a grouping level associated with it e.g. (Revenue, Cost of Sales, Supervision Costs, Overheads).
I need to be able to group these transactions by a derived type, so:
Revenue & Cost of sales = 'Gross Profit Before Supervision'
Revenue, Cost of sales, Supervision Costs = 'Gross Profit'
Revenue, Cost of sales, Supervision Costs, Overheads = 'EBIT'
So my problem comes about as the figure that gets posted against and account will only appear once in the fact table under say Cost of Sales. But then I need to include this figure in all 3 of the transaction types dervied above.
Any ideas on how best to design for this?
This looks like the normal way I would expect a chart of accounts to role up.
It is common (in accounting systems) for the values to be entered as positive numbers and for the reporting software to know that (for example) revenue accounts add as positive numbers and expense accounts get subtracted from the total.
Analysis Services eats this sort of things for breakfast. If you can define your chart of accounts with an account type SSAS will do a lot of the work for you. Alternatively, you can define a rollup operator (e.g. + for the values for this account are added to the total and "-" for the value for this account are subtracted from the total).
Have a look at "Account Intelligence" and/or rollup operator in your favourite search engine.
March 3, 2014 at 11:56 am
Agreed (if that is what the question represents--a rollup of a chart of accounts).
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply