Schema Design - 1 Dimension from 2 Tables

[Exie]

Hi Folks,

 

I have a small cube, that has a product dimesion from a single table like so:

Business Unit -> Product Group -> Product (+ Description)

... and I'm making a new product dimension which again comes from a single table like this:

Business Unit -> Sales Group -> Product

 

This works fine, but I want the product description in both dimensions, but dont want to store it twice in the database. How can I pull the attribute/property of a level from a different table ?

 

In the code below, how can I get the description from "PRODUCTGRP_JS_MV" in the other dimesnion.

 

Code:

<Dimension type="StandardDimension" name="Product Group">	<Hierarchy hasAll="true" primaryKey="PRODUCT_ID">		<Table name="PRODUCTGRP_JS_MV">		</Table>		<Level name="Product Type" column="BUNIT_ID" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">		</Level>		<Level name="Product Group" column="PRODUCT_GROUP" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never" ordinalColumn="PRODUCT_GROUP">		</Level>		<Level name="SKU" column="PRODUCT_ID" nameColumn="PRODUCT_ID" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never" ordinalColumn="PRODUCT_ID">			<Property name="Description" column="PRODUCT_DESCRIPTION" type="String">			</Property>		</Level>	</Hierarchy></Dimension><Dimension type="StandardDimension" name="Sales Group">	<Hierarchy hasAll="true" primaryKey="PRODUCT_ID">		<Table name="PRODUCTSAL_JS_MV">		</Table>		<Level name="Unit" column="BUNIT_ID" uniqueMembers="false" levelType="Regular" hideMemberIf="Never" ordinalColumn="BUNIT_ID">		</Level>		<Level name="Product Type" column="SALES_GROUP" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">		</Level>		<Level name="SKU" column="PRODUCT_ID" nameColumn="PRODUCT_ID" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never" ordinalColumn="PRODUCT_ID">		</Level>	</Hierarchy></Dimension>

[swood]

Try the dimension definition below with the Join element.

 

You can also use a View instead of the Table or Join and define a piece of custom SQL.

 

 

Sherman

Jaspersoft

Code:

<Dimension type="StandardDimension" name="Sales Group">	<Hierarchy hasAll="true" primaryKeyTable="PRODUCTSAL_JS_MV" primaryKey="PRODUCT_ID">		<Join leftKey="PRODUCT_ID" rightKey="PRODUCT_ID">		  <Table name="PRODUCTGRP_JS_MV">		  </Table>		  <Table name="PRODUCTSAL_JS_MV">		  </Table>		</Join>		<Level name="Unit" table="PRODUCTSAL_JS_MV" column="BUNIT_ID" uniqueMembers="false" levelType="Regular" hideMemberIf="Never" ordinalColumn="BUNIT_ID">		</Level>		<Level name="Product Type" table="PRODUCTSAL_JS_MV" column="SALES_GROUP" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">		</Level>		<Level name="SKU" table="PRODUCTGRP_JS_MV" column="PRODUCT_ID" nameColumn="PRODUCT_ID" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never" ordinalColumn="PRODUCT_ID">			<Property name="Description" column="PRODUCT_DESCRIPTION" type="String">			</Property>		</Level>	</Hierarchy></Dimension>