Connecting Power BI ===================== Power BI reads data from the platform through Trino, the platform's SQL endpoint. You do not host Trino yourself: it is a managed service run by Datafoundation at ``https://.trino.datafoundation.eu``. This page describes what works today, what does not work yet, and what happens to your data when you use Power BI. Management summary ------------------- - **Power BI Desktop works today.** Each user installs the Trino connector and signs in with their own platform account, the same account they use for Security Center. Every query runs as that user, and the access rules from :doc:`security-center` apply. - **Sign-in with a platform account is the only supported way in.** There is no supported way yet to create service accounts, so applications and unattended processes cannot access the data. Service accounts are on the roadmap. - **Scheduled refresh in the Power BI Service is not supported yet**, because it needs such a service account. - **Power BI stores data outside the platform.** Data you import into a Power BI report is a copy. Once you publish that report to the Power BI Service, the copy is stored in Microsoft's cloud, not in your own network and not on the platform. .. _power-bi-data-location: Where your data ends up ------------------------ The platform keeps your data on sovereign infrastructure and enforces who may see it. As soon as Power BI has loaded data, that copy is no longer under the platform's control: - **Power BI Desktop, Import mode**: the data is copied into the report file (``.pbix``) on the user's computer. Anyone who receives that file sees the data, whatever their own access on the platform. - **Power BI Service**: publishing a report uploads its imported data to Microsoft's cloud, in the region of your Power BI tenant. The data is then stored there, outside your network and outside the platform. - **DirectQuery**: the data is not stored in the report, but query results still pass through the Power BI Service when the report is viewed online. The access rules in Security Center apply when Power BI *loads* the data. They do not apply to the copy afterwards. Only load what the report's audience may see, and decide up front whether storing that data in Microsoft's cloud is acceptable for your organization. What is not supported yet -------------------------- A published report that refreshes on a schedule in the Power BI Service has no user present to sign in. It needs a **service account**: a non-personal account with its own, limited access to the data, used by an on-premises data gateway. There is no supported way yet to create such an account, so this set-up is not possible today. The same holds for other applications that need to read data without a person signing in. Service accounts for applications are on the roadmap. Until then: - Use **Power BI Desktop** with per-user sign-in (see :ref:`power-bi-connector`). Sharing is then file-based, and the data location described in :ref:`power-bi-data-location` applies. - Use **Apache Superset** for shared dashboards. It connects to Trino with the signed-in user's own identity and keeps platform-level enforcement end to end. .. _power-bi-connector: Installing the Trino connector in Power BI Desktop --------------------------------------------------- Power BI Desktop connects to the platform with a custom Trino connector. You sign in with your own platform account, so every query runs as you and the rules from :doc:`security-center` apply. 1. Download the connector: :download:`trino.mez `. Check that the file is intact (see :ref:`power-bi-connector-source`). 2. Copy ``trino.mez`` to the folder ``Documents\Power BI Desktop\Custom Connectors``. Create the folder if it does not exist yet. 3. The connector is not certified by Microsoft, so Power BI must be allowed to load it. In Power BI Desktop, open **File > Options and settings > Options > Security**, and under **Data Extensions** choose **Allow any extension to load without validation or warning**. Click **OK**. .. warning:: This setting applies to *every* custom connector on the computer, not only to this one. Power BI then loads any ``.mez`` file in the ``Custom Connectors`` folder without checking it. Only put connectors there that you trust, and ask your IT department first if they manage Power BI settings centrally. The connector is not signed, so it cannot be trusted by certificate (``TrustedCertificateThumbprints``) instead. 4. Close and restart Power BI Desktop. 5. Choose **Get data > More...**, search for **Trino**, select it and click **Connect**. 6. Fill in **Host** ``.trino.datafoundation.eu`` and **Port** ``443``. Leave **User** empty. Optionally fill in **Catalog** to show only one catalog, or **SQL Query** to load the result of one query. Click **OK**. 7. Choose **Organizational account** and click **Sign in**. In the window that opens, sign in with the account you use for Security Center (see :doc:`/user-manual/introduction`). Then click **Connect**. 8. In the navigator, select the tables you need and click **Load**, or **Transform Data** to edit them first. Power BI remembers the sign-in for this host. To sign in as someone else, remove the host under **File > Options and settings > Data source settings** and connect again. .. _power-bi-connector-source: Where the connector comes from ------------------------------- ``trino.mez`` is built from open source code: a fork of the community `Power BI Trino connector `_ that adds the sign-in through the platform. .. list-table:: :widths: 30 70 * - Source - `gschuurman/PowerBITrinoConnector `_ * - Version (commit) - `755cd83 `_ * - Build - ``python3 build/build.py`` (needs only Python 3.10 or later) * - SHA256 - ``22d44473106391114c8b9ce6cfb179932c88ee987f7d0bcb97052d2388c4abd8`` The build is reproducible: building that commit gives exactly the same file. To check a downloaded file, run this in PowerShell and compare the result with the SHA256 above: .. code-block:: powershell Get-FileHash .\trino.mez -Algorithm SHA256