The custom visual can be added through the “Get more visuals” option in Power BI. The visual is integrated into the report as a matrix visual. Data can be displayed, entered, edited, and written back once the setup (see the following sections) has been successfully completed.
Modeling the cBI Focus Planner #
The representation of data in the visual is based on the combination of information from the fact table and the associated dimension tables. These are assigned to the corresponding groups (data roles) in the “Build Visual” section:
- Values – Values to be displayed in the visual
- Column Groups – Grouping of data along the columns
- Row Groups – Grouping of data along the rows
- Filters (optional) – Limiting the displayed data
Column Selection for the Visual #
- Open the “Build Visual” section in the cBI Focus Planner.
- Add columns from the fact table to the data role “1. Values.”
- Assign columns from the dimension tables to the data roles “2. Column Groups,” “3. Row Groups,” and/or “4. Filters.”
To ensure the write-back function operates reliably, all relevant columns from the used dimension tables must be incorporated into the modeling. The system is only able to correctly assign and write back data if a unique key value can be identified for each dimension. A valid entry in the fact table requires the complete identification of all key values.
To support the modelling process, the cBI Focus Planner includes an integrated plausibility check that automatically verifies whether all required columns are present (see this section).
Modeling Guidelines #
- Columns that cannot be meaningfully categorized into “Row Groups” or “Column Groups” should be assigned to the data role “Filters.”
- For columns in “Filters”, a default value can be defined through the “Value Filter Options” to set a fixed value during the write-back process (see section “Filter Functions“).
- This is particularly necessary when a specific dimension should not be altered by the user in the Planner but is instead predetermined.
The successful selection of the tables is confirmed by green checkmarks in the “Data” section.
Configuration of the Database Connection for Write-back #
Utilizing the write-back function in the cBI Focus Planner requires a correctly defined connection to the target database, as well as the configuration of an authorized WRITE user. These settings are made in the “Writeback Service Settings” section of the Formatting Pane, where the database connection is established and one of two options for a WRITE user can be selected.
Mandatory Setup of the SQL Database Connection #
- Open the visual and select the “Writeback Service Settings” section in the “Visual formatting” area.
- Navigate to “Connection Settings.”
- Enter the complete connection details for the SQL Server (Database Server URL, Database Name).
Activating Writeback via a Proxy User #
- Open the “Proxy User (SQL Server Authentication)” tab and enter the credentials of a database user with the necessary permissions.
(Alternatively) Activating Writeback via Dedicated SQL Server User Login #
- Enable the “User-specific authentication” switch if individual logins for each user with dedicated database users are intended.
When “User-specific authentication” is enabled, a separate login field appears in the visual for all users. Only users with valid SQL Server credentials that have write permissions will have access to the write-back function.
Distinction Between Proxy User and Dedicated SQL Server User Login #
- Proxy User: Used for simplified, anonymized access. Multiple users access the system through the same login with identical rights. Differentiation is not possible in this mode.
- Dedicated SQL Server User Login: Allows for individual authentication and permission assignment by the database administrator. This option is preferred when there are security guidelines, role restrictions, or audit requirements.
Checking Modelling for Writeback – “Check Plausibility” #
Once an editor edits the report, the “Check plausibility” button is displayed in the visual. This function verifies whether a functional connection to the specified database exists and whether the write-back is technically feasible.
The check returns the following error messages:
This message indicates that in the modelling of the cBI Focus Planner, no corresponding dimension tables could be found for the listed key columns of the fact table. This can happen if:
In this message, it has been determined that there is ambiguity for the listed dimension tables. Consequently, a unique key for this dimension cannot be identified, which is essential for the write-back. To resolve this issue, columns from the dimension table must be added to the modeling.
Example:
The table for the time dimension D_Time contains the year and the month. However, in the modeling of the cBI Focus Planner, only the month has been included so far, leading to multiple elements (months from different years) being possible from the time dimension. Therefore, the year must also be added to the modelling.
Setting Up Primary Key (PK) Relationships #
In the “Primary Key (PK) Relationships” section, two data-specific aspects can be configured for processing in the cBI Focus Planner:
- Ensuring the unique assignment of key columns between the fact table and the corresponding dimension tables, even if they differ in name.
- Identifying surrogate keys if they have been established in the fact table.
Assignment of Key Columns #
The cBI Focus Planner is capable of automatically recognizing a logical relationship between the dimension table and the corresponding field in the fact table through their respective primary keys, provided they share the same name (e.g., a primary key named ProductID in both a dimension table and as a field name in the fact table indicates a content-related connection). The link will then be created automatically without further action by the editor. This way, the underlying relationships can be correctly identified and considered for further processing in the visual.
The appropriate mapping is essential for the correct functioning of the visual, especially concerning the write-back function. Only when the key relationships are clearly defined can the system accurately track changes and write them back to the database.
Incorporating Surrogate Keys #
If a surrogate key has been defined in the fact table on the database side, it serves to uniquely identify individual data rows and must be specified as the corresponding database field via a pull-down selection for the correct functioning of the cBI Focus Planner.
Using “Primary Key (PK) Relationships” #
- Open the visual in the Power BI report and select the “Format visual” option in the “Visualizations” pane.
- Navigate to the “Primary Key (PK) Relationships” section.
- Under “Mapping of Dimension Table PKs to Fact Table PKs,” assign the corresponding key in the fact table for each dimension table used.
- Under “Surrogate key,” select the synthetic key from your fact table, if available.
Note: The selectable key columns will be queried from the connected database. Therefore, a valid connection must be established in the “Writeback Service Settings” beforehand.
