This content has been marked as final. Show 3 replies
Have used the poor workaround of adding a column to a view on the table with it set to 1. You can them sum the new column in AWM.
Everywhere i need a count i will have to introduce this column. There must be a better way.
Cant get the AVG working or i could have done SUM(AMOUNT)/AVG(AMOUNT)=COUNT
OLAP does not make this as easy as it should be, but COUNT can be calculated along with another aggregation operator, like SUM.
When you create a cube using AWM and specify SUM as the cube aggregation, you should see something like this in the associated cube XML.
Alternatively you can see the specification in the USER_CUBES view.
<ConsistentSolve> <![CDATA[SOLVE ( SUM MAINTAIN COUNT OVER ALL )]]> </ConsistentSolve>
If you see the MAINTAIN COUNT keywords, then this says that a COUNT has been calculated alongside the SUM. If it isn't there, you can add it to the XML (as shown above) and recreate the cube. You can see the effect of the keywords in the aggmap generated for the cube. Note the "COUNT YES" statements in the following example.
select consistent_solve_spec from user_cubes where cube_name = '...'
If you have this in place, then you can get the value of COUNT using the AGGCOUNT function in OLAP DML. To add a "count of sales" measure to the UNITS_CUBE in global, for example, I created a calculated measure with this expression.
DEFINE UNITS_CUBE_SOLVE_AGGMAP AGGMAP LOCKDFN AGGMAP RELATION this_aw!TIME_AGGRREL(this_aw!UNITS_CUBE_SOLVE_TIME_HVSET) PRECOMPUTE(this_aw!UNITS_CUBE_SOLVE_TIME_PVSET) OPERATOR SUM args DIVIDEBYZERO YES DECIMALOVERFLOW YES NASKIP YES COUNT YES RELATION this_aw!CUSTOMER_AGGRREL(this_aw!UNITS_CUBE_SOLVE_CUSTOMER_HVSET) OPERATOR SUM args DIVIDEBYZERO YES DECIMALOVERFLOW YES NASKIP YES COUNT YES RELATION this_aw!PRODUCT_AGGRREL(this_aw!UNITS_CUBE_SOLVE_PRODUCT_HVSET) OPERATOR SUM args DIVIDEBYZERO YES DECIMALOVERFLOW YES NASKIP YES COUNT YES RELATION this_aw!CHANNEL_AGGRREL(this_aw!UNITS_CUBE_SOLVE_CHANNEL_HVSET) OPERATOR SUM args DIVIDEBYZERO YES DECIMALOVERFLOW YES NASKIP YES COUNT YES AGGINDEX NO CACHE SESSION END
QUAL(AGGCOUNT(GLOBAL.GLOBAL!UNITS_CUBE_STORED) GLOBAL.GLOBAL!UNITS_CUBE_MEASURE_DIM 'SALES')
Just noticed your reply; wIll give it a try and report back :)