Combining Jet Reports/Analytics with Power BI to Supercharge Your Reporting and Business Intelligence
Many Microsoft Dynamics 365 Business Central or Dynamics NAV users might be confused when considering reporting and analytics options. Popular options include Jet Reports, Jet Analytics, and Power BI.
Which is the best choice?
Each tool was designed for a specific purpose; therefore, it depends on what you want to do that will help determine the best tool to use for your organization.
With that said, the answer might surprise you: A combination of Jet Reports or Jet Analytics with Power BI is often the best solution for comprehensive business analysis and reporting for most companies.
Let’s take a closer look at each of these tools:
Jet Reports
Jet Reports is a reporting application that runs inside Excel and connects to NAV or BC data as well as other data sources. Users can easily bring real-time, dynamic data from NAV/BC and other sources into Excel. Since Jet Reports is accessed from a ribbon within Excel, it is favored by financial professionals who can prepare month- and year-end financial statements from any general ledger (GL) account, including multi-company consolidations. However, Jet Reports is not limited to financial statements. You can pull any data from NAV or BC into your Jet Report to make everything from Receivables and Aging reports to reports on Back-Orders, Commissions, and more. Reporting wizards make it easy for non-technical users to create new, dynamic reports, and Jet Reports also comes with 135+ out-of-the-box reports.
In the past, NAV and BC users had direct access to the underlying SQL database and could craft their own custom reports through queries. Recent changes, however, mean that BC SaaS users no longer have direct access to the SQL Server database. In addition, Web Services are being phased out. But Jet Reports provides access to every table and field in NAV/BC and gives users control over all their data. An alternative would be for users to have their partners write custom APIs to access the same information.
Jet Analytics
While Jet Reports is a front-end reporting tool allowing you to bring your data into Excel easily, Jet Analytics is a back-end tool. It gives you a Data Warehouse and seven OLAP Cubes out of the box, built on your NAV or BC data. The data warehouse and OLAP cubes make reporting fast and can be queried for analytics, reporting, and dashboards. For example, you can use your Data Warehouse tables and OLAP cubes as data sources in Power BI to quickly make visualizations and dashboards. Also, Jet Reports is included with Jet Analytics at no extra cost. Jet Reports can query the data warehouses and OLAP cubes, in addition to a direct connection to your NAV/BC data, for fast reporting. Further, the OLAP cubes can be used to quickly make pivot table reports in Excel in just a few clicks.
OLAP cubes (OLAP is short for Online Analytical Processing) are used to aggregate data (called measures, for example, the total number of sales) by multiple dimensions (such as color, year, location, size, salesperson, etc.). For example, in just a few clicks, OLAP cubes can be used to see the total number of sales of an item by store location, color, size, and quarter.
Jet Analytics has over 200 pre-built templates for financial reports and dashboards in Excel and Power BI formats.
Power BI
Power BI provides a platform that can connect to virtually any collection of data sources (including Jet Analytics OLAP cubes and data warehouse) and visually represents the data creatively. Power BI can be used to extract meaningful relationships from your data, visually represent these relationships in KPIs, reports, and dashboards, and share these visualizations with colleagues.
Using Power BI with Jet Reports and Jet Analytics
There was a time when financial reporting in Dynamics NAV required time and development resources. Once the data connections are made, non-technical users can use Jet Reports and Jet Analytics to perform sophisticated reporting and analytics.
Several benefits of Jet Reports are:
- Real-time access to your NAV or Business Central data
- Flow fields (dynamically calculated fields you see in NAV/BC like “Balance” on the Customer list or “QOH” on the Item list) are available to pull into your reports as if they are fields on your table (you do not have to calculate these in your report, as you would need to do with other reporting tools)
- Jet Reports operates within Excel, the tool of choice for financial professionals to produce financial statements and reports
- Jet Reports makes all of your NAV/BC dimensions available on every table, so you can slice, dice, and filter your reports in any functional area by any/all of your dimensions. This is a significant advantage over other reporting tools because only your two global dimensions physically exist on every table.
- Ability to quickly export any/all of your NAV or BC data into an Excel spreadsheet that refreshes on a schedule, which can then be used as a data source for your Power BI reports.
Several benefits of Jet Analytics are:
- Data is de-normalized, meaning that multiple tables are collapsed into one, giving faster access to all fields and quicker report run times
- Data is pre-loaded into the Jet Data Warehouse and the Jet Cubes, so reports run more quickly and do not affect the NAV or BC production environments
- Your Data Warehouse and OLAP cubes are pre-built and ready for reporting
- You can create calculated fields and measures in your Jet Data Warehouse and Cubes and have users pull those pre-calculated fields and measures into their Power BI or Jet Reports.
Adding Power BI to your Jet Reports and Jet Analytics solutions lets users provide clear visualizations of their data. Power BI can collect data from multiple sources, like the Jet Analytics data warehouse and OLAP cubes, in addition to a number of other data sources (CRM, websites, etc.). Power BI also includes artificial intelligence (AI) capabilities to help perform sophisticated business intelligence analysis on the data collected. However, because BC SaaS data is still not completely accessible by Power BI and the many benefits of the Jet Report’s connector and the pre-built Data Warehouse and Cubes that come with Jet Analytics, Jet Reports, and Jet Analytics perfectly complement Power BI’s capabilities.
While it is technically possible to use Power BI to create financial statements from data taken from NAV and BC environments, doing so will not be easy because Power BI was not built to be used for that purpose.
The better solution is to use a specialized reporting tool, like Jet Reports, to prepare your financial statements. Power BI is used to analyze and display data relationships and share this analysis with your stakeholders and other team members. Jet Reports/Analytics and Power BI can access data from multiple sources and combine them, for example, from Azure, Dataverse, AWS, etc.
Contact ArcherPoint to learn more about how you can gain greater business insights using Jet Reports, Jet Analytics, and Power BI. We would also be happy to demo how these products work individually and together to make the most of your data and reporting.