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
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.
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
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..
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
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
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
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
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
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?
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.
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.
Quote from: bus_pass_man on 28 May 2008 09:47:42 PM
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
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