Use Tableau dashboards and write data to a database

That’s right! Enter any value in your Parameter field(s) and have data written to a database (choose your flavor).  Keep history, study filter trends (and timing), leverage it as a form-enabler and/or data analytics tool. Now Tableau *can* take user input and write that input to a database.

I’ll let that soak in…Filter/Parameter values being written back to a database.

tableau-database - Page 1

Okay, here’s how to do it.

Step 1

Make a Dashboard and toss some Parameters on it (can be any data type). NOTE: you can also use filters because that data is also logged. But, parameters are fun because so many people think they aren’t dynamic.  It’s time to prove they are, in fact, very dynamic.

Step 2

Use the logs.

Every event performed on a dashboard/workbook is logged. It’s just a matter of finding the right key/value pair, parsing, and then adding to a database. For example, if I have a free-form parameter value and I enter, ‘2,000,000’, it is logged along with the parameter number (note this is different than your parameter caption) as well as user, session, request, and a bunch of other stuff.

This means you can keep a history of who changed the parameter, the old value, the new value, and a whole bunch of other stuff.

Step 3

Create some tables in your database. (NOTE: you only do this 1x)

This is simple if you just want to keep a log of the changing parameter values. If, on the other hand, you want to keep performance metrics, track which workbook/sheets are being used, then I’d recommend you also parse and create analysis on the Apache logs. I’ve talked about this a lot here, here, here and here.

Step 4

Parse the logs and add to the tables.

Simple since the main log is in json format. Use some PowerShell and you can do this in a simple function/module: 1 for pulling the log data and 1 for adding to a database.

If you have a log analytics tool in place, this becomes insanely easy since all your logs are centralized (they are, right?!). If not, you can just parse the log file in the default directory.

Step 5

Enjoy!

Use Cases

  • As a light-CRM
  • Data ‘picker’ tool for template dashboards (for example: choose between line/bar chart, etc and data gets written to database and picked up by another script that makes the workbook for you, based on those choices).
  • Replacement for embedded forms
  • Full cycle analytics (from data entry to data analysis, quantified self stuff)
  • Triggers (for example: User A picks Parameter B and, a csv and/or png file is delivered)
  • Usage data about dashboards (for example: if you create 15 parameters and users only ever use 2, then you can remove the unnecessary stuff).
  • Permission approval form (for example: User A request permission to Dashboard B)
  • Subscription forms
  • Form for filtered queries (for example: use fields to help analysts who might not know SQL to create custom SQL based on set fields/values)
  • And more!

A.I.M. for Success with Logentries and Tableau

#Data16 may be over and the Server Admin session may have ended but don’t let the fun stop there. Continuing with the recommendation and urgency of making sure you monitor your Tableau/Analytics infrastructure, Logentries and I have teamed up on a Whitepaper regarding all things Alerting, Integrating and Monitoring.

You’ll find a very through analysis of the *why* it’s important to have a strategy in place as well as tips/tricks and recommendations for further reading. What’s more, you’ll find out how easy it is to get a variety of log data back into Tableau for deeper analysis.

So, get the Whitepaper and spend Thanksgiving implementing it. Just kidding. Take a break for Thanksgiving and then do this 🙂

Best,

Mike

 

Hello Analytics Engineering

I’ve talked a lot about Analytics and how it must be re-imagined for today’s often frantic pace of innovation in both technology and theory. What’s typically missing is the other side of the Analytics coin: Engineering. Most people tend to forget that before one can either explore or view some sort of analytics, there is a lot of movement that must go into preparing that data. This doesn’t even include ‘Self Service’ Analytics which is another story in and of itself!

You often hear: “Well, I just want all the data, I don’t care how hard it is.” Which translates to: “I don’t know what I want but tons of potentially useless data might get me an answer.”

Enter Analytics Engineers.

The data world is modular and in constant flux. One must be able to adapt, move data and present a tool in the most efficient and scalable way possible.

Enter Analytics Ops.

In order to do that, there has to be a ‘glue’ that can hold all the pieces of the Analytics/Data world together. That glue is #PowerShell and I’m thrilled to say that I’ve been selected as a speaker at the Global Devops #PowerShell 2017 summit. Twice!

I’ll be speaking about a different use for PowerShell and one that I’m pretty excited to share with the world. Basically, PowerShell and Data go very well together.

So, here are the two sessions I’ll be speaking at:

Session 1

Time:

Tuesday at 3pm PST:

Title:

Operation Float the Bloat: Use PowerShell for Tableau Server QA and Alerting

Abstract:

Business Intelligence and, generally speaking, the data landscape is a mixture of moving parts, sometimes so many that it’s hard to keep track of what process does what. An Enterprise BI platform is just that, a platform. Within that, we’re dealing with data from APIs, databases (relational and non-relational), text files and many more data variables. Missing from the Analytics infrastructure, however, is a proper log analytics and QA strategy. How do you know your platform is performing as it should be? How do you know data is secure? How do you streamline analytics so users are left with correct and fast data? How do you ensure users are publishing quality content at scale? In this session, we’ll show you how to do all that and more with Tableau Server and PowerShell by focusing on three pillars: Alert, Integrate and Monitor. We’ll use PowerShell and custom functions to ‘Garbage Collect’ old content and archive on Amazon S3, we’ll leverage log files from both the BI Platform and our servers (Windows and Linux) to monitor and maintain the condition and health of the analytics infrastructure. We’ll use PowerShell to easily convert the analytics data (worksheets and views) into a medium upon which we can change anything. We’ll also use PowerShell to dynamically create content in Tableau based on a configuration file. Oh, and, all of this is automated because PowerShell can make it so.

Session 2

Time:

Wednesday at 10am PST

Title:

Using PowerShell for Analytics Engineering (or why PowerShell is the glue for data, big or small).

Abstract:

While there are numerous and exceptional benefits to using PowerShell as an IT Pro and Developer, the hidden gem is its capability for Business Intelligence and Analytics Engineering. In simple terms, it is the lynch-pin of data and analytics. In this session, I’ll demonstrate how PowerShell is used to load, query and aggregate data for Business Intelligence platforms, specifically Tableau Server. What’s more, we’ll automate everything from AWS EC2 instance provisioning, report generation, report delivery, and Log Analytics. Want integration too?! We’ll show you how to reach into pretty much anything via APIs using tools like Chocolatey, cURL, WMI , Git, and Remoting. Oh, one more thing. We’ll use PowerShell classes along with scheduled jobs to make platform administration simple and stable. In the end, we’ll have modules, functions and custom scripts. Adding PowerShell to your data toolbox will provide enormous benefits, not the least of which is adaptability in the rapidly changing data landscape.

 

 

Hope to see you there!

-Mike

Stop a Tableau Extract Refresh without stopping Server

Oh no! I kicked off this Tableau extract and I need to take it back!! How do I stop it?

As long as I’ve been using Tableau, the ‘run-away’ extract has always eluded me. Sure, there’s netstat and procexp that can sort of get you there in a pretty raw way. If it’s late at night and there’s just one job running, that’s pretty easy to find the port and process ID (netstat -ano in PowerShell 3+).

bye-bye-ts-2
Oops. We didn’t want to run that one.

The challenge is when you have multiple backgrounders (on multiple machines) and they’re all active; you then start asking: which one is it? Hopefully, you guess correctly and, voila, it’s gone.

Until now.

bye-bye-ts-1
The process id for the currently running extract

Here’s what we’ll do:

1.) grab the log from this directory: C:\ProgramData\Tableau\Tableau Server\data\tabsvc\vizqlserver\Logs and follow (or parse) this log: backgrounder*.txt

NOTE: If you have a Log Analytics strategy, you can easily follow this log file and leverage some of the vendor’s command line tools to make this process entirely automatic.

2.) Wrap your code in a PowerShell function which allows you to enter your TWB/TDS name.

Stop-TsExtract -ExtractName '<some name>'
bye-bye-ts-3
The result of stopping the process.

3.) The file in #1 is JSON so it’s super easy to parse and dig out the ‘PID’ which, believe it or not, corresponds to the process on your Tableau Server box. We’re going to look for this key/value pair:

k="ds-parser-connect-extract" AND v.caption=<name of your data source/workbook>

NOTE: You could also look for the session via “k=lock-session” (which you then have to correlate from the other backgrounder log file) but this next value gives you the ability to grab (and enter) the data source/workbook name.

4.) Now, if you’ve set up PowerShell remoting or SSH, you remote into your Tableau Server (via Invoke-Command) and enter (where the variable procID is the backgrounder process):

gps -Id $procID | kill -Verbose

 

BONUS

If you remember, I posted about refreshing your Tableau extracts via Slack here. Well, the next step (if you want to open this up outside the admin group) is the let users drop the name of a data source/workbook in a Slack channel. Those background jobs they enter will be stopped if they’re accidentally started.

Oh, and, I should mention: test, test, test.  This isn’t supported by Tableau, but people believe it should be:) Make your vote count.

Tableau Server Performance fun with Slack and Logentries

The Beginning

It started as a joke. Then it became a pretty good idea.

You see, often times Tableau Server admins get a lot of flak. We *constantly* get comments stating:

  • Tableau is slow
  • This workbook is slow
  • Tableau can’t do this because someone I know said as much
  • I like <insert silly dashboard tool here> more because it does <something I can’t remember right now but I’m just trying to sound smart>
  • You’re just an analyst so you don’t know how to optimize data
  • and more!

Let’s be honest, the above comments can, 99% of the time, be tied to someone who designed something incorrectly but wasn’t aware of the implications of said design. Until now 🙂

And in a natural way that a Slack conversation can allow, the comment dropped: ‘It’s like a dumpster fire’

Inspiration!

It goes like this:

  • Slow TWBs trigger alerts all the time on our platform (Server admins should know these bottlenecks already)
  • We pull log data (yes, you can also pull from Postgres but logs are so rich) for those queries via Logentries
  • We parse the log data and convert to the unruly string data into something usable (thank you PowerShell and, specifically, ConvertFrom-String)
  • At an interval of our choosing, we drop the results in Slack (only our team) with a mixture of funny GIFs (because levity is a good thing)
  • We analyze and reach out to the workbook owners for learning opportunities & improvement

Details

ts-slow-content-2
Monitoring the 90th percentile of workbook load time

 

This is the trigger and the cause of much confusion around what Tableau can actually do. You see, if the performance concerns aren’t addressed, every Server admin is going to get the ‘Tableau is slow’ argument. At that point, you’re left defending the platform you set up. But, the question and concerns should all be about what is *causing* Tableau to appear slow.

We address the performance concern with a solid Log Analytics strategy. The above image is one of many examples of alerts we’ll get. This time, we’re going to leverage the Logenties CLI to automatically pull this info out. Yes, automatically.

Here’s what we’ll use:

lecli query -n vizql -q slowwb -f $start -t $end | Out-File -FilePath $workpath\slowwb.txt -Force

The start and end variables are timestamps; we usually do a rolling 24 hours.

ts-slow-content-0
The output of the Logentries query. Not optimal for parsing/sorting, etc.

If you haven’t explored PowerShell’s ConvertFrom-String cmdlet, you’re missing out. It’s pretty remarkable what it can do with a template file and some string data. And it’s based off of some solid (and profound) research.

ts-slow-content-1
Example template file for ConvertFrom-String. We’re implicitly defining some structure and letting the cmdlet work its magic

After you have (1) pulled the log data and (2) set up your template file, run this:

ConvertFrom-String -InputObject $((gc $workpath\slowwbclean.txt)|Out-String) -TemplateFile $workpath\template_slowwb.txt | select Name,Value

Once you do that, you get a beautiful PowerShell object for which the possibilities are endless (well, as much as you want them to be).

So that string data from above is now easily manageable and prepared for a Slack channel drop.

ts-slow-content-5
A real PowerShell object

Enter Levity

Here’s what the daily GIF might look like in our Slack channel. No, no one sees this but us and the ‘movement’ of it really forces us to pay attention. We’re (as Server admins) responsible for maintaining and teaching others how to best use the platform. If we ignore it, then the tool (Tableau) gets misused and decision makers may start to see if the grass is greener.

Again, and I can’t stress this enough, levity is a good thing. I can’t tell you how many times I’ve seen C-level people view workbooks that take 3+ minutes to load. Three minutes of awkward silence. That. Scares. Me.

So we monitor and when it gets bad and we’re being questioned about the slowness, we need to laugh. That’s healthy and I can’t be convinced otherwise. If you’re intentions are wrong, you’re not being human.

This:

slowWB
It moves.

Becomes this:

 

ts-slow-content-4
Daily performance metrics

That’s it! Pretty easy, right? So what else can we do with this?

Well, here are a few ideas, based off of the magic of GIFs.

And remember, be responsible and kind. There are too many rude people on this planet. One more isn’t a good thing.

(1) Slow extracts become:

wrap_it_up

(2) Slow login times on Apache become:

tumblr_lil0h0CeAx1qavy44o1_500

(3) Large Server downloads become:

file.gif

Tableau Server: Get Bytes

Continuing the Log Analytics theme for Tableau Server, specifically the ‘Monitor’ pillar of A.I.M., it’s time to show another quick tip for analyzing your Apache logs. Understanding how much data is moving through the wire is another technique one could use to decide whether dashboards, CSVs or general crosstab download needs to be optimized. I shouldn’t need to mention it’s also a great way to monitor the security and integrity of your data.

apache-bytes-0
The ‘Alert’portion. If something is outside the norm, we’re alerted. 

We’ll use the module I loaded here to do the heavy lifting. Once you export a csv, load into Tableau for some analysis (or perhaps mash it up with the geo dashboard I demoed a while back).

You’ll want to pass this query for the ‘leFilter’ section: ‘/HTTP\/1\.1″ “-” \d{3}\s(?P<size>\d*)/ AND size>1000000’

The idea is that, over time, you’ll be able to see what is being requested with the most amount of bytes and, potentially, a better way to optimize your content. Further, it’s a fantastic way to be alerted (‘Alert’ portion of A.I.M.) on large (consistent) GET requests from questionable sources.

In the end, this all fits together with time-to-serve data and other performance and security related inputs.

 

Tableau Server Log Analytics: Easy Parser

I’ve mentioned before but it’s worth mentioning again: Log Analytics and Tableau Server is a wonderful thing. There’s a ton of helpful information in Tableau logs (and pretty much *all* logs) which, along with the PostgreSQL data, make for a very good data toolbox.

I’ve also mentioned Logentries a lot when digging through Tableau logs. There are many reasons I use the tool, but the one which makes it the most useful is: centralized log analysis. Essentially the workflow goes like this: Tableau –> Logs –> Logentries –> Tableau (and around and around). It’s a positive feedback loop of valuable data which can give you insight into things such as:

  • What workbooks take the longest to load and *where* they are geographically
  • What user downloads the most data, how much and how long does it take
  • Http 404s
  • Filters used by a user/workbook
  • Data sources used by a user/workbook
  • and more!

With Tableau, you’re either leveraging a Log Analytics strategy or you’re not. I cannot stress how vital it is for Tableau Server administrators to at least have some plan in place for when you are suddenly inundated with a ‘slow’ server and/or site.

That said, often times it’s easier to have a few functions and tools to make the ad-hoc or automated analysis easier. Here’s one: we’ll wrap the Logentries REST API in a PowerShell function. This will simply allow us to pull log data from Apache or VizQL based off of a simple parameter.

What’s returned is a neatly formatted csv which you can then import into Tableau, add to a database or simply do some quick research. For example, if you want to ensure excessive 404s are handled, you can simply use this function with a filter, parse, and lookup the offending IP address.  If necessary you’d add those IPs to a firewall rule.

More specifically, here’s an example of how you would use the function in PowerShell:

Get-TsLog -leAcctKeyVizQL 'your VizQL key' -leFilterVizQL 'k=end-update-sheet' -workpathVizQL "C:\users\$env:username\Documents" -apikey 'your Logentries API key'
vapor_rub_0
Here’s where your log data (parsed) can become a great means to improve performance and react before things happen

The added benefit of adding this type of aggregated data to your own Tableau data model and database is that it gives the admin some data for historical purposes, planning and learning.

So, here’s the module on the PowerShell gallery. Let me know if there are questions.

 

Tableau & Log Analytics Webinar

We all know Tableau has changed the way people use data to make better decisions (business or otherwise). Thanks to Tableau’s robust set of admin capabilities, APIs and integration, users are now empowered to change the way traditional business intelligence software is managed.

Join Tableau, Logentries and Pluralsight on June 16th at 2pm EDT to learn how you can build a simple analytics stack for automating your Tableau alerting, integration and monitoring workflows.

See you there!

-Mike