Generating Source Views for...

The Simplicity application offers a range of automatically published views to assist with the needs of your data and marketing solutions. These include views for selecting data, merging data for campaigns, analytical purposes, and reporting. The Customer Interaction Studio module of Simplicity also offers click-to-publish views for online form and survey data.

When querying data, whether through the Simplicity Customer Interaction Studio or directly in the database, your goal will determine what standard view set is most appropriate to use.

Campaign Selections.

The following example will demonstrate how to create views for campaign selections using the automatically generated views as building blocks.

 

Step 1.

 

 

Step 2.

 

  CREATE VIEW vw_cb_StaffList1_Demo AS
    SELECT
      RSN AS LogAgainst_Person_RSN,
      GivenName,
      FamilyName,
      Email,
      Roles
    FROM
      vw_Report_Person WITH (NOLOCK)

 

Step 3.

 

 

Step 4.

 

  CREATE VIEW vw_cb_StaffList2_Demo AS
    SELECT
      vw_Report_Person.RSN AS LogAgainst_Person_RSN,
      vw_Report_Person.GivenName,
      vw_Report_Person.FamilyName,
      vw_Report_Person.Email,
      vw_Report_Person.Roles,
      vw_FlatLoad_Person.Roles_Account_Manager,
      vw_FlatLoad_Person.Roles_CEO,
      vw_FlatLoad_Person.Roles_Consultant,
      vw_FlatLoad_Person.Roles_Director,
      vw_FlatLoad_Person.Roles_Project_Manager
    FROM
      vw_Report_Person WITH (NOLOCK) 
    INNER JOIN
      vw_FlatLoad_Person WITH (NOLOCK) ON
      vw_Report_Person.RSN = vw_FlatLoad_Person.RSN

 

Step 5.

 

 

Step 6.

 

  CREATE VIEW vw_cb_StaffEmployerList1_Demo AS
    SELECT
      vw_Report_Person.RSN AS LogAgainst_Person_RSN,
      vw_Report_Person.GivenName,
      vw_Report_Person.FamilyName,
      vw_Report_Person.Email,
      vw_Report_Organisation.CompanyName,
      vw_Rel_Organisation_EmployerStaff_Person.Employer_Entity
    FROM
      vw_Report_Person WITH (NOLOCK) 
    INNER JOIN
      vw_Rel_Organisation_EmployerStaff_Person WITH (NOLOCK) ON
      vw_Report_Person.RSN = vw_Rel_Organisation_EmployerStaff_Person.Employee_RSN 
    INNER JOIN
      vw_Report_Organisation WITH (NOLOCK) ON
      vw_Rel_Organisation_EmployerStaff_Person.Employer_RSN = vw_Report_Organisation.RSN

 

Step 7.

 

 

Step 8.

 

 

Step 9.

 

 

Back to Top.

Analytical Views.

The following example will demonstrate how to create views for use in the analytics database using user published views.

The Customer Interaction Studio allows a user to publish an analytical or campaign view of survey answers with the click of a button. This automatically prefixes the view with vw_ana in the OLTP database and will be copied into the same view in the analytics database with the use of the bulk copy tool which can be scheduled to run on a desired frequency.

 

Step 1.

 

 

Step 2.

 

 

Step 3.

 

  SELECT
    COUNT(*) AS Entry_Count
  FROM
    vw_ana_SrvyAns_Example_Person_Pg_1 WITH (NOLOCK)

 

Back to Top.

Reporting Requirements.

The following example will demonstrate how to create views for use as report data sources using the automatically generated views.

Report data sources can either be housed on the OLTP or analytics database depending on the report processing requirements. If a report is required to be real-time, a user can use an existing data source in the OLTP database, or create a custom view.

OLTP reporting source

 

Step 1.

 

 

Step 2.

 

  CREATE VIEW vw_rpt_Employer_StaffCount_Demo AS
    SELECT
      vw_Report_Organisation.CompanyName,
      vw_Report_Organisation.Abbreviation,
      vw_Report_Organisation.Website,
      COUNT(*) AS StaffCount
    FROM
      vw_Report_Person WITH (NOLOCK)
    INNER JOIN
      vw_Rel_Organisation_EmployerStaff_Person WITH (NOLOCK) ON
      vw_Report_Person.RSN = vw_Rel_Organisation_EmployerStaff_Person.Employee_RSN 
    INNER JOIN
      vw_Report_Organisation WITH (NOLOCK) ON
      vw_Rel_Organisation_EmployerStaff_Person.Employer_RSN = vw_Report_Organisation.RSN
    GROUP BY
      vw_Report_Organisation.CompanyName,
      vw_Report_Organisation.Abbreviation,
      vw_Report_Organisation.Website

Analytics reporting source

If the report does not to need to be real-time the report data source can be housed on the analytics database. Additionally if you want to store the report data source as a table instead of a view for processing speed, prefix the view with vw_rptx and this will populate the vw_rpt table version of the view overnight with the bulk copy tool. As the bulk copy tool is fully customisable, it can be configured to either copy all data to the table, or only copy changes.

 

Step 3.

 

 

Step 4.

 

  CREATE VIEW vw_rptx_Employer_StaffCount_Demo AS
    SELECT
      vw_Report_Organisation.CompanyName,
      vw_Report_Organisation.Abbreviation,
      vw_Report_Organisation.Website,
      COUNT(*) AS StaffCount
    FROM
      vw_Report_Person WITH (NOLOCK) 
    INNER JOIN
      vw_Rel_Organisation_EmployerStaff_Person WITH (NOLOCK) ON
      vw_Report_Person.RSN = vw_Rel_Organisation_EmployerStaff_Person.Employee_RSN 
    INNER JOIN
      vw_Report_Organisation WITH (NOLOCK) ON
      vw_Rel_Organisation_EmployerStaff_Person.Employer_RSN = vw_Report_Organisation.RSN
    GROUP BY
      vw_Report_Organisation.CompanyName,
      vw_Report_Organisation.Abbreviation,
      vw_Report_Organisation.Website

 

Step 5.

 

  SELECT
    *
  FROM
    vw_rpt_Employer_StaffCount_Demo WITH (NOLOCK)

 

Back to Top.