Datasets and Sheets
The sheets feature in Bipp provides the ability to view and visualize data corresponding to any data model created using Bling. It offers an opportunity for data analysts to verify the data model they created for discrepancies. Sheets also serve as visualization units that can be directly incorporated on Ding dashboards. Thus, sheets form an important link between the data model and the dashboard.
Sheets for New Datasets
Sheets corresponding to newly deployed data models or datasets can be created from the Datasets by clicking on the “Datasets” link on the main menu.
A blank sheet creation screen will be displayed with tables from the selected dataset. You can now begin your data visualization journey with Bipp.
In order to start visualizing data on sheets, 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.
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 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.
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.
Filters: Bipp provides 3 methods to filter data on sheets. Let us look into these one by one.
Params: Parameter columns defined in the Bling 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.
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.
Following is the query generated with the filters
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
DESCbut may be changed to
ASCunder the “Order By” tab on the right hand side of the screen.
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.
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.
After Pivot on Age
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.
- 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
- 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
- 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.
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
Geo 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.
Scatter Map: It can be used to map data containing locations with associated measures in a scatter type of visualization.
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.
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.
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.
- 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, map and custom visualizations are not enabled by default for all tenants. These need to be enabled on Tenant Settings screen
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||Applicable to||Description||Values|
|Auto Format||All Types||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.||Yes/No|
|Nav Bar||Table||Show or hide the pagination or navigation bar displayed on top of the data grid||Yes/No|
|Index Column||Table||Show or hide the index column that appears as the first column in the grid to display the row index.||Yes/No|
|Grand Total||Table, Bar||Show or hide a row for grand totals at the end of the grid or chart||Yes/No|
|Transpose Column||Table||It can be used to provide a pivot view for the data where the aggregate columns are transposed to rows.||Yes/No|
|Show Table Name in Header||Table||It can be used to show the table name along with the column names in the column header.||Yes/No|
|Color Palette||Bar, Pie, Map, gMap||Allows selection of colors to be used for displaying charts graphs or maps||Available color palettes for selected visualization|
|X Axis||Bar||Column to be mapped on the X-Axis||Selected non-aggregate columns|
|Columns for Metadata||Bar||Columns to be mapped on the Y-Axis||Selected aggregate columns|
|Orientation||Bar||Orientation of the chart||Horizontal or vertical|
|Chart Type||Bar||Type of plot to be used for every column on the Y-Axis||none, line, bar, dot, area, stack|
|Chart Type color||Bar||Color to be used to plot the column on the Y-axis||Depending on the color palette selected.|
|Axis Alignment||Bar||Show the Y-Axis at the left or right of the chart for each column on Y-Axis||Left Axis, Right Axis|
|Range||Bar, Metric||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||Yes,No|
|Y-Axis Range||Bar||This can be used to limit the range of the Y-Axis. The chart will be cropped to fit into the defined range.||Yes/No. Min and max of range to be entered if Yes is selected.|
|Reference Line||Bar||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||Yes/No with the option to choose value, label, color and line type when Yes is selected.|
|Hide Y Axis||Bar||Show or hide the labels on the Y-Axis||Yes/No|
|Hide X Axis||Bar||Show or hide the labels on the X-Axis||Yes/No|
|Data Labels||Bar||Show or hide the exact column value on the chart as a label||Yes/No|
|Hide Range Legend||Bar||Show or hide the range legend which is shown when ranges are defined using the Range setting.||Yes/No|
|Hide Legend||Bar||Show or hide the legend for colors used for every column on the Y-Axis||Yes/No|
|Stack All||Bar||Stack multiple dimensions in the same bar using different colors||Yes/No|
|Dimension||Pie||Column to be used for mapping the pie chart||Selected non-aggregate columns|
|Measure||Pie||Column to be used to calculate the portions of the pie||Selected aggregate columns|
|Type||Pie||Type of Pie Chart||Pie, Doughnut|
|Show Label||Pie||Show/Hide the data mapped in dimension column as labels near each slice of the pie.||Yes/No|
|Map Type||Map, gMap||Type of map to be generated||Geo Map, Scatter, Network, Path|
|Lat-Long Mode||Map, gMap||To be used if data contains location coordinates instead of names of places.||Yes/No|
|State, Source State, Target State||Map||Column to be used for location state or country if Lat-Long mode is set to off||List of available non-aggregate columns|
|City, Source City, Target City||Map||Column to be used for location city if Lat-Long mode is set to off||List of available non-aggregate columns|
|Country, Source Country, Target Country||gMap||Column to be used for location country if Lat-Long mode is set to off||List of available non-aggregate columns|
|Latitude, Source Latitude, Target Latitude||Map, gMap||Column to be used to map the latitude part of the location coordinates if Lat-Long mode is set to on.||List of available non-aggregate columns|
|Longitude, Source Longitude, Target Longitude||Map, gMap||Column to be used to map the longitude part of the location coordinates if Lat-Long mode is set to on.||List of available non-aggregate columns|
|Measure, Primary Measure, Secondary Measure||Map, gMap||Column to be used for mapping data values on the map||List of available aggregate columns|
|Segment Column||gmap (Path Map)||Column to be used for segmentation||List of available columns|
|Map Theme||gMap||Apply one of the available themes||Standard, Snow, Retro, Gray, Blue|
|HTML||Metric||Can be used to specify formatting for the columns displayed in the metric.||Valid HTML for each of the selected columns.|
|Columns||Metric||Number of columns to split the sheet screen into when displaying the selected columns as a metric.||Range 1 to number of selected columns|
|Label Font||Metric||Font Size of Label||Range 1 to 100|
|Text Font||Metric||Font Size of Text/Data||Range 1 to 100|
|Spacing||Metric||Spacing between Label and Text||Range 1 to 100|
|Border||Metric||Show/Hide border around each metric||Yes/No|
|Background||Metric||Use transparent background or default background||Yes/No|
|Reverse||Metric||Show label under the value when reversed||Yes/No|
|Fill Space||Metric||If yes, then empty columns after distributing data points available amongst number of columns selected will be merged in the last row||Yes/No|
|Hide Label||Metric||Show/hide labels for all metrics||Yes/No|
|No Data Label||Metric||Text to be shown when there is no data||Free Text|
|Columns to hide||Metric||Option to hide selected columns||All selected columns|
|Node Type||Graph||Node type to be shown on the graph||Box, Dot|
|Source||Graph||Column to be mapped as source nodes||List of available columns|
|Target||Graph||Column to be mapped as target nodes||List of available columns|
|Value||Graph||Column to be used to display value on graph connectors||List of available columns|
|Quick View||Grid Chart||Can be used to view the data in a simple grid without pivot||Yes/No|
|Columns||Grid Chart||Select fields to be displayed in columns||List of available columns|
|Rows||Grid Chart||Select fields to be displayed in rows||List of available columns|
|X-Axis/Y-Axis||Grid Chart||Select values to be displayed as data in the pivot chart||List of available columns|
|Colors||Grid Chart||Select the column based on which color variation would be applied to the data||List of available columns|
|Fill Container||Grid Chart||Select if chart should fill the width of the container||Yes/No|
|Measures Scrollable||Grid Chart||Select if horizontal scroll should be available||Yes/No|
|Limit Column Width||Grid Chart||Select if column width for all columns should be limited/optimized||Yes/No|
|Show Serial Number||Grid Chart||Select if serial number should be shown for rows||Yes/No|
|Merge Cells||Grid Chart||Select if rows or columns with same values should be merged||Yes/No|
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 Bling data model.
Save: The Sheet may be saved and given a name. A unique sheet Id will be generated when the sheet is saved.
Download: Sheet data may be downloaded using the “Export to PDF” option. This is possible even without saving the sheet. The current visualization visible on the screen is downloaded as PDF.
Search: 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.
Sort: 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.
Undo/Redo Actions: The sheet editor provides Undo and Redo buttons to undo or redo previous actions.
Unselect Columns: 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.
Drag & Drop Columns: 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.
Add New Sheets: 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.
Download Data: Data corresponding to the sheet may be downloaded by clicking on the “Download Data” menu option from the sheet menu. This will provide options to select the number of rows to be downloaded, file format for download and an optional filename.
Tag Sheets: You can tag sheets so that similar sheets can be found easily in the future. This can be achieved by using the “Add Tag” feature from the sheet menu. You can then search for all similarly tagged sheets by keying in the tag text in the search box available inside the application header.
Threads: You can capture a screenshot of the sheet and annotate it or start a discussion about it with other members of your group/team by clicking on the “Create Thread” option in the sheets menu. An email will be sent to all included members on the creation of the thread. You can also view all threads related to the sheet by clicking on the “View Threads” option.
Scheduled Reports: You can schedule delivery of reports corresponding to the sheet by using the “Scheduled Reports” option from the sheets menu. You can configure the title, frequency, timezone, recipients and delivery format for the report on the set schedule screen as shown below.
Alerts: You can create alerts corresponding to the data on the sheet by using the “Alerts” option from the sheet menu. An alert may be scheduled whenever there is a specific change in data that is displayed on the sheet. The condition for the alert may be defined in natural language using the (NLP) section of the alert. The condition may be validated by clicking “Enter/Return” after keying in the condition. Any errors or warnings will be displayed at the bottom of the NLP section.
A condition may be defined based on the row count in the resultset. A condition may also be defined by using a combination of aggregate functions, columns, comparison operators and user defined values in the condition section of the alert. All the conditions defined in the different sections should be true for the alert to be generated.
The alert may be configured by specifying the title, frequency, schedule and recipients. Alerts may also be configured to include a file attachment with data from the sheet.
Sheet Version Information: You can view the version of the dataset being referred to by the sheet. This is available under “Sheet Version Info” option in the sheet menu.
Sheet Settings: Some customization is available at sheet level for caching results as well as whether queries should fetch count of the rows. This is available under “Sheet Settings” option in the sheet menu. Following dialog box is shown when you access this option.
Access and Manage existing Sheets
Existing sheets can be accessed and managed from the Sheets listing screen. This screen is shown when the “Sheets” option is selected from the Bipp main menu.
You can search for sheets corresponding to a specific project, dataset, or those that have been shared with a specific user. Free text search based on the sheet name is also available. All sheets matching the filter criteria are listed on the screen. Following operations are available for every sheet listed on this screen.
View/Edit: A sheet may be viewed or edited by clicking on the Sheet name or the “View” option corresponding to the sheet.
Permissions: The sheet may be shared with other users by clicking on the “Permissions” option in the sheet menu. Email Id of the user with whom the sheet needs to be shared should be entered in the Permissions dialog box. Alternatively a group name may also be entered for sharing with all members of a specific group. Permissions for multiple sheets may be edited simultaneously by selecting the sheets and clicking on the “Update Permissions” button above the sheets list.
Delete: The sheet may be deleted by clicking on the “Delete” option in the sheet menu. You can also delete multiple sheets by selecting them and clicking on the “Delete” button above the sheets list. Sheets will be deleted upon confirmation.
Pin/Unpin: The sheet may be pinned for easy access. Links to all the pinned sheets and dashboards will be available on the home page under the “Pinned” tab so that they may be accessed directly. Pinned sheets may be unpinned later
Export: You can export one or many sheets to tar format which will be downloaded to your computer. For this select the sheets and click on the “Export” button above the sheets list.
You can import existing sheets created by you or other users by uploading the exported tar file to Bipp on the Sheets screen. For this click on the Import notes at the top right of the screen. The Import Notes dialog box will be shown as follows.
Select the tar file to be imported. Check the Replace Resources option if you want to overwrite any existing resources like datasets or projects referred by the imported sheets. Click on the Import button to import all the sheets contained in the tar file.
Sheets - Data model Version
New sheets always refer to the latest version of the data model. All sheets grouped together in the sheet editor refer to the same version of the data model. Subsequent revisions of the data model need to be explicitly applied to existing sheets by using the “Update Sheets Version” functionality on the Sheets listing screen. This feature may be used to apply a specific version of the data model to existing sheets.