Hi,
I am trying to use aggregate tables in oracle.
I have created a cube with product_id,customer_id,promotion_id,store_id as dimensions and store_sales,unit_sales,store_cost as measures using sales_fact_1997 in foodmart database.
Cube is created and displayed correctly.
Now i created a aggregate table agg_month_sales_fact_1997 as:
create table agg_month_sales_fact_1997(product_id number,customer_id number,promotion_id number,store_id number,store_sales number,unit_sales number,store_cost number,fact_count number);

insert into agg_month_sales_fact_1997 select product_id,customer_id,promotion_id,store_id,sum(store_sales),sum(unit_sales),sum(store_cost),count(*) from sales_fact_1997 group by product_id,customer_id,promotion_id,store_id;

Now I have added <Aggnmae> in my xml:

<AggName name="agg_month_sales_fact_1997">
<AggFactCount column="fact_count"/>
<AggForeignKey factColumn="product_id" aggColumn="PRODUCT_ID" />
<AggForeignKey factColumn="customer_id" aggColumn="CUSTOMER_ID" />
<AggForeignKey factColumn="promotion_id" aggColumn="PROMOTION_ID" />
<AggForeignKey factColumn="store_id" aggColumn="STORE_ID" />

<AggMeasure name="[Measures].[Store_Sales]" column="STORE_SALES" />
<AggMeasure name="[Measures].[Unit_Sales]" column="UNIT_SALES" />
<AggMeasure name="[Measures].[Store_Cost]" column="STORE_COST" />
</AggName>
<!--<AggPattern pattern="agg_month_sales_fact_.*">
</AggPattern>-->

But all the queries are fired on sales_fact_1997.The aggregate table is not used.
I upadted the mondrian.properties to use and read aggregate tables.Then there is a problem in loading/reloading aggregates.
For agg_month_sales_fact_1997 it is saying no measures and no fact count column.

What is the solution for this?How can we use aggregates?


Thanks
Sreelatha