Overview

Skill Level: Beginner

This recipe will show you how to use IBM Watson Analytics to analyze elevator device data stored in a Db2 Warehouse on Cloud Data Warehouse by creating dashboard and a few status and trend reports.

Step-by-step

  1. Introduction

    The Recipe ‘Simulate an Elevator – All using Node-RED nodes‘ showed you how you can simulate a real world IoT scenario taking an Elevator as example and using Node-RED. A second recipe titled ‘Monitor Elevator events in real time to take corrective actions using Watson IoT Platform‘, demonstrated how to perform real time analytics on the device events and how to implement a closed control loop putting the elevators in maintenance mode should the motor temperature go beyond 200 degree. A third recipe titled ‘Store Elevator device data and alerts in Cloudant NoSQL DB‘ went through different scenarios that can be used to store device data in an IBM Cloudant database for later use e.g. by a data scientist tool. Building on that, a fourth recipe titled ‘Create data warehouse for reporting on Elevator device data using DB2 Warehouse on Cloud‘ showed how you could construct a Data Warehouse for reporting by Business Analytic tools using Db2 Warehouse on Cloud.

    In this recipe we shall go through some basic steps to using IBM Watson Analytics to build some reports and dashboards using the elevator device data stored in Db2 Warehouse on Cloud. IBM Watson Analytics is a cloud based self-serve reporting tool that leverages Watson’s unique cognitive capabilities. Unlike IBM Data Science Experience it does not require programming language experience in order to get insight into the historical data. It is merely an analytical starting point for every business user with no specific training required. It can access data sources on-premise or in the cloud. It helps the user by automatically accessing the quality of the data and provide guided navigation, natural language dialogues and contextual infographics leading to new insights.

    The recipe will take you through the following basic steps using IBM Watson Analytics to analyze the device data:

    1. Get the Db2 Warehouse on Cloud credentials and start up Watson Analytics.
    2. Exchange of data using Comma Separated Value (CSV) files.
    3. Connect to the Db2 Warehouse on Cloud database using IBM Watson Analytics and create a data source for reporting.
    4. Create a report showing distribution of motor temperature alerts using natural language query as a starting point.
    5. Create a report that shows the number of alerts over time.
    6. Create a reports showing drivers for the motor temperature alerts.
    7. Create a dashboard for sharing the 3 reports with other users.

    Step 2 and 3 are exclusive: you can either get hold on the data in the Data Warehouse by exporting data out of Db2 Warehouse on Cloud and then importing it into IBM Watson Analytics or you can directly connect to Db2 Warehouse on Cloud from within IBM Watson Analytics. The latter mode requires Team Administrator access rights however which may not be available to all accounts. Step 3 will also provide a short introduction on how to use capabilities of IBM Data Connect – running under the hood of IBM Watson Analytics – to refine the data and prepare it for analysis.

  2. Architecture

    Before you start it is important to recall some details on the data flow and the architecture of the elevator simulator application and it’s components of the Watson Data Platform:

     

    00-Architecture-1

    The elevator simulator sends the data to the Watson IoT service, which in turn stores the data in Cloudant NoSQL DB using e.g. the Historic Data Storage Extension or a specific Node-RED flow. From Cloudant the data is then synchronized to the IBM DB2 Warehouse on Cloud data warehouse using Cloudant Analytics. Another option is to use Node-RED flows to directly store the information in the data warehouse.

    All 3 approaches represent a fast path for getting the data into the data warehouse for subsequent analysis and development of dashboards and reports. In this recipe we shall work with event data that was stored in the table ELEVATOR_EVENTS which is basically time series information generated every 5th second. This set of data was saved to Cloudant by a Node-RED flow and from there synchronized to Db2 Warehouse on Cloud using Cloudant Analytics.

  3. Getting Started

    In order to get started you will need to get hold on the credentials for the Db2 Warehouse on Cloud service created in the recipe titled ‘Create data warehouse for reporting on Elevator device data using Db2 Warehouse on Cloud‘ first. To achieve this, do the following:

    1. In the IBM Cloud dashboard, select the data lake application named e.g. elevator-datalake-<your name> that you created in the previous recipe.
    2. Click the Runtime tab, then the Environment variables tab and copy the credentials of the Db2 Warehouse on Cloud service for later use.
      01-dashDB-Credentials

    Next, open the Db2 Warehouse on Cloud dashboard so that you have an insight into the data available using the service. You will need the names of the tables for later use:

    1. In the Connections tab of your data lake application, select the Db2 Warehouse on Cloud service to open up the dashboard for the service.
    2. Click the OPEN button to open the Db2 Warehouse on Cloud console.
    3. Click Explore to view the existing tables.
    4. For the Schema, select the schema named by your dash DB user account (e.g. DASH6769).
    5. Select the table ELEVATOR_EVENTS:
      02.2U-Elevator-Events-Table-Definition

    Finally, launch IBM Watson Analytics. If you do not yet have access to IBM Watson Analytics you can obtain access to a trial account at the IBM Watson Analytics Home Page. Click one of the red colored ‘Try it for free‘ buttons on the home page and provide your IBM ID to get access to the tool.

    01-Watson-Analytics-Home-Page

    When you have achieved access to IBM Watson Analytics you should eventually see the following in the browser window:

    01-Watson-Analytics

    The 3 tiles at the top are the main entry points for defining new data sources, to discover relevant information in the data sources under guidance of IBM Watson Analytics and to share interesting findings by creating dashboards.

    There are two ways to make elevator device data stored in Db2 Warehouse on Cloud available to IBM Watson Analytics. The simple and fastest way is to exchange the data using Comma Separated Value (CSV) files, which require an export out of Db2 Warehouse on Cloud and an import into IBM Watson Analytics. This approach will work for all accounts of IBM Watson Analytics, i.e. also trial accounts, and will be covered in the next section.

    The other and more refined approach is to create a direct connection to Db2 Warehouse on Cloud which however requires Team Administrator rights. If you are not a Team Administrator, then your Watson Analytics Team Administrator can create the connection to the database for you. IBM’ers also have the possibility to request access to a version of IBM Watson Analytics that provide Team Administrator access rights by contacting IBM Support.

    We recommend that you continue with section 5 first to create a direct connection to Db2 Warehouse on Cloud. You should very soon be able to determine if IBM Watson Analytics offers you the rights to create a new connection. If not, continue with section 4 and exchange the data using Comma Separated Value files.

  4. Exchange data using Comma Separated Value files

    The exchange of data using Comma Separated Value files require 2 steps: export of data out of Db2 Warehouse on Cloud and import into IBM Watson Analytics.

    In order to try this path do the following first to export the data out of IBM dashDB:

    1. Go to the web page that shows the dashDB dashboard.
    2. Select the table named ELEVATOR_EVENTS.
    3. Select the View Data tab to view the data in the table.
    4. Click the Export as CSV button in the upper right corner:
      04.1U-Elevator-Events-Table-Content
    5. Copy the file to a folder of your choice and rename it to elevator_events.csv.

    Next step is to import the data into IBM Watson Analytics now that it has been exported out of Db2 Warehouse on Cloud. Do the following:

    1. Go to the IBM Watson Analytics web page.
    2. lick the New data button in the Data section:
      02-WA-New-Data
    3. In the resulting window, select the Local file tab:
      02-WA-Local-File
    4. Drag and drop the file elevator_events.csv onto the canvas as suggested by IBM Watson Analyzer.
    5. Click Import.
      02-WA-Imported-Data
    6. This will take you back to the main window of IBM Watson Analyzer which will display a new tile for the imported data source.

    This completes exchange of data using export and import.

  5. Connect to the Db2 Warehouse on Cloud Data Warehouse

    With IBM Watson Analytics it is possible to connect directly to the Data Warehouse that was created in the recipe titled ‘Create data warehouse for reporting on Elevator device data using Db2 Warehouse on Cloud‘, and then use the tables in Db2 Warehouse on Cloud as a data source for discovery. There are several steps involved in this process:

    1. Create a new connection to the Db2 Warehouse on Cloud database that holds the Data Warehouse.
    2. Given the new connection, create a new data source by selecting the table to be used for the data source.
    3. Optionally, refine the data in the Data Warehouse to bring it into a form that is more suitable for the current discovery process.

    We shall demonstrate all 3 steps involved. Let’s simply start by establishing the connection to the dashDB Data Warehouse first. For this step, you will need the IBM dashDB credentials that were identified in the step ‘Getting Started’:

    1. In IBM Watson Analytics, select Account settings in the upper right corner:
      03-Account-Settings
    2. In the window showing the account details, select the Data connections tab.
      03-Data-Connections
    3. Click the ‘+’ button in the upper right corner to add a new connection.
      03-Select-dashDB
    4. Select the tile named IBM dashDB and click Create.
      03-Data-Connection-Configuration
    5. Specify the connection details to the Db2 Warehouse on Cloud service using the information from the VCAP properties that you copied in section ‘Getting Started’:
      1. Provide the hostname.
      2. Provide the user name and password credentials.
      3. Provide the database name.
      4. Enter a connection name, e.g. ‘elevator_alerts’.
    6. Click the Create button to create the new connection.
    7. Wait until the data connection has been created:
      03-Data-Connections-1
    8. Click ‘IBM Watson Analytics’ in the upper right part of the browser window. This will take you to the starting page of IBM Watson Analytics.
    9. Select the Data tile in the upper left corner of the browser window.

    You have now added a new connection to an external database in the Cloud, but you have not created any data source that you can discover and report on. Consequently, if you browse the available personal or shared data sources, there will be no data source for the event data. You will need to create this data source nex:

    1. Click the +New Data button to add a new data source.
      02-WA-New-Data
    2. Select the Connection tab in the middle.
      03-Add-Data-Source
    3. To define the connection do the following:
      1. Select the tile for the data connection that you created in this step named elevator event data.
      2. Select the schema that contains your elevator event data.
      3. Select the table ELEVATOR_EVENTS.
    4. Click Shape Before and wait until the data are rendered.

    If you clicked the button Upload now the new data source would simply be added for you and you would be ready to discover the data using the features of IBM Watson Analytics. When selecting Shape before, you have the possibility to refine the data using capabilities of IBM Data Connect under the hood of IBM Watson Analytics. We shall take you through a few steps using the refinement operations by deleting some superfluous columns and by deriving a new Date column from the Timestamp column:

    1. Click the Toggle metrics button in the top middle of the screen to show the data quality of each column.
      03.2-Refine-Data-Source-ED
    2. Scroll to the very right and select the column _ID.
    3. Delete the column by selecting Remove column using the pop-up menu:
      03.2-Remove-Data-Column
    4. Repeat the remove column operation for the columns _REV, MAINTENANCE_REASON and MAINTENANCE_STOP.
    5. Select the TIMESTAMP column. We shall use this column next to add a new column that contains the Date only. This is basically done by splitting the TIMESTAMP column into 2 parts, the first containing the proper date, the next containing the rest (to be discarded).
    6. From the set of operations in the right part of the window, find the operation Split column on delimiter in the Organize submenu.
      03.3-Split-Column
    7. Provide the parameters for the operation as shown below:
      03.3-Split-Column-2
      1. Enter DATE as the name of the first column and REST as the name of the second column.
      2. Select true to keep the original column.
      3. Select Others for the delimiter, and enter T as the delimiting character.
      4. Click Apply.
    8. Select the new DATE column and invoke the operation String to date.
      03.3-String-to-Date
    9. Select the column named REST and delete it.
    10. We are now completed refining the data. In the upper part of the window, select the operation Upload my data to save your changes.
      03.3-Upload-Data
    11. Enter ‘elevator event data‘ name for the new data source.
      03.3-Upload-Data-Save-As
    12. Click OK to save your refinement operations.

    This will take you back to the starting page and you should now see your new data source:03.4-Uploaded-Data

  6. Discover distribution of alerts by elevator

    With the device data at hand, it would be interesting to discover if there are specific elevators that got problems with overheated engines. To carry out that analysis, do the following:

    1. Select the Data tile in them main window of IBM Watson Analytics.
    2. Select the tile for the data source named elevator event data that you have just created.
      04-Data-Source
    3. IBM Watson Analytics will provide you with a set of starting points for the discovery.
      04-Starting-points
    4. Enter the query ‘number of rows by device id‘ and press return.
      04-Rows-by-device-id
    5. IBM Watson Analytics will provide you with a number of reports that could make sense for that query. The first one – a bar chart titled ‘How does the number of Rows compare by DEVICEID‘ seems to be what we are looking for.
    6. Select this report. You should now see an almost equal distribution of events for all the elevators:
      04-Rows-by-device-id-result
    7. What is interesting however is to analyze the device events where the motor temperature is above 200 degree. Scroll to the right until you find the attribute named MOTORTEMP.
      04-Rows-by-device-id-result-with-filter
    8. Select the MOTORTEMP attribute and set the filter as shown above by entering the number 200 in the text field to the left. For the current data set, it is clear that only 2 elevators have problems with overheated engines and that Elevator01 is the one with the largest number of incidents.
    9. You can change the bar chart to a pie chart. Simply select Visualization button in the top left corner. You will then be presented with the options for rendering the results. Select Pie.
      04-Rows-by-device-id-result-as-pie-chart
    10. Next, provide a title for the report. Select the text field in the top of the report (marked with a red box in the picture) and enter ‘Elevators with overheated engines‘.
      04-Rows-by-device-id-save-as
    11. Click the Save button in the toolbar. Select Shared for the location and then click the Save button.
    12. Click ‘IBM Watson Analytics‘ in the upper left corner of the window to take you back to the main window.
  7. Discover trend for alerts

    The previous discovery is a main stream analysis showing distribution of one key metric (COUNT) according to a dimension (DEVICEID), filtered by another dimension (MOTORTEMP). Another typical discovery would be to view the distribution over time. We will do this now.

    1. Select the Data tile In IBM Watson Analytics.
    2. Select the tile for the data source named ‘elevator device data‘ that you created in step 3.
    3. Enter the query ‘number of rows by DEVICEID by DATE‘ and press return:
      05-Query
    4. Select the suggested trend report ‘What is the trend of the number of rows over DATE by DEVICEID‘:
      05-Report
    5. This will show the trend for all device data over time by elevator.
    6. You will need to filter this by MOTORTEMP so that the report just shows the device data that are causing the elevator to go into maintenance mode. Create a filter as in the previous section:
      05-Filter
    7. Notice that IBM Watson Analytics will provide a deep analysis of the data to identify correlations between the data, and on that basis suggest new discoveries for you. Looking at the Discoveries section to the right, you will see that Elevator05 is the one with the highest measured speed and Elevator10 with the highest load. You can scroll down the list to determine new discoveries suggested by IBM Watson Analytics.
    8. Next provide a title for the report, e.g. ‘Elevators with overheated engine over time’:
      05-Trend-report
    9. Save the report using the name ‘Elevator trend analysis’ to the Shared folder.
    10. Click ‘IBM Watson Analytics’ in the upper left corner of the window. This will take you back to the main window of IBM Watson Analytics.

    The result of this report as shown above does probably not reflect a real time scenario of maintenance stops for elevators caused by overheated motors. It simply shows how often that situation was triggered using the elevator simulator, e.g. during tests or demos.

  8. Discover drivers for motor temperature

    As a last report we shall look into if there are any specific drivers that causes the motor temperature to increase beyond 200 degree.

    1. Select the Data tile in IBM Watson Analytics.
    2. Select the tile for the data source named ‘elevator device data‘ that you created in step 3.
    3. Enter the query ‘drivers for MOTORTEMP‘:
      06-Query-1
    4. Select the discovery ‘What drives MOTORTEMP‘.
      06-Report-1
    5. This will show you drivers for motor temperature, which seems to be DEVICEID, LOAD and CABINTEMP.
    6. You can define a filter if you like as in the previous reports.
    7. In all cases, enter a title for the discovery such as ‘Drivers for motor temperature’.
      06-Name-and-Save
    8. Save the discovery as shown above to the Shared folder.
    9. Notice that you can view the data behind the discovery if you click the arrows in the bottom right part of the window. You can also try out some of the suggested discoveries in the right part of the window:
      06-Details
    10. Select IBM Watson Analytics in the upper left corner of the window to get back to the main window.

     

  9. Share discoveries using a dashboard

    As a last step we will create a dashboard to share our discoveries.

    1. Select the tile named Discover. You should now see the 3 discoveries that you have previously created:
      07-Discoveries
    2. Select the tile named Display.
      07-New-Display
    3. Click the New display button to create a new dashboard.
    4. Give the new display a name, e.g. ‘Elevator discoveries‘.
      07-Name-New-Display
    5. Click Create.
    6. Select as layout the Freeform display:
      07-layout
    7. In the resulting window, select the folder named Shared that contains the discoveries that you created:
      07-Add-Discoveries
    8. Expand the discovery ‘Elevators with overheated engines’ by selecting the arrow to the right of the discovery.
    9. Drag and drop the discovery onto the canvas as indicated by the red markers in the screenshot.
      07-Add-to-canvas
    10. You can use the icons marked red to either resize or reposition the discovery on the canvas as you like.
    11. Repeat the process for the two other discoveries that you created.
      07-Completed-dashboard
    12. Provide a title for the display, e.g. ‘Elevator discoveries’.
    13. Save the discovery in a similar way as you saved the discoveries that you created by giving it a suitable name and making it shared.
      07-Save
    14. Click IBM Watson Analytics in the top left corner. You should now see your new discovery:
      07-Result

    Notice that you can, once that you have added discoveries to the display, set filters and change the format etc. To set a filter, select the Filters icon to the left of the window, then click the + button to the left of the attribute that you would like to filter on (e.g. YEAR). This will create a new filter in the top right of the window and by clicking the filter you can then set the filter as shown below.

    07-Filter

  10. Conclusion

    In this recipe we have gone through some basic steps in using IBM Watson Analytics to build reports and dashboards for analytics using elevator device data. By doing so we have demonstrated some self-serve reporting features that leverages Watson’s unique cognitive capabilities in three ways:

    1. Data Discovery: Provides semantic recognition of concepts in the data such as drivers for motor temperature.
    2. Guided Analytics: Identifies recommended starting points for analysis.
    3. Natural Language: Enables interaction in natural language allowing for the exploration of data through simple questions.

     

    The recipe followed a few simple steps by creating a data connection to an external Db2 Warehouse on Cloud Data Warehouse and then defining the data source by selecting the appropriate Db2 Warehouse on Cloud table. Following that setup a number of discoveries (i.e. reports) where created and then in a final step added to a display (i.e. dashboard) suitable for team collaboration.

1 comment on"Analyze Elevator device data using IBM Watson Analytics"

  1. complutum May 18, 2017

    Hello,

    I think step 4 (create data connection between Watson Analytics and the dashDB) requires to be in the “plus” edition and can’t be implemented with the “free trial”.

Join The Discussion