Admin | ErpCoder

Analyze Dynamics 365 Finance / SCM Data in Synapse (Video)

20. February 2024 Leave a comment

Load Dynamics 365 F/SCM in Synapse and visualize in PowerBI

Synapse link for Dataverse is the replacement for Export to Datalake which has been deprecated in 2023. Although it is called link for Dataverse, it can be used to access Dynamics 365 Finance and Supply Chain tables (and CE data from dataverse as well).

SQL Track Changes has to be enabled in D365 F/SCM. Creates, Updates and Deletes are written to a storage account in CSV format. Synapse is running a spark pool that converts the CSVs into Deltalake format which relies on the (Open-Standard) Parquet format. As result you can see and query the F/SCM tables in the lake like it were tables in a relational database.

Synapse Workspace with Dynamics 365 Finance and Supply Chain data

Good news, Synpase has a serverless SQL pool and a public SQL endpoint. You can find the SQL endpoint from the Manage Icon (down left) > SQL Pools > Select your pool > Workspace SQL Endpoint.

You can create databases in this SQL pool and create views based on the tables in the datalake. For example join CustTable and DirPartyTable and provide a view that contains customer data with a name and address.

You can use the development workspace in Synapse web based workspace but you could also use other tools to connect e.g. SQL Server Management Studio. Tables in the datalake and views in another database can be accessed.

PowerBI has a built-in connector for Synapse Workspace. You can easily load data from the tables and also from the views.

Filed under Admin, Azure, BI, Dynamics 365 Finance and Operations, PowerBI Tagged with Azure, Dynamics 365 FO, PowerBI

Version Control for PowerBI with Git

28. August 2023 Leave a comment

When working on PowerBI projects for a longer time or supporting a customer, version control would be a desireable feature. In many cases PowerBI files are stored on file share or SharePoint. At this moment (August 2023) there is no integrated version control feature in PowerBI Desktop. But we can use featues from different software products to build a version control strategy for PowerBI.

There is a preview feature call “Save as PowerBI project” in PowerBI Desktop. This will split the PowerBI report into multiple files that contain the model definition, the report layout and some more files. Now that we have multiple files in a project folder, one can come up with the idea to put these files under a version control system.

You can use Git as version control system on your local PC or wherever the PowerBI reports are developed. Git has a local repository and can be connected to central repository. In Azure DevOps you can setup projects using Git as version control system. Connect your local PowerBI Git repository with Azure DevOps to manage your PowerBI report development.

Here you can read the original post from Microsoft: https://powerbi.microsoft.com/en-us/blog/deep-dive-into-power-bi-desktop-developer-mode-preview/

I’ve made a video that shows you how to setup version control and connect PowerBI with DevOps:

Filed under Admin, BI, PowerBI Tagged with DevOps, Git, PowerBI

Data I/O Cheat Sheet for Dynamics 365 F/SCM

12. May 2023 Leave a comment

There are many ways how to access, import and export data in Dynamics 365 Finance & Supply Chain Management. Find here a one page PDF summary of 12 common ways. Every solution has it pros and cons.

Filed under Admin, BI Tagged with Data access, Datalake, Dynamics 365 FO, Excel, OData, Power Platform

Admin Provisioning Tool Error: The value’s length for key ‘password’ exceeds it’s limit of ‘128’

12. April 2022 27 Comments

Microsoft has recently released the new VHD for Dynamics 365 Finance and Operations 10.0.24 to download from LCS. When you instantly try to execute the Admin Provisioning Tool and provide your domain user you will get an error.

The value’s length for key ‘password’ exceeds it’s limit of ‘128’

There are some steps required before you can assign it to your Domain:

Go to https://portal.azure.com > Active Directory > App Registration and register a new App

Register a new application in Azure Active Directory

Provide the One-Box URL as reponse address
Copy the AppID to your clipboard

App Registration for Dynamics 365 FO 10.0.24 One-Box Environment

Execute the “Generate Self-Signed Certificates” PowerShell script from the Desktop
Provide the AppID from the App Registration

6. Execute the Admin Provisioning Tool and provide your Domain account address
7. Open https://usnconeboxax1aos.cloud.onebox.dynamics.com/ in Edge and login

Dynamics 365 Finance and Operations 10.0.24 One-Box Environment

Filed under Admin, Azure, Dynamics 365 Finance and Operations Tagged with Dynamics 365 FO

Store Dynamics 365 F/SCM Attachments on SharePoint

14. February 2022 Leave a comment

Dynamics 365 Finance & Supply Chain allows you to store attachments in the database, on Azure BLOB and on SharePoint. See here how you can configure SharePoint as storage location and create a document type “Fact Sheet”:

Filed under Admin, Dynamics 365 Finance and Operations Tagged with Dynamics 365 Finance, SharePoint

Dynamics 365 Environment missing in PowerBI Dataverse Connector

12. January 2022 2 Comments

Dataverse is the name of the Dynamics 365 Apps database (e.g. Sales, Service, Field Services, etc.). PowerBI desktop comes with a builtin Dataverse connector. However, if you have multiple environments not all of them may show up in the connector.

Dataverse environment missing in PowerBI connector

In order to make the environment visible in PowerBI Desktop Dataverse Connector you have to enable PowerBI Embedded in your environment.

Open Power Apps Admin Center https://admin.powerplatform.microsoft.com/
Select the missing environment
Open Settings (gear icon on the top)
Search for TDS and open the Datastream Endpoint Configuration page
In the group Embedded enable embedded PowerBI visuals

Enable PowerBI embedded visuals to see the Dataverse environment in the PowerBI connector

Now when you open PowerBI Desktop and use the Dataverse Connector you should see the missing environment.

Missing Dynamics 365 Dataverse environment is now visible in PowerBI Dataverse connector

Filed under Admin, BI, PowerBI Tagged with Dynamics 365, PowerBI

Migrate Azure Analysis Services to another Tenant

28. October 2021 1 Comment

Azure Analysis Services are SSAS as a Service hosted and managed in Azure. We recently had to migrate an analysis model from one tenant to another. Here is a video that illustrates how this can be done:

Find more videos in my Youtube Channel.

Filed under Azure, BI Tagged with Analysis Services, Azure

Towards Trust in Complex Cloud-based ERP Systems by Informing Users about the System Status

22. September 2021 Leave a comment

Modern cloud-based ERP systems are complex distributed software applications. These systems became more powerful over the last decades and provide more features to satisfy user needs than previous generations of ERP systems. Furthermore, they are integrated with other cloud-based systems. [Question/problem] The resulting increase in complexity leads to a higher probability of failures within this integrated system. This makes it difficult for users to fully understand these systems and even qualified key users don’t have an overview of possible system issues anymore. As a result, the number of support calls and diffuse support ticket requests have increased in the last years. Moreover, ERP partner organizations such as insideAx experience that users lose trust in their systems. [Principal idea/results] The goal of our work is to foster the trust of ERP users in cloud-based ERP systems and to reduce the number of unnecessary support requests, by enhancing existing user feedback and monitoring mechanisms and provide a visualization of system health indicators to users. Overall, these visualizations and explanations of the system health status need to be easy to understand by users. [Contribution] In this workshop paper, we provide insights from industry on how to foster user trust in complex software systems and depict a conceptual solution which makes use of system monitoring data to communicate the system status to users in a simple and understandable way. Our conceptual solution, for which we also provide a first implementation architecture proposal, foresees that simple modifications to the software and ambient light devices allow to build such visualizations.

The 5th International Workshop on Crowd-Based Requirements Engineering (CrowdRE’21) https://crowdre.github.io/ws-2021/

Filed under Admin, Cloud, Methodology Tagged with Cloud, Dynamics 365, Research, Trust

Windows 365

11. August 2021 Leave a comment

Microsoft released a new offer: Windows Cloud-PC. You can choose between two licensing types, Windows 365 Business for small companies and Enterprise for larger ones. Technical speaking, Windows 365 is nothing more than a Windows 10 VM running in Azure providing access via RDP and RDP Web Client. This means you can operation your Windows from almost any device with a Browser e.g. a Tablet or shared PC. Windows 365 is integrated within Office 365. You can assign a cloud pc to a user from within the Office 365 admin panel. Users can access the cloud pc from Office 365.

A Cloud-PC can be accessed using a web browser from your Office 365 portal

In contrast to a typical Azure VM, Windows 365 comes with a fixed price you pay per month depending on the sizing. Unfortunately, you can only choose from different configurations based on CPU and RAM but not storage technology. For example, if you select a cheaper one with 2 cores and 4 GB RAM you will get a HDD instead of an SSD.

In theory you can use a Cloud-PC from a tablet, like in my case from a Samsung Galaxy Tab. But in practice it turns out that it is very buggy to use. The on-screen keyboard pops up very often. The full-screen view resizes many times. Calling the Windows start menu triggers strange actions. One opportunity for Windows 365 could be to provide Home-Office users a workplace.

Filed under Admin Tagged with Azure, Windows 365

Mini Webcast Series: Dynamics 365 FO Connector for Power Automate

9. March 2021 Leave a comment

Power Automate (aka. Flow) is a great solution for process automation. It is very useful to realize cross-application processes. Power Automate features a wide variety of tasks and connectors. It also comes with a connector for Dynamics 365 Finance and Supply Chain Management. It can be used to trigger a flow, interact with data and execute business logic in D365 FO from within a flow. You can find all videos on my YouTube Channel.

Part 1: Trigger a Flow with Business Events

Business Events in Dynamics 365 Finance and Supply Chain Management are use to interact with other systems. For example you can send message to Azure Event Hub but also to Power Automate. There are some specific events but you can also use change-based alerts in Dynamics. This video shows how to trigger a flow using such a business event.

Business Events can trigger Power Automate flows

Part 2: Interact with data in Dynamics 365 Finance and SCM

The Connector for Dynamics 365 FO uses entities to interact with data in the ERP system. You can create new records, read, update and delete records. Here is an example where Power Automate receives data from Forms Pro and create a new customer in Dynamics 365 FO.

Create new records in Dynamics 365 FO from Flow

Part 3: Execute Business Logic in Dynamics 365 Finance and SCM

The Dynamics 365 FO Connector in Power Automate supports to execute business logic by calling actions on entities. Here is a video how to approve a Bill of Materials in Teams and execute the Approval logic via Flow.