AxCMS.net Help & Support Center > Template Projects > Reporting Template Project

(1)

-Table Of Contents

Reporting Template Project

Installation

Configuration

Generating reports in Excel

Integration with MailCenter

Integrating Reporting to your solution

Sample Reports

(2)

AxCMS.net Help & Support Center > Template Projects > Reporting Template Project

Reporting Template Project

Concept

Reporting Sample Project is a sample solution which extends AxCMS.net logging functionality with advanced data analysis and reporting. The focus of this application is to log actions to the AxActionLog and process the data into reports shown in Excel files. Actions defined in web.config are logged to the AxActionLog tables in the database. The AxDWH_Processor generates a

Processor that loads new/changed data from databases to the data warehouse (DWH). Next the OLAP Cube is populated with data from Data Warehouse and the data is processed. Using data generated by OLAP Cube, you will be able to create and present various reports and charts in Excel.

For more info about Action Loging please see our Developing guide here: Logging Action

(3)

Installation of Reporting Template

Project

Reporting Template Project Prerequisites

Reporting Template currently requires Visual Studio 2005/ MS SQL Server 2005 to work.

In addition to standart template projects prerequisites,

Reporting Template Project requires the following services of MS SQL Server installed and started:

Make sure you also include Business Intelligence Development Studio in your SQL Server installation, this is a required component to work with Cube projects in Visual Studio.

Differences to standart template installation

Installation of Reporting Template Project is almost the same as of Sample project.

There are two main differences between Sample and Reporting Template project. The one is that all folders, virtual directories end

with _ReportingSample instead of _Sample. The second difference is that the custom extension is set to .AxCMS instead of

.Sample.

So, please, follow the Sample project installation guide, keeping in mind those 2 differences.

Configuring HTTP-handlers for DocumentViewAction

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

.pdf .zip .xls .doc

to aspnet_isapi.dll of Framework 2.0.

If you want to add additional document extensions, you should add mappings for them in IIS manager as described before, and additionally add them them to the extension list in

<httpHandlers> section of your LS.Web.config <httpHandlers>

(4)

</httpHandlers>

Project structure

AxDWH_Processor - ETL processor

AxDWHProcessor.exe - executable file for processor, you can run it with parameters or run it without any parameters:

AxDWHProcessor.exe - updates the data in cube from data warehouse AxDWHProcessor.exe createdatabase - creates new data warehouse database

AxDWHProcessor.exe createdatabase recreate - deletes old data warehouse databse and creates a new one

etl.config - main configuration file for ETL processor containing descriptions of all SQL connection strings for processor and Dimension/ Fact table mappings

CreateDatabase.bat - runs ETL processor in database creation mode RunIntegration.bat - runs ETL processor in cube update mode

AxDWHProcessor.exe.config - miscellaneous configurations for ETL processor

AxCMSTemplates_ReportingSample - Templates

Controls - put here the controls, used by template CSS - put your css-files here

Images - design-images, icons, etc.

RowControls - custom elements (former: "row controls") TopMenu - TopMenu extensions

CmsSite.xml - describes the structure of your templates

error.htm - the error page (has to be copied into cms- and/or live-root via PostBuild.build) ReportingSampleBaseTemplate.cs - inherit all your templates from this class

(5)

BaseTemplate.aspx - sample template. Use it or replace it with your own template ReportingSampleContext.cs - your own navigation context

Extras - Extension for the Management System

ArticleOverview[Detail|Filter].aspx - Sample usage of the Overview/Detail pattern

ExtraServices - Project for your custom web-services

ReportingSample.BL - Project for your business logic (everything but the GUI)

Resources_ReportingSample - Solution resources (will not be compiled)

Reporting - contains sample reports Excel file

Components - e.g. 3rd party DLLs used by your application

Configuration - configuration files for all applications and environments DB - DB-scripts for the DB-structures you develop in your application DeploySample - automation scripts, not only for deployment

Docs

UnitTests_ReportingSample - Unit Tests

SolutionAssemblyInfo.cs - here you set the version of the solution

(6)

Configuration of Reporting Template

Project

Configuring SQL connections

Please check the SQL connection strings and passwords in

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

and

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

make sure that any changes you make for Live System/Management System connections inside your MS.web.config and LS.web.config are also reflected in etl.config

</SourceConnections>

After making changes to configurations, please run

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

to copy them to appropriate folders.

Deploying Cube

NB! If you are running VISTA, please launch all programs listed in this document with: Right click/Run as Administrator, this is nessesary for them to work normally.

First step is to create a Data Warehouse database based on ETL configuration. To create it, please run

C:\Projects\AxCMS_ReportingSample\AxDWH_Processor\CreateDatabase.bat

(7)

After the process completes, open your solution and right click on the cube project you want to use and select Deploy from the context menu.

( we will use Cube_ReportingSample for this solution, but the process is the same for Cube_ReportingSample_MC)

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

Final step is to process the data within cube.

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

(8)

AxCMS.net Help & Support Center > Template Projects > Reporting Template Project • • • • • • • •

Generating reports in Excel

Using data generated by Reporting Template Project cube, you will be able to create and present various reports and charts in Excel.

Connecting to Reporting Template Cube in Excel

The example Excel file provided with Reporting Template Project shows some basic reports using the data warehouse.

C:\Projects\AxCMS_ReportingSample\Resources_ReportingSample\Reporting\Sample.xlsx

or

C:\Projects\AxCMS_ReportingSample\Resources_ReportingSample\Reporting\SampleMC.xlsx

if you have MailCenter integrated

If you are running Vista, find Start/Programs/Microsoft Office/Excel and right click/ Run as Administrator. Then in Excel, open the excel file instead of opening it directly. This is needed for cube connection to work.

By default, it is configured to work with Cube_ReportingSample on localhost. To change this (if Cube is located on a different server than localhost or if you need to use the Cube_ReportingSample_MC for MailCenter reports), follow this instructions:

Open the sheet in Excel Select the ribbon 'Data' Select 'Connections'

Select the connection 'Reporting_Cube' Click the button 'Properties'

Switch to tab 'Definition'

Click "Browse" and select the connection file with correct cube to connect to

If you don't have MailCenter integrated:

C:\Projects\AxCMS_ReportingSample\Resources_ReportingSample\Reporting\Configuration\ReportingSample_Cube.odc

If you have MailCenter integrated:

C:\Projects\AxCMS_ReportingSample\Resources_ReportingSample\Reporting\Configuration\ReportingSample_Cube_MC.odc If the cube(s) are located on server different than localhost, you can change the connection details either in .odc files or directly in

Connection string window

Now, press Refresh in Workbook Connections to retrieve new data from cube.

(9)

•

Creating a report in Excel file

Insert a PivotTable (or PivotChart if a chart should be shown with the table). Select to use an external data source and choose the Cube_reportingSample (Cube_reportingSampleMC for MailCenter reports) connection. Next select if you want to create the table on new worksheet or a existing worksheet.

(10)

•

The Privot Table Field List is opened to select values to the Table (and Chart)

Select action value(s) for the report from Privot Table Field List i.e. LS Banner Click Count and LS Banner Impression Count. Numbers of Banner impressions and Banner clicks are shown in table.

Select action info value(s) for the report from Privot Table Field List i.e. Create Date, HourOfDay

(11)

•

Number of Banner impressions and Banner clicks are shown in table and also what hour of the day the actions were made. Left click + hold and drag the HourOfDay option from the Column Labels area to the Row Labels area

The table shows the hour when the action where made as rows.

(12)

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>

(13)

AxCMS.net Help & Support Center > Template Projects > Reporting Template Project <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:

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

</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:

(14)

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;"/>

(15)

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_MC" /> Uncomment the MailCenter dimension tables

</DimensionTable>

</DimensionTable>

Uncomment the MailCenter facts table

<FactTable Name="FactMCSentMail"

SourceQuery="SELECT [To], TargetDate, OrderID , MailID, DateCreated, SendState, SendAttempts FROM {{MCArchive}}" SourceCacheTable="MCArchive">

</FactTable>

After making changes to configurations, please run

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

(16)

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.

(17)

Integrating Reporting to your solution

Copy Reporting directories

First, copy the cube project you want to use:

Copy C:\Projects\AxCMS_ReportingSample\Cube_ReportingSample directory

(or C:\Projects\AxCMS_ReportingSample\Cube_ReportingSample_MC if you need MailCenter integration) to C:\Projects\AxCMS_YourProject\

Then, copy the cube processor:

Copy C:\Projects\AxCMS_ReportingSample\AxDWH_Processor directory to C:\Projects\AxCMS_YourProject\

Then, copy the reporting Excel file and its assets:

Copy C:\Projects\AxCMS_ReportingSample\Resources_ReportingSample\Reporting directory to C:\Projects\AxCMS_YourProject\Resources_YourProject\

Add Reporting Cube to your solution

In Visual Studio, right click Solution AxCMS_YourProject, select Add/Existing Project, browse to the directory of the Cube project you just copied and add it to Visual Studio.

Configure etl.config and deploy new cube

Follow the steps of Configuring Template Project guide with this exception(s):

You should edit etl.config located in C:\Projects\AxCMS_YourProject\AxDWH_Processor\etl.config

and if you already had cube deployed, run axdwh_processor createdatabase recreate console command in AxDWH_Processor directory instead of CreateDatabase.bat

After the cube is deployed and populated, you can work with it from Excel as usual.

(18)

AxCMS.net Help & Support Center > Template Projects > Reporting Template Project • • • • • • • • • • • •

Sample Reports

Excel Sample sheets

The Reporting Solution comes with two sample Excel documents that already contain reports for different actions. At first the reports contain pre-generated data. Afterwards the files can be refreshed with data from cube and reports will show real actions done in Projects.

Reporting Sample actions shown in Sample.xlsx

The following actions are logged in Reporting Sample databases and can be presented in the Sample.xlsx file

LS actions

AxPageViewAction – logged when pages are viewed in browser. Displayed on Sample.xlsx sheets:

Page views over the day - This report shows the number of all page views and how they are distributed over the day PageViews by date and page type - In this report the number of page views and is arranged by the date where the page view happened and the type of the page that was viewed.

AxBannerImpression and AxBannerClick – banner impression is logged when a page whit banner in the content is viewed in

browser. Banner click is logged when banner is clicked. Displayed on Sample.xlsx sheet:

Banner Impressions/ Clicks - This report shows the number of banner impressions/ clicks for the day.

AxSearchAction – logged when Search is used. Displayed on Sample.xlsx sheet:

Search statistics - This report shows the number of searches done for day and how successful they were.

AxVote – logged when a vote is given in Voting module. Displayed on Sample.xlsx sheet:

LS Votes - This report shows the number of votes cast over the day.

AxLoginAction – logged when user logs in on live site. Displayed on Sample.xlsx sheet:

LS User Login - This report shows the number of LS user logins over the day.

MS actions

PublishPageActivity – logged when a page is published. Displayed on Sample.xlsx sheet:

Published pages - This report shows the number of pages published over the day.

PublishDocumentActivity –logged when documents are published. Displayed on Sample.xlsx sheet:

Published documents - This report shows the number of documents published over the day

Mail Center actions shown in SampleMC.xlsx

The following actions are logged in Mail Center Sample databases and can be presented in the

LS actions

AxPageViewAction – logged when pages are viewed in browser. Displayed on Sample.xlsx sheets:

Page views over the day - This report shows the number of all page views and how they are distributed over the day PageViews by date and page type - In this report the number of page views and is arranged by the date where the page view happened and the type of the page that was viewed.

AxLoginAction – logged when user logs in on live site. Displayed on Sample.xlsx sheet:

LS User Login - This report shows the number of LS user logins over the day.

AxSubscribeAction and AxUnSubscribeAction – subscribe is logged when user subsribes to a newsletter and unsubscribe is

logged when user unsubscribes from a newsletter. Displayed on SampleMC.xlsx sheet:

Newsletter registration / deregistration - This report shows the number of all newsletter registrations and deregistrations and how they are distributed over the day.

MS actions

(19)

•

PublishPageActivity – logged when a page is published. Displayed on Sample.xlsx sheet:

Published pages - This report shows the number of pages published over the day.

PublishDocumentActivity –logged when documents are published. Displayed on Sample.xlsx sheet:

Published documents - This report shows the number of documents published over the day MC actions

PublishNewsletterActivity – logged when a newsletter is published. Displayed on SampleMC.xlsx sheet:

Sent Mail – This report shows the number of send attempts and mails sent.