Tableau has a myriad of direct connectors to different data sources, including a convenient Google Analytics connector (it”s still using Core API v2 instead of the new v3 though). However, what Tableau doesn’t have is a direct connection to Adobe Analytics (formerly Adobe SiteCatalyst and Omniture). In this post, I’m going to show you a relatively simple workaround to building an automated SiteCat dashboard using Tableau Online or Server, especially if you are not ready for the full featured data warehouse options.
There are many ways to get data out of SiteCatalyst including downloading from the interface, extracting from Ad-hoc Analysis (formerly Discover), creating Data Warehouse requests, or utilizing the Report Builder add-in for Excel. Consequently, there are three main options for an automated data pull from SiteCat:
- Setting scheduled Data Warehouse requests to an ftp and then feeding these data pulls into a data warehouse.
- Scheduling Report Builder to send out refreshed data to a list of recipients.
- Utilizing Report Builder and Tableau Online (or Server) to create dashboards that update periodically with refreshed SiteCatalyst data.
The first option is suitable for massive data and reporting needs; the second for raw data pull and basic Excel chart. The third option gives you the full visualization power of Tableau Desktop while avoiding dependencies on data warehouse developers. (fyi, I’m using Tableau Desktop and Tableau Online. Also, our executable file is a .vbs file scheduled to run daily.) Now let’s do it!
1. Create a Report Builder request to pull SiteCat data into an Excel file(s).
To install Report Builder, sign in to SiteCatalyst, go to Analytics –> Report Builder, and follow the instruction. This should install the COM add-in in Excel.
Go to Excel, click on the Add-ins Tab, sign in to SiteCat, then click Create. Here you can specify your report suite, segment, type of report, time frame, granularity. Make sure to choose either rolling dates or dates from cell so the data refreshes according to the refresh date. You can use customized expressions (see below) for rolling dates.
Click Next, and in the next window you can select metrics, dimensions, and how you want your data to be look like. You can format and filter data by clicking “Format” or “Top 1-10” (the default filter) in the Row Labels next to the dimension names. Hit finish when you’re done.
Wait for the data to be retrieved. Feel free to tweak the requests until you are satisfied. For Tableau purposes, each sheet should only have one request.
2. Create an Excel macro to refresh all Report Builder requests.
The first step to running report builder was to create a macro to run it.
To setup your excel workbook we’ll begin by enabling developer mode in excel. To start, click the button in the upper left hand corner of excel as you would to open a new workbook, only we’re going to select “excel options”. You’ll want to make sure you have a check next to the “Show Developer tab in the Ribbon” option. From here you’ll want to click on the developer tab (it’s right after view along the top of your excel application). Once on that tab, click on “View Code” (towards the middle of the available options). This will launch a new blank space for your code to go. For ours, we put the code into a module which can be accomplished by right-clicking the VBAproject, mousing over “insert” and selecting “Module”. Once there, simply paste and save your code/workbook.
The following code was found on the adobe website here
I’m not going to walk through most of this code since it’s not something I wrote, however I do want to point out the line UnProtectAll (line 2) of the macro. We were having some trouble getting report builder to run due to the sheet being in a “Protected” state. This is another macro I can’t take credit for, but if you google “UnProtectAll macro excel” you should be able to find code similar to the macro we’re calling. If you’re doing this from an Unprotected workbook/worksheet, then there should be no need for this code.
3. Create Tableau data sources with these files and publish them to Tableau Online or Server.
Open Tableau and connect to the appropriate Excel file. Choose the extract all data option. Once you have all of your data extracts, right click on each of them and select Publish to Server. You can select a project, rename the data source, change authorization before hitting Publish.
4. Connect to the published data sources and create your dashboards.
You can close all of the extracts as soon as you publish them. Go to the Data window and the published data sources should be on the right hand side. Connect to all the appropriate ones to build your dashboard. Make sure that you are using the published data sources and not the original data extracts.
Once completed, publish the dashboard to Tableau Online by going to Server –> Publish Workbook. You are almost done! The last step is to schedule Report Builder to refresh and push the data to Tableau.
5. Creating a .vbs file to refresh the data requests and push the data to Tableau Online.
Creating the vbs file was the most challenging part of this project for us due to the difficulty we had testing it (the file we worked with was large and it took report builder roughly 15 minutes to run per trial). The code is posted in its entirety here.
Running the Macro
Jumping right in, the first part of this project was developing a script that could run the Report Builder macro. I had a few options for languages, but ended up going with VBScript (I was more familiar with this than Batch Scripting, and this seemed to have more support and more documentation out there to get it to interact with Excel). So I setup my .vbs file and began with my first subroutine RunMacro(). There are 4 main areas of interest here:
1) On line three, you’ll want to update the FILEPATH\FILENAME line to point to your excel spreadsheet. The extension should stay the same.
2) Line 5 you’ll need to update FILENAME with the name of the excel spreadsheet you are using.
3) Line 7, WScript.Sleep puts the program to sleep while report builder runs. The number that follows this is in milliseconds, so adjust accordingly. You’ll need report builder to completely finish before Saving and Closing, otherwise you’ll end up with some blank pages and pushing bad data!
4) xl.Quit quits all open instances of xl. If you have other workbooks open and you want it to stay that way, consider removing this line. The sheet itself should still close.
Pushing to Tableau
Once we had report builder running from our script, the next step was pushing the data to tableau.
The second subroutine is PushData(). To do this, we need to run tableau from a command line. The first two lines of this script (16 and 17) set all of that up for you. It’s worth noting that the current directory needs to point to a Tableau 8.1\bin. This is currently set to where tableau default installs, but if you have it installed in a different directory you’ll need to update accordingly.
Line 18 is where we handle the actual push. This was a little long to screen shot, but there’s a bunch of stuff you’ll want to update here as pointed out in the code (anything in caps will need to be updated with your information). You can also set up this portion of the code yourself by visiting the tableau site here, then just paste in the working version after cmd /k (this sets it up for the push).
Calling the Subroutines
The final part of all of this is just calling the subroutines themselves.
At that point, the macro will run then push the data to the tableau server!
6. Schedule the .vbs file to run daily/weekly/hourly or as you wish.
On your computer, go to Task Scheduler, click “Create Task” and give it a name.
Go to the Trigger tab, click on “New” and modify the refresh schedule as desired (mine is set to refresh daily at 2am). Go to Action, click “New” and choose “Start a program.” Use Browse to navigate to the .vbs file and hit ok.
Feel free to tweak the task until it fits your needs.
And you’re done!
Now every day at your specified time the Excel file will open, refresh the SiteCat data pulls, save, then push it to Tableau published data sources. Any dashboards that use these data sources would be automatically updated with the data. Congratulations, you’ve set up a “direct connection” from Tableau to SiteCatalyst. The data will be as fresh as your scheduled updates.
Thoughts, questions? Email us at email@example.com and firstname.lastname@example.org
Thanh-Trang Hoang-Le, aka T., is a Data Analyst at MaassMedia. She has experience with data analysis, statistics, and data visualization. T is Tableau Desktop 8 Qualified.
Dylan Renzulli is an Associate Analytics Engineer at MaassMedia. He has experience implementing analytics tools such as Google Analytic and SiteCatalyst, and is proficient in using Test&Target.