In Datameer, you can create a workbook to get to new insights with your data. Inside the workbook, you can add additional data sources, change the column and sheet names, collapse 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.
| Table of Contents |
|---|
...
Creating a Workbook
To create a workbook:
- Choose Workbooks from the + (plus) button or right-click in the navigation bar on the left side of the screen and select Create New > Workbook.
- From the File menu, select Add Data.
- Navigate to the folder where the data sources are located and select the name of the data source you want to use.
- Click Add Data.
...
Status notifications have been added to the bottom of workbook worksheets to show if the worksheet is dispaying displaying information based on sample or full data and if the worksheet is filtered.
...
If the worksheet is displaying no information, this could be that none of the origial original sample data fits the criteria of the caculationscalculations. Running the workbook takes into account all current worksheet caculations calculations and might bring in new sample data to populate the worksheet if data from the data source meet the caculation calculation criteria.
Full data messages
If the worksheet's caculations 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.
Opening Existing Workbooks
To open an existing workbook:
...
- Copy - Copy the cell value to the clipboard.
- Filter by - A shortcut to open the filter feature and auto completes the fields using the equals expression to filter for matchs matches the cell's value.
- Exclude - A shortcut to open the filter feature and auto completes the fileds fields using the does not equal expression to filter for matches that don't match the cell's value.
...
- 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)
Shading rows in a workbook
The worksheet highlights rows by each group series when a GROUPBY function is used.
Copy cells
Double-click a cell to select it, and then right-click it and select Copy Cell Content to copy its contents to the browser's clipboard. This functionality doesn't work with the Safari browser.
| Anchor | ||||
|---|---|---|---|---|
|
The inspector gives you important information about the data of each column in your workbook. Use this feature for quick insights into the data that each of your columns contain.
| Section | |||||||
|---|---|---|---|---|---|---|---|
|
| Anchor | ||||
|---|---|---|---|---|
|
| Note |
|---|
As of Datameer 7.2 There are two tabs at the top of the workbook inspector. The inspector is separated at the worksheet level and column level. The worksheet level tab has a description box where text can be entered about each worksheet in the workbook.
The column level tab has a description box where text can be entered about each column of a worksheet. Below the description box, the column information is as described above.
|
To open or close the workbook column inspector:
- Click View in the menu bar and select/unselect Inspector.
- Click the Inspector icon in the tool bar.
Revert Actions
If you would like to| Anchor | ||||
|---|---|---|---|---|
|
The deduplication feature eliminates duplicate/redundant data from your worksheet columns. This procedure can be performed across all columns of a worksheet or for specifically selected columns.
To deduplicate a column(s) in a worksheet:
- From the Edit menu, click Deduplicate OR click the Deduplicate icon on the toolbar.
or 
- The worksheet inspector changes to offer options on which columns you want to perform deduplication on.
Select the radio button for your choice between performing deduplication across all columns or select columns of the worksheet.- If you selected the all columns option, click the Create Sheet button at the bottom of the inspector to apply the deduplication process.
You workbook creates a new sheet titled TransformSheet. This sheet has purged any records that are a duplicate across all columns.
A new tab titled Deduplicate is located in your worksheet inspector.
Run the workbook to view the full results. - If you selected the select columns option, additional criteria fields appear.
Enter one or multiple columns to be used to deduplicate your data.
Optionally, you can select an additional column and choose to keep the first or last record of the deduplicated data.
Click the Create Sheet button at the bottom of the inspector to apply the deduplication process.
- If you selected the all columns option, click the Create Sheet button at the bottom of the inspector to apply the deduplication process.
A deduplicated sheet can be updated by clicking on the Deduplicate tab on the page inspector. Make a change to any option(s) and click Update Sheet.
Revert Actions
If you would like to revert an action that you performed in a workbook, you can use the Undo and Redo buttons available in the toolbar. By default, you can undo or redo the last two actions performed on a workbook that modify the workbook state in the database. These actions don't include changes such as font style or column size, as the workbook's state remains the same. You can also access these options through the Edit menu or using keyboard shortcuts (Cmd+Shift+Z for undo, Cmd+Shift+Z for redo). If the cursor is in a text field, using the keyboard shortcut reverts only the most recent changes in the text field, not the whole workbook.
Administrators can adjust the settings for these buttons in default.properties and system.properties. There, admins can edit the amount of time the history of a workbook is kept, the maximum number of actions a a user can revert, and how many workbooks can retain a history at the same time.
To make sure this functionality isn't causing performance issues, you can check various statistics about the memory usage and workbook states in <Datameer>/logs/workbook_undo.log. The log is set to record the following values separated by comma:
absoluteMemoryConsumption,absoluteMemoryUsageInBytes,generalMemoryUsage,historiesInUse,historyStatesInMemory,maxMemory,relativeUsageInPercent,usedMemory,usersWorking,workbooksInUse
All undo related actions are collected, but for memory performance, the system prints the values to the log once per minute.
...
The formula editor is located at the top of every a worksheet within a workbook. Select a column on a worksheet and apply a formula with supported Datameer Datameer functions and and operators.
Formulas can be added manually or using Datameer's s Formula Builder. An auto-complete feature gives you the ability to visualize and complete formulas efficiently.
| Anchor | ||||
|---|---|---|---|---|
|
| Note |
|---|
As of Datameer 7.2 The formula editor has multi-line functionality. Add more than one line when writing a formula so that it is easier to both read and write long and/or nested formulas.
Add additional lines by pressing Enter pressing Enter on your keyboard while in the editor. Submit your formulas by clicking the the checkbox on on the right of the editor or using the the keyboard shortcut ⌘-Enter (mac) and and Ctrl-Enter (win). The multi-line editor can be expanded or collapsed by clicking and holding the edge of the formula bar and dragging your mouse. |
...
A formula can only reference columns from it's own worksheet and one other worksheet in the workbook. Referencing columns from different worksheets is possible after a a join sheet has has first been created.
| Syntax for referencing a worksheet and column | ||
| Syntax | Example | |
|---|---|---|
| Worksheet | # | #apache_log |
| Column | ! | !remoteUser |
| Worksheet and column | #apache_log!remoteUser | |
...
Enter a function name into the formula editor (case insensitive) with variables separated by a semicolon within parentheses.
Example: | Result explained |
|---|---|
| GE(#apache_log!status;400) | The Greater Than or Equal To function is being used comparing the "status" column from the "apache_log" worksheet |
...
You can create formulas on worksheets 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 in the the Fx bar bar. You can also create a new sheet and create formulas that reference fields from other sheets. Click the Fx button on the formula line to display the formula builder. (As of Datameer 7.2, the formula builder is located in the worksheet inspector.)
To create a formula using the Formula Builder:
- Click the Fx button on the formula line to access the formula builder.
- Select the categories in the left column to choose the type of function.
- Choose a function from the list on the right.
- Enter the argument or arguments shown for that function. You can click a sheet and select a column.
- Click the the Plus icon icon to enter additional arguments (if supported).
- Click Click OK.
The resulting function displays next to the fx icon. To learn more, see see using the Formula Builder.
...
- Click the data area in a column and the current formula if one exists displays above the workbook.
- Create or edit the formula using regular expressions press Enter.
See 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.
...