Chapter 3: Connecting Your Data Sources

Now that you are familiar with the CRM Analytics (TRCM) user interface and have built your very first TRCM analytics app, you are ready to dive deeper into this highly capable business intelligence platform. In this chapter, you will learn how to ingest data into CRMA from various data sources. The diverse capabilities and limitations of CRMA in bringing in data from Salesforce, flat files, data warehouses, and other sources will be examined and explained, along with the frameworks and tools that enable this process. You will learn how to connect Salesforce data objects with CRMA and create datasets. You will also be instructed on how to bring a flat file into CRMA.

So, what will you be able to do by the end of this chapter? Well, first, you will clearly understand how to bring data into CRMA. Second, you will recognize the limitations of CRMA regarding data connections. Third, you will understand how to bring Salesforce data into CRMA....

Technical requirements

The following is required to successfully execute the instructions in this chapter:

• A laptop or desktop with internet access (a tablet or a phone is not sufficient).
• The latest version of the Google Chrome browser (Chrome is the preferred browser when working with CRMA).
• A working email address.
• Make sure you are logged in to your CRMA dev org, as demonstrated in Chapter 2, Developing Your First OOTB Analytics App in CRMA.

The capabilities of CRMA for data connections

CRMA offers you a great many options regarding the data that you can extract, load, transform, and analyze. This section will introduce you to the framework and tools that CRM Analytics provides for data ingestion and transformation. The resulting datasets will drive the lenses and dashboards that give your users the business insights they require.

In CRM Analytics, data connection and integration involve ingesting and preparing the Salesforce data and external data you want to visualize and investigate. External data is information that lives outside of the Salesforce org that is connected to your CRMA environment. This includes data from another Salesforce org, external applications, flat files, and other databases. After ingesting, connecting, and transforming the data, analytics users can analyze and explore it using CRMA lenses and dashboards.

Data preparation is the process of transforming your data into a form that is useful...

The limitations of CRMA for data connections

You have learned what can be done in CRMA as far as data integration goes. Now you will learn some limitations of data integration, as demonstrated in the following list:

• The results stored in Salesforce formula fields only update in CRMA datasets when full replication is performed for the relevant object.
• The current limit for data sync (according to the Spring '21 release) is 100 objects.
• Data sync only performs one sync per object. This is important if you need to perform multiple data syncs from one object.

  Important Note

  The Tableau Prep Builder can be used in cases where CRMA does not have the required ETL capability. For more information, please refer to https://www.tableau.com/products/prep.

Now it's time to create your first custom dataset using Salesforce data.

Bringing Salesforce data into CRMA

This section will teach you, in detail, how to ingest Salesforce data and create a dataset that is useful for analysis and exploration. Before you build your first dataset, you need to consider dataset design.

Designing a dataset

CRMA data is stored in inverted index structures. Ideally, you should start with the lowest, most granular level (that is, the child) and follow a path up to the parent table hierarchy, which constitutes a dataset answering the business case in question. You can have multiple datasets and augment to one large dataset, or you can calculate and aggregate on the fly using SAQL in the dashboard editor.

It is critical that you understand and work with the dataset as they pertain to questions of a business case nature. That is, when designing your dataset, consider the following:

• Avoid the notion of building a data warehouse in one dataset.
• At the same time, a dataset is not meant to be the substitute of...

Augmenting your Salesforce data with flat files

Now, you are going to use a CSV (flat) file to augment your Salesforce data by performing the following steps:

1. Go to your Analytics Studio home page, navigate to My First Analytics App, and click on the option to create a new dataset.
2. This time, choose the option for CSV File.
3. Go ahead and select a file (any CSV file will do for this exercise). Then, click on Next, name your CSV dataset, and click on Next.
4. You will arrive at the user interface for editing the dataset field attributes, which should look similar to the following screenshot:

Figure 3.14 – Editing the dataset field attributes

5. Select each field/column and edit the field attributes, as required, before uploading the file to create the new dataset.
6. Once this is done, click on Upload File and the dataset will be created. It is now ready for exploration, transformation, and combination.

   Note

   One common error...

Common questions and challenges

Here are some common questions and challenges that you might encounter when creating a dataset from Salesforce or CSV data:

• If I make a mistake in the dataset builder, can I reopen it and make corrections? No, you cannot reopen the dataset builder once you have chosen to create the dataset. Changes will then need to be made in the data prep tool.
• I made an error in editing the field attributes when uploading a CSV file, and now I want to make changes – how do I do that? You must make such changes in the CRMA data prep tool; you cannot return to the field attribute editing dialog box.
• When augmenting a Salesforce object in the dataset builder, what if I need to make an inner or outer join, as opposed to a simple lookup or left join? This cannot be performed in the dataset builder; it must be done in a data recipe or in the dataflow builder.
• I have augmented CSV data to a dataset, but I need to update that CSV data. What can...

Summary

In this section, you learned how to extract data from various sources and bring it into CRMA. You should now have a good understanding of the capabilities and limitations of CRMA when it comes to bringing in data from Salesforce, flat files, and other data sources, as well as the framework and tools that enable this. You have been taught how to create datasets from Salesforce data and CSV files, and you have performed hands-on exercises to do this for yourself. These lessons have formed the basis for your expertise in transforming data and building dashboards in CRMA.

In the following chapter, the process of creating a data recipe in CRMA will be defined and explained. Then, beginning with the dataset created in this chapter, you will be able to apply filters, calculations, and transformations to that data in order to create a new dataset that meets the requirements of a business use case.

Questions

Here are some questions to test your knowledge of this chapter:

• What is meant by data sync?
• Name five remote data connections available for CRMA.
• What is the dataset builder used for – to create a dataset from Salesforce data, from external data, or both?
• When working in the dataset builder, why is it important to ensure that you select any ID fields that will be required to add object relationships?
• What common error is given when editing dataset field attributes?