Export to Data Lake has been one of those reasonably newer Power Apps / Dynamics 365 features which were introduced in the platform but I didn’t get a chance to explore until now. A little bit of a background, until 12 months back the only way to export data from Common Data Service (CDS) or Dynamics 365 to any other data storage was Data Export Service. This add-on still exists and exports data in near real-time to Azure SQL Server database.
Now, why would you want to export data out of CDS? Several reasons:
- Complex reporting in Power BI
- Feed Power Apps / Dynamics 365 data to AI / Machine Learning models for extracting insights
- Extensive integration with other systems without introducing any performance overhead on CDS / Dynamics 365 environment
Why Data Lake?
Azure Data Lake Storage Gen 2 (yes, that’s the full name of the product) is a cheap and massively scalable storage solution. The storage is capable of storing binary objects (blobs), queues, files, and table-based storages. Every stored record in Data Lake gets a unique URL and is secured through active directory and other mechanisms.
Because of all of above capabilities, the option to export data to Data Lake is exciting and has obvious benefits over Azure SQL Server particularly for the reporting use case (Power BI talks natively with data lake).
Enough introduction, lets jump on the action.
1. Setup data lake storage
Sign up for a free or paid trial of Azure. Click on New Resource and search for Azure Data Lake Storage Gen 2, and click ‘Create’. Enter the name and other details. Make sure to select ‘Storage V2’ in Account kind and ‘Hot’ as access tier. Click ‘Review+Create’
Select ‘Enabled’ in Hierarchical namespace, leave rest as default
You’ll see the summary page like below. Click ‘Create’. It’ll then take a couple minutes for Azure to spin-off storage.
2. Setup Export configuration in CDS
Go to Power Apps Maker Portal. Click on ‘New link to data lake’.
In the form, select your Azure subscription, storage account will automatically appear in the ‘Storage account’ drop-down, select the resource which you just created
Select entities that you’d like to export. Click ‘Save’
Now give it a couple minutes, then click on ‘Manage entities’.
This view will show synchronisation in progress with the count of records too
3. The action
With all that setup done, let’s see data in action. For viewing data in the data lake, we need Azure Storage Explorer tool. Download and install.
Once installed, open the tool. You’ll need to log in using Azure credentials. It’ll show a list of resources, select your storage and expand to see Blob containers
You’ll see a folder each for an entity. In the folder, all the data is saved as csv.
Per the documentation (and verified in my experienced 😀), the export capability works in almost real-time – which is very impressive! This makes it very easy to create live dashboards, data flows and reports in Power BI.
Happy days!