Semi-Additive Facts

[arickard]

Anyone have suggestions for dealing with semi-additive facts in framework manager.

I've got a sales query subject in which the grain is one row per line item. There is a fact in this table that represents the local net revenue. This fact is considered semi-additive since it can only be aggregated with the context its local currency. The query subject also has attribute to represent the local currency.

I can't simply set the regular-aggregate to Sum since it would give incorrect results. Any suggestion?

-Andrew
http://metatheory.wordpress.com

[blom0344]

Many source systems have a reservation for storing a conversion rate to one of the principal currencies (euros/dollars).
The real problem lies when you need to take account of the historic change of the conversion rate. You either need some sort of slowly changing dimension with begin/end date of a certain rate or a source that stores the rate with the fact.

Obviously (net value * conversion rate) gives a fully additive fact you can work with.

[arickard]

Thanks for the response. It turns out this query subject also happens to have the standard net revenue which is additive. I'm just not sure what to do with the local net revenue which is semi-additive.

-Andrew
http://metatheory.wordpress.com

[blom0344]

Look into the source definition of the database objects that constitute the model query subject. Big chance that you will find the conversion rate you need..

[arickard]

Perhaps it would be clearer if I didn't use the currency example. Say I have a fact that is semi-additive. Semi-additive facts are facts that can be summed up for some of the dimensions in the fact table, but not the others.

What does one do in framework manager for these facts?

-Andrew
http://metatheory.wordpress.com

[blom0344]

From my own experience in retail:

Consider a company that stocks, purchases and sells articles.
For articles the sales (quantity&revenue) are additive facts. Stock is semi-additive. I can take a snapshot each month of stock-levels, but these quantities can only be aggregated over the individual stores of the stocking department.
Ditto for purchases. The outstanding purchase-orders can be taken as a snapshot , but not accumulated over time.

So, both stock and outstanding purchase orders have their own snapshot fact table.
The common dimension would be article / article-group or any higher hierarchy.

A multifact query would be a time-range on sales (year = 2008), while the most recent snapshot (200805 , as May 2008) would fetch the latest inventory/purchase data

[arickard]

So using your example how would you set your 'stock' fact in framework manager?

What would you have its regular aggregate configured as? It couldn't be set to Sum since you can't have someone aggregating across certain dimensions like store. Would you set it to Calculated? What about Unsupported?

-Andrew
http://metatheory.wordpress.com

[blom0344]

The stock facts (in my case for stores and articles) are additive for stores / articles , but not against time dimension:

period     store      article      quantity

200801    export      acb        500

200801    export      def        200

200802    export      acb        400

200802    export      def        240

200801    domestic   acb        100

200803    export      acb        60

200804    domestic   def        80

200804    export      acb        120

200803    domestic   acb        300

So , reporting on sales for the year 2008 on 1st of april would require aggregating sales data for 1-1-2008 till 31-3-2008 and selecting the snapshot on stock of 200803 (assuming we freeze the stock levels at the end of march as 200803)

This type of stock storage would make it possible to report on snapshots in time.
So we can report on YTD (year-to-date) sales 2007/2008 and show the stock levels for 200703/200803 as well

[rockytopmark]

I think the use of Determinants solves this.

So your Time dimension would have determinants setup, to handle the different levels of grain in that table (Year, Quarter, Month, Date ...for example), and your Facts, are related to that Time dimension based on their low level grain with respect to the Time dimension.

The Determinants will handle the aggregation (or lack thereof)

Interested to hear if you have cross this path yet

[almeids]

If I might interject...
I think arickard is asking what are the settings, in FM, to model such measures so that when querying via the Studios an end-of-period rollup is applied as can be specified for measures in Transformer/PowerPlay (so that user does not have to be aware of such special consideration in the time dimension).
I assume this is possible with DMR in C8?

[rockytopmark]

If the report is for one Month, Stock will want to be additive.  If for a year, then Stock becomes more of an attribute, and the Determinants in the Time dimension should handle this.

[bus_pass_man]

Set the aggregate rule for the measure for those dimensions that you want it to be semi-additive.   It also will allow you to define the semi-additive roll-up.   

[rockytopmark]

Set the aggregate rule for the measure for those dimensions that you want it to be semi-additive.   It also will allow you to define the semi-additive roll-up.   

Can you explain what this means and/or how this is done... by dimension?!?!  I'm very curious.

Thanks

[arickard]

Thanks almeids! Thats exactly what I would like to know. How do you go about modeling this type of semi-additive fact in framework manager so that a studio user doesn't drag the a semi-additive fact into the work area and think the result is is correct. I don't want the query studio user thinking that if they drag the local net revenue fact into the work area resulting in $1,000,000 that they can assume they just made 1 million dollars when in fact the only made USD 100,000 and EUR 200,000 and CAD 400,000