Excel
Excel can work on the model open in the studio, live, as you or as a role: as a PivotTable, or with OpenBI’s own pane and formulas. Tables, matrices and financial statements also save as Excel files.
Analyze in Excel (Windows)
Choose Home › Analyze in Excel. Excel opens a new workbook with a PivotTable connected to the model, live, as you: drag the model’s fields into it as you would with a Power BI model. Excel talks to the OpenBI engine running for the project on this computer.
If Excel can’t be opened that way, the studio opens a connection file instead and Excel asks how to show it: choose PivotTable. The file is kept in Documents\My Data Sources as “<model> (OpenBI Studio).odc”, so Excel also lists it under Data › Existing Connections.
OpenBI’s Excel pane
Home › Connect Excel (on a Mac, Analyze in Excel) opens Excel with OpenBI’s own pane: the model’s fields, tables the engine computes and refreshes, and three functions for your formulas:
OPENBI.VALUE- A measure’s value, filtered by up to six columns and their values, as
CUBEVALUEgives one. OPENBI.TABLE- An OAX query’s rows, spilled into the sheet with their header. OAX, OpenBI’s expression language, is compatible with DAX, so a query written for Power BI works here as it is.
OPENBI.MEMBERS- A column’s values in its sort order, spilled down.
=OPENBI.VALUE("[Liters sold]", "Products[Product]", "Pils")The pane has Insert, Refresh and Refresh all. The first time:
- The studio asks your permission to trust its own local certificate, which Excel needs to reach it. Windows then shows a security warning: choose Yes. A Mac asks your password.
- The pane is added to Excel, and Excel opens a new workbook, kept in
Documents\OpenBI\Excel. - Each pane asks once whether Excel may read the project: Allow or Don’t allow.
The pane is served by the studio on this computer only. To take back what you allowed, choose Disconnect Excel in the agent window (Agent on the Home tab).
On a Mac
Analyze in Excel is the pane: there is no PivotTable route. The first time, the studio opens two Finder windows: drag OpenBI’s wef folder into Excel’s Documents folder, once, and choose Done.
Excel as a role
To check what a role’s members see in Excel, choose Excel as this in the Security view’s See as (see Roles), or set View as in the Report view and use the page header’s Analyze in Excel link.
On a live connection
For a project connected live, Excel connects to the server itself, as you, and Power BI, Fabric and Azure Analysis Services ask your Microsoft sign-in. The project’s own measures are OpenBI’s, so they aren’t in Excel (see Live connections).
Excel files
- A table’s or a matrix’s menu on a report page: Excel file (.xlsx), its values with their formats, a matrix’s levels as an outline.
- A financial statement’s menu: Excel file (.xlsx), the statement as shown, its rows grouped as an outline, with its totals and its columns’ formulas as Excel formulas.