Chapter 5: Advanced ETL Using CRMA Data Prep

This chapter will demonstrate how to use the CRM Analytics (CRMA) Data Prep tool to its full capability. This is where the more powerful extract, transform, and load (ETL) work will be explained and demonstrated, instructing you in how to combine many sources of data, perform complex transformations and calculations, and create accurate, complete datasets for analytics.

Understanding and applying your data prep and dataflow skills are critical as a foundation for building accurate and useful data analytics in CRMA.

By the end of this chapter, you will have a much deeper understanding of how to join Salesforce and external data. You should be able to apply advanced use cases of the Aggregate node. You will develop an understanding of how to apply complex formula transformations to your data. Going ahead, you will be proficient in working with dates in CRMA Data Prep. You will know the reasoning behind flattening datasets and how to...

Technical requirements

You will need the following tools/software to successfully execute the instructions in this chapter:

• The latest version of the Google Chrome browser (Chrome is the preferred browser when working with CRMA)
• A working email address

Be sure to be logged in to your CRMA dev org.

Advanced use of the Data Prep tool

As you dive into more complex and powerful usage of the Data Prep tool, you will begin with one of the most common requirements for the ETL process—data joins.

The ETL Process

ETL is a data integration process that encompasses three distinct but interrelated steps (extract, transform and load) and is used to synthesize data from multiple sources many times to build a dataset that reflects business rules and meets analysis requirements.

Diving into data joins

We looked briefly at joining and augmenting your data in Chapter 4, Building Data Recipes.
In this section, we will dive much deeper into this important area of data transformation. First, let's begin by understanding the various types of data joins.

Understanding the various types of joins

There are four common ways to join your data, as seen in the following diagram:

Figure 5.1 – The four types of Structured Query Language...

Advanced hands-on exercise

The purpose of this exercise is to create a single dataset that contains a summary of every account that meets all of the following conditions: has open opportunities, at least one activity, and an open case attached to the account.

The resulting dataset should allow the user to determine the following via direct exploration of the lens based on the dataset:

• Account name
• Age of account
• Owner name
• No. of open opportunities
• No. of open cases
• Average case duration
• No. of activities
• Total amount
• Total expected amount
• Total amount (opportunity with line items)
• Total amount (opportunity without line items)

Resources required

Data sources that are available in your CRMA environment will be used for the purpose of this exercise. Please use the datasets found under the My First Analytics App app so that the results will be consistent with the following steps:

• Opportunities
• Cases
• Activities...

Summary

In this chapter, you learned how to use the more advanced ETL tools available in Data Prep and CRMA recipes. You should now be able to join, aggregate, flatten, and transform your data with confidence using the CRMA data preparation GUI. This includes the use of complex and custom transformations using formulas.

In the following chapter, you will learn how to use the data exploration tool known as a CRMA lens, and you will understand how lenses are the building blocks of CRMA dashboards.

Questions

Here is a list of questions to test your knowledge:

• What is the difference between a lookup join, an inner join, and a full join?
• Give one real-world example of how to use a full join.
• Why would you need to connect data from external sources with Salesforce objects? Give one practical example.
• Explain how to combine aggregation and grouping using two data streams in one recipe.
• The Multiple Row Formula option must be selected to perform window functions. What are window functions, and when might you use them?
• What is a flatten transformation, and what is a use case for this?