Data Warehouse and Power BI

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.