Boite à outils Office

The Data Warehouse & analytics reports in Commerce Server

Analytics, Commerce Server, Data Warehouse, Reporting Add comments

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:

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>

Commerce Server - Add Data Warehouse ressourece: Commerce Server Manager Commerce Server - Add Data Warehouse ressourece: Open DW.Pup Commerce Server - Add Data Warehouse ressourece: Select the Data Warehouse ressource Commerce Server - Add Data Warehouse ressourece: Use a new ressource Commerce Server - Add Data Warehouse ressourece: Confirm the parameter Commerce Server - Add Data Warehouse ressourece: rocking ;) Commerce Server - Add Data Warehouse ressourece: give a name to the ressource Commerce Server - Add Data Warehouse ressourece: still rocking Commerce Server - Add Data Warehouse ressourece: yeah Commerce Server - Add Data Warehouse ressourece: confirmation Commerce Server - Add Data Warehouse ressourece: Proof :)

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”:

Commerce Server - Import de reports: Open the shell Commerce Server - Import de reports: run the program

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”

Commerce Server - Import de reports: set the report Commerce Server - Import de reports: report server Commerce Server - Import de reports: list of 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:

Commerce Server - Import dts wizard: Open Commerce Server - Import dts wizard Commerce Server - Import dts wizard: select a Commerce Site Commerce Server - Import dts wizard: Commerce Server - Import dts wizard Commerce Server - Import dts wizard Commerce Server - Import dts wizard Commerce Server - Import dts wizard

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

Commerce Server - Import the data Commerce Server - Import the data Commerce Server - Import the data

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

image image

And process the whole cubes and dimensions:

image image image

That’s it’s that’s all!

Commerce Server Report: Product Sales

Commerce Server Report: Customer Sales

Commerce Server Report: Order Events

image

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

Commerce Server Data Warehouse: Set the local time

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


JARAC 2011

10 Responses to “The Data Warehouse & analytics reports in Commerce Server”

  1. Gael Duhamel [MVP] » Blog Archive » Create simple and effective reports for Commerce Server with PowerPivot (aka Gemini) Says:

    [...] one of my previous post, I explained how to configure the analytics reports for Commerce Server, and I’m sure that most of you guys was disappointed by the result (who says [...]

  2. Abdul Abdul Says:

    This article is very helpfuland has gotten be much ahead in the process but I see a few things that where nottouched on and I think it will be big help. When I try to Unpack the DW.pup to a new resource it unpacks fine but to my existing commerce sites I get an error; The resources listed below already exist in the site you have selected. You cannot unpack resources over existing resources. Data Warehouse.

    When I run theData Warehouse Import Wizard then selectthe Commerce Site ALL check boxes are available except the fist one (Web server logs) I check them all and click next and there I get an error Failed to connect to the datawarehouse for this site. You must unpup the datawarehouse resource before running the import wizard for this site or choose a different site. This is a catch 22.

    Kindly assis and I am sure other will benefit from this too.

    Regards
    Abdul

  3. GaelDuhamel Says:

    Hi Abdul,

    Thanks for that :)

  4. Cesar Says:

    Hi Gael;

    Thank you so much for your article… I would appreciate your help with the following:
    I followed the steps indicated in your article (unpuping Dw.pup); unfortunately, it did not add the Commerce Server Datawarehouse (under Site Resources), or anything like it. Please advice on what may have gone wrong. Thank you.

  5. GaelDuhamel Says:

    Hi Cesar,

    Are you running on an english language operating system?

  6. Jigger Says:

    I have the same problem. Unpup errored, created database but no entry in Commerce server manager. Now I cant unpup again. English language yes.

  7. GaelDuhamel Says:

    The only workaround I know is to go to the MSCS db and remove the datawarehouse ressource manualy… (I know :))

    Do you have the following compoments installed?
    1.Install the Microsoft SQL Server Native Client.
    2.Install the Microsoft SQL Server Management Objects Collection.
    3.Install the Microsoft ADOMD.NET.

  8. Pritesh Gandhi Says:

    Hi Gael

    Where is solution of abdul question ?

    Thanks
    Pritesh Gandhi

  9. GaelDuhamel Says:

    Hi Pritesh,

    Which questions are you talking about?

  10. Pritesh Gandhi Says:

    Hi Gael,

    I am facing same problem which abdul mentioned in first post.

    Not able to view Commerce server Data warehouse in Commerce Server Manager …

    I have tried multiple times but still it’s not working …

    Any help from your side is helpful to me.

    Thanks
    Pritesh Gandhi

Leave a Reply


WP Theme & Icons by N.Design Studio
Entries RSS Comments RSS Log in
Creative Commons License