Posts

Understanding Writeback Target Allocation

I’m working on architecting a financial planning solution powered by Analysis Services Multidimensional. One thing that might not be obvious is how Multidimensional selects the target of writeback allocation. In this case, planning will be done at Customer and Product level. With the default equal allocation when writing at the customer level, it might appear that writeback doesn’t work correctly. You’d expect that only the cells that contribute to the aggregated value (10 in the screenshot below) will be affected by writeback. However, if the Customer and Product entities are in different dimensions, writeback will affect all products!

The reason behind this becomes obvious if you right-click the pivot table, and from its options enable “Shows rows with no data”. Then, you’ll see all products appearing with each customer (customers are crossjoned with products). Recall that by default, the pivot table uses NON EMPTY in MDX query to exclude combinations that don’t exist in the cube. But writeback makes no such assumptions. The reason for this is that the writeback cell is empty, then there is nowhere the writeback value will be allocated to. If the Customer and Product entities are in the same dimension, then the default equal allocation will write to all children of the affected parent, irrespective if their values contribute to its aggregated cell.

So, writeback is not the same as drilling through a cell. Now that you know how it works you can use different allocation settings to achieve the behavior you want. For example, you can choose a weighted allocation with the following expression to avoid writing back to empty values:

iif(Measures.CurrentMember = 0, null, Measures.CurrentMember)

Trend Lines in Power BI Charts

Recently, Power BI charts introduced trend lines. However, they require numeric or date values on X-axis, which must have a continuous type. In fact, if you use a text field for the X-axis, a warning indicator will be displayed in the top left corner of the chart to warn you that non-numeric values are used.

072916_0110_TrendLinesi1.png

This requirement presents issues if the report is connected to a Multidimensional cube because by default all attributes are text-based. As a workaround, in the Multidimensional project set the ValueColumn property of the attribute to a column in the underlying table of a numeric or date data type, and deploy the cube.

072916_0110_TrendLinesi2.png

Back to Power BI Desktop, bind the corresponding .Value field to the X-axis.

072916_0110_TrendLinesi3.png