Introduction

Welcome to Analysis Modelling Fundamentals. In this chapter we will learn about designing and creating analysis models including cleansing and enriching data.

Concepts

To find out more about the Analysis Modelling options and design principles before you complete the chapter below, or simply to refresh your memory, see Analysis Models for Batch Processing Data.

Video Demonstration 

Watch the video demonstration, then complete the task list below.

Part 2 - Analysis Fundamentals

Fundamentals Tasks

Complete the tasks below, before moving to the next chapter. 

Note: If you have not completed 5. Analysis Fundamentals - Part 1, it is recommended you do this before undertaking the below tasks.

Task 1: Setup a Merge Table

1. Open the CRM Data Import Analysis Model

2. On the toolbar, hover over 

   Insert excerpt
   _tables _tables nopanel true

    and click and drag a  

   Insert excerpt
   _table_merge _table_merge nopanel true

    onto the canvas
3. In the table's

   Insert excerpt
   _property_settings _property_settings nopanel true

   , change the name to: 
   1. All Company Data
4. Hover over the Companies table and select the

   Insert excerpt
   _pipe_icon _pipe_icon nopanel true

    icon, then click on the All Company Data table to connect the two tables

Task 2: Deduplication on the in Pipe

1. Click on the in pipe between the Companies table and All Company Data table
2. On the in pipe's

   Insert excerpt
   _property_tabs _property_tabs nopanel true

   , under Sort/Group, select the 

   Insert excerpt
   _attributes_icon _attributes_icon nopanel true

    icon
3. From the 

   Insert excerpt
   _attributes _attributes nopanel true

    list, select CompanyID and drag it into the Sort/Group section
   1. Close the attribute window
4. On the in pipe's Basic Settings, change Data to Read to All

   1. Insert excerpt
      _save _save nopanel true

       the changes
   Then  Insert excerpt_save_saveModel_save_saveModelnopaneltrue the model

Task 3: Deduplication on the in_2 Pipe

1. Hover over the Companies table and select the 

   Insert excerpt
   _attributes_icon _attributes_icon nopanel true

    icon
2. Select all of the 

   Insert excerpt
   _attributes _attributes nopanel true

    in the list, then drag and drop them onto the All Company Data table
3. Make the Companies table static by hovering over it and pressing the

   Insert excerpt
   _static_icon _static_icon nopanel true

    icon
4. Then 

   Insert excerpt
   _save_saveModel _save_saveModel nopanel true

    the model

Task 3: Deduplication on the in_2 Pipe

1. Hover over the Additional Companies Processed table and select the

   Insert excerpt
   _pipe_icon _pipe_icon nopanel true

    icon, then click on the All Company Data table to connect the two tables
2. Click on the newly created in_2 pipe, and in its 

   Insert excerpt
   _property_settings _property_settings nopanel true

   , scroll to the Sort/Group section
3. Select the 

   Insert excerpt
   _attributes_icon _attributes_icon nopanel true

    icon
4. From the 

   Insert excerpt
   _attributes _attributes nopanel true

    list, select CoID and drag it into the Sort/Group section
5. Close the attribute window

6. Insert excerpt
   _finish _finish nopanel true

    the in_2

   Insert excerpt
   _property_tabs _property_tabs nopanel true

Task 4: Set the in_2 Pipe as the Master Data

1. Click on the All Company Data table and

   Insert excerpt
   _pin _pin nopanel true

    its

   Insert excerpt
   _property_tabs _property_tabs nopanel true

2. In the

   Insert excerpt
   _property_settings _property_settings nopanel true

   , under Attributes, double-click on the CompanyID attribute and make the following change:
   1. Expression: _key[1]

   2. Insert excerpt
      _finish _finish nopanel true

       the changes
3. Double-click on the CompanyName attribute and change the Expression to:

ifNull(in_2.Name, in.CompanyName)

4. 

5. Repeat the above steps 3 and 4 for the remaining attributes, changing the attribute name each time

a. For example, change the Expression for the Web attribute to:

ifNull(in_2.Web, in.Web)

Task 5: Run Analysis

1. Hover over the Additional Companies Processed table and click the

   Insert excerpt
   _static_icon _static_icon nopanel true

    icon
2. Hover over the All Company Data table and click the

   Insert excerpt
   _run_analysis_icon _run_analysis_icon nopanel true

    icon
3. On the message window, click Yes

Task 6: Rollback Recordsets (Optional)

1. Hover over the All Company Data table and click the

   Insert excerpt
   _recordsets_icon _recordsets_icon nopanel true

    icon
2. On the Recordsets window, right-click on the recordset and select

   Insert excerpt
   _rollback _rollback nopanel true

    Recordset
3. In the Warning box, choose the Rollback all data option and toggle off

   Insert excerpt
   _toggle_off _toggle_off nopanel true

    Keep Old Recordsets, then Confirm
4. Hover over the All Company Data table and click the

   Insert excerpt
   _run_analysis_icon _run_analysis_icon nopanel true

    icon

Further Reading (Optional)

For more information on the topics covered in this chapter, see the below links.

• Merging Tables
• Pipe
• _key - Internal Variables
• Functions
• Rollback Recordsets