Category: Tableau
Connecting Tableau to SAS Datasets via the SAS OLE DB Provider
One of the topics which generated a lot of interest during my presentation on SAS/Tableau Integration at the recent ‘TCC13’ Tableau Conference, was the idea of connecting to SAS datasets via the SAS OLE-DB Provider. While SAS themselves refused to allow such a presentation at their own customer conference…, it was unexpected as much as rewarding to find an audience of around 150 people at a Tableau conference interested in SAS connectivity!
Currently, Tibco Spotfire is the only 3rd party Data Discovery tool I’m aware of, which natively supports OLE-DB connectivity to SAS datasets. The benefit of OLE-DB is that it does not require access to SAS Server in order to connect to a SAS dataset. This is important, because often SAS datasets are stored locally or on a network share. SAS ODBC requires SAS Server connectivity, which in turn requires SAS client/server connectivity (eg SAS Integration Technologies)
A workaround is to connect to SAS datasets via OLE-DB using Excel as a ‘gateway’. Since the OLE-DB connection definition dialog is fully exposed within Excel, the connection details to the SAS dataset can be set up and tested. Then Tableau can be pointed to the Excel file, through which the SAS data can be retrieved.
Since the OLE-DB connection provides a way to automate the refresh of the SAS data from within Excel, this method can help ensure that the Tableau workbook is kept up-to-date as the underlying SAS data changes.
To follow are the steps to set up the Ole-DB connectivity.
|
Step 1: Define OLE-DB connection within Excel Open up a blank Excel workbook. Under the ‘Data’ menu, select the option ‘From Other Sources’ then select ‘From Data Connection Wizard’: |
 |
|
Step 2: Select the Base SAS Data Provider In the Data Connection Wizard, select ‘Other/Advanced’ then click Next: |
 |
| Select the SAS OLE-DB Provider (9.2 or 9.3 as appropriate): |
 |
|
Step 3: Define Connection Properties The next step is perhaps the least intuitive. The ‘Data Link Properties’ dialog requires a connection to be defined to the SAS data set. For this to work correctly, all that is needed is the path to the folder where the SAS datasets reside. This is entered into the ‘Data Source’ property. The other options such as ‘Location’ and ‘User name’ can be ignored. Test the connection, then click OK. |
 |
| Step 4: Select the desired SAS dataset to connect to |
|
| Step 5: Save the Data Connection |
 |
| Step 6: Import the data |
Verify that the OLE-DB is working as expected by importing the SAS dataset into Excel via the OLE-DB Provider connection which has just been defined: |
| Step 7: Save the Excel spreadsheet containing the SAS OLE-DB Connection |
 |
| Step 8: In Tableau, Connect to the Excel File |
Select ‘Connect to Data’, then select ‘Microsoft Excel’. Select ‘ Live Connection’. |
When the Tableau workbook is saved, the SAS data can be refreshed by periodic refresh of the Excel connection. This can be scheduled to ensure that Tableau is retrieving the most up-to-date information from SAS.
Here is a video demonstrating the process described above (sorry, no audio):
Reporting on ‘% of base’ in Cognos10
A workaround for the lack of ‘% of base’ functionality in Business Insight Advanced
A common requirement is to be able to product % of total or ‘% of portfolio’ reports, for comparative analysis. Unfortunately, this capability is currently missing in Business Insight Advanced. A workaround exists, which is to use the ‘% of’ calculation provided in Business Insight Advanced to hard-code the total value. However this introduces excessive report maintenance implications.
An alternative approach is to use Analysis Studio, which does support % of base, to ‘author’ the basic report layout required, then finish the report in Business Insight Advanced. (Note, however, that it is currently not possible to open an Analysis Studio view in Business Insight Advanced – it is necessary to open the view in Report Studio first, save to Cognos Connection, from where it can then be opened in Business Insight Advanced)
Example
In the following example, we are looking to create a sectioned report, showing % of total for each column in the report. Starting in Analysis Studio, we have a crosstab like this:
Since Analysis Studio does not report ‘Section’ functionality (as is available in Query Studio, Report Studio and BIA), this will have to be added later.
The first step in Analysis Studio will be to add the desired measure as a nested level within the crosstab view (this will simplify creation of the % calculations later):
Next, the ‘% of total’ calculation will be added:
Next, an additional dimension will be added, which will form the rows of each section in the final report:
This view can now opened directly into Report Studio, or saved to Cognos Connection:
From Report Studio, the report can now be saved back to Cognos Connection (under a new name), which will finally enable it to be opened in Business Insight Advanced:
(Note: it is hoped that Cognos will improve the inter-Studio workflow in future versions, to enable Analysis Studio view to be opened directly into Business Insight Advanced, either from within Analysis Studio or from Cognos Connection)
This view can now opened directly into Report Studio, or saved to Cognos Connection:
Note the automatic creation of subtotals for rows and columns:
In the context of report sections, this is entirely unnecessary. However, currently within BIA there is no easy way to add/remove nested subtotals. After manually cutting out each of the superfluous totals, and sectioning the report (using the Section/Unsection button), we end up with the required report layout:
Summary
It had been hoped that Cognos 10 would introduce some level of Studio ‘rationalization’ in order to simplify the workflow for Business Analysts in particular. Unfortunately, the introduction of Business Insight Advanced (BIA) and Business Insight has thus far only added 2 new Studios without rendering any of the existing Studios redundant. It is to be hoped that in future versions, it will be less necessary to jump around several Cognos interfaces in order to construct what are relatively simply reports.
Footnote: It turns out this is trivially easy to do in Tableau (around 9 mouse clicks) and, ironically, in PowerPlay Client (5 mouse clicks).
Setting up SAS dates for input into Tableau Desktop
Some techniques for setting up integration between the SAS and Tableau solutions, specifically with respect to date formats.
SAS and Tableau are ‘best of breed’ in their own areas – SAS in the area of Analytics and ‘Analytical Data Preparation’, Tableau in the area of data visualization and interactive dashboarding in an intuitive, drag & drop environment. Consequently, it makes sense to find ways to integrate these technologies to provide an Integrated Information Framework which leverages the strengths of both solutions.
Leveraging SAS capabilities in this way provides a way to ‘rapid prototype’ business reporting requirements, without the costs and delays typically seen when attempting to model emerging business requirements in the Data Warehouse via traditional ETL methods.
In addition, this approach suggests a way to protect the investment in analytical reporting as developed by the SAS team, by providing a platform to publish those reports for easy consumption, plus easy re-formatting and ‘slice & dice’ of these reports in the Tableau environment
Example
Typically, the easiest way to prepare SAS data for consumption in Tableau is to set up an export process in SAS Enterprise Guide:
Using the SAS Enterprise Guide Export wizard, a SAS dataset can be exported as CSV, as a step in the process. Dataset is exported to a network location. SAS process looks like this, and can be set up to run on a schedule eg monthly.
In this example, we have a number of dates in our SAS dataset:
Often, the default date format as set up in SAS is adequate to be imported into, and correctly interpreted by, Tableau.
Where this is not the case, the SAS date format ‘DDMMYYS8.’ can be used:
Which would produce SAS code similar to the following:
PROC SQL;
CREATE TABLE WORK.QUERY_FOR_POLICY1 AS
SELECT t1.trandate FORMAT=DDMMYYS8.,
t1.polexpdt FORMAT=DDMMYYS8.,
t1.commdate FORMAT=DDMMYYS8.
FROM WORK.QUERY_FOR_POLICY t1
QUIT;
On importing the dataset into Tableau, the dates are correctly interpreted, automatically providing the ability to drill from Year to Quarter to Month:
Note: While it is generally easier to prepare the data in the most appropriate format for Tableau using the extensive SAS’ data functions and formatting options, there are also date functions within Tableau. For example, to convert a text (string) field containing dates in the format YYYYMMDD into a date within Tableau, the following could be set up as a calculated field:
Date(right([PERIOD],2) + “/” + mid([PERIOD],5,2) + “/” + left([PERIOD],4))
Summary
In addition to the approach described above, another way to ‘prepare’ SAS data for consumption in Tableau involves using the SAS ‘PROC SQL’ method to output SAS results directly to a relational table. With the SAS/ACCESS interface, database objects can be referenced directly in a DATA step or SAS procedure using the SAS LIBNAME statement. PROC SQL can be used to update, delete or insert data into a relational table, for example via Bulk Load.
Connect
Connect with us on the following social media platforms.