Setup Entity Store’s export to Azure Data Lake storage

It’s easy to start this post, because many people can ask:

What’s a Data Lake?

Fishing in a Data Lake. By cazapelusas.

A Data Lake is not an Azure product but a term referring to a place where data is stored, regardless of whether it’s structured or unstructured. Its only purpose is storing the data ready to be consumed by other systems. It’s like a lake that stores the water of its tributaries, but instead of water with data.

In Azure the Data Lake is a Blob storage which holds the data. And this data can come from Microsoft Dynamics 365 for Finance or Supply Chain Management (I’ll go crazy with the name changes of Axapta 7) or from other sources.

Currently, and since PU23, #MSDyn365FO (#MSDyn365F ? or #MSDyn365SCM ?) officially supports exporting the Entity Store to Azure Data Lake storage Gen1, but compatibility with Data Lake Storage Gen2 is on the works in a private program with Data Feeds that will allow us to export entities and tables (YES!) in near real time. If you want to know more check the Data Management, Data Entities, OData and Integrations Yammer group in the Insider Program (if you still haven’t joined, you should).

Comparison vs. BYOD

The first thing we must notice is the price. Storage is cheaper than a database, even if it’s a single SaaS DB on Azure SQL. For example, a 1GB Blob storage account on Azure costs $21.6/month.

And the simplest Gen 4 with 1 vCore Azure SQL database costs $190.36/month. Almost 10 times more.

And what about performance? This comes from observation, not a real performance test, but data is transferred real fast. And it’s fast because in a Data Lake data is sent raw, there’s no data transformation until it’s consumed (ETL for a DB, ELT for a Data Lake) so there’s less time spent until data reaches its destiny. This doesn’t have a real impact for small sets of data but it does for large ones.

Setup

The process to export the Entity Store to a Data Lake is pretty simple and it’s well documented (but not updated) on the docs. I’ll explain step by step.

Create a storage account on Azure

On Azure go to or search in the top bar for Storage accounts and add a new one with a setup like the one in the pics below:

Make sure to disable Gen2 storage:

And you can go to review & create. When the account is ready go to Access Keys and copy the connection string:

Azure Key Vault

The next step is creating a Key Vault. For this step you need to select the same region as your Dynamics 365 instance:

When the Key Vault is ready go to the resource and create a new secret. Paste the connection string from the storage account into the value and press create:

Create an AAD App Registration

Now we’ll create an AAD App. Give it a name, select the supported account types you need and fill the URL with the base URL of your #MSDyn365FO instance:

Click register and now we must add the Azure Key Vault API to the app as in the image below:

Select the API and add the delegated user_impersonation permission:

Don’t forget to press the button you can see above to grant privileges (must be done by an Azure admin). Now go to secrets and create a new one, give it a name and copy the secret value. When you close the tab you will not be able to recover that secret anymore so copy it and save it somewhere until we need it.

Setup the Key Vault

Go back to the Key Vault we created in the second step and go to Access policies. Add a new one:

You have to select Get and List for Key and Secret permissions:

Now press Select principal and here add the AAD App created in the third step:

Add it and don’t forget to save in the access policies screen!!

Set up MSDyn365F… and O or and SCM or whatever its name is this month

Navigate to System Administration -> Setup -> System parameters and go to the Data Connections tab. Here there’s 4 fields for the key vault. The Application ID field corresponds to the Application ID of the AAD App (pretty obvious) and the Application Secret is the secret from the AAD App. This part is easy and clear.

The DNS name is the url on your Key Vault and the Secret name field is the name of your Key Vault’s secret where you pasted the storage account connection string.

Once all these fields are complete you can press Test Azure Key Vault and Test Azure Storage and, if you followed all steps correctly, you should see the following messages:

If any of the validations don’t succeed I’d just delete all resources and start from scratch, probably a secret mismatch.
Now, the two buttons you see next to the setup fields:
  • Enable Data Lake integration: will enable the full push of the entity store to the storage account you have just created and which is the main purpose of this post.
  • Trickle update Data Lake: will make updates after data is changed (Trickle Feed).

Setup Entity Store

Now we just need to go to the Entity Store (under System Administration -> Setup -> Entity Store) and enable the refresh of the entities we’d like to hydrate the Data Lake (I love this, it looks like it’s the correct technical word to use when feeding the Data Lake):

And done, our data is now being pushed to an Azure Blob:

The entities are saved each in a folder, and inside each folder there another folder for each measure of that entity and a CSV file with the data in it.

Now this can be consumed in Power BI with the blob connector, or feed Azure Data Factory or whatever you can think about, because that’s the purpose of the Data Lake.

 

Manually deploy Retail packages for Microsoft Dynamics 365 for Finance and Operations

First Microsoft Dynamics 365 for Finance and Operations Retail post! I hope more will come.

As you might know, one of the setbacks of the database refresh from production in LCS is that some data doesn’t get copied. This is a safety feature that prevents, among others, that emails are sent or batches run accidentally after a DB restore.

Remember that it’s a good idea to have a SQL query/script that changes all endpoints, passwords, enables users, etc. that you can run after a prod DB refresh, just like it was done with AX2009/2012. Just F5 it in SSMS and the environment will be ready to use and to export to your dev boxes.

Another thing that doesn’t get moved after a DB refresh are storage specific files, ER XLSX, DocuValue files and the self-service Retail installers.

Retail packages

Retail packages are the executable files used to install MPOS on the… well, on the points of sale (POS). These files are stored in an Azure blob storage which is specific to each environment, so after the DB refresh there’s no self service packages in the target environment because the reference was to the production blob:

Microsoft’s official fix for this is applying a binary package that will recreate the EXE files in the VM’s storage where the Deployable Package is run. And as you all know this is time consuming and while you can run it outside working hours you can fix it in less than 10 minutes.

The workaround

Ahhh “workaround”… it’s such a beautiful word with so many different meanings… And this workaround has a restriction: it only applies to dev boxes and Tier 2+ regular environments, this can’t be done on self-service environments as we don’t have access to the AOS VM.

What we need to do is log into the AOS VM using the RDP and go to the service volume (usually K on dev, G on Tier 2+). There should be a folder called DeployablePackages, if you have applied any, otherwise just go with the official fix. However, if the folder doesn’t exist this can probably be done in another way, which is using the files from the install drive, but I haven’t tried.

Sort the files by date modified (newer first) and inside the first folder you should see another folder called RetailSelfService:

And inside this folder you’ll see 3 more folders, Packages, Scripts and ServiceModel. Inside the Packages folders there’s the EXE files and inside the Scripts folder the scripts (obviously Mr. Obvious), open it and the open the Upgrade folder and you’ll find a PowerShell script called UpdateRetailSelfService. You need to run this script in PowerShell as an administrator. It will take between 3 and 5 minutes and when it’s done the packages will be uploaded to the environment’s storage and appear in the Retail Parameters form.

That doesn’t work for me!

There’s a case in which the installers will not be restored: if you have no setup done for Retail. Why? The PowerShell script runs a SQL query that will check for the following:

  • Has Channel data in Channel DB
  • Has any Channel data in AOS
  • Has transaction data in AOS
  • Has transaction data in Channel DB
  • Has Channel DB extensions

If none of the above conditions is met the script will not upload the installers to the blob. But we can do something! Yes, you can go and configure a Channel DB for instance. But what if you don’t feel like doing it?

Remember the UpdateRetailSelfService script I talked about before? Edit it and comment the following lines:

This will make the script skip the check and will deploy the installers.

That’s pretty dirty, right? Yes.

What about self-service environments?

I’m sure this can be also done modifying a Deployable Package that contains the Retail packages (the one for a monthly version update), leaving Retail only in the DefaultTopologyData.xml file, and even editing the script if needed. But I haven’t tried. Any volunteers?