help.axcms.netAxinom Logo
Save Save Chapter Send Feedback

Integration of Reporting Template Project with MailCenter

 

Reporting Template Project can be integrated with Newsletter Template Project to use its databases to retrieve data for analysis and reporting.

Launching compatibility upgrade SQL script

Run the following SQL script

C:\Projects\AxCMS_ReportingSample\Resources_ReportingSample\DB\UpgradeMailCenterDB.sql

in Live database of MailCenterSample


Configuring HTTP-handlers for DocumentViewAction and NewsLetterView

To enable AxCMS.net to log document/ tracked newsletter views, you need to open virtual directory properties of AxCMSwebLive_MailCenterSample in IIS manager, and create mappings for the following extensions

.pdf
.zip
.xls
.doc
.track


to aspnet_isapi.dll of Framework 2.0.


Configuring LS.web.config of MailCenterSample

Now, you need to configure the LS.web.config of MailCenterSample to support action logging:

ensure that AxActionLog is described inside <configSections>

<configSections>
  <section
   name="AxActionLog"
   type="Axinom.AECMS.Logging.AxActionLogSectionHandler, AxCMS.BL"
   />
</configSections>

ensure that there is a <AxActionLog> section (you may need to add it if it's not present yet)  inside <configuration> section with the following strings:
 
<AxActionLog>
    <action class="Axinom.AECMS.Logging.AxPageViewAction" mode="on" />
    <action class="Axinom.AECMS.Logging.AxDocumentViewAction" mode="on" />
    <action class="Axinom.AECMS.Logging.AxLoginAction" mode="on" />
    <action class="Axinom.AECMS.Logging.AxRegisterAction" mode="on" />
    <action class="Axinom.AECMS.Logging.AxSearchAction" mode="on" />
    <action class="Axinom.AECMS.Logging.AxTeaserAction" mode="on" />
    <action class="Axinom.AECMS.Logging.AxTrackedLinkAction" mode="on" />
    <action class="Axinom.AECMS.Logging.AxSubscribeAction" mode="on" />
    <action class="Axinom.AECMS.Logging.AxUnSubscribeAction" mode="on" />
    <action class="MailCenterSample.BL.UserActions.AxNewsletterView" mode="on" />
  </AxActionLog>

ensure that the <httpHandlers> section contains the following strings:

<httpHandlers>
    <add verb="*" path="*.track" type="MailCenterSample.BL.NewsletterViewTrackingHandler, MailCenterSample.BL" />
    <add verb="*" path="*.pdf,*.zip,*.xls,*.doc" type="System.Web.StaticFileHandler" />
</httpHandlers>

 

Configuring MS.web.config of MailCenterSample

MS.web.config of MailCenterSample also needs to be configured to support action logging:

ensure that AxActionLog is described inside <configSections>

<configSections>
  <section
   name="AxActionLog"
   type="Axinom.AECMS.Logging.AxActionLogSectionHandler, AxCMS.BL"
   />
</configSections>

ensure that there is a <AxActionLog> section (you may need to add it if it's not present yet)  inside <configuration> section with the following strings:
 
  <AxActionLog>
    <action class="Axinom.AECMS.Logging.AxCMSLoginAction" mode="on" />
    <action class="Axinom.AECMS.AddStructureElementActivity" mode="on" />
    <action class="Axinom.AECMS.CheckinActivity" mode="on" />
    <action class="Axinom.AECMS.Logging.CheckOutAction" mode="on" />
    <action class="Axinom.AECMS.ClassifyActivity" mode="on" />
    <action class="Axinom.AECMS.CloneActivity" mode="on" />
    <action class="Axinom.AECMS.CreatePageSnapshotFileActivity" mode="on" />
    <action class="Axinom.AECMS.DeleteActivity" mode="on" />
    <action class="Axinom.AECMS.FillPropertiesActivity" mode="on" />
    <action class="Axinom.AECMS.GenerateReportsActivity" mode="on" />
    <action class="Axinom.AECMS.ImportUserActivity" mode="on" />
    <action class="Axinom.AECMS.MoveDownActivity" mode="on" />
    <action class="Axinom.AECMS.MoveUpActivity" mode="on" />
    <action class="Axinom.AECMS.RecoverActivity" mode="on" />
    <action class="Axinom.AECMS.RestoreSnapshotActivity" mode="on" />
    <action class="Axinom.AECMS.BulkOperations.ObjectCheckinActivity" mode="on" />
    <action class="Axinom.AECMS.BulkOperations.ObjectDeleteActivity" mode="on" />
    <action class="Axinom.AECMS.Publish.PublishDocumentActivity" mode="on" />
    <action class="Axinom.AECMS.Publish.PublishMailTemplateActivity" mode="on" />
    <action class="Axinom.AECMS.Publish.PublishNewsletterActivity" mode="on" />
    <action class="Axinom.AECMS.Publish.PublishPageActivity" mode="on" />
    <action class="Axinom.AECMS.Publish.RequestPublishingActivity" mode="on" />
    <action class="Axinom.AECMS.Publish.RevokeApprovalPageActivity" mode="on" />
    <action class="Axinom.AECMS.Logging.AxCMSLoginAction" mode="on" />
  </AxActionLog>

 

 ETL changes

You need to change connection/description strings inside the

C:\Projects\AxCMS_ReportingSample\Resources_ReportingSample\Configuration\SampleDEV\etl.config

of Reporting Template Project for ETL processor to work with Newsletter Template Project and MailCenter data.

Change LS and MS connection strings to use Newsletter Template database

<Connection Name="LS" ConnectionString="Data Source=(local);Initial Catalog=AxCMS_Live_MailCenterSample;UID=MailCenterSample;PWD=AxinomCMS1;"/>
<Connection Name="MS" ConnectionString="Data Source=(local);Initial Catalog=AxCMS_MailCenterSample;UID=MailCenterSample;PWD=AxinomCMS1"/>

Uncomment and check connection for MailCenter database connection string, it must match those from your MailCenter. 

<Connection Name="MC" ConnectionString="Data Source=(local);Initial Catalog=AxCMS_MailCenter;UID=MailCenter;PWD=AxinomCMS1;"/>

 Uncomment the MC SourceFact table connection in <CacheTables>

<SourceFactTable Connection="MC" Table="MailArchive" Alias="MCArchive" IDColumn="MailID" DeleteIntegratedRows="false"/>

Uncomment the Cube_ReportingSample_MC connection and comment the previous one.

 <!--<OlapCubeConnection Server="(local)" Database="Cube_ReportingSample" />-->
 <OlapCubeConnection Server="(local)" Database="Cube_ReportingSample_MC" />

 

Uncomment the MailCenter dimension tables

   <DimensionTable Name="DimMailRecipient" EnableSCD="false" SourceQuery="SELECT DISTINCT [To] FROM {{MCArchive}}">
    <Columns>
     <Column Name="MailAddress" SourceColumn="To" BusinessKey="true"/>
    </Columns>
   </DimensionTable>

   <DimensionTable Name="DimMailOrder" EnableSCD="false" SourceQuery="SELECT DISTINCT [From], Subject, OrderID FROM {{MCArchive}}">
    <Columns>
     <Column Name="OrderID" SourceColumn="OrderID" BusinessKey="true"/>
     <Column Name="Sender" SourceColumn="From" UnknownValue="Unknown"/>
     <Column Name="Subject" SourceColumn="Subject" UnknownValue="Unknown"/>
    </Columns>
   </DimensionTable>

 

Uncomment the MailCenter facts table

   <FactTable Name="FactMCSentMail"
      SourceQuery="SELECT [To], TargetDate, OrderID , MailID, DateCreated, SendState, SendAttempts FROM {{MCArchive}}"
      SourceCacheTable="MCArchive">
    <Columns>
     <Column Name="MailID" SourceColumn="MailID" BusinessKey="true"/>
     <Column Name="CreateDate" SourceColumn="DateCreated" TransformationAssemblyName="AxDWH_Transformations" TransformationClassName="Axionom.AxDWH_Transformations.DateTimeTransformation" References="DimTime"/>
     <Column Name="Recipient" SourceColumn="To" References="DimMailRecipient"/>
     <Column Name="OrderID" SourceColumn="OrderID" References="DimMailOrder"/>
     <Column Name="TargetDate" SourceColumn="TargetDate" TransformationAssemblyName="AxDWH_Transformations" TransformationClassName="Axionom.AxDWH_Transformations.DateTimeTransformation" References="DimTime"/>
     <Column Name="SendState" SourceColumn="SendState"/>
     <Column Name="SendAttempts" SourceColumn="SendAttempts"/>
    </Columns>
   </FactTable>

After making changes to configurations, please run


C:\Projects\AxCMS_ReportingSample\Resources_ReportingSample\Deploy\PostBuild.bat


to copy them to appropriate folders.

 

Recreating Data Warehouse to use Newsletter Template Project data

To recreate Data Warehouse database, run the following command in C:\Projects\AxCMS_ReportingSample\AxDWH_Processor folder:

axdwh_processor createdatabase recreate

 

Deploying the Cube_ReportingSample_MC cube

 

In AxCMS_ReportingSample solution, right click on the Cube_ReportingSample_MC project and select Deploy from the context menu.

 

 

After the cube is succesfully deployed, we need to populate it with data from Data Warehouse.

To do this, run the file
C:\Projects\AxCMS_ReportingSample\AxDWH_Processor\RunIntegration.bat

 

 

Like in configuration, final step is to process the data within cube.

For this, right click Cube_ReportingSample_MC project in your solution and select Process... from the context menu.