Data Tool Kit - Business Intelligence Handbook

For the purpose of this handbook, BI is considered to be any software program that allows users to access, manipulate, analyze, and interactively visualize data, and create sophisticated data-based reports and applications, all with little to no direct reliance on support staff.

There are a wide variety of business intelligence tools currently available, with a wide range of functionality, ease-of-use, external support, and cost. Several of the leading options are currently available to Water Boards staff, and the Water Board’s Office of Information Management and Analysis (OIMA) prepared a white paper (Business Intelligence Recommendation for Water Board Programs and Staff) that assesses six business intelligence platforms that are available to Water Board staff as of April 2019, including:

• Microsoft Excel: widely used spreadsheet software with basic data visualization capabilities
• Microsoft PowerBI: full service BI software with tools for importing and manipulating data from a variety of sources, creating interactive, data-driven visualizations and reports, and sharing those data products on the web
• ArcGIS: widely used software for managing, analyzing, and visualizing spatial data with additional tools for creating interactive maps and story maps
• Tableau: well established full service BI software that can connect to and access data from a wide variety of data sources, perform data preparation and cleaning tasks, produce sophisticated interactive visualizations and dashboards, and deploy products on the web
• Shiny: free and open source tool for building highly customizable interactive web-based applications using the R programming language
• Jupyter Notebook: open-source web application for producing easily shareable documents that contain live code, equations, visualizations and narrative text
• JavaScript: programming language that is one of the core technologies of the modern web, used for rendering and manipulating interactive web pages, allowing users to create complex and highly sophisticated tools and applications

The report also provides a set of recommendations on how the State Boards can effectively adopt a range of these tools to meet staff needs while also remaining responsive to the rapidly changing and expanding set of business intelligence options available.

The table below compares popular BI tools available at the Water Boards, including: the types of data they can utilize, the best uses for each tool, and some things to consider regarding ease of use and licensing. More detailed information on how to access these BI tools, as well as training resources and examples of how the Water Boards has used each of these tools is provided below.

As of August 2019, Water Boards staff can access all of the tools assessed in the Business Intelligence White Paper described above. The sections below provide brief instructions on how Water Boards staff can install and begin using these tools. For more detailed information about how to use each tool, refer to the Additional Resources section below.

Microsoft Excel

Excel is available to all Water Boards staff as part of the standard Microsoft Office installation, and can be opened as a desktop application. In addition, a cloud version of Excel can be accessed through Microsoft Office 365.

Microsoft PowerBI

The Desktop version of PowerBI is available to all Water Boards staff, as the software is free and there are no restrictions on the number of licenses available. Staff simply need to submit a request to the IT helpdesk to install PowerBI. After installation, the software is accessible as a desktop application, or as a cloud-based version available via Microsoft Office 365.

The Desktop version of PowerBI does not allow a user to publish visualizations and reports to the web, but Desktop users can work with any staff member who has a PowerBI Pro license to publish their reports. Contact the IT department to locate a staff member with a PowerBI Pro license.

ArcGIS

ArcGIS desktop software is available to all Water Boards staff through the Software Center application. To install, open the Windows search toolbar and search for “Software Center”. Under Applications, select “ArcGIS Pro” or “ArcGIS”. (ArcGIS Pro is the new generation ArcGIS software that will eventually supersede ArcGIS/ArcMap). The installation package includes both the Basic and Advanced versions of the software. A Basic license is adequate for most use cases. An Advanced license offers additional functionality not accessible with a Basic license.

ArcGIS Portal is a separate platform for creating and publishing map layers, interactive maps, story maps, and other applications. Portal can be accessed through a web browser and does not require additional software; however, it is recommended that staff install ArcGIS Pro and connect it to Portal for full sharing and publishing functionality. More information about Portal is available on the internal GIS wiki. Email the GIS unit at GIS@waterboards.ca.gov to request access to Portal.

Tableau

A limited number of Tableau licenses are available to Water Boards staff (Tableau licenses are named licenses only, meaning they are tied to a specific user). Contact the IT department or the Water Board’s Office of Information Management and Analysis to inquire about license availability and installation. Once installed, Tableau can be run as a desktop application, and reports and visualizations can be published to a publicly available website.

In addition, a free version - Tableau Public - is available for all staff to install by contacting the IT department. However, this version of Tableau has a number of significant limitations (e.g., limited size and file type of data sources, inability to save Tableau workbooks to the desktop, inability to control access to the Tableau workbook or the underlying data), so it is likely only useful for evaluation purposes or projects with a very limited scope.

Shiny

Shiny is part of the free and open source set of tools available as part of the R programming language, so R must be installed on a user’s computer prior to installing Shiny. Staff may be able to download and install R and the associated RStudio software directly through the Software Center application, by opening the Windows search toolbar and searching for “Software Center”, then selecting the “R for Windows” and “RStudio” applications. If staff encounter any problems finding or using the Software Center application, contact the IT helpdesk and ask them to install R and RStudio.

After R and RStudio are installed on a user’s computer, Shiny can be installed by typing “install.packages('Shiny')” (without quotation marks) into the R Console. Note that to use Shiny, an intermediate or higher level of proficiency in R programming is generally a prerequisite. Currently (as of February 2020) visualizations and reports can be published for free to a publicly available website (www.shinyapps.io) which provides for a limited duration of total monthly usage time (25 hours/month). The Water Board also has a professional level license available for publishing. To utilize this resource for publishing, see the OIMA SharePoint site for the Shiny Application Publication Policies and Procedures.

Jupyter Notebook

Jupyter Notebook is a free and open source tool, and there are multiple ways to install it, as described in the documentation. As noted in the documentation, the recommended way to install Jupyter is via the Anaconda Distribution, which includes Python, the Jupyter Notebook, and other commonly used packages for scientific computing and data science. The Anaconda Distribution may be available for staff to download directly through the Software Center (search Windows for “Software Center”, then select “Anaconda”). Otherwise, contact the IT department and request installation of the Anaconda Distribution. Note that an intermediate or higher level of proficiency in at least one of the supported coding languages is generally a prerequisite to effectively use Jupyter Notebooks. Jupyter notebooks can be published publicly on the web via GitHub or other similar resources.

JavaScript

All that is needed to start programming in JavaScript is a modern web browser (e.g., FireFox, Chrome) and a text/code editor. Include the JavaScript code in a Hypertext Markup Language (HTML) file and open the HTML file in a web browser to execute the code. For examples of how to include JavaScript code in an HTML file, refer to the Mozilla Developer Network (MDN) Web Docs website. Opening the HTML file in a web browser will work in most cases; however, when loading external data, it is more reliable to run the files on a local web server. The MDN Web Docs website provides a step-by-step guide for starting a local web server using Python’s SimpleHTTPServer module. Note that client-side JavaScript is often used together with HTML and Cascading Style Sheets (CSS). All three web technologies (HTML, CSS, JavaScript) are needed to create dynamic, interactive web content.

Staff from the Water Boards’ Office of Information Management and Analysis are working in collaboration with staff from other divisions and offices to survey training needs and requests, and to develop training courses and programs that will include instruction on how to effectively use these (and potentially other) business intelligence tools. These training resources may take multiple forms (e.g., online self-paced tutorials, in-person classes and workshops, etc.), and will be announced as they become available.

There is also an abundance of resources available on the internet to help users get started using each of the tools above and answer many of the questions that arise when using them for more advanced applications. These resources are available in multiple formats, such as documentation files, tutorials, online classes, blog posts, or user forums, and many of these are easily found through a simple internet search, especially if you have a particular issue. However, the list of resources below can be used to help new users get oriented as they are starting out with any particular tool.

Microsoft Excel

• Microsoft’s Excel help center (training and tips)
• Data Carpentry data organization in spreadsheets (tutorial - primarily deals with formatting machine-readable data)
• See the Free Training Resources Handbook for more Microsoft Excel resources!

Microsoft PowerBI

• Microsoft’s PowerBI learning resources
• Microsoft’s PowerBI community forums
• Data Flair PowerBI Tutorials

Water Board Applications:

• TMDLs and 303d Listings
• Sanitary Sewer Overflows
• Inspections – all major programs

ArcGIS

• Free ESRI Online Training – be sure to check the “Free” box to display over 300 free resources in the catalog!
• Coursera GIS Specialization (online courses, free to audit)

Water Board Applications:

• Division of Water Rights Fully Appropriated Stream Systems (FASS) in California Web Map and Story Map
• Central Valley Water Board E. coli Monitoring Results
• 2019 Water Quality Status Report Story Map. See the Water Quality Status Report Page for more Water Quality Stats Report examples.
• Groundwater Ambient Monitoring and Assessment Program’s Suite of Online Tools
• Elk River Story Map
• For examples of other ESRI GIS visualization products, check out the California Water Boards GIS Gallery!

Tableau

• Tableau’s training videos
• Tableau’s community forums
• Data Flair Tableau Tutorials

Water Board Applications:

• Water Quality of California Beaches
• Proposition 1 Funded Water Projects
• Water Supplier Conservation Dashboard
• For over 250 examples of Tableau visualizations using Water Board datasets, check out Rafael Maestu’s Tableau Profile!

R and R Shiny (Programming Language)

• R for Water Resources Data Science - Introductory Course Materials
• R for Water Resources Data Science - Intermediate Course Materials
• A Succinct Intro to R
• Programming with R Tutorial
• Data Flair R Tutorials
• R for Data Science (book about general R and data science concepts)
• R Shiny Tutorials
  • Are you ready for Shiny? Quiz
  • Seven written tutorials covering the basics; 32 video tutorials covering more advanced material
• Stack Overflow (forum - use the search bar to find information about specific topics)
• See the Free Training Resources Handbook for more R and R Shiny resources!

Water Board Applications:

• Industrial Stormwater Quality Tool
• California Integrated Assessment of Watershed Health
• For a complete list of R Shiny applications that have been published on the Water Board’s Shiny server, please review the R Coded Applications Published on Shiny Server SharePoint Site.

Python and Jupyter Notebook

• Data Flair Python Tutorials
• Working with Geospatial Data: Basics in Python Tutorial, City of Los Angeles
• Jupyter/IPython Notebook Quick Start Guide Tutorial
• Jupyter Notebook for Beginners (tutorial)
• Jupyter’s Documentation
• See the Free Training Resources Handbook for more Python and Jupyter Notebook resources!

JavaScript

• Mozilla Developer Network (MDN) Web Docs
• Free Code Camp
• Eloquent JavaScript (book)