PRB: Error Occurs When You Create a Cube in the Dimension Editor (314161)

SYMPTOMS

If you follow the instructions in Appendix B of the Extending Analysis and Reporting document, which is included with the BizTalk Accelerator for Suppliers, you may receive the following error message when you use the Dimension Editor to create a new cube:

Unable to count the members...

CAUSE

This problem occurs because there is no data in the Data Warehouse for the field in the corresponding table or view. Even if the data is available in the Commerce Server database, the data may not be populated when you run the DTS task.

RESOLUTION

To correct this problem, run a SQL script to update the date fields for the existing data in these tables. To create the script, and then run it at the appropriate time in the documented instructions, do the following:

Create an empty text file, and then name it "Update.sql."

Copy the following code into the Update.sql file:

UPDATE [dbo].[SE_FulfillmentStatus]
SET d_date_last_changed = getdate(),
d_date_created = getdate() where d_date_created is null
GO

UPDATE [dbo].[SE_Marketplace]
SET d_date_last_changed = getdate(),
d_date_created = getdate() where d_date_created is null
GO

After you run the alter table scripts in the "Step 3: Preparing to Import Data" section of the Extending Analysis and Reporting document, run the Update.sql script.

MORE INFORMATION

Even though the data is available in the Commerce Server database, the data is not populated into the Data Warehouse because the DTS task picks up the data based on a specific date. The SQL scripts in the "Step 3: Preparing to Import data" section add two new fields (d_date_created and d_date_last_changed) to the SE_MarketPlace and SE_FulfillmentStatus tables, and then default these fields to getdate(). However, this does not update the value in these two fields for the data that is already present in these two tables. Any data that is already present in the table for these fields is Null. Because the date value is Null for the existing data, the DTS task does not pick it up. Therefore, the user receives the following error message:

Unable to count the members...