Converting .mrt reports from OMS to DMS

In this article I’m going to explain how to convert .mrt reports from the Sitecore Online Marketing Suite (Sitecore 6.4) to the Sitecore Digital Marketing System (Sitecore 6.5).

Report related changes between OMS and DMS

Both the OMS and the DMS use .mrt reports for Analitics reporting. There are two mayor changes between the OMS and DMS reporting; a full redesign of the datamodel and the location of the SQL query for the report data.

Mapping the datamodel of the OMS with the DMS

If you compare the datamodel of the OMS to the DMS datamodel you will see that it’s completely different. But the main concept of both datamodels is almost the same.

OMS table DMS Table Description
GlobalSession Vistors Contains the Visitor information.
Session Visits Contains the information of all the visits. A visits is underneed a Visitors.
Page Pages Contains all pages that are visited and are connected to a Session.

The following drawing will show the relation of Visitor->Sessions->Pages.


In this example we have a Visitor who has two visits on our website; Visit 1 and Visit 2. During Visit 1 she visited three pages; page 1, page 2 and page 3. During the second Visit she visited two pages; page 1 and page 2. The data will be saved in this order in the Visitor, Visit and Pages tables.

This new naming convention makes the datamodel of the DMS easier to understand and to relate to. But you’ll need to change your SQL queries to map the new convention. Also some tables are deleted and other tables are merged in to one flat table.

SQL query location

With the OMS the sql query to retrieve your data from the database was in the .mrt file. This is changed in the DMS. The SQL queries is located in a item, these items are located at /sitecore/system/Settings/Analytics/Reports SQL Queries/. This way you can change the query outside the report (even if you broke the report with your query ;-)).

Example: Converting the Top Pages report

In this example we are going to convert the Top pages report from OMS to the DMS.

Step 1: Copy the Toppages.mrt

Copy the /sitecore/shell/Applications/Analytics/Reports/Toppages.mrt report to the same folder in your DMS solution.

Step 2: Convert the SQL query

Open the Toppages.mrt file in Visual Studio and locate the SQL query in the <SQLCOMMAND> node. Copy the SQL code, clear the SQLCOMMAND node and save the report.


Paste the SQL query into SQL management studio. Now map the Page, Session and GlobalSession table in the from section to the new DMS tables. Leave the original names as comment behind the new name so you can reference to those names while converting where and select statements. Comment out the IPOwner table and IP table. The from section should look like this.

    Pages, -- Page,
    Visits, --Session
    Visitors --GlobalSession,

Now convert the select and where statements. In the where clause use BETWEEN for the date filter.

When your finished your SQL query should look like this.

select top 50
    (select top 1 Page2.Url from Pages as Page2 where Page2.ItemId = Pages.ItemId) as Url,
    Count(*) as Total
    Pages, -- Page,
    Visits, --Session
    Visitors --GlobalSession,
    --Page.Timestamp &gt;= @StartDate and
    --Page.Timestamp &lt;= @EndDate and
Pages.[DateTime] BETWEEN @StartDate AND @EndDate and
    Pages.ItemId != &#039;00000000-0000-0000-0000-000000000000&#039; and
    Visits.VisitId = Pages.VisitId and
    Visits.VisitorId = Visitors.VisitorId
    group by
order by
    Total desc

Step 3: Create the Reports SQL Queries item

Login to Sitecore and duplicate the Latest Visits item in /sitecore/system/Settings/Analytics/Reports SQL Queries/ name the new item Top Pages.

Past the SQL query created in step 2 into the Queries field.

Save the item.

Step 4: Create the Report item

Go to /sitecore/system/Settings/Analytics/Reports/Reports/Recent Activity and duplicate the Latest Visits report as Top Pages. In the Top Pages report Item Change the path in the File Name field to /sitecore/shell/Applications/Analytics/Reports/TopPages.mrt  and the Report Title to Top Pages


At the Queries section, change the datasource parameter to the Top Pages Report Sql Query item.


Step 5: View the Top Pages report

Open Engagement Analytics, go to Recent Activity and select Top Pages. If everything went well the report will show.


Good WORK! If not check here is some troubleshooting information.Check the following :

  • Did you remove the SQL statement from the .mrt file. <SqlCommand />
  • Is you query working? Test your query by declaring  a startdate and enddate variable.
    SET @StartDate = '2011-01-22 10:31 PM'DECLARE @EndDate DATETIME
    SET @EndDate = '2012-01-22 10:31 PM'

  • Still no luck try leaving a comment below.

That´s it! Hope it helps. Happy development.

Leave a Reply

Your email address will not be published. Required fields are marked *