.. _microsoft-power-bi_magicbook-consume: ===================== Microsoft Power BI ===================== Connecting Power BI to AgileData Consume ============================================== This MagicBook outlines how to connect PowerBI to the Consume area of your AgileData tenancy. By following this MagicBook you will be able to use PowerBI to query and visualise the Consume data which is available to you in AgileData. The data stored within AgileAdata.io is physically stored in a database called Google BigQuery. PowerBI can natively connect to the Google BigQuery database with a few simple steps. This MagicBook describes these steps. Pre Requisites ============================================== You will need to have access to the following PowerBI components: * PowerBI Desktop * PowerBI Service This MagicBook assumes you have already installed PowerBI Desktop. It also assumes you and have signed up for the PowerBI service and have a valid login for this service. Connection Steps ============================================== The first step is to connect PowerBI Desktop to the AgileData Consume view. The second step is to publish this connection to PowerBI service. Connect PowerBI to AgileData Consume View ----------------------------------------------- When you have opened PowerBI Desktop you should see the "Get Data from another source" link: .. image:: https://storage.googleapis.com/docs-agiledata-io/product-guild/consume/lastmile-viz/microsoft-powerbi/1.png :width: 800 If the add data option is not visible, using the top menu go to "File > Get data > Get data to get started" menu option: .. image:: https://storage.googleapis.com/docs-agiledata-io/product-guild/consume/lastmile-viz/microsoft-powerbi/2.png :width: 800 Once the "Get Data" popup appears, search for "bigquery". Then select "Google BigQuery" from the list on the right and click the "Connect" button: .. image:: https://storage.googleapis.com/docs-agiledata-io/product-guild/consume/lastmile-viz/microsoft-powerbi/3.png :width: 800 If you have not signed in previously you will be promtped with the sign in option. Click the "Sign In" button: Once you have signed-in you will not need to do it again. .. image:: https://storage.googleapis.com/docs-agiledata-io/product-guild/consume/lastmile-viz/microsoft-powerbi/4.png :width: 800 You will be prompted for your username/email address and your password. Enter those details: .. image:: https://storage.googleapis.com/docs-agiledata-io/product-guild/consume/lastmile-viz/microsoft-powerbi/5.png :width: 800 You will be asked to confirm the connection, click the "Allow" button: .. image:: https://storage.googleapis.com/docs-agiledata-io/product-guild/consume/lastmile-viz/microsoft-powerbi/6.png :width: 800 You will receive confirmaiton you have signedin, click Connect: .. image:: https://storage.googleapis.com/docs-agiledata-io/product-guild/consume/lastmile-viz/microsoft-powerbi/7.png :width: 800 You will be shown a list of AgileData Tenancy's you have access to: .. image:: https://storage.googleapis.com/docs-agiledata-io/product-guild/consume/lastmile-viz/microsoft-powerbi/8.png :width: 800 Expand your AgileData Tenancy by clicking on the ">" icon to the left of the Tenancy. Then expand the Consume area using the same icon, and you will see a list of Consume Views you have permission to access: .. image:: https://storage.googleapis.com/docs-agiledata-io/product-guild/consume/lastmile-viz/microsoft-powerbi/9.png :width: 800 Tick the selection box for the Consume View you want to connect PowerBI to. Then click the "Load" button: MagicTip - A preview of the data in the Consume View is available on the right hand side if you click the Consume View. .. image:: https://storage.googleapis.com/docs-agiledata-io/product-guild/consume/lastmile-viz/microsoft-powerbi/10.png :width: 800 Select the "DirectQuery" option from the list of options provided and click the "ok" button: This option means PowerBI reports will always be refreshed with the latest data from AgileData everytime a user accesses the PowerBI report and therefore will always be upto date. .. image:: https://storage.googleapis.com/docs-agiledata-io/product-guild/consume/lastmile-viz/microsoft-powerbi/11.png :width: 800 The fields in the Consume view are now displayed on far right: .. image:: https://storage.googleapis.com/docs-agiledata-io/product-guild/consume/lastmile-viz/microsoft-powerbi/12.png :width: 800 If you want to see the Consume view in the Power BI model screen click model icon on the left: .. image:: https://storage.googleapis.com/docs-agiledata-io/product-guild/consume/lastmile-viz/microsoft-powerbi/13.png :width: 800 You can now create a powerbi report based on this data, using your usual report create approach. Publish PowerBI Connection to PowerBI Service ------------------------------------------------ If you want other PowerBI report authors to be able to use this connection and create new reports based on the selected AgileData Consume view you will need to publish the connection to the PowerBI service. The first thing we need to do is to login to the PowerBi Service from within the PowerBI Desktop. To do this click the "Signin" link at the top right of the s cfreen. .. image:: https://storage.googleapis.com/docs-agiledata-io/product-guild/consume/lastmile-viz/microsoft-powerbi/14.png :width: 800 A pop up screen will appear asking for your email address. This is the email address you use for Microsoft PowerBI not the one you use for AgileData (but they might be the same) .. image:: https://storage.googleapis.com/docs-agiledata-io/product-guild/consume/lastmile-viz/microsoft-powerbi/15.png :width: 800 Enter your email and click the "Continue" button. A Password prompt will appear, enter your PowerBI password. If you succesfully login you will be returned to the PowerBI Desktop screen and in the top right of the screen instead of seeing "Signin" you will see your PowerBI user name. .. image:: https://storage.googleapis.com/docs-agiledata-io/product-guild/consume/lastmile-viz/microsoft-powerbi/16.png :width: 800 Click the "Publish" option on the menu ribbon. A pop up screen will show the PowerBI workspaces you have available to you. Select the relevant workspace you want to save this connection into and the click the "Select" button. .. image:: https://storage.googleapis.com/docs-agiledata-io/product-guild/consume/lastmile-viz/microsoft-powerbi/17.png :width: 800 You will see a message saying PowerBI is publishing. .. image:: https://storage.googleapis.com/docs-agiledata-io/product-guild/consume/lastmile-viz/microsoft-powerbi/18.png :width: 800 And then you will see a success message. Click on the "Got it" button .. image:: https://storage.googleapis.com/docs-agiledata-io/product-guild/consume/lastmile-viz/microsoft-powerbi/19.png :width: 800 You can now open PowerBI Service in your browser https://app.powerbi.com And then signin using your PowerBI email and password. You can leave PowerBI Desktop open if you want to create another connection or close it if you have finished with it. Click on the "Datasets" menu option on the left. .. image:: https://storage.googleapis.com/docs-agiledata-io/product-guild/consume/lastmile-viz/microsoft-powerbi/20.png :width: 800 You will see a list of connections to data that are available to you. Click on the Connection you just published from the PowerBI Desktop: .. image:: https://storage.googleapis.com/docs-agiledata-io/product-guild/consume/lastmile-viz/microsoft-powerbi/21.png :width: 800 Click on the "Create from scratch" button: .. image:: https://storage.googleapis.com/docs-agiledata-io/product-guild/consume/lastmile-viz/microsoft-powerbi/22.png :width: 800 You can now start using data in the report using your typical report creation approach.. .. image:: https://storage.googleapis.com/docs-agiledata-io/product-guild/consume/lastmile-viz/microsoft-powerbi/23.png :width: 800 Frequently Asked Questions ============================ Can we create a connection to a Consume view without using PowerBI Desktop? No, at the present time only PowerBI Desktop is able to create a new connection to Google Big Query. But once the connection is published to the PowerBI Service then it can be used without using the PowerBI Desktop. Do we need the Power BI Gateway to connect to our data? ??? Can I include multiple Consume Views in a single PowerBI connection? ???