Power BI & Analytical Workspaces

What are analytical workspaces?

Simple explanation

Think of a car dashboard.

You don’t open the bonnet to check your speed — you glance at the dashboard. Analytical workspaces do the same thing for F&O. Instead of running reports and exporting to Excel, the production manager opens their workspace and sees live KPI tiles: units produced today, defect rate, capacity utilisation — all updated automatically.

Behind the scenes, Power BI reads from a special analytics-friendly copy of your data (the entity store), not the live transactional database. So no one’s slowing down order entry by running dashboards.

Entity store deep dive

The entity store is the data engine behind all analytical reporting in F&O.

┌──────────────────┐     Scheduled      ┌──────────────────┐
│  Transactional   │────Refresh─────────▶│   Entity Store   │
│  Database (OLTP) │                     │  (Star Schema)   │
│  normalised,     │                     │  denormalised,    │
│  write-optimised │                     │  read-optimised   │
└──────────────────┘                     └────────┬─────────┘
                                                  │
                              ┌────────────────────┼──────────────────┐
                              ▼                    ▼                  ▼
                     ┌──────────────┐    ┌──────────────┐   ┌──────────────┐
                     │  Power BI    │    │  KPI Tiles   │   │  Analytical  │
                     │  Reports     │    │  (workspace) │   │  Workspaces  │
                     └──────────────┘    └──────────────┘   └──────────────┘

Configuring entity store refresh

Setting	Detail
Location	System Administration → Setup → Entity Store
Refresh options	Manual (on-demand) or Scheduled (e.g. every 4 hours)
Incremental refresh	Only processes changed records — much faster than full refresh
Per-measurement	You can set different refresh schedules for different aggregate measurements

Scenario: Elena configures entity store for PacificForge

Elena sets up the entity store refresh schedule with CTO Gregor:

Measurement	Refresh Schedule	Reasoning
Production KPIs	Every 2 hours	Production managers need near-current data
Financial summaries	Every 6 hours	Daily financial reviews, not real-time
Sales pipeline	Every 4 hours	Sales team checks twice daily
Inventory counts	Every 1 hour	Warehouse needs fresh stock levels

“We don’t need real-time,” Gregor says. “Hourly for inventory and bi-hourly for production is enough.”

Aggregate measurements in the AOT

Aggregate measurements are defined in the Application Object Tree (AOT) and deployed to the entity store.

Component	Purpose	Example
Measurement	The top-level container	`ProdOutputMeasurement`
Measure group	A fact table with its measures	`ProdOutput` (maps to production output table)
Measures	Calculations on fact data	`SUM(QtyGood)`, `COUNT(ProdId)`, `AVG(CostAmount)`
Dimensions	Grouping/slicing axes	Date, Warehouse, Product Category, Production Pool
Attributes	Descriptive fields on dimensions	Warehouse name, category description

Vik builds an aggregate measurement

Vik Kapoor at Axion Dynamics creates an aggregate measurement for production output:

Aggregate Measurement: AxProductionOutput
├── Measure Group: ProdRouteTransOutput
│   ├── Measure: TotalGoodQty    = SUM(QtyGood)
│   ├── Measure: TotalErrorQty   = SUM(QtyError)
│   ├── Measure: TotalOrders     = COUNT(ProdId)
│   └── Measure: AvgCostPerUnit  = AVG(CostAmount)
├── Dimension: Date (SchedDate)
├── Dimension: Warehouse (InventLocationId)
├── Dimension: ProductCategory (ItemGroupId)
└── Dimension: Worker (ResponsibleWorker)

Once deployed and the entity store is refreshed, Power BI can visualise these measures sliced by any dimension combination.

Embedding Power BI in F&O

The two embedding patterns

Embedded = self-contained in F&O. Linked = connects to Power BI cloud service.
Aspect	Analytical Workspace (Embedded)	Power BI Tab (Linked)
Integration type	Tightly embedded in workspace form	Power BI report linked via configuration
Data source	Entity store (local star schema)	Any Power BI dataset (cloud)
Interaction	Filter, drill-through, KPI tiles in workspace	Full Power BI experience in a tab
Security	Inherits F&O workspace security	Power BI row-level security configured separately
Development	Power BI Desktop → .pbix deployed with AOT	Power BI service → linked by URL/ID

Building an analytical workspace form

Step	Action
1	Create a new Form with pattern `AnalyticalWorkspace`
2	Add the Power BI report resource (.pbix) to the AOT
3	Reference the report in the form’s `PowerBIReportControl`
4	Add KPI tiles — each tile maps to an aggregate measurement
5	Configure drill-through — clicking a KPI opens a detail page
6	Create a menu item and secure with a privilege
7	Refresh the entity store so data is available

Exam tip: AnalyticalWorkspace form pattern

The exam may ask about workspace form patterns:

The form must use the AnalyticalWorkspace pattern (not OperationalWorkspace)
The pattern requires specific control structures: header, KPI section, Power BI section
KPI tiles are bound to aggregate measurements — they don’t query live data
The form pattern validates at compile time — missing required sections cause a build error

KPI tiles and drill-through

KPI tiles

KPI tiles are the cards at the top of an analytical workspace:

Property	Purpose
Value	The aggregate measurement value (e.g. `SUM(QtyGood)`)
Goal	Optional target value for comparison
Status	Green/Yellow/Red based on value vs goal threshold
Trend	Arrow showing direction compared to previous period
Drill-through	Menu item that opens when the tile is clicked

Drill-through design

┌──────────────────────────────────┐
│  Analytical Workspace            │
│  ┌───────┐ ┌───────┐ ┌───────┐  │
│  │12,450 │ │ 2.1%  │ │ $1.2M │  │   ← KPI tiles
│  │Units  │ │Defect │ │ Cost  │  │
│  └───┬───┘ └───────┘ └───────┘  │
│      │                           │
│  ┌───▼───────────────────────┐   │
│  │ Power BI Report           │   │   ← Embedded visuals
│  │ (charts, tables)          │   │
│  └───────────────────────────┘   │
└──────────────────────────────────┘
       │ Click KPI tile
       ▼
┌──────────────────────────────────┐
│  Detail Page (filtered)          │
└──────────────────────────────────┘

Scenario: Elena's production workspace

Elena builds PacificForge’s production analytical workspace:

KPI Tile	Measurement	Drill-through
Units Produced	`SUM(QtyGood)` today	Production order list (filtered to today)
Defect Rate	`SUM(QtyError) / SUM(QtyTotal) * 100`	Quality order list with defect details
Capacity Utilisation	`SUM(ActualHours) / SUM(PlannedHours) * 100`	Resource utilisation breakdown
On-Time Delivery	`COUNT(OnTime) / COUNT(Total) * 100`	Late order list with delay reasons

The production manager sees four tiles, glances at the trends, and clicks “Defect Rate” to investigate a spike — all without leaving F&O.

Power BI Desktop development workflow

Step	Detail
1. Connect	Power BI Desktop → Get Data → Entity Store (or OData for development)
2. Model	Build relationships between entity store tables
3. Visualise	Create charts, tables, KPI cards for the workspace viewport
4. Filter context	Add filters responding to F&O workspace context (company, user)
5. Export .pbix	Save as .pbix file
6. Deploy	Add .pbix as AOT resource → reference in workspace form

Exam tip: Power BI filter context from F&O

The F&O workspace can pass filter context to the embedded Power BI report:

Current legal entity — report auto-filters to the user’s current company
Date range — workspace can pass a default date range
Security context — if the user only has access to certain warehouses, the report respects that

Configured in the PowerBIReportControl properties on the workspace form.

Security for analytical workspaces

Layer	Control
Workspace access	Menu item privilege — users must have the role granting access
Data access	Entity store contains cross-company data; workspace forms filter by current legal entity. Power BI RLS can add row-level filtering
Power BI RLS	For linked (cloud) reports, configure row-level security in Power BI service
Aggregate level	Workspaces show aggregate data — individual record security is less granular

Sophie asks about data leakage

Sophie Chen asks: “If the entity store has all companies’ data, doesn’t a production manager see other companies’ numbers?”

Elena explains: “Two safeguards. First, the workspace form passes the current legal entity as a filter. Second, we can configure Power BI RLS for additional row-level security.”

“The entity store contains cross-company data for efficiency, but the presentation layer always filters,” Elena adds.

Question

What is the entity store and why does F&O use it for analytics?

Click or press Enter to reveal answer

Answer

A read-optimised, star-schema database mirroring transactional data. F&O uses it to separate analytical from transactional workloads — heavy dashboards run against the entity store, not the live OLTP database. Refreshed on a schedule, not real-time.

Click to flip back

Question

What form pattern must an analytical workspace use?

Click or press Enter to reveal answer

Answer

The AnalyticalWorkspace pattern (not OperationalWorkspace). It requires: header, KPI tile area, and Power BI report control. Validates at compile time — missing sections cause build errors.

Click to flip back

Question

What are the components of an aggregate measurement?

Click or press Enter to reveal answer

Answer

1) Measurement — top-level container. 2) Measure groups — map to fact tables. 3) Measures — SUM, COUNT, AVG on fact data. 4) Dimensions — grouping axes (date, warehouse, category). 5) Attributes — descriptive fields on dimensions. Deployed to entity store.

Click to flip back

Question

How does an analytical workspace pass context to an embedded Power BI report?

Click or press Enter to reveal answer

Answer

Through the PowerBIReportControl on the workspace form. It passes current legal entity, date ranges, and security context. The embedded report auto-filters based on these parameters.

Click to flip back

Knowledge Check

Elena notices that the production KPI tiles show yesterday's numbers, not today's. What is the most likely cause?

Knowledge Check

Which form pattern must Elena use when building an analytical workspace that embeds Power BI and KPI tiles?

Knowledge Check

A production manager can see all companies' data in the analytical workspace, but should only see their own legal entity. What should Elena configure?

Next up: Excel & Electronic Reporting — Edit in Excel, ER format designer, and generating regulatory documents.