Counters in ODS
We all know the ODS has a generic record count that can be used – but then the record counter cannot be used in all cases – especially when you want to apply restrictions / exception aggregation etc..
It is nice to have a counter set to value one when loading the data
In case of overwriting mode – you can use this as a record counter and also use it for exception aggregation in the data models and reporting
If you have additive mode – then this value can then be used to calculate averages if required and also help understand the level of aggregation
Counters in cubes
Counters in cubes are tricky – because you will have to make sure you handle the reversals when the values change – for instance if you have a hard counter of value 1 then when you have multiple records with the plus and minus in the cube then the counters will become additive.
Also if you have a hard counter then compression with zero elimination will not happen in the cube leading to blank values in the queries which should have ideally been deleted.
Possible uses :
- Count the number of records
- Count the number of unique occurrences ( count of all values <> 0 with exception aggregation in the query )
- Use the counter in a formula to determine average if required – This can be done using the key figure settings but then the counter option gives you another way to achieve the same
Always use caution when using counters in Cubes or DSO with additive Key figures to make sure that record deletion is handled properly when you are reversing records using the Key figure values or using recordmode
These are some of the ways I thought counters would be used and why counters need to be used in all data models. Do you have anymore uses of counters or think they should not be used at all ..?