The power of queries to map Business Central entities into Power BI

The power of queries to map Business Central entities into Power BI

If you use Power BI connected to Business Central you can map new entities (page, queries) to access data, in addition to those already existing prepared by Microsoft.

How to add new entities?

In Business Central Entity = Page or Query exposed as Web Service (OData) and mapped by Business Central Connector (native or by gateway), the structure is different from CRM and other systems etc. etc.

To add new entities that can be used in Power BI you can map existing pages\queries or create new pages and queries mapped as Web Services.

Pages VS Queries

If I publish the standard pages (or custom pages), I could run into these problems:

– I could have Locks on a table

– Pages read data from a single table

– There could be “Filter” fields that go wrong in Power BI (“unknown” field format)

– Functions added to the standard page by extension (ex: “Lookup” of a field etc.) that could slow down the extraction of data by going out in time, furthermore I might not know which app has extended the page. Better to create a new page to expose as Web Service (better control).

Ex: OLE DB Error (Item Table is Locked) > ItemList (page 31) exposed

Ex: “Fields Filter” Errors

Benefits of using queries:

– Read data from multiple tables

– They can already totalize the data upstream (data segmentation)

– They are “NO Lock” by Design – the queries are NO lock by Design, they do not block the tables

Ex: New Query “ItemListWS” is exposed instead of ItemList Page

EX: “QUERY” on Item table

Some queries “READY TO GO” in my GitHub page

https://github.com/rstefanetti/AL-Samples/tree/AL-Queries

*** Tip: use Queries!