This discussion is archived
3 Replies Latest reply: Nov 8, 2012 2:37 AM by 959628 RSS

Analytic workspace manager - simple count?

959628 Newbie
Currently Being Moderated
I must be missing something in the Analytic workspace manger as all i want is a simple count of the rows. Its a basic type of calculation.
I have a number of agreements that have a sum of the amount on a Yearly, QTR, Month, Day. I also need the number of agreements for each part of the hierarchy.

i.e. (contrived daily example)
SELECT WORKING_DATE,COUNT(AMOUNT),SUM(AMOUNT)
FROM AGREEMENTS
GROUP BY WORKING_DATE

The amounts work fine and the dimensions are working. Just cant get a COUNT working. I have seen work around hacks; but find it hard to believe that these are needed on a basic function that should be there.

Cheers
Chris
  • 1. Re: Analytic workspace manager - simple count?
    959628 Newbie
    Currently Being Moderated
    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
  • 2. Re: Analytic workspace manager - simple count?
    DavidGreenfield Expert
    Currently Being Moderated
    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.

    <ConsistentSolve>
    <![CDATA[SOLVE 
    (
      SUM
        MAINTAIN COUNT
         OVER ALL
    )]]>
    </ConsistentSolve>
    Alternatively you can see the specification in the USER_CUBES view.
    select consistent_solve_spec 
    from user_cubes
    where cube_name = '...'
    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.
    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
    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.
    QUAL(AGGCOUNT(GLOBAL.GLOBAL!UNITS_CUBE_STORED) GLOBAL.GLOBAL!UNITS_CUBE_MEASURE_DIM 'SALES')
  • 3. Re: Analytic workspace manager - simple count?
    959628 Newbie
    Currently Being Moderated
    Just noticed your reply; wIll give it a try and report back :)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points