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:
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 126.96.36.199.0A
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
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