Skip to content

Creating a new Workbook#

To create a Workbook:

  1. Click on the "+" button and select "Workbook" or right-click in the File Browser and select "Create New"→ "Workbook". The dialog 'Add Data' opens.

    INFO: A new Workbook is already created in the background with the default designation "Workbook". If there is already a Workbook "Workbook" the name for the ne Workbook will change to "Workbook 1" and so on.

  2. Select the folder where the data source is located, select the data source and confirm with "Add Data". The Workbook opens.

  3. Click on "File" and select "Save". The "Save" dialog opens.

  4. Select the folder where the Workbook shall be saved in, enter the Workbook name and confirm with "Save Changes". Creating a Workbook is finished.

INFO

When using data from a sheet in another Workbook or an import job, the data from that source sheet is copied to the new sheet if the new sheet is marked as kept. In previous versions, the source sheet was referenced instead, which led to Workbook data not being deleted by housekeeping.

The Workbook is populated with a sample of data from that data source. To use data in Spectrum that data must be part of a data source. If you have a source of data you want to use for analysis, such as an Excel spreadsheet, you need to create a data source using that data, or have a system administrator create one for you.

Sample data and full data#

Data displayed in Spectrum Workbooks is a sampling of the full data from the source. When creating or editing an import job, file upload, or data link, you set the number of sample records to populate a Workbook from the specific data source being used.

A data source added to a Workbook displays the number of sample records configured for that source. When creating calculations in the Workbook, those calculations are being applied only to the sample data until the Workbook has run. After running a Workbook, the calculations made in the Workbook are applied to the full data and can be viewed or downloaded by right-clicking on the Workbook and selecting Browse All Data.

The next time the Workbook is opened after running, while the calculations have been applied to the full data, you still view the sample size configured from the data source.

Status notifications have been added to the bottom of Workbook worksheets to show if the worksheet is displaying information based on sample or full data and if the worksheet is filtered.

Sample data messages#

If the worksheet's calculations are based only on sample data, the notification is orange. It tells you how many sample rows are being displayed in the worksheet and that you can run the Workbook to display the sheet calculations based on the full data from the data source.

If the worksheet is displaying no information, this could be that none of the original sample data fits the criteria of the calculations. Running the Workbook takes into account all current worksheet calculations and might bring in new sample data to populate the worksheet if data from the data source meet the calculation criteria.

Full data messages#

If the worksheet's calculations are based on the full data, the notification is green. It tells you how many sample rows are being displayed in the worksheet out of how many total records from the data source. If all the records of the source are being used, the status bar displays the total record count.

Creating Multiple Workbooks#

To create multiple Workbooks that reference the same data set:

  1. From the "File" menu, select "New".
  2. In the new Workbook, from the File menu, select "Add Data".
  3. Navigate to the data source you want to use and select the data source.
  4. Click "Add Data".
  5. Save your changes if needed.

Running a Workbook#

From the Workbook you can run the Workbook using the entire data set. Depending on the volume of data involved, this process might take a while.

To run the Workbook having it opened, click on the "Run" icon from the 'Workbook toolbar'.

To run a Workbook from the File Browser, right-click on the required Workbook and select "Run".

Storing a Workbook#

Storing a newly created Workbook#

When creating a Workbook from anywhere in Spectrum it gets stored in your home folder with a default name.

INFO

Closing an empty Workbook, which is stored under default name and location, being in 'File Browser' or another Workbook, the empty Workbook is deleted automatically.

Closing New Workbook with Content#

Closing an open Workbook with content, which has not been renamed or moved for the first time, opens the 'Rename and Move' dialog to store the Workbook. Otherwise the Workbook is named 'Workbook' as default.

Closing an open Workbook with content, being in 'File Browser' or another Workbook, the 'Rename and Move' dialog appears.

Closing New Workbook without Content#

Closing an empty Workbook, which is stored under default name and location, the Workbook is deleted automatically.

Closing an empty Workbook, which is stored under default name and location, being in 'File Browser' or another Workbook, the empty Workbook is deleted automatically.

Renaming and Moving#

You can rename and move an existing Workbook within your File Browser. The content remains the same while only the name and storage location changes.

To rename and move a Workbook:

  1. Double-click on the Workbook. The Workbook opens.

  2. Select "File" and click on "Rename and Move". The 'Rename and Move' dialog opens.

  3. If needed, enter the new name of your Workbook and select the new storage destination on the left side menu.

  4. Confirm with "Save Changes". The Workbook is stored using the new name and location. Renaming and moving is finished.

Duplicating#

You can duplicate a Workbook within your File Browser. The content of the original Workbook remains the same while you can store and edit the content of the duplicate.

To duplicate a Workbook:

  1. Double-click on the Workbook. The Workbook opens.

  2. Select "File" and click on "Duplicate". The 'Duplicate' dialog opens.*

  3. If needed, enter the new name of your Workbook and select the new storage destination on the left side menu. INFO: Default settings enlarge the name of the original Workbook to 'Copy of'.

  4. Confirm with "Save Changes". The duplicated Workbook is stored. Duplicating is finished.

Opening a Workbook#

To open an already existing Workbook, double-click on the Workbook or right-click and select "Open". The Workbook opens in a new tab.

Opening a Prior Version of a Workbook#

You can save previous versions of a Workbook when it is run more than once.

To open a prior version of a Workbook:

  1. Right-click the Workbook and select "Show Details". The Workbook Details page opens.

  2. Click on "View Latest Results" or scroll down to the section 'Current Data' and select the "Show Data" icon.

  3. Open the required Workbook or download the data.

Opening a Workbook Using a URL#

A Workbook can be opened by a user with permissions to view the Workbook entry by entering a URL into the browser.

To open a Workbook by using a URL:

  • by ID: Enter the URL with the Workbook ID, e.g. <https://localhost:8080/workbook/25>

    http(s)://<server>:<port>/workbook/<wbkID>

  • by path: Enter the path, e.g. <https://localhost:8080/workbook?path=Users/Matthew>/Seasonal\_Earnings

    INFO: The schema is: http(s)://<server>:<port>/workbook?path=<path/to/workbook.wbk>

  • into a specific worksheet: Enter the path to a specific sheet, e.g. <https://localhost:8080/workbook?path=/Users/Matthew>/Seasonal\_Earnings.wbk&sheet=products

    INFO: The schema is: http(s)://<server>:<port>/workbook?path=<path/to/workbook.wbk>&sheet=<sheetName>

Viewing Full Results in a Workbook#

To view the full Workbook results:

  1. From the Workbook menu, select "View Full Records", or click the 'View Full Results' icon on the toolbar.
  2. Click "Open" to view the data set in the Worksheet view or click "Next" at the bottom of the table to view more records.

Integrating Workbook Results With Other Systems#

If you use other systems or BI tools, you can connect and consume results generated by Spectrum Workbooks so they can be leveraged for other processes or reporting mechanisms using the integration link.

INFO

The default limit for number of rows to download is 100,000, as this functionality is intended for small aggregated data sets. To adjust the record download limit, change the rest.download-data.records-max=100000 property in the /conf/default.properties file. Setting the value to 0 unlocks the limit, but increasing the number could result in slower processing for the Spectrum conductor. Your local system might also have download restrictions. If you want to download more than 100,000 rows, we suggest adjusting the number based on environmental variables from Spectrum, your infrastructure, and external consuming tools.

Example: How to Use This in Power BI#

  1. Click "Copy Integration Link" in the Download dialog for full results page of a Workbook. If you are using a browser that doesn't support copying to the clipboard, copy the provided URL instead.
  2. Go to Power BI Desktop and in the Get Data workflow, find "Other > Web".
  3. Paste the URL in dialog and click "OK".
  4. Select "Basic authentication" and enter your Spectrum username and password for the environment linked previously and click "Connect".
  5. On the next screen review and confirm the data to be loaded.
  6. Data is loaded and you can take full advantage of Power BI capabilities. You can also publish your Power BI dashboards and reports to Power BI Cloud maintaining reference to this Spectrum connection.