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[Type='Notifications!System.Notification.Endpoint']/TargetAddress$" SlowlyChangingAttribute="false" /> <InclusionAttribute ID="ChannelName" PropertyPath="$Context/Property[Type='Notifications!System.Notification.Endpoint']/ChannelName$" SlowlyChangingAttribute="false" /> <InclusionAttribute ID="Id" PropertyPath="$Context/Property[Type='Notifications!System.Notification.Endpoint']/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:
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