Develop API query and API page objects for Power BI

Completed

In the previous unit, you've seen how to develop page and query objects and how to expose page and query objects as web services. Power BI can then connect to the Business Central web services and import the data.

Whenever possible, you're encouraged to use API pages/queries instead of OData web services. APIs are faster at loading data in Power BI reports than OData web services. Plus, they're more flexible because they let you get data from table fields that aren't defined in a page object.

Developers can define page objects and query objects that are of the type  API. This way, they can expose data from database tables through a webhook-supported, OData v4-enabled, REST service. This type of data can't be displayed in the user interface, but is intended for building reliable integration services.

Business Central online comes available with a set of built-in APIs, which you can use to get data for the most common business entities, like customers, items, sales orders, and more. No extra work or setup is required to use these APIs as a data source for Power BI reports. For more information about these APIs, see Business Central API V2.0.

Business Central online also supports custom APIs. Application developers of Business Central solutions can create their own API pages and queries and package them into apps. You then install the apps on your tenant. Once installed, you use the API pages for your Power BI reports, like you'd do with the built-in APIs (v2.0). For more information about how to create an API by exposing pages or queries, see Developing a Custom API.

Power BI reports for Business Central online are sourced from a secondary, read-only database replica for performance reasons. As a consequence, AL developers should avoid designing API pages that make database modifications while the pages are opening or loading records. In particular, consider the code on the AL triggers: OnInit, OnOpenPage, OnFindRecord, OnNextRecord, OnAfterGetRecord, and OnAfterGetCurrRecord. These database modifications, in some cases, may cause performance problems and prevent the report from refreshing data. For more information, see Performance Articles For Developers in the Business Central development content.

In rare cases, the behavior causes an error when a user tries get data from the API for a report in Power BI Desktop. However, if database modifications are necessary in the custom API, Power BI Desktop users can force the behavior. For more information, see Building Power BI Reports to Display Business Central Data.

Let's now recreate the Page and Query we developed in the previous unit as an API Page and an API Query.

To create an API page based upon the Customer table, follow these steps

1. In Visual Studio Code, add a new .al file to your project and name it CustomersAPI.Page.al.

2. Paste the following code in the CustomersAPI.Page.al file:

   page 50102 CustomersAPI
   {
       APIGroup = 'powerBI';
       APIPublisher = 'microsoftLearn';
       APIVersion = 'v1.0';
       ApplicationArea = All;
       Caption = 'customers';
       EntityName = 'customer';
       EntitySetName = 'customers';
       PageType = API;
       SourceTable = Customer;
       Editable = false;
       DataAccessIntent = ReadOnly;
   
       layout
       {
           area(content)
           {
               repeater(General)
               {
                   field(no; Rec."No.")
                   {
                       Caption = 'No.';
                   }
                   field(name; Rec.Name)
                   {
                       Caption = 'Name';
                   }
                   field(city; Rec.City)
                   {
                       Caption = 'City';
                   }
                   field(countryRegionCode; Rec."Country/Region Code")
                   {
                       Caption = 'Country/Region Code';
                   }
               }
           }
       }
   }
   

As you can see, the API Page exposes the same fields as the Page webservice you created in the previous unit. The difference is in the properties of the Page object.

The properties: APIVersion, APIPublisher, APIGroup, EntityName, and EntitySetName affect your custom endpoint:

https://api.businesscentral.dynamics.com/v1.0/<user domain name>/api/<API publisher>/<API group>/<API version>/companies(<company id>)/

For more information, see Business Central API endpoints and Calling the API.

To create an API query based upon the Customer Ledger Entry table, follow these steps:

1. In Visual Studio Code, add a new .al file to your project and name it CustomerLedgersAPI.Query.al.

2. Paste the following code in the CustomerLedgersAPI.Query.al file:

   query 50102 CustomerLedgersAPI
   {
       APIGroup = 'powerBI';
       APIPublisher = 'microsoftLearn';
       APIVersion = 'v1.0';
       EntityName = 'customerLedger';
       EntitySetName = 'customerLedgers';
       QueryType = API;
       DataAccessIntent = ReadOnly;
   
       elements
       {
           dataitem(custLedgerEntry; "Cust. Ledger Entry")
           {
               column(customerNo; "Customer No.") { }
               column(postingDate; "Posting Date") { }
               column(amountLCY; "Amount (LCY)") { }
           }
       }
   
       trigger OnBeforeOpen()
       begin
   
       end;
   }
   

You've now created a new API page object and a new API query object. You can further fine tune the API page and API query objects by adding more fields or filters. For this example, we leave them as they are right now.

For API Pages and API Queries you don't need to create a webservices.xml file for the APIs to become available in Business Central. You only need to publish the extension to the Business Central tenant.

To do that, follow these steps:

1. In Visual Studio Code, go to the Command Pallet and select the option: AL: Publish without debugging.

2. Wait until the extension is published. When it publishes, your browser automatically opens and connects to your sandbox in Business Central.

3. In Business Central, open the Extension Management page, to verify your extension was successfully published and installed.

You can now use Power BI Desktop and see these new APIs, using the Business Central Connector for Power BI.

As you can see, custom APIs are available under Advanced APIs in the Business Central connector in Power BI Desktop.