SAS Visual Analytics

SAS has been one of the leaders in advanced analytics for more than 40 years. The SAS software suite has hundreds of components designed for various use cases ranging from data mining and econometrics to six sigma and clinical trial analysis. For this chapter, we’re interested in the self-service analytic tool known as SAS Visual Analytics.

SAS Visual Analytics is a web-based, self-service interactive data analytics application from SAS. SAS Visual Analytics allows users to explore their data, write reports, and process and load data into the SAS environment. It has in-memory capabilities and was designed for big data. SAS Visual Analytics can be deployed in distributed and non-distributed mode. In distributed mode, the high-performance SAS LASR Analytic Server daemons are installed on the Hadoop worker nodes. It can take advantage of the full processing capabilities of your Hadoop cluster, enabling it to crank through large volumes of data stored in HDFS.

SAS Visual Analytics distributed deployment supports Cloudera Enterprise, Hortonworks HDP, and the Teradata Data Warehouse Appliance. The non-distributed SAS VA deployment runs on a single machine with a locally installed SAS LASR Analytic Server and connects to Cloudera Impala via JDBC. ⁱ

Zoomdata

Zoomdata is a data visualization and analytics company based in Reston, Virginia. ⁱⁱ It was designed to take advantage of Apache Spark, making it ideal for analyzing extremely large data sets. Spark’s in-memory and streaming features enable Zoomdata to support real-time data visualization capabilities. Zoomdata supports various data sources such as Oracle, SQL Server, Hive, Solr, Elasticsearch, and PostgreSQL to name a few. One of the most exciting features of Zoomdata is its support for Kudu and Impala, making it ideal for big data and real-time data visualization. Let’s take a closer look at Zoomdata.

Self-Service BI and Analytics for Big Data

Zoomdata is a modern BI and data analytics tool designed specifically for big data. Zoomdata supports a wide range of data visualization and analytic capabilities, from exploratory self-service data analysis, embedded visual analytics to dashboarding. Exploratory self-service data analysis has become more popular during the past few years. It allows users to interactively navigate and explore data with the goal of discovering hidden patterns and insights from data. Compared to traditional business intelligence tools, which rely heavily on displaying predefined metrics and KPIs using static dashboards.

Zoomdata also supports dashboards. Dashboards are still an effective way of communicating information to users who just wants to see results. Another nice feature is the ability to embed charts and dashboards created in Zoomdata into other web applications. Utilizing standard HTML5, CSS, WebSockets, and JavaScript, embedded analytics can run on most popular web browsers and mobile devices such as iPhones, iPads, and Android tablets. Zoomdata provides a JavaScript SDK if you need the extra flexibility and you need to control how your applications look and behave. A REST API is available to help automate the management and operation of the Zoomdata environment.

Real-Time Data Visualization

Perhaps the most popular feature of Zoomdata is its support for real-time data visualization. Charts and KPIs are updated in real time from live streaming sources such as MQTT and Kafka or an SQL-based data source such as Oracle, SQL Server, or Impala.

Architecture

At the heart of Zoomdata is a stream processing engine that divides and processes all incoming data as data streams. The data source doesn’t have to be live, as long as the data has a time attribute, Zoomdata can stream it. Zoomdata has a technology called Data DVR, which not only lets users stream data, but also rewind, replay, and pause real-time streaming data, just like a DVR. Zoomdata pushes most of the data processing down to the data source where the data resides, taking advantage of data locality by minimizing data movement. ⁱⁱⁱ Zoomdata uses Apache Spark behind the scenes as a complementary data processing layer. It caches streamed data as Spark DataFrames and stores it in its result set cache. Zoomdata always inspects and tries to retrieves the data from the result set cache first before going to the original source. ^iv Figure 9-1 shows the high-level architecture of Zoomdata. Apache Spark enables most of Zoomdata’s capabilities such as Zoomdata Fusion and Result Set Caching.

Deep Integration with Apache Spark

One of the most impressive features of Zoomdata is its deep integration with Apache Spark. As previously discussed, Zoomdata uses Spark to implement its streaming feature. Zoomdata also uses Spark for query optimization by caching data as Spark DataFrames, then performing the calculation, aggregation, and filtering using Spark against the cached data. Spark also powers another Zoomdata feature called SparkIt. SparkIt boosts performance of slow data sources such as S3 buckets and text files. SparkIt caches these data sets into Spark, which transforms them into queryable, high-performance Spark DataFrames. ^v Finally, Zoomdata uses Spark to implement Zoomdata Fusion, a high-performance data virtualization layer that virtualizes multiple data sources into one.

Zoomdata Fusion

Zoomdata uses a high-performance data virtualization layer called Zoomdata Fusion. Also powered by Apache Spark, Zoomdata makes multiple data sources appear as one source without physically moving the data sets together to a common location. ^vi Zoomdata uses Spark to cache data when optimizing cross-platform joins but pushes processing to the data source for maximum scalability. With Zoomdata Fusion, users will be able to conveniently query and join disparate data sets in different formats, significantly shortening the time to insight, increasing productivity, and reducing reliance on ETL (see Figure 9-2).

Support for Multiple Data Sources

Even though Zoomdata was designed for big data, it also supports the most popular MPP and SQL databases in the market including Oracle, SQL Server, Teradata, MySQL, PostgreSQL, Vertica, and Amazon Aurora. ^viii In almost all enterprise environments you will find a combination of various flavors of SQL databases and Hadoop distributions. With Zoomdata Fusion, Zoomdata allows users to easily query these disparate data sources just like it was a single data source. Other Zoomdata features that enables fast visual analytics such as data sharpening, Data DVR, and micro-queries all work on these SQL and MPP databases as well. Other popular data sources such as MongoDB, Solr, Elasticsearch, and Amazon S3 are also supported (see Figure 9-3).

Let’s get started with an example. Start Zoomdata. You’ll be presented with a login page that looks like Figure 9-4.

The first thing you need to do is create a new data source (see Figure 9-5).

Notice that the Kudu Impala data source added to the list of data sources (see Figure 9-6).

You will be required to input your credentials, including the JDBC URL and the location of the Kudu Master (Figure 9-7). In our example, the Kudu Master resides at 192.168.56.101:7051. Update the JDBC URL to jdbc:hive2://192.168.56.101:21050/;auth=noSasl. Note that even though we’re connecting to Impala, we need to specify “hive2” in our JDBC URL. Click “Next” when done (Figure 9-8).

Pick the table that we will be using in this example (Figure 9-9). You can also select fields (Figure 9-10). Unless you’re running out of RAM, it’s always a good idea to turn on caching. Since our table is from a relational data store, you don’t need to enable SparkIt. If you remember, SparkIt is only for simple data sources such as text files that do not provide caching capabilities. Click Next to proceed to the next step.

Zoomdata can maintain cached result sets for certain data sources asynchronously (Figure 9-11).

You can also schedule a job to refresh the cache and metadata (Figure 9-12).

For advanced users who are more comfortable with the cron Unix-style scheduler, a similar interface is also available (Figure 9-13).

To support real-time visualization, Live Mode and Playback must be enabled (Figure 9-14). As long as the data set has a time attribute, in our case the sensortimestamp, Zoomdata can visualize using the time attribute. I set the refresh rate and delay to 1 second. I also set the range from Now-1 minute to Now.

You can configure the available charts (Figure 9-15).

There are different configuration options available depending on the type of chart (Figure 9-16).

The data source will be saved after you click “Finish.” You can immediately create a chart based on the source (Figure 9-17). In this case we’ll pick “Bars: Multiple Metrics.”

A bar chart similar to Figure 9-18 will be displayed. You can adjust different bar chart options.

When a chart is maximized, you’re provided with more configuration options such as Filters, Color, and Chart Style to name a few. In the example shown in Figure 9-19, I’ve changed the color of the bar chart from yellow and blue to purple and green.

To add more charts, click “Add Chart” located near the upper-right hand corner of the application (Figure 9-20).

The pie chart is one of the most common chart types (Figure 9-21).

Zoomdata has numerous chart types as shown in Figure 9-22.

Zoomdata has mapping capabilities as shown in Figure 9-23.

Zoomdata can update the map with markers in real time as shown in Figure 9-24.

Create the Kudu Table

Zoomdata requires the Kudu table to be partitioned on the timestamp field. To enable live mode, the timestamp field has to be stored as a BIGINT in epoch time. Zoomdata will recognize the timestamp field as “playable” and enable live mode on the data source (Listing 9-1).

Design the Pipeline

We can now design the StreamSets pipeline. Refer to Chapter 9 for a more in-depth discussion of StreamSets. The first thing we need to do is define an origin, or the data source. StreamSets supports different type of origins such as MQTT, JDBC, S3, Kafka, and Flume to name a few. For this example, we’ll use a “File Tail” origin. Set data source to sensordata.csv. We’ll run a script to populate this file later (Figure 9-26).

The File Tail data source needs a second destination for its metadata. Let’s add a Local FS destination to store metadata (Figure 9-27).

We’ll need a field splitter processor to split our CSV data into individual columns (Figure 9-28). We’ll specify our SDC fields in the New Split Fields configuration box (Listing 9-4).

[

 "/rowid",

 "/deviceid",

 "/temperature",

 "/pressure",

 "/humidity",

 "/ozone",

 "/sensortimestamp_str",

 "/city",

 "/temperature_warning",

 "/temperature_critical",

 "/pressure_warning",

 "/pressure_critical",

 "/humidity_warning",

 "/humidity_critical",

 "/ozone_warning",

 "/ozone_critical",

 "/lead_level",

 "/copper_level",

 "/phosphates_level",

 "/methane_level",

 "/chlorine_level",

 "/lead_warning",

 "/lead_critical",

 "/copper_warning",

 "/copper_critical",

 "/phosphates_warning",

 "/phosphates_critical",

 "/methane_warning",

 "/methane_critical",

 "/chlorine_warning",

 "/chlorine_critical"

]

Listing 9-4

New SDC Fields

We’ll also need a field type converter to convert the data types of the fields generated by the field splitter processor (Figure 9-29).

When configuring the field type converter processor, we have to specify the fields that we need to convert and the data type to convert the data into. All fields are set to STRING by default by SDC. You need to convert the fields to their correct data types (Figure 9-30).

Kudu expects the timestamp to be in Unix time format. We’ll use a JavaScript Evaluator (Figure 9-31) and write code to convert the timestamp from STRING to Unix time format as BIGINT ^ix (Listing 9-5). Note that this limitation has been addressed in recent versions of Kudu. However, Zoomdata still expects the date to be in Unix time format. Consult Chapter 7 to learn more about StreamSets evaluators.

Date.prototype.getUnixTime = function() { return this.getTime()/1000|0 };

if(!Date.now) Date.now = function() { return new Date(); }

Date.time = function() { return Date.now().getUnixTime(); }

for(var i = 0; i < records.length; i++) {

  try {

    var someDate = new Date(records[i].value['sensortimestamp_str']);

    var theUnixTime = someDate.getUnixTime();

    records[i].value['sensortimestamp'] = theUnixTime;

    output.write(records[i]);

  } catch (e) {

    error.write(records[i], e);

  }

Listing 9-5

Javascript Evaluator code

Finally, we use a Kudu destination as the final destination of our data. Make sure you map the SDC fields to the correct Kudu column names (Figure 9-32).

That’s it on the StreamSets end.

Configure Zoomdata

Let’s configure a data source for Zoomdata. We’ll use the Kudu Impala connector (Figure 9-33).

Enter the connection information such as the IP address and port number for the Kudu Masters, the JDBC URL, and so on. Make sure to validate your connection (Figure 9-34).

Choose the table that you just created earlier. You can exclude fields if you wish (Figure 9-35 and Figure 9-36).

You can schedule data caching if you wish. If you have a large data set, caching can help improve performance and scalability (Figure 9-37).

Configure the time bar. Make sure to enable live mode and playback (Figure 9-38).

You can also set default configuration options for charts (Figure 9-39).

Save the data source. You can now start designing the user interface (Figure 9-40).

I choose Bars: Multiple Metrics as my first chart. You won’t see any data yet because we haven’t started the StreamSets pipeline (Figure 9-41).

Let’s start generating some data. On the server where we installed StreamSets, open a terminal window and run the script. Redirect the output to the File tail destination: ./generatetest.csv >> sensordata.csv.

Start the StreamSets pipeline. In a second, you’ll see data coming in StreamSets via the different statistics about your pipeline such as Record Throughput, Batch Throughput, the Input and Output Record Count for each step. It will also show you if there are any errors in your pipeline (Figure 9-42).

As you are designing your Zoomdata dashboard, charts are immediately populated (Figure 9-43).

Congratulations! You’ve just implemented a real-time IoT pipeline using StreamSets, Kudu, and ZoomData.

Trifacta

Trifacta is one of the pioneers of data wrangling. Developed by Stanford PhD Sean Kandel, UC Berkeley professor Joe Hellerstein, and University of Washington and former Stanford professor Jeffrey Heer. They started a joint research project called the Stanford/Berkeley Wrangler, which eventually became Trifacta. ^xii Trifacta sells two versions of their product, Trifacta Wrangler and Trifacta Wrangler Enterprise. Trifacta Wrangler is a free desktop application aimed for individual use with small data sets. Trifacta Wrangler Enterprise is designed for teams with centralized management of security and data governance. ^xiii As mentioned earlier, Trifacta can connect to Impala to provide high-performance querying capabilities. ^xiv Please consult Trifacta’s website on how to install Trifacta Wrangler.

I’ll show you some of the features of Trifacta Wrangler. Trifacta organizes data wrangling activities based on “flows.” Trifacta Wrangler will ask you to create a new flow when you first start the application.

The next thing you need to do is to upload a data set. Trifacta can connect to different types of data sources, but for now we’ll upload a sample CSV file containing customer information. After uploading the CSV file, you can now explore the data. Click the button next to the “Details” button and select “Wrangle in new Flow” (Figure 9-44).

You will be shown the transformer page that looks like Figure 9-45. Trifacta immediately detects and shows how data in individual columns are distributed.

You can scroll to the left or right of the chart that shows the distribution of data to quickly view the contents of the column. This will help you determine the data that need to be processed or transformed (Figure 9-46).

Under the column name, you’ll see a data quality indicator for the specific column. Mismatched data types will be shown as red, and empty rows will be shown as gray. The indicator should be green if the data stored in that column are all valid (Figure 9-47).

By clicking a specific column, you will be presented with various suggestions on how to transform data stored on that column. This feature is powered by machine learning. Trifacta learns from your past data wrangling activities to make the next suggestions (Figure 9-48).

Different columns get different suggestions (Figure 9-49).

Click the button next to the column name to get a list of data transformation options for the particular column. You can rename the column name, change data types, filter, clean, or perform aggregation on the column to name a few (Figure 9-50).

Almost every type of data transformation is available (Figure 9-51).

Double-clicking the column will show you another window with various statistics and useful information about the information stored in that column. Information includes top values, the most frequent values, and string length statistics to name a few (Figure 9-52).

Trifacta is smart enough to know if the column includes location information such as zip code. It will try to show the information using a map (Figure 9-53).

Let’s try one of the suggested data transformations. Let’s convert the last name from all uppercase to a proper last name (only the first letter is uppercase). Choose the last suggestion and click the “Modify” button to apply the changes (Figure 9-54).

The last name has been transformed as shown in Figure 9-55.

Click the “Generate Results” button near the upper-right corner of the page (Figure 9-56).

You can save the result in a different format . Choose CSV for our data set. You can optionally compress the results if you want. Make sure the “Profile Results” option is checked to generate a profile of your results (Figure 9-57).

A “Result Summary” window will appear. It will include various statistics and information about your data. Review it to make sure the results are what you expect. Trifacta doesn’t have built-in data visualization. It depends on other tools such as Tableau, Qlik, Power BI, or even Microsoft Excel to provide visualization .

Alteryx

Alteryx is another popular software development company that develops data wrangling and analytics software. The company was founded in 2010 and is based in Irvine, California. It supports several data sources such as Oracle, SQL Server, Hive, and Impala ^xv for fast ad hoc query capability.

You’ll be presented with a Getting Started window the first time you start Alteryx (Figure 9-58). You can go through the tutorials, open recent workflows, or create a new workflow. For now let’s create a new workflow.

Alteryx is known for its ease of use. Most of the user’s interactions with Alteryx will involve dropping, dragging, and configuring tools from the Tool Palette (Figure 9-59).

The Tool Palette provides users with tools organized into tool categories. You drag these tools into the canvas and connect them to design your workflows. The tools let users perform different operations that users would normally perform to process and analyze data. Popular operations include select, filter, sort, join, union, summarize, and browse to name a few. These are tasks that users would normally perform by developing SQL queries. Alteryx provides a much easier and more productive method (Figure 9-60).

Alteryx also provides a myriad of tools for more complicated operations such as correlation, transposition, demographic analysis, behavior analysis, and spatial match to name a few. Explore the other tabs to familiarize yourself with the available tools (Figure 9-61).

Every workflow starts by specifying a data source using the Input Data tool. Drag and drop the Input Data tool to the workflow window. You can specify a file or connect to an external data source such as Impala, Oracle , SQL Server, or any data source that provides an ODBC interface (Figure 9-62).

We’ll get our data from a file in this example (Figure 9-63).

Alteryx comes with several sample files . Let’s use the Customers.csv file (Figure 9-64).

Now that you’ve configured a data source, let’s drag and drop the Select tool. The Select tool lets you select specific columns. The columns are all selected by default, so let’s deselect some columns for this example. Make sure to connect the Input Data and Select tools as shown in Figure 9-65.

After we’ve selected a few columns, we can sort the results with the Sort tool. Connect the Select tool with the Sort tool. The Sort tool lets you specify how you would like to sort the results. In this example, we’ll sort by City and Customer Segment in ascending order. You can do that via the configuration option (Figure 9-66).

We could go on by adding more tools and make the workflow as complex as we want, but we’ll stop here for this example. Almost every Alteryx workflow ends by browsing or saving the results of the workflow .

Let’s browse the results to make sure the data looks correct. Drag a Browse tool on the workflow window and make sure it connects with the Sort tool (Figure 9-67).

Run the workflow by clicking the green run button located in the upper corner of the window, near the Help menu item. After a few seconds, the results of your workflow will be shown in the output window. Inspect the results and see if the correct columns were selected and the sort order is as you specified earlier.

Like Trifacta, Alteryx also profiles your data and shows helpful statistics and information about it such as the distribution of data in the column, data quality information, data type of column, number of NULLs or blank values, average length, longest length, etc. In Figure 9-68, it shows statistics about the CustomerID field.

Click on the Customer Segment field to show statistics about the column (Figure 9-69).

Figure 9-70 shows statistics about the City field .

As mentioned earlier, we can also save the results to a file or an external data source such as Kudu, Oracle, SQL Server, or HDFS . Delete the Browse tool from the workflow window and replace it with the Output Data tool (Figure 9-71).

Let’s save the results to a file (Figure 9-71). The configuration window on the left side of the screen will let you set options on how to save the file, including the File Format. As shown, you can choose to save the file in a different format such as CSV, Excel, JSON, Tableau data extract, and Qlikview data eXchange to name a few.

Let’s save the data in CSV format (Figure 9-72). After saving the results, let’s inspect the file with Notepad to make sure that the data was saved correctly (Figure 9-73).

Congratulations! You now have some basic understanding of and hands-on experience with Alteryx.

Datameer

Datameer is another popular data wrangling tool with built-in data visualization and machine learning capabilities. It has a spreadsheet-like interface and includes over 200 analytics functions. ^xvi Datameer is based in San Francisco, California, and was founded in 2009. Unlike Trifacta and Alteryx, Datameer has no connector for Impala, although it has connectors for Spark, HDFS, Hive, and HBase. ^xvii

Let’s explore Datameer. First thing you need to do is upload a data file (Figure 9-74).

Specify the file and file type and click Next (Figure 9-75).

You can configure the data fields. Datameer allows you to change the data type, field name, and so on (Figure 9-76).

Data is presented in spreadsheet format (Figure 9-77).

Just like Trifacta and Alteryx, Datameer profiles your data and provides statistics and information about your data fields (Figure 9-78).

Using a feature called Smart Analytics , Datameer provides built-in support for common machine learning tasks such as clustering, classification with decision trees, and recommendations (Figure 9-79).

Performing clustering can show some interesting patterns about our data (Figure 9-80).

The data will be grouped into clusters . The groupings will be added to your data set as another column. Each cluster will be identified by a cluster id (Figure 9-81).

Classification can also be performed using decision trees (Figure 9-82).

Data will be classified via an additional prediction field as shown in Figure 9-83.

As previously mentioned, Datameer includes built-in data visualization (Figure 9-84).