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  Table and click and drag a   Merge Table onto the canvas
3. In the table's  Properties, change the name to: 
   1. All Company Data
4. Hover over the Companies table and select the  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  Properties tab, under Sort/Group, select the  icon
3. From the  Attributes 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.  Apply the changes
5. Hover over the Companies table and select the  icon
6. Select all of the  Attributes in the list, then drag and drop them onto the All Company Data table
7. Make the Companies table static by hovering over it and pressing the  icon
8. Then  Save the model

Task 3: Deduplication on the in_2 Pipe

1. Hover over the Additional Companies Processed table and select the  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  Properties, scroll to the Sort/Group section
3. Select the  icon
4. From the  Attributes list, select CoID and drag it into the Sort/Group section
5. Close the attribute window
6.  Apply and Close the in_2  Properties tab

Task 4: Set the in_2 Pipe as the Master Data

1. Click on the All Company Data table and  Pin its  Properties tab
2. In the  Properties, under Attributes, double-click on the CompanyID attribute and make the following change:
   1. Expression: _key[1]
   2.  Apply and Close the changes
3. Double-click on the CompanyName attribute and change the Expression to:

ifNull(in_2.Name, in.CompanyName)

4.  Apply and Close the changes

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  icon
2. Hover over the All Company Data table and click the  icon
3. On the message window, click Yes

Task 6: Rollback Recordsets (Optional)

1. Hover over the All Company Data table and click the  icon
2. On the Recordsets window, right-click on the recordset and select Rollback Recordset
3. In the Warning box, choose the Rollback all data option and toggle off  Keep Old Recordsets, then Confirm
4. Hover over the All Company Data table and click the  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