Having plentiful choices can sometimes be overwhelming, hence it is the job of a specialist/advisor to use a select tools from a plethora of tools and tailor-fit them to the analytical needs of the customer.
Let's explore key data options available in Dynamics 365 for Finance and Operations, Enterprise edition (AX) for usage in analytics:
- AX database (AXDB): This is the main OLTP transactional database of your Dynamics 365 for Finance and Operations, Enterprise edition in SQL.
- AX data warehouse (AXDW): This is the Entity Store for data warehousing, available out of the box for Dynamics 365 for Finance and Operations, Enterprise edition in SQL. It supports in-memory processing for large-volume scenarios. It uses aggregated data entities and SQL Non-Clustered Column Store Index (NCCI).
- Read only secondary (ROS): This is a read-only database used by the Application Object Server (AOS) to redirect certain read-only queries off the transactional database.
- Bring Your Own Data Warehouse (BYODW): This allows enterprises to combine information from Dynamics 365 for Finance and Operations, Enterprise edition (AX) by exporting data to an Azure SQL database and then pulling it to their own data warehouse for decision making.
The following table explores these data options as data sources and their usage scenarios:
Tool |
Author |
Datasource/Dataset |
Excel/Office 365 |
End user or Power user |
Frontend or web app for Dynamics 365 for Finance and Operations, Enterprise edition (AX), PowerPivot, Entity Store (AXDW), and OData |
Power BI |
Power user |
OData, Entity Store (AXDW), and DirectQuery to Azure SQL (AXDB) |
Management Reporter |
Power user |
Frontend or web app for Dynamics 365 for Finance and Operations, Enterprise edition (AX) |
Embedded BI |
Developer |
Entity Store (AXDW) |
SSRS reports |
Developer |
AX Azure SQL (ROS), Visual Studio - application explorer, Azure SQL (AXDB), and Entity Store (AXDW) |
Cortana analytics |
Developer |
Entity Store (AXDW), OData, Azure SQL data warehouse, Azure data lake, and Azure data factory |
Azure Machine Learning (AML) |
Developer |
Cortana analytics, Entity Store (AXDW), OData, Azure SQL data warehouse, Azure data lake, and Azure data factory |
With the increasingly rich and populated Microsoft Cloud Technology landscape, new opportunities are presented in ways that organizations can make informed decisions at the speed of light.
Let's explore all the possibilities of using the Microsoft cloud to empower intelligent information insights and action:
As shown in the previous image, the broad areas of analytics are:
- Built-in experiences using SSRS report, KPIs, charts, and more.
- KPI's are Key performance indicators and have always been the backbone of all kinds of analytical reporting needs.
- Business documents including modern reports.
- Financial statements for ad hoc and systematic reporting of financials.
- Data exploration using a powerful visualization drive by Power BI.
- Intelligent actions as recommendations or tasks taken by Cortana cognition.
We foresee modern world business users' need of a BI tool, which can enable the following:
- A cloud analytics service (SaaS-based) that can seamlessly talk to the cloud service of Dynamics 365 for Finance and Operations, Enterprise edition.
- Transactional data in Dynamics 365 for Finance and Operations should be available as a native or first class data source in the BI tool.
- Be able to prepare, distribute, and consume industry templates across organizations and also within an organization
- Security is a long and iterative process, and any BI tool respecting security within Dynamics 365 for Finance and Operations, Enterprise edition is needed
- Information seekers would like to see the dashboards/visuals right within Dynamics 365 for Finance and Operations, Enterprise edition instead of opening several apps
- Mobile is unanimous in the modern world; hence there is a need for a BI solution that can offer interactive visuals over all platforms (Android, iOS, Windows)
- A BI service/solution that offers choice to author reports, be it online or on the desktop
- Ability to fully utilize in-memory processing for faster throughput, leveraging aggregated measures
- Ability to query using simple natural language to represent and filter the data with ease
- Ability to use R programming, a data science language for statistical computing, widely used by data scientists
We all know how much user friendly and great Excel is, and if it could be leveraged seamlessly in your business applications, it will empower the users and increase productivity a lot. Let's explore the various options of using Excel with Dynamics 365 for Finance and Operations, Enterprise edition.