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.
[code language=”sql”]
from
Pages, — Page,
Visits, –Session
Visitors –GlobalSession,
[/code]
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.
[code language=”sql”]
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
[/code]
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.
- [code language=”sql”]
DECLARE @StartDate DATETIME
SET @StartDate = ‘2011-01-22 10:31 PM’DECLARE @EndDate DATETIME
SET @EndDate = ‘2012-01-22 10:31 PM’
[/code] - Still no luck try leaving a comment below.
That´s it! Hope it helps. Happy development.