Step By Step Process to Implement Sales analytics through Matillion

PREREQUISITE:-

1. Salesforce Credentials.
2. Matillion Cred.
3. Snowflake DW.
4. Tableau Reports.

Create environment variables for the database name for Sales analytics through Matillion

Go to project=>manage environment variable => add variable name

Step-1:

Create Orchestration Job and use Salesforce query component for pulling data from Salesforce for sales analytics

Step-2:

Use Query to scalar component for incremental loading

Use the below query for checking new data to be inserted or updated

select NVL(max(“LastModifiedDate”),’1990-01-01′) as DATE_LAST_MODIFIED from ${SALES_LANDING}.”SALES”.”L_ACCOUNT”

create a scalar variable for checking conditions on the input column.

Step-3: 

Run job and data will be pulled into transition table that is named as L_Table_name.

Step-4:

Create transformation job using table input and table update.

• Use calculator component for creating expression

• Create two columns in the Account table structure named DW_INSERTED_DATE and DW_UPDATED_DATE

• Map created expression V_CURR_DATE to this column in table update component in update mapping and insert mapping parameters.

Step-5:

Run the job and migrate data from L_Table to Table.

STAGING

• Create Transformation job using table input, calculator, and table update components,

• Create expressions if required in the calculator component.

Run Job and migrate data from Landing_Table => STG_Table.

DIMENSION

• Create Transformation job using table input/SQL, calculator, and table update
• components,
• 

• Create a column for key in structure in snowflake
• 

• Run the job and migrate data from stg_table => Dim_table by generating an auto-increment key.

FACT

• Create Transformation job using table input, calculator, and table update components,

• Create required expression in calculator components.
• Run the job and migrate data from Dim_table => Fact_Table by generating an auto-increment key.
• Here are some examples of the Sales Analytics dashboards,

1. Sales Analytics

\

2. Sales_Contract Analytics