Chapter 6 · Measures

Advanced Patterns

Two problems turn a simple sales model into a real P&L model: more than one currency, and users who may only see part of the business. Each one is solved once, low in the measure architecture, so every P&L line inherits it.

1. Local-currency conversion

Adventure Works reports in US dollars, and the warehouse stores every amount in USD. But each order was placed in a currency (USD, AUD, CAD, GBP, EUR, or before mid-2011 DEM and FRF), and a country manager wants to see their orders in that currency. Two pieces of data make it possible:

• Sales[LocalCurrencyRate]: local units per US dollar on the order date. The source query takes it from FactCurrencyRate.AverageRate, which is stored the other way round (USD per local unit), so the query divides: 1.0 / r.AverageRate. Every one of the 121,253 order lines has a rate.
• The Currency dimension is the order's currency. Selecting AUD both filters to AUD orders and asks for conversion.

The rule: if exactly one currency other than USD is selected, convert row by row; otherwise show USD.

_Show Local Currency =
HASONEVALUE ( 'Currency'[CurrencyKey] )
    && NOT SELECTEDVALUE ( 'Currency'[IsReportingCurrency] )

_Local Gross Sales =
IF (
    [_Show Local Currency],
    SUMX ( Sales, Sales[GrossSales] * Sales[LocalCurrencyRate] ),   -- rate differs per row
    [_Gross Sales Base]                                              -- plain SUM, fast
)

With AUD selected, Net Sales is AUD 19,469,882 for 14,701 order lines worth USD 10,646,323. The same conversion in SQL, SUM(SalesAmount / AverageRate) over those orders, gives the same number to the cent.

A version that looks right but isn't

It is tempting to convert the total once: USD Net Sales × the average rate of the selected rows. It is faster and shorter, and for AUD it returns 19,709,151: AUD 239,269 (1.2%) too much. The rate changed every day, and more was sold on some days than on others, so the plain average of the daily rates isn't the rate the business actually earned. Nobody in finance can reproduce that number. Iterate the rows, and only when conversion is needed: with no currency selected, the measure is a plain SUM.

2. Row-level security

Sales reps may see only their own territory, and the three sales managers their whole territory group. The hidden, disconnected User Access table is built in SQL from DimEmployee: a rep's e-mail maps to their territory, and a manager's e-mail to every territory in their group (27 rows for 17 people). One role filters the Sales Territory dimension:

role 'Territory Access'
	modelPermission: read

	tablePermission 'Sales Territory' =
			'Sales Territory'[SalesTerritoryKey]
			    IN CALCULATETABLE (
			        VALUES ( 'User Access'[SalesTerritoryKey] ),
			        'User Access'[UserEmail] = USERPRINCIPALNAME ()
			    )

	tablePermission 'User Access' = 'User Access'[UserEmail] = USERPRINCIPALNAME ()

Because Sales Territory filters Sales, one filter on the dimension secures the fact and every measure built on it. A user with no row in User Access sees nothing, which is the safe default. Adding a user is a row in a table, not a model change.

What the role gives three of the mapped users, for FY2013 net sales:

• Test with "View as" in Desktop (Modeling › View as, tick the role and enter another user), and with a real account in the service, before every release that touches the role or the dimension. Connected as yourself with the role applied, you should see nothing, because your e-mail isn't in the table.
• Every fact must be reachable from the secured dimension. A new fact without a territory key would be visible to everyone; add it to the checklist in Chapter 9.
• Totals are secured too. Amy sees a total of three territories, not the company.