Page tree
Skip to end of metadata
Go to start of metadata

You can create workbooks, add additional data sources, change the column names and sheet names, hide columns, apply formulas, view workbook details, and more.

You can also copy workbooks, import worksheets from other workbooks, duplicate workbooks and worksheets, and exchange datasources.

To learn more about working with workbooks, see the Analytics Video.

 

 

Create workbook

To create a workbook:

  1. Choose Workbooks from the + drop down menu located in the top left corner of the screen or right-click in the navigation bar on the left side of the screen and select Create New > Workbook.
  2. From the File menu, choose Add Data.
  3. Navigate to the folder where the data sources are located and select the name of the data source you want to use.
  4. Click the Add Data button.

The workbook is populated with a random sample of data from that data source.

Note: To use data in Datameer, 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.

 

Open existing workbook

To open an existing workbook:

  1. Under the browser tab, navigate the file tree on the left of the screen and find the workbook you want open.
  2. Highlight the workbook by clicking on it.
  3. Double-click to open or right click on the highlighted workbook and select Open from the drop down menu.

Open a workbook by URL

A workbook may be opened by a users with permissions to view the workbook by entering a URL into the browser.

There are two valid URLs that can open a workbook:

Workbook IDhttp(s)://<server>:<port>/workbook/<wbkID>

Example: https://localhost:8080/workbook/25

As of Datameer v5.10

Workbook Pathhttp(s)://<server>:<port>/workbook?path=<path/to/workbook.wbk>

Example: https://localhost:8080/workbook?path=/Users/Matthew/Seasonal_Earnings.wbk
 

Open a workbook to a specific worksheethttp(s)://<server>:<port>/workbook?path=<path/to/workbook.wbk>&sheet=<sheetName>

Example: https://localhost:8080/workbook?path=/Users/Matthew/Seasonal_Earnings.wbk&sheet=products

 

Open a prior version of a workbook

Datameer gives users the option of saving previous versions of a workbook when it is run more than once. Learn more about optimizing your workbooks and data retention

To open a prior version of a workbook:

  1. Under the browser tab, navigate the file tree on the left of the screen and find the workbook you want open.
  2. Highlight the workbook by clicking on it.
  3. Right click on the highlighted workbook and select Show Details from the drop down menu.
     
     
  4. Find the workbook needed previously run workbook iteration in the Current Data section. Click on the show data icon in the right side of the box.
     
     
  5. From the Full Results page, you have the option of opening the workbook in Datameer or downloading the data. 
    For convenience, other previous ran workbook iterations have a provide link.


     

Add additional data sources

To add additional data sources to an existing workbook:

  1. From the File menu, choose Add Data or click the Add Data icon on the toolbar.
  2. Navigate to the folder where the data sources are located and select the name of the data source you want to use.
  3. Click the Add Data button.
  4. Repeat the previous steps for each data source you want to use.

For each data source you select, a tab is added to the workbook, containing a sample of data from that data source.

Available as of Datameer 3.1

Add multiple data sources at the same time by holding the shift button.  

Exchange data sources

 You can exchange data sources to run a workbook on a different set of similar data. This method is faster than recreating the workbook.

For example, you can run a workbook on data for each month by exchanging the data source to point to the data for the next month. Each data source must have the same structure. Further analysis applies only to the new data after the exchange.

This option is only available when the current data sheet uses a data source imported using Add Data. The option is not available for sheets created using filters, joins, sorts, external datasheets, or formulas. You must also have write permissions for this workbook to use this option.

To exchange data sources in an existing workbook:

  1. From the File menu, choose Exchange Data or click the Exchange Data icon on the toolbar.
  2. Navigate to the folder where the data sources are located and select the name of the data source you want to use.
  3. Click the Exchange Data button.

Working with sheets and columns

You can set up sheet names, resize columns, hide and show columns, rename worksheets, import worksheets from other workbooks, and more.

Viewing sheets within a workbook

There are multiple ways to navigate between sheets within a workbook.

To change between sheets within a workbook:

  1. Click on the sheet tab you wish to view a the bottom of the page.
  2. Select the Sheets menu at the top of the page and click on the sheet in which you wish to view.
  3. Click on the Sheet Dependencies button in the toolbar and switch between sheets by clicking on sheets that are dependent with each other.

Set up sheet names (tabs)

You can rename workbook sheets.

To change the name of a sheet:

  1. Right-click the sheet name and click Rename.
  2. Enter the new name and press Enter.
  3. To undo the change, right-click the name again and click Undo.

Delete sheets in a workbook

You can delete sheets in a workbook that you don't want to use or keep.

To delete sheets:

  1. Click the tab in the workbook that you want to delete to select it.
  2. Right-click the tab and choose Delete.
  3. Click Delete to confirm.

Duplicate sheets in a workbook

You can duplicate workbook sheets - so that you can create variations on a theme. The new sheet is named with the next available number. If there are five sheets and you duplicated Sheet3, the new sheet is named Sheet6.

 To duplicate sheets in a workbook:

  1. Click the tab in the workbook that you want to duplicate.
  2. From the Workbook menu, choose Duplicate Sheet, or click the Duplicate Worksheet icon on the toolbar.
    You can also right-click the tab and choose Duplicate.
  3. Select the columns you want to copy to the new sheet and click Create Sheet Copy.

Reorder sheets in a workbook

You can reorder sheet locations inside of a workbook.

To reorder sheets in a workbook:

  1. Right-click the sheet tab at the bottom that you wish to move within your workbook.
  2. Select Move.
  3. Click the space between the current sheet tabs where you would like the selected sheet to be placed.

Reorder columns in a workbook

You can reorder columns on a sheet inside of a workbook.

To reorder columns on a sheet:

  1. Left-click and drag the column from the column name you want to reorder to the new placement.

Sheet dependency graph and navigation

The graph overlay shows the current selected sheet with in and outgoing sheets in a visual manner.

  1. View the sheet dependency overlay by clicking the Sheet Dependencies button in the toolbar at the top of the screen.
  2. The current sheet in which you have selected with be highlighted in the middle of the graph.
  3. To navigate between dependent sheets, click on the sheet name located on the graphic overlay.
  4. To close the sheet dependencies overlay, re-click the toolbar button or click the "X" on the current sheet selected on the overlay.

Import sheets

Importing sheets allows you to chain workbooks together.

To import sheets from one workbook into another workbook:

  1. Start by opening the workbook you want to import the worksheet into.
  2. From the Workbook menu, choose Add Data, or click the Add Data icon on the toolbar.
  3. Navigate to the name of the workbook you want to use and click the Add Data button.
  4. Click the box next to the sheets you want to import and click Add Data button.
  5. The new worksheet tabs are added to the right of the existing worksheets.

 

 

Suggested column names

Datameer creates suggested column names when the user creates new columns using the formula builder.

These names are based off the column name used in the formula and the type of formula used.

If you would like to change these name see Setting up column names.

Set up column names

Column names may only contain letters (only standard, capital or lower-case characters), numbers and/or underscores. Column names must begin with a letter or underscore.

Column names are case sensitive by design. Currently there isn't a parameter available that can disable the case sensitive nature of these names.


You can rename the column names on workbook sheets that are editable.

Tip: Look at the status line just above the list of tabs to see if a particular sheet is writable.

 To edit the column name:

  1. Right-click the column name and click Rename.
  2. Enter the new name and press Enter.
  3. To undo the change, right-click the name again and click Undo.

Resize columns

To resize columns:

  1. Place the cursor to the right of the column you want to change between the two column headings and the icon will change to a double-ended arrow.
  2. Drag the column marker to the desired width.
  3. Click to lock the width.

 

Double-click a column name to fully expand the column.

 

Hide and expand columns

To hide (collapse) a column, right-click the column name and select Hide Column. 

Double-click the hidden column to bring it back into view.


Available as of Datameer v5.6

A Show/Hide Columns toolbar button displays all columns of the current worksheet. Users can use this menu to show or hide columns as a batch process.

Insert columns

To insert a new column:

  1. Right-click a column name.
  2. Choose Insert Column.

    A column is added to the left of the column chosen. 

    Each workbook can contain a maximum of 702 columns.

Remove columns

To remove a column:

  1. Right-click a column name.
  2. Choose Remove Column.

Format columns

To format the contents of a column:

  1. Right-click a column name.
  2. Choose Format Cells.
General
  • Column text color
  • Column background color
  • Column text alignment (right, left, center)
Numbers
  • Thousands separator (comma to separate numbers by thousand)
  • Select the amount of decimal places to display in the workbook

Date

  • Input a parse pattern to display the date.  
    (The default doesn't display milliseconds)



Shaded rows in a workbook

The worksheet highlights rows by each group series when a GROUPBY function is used.

 

Formulas

Formulas provide the ability to analyze your data in powerful ways.

Create formulas

You can create formulas on workbook sheets that are editable. When you click the data area of a column that has a formula associated with it, the formula displays above the workbook. You can also create a new sheet and create formulas that reference fields from other sheets. Double-click a column to display the Formula Builder.

Tip: Look at the status line just above the list of tabs to see if a particular sheet is writable. A read-only and a writable sheet are shown.



To create a formula using the Formula Builder:

  1. Double-click the data area in a column and the Formula Builder displays. Or you can click the fx icon.
  2. Click the categories in the left column to choose the type of function.
  3. Choose a function from the list on the right.
  4. Enter the argument or arguments shown for that function. You can click a sheet and select a column.
  5. Click the Plus icon to enter additional arguments (if supported).
  6. Click OK.

The resulting function displays next to the fx icon. To learn more, see Using the Formula Builder.

To see this in action, see the Introduction to Analytics.

To use operators  in a formula:

  1. Click the data area in a column and the current formula if one exists displays above the workbook.
  2. Create or edit the formula using operators such as < or > or <= and press Enter.

To use regular expressions in a formula:

  1. Click the data area in a column and the current formula if one exists displays above the workbook.
  2. Create or edit the formula using regular expressions press Enter.

See Using Regular Expressions to see some examples.

Datameer offers an API to extend the built-in formulas. See the Developer's Guide to learn more.

Edit formulas

To edit formulas:

  1. Click the data area in a column and the current formula displays above the workbook.
  2. Edit the formula and click Enter.

Workbook level tasks  

You can save workbooks, view or change workbook settings, link a workbook to multiple data sources, calculate a workbook, and more.

Save workbooks

When you save a workbook, you also specify the Workbook Settings. If you don't know which settings to use, you can keep the default settings and you can change them later or the system administrator can set them. See Configuring Workbook Settings to learn more.

To save workbooks:

  1. From the File menu, choose Save, or click the Save Workbook icon on the toolbar.
  2. Navigate to the folder where you want to save this workbook or choose to save the workbook in a new folder.
  3. Enter a name in the Save as field.
  4. Click Save.
  5. Fill in workbook settings:
    1. Select the appropriate trigger when the workbook will run.
    2. Select data retention method for how long workbook runs will be stored.
    3. Write a workbook description.
    4. Select which sheets need to have results saved. Storage space will be saved by only selecting useable data.
    5. Specify how to deal with errors in workbook formulas.
    6. Add email addresses that will receive a notification if the workbook experiences an error or each time the workbook preforms a successful run.
    7. Add custom hadoop or logging properties.
    8. Check the box if the workbook should first run immediately after saving.
  6. Click Save when you are finished.

To save a copy of a workbook:

  1. From the File menu, choose Save As, or click the Save Workbook As icon on the toolbar.
  2. Navigate to the folder where you want to save the workbook, enter a name, and click Save As.

Create multiple workbooks

You can create multiple workbooks that reference the same data set, however you can only view and use one workbook at a time.

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

Calculate a workbook

From the workbook you can calculate the workbook using the entire data set. Depending on the volume of data involved, this may take awhile. Once you are viewing the Full Data page, you can view additional data using the Next button or go to a specific record using the Go To Line option.

  1. From the Workbook menu, choose Calculate or click the Calculate Full Workbook button on the toolbar.
  2. While the data is being calculated, you can click the Abort button if you want to calculate at a later time.
  3. Once the calculation is complete, the Full Data page displays.
  4. You can click the Next button to view additional pages of data or click the Go To Line option and enter a line number to view a specific record.
  5. Click the Edit button to return to the Workbook.

After making changes to an existing workbook, the next time the workbook is run, the changes are applied in the workbook calculation. Historical data before the change to the workbook isn't updated.

View full results in a workbook

 To view full results:

  1. From the Workbook menu, choose View Full Results, or click the View Full Results icon on the toolbar.
  2. Click the Open button to view the data set in the worksheet view or click the Next button at the bottom of the table to view more records.

Go to line - in a workbook

To go to a specific record while in the Workbook:

  1. From the Workbook menu, choose Go to Line or click the Go to Line button on the toolbar.
  2. Enter a line number to view a specific record and click Go.

View workbook details

You can view information about the workbooks that you have open including:


Workbook information
gives general information about the current workbook:

Name of the workbook, the number of sheets the workbook contains, the reprocessing schedule, the last time the workbook was processed, and any notifications set for the workbook.

Sheet Summary information gives advanced information on individual pages within the current workbook:

 

Sheet Typesheet typedescriptionjob pathconnectionlast executedkeptpartitionedformulas usedfilter sourcefilter connectorfilter argumentsjoin typejoin pairssort sourcesort argumentssources of union
Source Sheetxxxxxx          
Formula Sheetx    xxx        
Filtered Sheetx    xx xxx     
Joined Sheetx    xx    xx   
Sorted Sheetx    x       xx 
Union Sheetx    xx        x

 

To view Workbook details:

  1. From the Workbook menu, choose Workbook Info or click the Workbook Info button on the toolbar.
  2. The workbook information displays in a new window.

Changing workbook settings

You can set up the schedule of when a job runs when you create a workbook or you can change the schedule settings later. See Configuring Workbook Settings to learn more about scheduling.

Workbook sharing permissions security

Only users with admin rights can set or change permissions.

To view sharing permissions and security settings for a workbook:

  1. Click the Browser tab at the top of the page.
  2. Click on Workbooks in the navigation window on the left side of the screen.
  3. Click to highlight the workbook for which you wish to edit permissions.
  4. Right-click the workbook and select Information or simply click the Information button on the tool bar.
  5. The sharing and full results sharing permissions can be found by clicking the Lock Icon in the info box on the right side of the screen.

Sharing

Owner:

  • The owner box shows the current owner of the workbook. The owner has read, write, and execute privileges. Changing the owner can only be completed by someone with administrative privileges. 

Groups:

  • User groups can be added to share the workbook. The administrator or owner of the workbook can set groups to read, write, and execute the workbook.
  • By adding the read, write, or execute settings to Others, the admin or user can set permissions for all users.

Full results sharing

  • Full results sharing give the admin or owner the ability to give selected groups the ability to read the full data from the workbook.
  • Only users that have access to the workbook in from the sharing section are eligible to view the full data of a workbook.
  • In order to create infographics from workbook data that you are not the owner from, users must have full results sharing permissions.
  • No labels