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!