Using Datasets and Sheets
The sheets feature in bipp provides the ability to view and visualize data corresponding to any data model created using bippLang. 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 bippDash dashboards. Thus, sheets form an important link between the data model and the dashboard.
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.
Following are the features available when creating and managing sheets using the sheet editor.
The core function of sheets is to visualize data. This is made possible by different types of visualizations that can be customized to view data in an easily consumable manner. Details of available visualizations types and settings can be found here.
The Sheet may be saved and given a name. A unique sheet Id will be generated when the sheet is saved.
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.
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.
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.
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.
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.
You can create alerts corresponding to the data on the sheet by using the “Alerts” option from the sheet menu. An alert may be triggered whenever there is a specific change in data that is displayed on the sheet. bipp provides two methods to define and create alerts.
a. You can use the condition builder that allows you to define the alert condition by selecting, table/columns, aggregation functions, comparison operators and user defined values as shown in the image below. A condition may be defined based on the row count in the resultset.
b. The condition for the alert may be defined in natural language using the (NLP) section of the Alerts screen. NLP conditions allow you to specify a condition in a sentence which will be converted to a logical statement by the platform. NLP conditions can be written only for Measures and not for Dimensions. 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.
Standard and Basic mathematical functions like AVG, MIN, MAX, SUM, > , < , >=, <= may be used in the condition. The following image shows an example of an alert defined as a NLP statement and the logical statement generated by bipp.
Following are some additional examples of NLP statements that may be used with their corresponding logical interpretations. (I and P are the two measure columns used by the examples)
|NLP Statement||Logical statement|
|I is more than P||I > P|
|I more than P||I > P|
|I less than P||I < P|
|I is less than P||I < P|
|I is larger than P by 10||(I - P) > 10|
|I is less than P by 20%||(P - I) > (20 * P / 100.0)|
|average I is more than P||AVG(I) > P|
|minimum I is less than P||MIN(I) < P|
|minimum of P-I is more than 30||MIN(P - I) > 30|
|I is within 30% of P||(0.70 * P) <= I AND I <= (1.30 * P)|
|sum of P is greater than average of I||SUM(P) > AVG(I)|
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.
Multiple alerts may be defined corresponding to the data on a sheet. The Alerts screen may be used to manage these alerts simultaneously. It lists down all the available alerts for the sheet. Selected alerts may be deleted, enabled or disabled from this screen by clicking on the corresponding buttons as shown below.
You can view the version of the dataset being referred to by the sheet. This is available under the “Sheet Version Info” option in the sheet menu.
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 the “Sheet Settings” option in the sheet menu. Following dialog box is shown when you access this option.
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.
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.
IN THIS PAGE