all things Sitecore related

Converting .mrt reports from OMS to DMS

By on November 15, 2011 in Analytics, DMS, Sitecore, SQL Server, TSQL, Tutorials with 0 Comments

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.

Converting .mrt reports from OMS to DMS image thumb

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.

Converting .mrt reports from OMS to DMS image thumb1

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.


from
    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
    Pages.ItemId,
    (select top 1 Page2.Url from Pages as Page2 where Page2.ItemId = Pages.ItemId) as Url,
    Count(*) as Total
from
    Pages, -- Page,
    Visits, --Session
    Visitors --GlobalSession,
    --IPOwner,
    --IP
where
    --Page.Timestamp >= @StartDate and
    --Page.Timestamp <= @EndDate and
Pages.[DateTime] BETWEEN @StartDate AND @EndDate and
    Pages.ItemId != '00000000-0000-0000-0000-000000000000' and
    Visits.VisitId = Pages.VisitId and
    Visits.VisitorId = Visitors.VisitorId
    group by
    Pages.ItemId
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

Converting .mrt reports from OMS to DMS image thumb2

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

Converting .mrt reports from OMS to DMS image thumb41

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.

Converting .mrt reports from OMS to DMS image thumb31

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.
  • 
    DECLARE @StartDate DATETIME
    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.

Tags: , , , ,

About the Author

About the Author: Pieter is Technical Marketing Manager for Sitecore Netherlands and owner of Gaatverweg.nl. He has more than ten years experience with software developing in multiple programming languages and with different Content Management Systems. Before joining Sitecore Pieter was a lead developer for multiple Sitecore and .Net projects, he joined Sitecore in 2011 as an Solution Architect in The Netherlands, after two years as an Solution Architect he joined the Technical Marketing department. In the role as Techinical Marketing Manager he is responsible for the Global MVP program and the Sitecore technical branding strategy. You can follow Pieter on twitter: @pieterbrink123 or Google+ .

Subscribe

If you enjoyed this article, subscribe now to receive more just like it.

Post a Comment

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

Top