Skip to main content
This feature is currently in beta. Omni turns it on per organization; ask your Omni contact.
You can use Excel PivotTables to analyze the data in an Omni model. You connect in the same way as you connect to an Analysis Services cube, and each PivotTable gets its data from Omni with live queries. Excel shows each Omni model you can read as a database, each topic as a cube, and each field as a PivotTable field. Each query that Excel sends runs as you, with your permissions, access filters, and user attributes.

Requirements

To connect Excel to Omni, you need:
  • Excel for Windows. The connection is tested with Microsoft 365 Excel on Windows. Excel for Mac doesn’t include the Analysis Services connector.
  • The beta enabled for your organization. Ask your Omni contact.
  • A Personal Access Token (PAT). Create one in Settings > API access > Personal tokens. An Organization Admin must first enable personal tokens, and you need Restricted Querier access or more. Refer to Creating API tokens. Organization API keys and OAuth tokens aren’t accepted.

Connect Excel to Omni

1

Open the Analysis Services connection

In Excel, click Data > Get Data > From Database > From Analysis Services.
2

Enter the server name

In Server name, enter your Omni address followed by /api/v1/xmla. For example:
3

Enter your credentials

In Log on credentials, select Use the following User Name and Password:
  • User Name - Any value. Your email address is fine.
  • Password - Your PAT.
Click Next.
4

Select the model and topic

Select the database, which is the Omni model. Excel shows each model’s name and the start of its ID, for example Sales (3f2a9c). Then select the cube, which is the topic.Click Finish.
5

Create the PivotTable

Select PivotTable Report and the location for the PivotTable, then click OK.
Excel keeps the connection in the workbook. If you select Save password in file, the workbook also keeps your PAT, and anyone who has the workbook can query Omni as you until you disable or delete the PAT. Don’t save the password in workbooks that you share.

Use the PivotTable

  • Add fields as you do with any cube.
  • Show Details - Double-click a value to see the rows behind it. Omni shows the drill fields that the model defines for that measure. By default, Excel gets the first 1,000 rows.
  • Refresh runs the queries again. Omni’s query cache applies, as it does in Omni.

Dates

Each date field shows in two forms:
  • A Year > Quarter > Month > Date hierarchy, with the name of the date field (for example, Order Date). When you add it to Rows, Excel shows years. To show quarters or months, click the + next to a year, or right-click a year and select Expand/Collapse > Expand to. To filter, open the field’s filter list, then expand a year to its quarters, months, and dates.
  • One field for each part of the date, for example Order Date Year, Order Date Quarter, Order Date Month, and Order Date Date. Excel shows these fields in the More Fields folder of the view. Use one of these fields to show a single part of the date, such as all quarters.
Excel shows a date in your regional short date format, for example 1/16/2025 in English (United States) or 16/01/2025 in English (United Kingdom). For some regional formats, Excel shows the date as 2025-01-16. Excel shows a quarter as the year and month that it starts, for example 2025-04 for the second quarter of 2025, and a month as 2025-05. Date Filters (for example, This Quarter, Last Month, or Between) are available only on the field for the full date, for example Order Date Date. On the hierarchy and on the other date fields, use the filter list or label filters.

Slow queries

Excel waits up to approximately three minutes for a query, then sends it again one time. If the query is still not complete, Excel shows this message:
The query continues to run in your database. Refresh the PivotTable to get the result when it’s complete.

Known limitations

PivotTable features that aren’t supported

Omni doesn’t support these PivotTable features yet, and Excel shows Unsupported MDX: ... when you use one. Support for them is in development:
  • MDX calculated measures, MDX calculated members, and sets that you define in Excel
  • Top 10 filters by percent or sum, a top N within each item of another field, and top N or value filters on more than one field
  • Label filters that compare text alphabetically (Greater Than, Between), and label filters on number or true/false fields
  • Show items with no data on nested fields, or on a field when a field from another view filters the PivotTable
  • Include filtered items in totals

Other limitations

  • Timelines aren’t available. Omni models don’t have a date table.
  • Fiscal and custom calendars - Dates on a fiscal or custom calendar don’t get the Year > Quarter > Month > Date hierarchy. Their timeframe fields work as usual.
  • Size - A query that returns more than 50,000 rows or 200 columns stops with a message that says the result has more than that number of rows or columns. Add a filter, or remove or move a field.

Troubleshooting

Next steps