Working with Notebooks
  • 18 May 2022
  • 16 Minutes to read
  • Contributors
  • Dark
    Light
  • PDF

Working with Notebooks

  • Dark
    Light
  • PDF

This article details all the functionality that is available to you, as data designers, to work efficiently with Notebooks.

Getting Started

To start working in Notebooks:

  1. Go to Admin > Feature Management, and toggle on the Notebooks button. All users, including Viewers, will see the tab, but the content in the tab differs based on user type.
    admin.png

  2. To access Notebooks, click the Notebooks tab. It may take a few minutes for the Notebooks service to load when enabled for the first time. The browser automatically refreshes once the service is ready.

Opening a Notebook

When you open Notebooks, a list of your existing Notebooks are displayed. The list includes the name of the Notebook, the Notebook owner, and the date the Notebook was last modified.
You can search for Notebooks by their name or by their owner's name.
Filter the list by:

  • Recently accessed Notebooks
  • All Notebooks
  • Notebooks that you created
    allnotebooks.png

To open a Notebook:

  1. Select a Notebook from the list.
    Or—
  2. Select a Notebook from the Notebook Browser.

Creating a Notebook

When you create a Notebook, it displays in view mode and is not yet editable.

Watch this video about creating notebooks:

To create a Notebook:

  1. To access Notebooks, click the Notebooks tab.
  2. In Notebooks, either:
    • At the top of the Notebook Browser, click + and select Add New Notebook.
      addnotebook.png

Or —
* Click + New Notebook.
newnotebook.png

  1. Enter a name for your Notebook and click Save.
  2. Select a Live connector and enter the connection details. For more information about connecting to your data source, see the following:
  3. Click Next.

Sharing a Notebook

You can share a Notebook with anyone who has a Sisense account in your deployment. Viewers and Designers only have view access to all notebooks. Data Designers can be given view or design access to a notebook that is shared with them. Admins and Data Admins can see all notebooks even if they are not shared with them explicitly.

Watch this video about sharing notebooks:

To share a Notebook:

  1. Select the notebook you want to share, click the action button and select Share.
    share.png

  2. Add users or groups to the Notebook share list:
    useraccess.png

    A. Search for users or groups to share the Notebook with.
    B. Select the permissions for this user.
    C. User can edit the SQL/code in the Notebook:

    • Viewers and Designers will have the SQL/code toggle disabled by default, and the Can View/Can Design dropdown greyed out
    • Admins and Data Admins can see the SQL/Code toggle even if it is toggled off for their user.
      D. Remove the user or group from the shared list.
  3. Click Save.

Permissions

Actions within the notebooks are dependent on user permissions:

  • All user types can access the Notebook tab
  • Only Admins, Data Admins, and Data Designers can create new Notebooks
  • Only Owners, Data Admins, and Admins can change the Notebook owner or delete a notebook

There are two factors that determine what you can do in a specific Notebook: Your Sisense role and the Notebook share permissions:

  • Sisense Admins and Data Admins can perform all actions on a Notebook, regardless of the Notebook share permissions.
  • Data Designers with Owner permissions can perform all actions on the Notebook.
  • Data Designers with Edit permissions can perform all actions on the Notebook, but cannot change the Notebook owner or delete the Notebook.
  • All user types with View permissions can only view the Notebook and cannot make any changes to it.

Organizing Notebooks into Folders

You can create folders for organizing notebooks:

  • Folders can be nested up to 3 times.
  • You can only add notebooks that you own into the folders that you own.
  • Shared folders have a shared icon on the folder icon.
  • A folder cannot be shared but a folder will have the shared icon if a notebook within that folder has been shared with the user.
  • For Admins and Data Admins, the shared icon is on folders and notebooks that they do not own themselves. Since Admins and Data Admins can see all folders and all notebooks, the shared icon indicates which items they don’t own and are owned by others even if those have not been shared with them explicitly.

To create a folder:

  1. At the top of the Notebook Browser, click + and select Add New Folder.
    addnewfolder.png
    Or
    Click the action menu and select New Folder.

  2. Enter a name for the folder. There is a limit of 100 characters for the name.

  3. To add nested folders up to 3 levels, repeat the action in each folder you create.

Adding a Code Cell

To gain further insights from the analysis done using SQL, you can use Python or R to do more complicated manipulations. The outputs of the SQL block can be loaded into the Code blocks using the SisenseHelper.load_dataframe() function with the name of the SQL block as the input to continue the analysis using the preferred coding language.

There are 2 Sisense Helper functions that help load in the data from another SQL cell and save the final output as a dataframe.

  • Click + Code. The code block appears and already shows the Sisense Helpers for reference. The load_dataframe() function already has the most recent SQL block name as the input. In the screenshot below, that is “data_year”
  • To create a Sisense chart using the output of the code, the SisenseHelper.save_dataframe() must be used with the desired dataframe used as the input.
  • SQL block outputs can be loaded into and used in Code blocks but Code blocks cannot be loaded into other Code blocks as dataframes at this time.
    addcodecell.png

Watch this video on how to add Python code:

Find and Replace

You can perform a “find and replace” within SQL and Code blocks.

Note:

"Find and replace" only finds results in SQL and Code fields, not in text fields.

  1. From the menu icon in the top-right corner, select Find and Replace.
    findandreplace.png

  2. In the Find and Replace dialog box, enter the Find term and, if required, the Replace term.

The search results are highlighted in the notebook and you can click < and > to navigate through them. A red box around the Find field indicates that no results were found. You can also drag the Find and Replace dialog box around the screen to position it, as necessary.

Download Output as CSV

When you have successfully run a query, you can download the results in CSV format. The CSV file contains the entire results set up to 500MB.

  • To download SQL and Code Cell results as a CSV file, in the top-right of the SQL and Code Cell, click the download icon.
    download.png

Editing a Notebook

If you’ve opened an existing Notebook, you’ll see the queries that were previously entered, and the results of those queries, if the queries were previously run.
Note:
By default, the View SQL toggle is Off, meaning that unless a query has been run and visualizations created, no queries or blocks are displayed. To view the visualizations, toggle the button On.

Note:

The data on which the query is based doesn’t automatically update. Run the query to see the latest results.

  1. Open an existing Notebook and click Edit Notebook.
    The following image represents a sample Notebooks cell.
    editnotebook.png

  2. Enter your query.

    1. As you type, Sisense Notebooks provides autocomplete options, based on the databases, tables, and column names in the Schema Browser
    2. You can expand and contract items in the Schema Browser to view or hide databases and tables
    3. The cell automatically expands and wraps your text as you enter long queries so that you don’t have to scroll
  3. Set the Limit Preview field to limit the number of rows that show in the results. The default is 5,000 rows.

  4. To view the results of your SQL query:

    • Click to run all SQL query cells in your Notebook
    • Or —
    • Click Run SQL to run a specific cell in your Notebook (also see Referencing Other Cells, below). A preview of the query results displays below the query.
  5. Click Save to save your Notebook.

Keyboard Shortcuts

You can use keyboard shortcuts to make working with SQL cells and text cells in Sisense Notebooks more efficient.

  • For a list of all of the available keyboard shortcuts, in a Notebook you are editing, click the menu icon in the top-right corner and select Keyboard Shortcuts.
    keyboardshortcuts.png

Adding Charts

The SQL and code query results can be vizualised as charts for business users. Each cell result can be plotted to a chart.

Watch this video about adding charts:

To create a chart:

  1. In the query results, click Chart in the specific cell. The chart view appears.
    chart.png
  2. Select the data and chart type you want to visualize.
    Available chart types include:
    • Indicator
    • Pie Chart
    • Column Chart
    • Bar Chart
    • Line Chart
    • Area Chart
    • Table
  3. Configure the axes and series (Break By)
  4. Apply filters.
  5. To save the chart, click Create. The chart appears as a cell in your Notebook.
    applyfilters.png

Manipulating Charts

Arranging Charts

  • In Edit mode, drag and drop charts in your Notebook to arrange them side-by-side, or on a row of their own. In the following image, a chart is being dragged from its own row (grey) and positioned next to another chart (purple placeholder).
Note:

Only text and chart blocks can be dragged to be rearranged or resized. SQL and Code blocks cannot be dragged to rearrange or resize them. Use the up and down arrows to reorder the code blocks.

rearrangerows.png

codeblocks.png

Resizing Charts

  • In Edit mode, resize charts’ height and width by dragging the chart border. When resizing, the chart turns blue.
    resizingcharts.png

Coloring Bar Charts

If you create a bar chart, you can apply colors to it.

  1. If you don't already have a bar chart in your Notebook, create one.
  2. Above the bar chart, click (FIGURE) to edit it.
  3. Add a "break by".
  4. Click a color next to a value to open the color palette to select a new color or enter a custom color value.
  5. Click Apply.
    colorpalette.png

Filtering Charts

Filters are added to charts from the Notebook editor by data designers. All other users with view access to the notebook can interact with the filters from the view-only mode 

Filtering options include:

Filters only apply to Chart blocks and not the Preview tables that are shown in the SQL and Code blocks. If there are no charts on the Notebooks, the filters do not apply to anything. The filters that can be added are automatically populated and shown in a drop-down to the users based on the columns that are outputted from the SQL the user has written and run. After choosing the filter based on the columns, the values are auto-populated based on everything that is available in that column. In view-mode, users do not see the Filter panel if the Notebook editor has not added any filters to the notebook.


Note:

Some features found in the Dashboard filtering, such as customizing queries with json/code, selecting single-select mode, and saving a filter as starred are not supported in Notebooks.


Setting Date Filters

There are various options for setting a date filter for your dashboards. Click on each of the options below to learn how to set date filters.

Set a filter to include specific time period/s

Use this filter to select a single or multiple specific periods from a closed list. For example: "Year = 2020", "Month = February 2021", "Quarter = Q3 2019 + Q4 2019", etc.

To set a filter by including specific time periods:

  1. Select the period granularity (Year, Month, etc.), and then select the specific periods from the list (e.g. “January 2021, Q4 2020, etc.):
    • If Allow multiselect for lists is enabled, you can select all options in the second list or clear all selections. Otherwise, select a single option.
    • Dates are listed chronological and descending. After selection, the selected options move to the top of the list
    • Dates available for selection are dates that belong to the dataset. The list is updated dynamically to include new dates added to the dataset
    • Weeks are based on the system's "First day of the week" Admin setting. For example, if first day of the week is set to "Monday", then selecting "This week" from the filter will return all data from Monday of the current week (e.g. week 32) till today
    • If the Admin setting for Fiscal year is turned on, then Fiscal year is supported and relevant periods in the list display the fiscal year tag (Years, Quarters, Weeks):
    • Results are retrieved based on the fiscal year: for example, if my fiscal year starts in April, and I select "Q1 2020", then it should retrieve results from April 1 2020 - June 30 2020.
    • You cannot exclude any dates in this filter type. Use the Is not option for this. See Setting a Filter to Exclude exact time periods.
Set a dynamic time frame (relative to today)

Use this filter to select ANY possible number of defined time periods to get data and answers to any timeframe that's relevant for your needs. For example, I want to be able to select "Last 13 months", "Last 5 days", "Next 3 months" or any other combination. The time period can be in the past, or in the future.

Add to this filter the option to include the current period, if required. For example: "Past 7 days including/excluding TODAY", "Past 2 weeks including/excluding THIS WEEK".)

  1. In the Filter window, select the third radio button, and from the dropdown list, make your selection as required.
  2. Select the amount and time period you want to include. If you select This, then you can only select the time period.
  3. Select Including current to include the current period:
    • This includes the current period (This week/month etc.)
    • Default setting is OFF
Set a date range ("From - To")

You can filter by 3 types of date ranges:

  1. If you have specific start and end dates (for example: "Show all flights between March 13 2020 to March 17 2020") ? selecting a start date and an end date from the calendar
  2. If you know the start date, but don’t want to limit the end date (for example, "Show all contracts signed as of Jan 1st 2020") ? select a start ("From") date from the calendar, and select the "Latest date" option as the end date.
  3. When you know the end date, but don't want to limit the start date (for example, "Show all contracts that were signed anytime up to Dec 31 2020") ? select the “Earliest date” option as the start date ("From"), and select the desired end date from the calendar.
    • Use the Today option as a shortcut to set Today’s date. However, note that Today is not a dynamic setting (it enters today’s date, but then that date remains selected, and does not change as days go by.
    • Dates available for selection are dates that belong to the dataset. The list is updated dynamically to include new dates added to the dataset. Dates for which there is no available data appear grayed out.
Set a filter to exclude exact time periods

Use this filter to exclude a single or multiple specific period/s. For example, "Show me all sales except for December 2021".

  • In the Filter window, select the fifth radio button, select the condition Is Not and then select the period/s from the dropdown lists.
Set a date filter that is dependent on a measure value (Top/Bottom ranking)

Use this filter to set a date filter that is dependent on a value of a certain measure, to ask questions such as "Show the Top/Bottom 10 months in terms of Total Cost".

  1. On the fifth radio button in the filter window, select the condition (Top or Bottom).
  2. Select the number and type of time periods (e.g. 10 months).
  3. In the Ranked by field, select the measure to be used for ranking (e.g. Total Sales, Number of students, etc.).
Set a dynamic time frame relative to any date

Use this filter to set a time frame that is relative to a date that may not be Today, but rather some other date, or a moving date, such as our latest date, to ask questions such as "show all sales for the last 3 days in which we had sales".

  1. In the Filter window, select the fifth radio button and select the condition Is Within.
  2. Select the time frame; period, before/after, date.
  3. If you do not want to limit the start date or the end date, select Earliest date or Latest date from the calendar.
Set a filter to include or exclude a recurring hour of day

Use this filter to select an hour or part of an hour, not on any specific date, to answer questions such as "How many calls do we get between 2-3 am?" or "How many customers do we have during the morning hours vs. afternoon hours?”.


Setting Text Filters

Text filters let you filter according to text matching. Text filters are case insensitive for all data sources by default.

There are various options for setting a text filter for your dashboards. Click on each of the options below to learn how to set text filters.

Set a filter to include specific value/s

Use this filter to select a specific known value or multiple values to view, for example, "Condition = New + Refurbished".

  • Searching for a value:
    • If Allow multiselect for lists is enabled, you can select all options in the second list or clear all selections. Otherwise, select a single option.
  • When the list is filtered by search, Select all applies to the filtered list - only items that appear will be selected.
  • Search is not case-sensitive.
  • You cannot exclude any values from this list. Use the Is not option for this.
Filter by a text condition

Use this filter to find values by certain textual characteristics. For example, "All products starting with ''iphone”, ''All products containing "chair".

Set a filter that is dependent on a measure value (Top/Bottom ranking)

Use this to filter a textual field, depending on the value of a certain measure, to answer questions such as "Show the Top/Bottom 10 countries in terms of Total sales".

  1. On the third radio button in the filter window, select the condition (Top or Bottom).
  2. Select the number of items you want to retrieve from the textual field (e.g. 10 countries).
  3. In the Ranked by field, select the measure that will be used for ranking (e.g. Total sales).

Setting Numeric Filters

Numeric filters let you to limit your dashboards to specific value ranges. For example:

  • Keep only sales above 100$.
  • Keep only product IDs between 1000020 and 1000030.

There are various options for setting a numeric filter for your dashboards. Click on each of the options below to learn how to set numeric filters.

Set a filter to include specific value/s

Use this filter to select a specific known value or multiple values from a numeric field: for example: "Quantity = 1 and 2 and 3”.

  • Searching for a value - you can search the list of numeric values to narrow it down. When the list is filtered by search, Select all applies only to the items that appear in the filtered list (items that match the search criteria).
  • You cannot exclude any values from this list. Use the Is not option for this. See Set a filter to exclude specific value/s.
Filter by a number range

You can filter numeric values by a range (“between”), for example, "All products whose price is between 1.99 and 20".

Filter by a numeric condition

You can filter numeric values by specific characteristics. For example: "All customers whose age is over 15", "All products whose price is between 1.99 and 20".

Set a filter that is dependent on a measure value (Top/Bottom ranking)

Use this to filter a numeric field, depending on the value of a certain measure, to answer questions such as "what were the Top/Bottom 10 versions in terms of Total number of bugs".

  1. On the fourth radio button in the filter window, select the condition (Top or Bottom)
  2. Select the number of items you want to retrieve from the numeric field (e.g. 10 ID numbers)
  3. In the Ranked by field, select the measure that will be used for ranking (e.g. Total number of bugs .).
Set a filter to exclude specific value/s

Use this filter to exclude a single or multiple value/s, for example, "Show me all IDs that are not 3144 and 3145”.

  • In the Filter window, on the fourth radio button, select Is Not and then select the values/s you wish to exclude from the dropdown list.


After filters are applied to a chart, you can hover over the filter icon on the chart block to see which filters are applied.

Viewing Statistics

The Statistics view in Sisense Notebooks helps data analysts to save time by automatically showing the most common statistics without having to write more SQL or wait for additional charts to load individually. This is an ad-hoc feature to help data analysts to quickly understand the query. The tab is available for both SQL and Code blocks.

  • To view the results of your query as statistics, click the Statistics tab.
    statistics.png

Was this article helpful?