Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Grouping the same transaction type in multiple ways Expand / Collapse
Author
Message
Posted Tuesday, February 18, 2014 10:31 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 6:47 AM
Points: 319, Visits: 783
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?


Post #1542670
Posted Friday, February 28, 2014 2:08 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 28, 2014 7:12 PM
Points: 2,148, Visits: 487
Are you trying to create a conditional sum?
Post #1546573
Posted Sunday, March 2, 2014 7:24 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 12:30 AM
Points: 2,982, Visits: 2,584
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.



Post #1546759
Posted Monday, March 3, 2014 11:56 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 28, 2014 7:12 PM
Points: 2,148, Visits: 487
Agreed (if that is what the question represents--a rollup of a chart of accounts).
Post #1547050
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse