This discussion is archived
3 Replies Latest reply: Sep 5, 2012 9:18 AM by Nasar RSS

How to calculate count of records

user1299751 Newbie
Currently Being Moderated
I have a fact table with following columns:

Invoice_no
Date_key
Product_key
Sales
Cost

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:

Day--------------product---------sales--------Cost------Count
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 11.2.0.1.0A
  • 1. Re: How to calculate count of records
    Stuart Bunby Explorer
    Currently Being Moderated
    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

    Thanks,

    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
    959628 Newbie
    Currently Being Moderated
    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!

Legend

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