Are you performing data analysis in Excel? This blog is for you! Bring your Excel spreadsheet files to Bold BI. Explore your business data, prepare a dashboard, and play with its widgets to see the story that lives in your data.
Let’s see how to structure an Excel workbook, integrate Excel files as tables and worksheets, and connect Excel files directly from local and cloud storage to a Bold BI dashboard.
Structured Excel Spreadsheet
The first row values of your worksheet will be considered column names by default in Bold BI. So, provide a unique and meaningful column name in your spreadsheet that will make it easy to identify your data.
File size and types
Bold BI supports connecting to Excel workbooks created with version 2007 and later with .xlsx and .xls file extensions. You can upload a maximum of file size 75 MB.
Getting data from an Excel workbook with Bold BI
Bold BI allows you to connect Excel files from local storage and cloud storage to the dashboard designer. Let’s see how to connect Excel files from a local disk.
To create the Excel data source from the dashboard designer, open the Data Sources configuration panel and select Excel.
Now the Excel connection window opens as shown in the following image.
In the connection window, provide a unique name for your Excel data source and choose the Excel workbook from your local storage.
Excel data sources have an additional Import Type feature. Choose Tables to fetch tables from Excel worksheets. Choose Worksheets to fetch an entire worksheet of data along with tables. The import type feature is only available in later versions of Excel.
Bold BI Excel data connector allows you to connect multiple tables from one worksheet. In the following workbook, Order Details worksheet has two different tables. Now you can connect both tables in the designer.
To connect one or more tables from one sheet, choose Tables in import type. Click Preview & Connect. As a result, a window opens listing the data results as seen in the following image.
Here each sheet of the Excel file will be shown as a table on the left side of the window. You can view the raw data for each table by choosing the table at the top of the right-side window.
Choose the desired columns and click Connect. Now your selected data will move to the intermediate database specially reserved for your tenant. Once it’s done, you will be directed to the Bold BI’s data source designer. The Excel schema is listed in a tree view on the left side of the editing window. Drag and drop the table you want to analyze and save the data source.
Now, let’s see how to connect an Excel file from cloud storage.
Connecting an Excel workbook from cloud storage
To connect an Excel file from cloud storage, select Cloud Storage from the categories list in the data source listing page.
You can connect an Excel workbook available in any of the listed cloud storage with Bold BI.
Let’s start to connect a workbook from Microsoft SharePoint.
Select the Microsoft SharePoint connector from the list. Follow the steps here for authentication with Microsoft SharePoint. After successfully connecting your account, enter a name for the data source and select the site from the drop-down in the window. Choose the file you wish to preview and connect the data.
Click Refresh Settings in the window to trigger the request periodically and keep the data up to date in the dashboard.
In the Refresh Setting window, you can schedule the data to refresh hourly, daily, weekly, or monthly. This will automatically trigger a call to the API configured in the data source to keep the data up to date. If you do not want to sync your new data, disable the Enabled option.
You can import one or more worksheets and tables from cloud storage, also.
Click Select. A window opens listing the data results.
Click Connect. Drag the table and save the data sources. Finally, the created data sources will be added to the DATA SOURCES panel.
Joining multiple data sources
Joining data from multiple data sources is a common requirement for various online applications. One example is using data from Google Analytics and Stripe, which is discussed more in the blog post, “Track Website Conversions and Revenue Together.”
You can create a new data source by joining the already-created two data sources. Bold BI allows you to join tables in two or more different data sources provided they were extract-mode connections.
To combine multiple data sources, use the Shared Tables panel at the bottom of the data source designer.
This pane displays the data sources you have permission to access. By default, the data sources you created previously for this or any other dashboard will be listed here. You can drag them to the canvas, as shown in the image, to join them.
Scheduling data refresh
You can schedule refreshes to keep your data on the dashboard up to date for already created data sources as well.
To update data from an Excel source in local storage, edit the data source. From the data source designer, use the Edit Connection option and select the Excel file, which should be the same source file with updates or a different file name but with the same schema.
With a cloud-stored Excel file, use the Refresh Settings option to create a schedule to move data from your cloud location to an intermediate database location.
Note: In cloud storage, you have to either replace the latest file with one of the same name or edit your existing file and save it with the modified content before a refresh triggers. Deleting a file and then uploading a file with same name will make the scheduler consider that file a new one, which results in data loss of the table created from the old file.
Let’s visualize the product and supplier data using a Bold BI dashboard through the Excel data connection. Also, let’s schedule the data for refresh and see it updated in Northwind Products and Suppliers dashboard.
Visualizing Northwind Products and Suppliers metrics
Refer to the following Northwind Products and Suppliers analysis dashboard whose source data comes from an Excel spreadsheet placed in an online storage like Microsoft SharePoint.
This dashboard showcases product and supplier data through the following metrics, tracking sales performance.
- Total sales
- Order placed by country
- Top 5 products and suppliers by order
- Top 5 categories sold
- Least-demand countries based on order
- Least-sold top 5 categories
The data source is scheduled to update the data every five minutes in the Northwind Products and Suppliers dashboard.
Data is updated in the dashboard every five minutes, as shown in the following image.
The Northwind Product and Supplier Dashboard can be viewed in this demo where it showcased with additional metrics. You can download the Excel spreadsheet used for this blog from Google Drive to prepare the dashboard on your own.”
We hope this blog article guides you in preparing Excel data and integrating it with Bold BI. If you have any questions, please post them in the comments section below. You can also contact us by submitting your questions through the Bold BI website or, if you already have an account, you can log in to submit your support question.