With IBTab, data from a SQL Query from any ODBC compliant database can be exported to a Tableau Data Extract file in TDE or Hyper format using InfoBurst.
In this workflow, Company ABC has "Production" SQL that is used to generate reporting in their organization. Company ABC wants to use the underlying data from the SQL Query as the basis for visualizations to be created using Tableau Desktop and/or Tableau Server. In addition, the Hyper extract needs to be updated daily between Monday and Friday so that Tableau Dashboards based on this data are updated with current data. Company ABC also has a requirement to split a TWBX file using with the Tableau Data Source created using InfoBurst into multiple smaller TWBX files by Product Category.
In this topic, we will cover:
- Creating a Hyper Extract based on a SQL Query against a SQL Server database with InfoBurst
- Hyper Extract Delivery to Tableau Server
- Hyper Extract Delivery to Network Share
- Scheduling the SQL Query Extract Refresh using InfoBurst
Option 1: Watch the Video Tutorial
Option 2: Review the step by step directions
Consider the Stored Procedure below. This stored procedure returns a number of dimensions and measures from the database. Company ABC wants to use the underlying data from this SQL Query in a Tableau Dashboard to create visualizations of the exact same data.
Dashboards will be built using Tableau Desktop and the Web Authoring capabilities of Tableau Server with the data returned from the stored procedure. (Note: The screenshot below only shows some of the columns retrieved from the query)
Steps to Create a Tableau Data Extract with InfoBurst
1. In order to use the SQL Query as the source for Tableau visualizations,the Query must be added to a DB Connection defined in InfoBurst. Open the Database Connection. In this sample workflow, it's named Adventure Works Sales.
Read more about Database Connections in InfoBurst here.
2. The DB Connection will display information about the Server/Instance and credentials used to access the data. This sample workflow is using a database named AdventureWorksDW2012 on the Microsoft SQL Server 2012 database platform. Click New to add a new query.
3. Provide a name for the query and add the SQL statement. In this case, we're executing a stored procedure to return the data. This could also be a raw SQL statement. Click Add to add the query.
4. Before a query can be used in InfoBurst, it must be tested. Click the green arrow icon to test the query.
5. InfoBurst will return the first 500 rows of the query during the test.
6. Select New/Burst to create a new Burst.
7. Click the Green + icon to add a new document. Select the DB Connection and Query and click OK.
8. Delivery to Tableau Server. Click on the Deliveries tab and create a new Blank delivery.
9. Select Tableau Server as the destination.
10. Select your defined Tableau Server platform. In this example, that platform is named Tableau. Check the Replace existing file if the intent is to overwrite the existing Data Extract. Select the Tableau Server Project. In this example, we have a project named IBTab Workflow Examples.
11. Provide a Name for the Tableau Data Source to be published to Tableau Server.
13. In the Format section, select Tableau Data Source as the format. TDE or Hyper can be selected as the file types. Typically the first row of your data should include the column headers. This box is checked by default.
14. Click Add to add the Delivery to the Burst.
15. Click Burst Now to run the Burst.
16. On Tableau Server, the Hyper file created by InfoBurst appears under Data Sources in the specified project.
17. Tableau users with Web Authoring permissions, can use this data source to create a New Workbook on Tableau Server.
18. This is the interface for Web Authoring on Tableau Server using the Data Source generated by InfoBurst from a SQL Query.
19. To use the same data source from Tableau Desktop, connect to Tableau Server.
20. Once you have logged in to Tableau Server from Tableau Desktop, select the Data Source created by InfoBurst.
21. After refreshing the data, we see the rows from the SQL Query used by InfoBurst to create the Hyper Data Extract in Tableau Desktop.
22. Delivery to Network Share. Create a New Burst and repeat steps 1-7 above. Create a New Delivery. Select Network Share as the destination.
23. Select the Destination. The Network Share destinations are configured by your InfoBurst Administrator.
24 For the File Name, for this sample workflow, we'll use SQL Query to Hyper.
25. Select the following Format options.
26. Click Burst Now to run the Burst.
27. Burst has completed successfully.
28. Tableau Hyper file has been created on the network share, in this example D:\Infoburst_output - the Hyper file has been created.
Scheduling a Data Extract Refresh with an Event
Using InfoBurst, a burst can be Scheduled dependent on an Event. For example, a Burst may be scheduled to run early in the morning; however, it may be dependent on an ETL having completed successfully. This can be accomplished in InfoBurst incorporating an Event with a Schedule.
Here are the steps to create a schedule that will refresh the Hyper extract on Tableau Server.
1. First, create an Event by Selecting New/Event from the main menu.
2. Give the Event a name and select File Presence as the Type.
Read more about Creating an Event-Based Schedule in InfoBurst here.
3. Select the file that should be present for the schedule to run. In this scenario, the ETL job would create the file once the data load has completed successfully. InfoBurst can also delete the file once the schedule has run.
4. Test the file presence event.
5. The test should be successful if the file is present. The file can be removed after the test, or once the schedule depending on the event has been created and saved.
6. From the InfoBurst main menu, select New/Schedule.
7. Enter the Schedule parameters. In this example the schedule will run between Monday and Friday at 05:00. The Hyper Data Extract on the Tableau server will be updated with current data from the Webi report which is refreshed when the burst is executed. This topic does not cover adding dependent events to a schedule - for example, not running the schedule until an ETL load completes successfully; however, InfoBurst does support this functionality.
8. Add the File Presence Event. The file would be created by the ETL process once the load has completed successfully. The schedule will not run until the file is present.
9. Select the Burst to add to the Schedule. We're selecting the Burst that publishes the SQL Query Hyper data extract to Tableau Server.
10. Add the Schedule by clicking Save.
11. Schedule has been added.
This is the end of the workflow to create and schedule a Tableau Data Extract in Hyper format from a SQL Query that is dependent on a file being present in a location specified in InfoBurst.