3 Replies Latest reply: Sep 5, 2012 11:18 AM by Nasar-Oracle RSS

    How to calculate count of records

      I have a fact table with following columns:


      There are multiple lines for each invoice.

      I have created a AWM schema where I have a product dimension and Date dimension. Date dimension has year, month and day as one hierarcy.

      I have created a cube on this table with dimensions of date & product.

      The cube shows a summary by product as well as by date dimension. The last level of summary is based on a day. I would like to count the no of records which sum up in a day.

      For example, I need the count column in the following result set in the cube:

      01-jan-2010---Lays Chips-----20000------15000---20

      here count is the no of records which sum up to make 20000 value sales.

      what formula in AWM I have to use for this calculated measure.

      I am using AWM
        • 1. Re: How to calculate count of records
          Stuart Bunby-Oracle
          Hi there,

          Have you considered using a stored measure to achieve this?

          For example, in the fact table which contains each of the individual records, you could create a new field called rec_count and assign it a value of 1 for every row. You can then map this new field as a stored measure and load/aggregate it just like the sales and cost stored measures. If you are unable to amend the source table then it is possible to achieve the same using a view instead.

          While you are loading data at day level on your date dimension, the OLAP engine will always aggregate these records during the load process. Therefore, I can't see how it will be possible to make this work with a calculated measure because the required level of granularity will be lost.

          I hope this makes sense


          Stuart Bunby

          OLAP Blog: http://oracleOLAP.blogspot.com
          OLAP Wiki: http://wiki.oracle.com/page/Oracle+OLAP+Option
          OLAP on OTN: http://www.oracle.com/technology/products/bi/olap/index.html
          DW on OTN : http://www.oracle.com/technology/products/bi/db/11g/index.html
          • 2. Re: How to calculate count of records
            I have the same problem, all i want is a simple count. I would have expected to see it as an option on the Measures->aggregation option like the AVG and SUM.
            There must be a simple way of doing this!