Setup and Configuration - BarChart

This section describes the available configuration options for the BarChart. Also, the following pages describes a short step by step instructions for building a simple report using BarChart. You will find the details of the configuration elements later in this document.

Validate the Write-back Service

After installation you might want to be sure that the write-back service is installed properly. You can validate that by navigating to your deployed URL, which is in the following format:

http(s)://yourserverName/PPWebService/PPWebService.svc

If you see the following page after the page load, then the Write-back Service is up and running.

Setting up the Basics

After you launched Power BI Desktop, connected to a data source, and imported the visuals (VPService, BarChart) into your report, the very first step is the configuration of VPService visual.

We must tell the visuals where they can find the Write-Back Service and which connection they should use during the process. You can see detailed information in this chapter how to configure VPService.

The configuration options will appear in the Format section of the Visualizations after you dropped the first column into the BarChart visual. You must complete the following steps in order to have a working report element for the write-back functionality.

Starting with [BarChart], you must set the WebService, Connection and Type properties (see referring chapters) to make the write-back service working.

Note! The content of [BarChart] » Web Service property has to be exactly the same (case sensitive too!) as in VPService ([Web Service] » Url).

Last but not least please make sure you use the chart properly, meaning adding measure(s) to the Measure Data and dimensions to Category Data (and Series Data). A chart supports multiple measures or one measure with one series dimension. Dimensions are usually text or date(time) type of values, the measures are numeric values.

Add Dimensions and Measures

Simply add some fields into the Category Data and Series Data sections of the visual and add a write-back compatible measure.

Publishing and Testing the Report

When you finished with your report, publish it to either your on-premises Power BI Report server, or to PowerBI.com. The write-back will work reliable only when your report is published, so you cannot test the full functionality in Power BI Desktop while you are authoring it. After publishing your report try to modify a cell value and click on save changes. Your modification should be visible in your report.

Completing the above steps, you should have a working, simple, write-back capable report using BarChart visual.

The following pages cover more detailed configuration options as well as typical use-cases that you might find useful for your needs.

Configuration

This section contains the configurations regarding the connectivity with the write-back service.

WebService

Here you must set the write-back service URL which should point to the hosting machine – or app service – where it was installed. Typical URL is:

http(s)://WEBSERVICE_COMPUTER_NAME/PPWebservice/PPWebservice.svc

Be careful not to have an extra slash at the end of the URL.

In case of missing or incorrect Web service URL, an error message pops up during the first write-back operation:

Type

This option is referring to the connection type that the write-back service will use. The setting is required.

• SQL: Set the connection string directly in the Connection property (leave the connection blank if using the default "SQLConnection" in set in the web.config of the write-back service)
• SQL Datasource: The connection is set in the PPWebService web.config and you will have to refer it by its name in the Connection property. Note: without an SSAS semantic model on top of your SQL database, SQL connections are only supported, if the relationships defined in your PowerBI report exists in your database with foreign key constraints, and only simple measures are used.
• SSAS: Set the connection string directly in the Connection property (leave the connection blank if using the default "SSASConnection" in set in the web.config of the webservice)
• SSAS Datasource: Set a connection name in the Connection property defined in the web.config of the webservice

Connection

Specify Name of the SQL or SSAS connection defined in the web.config file of the webservice, or the connection string directly, or leave blank depending on the Type setting.

Note: Maximum length of this property is 250 characters.

In case of missing Connection, an error message pops up during the first writeback operation:

General

Version

It provides the version number of the current visual.

Data Entry (Settings)

Windows Authentication

If you are in an on-premises environment using Power BI Report Server and Windows Authentication is turned on in the IIS for the write-back service this setting must be turned on, so that the Windows credentials can be passed back to the underlying data source. If you are completely in the cloud - your reports are published to PowerBI.com service - and you are using Azure AD, the setting should be turned off, so that AD credentials will be used when accessing the data source. 

In case of Gateway by turning on Windows Authentication will have the following effects: Instead of setting the PowerBI.com credentials in the write-back service request, the visual posts the windows login context. This value (e.g. domain\user instead of username@domain.com) will be set if you use USERNAME() in computed and/or default value columns (see later in this document) as well in SQL context variables. Also, this makes possible to use impersonation, and it is necessary to be turned on if Windows Authentication is set in IIS for the write-back service. 

DirectQuery

Legacy option, the type of the SSAS model (if present) is determined automatically and will be removed in future releases. 

Customer

Customer name provided by us along with the License server license key. If already specified in the web.config file (generally, you don’t have to set this at it is done by the setup), leave blank. If you are sharing one webservice between multiple license keys, specify here the customer key you want to use. 

Domain

Set it to one of the following. Generally, this should be left blank as it is configured in the write-back service web.config file during setup. 

• the fixed the name of the internal domain used (e.g.: DOMAIN) 
• auto: use the domain part (part after @) of the powerbi.com user 

• auto-short: use the short domain part (part after @ and before the .) of the powerbi.com user 
• azure: use the full powerbi.com username (e.g.: user@domain.com) for Azure Active Directory authentication 
• leave empty for no domain (e.g.: for a SQL authentication user) 
• SQL=…, SSAS=… set domain options for SQL and SSAS separately 
• Custom user mapping of powerbi.com users to SQL and SSAS users can be set up in the UserMapping table in the SQL database (should contain User, SQLUser, SSASUser varchar columns). For more information please visit: https://support.poweronbi.com/portal/kb/articles/custom-user-mapping-when-using-powerbi-com-service 

Initial Refresh

Only affects how the data is retrieved when the visual is first rendered on the report page. If turned off the matrix will use the PowerBI cache –if available – to retrieve data. If turned on, the matrix will reach out to the SSAS model to fetch the most up to date data. The background is that PowerBI caches cell data for faster report rendering. It is mostly utilized when you have multiple report pages, and you navigate between them. Therefore, by default the BarChart has this setting enabled, so that when the visual rendered it will always connect to the underlying SSAS model to get data, and it will not use the cache. This is for concurrent workloads, as it might happen that after you made changes someone else also did, and if you navigated away and back, you might not see the change that was committed by the other user. 

Commenting

Turning ON the commenting property allows the users to add comments to each data point.

Double clicking on a point brings up a small window where you can edit the value and the corresponding comment. To save the data and comment, press the [Save changes] button.

Hover on a data point the comment will be shown in the tooltip.

To refresh the comments on the visual press the [Reload Data] button on the top ribbon of the visual.

The commenting feature of Barchart can work together with a DEMx visual. Comments written on the Barchart will be visible on the same dimension total members on DEMx and vice versa. (Saved in the same comments table, with the same JSON structure)

Color Settings

Chart style is set to Line Chart:

Every measure on the chart will be appearing under the [Color settings] and you can change their line colors with the regarding Color picker. If you have only one measure it will change only the color of the line but if there are multiple ones it effects the data points as well. When you have only one measure on the chart you can change the color of the data points by the values of Category Data (X axis).

Chart style is set to Bar Chart:

You can set the color of each bar by the values of Category Data or by measures if you have multiple ones. When you have only one measure and changing its color, it will affect only the color of the legend.

Chart Options

Dragging Precision

Only available when the Percent View property is turned OFF. With this property, you can change the accuracy of moving a data point. Recommended to set it manually based on the scale of your Y axis.

Pay attention on the value that you give as a Dragging Precision, because moving the data points, the value of the measure can be the multiple of the Dragging Precision only. It is not a step size.

E.g.: you have a data point where the value is 1.75 and this property is set to 3. When you try to change the value 1.75, it can jump only to 0, 3, 6, 9... etc. You will not get values like 4.75, 7.45 etc. (if you want to get these values you should apply a smaller Dragging Precision)

Percent Dragging Precision

Only available when the Percent View property is turned ON. With this property, you can change the accuracy of moving a data point. The value of this field is in percentage, e.g.: 10 equals to 10%, 0.1 equals to 0.1%.

In this case it is also not a step size, works like the normal Dragging Precision.

Chart Style

You can switch the style of the chart between Line chart and Bar chart views.

Line chart:

You can drag the data points and change their values by moving them.

Bar chart:

You can drag the small black rectangle at the end of each bar to change the values.

Legend Position

You can change the position of the displayed measures’ legends.

Top

Bottom

None

Percent View

It is turned OFF (by default):

The chart is in the normal view and all data points show the exact value of the current context. By dragging a data point you can set a new value based on the setup Dragging precision.

Turned ON:

The data points will show the percentage ratio of the total value. Modifying one data point will affect the other ones as well by keeping the total value of the measure (e.g.: you have two data points, and their distribution is 50-50%. When you change one of them to 40% the other one will be changed to 60% automatically.).

When the BarChart is in Percent view, the data points can be locked. Locked points will not be affected by changing other points on the chart. To lock a data point you can use CTRL + Left click combination on it or open the context menu and check the Lock box. (Locked points’ color become darker.)

Lock function works only if Percent View is enabled.

Transparent Background

Turning it ON makes the chart area transparent so, its color will match with the background color of the visual.

OFF

ON

Line Styles

Only available when the Chart style is set to Line Chart. You can change the line style of any measure or series element by selecting one from the dropdown menu.

Importance of SmartFilter Helper Visual

It is a general functionality in PowerBI that when you are using slicers, page-report or visual level filters the visual itself is not aware that it is receiving a filtered dataset. For the write-back to work properly if you are using any of these filtering capable elements, you need place SmartFilter helper visuals in your report, so that the BarChart will be aware of these, and the write-back engine will take these into consideration when it composes the SQL statements. Without it, slicer selection will be left out of the tuple that is being sent back to the service, it will be missing from the SQL statement, hence the saved result will be wrong. It is important that you use the visual that is shipped with the setup kit, as you can find a visual with the same name in the Microsoft Visual Store but that does not have this functionality.

This visual is invisible to the end users as it has no background no borders, and the values are not shown.

General rules:

• If both the field that is used in a slicer and a field used in your BarChart category or series data collection are originated from the same dimension (table), you do not need to use SmartFilter – as the field in the category or series data is deterministic.
• If the field of the slicer can be found in BarChart you do not need to use SmartFilter.
• If you use a field that is not used in your BarChart and originated from a different dimension, put a SmartFilter in your report with the same field. Example: you have a slicer on Customer Group Name, but the customer table is not used at all in the BarChart, add a SmartFilter helper visual to your report and place the same field in it – in this case the Customer Group Name
• If you have more slicers which fields are originated from the same table, put a SmartFilter in your report and use the lowest granularity field in it. Example: you have slicers on Size and Brand which are both columns of the Product table, add a SmartFilter helper to your report and put the ProductId in it.

Performance Optimization

In case of large fact tables reprocessing in-memory SSAS models can take some time. The write-back service can determine which portion of your data should be processed. For that you can implement special partitioning. Please refer to the following article in our Knowledge Base for examples, walk throughs and tips:

• https://support.poweronbi.com/portal/kb/articles/performance-tips-for-direct-query-mode
• https://support.poweronbi.com/portal/kb/articles/performance-optimization-tips
• https://support.poweronbi.com/portal/kb/articles/implement-ssas-partitioning-for-improved-write-back-performance
• https://support.poweronbi.com/portal/kb/articles/dedicated-table-for-write-back-using-ssas-partitioning

Models with Calculated Tables and Columns

As described in the Limitations chapter, you cannot write-back on calculated tables and columns. You can have these types of objects inside your SSAS model, but by default the write-back service always do a model metadata check before saving changes, and if it finds such structures will prohibit the process. If you are certain that you are not trying to write-back on measures that are using calculated columns, and you are not using calculated tables in your chart (neither on category nor series), also these objects are not used in any kind of filters (slicers, visual level filters, etc.), you can turn off this strict checking by using Advanced Configurations.

Advanced Configuration

You can control the behavior of the write-back service for each SSAS model. For this you need to create a dedicated SQL table in a strict format. It is important to know, that if you have multiple data sources in your SSAS model, you need to tell the write-back engine that in which data source should it look for this setting table. The way to do it is to add the write-back – case sensitive string in the name of your data source where you will be creating this helper table. So, for instance if you have three data sources defined  (ds_A, ds_B and ds_C) for three different SQL databases (Database_1, Database_2, Database_3) and you want to create this settings table in Database_2 – which is used in ds_B, then rename it to be ds_B_WriteBack for instance.

The structure of the table is:

CREATE TABLE [dbo].[AdvancedSettings]

(

    [Setting] [varchar](255) NOT NULL,

    [Value] [sql_variant] NULL,

CONSTRAINT [pk_AdvancedSettings] PRIMARY KEY CLUSTERED( [Setting] ASC )

)

You cannot rename the table or use other schema then dbo at the moment. Be aware, that the [Value] column is an sql_variant datatype, so different settings will have different types (like bit, varchar, numeric, etc.) It means that when you try to copy this table by the Generate Script tasks in SQL Management Studio it will not recognize the correct data types and might result in a non-compatible setting. If you need to move/copy this table – or the entire database – always re-create this helper table with the appropriate data types by using the correct insert statements.

Below you find the most important settings, for the complete list please refer to this article:

https://support.poweronbi.com/portal/kb/articles/advanced-configuration

Important settings:

1. Turn strict metadata OFF checking in case of you have calculated tables, relationships by default the service will prohibit the operation. If you turn this OFF, the operation will not be blocked. Be careful that you will not use unsupported objects during the write-back (for example you do not use calculated column as a row / column member in your matrix or as slicers, etc.)

2. Enable tracing: if you enable this a Trace.Txt file be created in the folder at the location of the webservice which will log all operations during write-back.

3. Save write-back history: if you enable this, a dbo.WriteBackHistory table will be created in your database (in case of multiple data sources, it will use the connection with the WriteBack tag) that will contain the following information for each cell modification:
   1. ChangeDate – time of change
   2. ChangedBy – username who committed the change
   3. Tuple – the cell’s tuple or coordinate
   4. OldValue – original value of the cell
   5. NewValue the updated value of the cell

For point 1:

INSERT [dbo].[AdvancedSettings] ([Setting], [Value]) VALUES (N'SkipInvalidMeasures', CAST(1 as bit)) --Don't throw error on non-parsable DAX expressions

INSERT [dbo].[AdvancedSettings] ([Setting], [Value]) VALUES (N'SkipCalculatedRelationships', CAST(1 as bit)) --Don't throw error if calculated column used in relation

For point 2:

INSERT [dbo].[AdvancedSettings] ([Setting], [Value]) VALUES (N'EnableTracing', CAST(1 as bit)) -- creates Trace.txt inside the web service's folder and log all operations

For point 3:

INSERT [dbo].[AdvancedSettings] ([Setting], [Value]) VALUES (N'SaveWritebackHistory', CAST(1 as bit)) -- creates dbo.WritebackHistory table and logs cell changes