Category: TSQL

Posted on

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.

image

In this example we have a Continue reading “Converting .mrt reports from OMS to DMS”

Posted on

TSQL: Nested Select with multiple results to one (comma separated) string

For this example I will use the default asp.net Membership tables.

Let say: I need to return all users with their roles (comma separated) in one query. After a long time Googling I found the following solution.

[code language=’sql’]

select
  UserName,
  (select roles.RoleName + ‘, ‘
    FROM aspnet_Roles roles
    join aspnet_UsersInRoles usersInRole on roles.RoleId = usersInRole.RoleId
    WHERE usersInRole.UserId = aspUser.UserId
    for xml path(”)) as roles
from
    aspnet_Users aspUser

[/code]

Don’t know if this is the best way, but it works.

Posted on

TSQL: Use common table expression

With common table expressions you can save the results to a temporary result set and use this results set for other queries.

[code language=’sql’]
WITH temporaryNamedResultSet
AS
(
  select UserName from aspnet_Users
)
select * from temporaryNamedResultSet

[/code]