Understanding Flat Files and Their Role in Data Analysis

Optimizing Flat File Performance in Tableau Cloud

Common Challenges and Solutions When Using Flat Files

Introduction to Flat Files in Tableau Cloud

Connecting and Importing Flat Files into Tableau

Improving Data Performance for Flat Files in Tableau

Handling Large Flat Files for Better Visualization

What Are Flat Files? Understanding Their Role in BI

CSV vs. Excel: Choosing the Right Format for Tableau Cloud

Automating Data Updates from Flat Files in Tableau

Troubleshooting Common Flat File Issues in Tableau Cloud

When migrating from Tableau Server to Tableau Cloud, one challenge consistently stumps organizations: how to properly handle flat files? As migration specialists who’ve guided numerous finance, healthcare, and aviation companies through this transition, we’ve seen firsthand how flat file management can make or break the migration process. In this blog, we’ll tackle this common obstacle head-on, providing practical solutions to ensure your data remains accessible and up-to-date in Tableau Cloud’s advanced visualization environment.

Understanding the Challenges of Flat Files in Tableau Cloud

In an ideal scenario, it’s always better to ingest flat files into a database—whether on-premises or cloud-based—and then connect Tableau to the database for faster and more efficient access. However, in practice, business users often rely heavily on flat files for various types of data connections.

Before diving into the issues related to flat files during the migration process, let’s take a moment to understand how Tableau workbooks connect to data sources, especially for readers new to the Tableau ecosystem.

Tableau Data Sources: Published v/s Embedded

In Tableau, there are two primary types of data sources:

Embedded Data Sources

These are stored directly within a Tableau workbook (.twb or .twbx file) rather than being saved as independent files.

Published Data Sources

As the name suggests, these data sources are published on Tableau Server or Tableau Cloud and can be shared with other users.

With that understanding in place, let’s walk through an example.

Suppose a business user creates a workbook using an .xlsx file and attempts to publish the workbook to Tableau Server or Cloud. During the publishing process, the user encounters a prompt: “Include External Files.”

Now, when should this option be checked or unchecked?

Live v/s Extract Connections in Tableau

Before answering that, it’s essential to understand another key distinction in Tableau: Live and Extract connections.

Live Connection

A live connection continuously queries the underlying data in the data source, ensuring that visualizations reflect the most up-to-date information in real-time. No data is copied to Tableau Server/Cloud. Live Connections are not supported with flat files in Tableau Cloud as of now.

Extract Connection

This type of connection involves copying data from the source into Tableau Server/Cloud. The data is static and must be refreshed at scheduled intervals to stay up to date.

To Include or Not to Include External Files?

When the “Include External Files” option is checked, Tableau creates a copy of the flat file (like an .xlsx) and packages it within the workbook. This means the data will not update unless the workbook is manually republished with a new file.

On the other hand, if this option is unchecked, the files need to be accessible to Tableau Server/Cloud, meaning the original file must remain in a location that Tableau can reach.

Here’s where the issue arises: If the user packages the file with the workbook and publishes it, they may expect the data to refresh. However, since the file is static, no updates occur. Often, users only notice this when they attempt to transition the workbook to Tableau Cloud.

Dealing with Flat Files During the Migration Process

This is just one side of the issue. Let’s dive deeper into other potential problems during migration.

Continuing with our example, if the user had packaged the flat file within the workbook and is using an extract connection, they might schedule an extract refresh. However, if Tableau cannot connect to the original file, the refresh fails.

To address this, users typically move the original file to a network drive and adjust the workbook connection accordingly. As long as Tableau Server and the network drive are within the same network, extract refreshes work seamlessly, and the data stays updated.

The Challenge of Flat Files in Tableau Cloud

However, even though this setup works well on Tableau Server, extract refreshes might fail after migrating to Tableau Cloud. This is because Tableau Cloud does not have direct access to private network drives located behind a firewall.

The Solution: Tableau Bridge

This is where Tableau Bridge comes into play. For data sources or virtual connections that Tableau Cloud cannot directly access, Tableau Bridge keeps the data fresh. Bridge acts as a client software that runs within your private network, working alongside Tableau Cloud to ensure data hosted behind a firewall remains up to date.

For example, in our scenario where the flat file resides on a network drive, Tableau Bridge enables Tableau Cloud to connect and update the data. To make this work, the network drive must be whitelisted in Tableau Cloud.

For a more detailed explanation of how Tableau Bridge works, check out this three-part blog series: Bridging the Gap.

Whitelisting Network Drive location in Tableau Bridge

To ensure a seamless extract refresh when using flat file data sources, it becomes essential to follow certain mandatory steps:

Step 1

Hosting Files on a Network-Accessible Share

Centralize the data by storing the flat files on a reliable network-based file share, enabling accessibility for the Bridge Machine.

Step 2

Permissions on Shared Location

To ensure Tableau Cloud can interact with and retrieve the flat files from the shared drive without interruptions, all the necessary access or at least the ‘Read Only’ permission to the ‘Service Account’ should be granted.

Step 3

Connect Using UNC Pathing

It is recommended to always connect to the data using the Universal Naming Convention (UNC) path as it eliminates dependencies on local drives and ensures consistent access across the network for data refresh. A UNC path typically starts with \\, followed by the server name and the folder structure.

Steps to Whitelist Network Drive Location

Step 1

Open the Settings and go to the Bridge tab on Tableau Cloud.

Step 2

To access the files located at \\OperationsDept\Accounting\Excelfile.xlsx, add OperationsDept in the “Add New Domain” option under the Private Network Allowlist.

Step 3

Here in the Pool option, make sure to select the Connected Bridge Machine.

Note: Make sure the ports are open in the Bridge Machine for connectivity with Tableau Cloud.

If the user is unsure of the network file share path, they can find out the same by downloading the data source from Tableau Cloud, then opening it on Tableau Desktop and choosing the ‘Edit Connection’ option for the data source pane.

Note: This shall only work if the user has access to the file’s location. Handling flat files during a migration to Tableau Cloud requires careful consideration of how data sources are managed and connected. By leveraging Tableau Bridge and following best practices, businesses can ensure smooth transitions without sacrificing data integrity.