Tech and T-Accounts

SAS ODBC and Tableau

SAS ODBC Driver setup instructions for creating a DSN that can be used with any ODBC reading software (like Excel, Access, Tableau):

I worked with Robert Morton of Tableau Software for the past year to get this SAS connectivity working and debugged several issues. Here are some notes about what I found out:

- 32 BIT ODBC drivers from SAS need to be installed on the local PC. A SAS license is not required to use this driver.
- Set up a DSN using windows to establish a connection through the SAS ODBC driver (you must use the 32 bit version, so to get the ODBC setup for the 32 bit, and you have a 64 bit operating system, don't open the data connections from Control Panel as that will create a 64-bit connection.  To open a 32 bit connection (at least for Windows 7), place this into your start menu search C:\Windows\SysWOW64\odbcad32.exe. To eliminate the concern whether the connection issues are Tableau related, test your DSN setup using a product that SAS ODBC does recognize, like Microsoft Excel or MS Access. This will enable you to have a conversation with SAS technicians without muddying the issue with Tableau.
- Once the DSN connectivity works (you will need to set up different ones for SAS/SHARE versus locally accessed SAS7bdat files), then connect with Tableau.
- While you can connect live, date fields are not properly interpreted unless it is an extract. Explore this and test!
- When setting up to connect to SAS/SHARE, the SAS instructions are somewhat obtuse. Names of the SAS/SHARE as recognized by the server must be included in your services file and match the DSN Server name. Here is the setup (with some names modified to maintain confidentiality):

Setup Windows so it will know where to route the port:

c:\windows\system32\drivers\etc\services (be sure to insert this line in numeric ascending order of the port #'s)
share 1624/tcp #SAS odbc

DSN setup:
General tab:
   Data Source Name: My SAS connection (this is freeform)
   Description: (can be left blank)
   Server: share.share
   Records to Buffer: 100000 (I picked more than Robert mentions because I'm dealing with large datasets)

SQL Options to check:
   Support VARCHAR
   Disable_0 override parsing
   Fuzz numbers at 12 places
   Infer type in min/max functions

Servers Tab:
   Name:share.share (must be the same as on General tab)
   Password: (left blank)
   Configure button:
   Server Address: servername (this is the actual server name that is used within SAS to connect -- no // or other UNC conventions, just the name)
   UserName: myproduser (we use a Unix user name that is for production reporting access, but whatever user you use, it must have access to the server named above)
   UserPassword: xxxxxxx (we enter the myproduser password so that it doesn't have to be entered each time that you want to access sas/share)

Libraries tab:
   Name: Mylibrary (this is freeform)
   Host File: /export/appl/theexposeddirectory/ (beginning and ending slash are important -- the user must have access to this directory, which is where the sas7bdat files should reside).

Tableau specifc data connection file (only for Tableau versions 6.14 and before)
You may also need to create a tdc file to be placed in the "My Tableau Repository\datasources" subdirectory, but generally this isn't required for version  6.1.4  and after. Create genericodbc-sas.tdc with a text editor with the following content:

<connection-customization class="genericodbc" enabled="true" version="7.7">
<vendor name="SAS"/>
<driver name="SAS"/>
<customization name="CAP_CREATE_TEMP_TABLES" value="no"/>
<customization name="CAP_ODBC_USE_NATIVE_PROTOCOL" value="yes"/>
<customization name="CAP_QUERY_BOOLEXPR_TO_INTEXPR" value="yes"/>
<customization name="CAP_QUERY_GROUP_BY_ALIAS" value="no"/>
<customization name="CAP_QUERY_GROUP_BY_DEGREE" value="yes"/>
<customization name="CAP_QUERY_JOIN_ACROSS_SCHEMAS" value="no"/>
<customization name="CAP_QUERY_JOIN_REQUIRES_SCOPE" value="no"/>
<customization name="CAP_QUERY_SUBQUERIES" value="yes"/>
<customization name="CAP_QUERY_SUBQUERIES_WITH_TOP" value="no"/>
<customization name="CAP_QUERY_TOP_N" value="no"/>
<customization name="CAP_SELECT_INTO" value="no"/>
<customization name="CAP_SELECT_TOP_INTO" value="yes"/>
<customization name="CAP_SKIP_CONNECT_VALIDATION" value="yes"/>
<customization name="CAP_SUPPRESS_DISCOVERY_QUERIES" value="yes"/>
<customization name="CAP_ODBC_FORCE_SINGLE_ROW_BINDING" value="yes"/>
<customization name="CAP_ODBC_BIND_SUPPRESS_INT64" value="yes"/>

Other resources that might be useful:

 Howard Plemmons, SAS Institute paper 156-28, V8 SAS help on ODBC, v9.3 SAS help on ODBC, SAS Help on ODBC:


Oracle SQL - check for no table, no rows

I was recently tasked with an interesting issue.  The report that I created connected to a database table that would be refreshed regularly.  During the refresh process (sometimes taking minutes, sometimes longer), the report would fail to show any data, since the table was empty at that exact moment.  I needed a means to determine if the report had no rows, then I would display a message that the data was being updated.  The report was then to be migrated to another database, where the table didn't even exist yet.  In this case, I wanted to also display the same wait message.  Oracle doesn't have an obvious method to determine if a table exists.  Attempting to run Oracle SQL against a non-existent table results in an Oracle 904 error. 

I figured out that this code will do the trick, whether the table exists or not and whether the table has any rows in it or not.  It returns a binary 1 if the table doesn't exist or doesn't have any rows in it, otherwise a zero is returned.

   CASE WHEN (select num_rows from all_tables where table_name = '<your table name>') IS NULL
   THEN 1
   ELSE 0
from dual



Waterfall graph in Excel and Tableau

Lots of people are fond of creating a waterfall graph, but haven't figured out how to do it.

Here's the method that I've found to be simple and effective.  The basics are to first create four series:

A Base series for setting the beginning and ending balances. (blue)
An 'UP' or positive influence to the balance - (green)
A 'DOWN' or negative influence. -- (red)
A series that sets the bottom of the element in the UP or down.  This series is formatted with the border and area set to 'None'.

That's it.  The tricky part is the calculation to determine the number to use in the bottom series.  For the first element, it is simply the beginning balance less the down amount.  Subsequent columns are the prior bottom plus the prior up amount less the current column down amount.

Because I really like Tableau for graphics, I also used it to create the waterfall chart.  Download the Tableau workbook to understand how that was done.

Download Sample Excel Workbook Download Sample Tableau Workbook
Sample Excel Waterfall Chart:  Sample Tableau Waterfall Chart:
Page 6 of 7 << < 1 2 3 4 5 6 7 > >>
Copyright© Brad Earle
Site Map | Printable View | © 2008 - 2021 KB Earle Associates LLC | |