Writeback on semantic models
Overview
Writeback to semantic models enables updating cell values for specific combinations of dimension values directly within the semantic model. This capability allows business users to modify data from Microsoft Excel and Kyvos Viz. While analyzing semantic model data, it eliminates the need for external updates. It supports advanced analytical scenarios, such as What-if Analysis—for example, in a financial semantic model, actuals can be sourced from underlying systems. At the same time, budget values can be entered directly by users through front-end applications such as Excel.
Points to know
Writeback is not recommended when using Kyvos filtering on date data type dimensions.
Writeback is only supported on Dimensions filtering.
Writeback is supported only for the multidimensional semantic model in No-Spark.
Enable writeback functionality in the semantic model to perform writeback. By default, writeback is disabled.
Writeback on calculated measures, distinct count, and semi-additive measures is not allowed.
Writeback is performed at the lowest level of any hierarchy. A unique Identifier is set at the lowest level of any hierarchy and attributes.
If no identifiers are defined, it includes all lowest-level members from all hierarchies and all attributes from related dimensions in the view.
If identifiers are provided, only the specified unique identifiers for each dimension in the view are used.
Writeback is supported only for base measures with the SUM summary function.
If Column Level Security (CLS) or Masking is applied to a column that is mandatory for performing writeback, then writeback will not be supported.
Writeback is not supported when a Row Level Security rule is applied, and the corresponding attribute contains a blank value.
Kyvos visualization supports writeback only when the related dimension or attribute appears in the view, not when used solely as a filter. In contrast, Microsoft Excel supports writeback even if the related dimension or attribute is present only as a filter.
Enabling writeback on semantic models
To enable writeback on the semantic model, perform the following steps.
Select a semantic model from the list.
In the Properties pane, select the Enable Writeback checkbox. By default, it is disabled on the semantic models.
Click the i icon to read more about the feature.
Setting the unique identifier
To set the unique identifier, perform the following steps.
A Unique Identifier is required to ensure that each row in a dimension or dataset is distinct. This prevents duplication during Writeback operations.
Select a semantic model from the list.
In the Properties pane, select the Writeback Settings. The Writeback Settings dialog is displayed.
Select the unique identifiers.
Click Apply.
Write back on the semantic models
To write back on the semantic model, perform the following steps:
Verify that the Writeback is enabled on the semantic model.
Click Design to select the design mode.
Drag the related dimension into one of the places (shelves) where Drop here is displayed. The available places to drop a dimension or measure vary depending on the selected chart type.
Right-click the dimension, Writeback > Modify. The Writeback dialog is displayed.
In the dialog box, the current value is displayed. You can now edit the current value with the new value.
Click Apply. The Sum Sales value has been updated along with the total sales values in the worksheet. The edited value is marked with the edit icon, indicating that a writeback operation has been performed on it.
After the writeback operation, you can update, commit, or discard the writeback changes.
To update, commit, or discard the writeback changes, perform the following steps.
Note
You cannot discard the updated value once it is committed.
Right-click the dimension, Writeback > View Writeback Changes. Alternatively, click the Writeback Changes link displayed under the Semantic Model. The writeback number will incrementally change when you perform the writeback.
The Writeback Changes dialog is displayed. In the Actions list, select one of the following:
Commit: When you commit a writeback change, it applies to all users. The changes will be reflected in the semantic model.
Discard: When you discard a change, it removes the writeback changes from the worksheet and restores the original value.
Write back on Microsoft Excel
To write back on Microsoft Excel, perform the following steps:
After selecting the semantic model, enable writeback in Excel.
In the PivotTable Analyze tab, under the Calculations group, select OLAP Tools > What-If Analysis > Enable What-If Analysis.
Right-click and select Calculate Pivot Table with Change to modify the Sales values as needed.
Go to the Calculations group in the PivotTable Analyze tab, click OLAP Tools, and select one of the following:
Publish changes: Publishing applies changes to all users and updates the semantic model. Changes made in Excel also appear in the semantic model.
Changes in Excel
Changes in the semantic model
Discard Changes: Discarding a change removes the writeback changes from the worksheet and restores the original value.
Inserting new records
Users with the Writeback Executor privilege can insert new records while browsing data in Kyvos Viz. Record insertion is supported only in the Crosstab visualization and applies to measures configured as base measures with the SUM aggregation function that are present in the current view.
To enable record insertion, you must configure the required dimension columns as unique identifiers through the existing writeback settings.
When inserting a new record, a table containing all required columns is displayed for data entry. In addition, you can select:
Any column from dimensions related to the fact table for which unique identifiers are not configured.
All permitted measures originating from the same fact table.
To insert a new record, perform the following steps.
In Design mode, right-click the measure header or cell value and click Insert Record.
The Insert Record dialog box is displayed.Select or enter values for the required dimension columns configured as unique identifiers.
Optionally, select values for additional related dimension columns, if available.
Enter values for the allowed measures displayed in the dialog box.
Click Insert to add the new record.
Click the Plus (+) icon to add multiple records. To remove a record, click the Delete (-) icon corresponding to the record.
After entering all required details, click Insert to save the records successfully.
Managing Writeback Changes
The Writeback Changes dialog displays all pending writeback transactions, including inserted and modified records, before they are committed to the semantic model.
To manage writeback changes, perform the following steps.
In the left navigation pane, click Manage Writeback Changes.
Review the list of pending writeback transactions in the Writeback Changes dialog.
Verify the operation type for each transaction:
Inserted – Indicates a newly added record.
Modified – Indicates an update to an existing record.
Review the affected dimension members and measure values in the Members column.
Use the available actions for a transaction:
Commit (green icon) to commit the change. After committing this change, this will be permanent for all users.
Modify (pencil icon) to modify the writeback entry.
Delete (cross icon) to discard the writeback change.
To perform an action on multiple transactions, select the required rows and use the Actions drop-down menu.
Click Refresh to reload the list and view the latest writeback changes.
After reviewing all transactions, click Close to exit the dialog.