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 take the course below, or simply to refresh your memory, see Analysis Models.

Video Demonstration

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

Part 1 - Analysis Fundamentals

Fundamentals Tasks

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

Task 1: Download Learning Centre Files

Access the Learning Centre using the Help link in the top right corner of PhixFlow or via Learning Centre
Scroll to the bottom of the webpage and in the Sample Datasets section, download the files:
- Companies
- Companies Additional

Task 2: Create the Analysis Model

Select Analysis from the PhixFlow home page
Click Add New
On the Create Analysis Model window that appears, enter the name, CRM Data Import, and select Create

Task 3: Add a File Collector

Add a new File Collector by hovering over Files in the toolbar and clicking and dragging the File Collector icon onto the canvas
1. In the Properties on the right, enter the name, Additional Companies, and Apply
Hover over the Additional Companies File Collector on the canvas and in the pop-up menu, click the icon
In the Upload File window:
1. choose Select
2. find the Additional Companies file on your PC/laptop
3. click Open
4. click Upload File
An Additional Companies table will be automatically created and added to the canvas
1. this table does not yet hold the data you have uploaded, only the attributes (column headers). This allows any changes to be made to the attributes first which affects how the data is imported

Task 4: Setup the Additional Companies table

Click on the Additional Companies table and Pin the tab
In the table's Properties, in the Attributes section, make the following changes:
1. double-click on the attribute TurnOver and change:
  1. Type: Decimal
  2. Decimal Places: 2
  3. Apply and Close
2. click and set up the following new attribute:
  1. Name: Source
  2. Expression: "Additional Company Data"
  3. Apply and Close
3. Move the attribute Source to position 13 in the attribute list
  1. do this by dragging and dropping Source over Status
  2. A message will show asking to confirm if this table attribute should be moved - select Yes

Task 5: Import the Additional Companies data

To import the data from the File Collector to the Table, hover over the Additional Companies table and select
Confirm that you want to Run Analysis by pressing Yes on the message
1. A Database icon will show on the table to indicate it now holds data
View the imported data by hovering over the Additional Companies table and selecting
On the Analysis Model toolbar, press Save

Task 6: Add the Companies table

In 2. ERD Fundamentals you created a Companies table - this can be added to your Analysis Model by clicking the Table icon in the toolbar
1. If you have not completed 2. ERD Fundamentals, repeat the relevant steps in Task 2-5 above to import the Companies table
The list of tables in your app opens in the Repository on the right
Click and drag the Companies table onto the canvas
1. To see the File Collector for the table, hover over the Companies table and press

Task 7: Enrich the data

Hover over the Table icon in the toolbar and drag a Calculate Table onto the canvas

In the Basic Settings for the new table, name it: Additional Companies Processed
Apply the settings
Connect the tables by hovering over the Additional Companies table and clicking on , then click on the Additional Companies Processed table
Hover over the Additional Companies table and select the icon to list all of the attributes for this table
Copy these attributes by selecting them all, then dragging and dropping them onto the Additional Companies Processed table
Click on the Additional Companies Processed table and in the Attributes section on the Properties tab:
1. click the More Options icon, then click Refresh to show the attributes
2. add a new attribute by clicking the button and giving it the following settings:
  1. Name: ImportDate
  2. Type: Datetime
  3. Expression: now()
  4. Apply and Close the changes

Help boxes appear after typing any function. Click Learn More on the Help box or click the Help icon above the Expression box to see more.

Task 8: Enhance the Additional Companies Processed table

In the Additional Companies Processed table, on the Properties tab make the following changes to the attributes:
1. Double click on the Name attribute and change the expression to:

trim(in.Name)

b. Double click on the Web attribute and change the expression to:

ifNull(
     // If a website is provided use it
     in.Web,

     // ELSE, if it is NULL, use
     "https://www.google.com/search?q=" + replaceAll(_out.Name, "[ ,.]", "")
)

c. Apply and Close the changes

d. On the Analysis Model toolbar, press Save

Task 9: Make the tables static

Hover over the Additional Companies table and select the icon
Hover over the Additional Companies Processed table and select the icon
1. Confirm you want to Run Analysis by pressing Yes to the message
2. Once the Database icon shows on the table indicating there is now data in the table, hover over the table and press to see the results of the expressions you added in Task 8
Save the model

What does the Static flag mean?

When Run Analysis is selected on a Table, the tables preceding it are run first, so that the data contained is the most recent. When there is no need to run all of the tables that are connected, e.g. you have a static dataset which will not change, you can make a table or pipe Static. This ensures that the data in that table will not change - unless Run Analysis is selected for that specific table.

Browser not supported