Is there a way to limit data in OLAP Schema

[suppachartt]

Hi all,

I have data in PRODUCT_DIMVIEW table that look like this,
PRODUCT      PRODUCT_LEVEL      PRODUCT_LV1_DESC      PRODUCT_LV2_DESC
PT                     1                                      Total Products                       (null)
P01                   2                                      Total Products                       Product 1
P02                   2                                      Total Products                       Product 2

Here is my dimension schema,
<Dimension type="StandardDimension" foreignKey="PRODUCT" name="Product">
   <Hierarchy hasAll="true" primaryKey="PRODUCT">
      <Table name="PRODUCT_DIMVIEW" schema="ESADMIN"></Table>
      <Level name="lv1" column="PRODUCT" type="String" uniqueMembers="true"></Level>
      <Level name="lv2" column="PRODUCT" type="String" uniqueMembers="false"></Level>
   </Hierarchy>
</Dimension>

How can I tell mondrian that in lv1 Level, I want just only product_id = PT to show up and lv2 Level has only 'P01' and 'P02' data? I can write SQL to select data by level. How can I do this in this schema file?

Cheers,
Suppachart

Post Edited by suppachartt at 08/03/2009 08:21

[swood]

So you have all the data pre-aggregated to all the levels of detail that you want. This is not what the OLAP engine expects. I would ignore the total rows altogether, and have a hierarchy like:

 

   <Hierarchy hasAll="true" primaryKey="PRODUCT">
      <Table name="PRODUCT_DIMVIEW" schema="ESADMIN"></Table>
      <Level name="Product" column="PRODUCT" type="String" uniqueMembers="true"></Level>
   </Hierarchy>

 

How does the dimension join to the facts? I can't imagine the total rows in the dimension having a foreign key to the facts.

 

Sherman

Jaspersoft

[suppachartt]

Hi Sherman,

 

Thank you for your reply, Sherman.

 

Yes, our data is pre-aggregated straight out of Oracle OLAP engine. In the fact table has the summary top level for each dimension along with all the child level data, we have several dimensions. The fact table size is massive.

 

In the fact table has PRODUCT column which contains product_id. We use this column to join with dimension PRODUCT_DIMVIEW table using PRODUCT as a key.

 

The problem is when I used the schema like the one you suggested, the mondrian will roll-up all the data from P01 to P86 including PT in the fact table which is not right as PT is the summarize level of P01 to P86, in other word, the figure I end-up with is double counting.

 

Cheers,

Suppachart

Post Edited by suppachartt at 08/05/2009 00:41

[swood]

Do you actually have facts with PRODUCT = "PT"? If so, I can see you would have a problem with double counting.

 

You could use a <View> on the dimension instead, which will be SQL to exclude all the summary only rows.

 

Sherman

Jaspersoft

[suppachartt]

Thank you, will try that.