Monthly Archives: February 2014

Manual UTC Conversion

During a project last year I was creating a FACT table from one of many databases in my organization used to track program information. Using SQL Server, one of the fields that I wanted to pull was Air Date/Time of a program. When I first created my fact table, I noticed that the Air Date/Time field was in UTC. I suspect this database was created with a time OFFSET field but I didn’t have schema documentation for it.

I decided create my own conversion calculation to East Coast time. I’ve been working with SQL for a little time now but I’m no expert. So the challenge I faced was adjusting for Standard versus Day Light Savings because UTC does not change. I looked up the dates when we adjust our clocks from 2011 – 2025. The rest was the following SQL CASE statement:

CASE
WHEN [Table].[Date Field] BETWEEN ‘2011-03-13 07:00:00:000’ AND ‘2011-11-06 06:00:00:000’ THEN DATEADD(hour,-4,[Table].[Date Field])
WHEN [Table].[Date Field] BETWEEN ‘2012-03-11 07:00:00:000’ AND ‘2012-11-04 06:00:00:000’ THEN DATEADD(hour,-4,[Table].[Date Field])
WHEN [Table].[Date Field] BETWEEN ‘2013-03-10 07:00:00:000’ AND ‘2013-11-03 06:00:00:000’ THEN DATEADD(hour,-4,[Table].[Date Field])
WHEN [Table].[Date Field] BETWEEN ‘2014-03-09 07:00:00:000’ AND ‘2014-11-02 06:00:00:000’ THEN DATEADD(hour,-4,[Table].[Date Field])
WHEN [Table].[Date Field] BETWEEN ‘2015-03-08 07:00:00:000’ AND ‘2015-11-01 06:00:00:000’ THEN DATEADD(hour,-4,[Table].[Date Field])
WHEN [Table].[Date Field] BETWEEN ‘2016-03-13 07:00:00:000’ AND ‘2016-11-06 06:00:00:000’ THEN DATEADD(hour,-4,[Table].[Date Field])
WHEN [Table].[Date Field] BETWEEN ‘2017-03-12 07:00:00:000’ AND ‘2017-11-05 06:00:00:000’ THEN DATEADD(hour,-4,[Table].[Date Field])
WHEN [Table].[Date Field] BETWEEN ‘2018-03-11 07:00:00:000’ AND ‘2018-11-04 06:00:00:000 THEN DATEADD(hour,-4,[Table].[Date Field])
WHEN [Table].[Date Field] BETWEEN ‘2019-03-10 07:00:00:000’ AND ‘2019-11-03 06:00:00:000’ THEN DATEADD(hour,-4,[Table].[Date Field])
WHEN [Table].[Date Field] BETWEEN ‘2020-03-08 07:00:00:000’ AND ‘2020-11-01 06:00:00:000’ THEN DATEADD(hour,-4,[Table].[Date Field])
WHEN [Table].[Date Field] BETWEEN ‘2021-03-14 07:00:00:000’ AND ‘2021-11-07 06:00:00:000’ THEN DATEADD(hour,-4,[Table].[Date Field])
WHEN [Table].[Date Field] BETWEEN ‘2022-03-13 07:00:00:000’ AND ‘2022-11-06 06:00:00:000’ THEN DATEADD(hour,-4,[Table].[Date Field])
WHEN [Table].[Date Field] BETWEEN ‘2023-03-12 07:00:00:000’ AND ‘2023-11-05 06:00:00:000’ THEN DATEADD(hour,-4,[Table].[Date Field])
WHEN [Table].[Date Field] BETWEEN ‘2024-03-10 07:00:00:000’ AND ‘2024-11-03 06:00:00:000’ THEN DATEADD(hour,-4,[Table].[Date Field])
WHEN [Table].[Date Field] BETWEEN ‘2025-03-09 07:00:00:000’ AND ‘2025-11-02 06:00:00:000’ THEN DATEADD(hour,-4,[Table].[Date Field])
ELSE DATEADD(hour,-5,[Table].[Date Field]) END AS [Date Name]

I first created a parameter in Tableau to let the user decide to view the data between standard and daylight savings. But I wanted the users to avoid any confusion between the viz and any other area where one can see program air date/time. Secondly, for Tableau best data practices, do heavy data lifting calculations before connection to Tableau. Software like SQL Server are built for this type of data manipulation. Especially when working with millions of records, long winded calculations can slow Tableau performance.

Thanks to my manager for encouraging this post. I hope it saves anyone time who needs to convert UTC date fields. 

UPDATE: To perform the same in Tableau click here.

Share Button

Tableau Server Tip

As an administrator of Tableau Server, you’re granted access to six dashboards that show a variety of server activity/performance/utilization information. These dashboards are connected to Tableau’s internal PostgreSQL database. What if you’d like other server users to access these admin dashboards but you do not want to grant those users Administrative rights? There are four easy steps to accomplish this.

  1. Create a PostgreSQL password
  2. Find and copy the tabbed_admin_views workbook
  3. Open workbook and update database connections with your server and password
  4. Publish workbook

Step 1:

  • Log onto your machine that runs Tableau Server and in Command Line navigate to the bin folder
  • Type tabadmin db pass [password] (in version 9, “dbpass” is one word)
  • For [password] input your desired password
  • Type tabadmin configure
  • Type tabadmin restart

You’ve now set a password to the internal PostgreSQL database. You’re free to connect to it from a new workbook and poke around. Note, if you have not done so, you’ll need to install PostgreSQL database drivers.

Step 2:

Thank you Adolph Barclift, DC-VA Tableau User Group Co-Chair for the assistance on this step. 

While logged in to your machine running Tableau Server, open folder …Tableau\Tableau Server\[version]\wgserver\z5\WEB-INF\admin

Make a copy of the tabbed_admin_views.twb and save to desired location.

ScreenHunter_01 Feb. 08 12.44

Step 3:

Open the workbook and click OK when prompted to log in, you’ll get an error and click Yes to edit the connection.

Complete the connection dialog with your credentials. Port, Database and Username should remain unchanged.

ScreenHunter_02 Feb. 08 13.03

This workbook has eight separate connections so you’ll have to repeat the process seven more times.

Step 4:

Publish workbook, embedding credentials, and set your security as desire.

Congratulations! You now have your own administrative dashboards. What’s even better is that you can create custom admin dashboards! Check out Russell Christopher’s series on Tableau History Tables for more details on PostgreSQL schema.

Share Button

Year over Year Quick Tip

Over the years as a Tableau user I’ve learned to adjust my vizzes depending on the audience. Displaying Year over Year is an important metric to many at my organization. Here is a quick and easy way that I use often.

I’m using the Superstore sample data that ships with Tableau. Place Order Date in the Columns shelf and Sales in the Rows shelf, change the Marks to Bar.

ScreenHunter_06 Feb. 01 20.01

Now grab Sales again and place it in the Label Marks. Hover over the right edge of the pill until you see the triangle. Click it and move down to Quick Table Calculation > Percent Difference

ScreenHunter_09 Feb. 01 20.05

And that’s it! Tableau will use the earliest date as the base line and calculate the YoY change.

ScreenHunter_10 Feb. 01 20.06

I like this view because the viz utilizes the Y axis to display the Sales in dollars, but the label shows the YoY growth. The two metrics combined provide more context to your audience.

One thing to keep in mind is that if current year is not over, filter for months. Let’s say today is currently June 2013 and management wants to know how Sales are tracking against previous years.

ScreenHunter_12 Feb. 01 20.09

Hold right click as you drag Order Date into the Filter shelf. Filter on Months and only include January to May. Very simple!

Share Button