The example in this section shows how you would add a new dimension, specifically a shipping method dimension. Having added this, you could then create reports that show sales by shipping method.
Note: Take care when designing new dimensions. One potential pitfall lies in the reduction of performance that can result if your queries join your new dimension to existing, large dimensions such as the User dimension. Always consider whether you could extend an existing dimension instead, or otherwise streamline your data gathering process.
To add a new dimension:
Create a table that represents this dimension in the data warehouse. In this case, we’ll call it
ACME_SHIPPING_METHOD. For purposes of this example, the table is very simple, consisting only of an identifier, the shipping method name, the estimated number of days for delivery, and the carrier.
ACME_SHIPPING_METHODIDNAME—Name of shipping method, for instance, FedEx 3 day GroundDELIVERY_COMMENT—For instance Same Day, 1-2 Business DaysCARRIER—Name of carrier, for instance, FedEx, UPS
Prepopulate the table with your data. This can be done many ways, including executing INSERT statements for static data or pipeline processors for more dynamic information. Since this table is your custom creation, you are responsible for its maintenance. For this example, since shipping methods are unlikely to change often, prepopulating it via INSERT statement would probably be used.
Modify the
ARF_LINE_ITEMtable to add aSHIPPING_METHOD_IDcolumn, which is a foreign key reference to theACME_SHIPPING_METHODtable’sIDcolumn.Extend the
/atg/reporting/datawarehouse/dataWarehouseRepository.xmlfile to add aShippingMethoditem:<item-descriptor name="ShippingMethod">
<table name="ACME_SHIPPING_METHOD" id-column-names="id" type="primary">
<property name="carrier" column-name="CARRIER" type="string"/>
<property name="deliveryComment" column-name="DELIVERY_COMMENT"
type="string"/>
<property name="name" column-name="NAME" type="string"/>
</table>
</item-descriptor>Modify the line item (you can use XML-combine to make this task easier; for information, see the ATG Repository Guide).
<item-descriptor name="lineItem">
<table name="ARF_LINE_ITEM">
<property name="shippingMethodID" column-name="SHIPPING_METHOD_ID"
type="int"/>
</table>For this example, the ID is of type
int. If necessary, you can create a new item type instead.Create a new pipeline processor in
/atg/reporting/datawarehouse/process/ calledShippingMethodLookupProcessor. The new processor must do the following:Look up the shipping method ID. In this case, the pipeline should look up the shipping method based on some property in the order.
At the end of the processor, set the output to the member value. In this case, set
lineItem.shippingMethodIDto the member value as shown in the following code:runProcess(Object pParam, PipelineResult pResult) {
// perform shipping method id lookup
Dynamic.Beans.setPropertyValue(pParam, "lineItem.shippingMethodId",
value)
}
You may want to review the information on existing pipeline processors in this guide. The exact features of your pipeline will vary depending on what information you are retrieving and what, if any, transformations you want to make to the data.
Modify the pipeline chain to include your new lookup processor. Add the following line to the file:
<pipelinelink name-"shippingMethodLookup" transaction="TX_MANDATORY">
<processor jndi="/atg/reporting/datawarehouse/process
/ShippingMethodLookupProcessor"/>
</pipelinelink>Modify the
/atg/reporting/datawarehouse/process/LineItemLoggerProcessor.propertiesfile:lookupProperties += \lineItem.shippingMethodIddimensionProperties += \shippingMethodIdModify the Oracle Business Intelligence data model so that it includes the new dimension data. Modify any reports that will use the new dimension data. See information about working with the Oracle Business Intelligence data model and reports in the documentation for that product (http://www.oracle.com/technetwork/middleware/bi-enterprise-edition/documentation/bi-ee-087853.html).
