Did you know that with Tableau you can empower your business with the ability to explore data in a trusted environment? With its excellent reliability, Tableau is definitely something you should be thinking about and adding to your arsenal of tools.
Setting up file-based data sources (Excel, CSV, etc.) to automatically refresh on Tableau Server is particularly difficult for several reasons. Thus, in this blog I am going to explain the steps required to auto-refresh your file-based data sources.
Before beginning, set up your Microsoft account. Once your Microsoft account is set up, then go into OneDrive and upload the Excel file which we will be using as a data source for Tableau.
File-Based Sources
Before we start, let us define file-based data sources. These file-based data sources are different from database data sources in that there are physical files that are available on your computer (or some shared network-based drive). As these are physical files, you can email, move, or copy them like any other file. To connect those files in Tableau, you simply choose from the list of file connectors, then select your file.
It is always a best practice to access your data from a real database as the situation allows, as those databases tend to be the “system of record” for the data. Files that have been traded from the database get obsolete very quickly. In any case, now and then you must choose the option to work with a file-based data source. Unfortunately, as noted above, there are several steps required to ensure that Tableau Server can automatically update these data sources.
The 3 Keys of Auto-Refresh
There are three main things you should do while attempting to set up Tableau Server to auto-refresh file-based data sources:
- Store the files [SharePoint or One Drive] on a network-based file.
- Ensure Tableau Server has access/permissions to the files
- Set Refresh Schedules task at Tableau Server/Online side
Make the connection and set up the data source
1. Start Tableau and under Connect, select OneDrive and SharePoint Online. In the tab Tableau opens in the default browser, follow these steps:
a. Enter your email address and then select Next.
b. Enter your password and then select Sign In.
c. Select Yes to grant Tableau access to your OneDrive files and SharePoint Online document library files.
d. Close the browser pane when notified to do so.
-
-
- Search for or select the file to connect to, and then select Connect.
-
2. On the data source page, do the following:
a. Select the relevant file from OneDrive.
b. Once you have selected the Excel file you will be able to see all available sheets in Excel file. Select the sheet which we want to use as a data source.
c. Click on extract option in data source pane now that selected file will get extracted save it.
d. Once extraction is done Select Publish Data Source Option from Server tab.
e. Publish it on Tableau Server/Online with Unique name.
3. On the Tableau Server/Online side, do the following:
a. Open the Data Source which we published
b. Go in Edit Connection, once you select this one new window will appear, select the Embedded option with our Microsoft mail id and save it.
c. Open Refresh Schedules task, here you will be able to see different scheduling options like daily, weekly, monthly. Select one of them as per our data refreshing frequency with time and save it.
d. Open the new workbook on Tableau Desktop/Server/online click on connect to data and select the data source which we recently published, and you can develop the report with this data source.
There you have it – the process you need to follow in order to auto-refresh your file-based data sources. I am going to write another blog on why we need to implement this and the various scenarios this needs to be implemented in. Stay tuned for it!
Write to us with your thoughts about this blog and visit us at Nitor Infotech to know more about how we work with data analytics.
