3 Replies Latest reply: Nov 8, 2012 4:37 AM by 959628 RSS

    Analytic workspace manager - simple count?

    959628
      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
          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
            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
              Just noticed your reply; wIll give it a try and report back :)