Troubleshooting WebSphere Commerce Analyzer: No data in the Business intelligence reports

If after your configure the Business Intelligence reports, you do not see any data in the reports in the WebSphere Commerce Accelerator, you may have one of the following problems:

• WebSphere Commerce Analyzer has not analyzed any data. Check the FACT_ tables in the data mart to see if there is data. If there is no data, make sure replication and ETL have completed successfully and that there is data to analyze in the WebSphere Commerce database. Try to run the report SQL directly against the data mart from a DB2 Command Prompt. You can locate the SQL in various XML files which define the individual reports. These files are located in the following directory:

  WC_eardir/xml/tools/bi

  For example, you could run the following SQL to test whether the WebSphere Commerce Analyzer database has data to report. Issue the following SQL statements in a DB2 Command line processor after connecting to the WebSphere Commerce Analyzer database and replacing the "{storeent_id}" with the store ID value of the published store:
  

  SELECT
      YEAR(P.CALENDAR_DATE) AS "Year",
      P.MON_OF_FY AS "Month",
      P.DAY_OF_FM AS "Day Of Month",
      COUNT (distinct (FOI.ORDER_ID)) AS "No of Orders",
       INT(count(FOI.NUM_UNITS)) AS "No of Items",
       (SUM (FOI.RPT_SALES_VALUE ) + SUM (FOI.RPT_DISCOUNTS)) AS "Sales Value",
      FOI.REPORT_CURRENCY AS "Currency"
  
  FROM
      WCA.FACT_ORDERITEMS FOI, wca.fact_orders fo, wca.period P
  
  WHERE
      foi.order_id=fo.order_id
      AND fo.per_id=P.per_id
      AND FOI.order_status_id in (
          select int(param_value) from wca.parameters 
          where param_type in ('ORDER_STATUS_SHIPPED', 'ORDER_STATUS_SUBMITTED'))
          AND FOI.STORE_ID IN ({storeent_id})
      GROUP BY
      YEAR(P.CALENDAR_DATE),
      P.MON_OF_FY,
      P.DAY_OF_FM,
      FOI.REPORT_CURRENCY
  

  If data is returned, there is a problem with the WebSphere Application Server dataSource setup. If no data is returned, then WebSphere Commerce Analyzer does not have any data to report. Try re-running WebSphere Commerce Analyzer and make sure there is data in the WebSphere Commerce database to analyze.
• WebSphere Commerce Analyzer did not complete ETL processing, possibly due to some error. Check the error log for details. The last step in ETL, End_ETL, should have run successfully.
• You do not have a connection with the data mart. Check that you have created a connection to the data mart database from the WebSphere Commerce machine.

Related concepts

• WebSphere Commerce Analyzer

Related reference

• Troubleshooting WebSphere Commerce Analyzer

(C) Copyright IBM Corporation 1996, 2006. All Rights Reserved.