My Sitecore Footsteps

Friday, June 12, 2015

Community Documentation Site for Sitecore

There is a very very useful documentation site which is written by Sitecore employees.

http://sitecore-community.github.io/docs/

Most of you may already knew about this site, but I found it today Only :-D

Hope this will help someone!!

Tuesday, June 9, 2015

Data Flow - Sitecore WebForms For Marketer (WFFM) - Part I

In this Blog post series, I will discuss about the Data Flow of Sitecore 8 - from front-end website to MSSQL Databases

1) Data Flow - Sitecore WebForms For Marketer (WFFM) - Part I
2) Data Flow - Sitecore WebForms For Marketer (WFFM) - Part II

Setting Up :

1) Create WFFM Form

2) Add newly created WFFM form to Item Presentation Details

3) Publish

4) View it in Front Website

5) Empty Forms Reports
As you can see from the below image, WFFM reports does not have any records. So, we will start with a fresh copy of WFFM.

Data Injection :

Enter Data to WFFM form fields and click "Submit"

Data Flow :

1) Front Webpage to Session

User enters data into wffm form and click "Submit" button
You can view the data going from front webpage to server by using Fiddler tool (see Screenshot below)

2) Session to MongoDB database

Sitecore migrate data from Session to MongoDB only when user session is ended.
By default session end time will be 30mins. You can adjust that time by adding "timeout" parameter to <forms ... > tag as below

<authentication …>
<forms name=".ASPXAUTH" cookieless="UseCookies" timeout="2" />
</authentication>

Refer : https://kb.sitecore.net/articles/345947

Empty MongoDB - Before processing any data

As you can see in the below image, there is still no MongoDB collection related WFFM, inside the analytics database

After processing WFFM form data to MongoDB

When the data was processed to MongoDB at the end of the user session timout, a new collection (FormData) is created inside the analytics database.

Below image displays how data is stored into MongoDB "FormData" collection

That is it for the first part.

In the next part, we will discuss some issues/bug with WFFM module and fixes, when migrating content from MongoDB to MSSQL "reporting" database as well as how these data is processed into MSSQL database tables.

see you soon!!

Thursday, June 4, 2015

Sitecore 8 WFFM Data Aggregation Error

Issue :

WFFM Data not aggregated (transferred) from MongoDB to MSSQL Reporting database

Error :

8184 18:38:35 ERROR Error during aggregation.

Exception: System.Data.SqlClient.SqlException

Message: T-SQL ERROR 242, SEVERITY 16, STATE 3, PROCEDURE (null), LINE 231, MESSAGE: The conversion of a datetime data type to a smalldatetime data type resulted in an out-of-range value.

Source: .Net SqlClient Data Provider

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj,

.....

Sitecore Version :

8.0 Update-2 & 8.0 Update-3

Solution :

Adding following configuration (if not exists) into /App_Config/Include/Sitecore.Analytics.Processing.Aggregation.config file

<Table>Fact_FormStatisticsByContact</Table>

<Routine>Add_FormStatisticsByContact</Routine>

</SqlMappingEntity>

Run following SQL on Reporting Database

--------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[Add_FormStatisticsByContact]
@ContactId [uniqueidentifier],
@FormId [uniqueidentifier],
@LastInteractionDate [datetime],
@Submits [int],
@Success [int],
@Dropouts [int],
@Failures [int],
@Visits [int],
@Value [int],
@FinalResult [int]
AS
BEGIN

SET NOCOUNT ON;

BEGIN TRY

    MERGE [dbo].[Fact_FormStatisticsByContact] AS t
    USING
    (
      VALUES
      (
        @ContactId,
       @FormId,
       @LastInteractionDate,
       @Submits,
       @Success,
       @Dropouts,
       @Failures,
       @Visits,
       @Value,
       @FinalResult
      )
    )
    as s
    (
      [ContactId],
      [FormId],
      [LastInteractionDate],
       [Submits],
       [Success],
       [Dropouts],
       [Failures],
       [Visits],
       [Value],
       [FinalResult]
    )
    ON
    (
      t.[ContactId] = s.[ContactId] AND
      t.[FormId] = s.[FormId]
    )

    WHEN MATCHED and (t.[LastInteractionDate] < s.[LastInteractionDate]) THEN UPDATE SET
      t.[LastInteractionDate] = s.[LastInteractionDate],
      t.[Submits] = s.[Submits],
      t.[Success] = s.[Success],
      t.[Dropouts] = s.[Dropouts],
      t.[Failures] = s.[Failures],
      t.[Visits] = s.[Visits],
      t.[Value] = s.[Value],
      t.[FinalResult] = s.[FinalResult]

    WHEN NOT MATCHED THEN
      INSERT(
      [ContactId],
      [FormId],
      [LastInteractionDate],
       [Submits],
       [Success],
       [Dropouts],
       [Failures],
       [Visits],
       [Value],
       [FinalResult]
       )
      VALUES(
      s.[ContactId],
      s.[FormId],
      s.[LastInteractionDate],
       s.[Submits],
       s.[Success],
       s.[Dropouts],
       s.[Failures],
       s.[Visits],
       s.[Value],
       s.[FinalResult]
       );

END TRY
BEGIN CATCH

    DECLARE @error_number INTEGER = ERROR_NUMBER();
    DECLARE @error_severity INTEGER = ERROR_SEVERITY();
    DECLARE @error_state INTEGER = ERROR_STATE();
    DECLARE @error_message NVARCHAR(4000) = ERROR_MESSAGE();
    DECLARE @error_procedure SYSNAME = ERROR_PROCEDURE();
    DECLARE @error_line INTEGER = ERROR_LINE();

      RAISERROR( N'T-SQL ERROR %d, SEVERITY %d, STATE %d, PROCEDURE %s, LINE %d, MESSAGE: %s', @error_severity, 1, @error_number, @error_severity, @error_state, @error_procedure, @error_line, @error_message ) WITH NOWAIT;

END CATCH;

END;
GO

--------------------------------------------------------------------------------------------------------------------

Note : Thanks Sitecore Support for proving the above Fix.