Using Visualizations

CONTENTS

Home

Every sheet in bipp is a visualization format for data that can be saved and used in dashboards. This section provides details about the types of visualizations available in bipp and how they can be used

Visualizing Data

In order to start visualizing a dataset on the Sheets screen, select a few columns from the tables listed on the left hand side and click on the Fetch button at the top-right corner of the screen. The data will be presented in a grid format by default as shown in the following diagram.

New Dataset Sheets

This is the very basic visualization offered by sheets. Lets dig deep to find what other options are available with respect to visualizations and settings.

Column Settings

Column data settings allow for usage of different features like aggregation, filtering and ordering when visualizing data. Following are the column level settings available when creating sheets.

  1. Aggregates: By default all integer columns are aggregated as a sum of values grouped by the other selected columns when visualizing data. The default aggregation may be changed in the data model itself using the column - default_aggregate property. Alternately, it may be changed on the sheet’s user interface. Allowed aggregations include Count, Count Distinct, Count If, Lower, Sum, Avg, Min and Max. There is also an option to disable aggregation for specific columns, so that the aggregate will not be calculated. When aggregation type “Count If” is chosen, you will be requested to enter the value that the aggregated column should be compared with.

  2. Filters: bipp provides 3 methods to filter data on sheets. Let us look into these one by one.

    • Params: Parameter columns defined in the bippLang data model would automatically appear on the sheet as a filter. You would be required to enter a value for the parameter if a linked column is selected.

    • Column Filters: These can be created by selecting “Filter” from the menu options available for a specific column. The compare operator and value to be used for filtering may be entered in the filters section on the right hand side. The condition will be used when fetching the data.

      If the column selected for filter is of date datatype, the filter displayed will be suitable for selection of range of dates or a specific date/day in relation to current date. For example you can choose to view data for Today, Yesterday, Last n days, Before 2019, Q1 2020 and so on.

    Filter Types

    • Custom Filters: A custom filter allows you to key in the condition to be used for filtering data directly in the “Filters” section. This condition would be appended as it is to the SQL query when fetching data.

    Following image shows a sheet that uses all of the 3 types of filters defined above. Filter Types

    Following is the query generated with the filters Filter Types

  3. Order: Data may be ordered by selecting “Order By” from the menu options available for a specific column. If multiple columns are used for ordering, they will be applied in the order in which they were selected. Default order is selected as DESC but may be changed to ASC under the “Order By” section on the right hand side of the screen.

    A column that is not selected for visualization may be used for ordering. In this case the column will appear under the Disabled section on the settings panel along with the Order By section. Following example shows a snapshot of data that is ordered by the month name in the first scenario and by the actual order of months in the second scenario.

    Order by

  4. Tooltips: Tooltips may be specified for columns by clicking on “Add Tooltip” from the menu options available for a specific column. On adding a tooltip a small (i) icon will appear next to the column heading. The tooltip can be viewed by hovering on this icon.

  5. Pivot: The pivot option is available for any of the selected dimension columns. This allows the user to pivot the Measure data based on the values in these columns. Pivot may be applied to specific dimension columns even when no other dimension is available. Pivot can be applied or removed by clicking on the options available for the column in the Data pane. Following images show the same data with and without a pivot on the column Age.

    Before Pivot Before Pivot

    After Pivot on Age After Pivot

  6. Drilldown: The drilldown option is available for any column which has been defined with the drilldown property in bippLang. For example, in the following example, column Region can be drilled down to show countries in the region.

    Drilldown 1

    Drilldown may also be applied to bar and pie charts as shown here.

    Drilldown 2

Visualization types

bipp sheets support many different types of visualizations for the same data. The visualization selected at the point of the last-save is used when displaying the sheet on the dashboard. Following are the types of visualization supported.

Table

This is the default visualization that shows the selected columns in a grid format with data in different rows. The grid comes with customizable pagination with default set to 10 rows at a time.

GridChart

This visualization can be used to analyze data across multiple dimensions in a pivot view. The fields to be shown in rows and columns as well as the measures to be shown in the grid can be customized.

Grid Chart

Bar

This visualization presents the data in a bar chart format. By default the first non-aggregate column selected is mapped on the X-axis, while all the aggregate columns selected are mapped on the Y-axis using different colors. A legend is included and can also be used to select a particular Measure or aggregate for visualization. The chart type and column mapping may be changed through Visualization Settings

Bar Chart

Pie

This visualization presents the data in a pie chart format. By default the pie is created using measures of the first aggregate column selected and labelled with values from the first non-aggregate column selected. Columns mapped may be changed through Visualization Settings

Pie Chart

Map

Columns with location coordinates or names of places as data can be visualized using this type of visualization. bipp uses the echarts library to render the map visualization. For example the following data may be visualized on a map as shown below.

Map 1

Maps can be also configured to show scatter pattern of data by country + city, or to display the network or path for flow of goods/commodities.

gMap

Data that includes dimensions based on Country + City or Latitude + Longitude can also be visualized by using the gmap visualization. This uses Google maps in the background. Four different types of gmaps may be used based on available data. These are as follows

  1. Geo Map (Choropleth Map): It can be used to map data containing locations (Country-City or Latitude-Longitude) with associated measures. Following image shows the visualization for the data on a gMap - Geo Map. The map uses different colors or shades based on the value of the measure mapped.

    Map 2

    The location and value are displayed as a tooltip as you hover over regions where data is mapped .

  2. Scatter Map: It can be used to map data containing locations with associated measures in a scatter type of visualization.

  3. Network Map: It can be used to map data containing two sets of locations to visualize the network path from source to destination. This type of a map can be generated without a measure column.

  4. Path Map: It can be used to visualize the path traversed by a particular entity based on the touch points identified by the location coordinates (latitude, longitude). The entity itself can be identified by marking it as the Segment column in map settings. This type of a map can be generated without a measure column. Following is an example of a segmented path map where the column Device Id has been used for segmentation.

    Map 2

Treemap

This visualization is used to present hierarchical data or tree data in the form of nested rectangles. Non-aggregate columns are used to define the structure of the treemap and aggregate columns are used to define the size or color of the individual rectangles. By default the first aggregate column is mapped to the area of the rectangle and the second aggregate column, if present, is mapped to the color of the rectangle. Column mapping can be changed through Visualization Settings.

Treemap

Metric

This visualization may be used for single value data points or high level metrics. It can be used on the dashboard to present a metric that can be drilled down further if required.

Metric

Graph

The graph type of visualization may be used to understand the relation or hierarchy between data points using nodes of a graph. For example, the following image shows the visualization for the map data as a graph. The source and target nodes as well as the column used to represent the value, may be configured for graphs.

Metric

Custom

The custom type of visualization can be used if you have created a custom visualization as a JavaScript extension using the bipp Extensions API.

Metric, map and custom visualizations are not enabled by default for all tenants. These need to be enabled on Tenant Settings screen

Visualization settings

Every visualization available for viewing sheets can be customized using the available settings. Following is a list of all available settings applicable to different types of visualizations. These can be accessed using the settings icon at the top right of the screen.

Setting type Auto Format
Applicable to All Types
Description This can be used to format numbers to use K for thousands, M for Millions and so on. E.g., 2450 will appear as 2.45K, 5870000 will appear as 5.87M.
Values Yes/No
Setting type Nav Bar
Applicable to Table
Description Show or hide the pagination or navigation bar displayed on top of the data grid
Values Yes/No
Setting type Index Column
Applicable to Table
Description Show or hide the index column that appears as the first column in the grid to display the row index.
Values Yes/No
Setting type Grand Total
Applicable to Table, Bar, Grid Chart
Description Show or hide a row for grand totals at the end of the grid or chart
Values Yes/No
Setting type Transpose Column
Applicable to Table
Description It can be used to provide a pivot view for the data where the aggregate columns are transposed to rows.
Values Yes/No
Setting type Show Table Name in Header
Applicable to Table
Description It can be used to show the table name along with the column names in the column header.
Values Yes/No
Setting type Color Palette
Applicable to Bar, Pie, Map, gMap
Description Allows selection of colors to be used for displaying charts graphs or maps
Values Available color palettes for selected visualization
Setting type X Axis
Applicable to Bar
Description Column to be mapped on the X-Axis
Values Selected non-aggregate columns
Setting type Columns for Metadata
Applicable to Bar
Description Columns to be mapped on the Y-Axis
Values Selected aggregate columns
Setting type Orientation
Applicable to Bar
Description Orientation of the chart
Values _Horizontal or vertica_l
Setting type Chart Type
Applicable to Bar
Description Type of plot to be used for every column on the Y-Axis
Values none, line, bar, dot, area, stack
Setting type Chart Type color
Applicable to Bar
Description Color to be used to plot the column on the Y-axis
Values Depending on the color palette selected.
Setting type Axis Alignment
Applicable to Bar
Description Show the Y-Axis at the left or right of the chart for each column on Y-Axis
Values Left Axis, Right Axis
Setting type Range
Applicable to Bar, Metric
Description Allows use of different colors for different range of values. You will be requested to key in the ranges and select colors for each range on enabling this option
Values Yes,No
Setting type Y-Axis Range
Applicable to Bar
Description This can be used to limit the range of the Y-Axis. The chart will be cropped to fit into the defined range.
Values Yes/No. Min and max of range to be entered if Yes is selected.
Setting type Reference Line
Applicable to Bar
Description Include one or more reference lines for specific values, define a label to be displayed along the line, select the color and type of the line
Values Yes/No with the option to choose value, label, color and line type when Yes is selected.
Setting type Axis Label Style
Applicable to Bar
Description Allows users to control the font size and length for labels used on the X-Axis and Y-Axis
Values Yes/No
Setting type Axis Label - Font Size (px)
Applicable to Bar
Description Size in pixels for labels displayed on the axis of the bar chart
Values Numeric
Setting type Axis Label - Font Length (characters)
Applicable to Bar
Description Number of characters to be shown in axis labels
Values Numeric
Setting type Legend Style
Applicable to Bar
Description Allow definition of style for legends which includes font size and scrolling feature.
Values Yes/No
Setting type Legend Style - Font Size
Applicable to Bar
Description Available when Legend Style is set to Yes. Can be used to control the font size of legends.
Values Numeric. Default value=14
Setting type Legend Style - Allow legend scroll
Applicable to Bar
Description Available when Legend Style is set to Yes. Includes a horizontal scrolling option to view all legends when there are too many
Values Yes/No
Setting type Hide Y Axis
Applicable to Bar
Description Show or hide the labels on the Y-Axis
Values Yes/No
Setting type Hide X Axis
Applicable to Bar
Description Show or hide the labels on the X-Axis
Values _Yes/No
Setting type Data Labels
Applicable to Bar
Description Show or hide the exact column value on the chart as a label
Values Yes/No
Setting type Data Label Style
Applicable to Bar
Description Allows specification of data label style for the bars. Available only if Data Labels = Yes
Values Yes/No
Setting type Data Label Style - Rotate Label
Applicable to Bar
Description Orientation of the label to be specified in degrees. Use 0 for horizontal labels and 90 for vertical labels
Values 0-90
Setting type Data Label Style - Label position
Applicable to Bar
Description Position of the label in percentage x and y coordinates relative to the bar on which it is displayed. E.g., x=50 implies it will be displayed at the center of the bar width.
Values 0-100
Setting type Hide Range Legend
Applicable to Bar
Description Show or hide the range legend which is shown when ranges are defined using the Range setting.
Values Yes/No
Setting type Hide Legend
Applicable to Bar
Description Show or hide the legend for colors used for every column on the Y-Axis
Values Yes/No
Setting type Stack All
Applicable to Bar
Description Stack multiple dimensions in the same bar using different colors
Values Yes/No
Setting type Dimension
Applicable to Pie
Description Column to be used for mapping the pie chart
Values Selected non-aggregate columns
Setting type Measure
Applicable to Pie
Description Column to be used to calculate the portions of the pie
Values Selected aggregate columns
Setting type Type
Applicable to Pie
Description Type of Pie Chart
Values Pie, Doughnut
Setting type Show Label
Applicable to Pie
Description Show/Hide the data mapped in dimension column as labels near each slice of the pie.
Values Yes/No
Setting type Map Type
Applicable to Map, gMap
Description Type of map to be generated
Values Geo Map, Scatter, Network, Path
Setting type Lat-Long Mode
Applicable to Map, gMap
Description To be used if data contains location coordinates instead of names of places.
Values Yes/No
Setting type State, Source State, Target State
Applicable to Map
Description Column to be used for location state or country if Lat-Long mode is set to off
Values List of available non-aggregate columns
Setting type City, Source City, Target City
Applicable to Map
Description Column to be used for location city if Lat-Long mode is set to off
Values List of available non-aggregate columns
Setting type Country, Source Country, Target Country
Applicable to gMap
Description Column to be used for location country if Lat-Long mode is set to off
Values List of available non-aggregate columns
Setting type Latitude, Source Latitude, Target Latitude
Applicable to Map, gMap
Description Column to be used to map the latitude part of the location coordinates if Lat-Long mode is set to on.
Values List of available non-aggregate columns
Setting type Longitude, Source Longitude, Target Longitude
Applicable to Map, gMap
Description Column to be used to map the longitude part of the location coordinates if Lat-Long mode is set to on.
Values List of available non-aggregate columns
Setting type Measure, Primary Measure, Secondary Measure
Applicable to Map, gMap
Description Column to be used for mapping data values on the map
Values List of available aggregate columns
Setting type Segment Column
Applicable to gmap (Path Map)
Description Column to be used for segmentation
Values List of available columns
Setting type Map Theme
Applicable to gMap
Description Apply one of the available themes
Values Standard, Snow, Retro, Gray, Blue
Setting type HTML
Applicable to Metric
Description Can be used to specify formatting for the columns displayed in the metric.
Values Valid HTML for each of the selected columns.
Setting type Columns
Applicable to Metric
Description Number of columns to split the sheet screen into when displaying the selected columns as a metric.
Values Range 1 to number of selected columns
Setting type Label Font
Applicable to Metric
Description Font Size of Label
Values Range 1 to 100
Setting type Text Font
Applicable to Metric
Description Font Size of Text/Data
Values Range 1 to 100
Setting type Spacing
Applicable to Metric
Description Spacing between Label and Text
Values Range 1 to 100
Setting type Border
Applicable to Metric
Description Show/Hide border around each metric
Values Yes/No
Setting type Background
Applicable to Metric
Description Use transparent background or default background
Values Yes/No
Setting type Reverse
Applicable to Metric
Description Show label under the value when reversed
Values Yes/No
Setting type Fill Space
Applicable to Metric
Description If yes, then empty columns after distributing data points available amongst number of columns selected will be merged in the last row
Values Yes/No
Setting type Hide Label
Applicable to Metric
Description Show/hide labels for all metrics
Values Yes/No
Setting type No Data Label
Applicable to Metric
Description Text to be shown when there is no data
Values Free Text
Setting type Columns to hide
Applicable to Metric
Description Option to hide selected columns
Values All selected columns
Setting type Node Type
Applicable to Graph
Description Node type to be shown on the graph
Values Box, Dot
Setting type Source
Applicable to Graph
Description Column to be mapped as source nodes
Values List of available columns
Setting type Target
Applicable to Graph
Description Column to be mapped as target nodes
Values List of available columns
Setting type Value
Applicable to Graph
Description Column to be used to display value on graph connectors
Values List of available columns
Setting type Quick View
Applicable to Grid Chart
Description Can be used to view the data in a simple grid without pivot
Values Yes/No
Setting type Columns
Applicable to Grid Chart
Description Select fields to be displayed in columns
Values List of available columns
Setting type Rows
Applicable to Grid Chart
Description Select fields to be displayed in rows
Values List of available columns
Setting type X-Axis/Y-Axis
Applicable to Grid Chart
Description Select values to be displayed as data in the pivot chart
Values List of available columns
Setting type Colors
Applicable to Grid Chart
Description Select the column based on which color variation would be applied to the data
Values List of available columns
Setting type Fill Container
Applicable to Grid Chart
Description Select if chart should fill the width of the container
Values Yes/No
Setting type Measures Scrollable
Applicable to Grid Chart
Description Select if horizontal scroll should be available
Values Yes/No
Setting type Limit Column Width
Applicable to Grid Chart
Description Select if column width for all columns should be limited/optimized
Values Yes/No
Setting type Show Serial Number
Applicable to Grid Chart
Description Select if serial number should be shown for rows
Values Yes/No
Setting type Merge Cells
Applicable to Grid Chart
Description Select if rows or columns with same values should be merged
Values Yes/No
Setting type Merge Cells
Applicable to Grid Chart
Description Select if rows or columns with same values should be merged
Values Yes/No
Setting type Size Measure
Applicable to Treemap
Description Select which non-aggregate column should be mapped to the area of the rectangle
Values List of available non-aggregate columns
Setting type Color Measure
Applicable to Treemap
Description Select which non-aggregate column should be mapped to the color of the rectangle
Values List of available non-aggregate columns
Setting type Labels
Applicable to Treemap
Description Select which column data should be displayed inside a rectangle
Values List of available columns
Setting type Tooltips
Applicable to Treemap
Description Select which column data should be displayed in a tooltip
Values List of available columns
Setting type Show Labels Attribute Name
Applicable to Treemap
Description Select if column names should be included when displaying labels
Values Yes/No
Setting type Show Tooltips Attribute Name
Applicable to Treemap
Description Select if column names should be included when displaying tooltips
Values Yes/No
Setting type Show Color indicator
Applicable to Treemap
Description Select if the color indicator should be displayed
Values Yes/No
Setting type Show Breadcrumb
Applicable to Treemap
Description Select if the breadcrumb should be displayed
Values Yes/No

Visualization features

Following are the additional features available when creating and managing sheets using the sheet editor.

Number of Rows

The number of data rows to be retrieved for any visualization is set to 100 by default. This can however be changed by altering the number in the input box at the top right of the visualization pane.

View SQL

The SQL generated corresponding to the original data source can be viewed by clicking on the “View SQL” button. This displays the SQL based on the columns selected, filters and order applied and the operations defined in the bippLang data model.

Manage Columns

In case there are many tables and columns available in the data model, you can search for a specific column by keying the name in the search box.

Listed columns for each table may be sorted, alphabetically or by data type. By default, columns are not sorted and are shown in the order in which they are defined in the data model.

Selected columns may be removed from the visualization by unselecting a specific column using the column menu or clicking the “Unselect All” button on the top-left of the screen.

Columns displayed in the table may be repositioned by dragging the column and dropping it into a new position in the table view. Alternately this can be accomplished by dragging and dropping the column name in the data settings pane on the right.

Editor Functions

The sheet editor provides Undo and Redo buttons to undo or redo previous actions.

New sheets may be added corresponding to the same version of the data model by clicking on the “+” button at the bottom of the screen. This will add a new blank sheet tab in the sheets editor.