Introduction

IBM® WebSphere® Commerce Analyzer (WCA) V5.6, a component of the WebSphere Commerce family of products that ships with WebSphere Commerce Express, not only provides a predefined set of business intelligence reports, but helps process data collection from WebSphere Commerce. This article focuses on how to create new WebSphere Commerce Analyzer business intelligence reports (hereafter called WCA reports) and how to customize the pre-built reports to fit the end-users' needs using the report framework provided with the product. This article comes with a set of sample script and report files. These files have been tested with a Windows® 2000/IBM HTTP Server/DB2® configuration of WebSphere Commerce Express Edition V5.6.

Back to top

WebSphere Commerce Analyzer features

In Figure 1, WCA extracts data from a WebSphere Commerce operational database and other data source (the product cost data in this case), and then the data is integrated with the reference data to populate the predefined WCA data marts on the WCA server. The technique reference data comes with WCA and the business reference data is provided during WCA configuration. All the WCA reports is designed based on WCA data marts.

WCA reports are accessed via WebSphere Commerce Accelerator, which is a Web-based WebSphere Commerce server administration tool. These predefined WCA reports exploit the data to provide analysis of products, geographies, demographics, time period sales, customer activities campaign, and e-marketing spot effectiveness. The WebSphere Commerce reporting framework provides a programmatic solution for creating and customizing the reports.

Back to top

Sample business scenario

A WebSphere Commerce merchant wants to see the profit analysis information of the store products and customers in the WebSphere Commerce Analyzer reports. After carefully analyzing the business requirements, you decide to customize an existing report and create a new report to meet the business needs.

Assume that WebSphere Commerce Analyzer is already installed and configured to work with the target WebSphere Commerce server and the store product cost information is already integrated into WebSphere Commerce Analyzer data marts. (The UNIT_COST column is already added to the FACT_ORDERITEMS table in the WCA data mart and is populated with the product cost data.)

Back to top

Enabling WebSphere Commerce Analyzer

The default WebSphere Commerce server configuration does not enable WCA reports, which means all WCA reports are not accessible from WebSphere Commerce Accelerator. To enable WebSphere Commerce Analyzer reports:

1. From the Configuration Manager, expand the node with your server name, expand Instances, and select the instance you want to modify.
2. Select the CommerceAccelerator listing.
3. Select the check box to indicate that WebSphere Commerce Analyzer has been installed.
4. Enter the path to the Reports Document Root, or accept the default. By default, this path is <WC_Install_Root>\instances\<WC_Instance_Name>\<Report_Document_Root>, where:
• <WC_Install_Root> is the root directory of WebSphere Commerce.
• <WC_Instance_Name> is the WebSphere Commerce instance name.
• <Report_Document_Root> is the document root folder name.
5. Expand the Components node, enable the UserTrafficEventListener and CampaignRecommendataionListener components.
6. Stop and start your WebSphere Commerce Server, you will see the the WCA report list from WebSphere Commerce Accelerator Business intelligence menu.

Back to top

Modifying an existing WebSphere Commerce Analyzer report

The following procedure shows how to modify the pre-defined WCA report, biProductUnitSoldReport , (included with the product) to include gross profit information.

1. Modifying the Report definition file

The biProductUnitSoldReport.xml file is the report definition file. This file has the SQL and column definition for the report. It is in the <WC_Install_Root>\xml\tools\bi\ directory.

There are five sets of SQL queries and column definitions for five different durations in biProductUnitSoldReport.xml. You can change some or all of them. Make sure the column definition order matches the data fields of the SQL query. If you plan to use the sample biProductUnitSoldReport.xml file as-is, just replace the original one with the sample biProductUnitSoldReport.xml.

For example, to add gross profit information to the report, replace the first SQL statement in BiProductUnitsSoldReport.xml with the following:

select
	PREF.NAME AS "Product",
	SUM (FOI.NUM_UNITS) AS "Units Sold",
	SUM (FOI.RPT_SALES_VALUE) AS "Sales Value",
	SUM ((FOI.UNIT_PRICE-FOI.UNIT_COST)*FOI.NUM_UNITS + FOI.DISCOUNTS) AS "Gross Profit",
	100*(SUM ((FOI.UNIT_PRICE-FOI.UNIT_COST)*FOI.NUM_UNITS+FOI.DISCOUNTS))
	/(SUM (FOI.RPT_SALES_VALUE)) AS "Margin %",
	100*SUM ((FOI.UNIT_PRICE-FOI.UNIT_COST)*FOI.NUM_UNITS+FOI.DISCOUNTS)
	/(SELECT SUM((UNIT_PRICE-UNIT_COST)*NUM_UNITS+DISCOUNTS) 
				FROM WCA.FACT_ORDERITEMS 
				WHERE STORE_ID IN ({storeent_id})) 
                    AS "Profit Contribution %",
	FOI.REPORT_CURRENCY AS "Currency"
from
	WCA.FACT_ORDERITEMS FOI,
	WCA.PERIOD P,
	WCA.PRODUCT PRO,
	WCA.PRODUCT_REF PREF
where
	(P.PER_ID = FOI.PER_ID
	and PRO.PRODUCT_ID = FOI.PRODUCT_ID
	and PREF.PRODUCT_ID = PRO.PRODUCT_ID
	and FOI.STORE_ID IN ({storeent_id})
	and PREF.LANGUAGE_ID IN (select INTEGER(PR.PARAM_VALUE)
                                 from WCA.PARAMETERS PR
                                 where PR.PARAM_TYPE = 'REPORT_LANGUAGE'))
	and (FOI.ORDER_STATUS_ID IN (select INTEGER(PR.PARAM_VALUE)
                                     from WCA.PARAMETERS PR
                                     where PR.PARAM_TYPE IN ('ORDER_STATUS_BILLED',
                                     'ORDER_STATUS_COLLECTED'))
	and P.FISCAL_YR = (select FISCAL_YR 
                           from WCA.PERIOD
                           where CALENDAR_DATE = CURRENT DATE))
group by
	PREF.NAME,
	FOI.REPORT_CURRENCY 
ORDER BY 5	DESC	

Three new columns are added to the original biProductUnitSoldReport:

1. Gross Profit: The difference between the product unit price and unit cost.
2. Profit Margin %: The percentage of the product gross profit in the product revenue.
3. Profit Contribution %: The percentage of a product gross profit in the total store product gross profit.

Insert the new column definitions for display after the last column as:

<columns>
     <columnKey>C3</columnKey>
     <columnName>grossProfit</columnName>
     <columnOptions>ALIGN=RIGHT HEIGHT=20 NOWRAP</columnOptions>
     <columnType>decimal</columnType>
     <setMinimumFractionDigits>3</setMinimumFractionDigits>
</columns>
<columns>
     <columnKey>C4</columnKey>
     <columnName>profitMargin</columnName>
     <columnOptions>ALIGN=RIGHT HEIGHT=20 NOWRAP</columnOptions>
     <columnType>decimal</columnType>
     <setMinimumFractionDigits>3</setMinimumFractionDigits>
</columns>
<columns>
     <columnKey>C5</columnKey>
     <columnName>profitContribution</columnName>
     <columnOptions>ALIGN=RIGHT HEIGHT=20 NOWRAP</columnOptions>
     <columnType>decimal</columnType>
     <setMinimumFractionDigits>3</setMinimumFractionDigits>
</columns>

Check the column keys in the <columnKey> tag to make sure that the column keys are realigned to match the data fields returned by the SQL query.

The column heading variables, defined in the <columnName> tag, are defined in the next step.

2. Modifying the Report Properties file

The report properties file contains text string definitions that include report titles, descriptions, and column headings. A column heading and description is added or changed in an existing report, its test string definition also needs to be added or changed in the report property file. Make sure the properties that are modified match the reporting language. In this case, three column names were added to the report.

Open the <WAS_Install_Root>\installedapps\<WC_Node_Name>\<WC_Instance_Name>.ear\properties\ com\ibm\commerce\tools\bi\properties\BINLS_<local>.properties report properties file, where:

• <WAS_Install_Root> is the WebSphere Application server root directory.
• <WC_Node_Name> is the WebSphere Commerce server name.
• <local> is the language, for example, en_us for US English.
• Add the report column heading definition for the report under # Column Headers.

# Report output - table headings
grossProfit=Gross Profit
profitMargin=Margin %
profitContribution=Profit Contribution %

3. Restarting the WebSphere Commerce instance

Since all the report information is loaded when the server instance starts, make sure to stop and restart the WebSphere Commerce instance for the changes to take effect.

Back to top

Creating a new WebSphere Commerce Analyzer report

This section explains how to create new business intelligence reports with the reporting framework in WebSphere Commerce Express. This solution is packaged with a set of files for a sample report that you can use. These files have been tested with a Windows 2000/IBM HTTP Server/DB2 configuration of WebSphere Commerce Express Edition 5.6.

The following procedure shows how to use the sample files to create a new WCA report called the "Loyal Customer Activity Report", which shows what kind of products loyal customers bought and the revenue and profit level.

1. Creating report definition file

Every WCA report has one report definition file, <ReportName>Report.xml, which contains the SQL query and column definitions.

Copy the sample report definition file biLoyalCustomerActivity.xml to the following directory, <WC_Install_Root>\xml\tools\bi\. Modify it as needed. Make sure that the following is true about the report definition file:

• All the report names are unique in WebSphere Commerce server.
• The data source name is correct. The data source is defined inside the <report> tag, the default value is WCA DataSource.
• Test the SQL query before using it in the XML file.

2. Creating input and output JSP files

A new report requires two JSP files, one for the inputview and one for the outputview. These JSP files are generally named <ReportName>ReportInputView.jsp and <ReportName>ReportOutputView.jsp. You can either create new JSP files or modify the sample JSP files.

Copy the biLoyalCustomerActivityReportInputView.jsp and biLoyalCustomerActivityReportOutputView.jsp sample files to the following directory and then modify them if needed.

<WAS_Install_Root>\installedapps\<WC_Node_Name>\<WC_instance_name>.ear\
         commerceaccelerator.war\tools\bi\

3. Creating report input and output configuration and displaying text xml files

Create two XML files for the report input and out dialogs:

• <ReportName>ReportDialog.xml contains configuration and display text for the <ReportName>inputDialog.jsp.
• <ReportName>ReportOutputDialog.xml contains configuration and display text for the <ReportName>outputDialog.jsp.

Copy the following sample files:

• biLoyalCustomerActivityReportDialog.xml
• biLoyalCustomerActivityReportOutputDialog.xml

from the sample directory to the following directory, <WC_Install_Root>\xml\tools\bi\, and then modify the sample report xml files to fit the requirements.

4. Adding XML files created above to the resource mapping file

The source mapping file, resources.xml, contains all the resource mapping information for the reports. You can find it the <WC_Install_Root>\xml\tools\bi\ directory.

Add the XML files in the target block for the report. For example:

<!-- Loyal Customer Activities -->
<resourceXML name="biLoyalCustomerActivityReportInputDialog"
file="bi/biLoyalCustomerActivityReportInputDialog.xml" />
<resourceXML name="biLoyalCustomerActivityReportOutputDialog"
file="bi/biLoyalCustomerActivityReportOutputDialog.xml" />
<resourceXML name="biLoyalCustomerActivityReport"
file="bi/biLoyalCustomerActivityReport.xml" />

Make sure that you back up the original one before modifying it.

5. Adding the new report to WebSphere Commerce Analyzer report list

WebSphere Commerce dynamic context is a list of grouped actions (or a list of actions shown up on the menu) that users can choose to perform. The list contains the names and brief descriptions of the actions. The list changes dynamically based on the roles of the user and components that are enabled.

Edit the <WC_Install_Root>\xml\tools\bi\biContext.xml file. Add a new <entry> ... </entry> block for your report within a <context> ... </context> block. Each entry corresponds to a report that is listed on a report list page. As part of each entry, you must specify the roles that see the report on the list page. If the user has been assigned at least one of the roles outlined in the <entry>, then the report is displayed on the list. For example:

<!-- ===================== New Reports -->
<entry nameKey="LoyalCustomerActivity" descriptionKey="LoyalCustomerActivityDescription" 
                      breadCrumbTrailTextKey="Report" toolsComponent="CommerceAnalyzer">
     <roles>
          <role>siteOwner</role>
          <role>siteAdmin</role>
          <role>catMgr</role>
          <role>seller</role>
          <role>makMgr</role>
          <role>podMgr</role>
          <role>chnMgr</role>
     </roles>
     <command name = "DialogView">
          <parameter name="XMLFile" value="bi.biLoyalCustomerActivityReportInputDialog" />
     </command>

Make sure that you back up the original one before modifying it.

6. Adding the appropriate text strings for your report

The text strings include report titles, descriptions, and column headings that need to be added in the report property file. Make sure the property file match matches your reporting language. Edit the following file:

<WAS_Install_Root>\installedApps\<WC_Node_Name>\<WC_instance_name>.ear\properties\com\ibm\commerce\
tools\bi\properties\BINLS_<local>.properties
                

This file contains the report name, report description, and the breadcrumb trail entry. Add an entry for each report name, report description, and breadcrumb trail entry to the bottom of the file. For example:

# Name Keys
LoyalCustomerActivity=Loyal Customer Activity

# Descriptions Keys
LoyalCustomerActivityDescription= This set of reports contains information about loyal customer 
shopping details by date.

# Column Headers
revenue=Revenue
grossProfit=Gross Profit 

You can share the string variables defined in this file between reports. Make sure you use the correct properties file for your reporting language.

7. Adding the new report View commands for the new report

The WebSphere Commerce database contains the view commands that map to each input and output JSP file. Use the DB2 insert commands below. Change the JSP file names and the View command names appropriately.

Note: If all stores are required to access the reports, enter <storeent_id>=0. If only a specific store requires access the report, enter the specific storeent_id value.

insert into viewreg (viewname,devicefmt_id,storeent_id,interfacename,classname,properties,
           description,https,lastupdate,internal)
   values ('biLoyalCustomerActivityReportInputView', -1, 0, 
      'com.ibm.commerce.tools.command.ToolsForwardViewCommand',
      'com.ibm.commerce.tools.command.ToolsForwardViewCommandImpl', 
      'docname=tools/bi/biLoyalCustomerActivityReportInputView.jsp','', 0, CURRENT TIMESTAMP, 1);
insert into viewreg (viewname,devicefmt_id,storeent_id,interfacename,classname,properties,
           description,https,lastupdate,internal) 
   values ('biLoyalCustomerActivityReportOutputView', -1, 0, 
      'com.ibm.commerce.tools.command.ToolsForwardViewCommand',
      'com.ibm.commerce.tools.command.ToolsForwardViewCommandImpl', 
      'docname=tools/bi/biLoyalCustomerActivityReportOutputView.jsp','', 0, CURRENT TIMESTAMP, 1);

You can execute these SQL commands from the DB2 command window or DB2 command center. Connect to the WebSphere Commerce database mall first.

The sample also includes a script that you can use to execute from DB2 Command Window as:

AddViewCommands.bat <database_name> <schema_name> <user_name> <password>

Where:

• <database_name> is the WebSphere Commerce database name. The default is mall.
• <schema_name> is the database schema name which owns table viewreg.
• <user_name> is the name of a user who has the authority to execute these insert commands.

Make sure that the viewname values match exactly with the JSP file name. They are case-sensitive.

8. Adding new actions for the new report

Each view defined in the VIEWREG table of WebSphere Commerce database needs a corresponding action defined in ACACTION table.

To add actions into the ACACTION table in the WebSphere Commerce database for the new report, replace the acaction_ids below with appropriate values that are not currently used in the acaction table and are less than the current key table value.

To find the current highest acaction_id value used in the database, enter the following in a DB2 command window:

db2 connect to mall
db2 select max(acaction_id) from acaction

To find the current key table value for acaction, enter the following in a DB2 command window:

db2 select counter from keys where tablename='acaction'

The current key table value is the maximum acaction_id value that you can use in the acaction table. When adding new actions into the table, the acaction_id values must fall between the highest acaction_id used in the acaction table and the current key table value.

Assume that the highest acaction_id used in the acaction table is 12105 and the current key table value is 12150. To add access control to the report biLoyalCustomerActivity, type the following DB2 commands from the DB2 command center with appropriate action_id values:

insert into acaction (acaction_id, action) values 
  (12110, 'biLoyalCustomerActivityReportInputView');
insert into acaction (acaction_id, action) values 
  (12111, 'biLoyalCustomerActivityReportOutputView');
insert into acactdesc (acaction_id, displayname, description, language_id) 
    values (12110, 'Specifies the criteria to create the report for Loyal Customer Activity', 
       'Specifies the criteria to create the report for Loyal Customer Activity', -1);
insert into acactdesc (acaction_id, displayname, description, language_id) 
     values (12111, 'Displays the report for Loyal Customer Activity', 
        'Displays the report for Loyal Customer Activity', -1); 

You can execute these SQL commands from the DB2 command Window or DB2 command center. Make sure:

• A connection to the WebSphere Commerce database mall is made first.
• The acaction_id values match exactly with the view ID values in the previous step.

To increase the current key to add more reports, issue the following command:

update keys set counter=12000 where tablename='acaction'

Make sure the machine has enough resources for the reports when the counter is increased.

The sample also includes a script that you can use to execute from a DB2 Command Window as AddActions.bat <database_name> <schema_name> <user_name> <password>.

9. Restarting the WebSphere Commerce instance

Since all the report information is loaded when the server instance starts, stop and restart the WebSphere Commerce instance for the changes to take effect.

The report is located on the WebSphere Commerce business intelligence report list page (menu) that was specified previously in Step 5. Be sure to login with a user ID that has been assigned one of the roles that may view the report.

Back to top

Conclusion

This article provides detailed instructions, techniques, and tips on how to customize and create new WebSphere Commerce Analyzer business intelligence reports to take advantage of the enriched WebSphere Commerce data in WebSphere Commerce Analyzer data marts and WebSphere Commerce reporting framework. You can modify the sample files to quickly create new business intelligence reports based on WebSphere Commerce Analyzer data marts.

Back to top

Download

Information about download methods

Resources

• Download the source code used in this article.
  
  
• IBM Solutions Builder Express Portfolio has the resources to help you build technical, sales and product skills with IBM software products.
  
  
• IBM WebSphere Commerce Analyzer Datamart Reference provides reference information about the IBM WebSphere Commerce Analyzer datamart.
  
  
• WebSphere Commerce Accelerator Customization Guide addresses the high level architecture of how the user interface interacts with the business users, and the WebSphere Commerce Server.
  
  

About the authors

Leon Gong is a staff software engineer in IBM Solution Builder Express Portfolio. For the last four years, he has developed many integrated solutions for IBM Mid-Market Business Partners. His focus has been on Business Intelligence and e-commerce. He has DB2 certifications in both application development and administration areas.

Donna Venditti is a Project Lead in IBM Solution Builder Express. For the last 5 years, she has delivered both industry and cross-industry starting point solutions for IBM Mid-Market Business Partners. Her focus has been on e-commerce and Business Intelligence solutions for the retail industry.

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• WebSphere Commerce Analyzer features
• Sample business scenario
• Enabling WebSphere Commerce Analyzer
• Modifying an existing WebSphere Commerce Analyzer report
• Creating a new WebSphere Commerce Analyzer report
• Conclusion
• Download
• Resources
• About the authors
• Comments

Local resources

• IBM Innovation Center - Austin, TX
• IBM Innovation Center - Chicago, IL
• IBM Innovation Center - Dallas, TX
• IBM Innovation Center - San Mateo, CA
• IBM Innovation Center - Waltham, MA
• Technical Briefings in North America

My developerWorks community

Dig deeper into WebSphere on developerWorks

Rate a product. Write a review.

Write a review for Rational products today!

Special offers