This content has been marked as final. Show 3 replies
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')