ADDING JDBC DRIVER TO MATILLION
September 8, 2021Shared File Transfer Integration through Mulesoft
January 20, 2022Step By Step Process to Implement Sales analytics through Matillion
PREREQUISITE:-
- Salesforce Credentials.
- Matillion Cred.
- Snowflake DW.
- 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,
- Sales Analytics
\
2. Sales_Contract Analytics