Retail Sales Reporting: Gateway Failure and Dynamic Row-Level Security
Northwind Retail operates 38 stores and reports daily sales in Power BI. The semantic model combines an on-premises SQL Server sales table with 14 Excel workbooks that store managers upload to a SharePoint library. Scheduled refresh at 02:00 fails three or four times per week; the error mentions that the gateway is offline. The gateway is installed on a financial analyst’s laptop, which sleeps overnight. Regional managers currently open a report that shows every store because row-level security has not been configured. Store managers need to see only their own store; regional managers need their region; the CFO needs all stores. The semantic model has one flat Sales table with store name, region, and 40 measures. The team wants to stop maintaining 38 separate roles and reduce refresh time before month-end.
1. Diagnose the refresh failure and propose a gateway and data-preparation architecture that prevents recurrence.
Read the worked answer
The error is consistent with a personal gateway on a laptop that sleeps. The 02:00 refresh has no running gateway to reach the on-premises SQL Server. This is not a dataset bug; it is an availability and ownership problem.
Fix:
- Install an enterprise gateway on an always-on server, ideally in a two-node cluster for high availability.
- Run the gateway service under a service account, not a personal account, and add the SQL Server data source with stored credentials.
- Move the 14 Excel workbooks out of the refresh path. Use a dataflow to extract and standardize them from the SharePoint library into a staging table, then refresh the semantic model from the dataflow and SQL. This removes 14 fragile sources from the 02:00 refresh.
- Schedule the model refresh after the dataflow completes, and configure refresh failure notifications to the platform team, not the analyst.
Judgment call: a personal gateway is acceptable for a one-person prototype, but not for a report used by 38 stores. If the SQL Server is reached through a VPN that drops overnight, the enterprise gateway server must sit on a network with stable connectivity. Also set the dataflow to refresh incrementally where possible; 14 full Excel extracts every night is unnecessary if the files are append-only.
2. Design row-level security so store managers see one store, regional managers see their region, and the CFO sees all stores, without 38 separate roles.
Read the worked answer
Use dynamic RLS with a security bridge table, not one role per store.
Create a SecurityUsers table with columns UserEmail, StoreCode, and Region. Each row maps a user to the scope they can see. Create a Store dimension with StoreCode and Region, and relate it to Sales. In the model, create one role, for example Store and Region Access, with a filter on SecurityUsers:
[UserEmail] = USERPRINCIPALNAME()
Because SecurityUsers relates to Store, the filter propagates to Sales. A store manager has one row for their store; a regional manager has rows for every store in the region, or a region-level row if the relationship is modeled at region level. The CFO gets a separate role with no filter, or is added to a role that grants all rows.
Important details:
- Test with View as role for a store manager, a regional manager, and the CFO.
USERPRINCIPALNAME()returns the signed-in UPN, so guest and B2B accounts need mapping. - If the model uses import mode, the security table must refresh with the model; stale security rows are a real risk.
- Do not rely on report-level filters for security. A user who can edit the report can remove them.
- Keep the security table narrow and auditable; a row per user-store pair is acceptable at 38 stores, but a row per user-region is easier to maintain.
3. What model changes would you make to reduce refresh time and measure complexity, and what would you not change?
Read the worked answer
Move from one flat Sales table to a star schema: a Sales fact at the transaction or daily-store grain, plus Store, Date, Product, and Region dimensions. Remove redundant text columns such as store name and region from the fact table. This reduces memory and improves compression, and it makes the RLS relationship cleaner.
Use incremental refresh on the Sales fact, with a parameterized RangeStart/RangeEnd on the date column. Keep a short rolling window for recent changes and archive older partitions. For the Excel workbooks, land them in a dataflow staging table and only load changed files where the source supports it; otherwise, consolidate the 14 files into one standardized table so the model has one source.
What I would not change: the 40 measures are probably not the refresh bottleneck. Measures are calculated at query time, so deleting or merging them does not reduce refresh duration unless they are calculated columns or calculated tables. Keep the business measures, but move row-level calculations to measures where possible and document them.
If month-end refresh still exceeds the window, add an aggregation table at store-day grain for historical reporting, and keep the detail table for drill-through. Do not add aggregations before fixing the gateway and the 14 Excel sources; those are the immediate failures.