How to add mail addresses to Data Warehouse

June 25, 2014 Posted by Alexander Axberg

In this post I will go through how to add your users mailaddresses to the Data Warehouse, to be able to display them i reports.
Since they are not transferred to the Data Warehouse by default, we have to build a new Data Warehouse Management Pack to be able to sync this information.

But first a quick look how the mailaddresses are stored.
They are not simply stored in a textstring directly on the user object as you might think. They are actually stored as a separate object in the class “System.Notification.Endpoint”. This is makes it able to create several addresses on the same user (SIP and SMTP).
A relation between this object and the user object is then created. The relation is called System.UserHasPreference.

So what we need to do is to define a dimension for the System.Notification.Endpoint, and include the attributes that store the actual mailaddress.
Then we also need a Relationship Fact between the Notification Endpoint dimension and the User dimension.

The code to create that looks like this:

  <Warehouse>
    <Dimensions>
      <Dimension ID="SubscriberAddressDim" Accessibility="Public" InferredDimension="true" Target="Notifications!System.Notification.Endpoint" HierarchySupport="Exact" Reconcile="true">
        <InclusionAttribute ID="TargetAddress" PropertyPath="$Context/Property&#91;Type='Notifications!System.Notification.Endpoint'&#93;/TargetAddress$" SlowlyChangingAttribute="false" />
        <InclusionAttribute ID="ChannelName" PropertyPath="$Context/Property&#91;Type='Notifications!System.Notification.Endpoint'&#93;/ChannelName$" SlowlyChangingAttribute="false" />
        <InclusionAttribute ID="Id" PropertyPath="$Context/Property&#91;Type='Notifications!System.Notification.Endpoint'&#93;/Id$" SlowlyChangingAttribute="false" />
      </Dimension>
    </Dimensions>
    <Facts>
      <RelationshipFact ID="HasPreferenceFact" Accessibility="Public" Domain="DWBase!Domain.ConfigurationManagement" TimeGrain="Daily" SourceType="System!System.Domain.User" SourceDimension="DWBase!UserDim">
        <Relationships RelationshipType="SupportingItem!System.UserHasPreference" TargetDimension="SubscriberAddressDim" />
      </RelationshipFact>
    </Facts>
  </Warehouse>

So the complete steps to create our new Data Warehouse MP looks like this:

  • Create a new MP with the code above, or download the complete one below
  • Seal it, and import it into Service Manager as usual
  • Wait for the MPSyncjob in Data Warehouse to kick in (every hour) or start it manually. The MP will then be synced into DW.
  • Take a beer while you wait for the deployment in Data Warehouse.
  • When deployment is completed, log into the DWDataMart database in SQL, and look under views and you should have 2 new views there: SubscriberAddressDimvw, HasPreferenceFactvw

    • Now you are all set to start query the database in Reports to display the mailaddresses. You can use the following SQL query to list all your user object in DW with the columns: Username, Domain, Mailaddress

    Take in mind that after the Management Pack deployment is completed, it could take a while to populate the tables with the mail addresses.

    SELECT
Username,
Domain,
smtp.TargetAddress AS 'E-Mail'
FROM
UserDimvw AS u
INNER JOIN HasPreferenceFactvw AS hp
ON u.UserDimKey = hp.UserDimKey
INNER JOIN SubscriberAddressDimvw AS smtp
ON hp.UserHasPreference_SubscriberAddressDimKey = smtp.SubscriberAddressDimKey
WHERE
smtp.ChannelName = 'SMTP'
AND hp.DeletedDate IS NULL

    Lumagate.NotificationEndpoint.DataWarehouse.xml

    Tags: No tags
    Categories: Service Manager
    You can leave a response, or trackback from your own site.

    Leave a Reply

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

    CAPTCHA

    *