Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Customer experience | Sales, Customer Insights,... / Access on-prem data us...
Customer experience | Sales, Customer Insights,...
Suggested answer

Access on-prem data using the On-Premises Data Gateway?

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

I think it's a very simple thing I am missing:

I have a database on-premises with 4 TB of data in one table - searching in that table using the PK and some other columns is fast and I only need 10 rows on a form as a sub-grid.

I read about the "On-Premises Data Gateway" that can be added in make.powerapps.com to the "Gateways" section and there is a matching database connector.

After installing and connecting the gateway I thought I have some type of "proxy" in my network, so that I can query data on-prem data in my common data model (now known as Microsoft Dataverse) using a virtual entity that simply points to that gateway. At least that would be the way how I would start implementing such a gateway.

What I can do is: import data using that connection into my CRM environment and refresh that data each 10 minutes - but, as I wrote above: the table of interest is HUGE and massive. I need something that can use the data gateway to query the data in realtime from the on-premises table instead of copying the data into the CRM environment.

So: how can I create a virtual entity that uses the "On-Premises Data Gateway" to query data from on-prem data sources on demand? Is there really no solution for that? Should I implement something by myself?

Currently, I do NOT have the opportunity to open ports from outside to my on-prem database or implement a custom service to act as a proxy.

  • Joe Gill Profile Picture
    Joe Gill on at
    RE: Access on-prem data using the On-Premises Data Gateway?

    Create a custom web page to sit in front of your database and  embedded this as iframe to display the data from your database. Assumes your users are on "your" network.

    Or create an API and use PCF or a html resource

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Access on-prem data using the On-Premises Data Gateway?

    Hi Joe,

    Thank you for that link - I thought about something similar when there is no out of the box solution.

    I will provide a more detailed description that shows why such a feature as a virtual entity is important:

    I am an experienced C#-/Azure-/Dynamics-developer that has been contracted by a small banking company to implement some aspects of the Dynamics instance they are currently rolling out. In another Dynamics related project of that company, they have the usecase of accessing this huge tables and presenting the data on Dynamics forms. Unfortunately, there is no deep knowledge inside the company about Dynamics, Power Platform, etc.. So, they need simple and "out-of-the-box"-solutions for the requirements that will work and can be maintained when I'm in my next project.

    The data must not be older than 10 minutes, because they will enter data into one system, it will flow through some other systems and eventually finds its way into the database that this project is able to access from the data gateway. They need to provide that data to other users that only have access to Dynamics to finish business processes with a tight time schedule. So, the accepted delay between entering data in system A and having that data in Dynamics is about 10 minutes.

    If there is no "oob"-way to connect a virtual entity to an already existing data provider for the on-prem-data-gateway, I will have to write one by my self.

    Another option might be to use a custom PCF control I already have to query a Power App Flow and show the result.

  • Suggested answer
    Joe Gill Profile Picture
    Joe Gill on at
    RE: Access on-prem data using the On-Premises Data Gateway?

    Hi Sven,

    You could create Power Automate triggered by a Htpp post passing the PK in the Json message.

    Create an action to query your on prem sql database using the PK parameter returning the results as Json. I did a post a while back that does something similar but inserts rather than selects data

    joegill.com/.../

    hth

    Joe

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans