Gael Duhamel [MVP]

In the Commerce Server enterprise edition, you can have access to the analytics reports and to a data warehouse.

In this post, I will show you how to configure them, and in the same time have a quick look on what it’s look like.

Before to start the installation, you have to install and configure:

• SQL Server Analysis Services (SSAS)
• SQL Server Reporting Services (SSRS)
• SQL Server Integration Services (SSIS)

Please, be sure that you have the mandatory prerequisites:

• For SQL Server 2005
  • Install Microsoft SQL Server 2005 Service Pack 2
  • Install Microsoft SQL Server 2000 DTS Designer Components
• For SQL Server 2008
  • Install Microsoft SQL Server 2005 Backward Compatibility Components
  • Update all the reports to SQL Server 2008. For that, open it all with the SQL Server Business Intelligence Development Studio 2008. The RDL’s files are stored in: %commerce_server_root%\RDLs

Now it’s time to unpup the ressource file for the data warehouse (the file takes place in %commerce_server_root%\Pup Packages\DW.pup). This ressource will create the database which will be the cube’s data source.

Beware: if you install your data warehouse in another language than english you must change the file %commerce_server_root%\Data\CommerceOlap.xmla  line 9679 and replace the “Everyone” string by “Tout le monde” for the french environment.

<Roles>
  <Role>
    <ID>All Users</ID>
    <Name>All Users</Name>
    <Members>
      <Member>
        <Name _loc="locData">EveryoneTout le monde</Name>
      </Member>
    </Members>
  </Role>
</Roles>

Once the data warehouse and the cube are installed, we can install now the reports. For that, open the Commerce Server prompt and type “ReportInstaller.exe”:

On the dialog box, fill in the fields and test the values by clicking “Test Connection”. If it’s ok, click now on “Install Reports”

It’s time now to configure the DTSImport permission against the DB:

• MSDB
  • db_datareader, db_dtsadmin, db_dtsltduser , db_dtsoperator
• <CommerceServerSiteName>_DataWarehouse
  • db_datareader, db_datawriter, db_owner, db_ddladmin
• <CommerceServerSiteName>_marketing
  • db_datareader
• <CommerceServerSiteName>_marketing_lists
  • db_datareader
• <CommerceServerSiteName>_productcatalog
  • db_datareader
• <CommerceServerSiteName>_profiles
  • db_datareader, Profile_Schema_Reader
• <CommerceServerSiteName>_transactionconfig
  • db_datareader
• <CommerceServerSiteName>_transactions
  • db_datareader

Add now this user to the local admin group from your SSIS/SSAS server.

To finish the installation, we have now to import the DTS (in the old fashion 2000 way ). For that, just run the Data Warehouse Import Wizard. It’s not a mandatory to run the import at the end of the wizard. You can run it later on (you’ll have more options to tune the import). At the end, save the DTS in SQL Server:

To open and execute the DTS, expand: SSMS > Gestion > Existant > DTS

In this last step, we have to build the cube. For that, open the SQL Server Business Intelligence Development Studio:

And process the whole cubes and dimensions:

 

That’s it’s that’s all!

If you want to change the time zone or the start week day,open the properties of the DW global resources:

If you want to go deeper in this subject, you can read the below links:

• Data Warehouse
• Analysis Reporting
• How Does the Data Warehouse Analytics System Work?
• Developing with the Data Warehouse Analytics System
• Data Warehouse Configuration Best Practices