> ## Documentation Index > Fetch the complete documentation index at: https://docs.activeviam.com/llms.txt > Use this file to discover all available pages before exploring further. # Atoti Power BI Adapter The Atoti Power BI Adapter connects Power BI to an Atoti application so that cube data can be visualized directly in Power BI. The adapter is built on the [BI Adapter SQL endpoint](https://docs.activeviam.com/engine/java-sdk/6.2/user_guide/querying/front_ends/sql_endpoint.md), which lets a BI tool query an Atoti cube using SQL. It combines Power BI's visualization capabilities with Atoti's multidimensional data model. Understanding how Power BI interprets cube data and behaves in different modes ensures accurate analysis. > [!NOTE] > > This feature requires a license that enables the `bi-adapter` component. ## Prerequisites * **Atoti application**: An active Atoti application with a cube available. * **Atoti Power BI Adapter**: [Activated on the server](https://docs.activeviam.com/engine/java-sdk/6.2/user_guide/querying/front_ends/power_bi/power_bi_adapter_how_to.md#how-to-activate-the-atoti-power-bi-adapter). * **Power BI**: Power BI Desktop. * **PostgreSQL connector**: Power BI Desktop includes a built-in PostgreSQL database connector, so no driver installation is needed. ## How is an Atoti cube represented in Power BI? Once connected through the Power BI Adapter, the cube appears in Power BI's Data pane. When Atoti exposes a cube to BI tools, it builds a flat table whose columns are the cube's levels and measures. Not every element of the cube is exposed: the sections below describe which ones are included and which are excluded. ### Levels and measures exposed by Atoti The visibility of a level or a measure in an Atoti cube is respected. For levels, this means the level's hierarchy is marked as visible; for measures, the measure itself has a visible flag. Elements marked as hidden in the cube configuration are excluded. There are special cases: * The `AllMember` level is never included. * The measure hierarchy itself is never included. ### Analysis hierarchies Analysis hierarchies whose members are **user-defined** (i.e., hardcoded within the project rather than derived from data in a store) are exposed to BI tools as strings. Analysis hierarchies whose members are derived from a store field are exposed normally. To link an analysis hierarchy to one or more store fields, its description must be customized, as in the example below: ```java theme={"languages":{"custom":["/engine/python-sdk/6.2/languages/pycon.tmLanguage.json"]}} final IActivePivotInstanceDescription cubeDescription = StartBuilding.cube("cube") .withDimension("dimension") .withAnalysisHierarchy("analysisHierarchy", "pluginKey") .withCustomizations( desc -> desc.setLevels(List.of(new AxisLevelDescription("levelName", "storeField")))) .build(); return cubeDescription; ``` ### Fields classification by Power BI *Field* designates a column in a discovered SQL table. In this case, this refers to both exposed cube levels and measures. Power BI classifies discovered fields as numeric and non-numeric. A numeric field is represented by a sigma symbol next to its name in the Data pane. Power BI automatically assigns a `Sum` aggregation to numeric fields. This aggregation can be changed by clicking on the field in the Data pane and selecting an aggregation among those suggested in *Summarization*: ![Change summarization](https://mintcdn.com/activeviam/A7IXC6LWL2iNLCTE/engine/java-sdk/6.2/assets/user_guide/power_bi_adapter/summarization.png?fit=max\&auto=format\&n=A7IXC6LWL2iNLCTE\&q=85\&s=6da1ac4fb1545789d659ad978729ed43) ### How to reproduce the cube's structure Power BI is unaware of the cube's structure behind the SQL table. The `.pbit` file [generated by Atoti](https://docs.activeviam.com/engine/java-sdk/6.2/user_guide/querying/front_ends/power_bi/power_bi_adapter_how_to.md#connect-using-a-power-bi-template-file) declares Power BI hierarchies that mirror the cube's multilevel hierarchies, and hides the levels that belong to them. The following steps adjust that structure in Power BI. #### Create a hierarchy 1. Right-click the field (Atoti level) in the Data pane. 2. Select `Hierarchy` and `Create Hierarchy`. ![Create a hierarchy in Power BI](https://mintcdn.com/activeviam/A7IXC6LWL2iNLCTE/engine/java-sdk/6.2/assets/user_guide/power_bi_adapter/create_hierarchy.png?fit=max\&auto=format\&n=A7IXC6LWL2iNLCTE\&q=85\&s=8926b8413980121981b03d1391c78194) #### Add a level to an existing hierarchy 1. Right-click the field (Atoti level) in the Data pane. 2. Select `Add to hierarchy` and the desired hierarchy. ![Add a level to an existing hierarchy in Power BI](https://mintcdn.com/activeviam/A7IXC6LWL2iNLCTE/engine/java-sdk/6.2/assets/user_guide/power_bi_adapter/add_to_hierarchy.png?fit=max\&auto=format\&n=A7IXC6LWL2iNLCTE\&q=85\&s=6fd6e089964eb697d3b08b5df8f5e3ac) #### Hide levels that have been added to hierarchies Once a level has been added to a hierarchy, it is visible in the Data pane both isolated and within the hierarchy. As the level should be visible only within its hierarchy, here are the steps to hide the isolated level: 1. Right-click the field that does not belong to a hierarchy in the Data pane. 2. Select `Hide`. ![Hide a field in Power BI](https://mintcdn.com/activeviam/A7IXC6LWL2iNLCTE/engine/java-sdk/6.2/assets/user_guide/power_bi_adapter/hide.png?fit=max\&auto=format\&n=A7IXC6LWL2iNLCTE\&q=85\&s=8025180ae11bab43fe6801f1dca445a1) #### Adjust the granularity of the dashboard Once a hierarchy has been created and populated, it can be used to drill down or up. This can be done by clicking on the `+` or `-` icon next to the hierarchy level in a pivot table: ![Lowest granularity level](https://mintcdn.com/activeviam/A7IXC6LWL2iNLCTE/engine/java-sdk/6.2/assets/user_guide/power_bi_adapter/drilldown.png?fit=max\&auto=format\&n=A7IXC6LWL2iNLCTE\&q=85\&s=bdbc1c677a72fe1a0014d6b9e26c975f) #### Save the structure Once the cube's structure has been reproduced manually, it is possible to save it for later Power BI sessions using the Power BI-specific `.pbit` file format. 1. Click on `File`. 2. Click on `Save as`. 3. Save the file using the `.pbit` format. ![Save pbit](https://mintcdn.com/activeviam/A7IXC6LWL2iNLCTE/engine/java-sdk/6.2/assets/user_guide/power_bi_adapter/save_pbit.png?fit=max\&auto=format\&n=A7IXC6LWL2iNLCTE\&q=85\&s=da496317aee5104135b503dfa07add2d) ## What are the Power BI modes? Power BI can connect in import mode or in DirectQuery mode. In import mode, Power BI queries all the granular data when the report is loaded, and stores it. In DirectQuery mode, queries are generated based on the need. Each of these modes has caveats, especially around re-aggregation. The `.pbit` file generated by Atoti opens in DirectQuery mode, which is the recommended mode: it lets Atoti handle the aggregations. ### How DirectQuery mode works In DirectQuery mode, Power BI sends a SQL query to the cube each time a visualization is displayed. #### Use of measures Numeric measures are automatically classified as numeric fields summarized with a `Sum` aggregation. This aggregation must not be removed to make the generated SQL queries coherent with the Atoti returned result. Atoti ignores this operator because the measure is already aggregated in the cube. Power BI however may compute the aggregation on its side, leading to an undesirable re-aggregation. > Only `Sum`, `Min`, `Max` and `Median` do not lead to undesirable re-aggregations. For example, when selecting an `Average` aggregation, Power BI queries the `Sum` as well as the `Count` and then computes the average on its side. > [!WARNING] > > It is possible to change the summarization of a field to `Don't summarize`. > This change is not recommended on Atoti measures because Power BI is unaware of Atoti aggregations. > Only numeric levels that are wrongly aggregated by Power BI should receive this change. > In import mode or when joining with other tables, Power BI may apply aggregation on its side. ### How import mode works Import mode retrieves data from the cube and stores it locally in Power BI. No live queries are sent after extraction, so data can be queried even if the cube is offline. #### Considerations * Apply filters to limit the extraction scope. * Hide unnecessary fields to reduce data volume. * Aggregation operators are applied by Power BI in this mode. * Be cautious with reaggregation: * `Mtm.SUM` can be reaggregated with `Sum` or `Average`. * Non-linear measures cannot be reaggregated. ## What are the supported data types? The following types are supported without any required action: * `String` * `Double` * `Boolean` * `Long` * `Int` * `Float` > [!WARNING] > > Arrays are not supported in the `DirectQuery` mode. > They are visible in the table's preview but cannot be used in a Power BI visualization. > Selecting such a field in a Power BI visualization leads to the following error message: > `We couldn't fold the expression to the datasource. Please try a simpler expression.` > > Arrays are supported in the `Import` mode, which comes with severe limitations on aggregations. ## Data preview Some Power BI screens allow a preview of a cube's content. This occurs when selecting the Atoti cube after connecting to the Atoti server: ![Data preview](https://mintcdn.com/activeviam/A7IXC6LWL2iNLCTE/engine/java-sdk/6.2/assets/user_guide/power_bi_adapter/preview.png?fit=max\&auto=format\&n=A7IXC6LWL2iNLCTE\&q=85\&s=a4013f35fda86536476cd7b1a801fbda) Atoti serves any query with a `LIMIT` clause and no `GROUP BY` clause, such as a data preview, as a drillthrough, whatever columns it selects. A drillthrough retrieves the stored records instead of aggregating them, so not all values are retrieved: * A level column has a value only if a store field has exactly the name of the level. Otherwise, its value is `null`. * Every measure column is `null`, except `contributors.COUNT`, which is `1` for each row. Atoti cannot reliably map a measure back to its store field: for instance, a vector store field and a measure can share a name but hold different value types, such as the sum of the vector's coordinates. ## Considerations on total aggregations A total aggregation is present in some Power BI visuals, like a table or a matrix. This total is calculated client side by Power BI using the wrapping aggregation function chosen in Power BI (`Sum` by default). > [!WARNING] > > The total aggregation is calculated on the client-side by Power BI. > No query is sent through the Power BI adapter. > As a result, the total aggregation may be wrong, as in the case of an Atoti measure not based on a linear aggregation function. If total aggregations make no sense in a specific visual, they can be manually turned off in the `Format visual` pane. ### Slicing hierarchies An important limitation of this total aggregation is with slicing hierarchies. It makes no sense, by definition, to compute a total aggregation over different slicing hierarchy members. The expected result would be the value corresponding to the slicing hierarchy's default member, but this cannot be configured in Power BI. To avoid misleading totals, disable total aggregations in the `Format visual` pane for visuals that include slicing hierarchies. > If the visual contains no slicing hierarchy members, its values are already calculated with the slicing hierarchies' default members, but grand totals might still be wrong.