Live from SHARE: Collaborative Mainframing (revisited)

I was delayed by 24 hours getting to this week’s SHARE conference in Providence, Rhode Island – more than 10,000 miles from my home in Australia.

I was onboard my first flight from Sydney to Los Angeles when the captain announced that there was a problem with the “onboard mainframe” and they were going to have to reboot.

Now I had no idea that aircraft run on onb

oard mainframes…or whether it was actually a mainframe…or how to reboot one. Does one use CTRL-ALT-DEL? Do you just turn it off and back on again? Some wise guy onboard suggested loudly that they “Call Bill Gates.” I don’t know how much this Mr. Gates knows about mainframes. Not much it would seem, because after about two hours the captain announced that the mainframe wasn’t collaborating (he may have said cooperating, but at that point I was a little preoccupied), and the flight was cancelled.

One problem with collaboration in the workplace is that it’s often devoid of all the facts – the supporting data. Decisions are made on the basis of hearsay and assumptions, rather than empirical evidence. But if you repeat a lie, or a false assumption, often enough does it become the truth? What value do we place on facts?

There is a lot of rich, valuable data stored on mainframes that could help support fact-based decision making and avoid false assumptions. But how easy is it to get to? Because when you think about it, a mainframe without timely, easy, responsive, flexible access – a mainframe where an IT department has to extract the data and load it into some kind of data warehouse in support of the business decision-making process – is really just a glorified, multi-million dollar file server.

So, what is “collaborative mainframing?” Is it a real thing? The answer is yes!

Imagine a scenario where, as an executive, I have multiple inputs to consider as I plan my business. Some operational data may come from the mainframe, some data from external or third-party sources and be in spreadsheet format, some data may come from our budgeting systems. Now imagine I have time pressure to make a particular business decision (who doesn’t, these days?), to pull together this information, interpret it, add some context and share with my team to reach a consensus decision. How am I going to do that?

As younger people are entering the management ranks, we are seeing a shift from “management by exception” to “management by analysis”, a new breed of business user who are data savvy yet information poor. As Inhi Cho Suh, GM, IBM Collaboration Solutions recently observed: “We’re going from a typing and texting culture to a culture of conversations.” A cognitive business empowers us to work as part of a seamless flow of information and conversations. It exploits our natural inclination for teamwork and creative collaboration. Data has little value without context, but by making mainframe data more accessible in support of a more holistic business view, we can leverage these information assets like never before.

As Charles Darwin is quoted as remarking, ”It is the long history of humankind (and animal kind, too) those who learned to collaborate and improvise most effectively have prevailed.” In the increasingly complex world of today, we need ever more timely, iterative, collaborative approaches to decision-making, regardless of where our supporting information resides. If we can do this effectively, then our business really can “take flight.” THAT is the essence of collaborative mainframing.

Connecting Tableau to SAS Datasets via the SAS OLE DB Provider

(This article was originally published on the see-change.co website)

One of the topics which generated a lot of interest during my presentation on SAS/Tableau Integration at the ‘TCC13’ Tableau Customer 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’:
Excel_to_SAS_1
Step 2: Select the Base SAS Data Provider
In the Data Connection Wizard, select ‘Other/Advanced’ then click Next:
Excel_to_SAS_2
Select the SAS OLE-DB Provider (9.2 or 9.3 as appropriate): Excel_to_SAS_3
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.
Excel_to_SAS_4
Step 4: Select the desired SAS dataset to connect to Excel_to_SAS_5

Step 5: Save the Data Connection Excel_to_SAS_6
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:
Excel_to_SAS_7
Step 7: Save the Excel spreadsheet containing the SAS OLE-DB Connection Excel_to_SAS_8
Step 8: In Tableau, Connect to the Excel File Select ‘Connect to Data’, then select ‘Microsoft Excel’. Select ‘ Live Connection’.
Excel_to_SAS_9

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):

Setting up SAS dates for input into Tableau Desktop

(This article was originally published on the see-change.co website)

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:
Tableau_Test_1
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:
Tableau_Test_1c
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:
Tableau_Test_zerob
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:
Tableau_Test_3b
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.