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.