According to the SQL standard, there's no such thing as an implicit ordering between records in a table. Since our ODBC connector follows the standard, it cannot make use of implicit ordering present in the SAS data set. If you wish to maintain a specific ordering you must expose that ordering as a field in the table, e.g. an ID field or some sort of numeric, ascending field. You can then use that field to sort the data displayed in your visualization.
Thanks for your reply.
I realize that the SAS ODBC connector to Tableau follows the standard (what I see in my case is sorting the data in alphabetical ascending order). Is there any way we can change this default to no sorting ... i.e. can I bring in the data without any sorting?
1 of 1 people found this helpful
I think I might understand your issue. It sounds like you might be referring to the order of the variables as they are brought into the Dimensions/Measures windows. So it's not the "data order" but the "variable order" in your SAS datasets (and I know that can be a lot of work in SAS to get the variables ordered the way you want them -- especially when there are 600+!).
If that is the case, on the Dimensions header click on the drop-down arrow, select Sort By and then Data Source Order (the default is Name order). Is that what you were looking for?
Thank you very much for your reply. You have got my issue right.
Your suggestion works perfect if I Connect Live' to the SAS data source. However, Tableau suggests copying the data into Tableau in order to use all of their functionalities. And if I bring in the data into Tableau, when I select Sort By, the 'Data Source Order' is grayed out.
Do we have any solution to that issue?
Thanks for your help.
Apparently the grayed out Data Source Order option is not a new issue. Here is another unanswered thread on it (not ODBC-related):
Someone posted a solution which was to preface their column names with a number that represented the order they wanted the variables to appear in the Dimensions/Measures window. Of course SAS won't allow numbers as a prefix of a variable name so you would need to preface with a character, then the sequence number and then some sort of separator. Not very practical.
ODBC has many limitations and the SAS ODBC driver in particular isn't necessarily the most friendly. So I was about to write it off as another limitation of the driver. But after digging a little deeper into the SAS ODBC documentation I see they support the Core ODBC function "SQLColumns" for which SAS uses a specially formatted query of DICTIONARY.COLUMNS. That specialty dataset is essentially the SAS metadata file that describes the columns in any given SAS dataset. That particular query returns the following columns:
# Variable Type Len Label
1 libname Char 8 Library Name
2 memname Char 32 Member Name
3 memtype Char 8 Member Type
4 name Char 32 Column Name
5 type Char 4 Column Type
6 length Num 8 Column Length
7 npos Num 8 Column Position
8 varnum Num 8 Column Number in Table
9 label Char 256 Column Label
10 format Char 49 Column Format
11 informat Char 49 Column Informat
12 idxusage Char 9 Column Index Type
13 sortedby Num 8 Order in Key Sequence
14 xtype Char 12 Extended Type
15 notnull Char 3 Not NULL?
16 precision Num 8 Precision
17 scale Num 8 Scale
18 transcode Char 3 Transcoded?
Item # 8 on that list is VARNUM which is the Order of the variables in a SAS dataset. In other words, that is the field we would want Tableau to use to order the Dimensions/Measures (Data Source Order). But since that option is grayed out there may be a limitation in Tableau when using ODBC. Maybe we can modify the Tableau ODBC connection query to take advantage of the availability of VARNUM? It might be doable since (miraculously) SAS seems to support it.
I am willing to test this out if we can get that far.
Tim, thanks for clarifying. I definitely missed the point in Rahman's original post. I don't know that Tableau can make use of the API you describe, because it appears completely inconsistent with the ODBC 3.x SQLColumns API documented here: http://msdn.microsoft.com/en-us/library/ms711683%28VS.85%29.aspx
SQLColumns does provide the ordinal position of each column, so we could look into using that information. If I recall correctly, the problem I discovered early on is that some data sources were not consistent in providing unique, densely ordered column ordinals. A possible improvement for Tableau's ODBC connector is to attempt to honor the ordinal data if it is safe to do so. I'll add that to the wishlist, but I cannot commit to any timeframe or priority for this work.