Introduction
Microsoft introduced a new feature called Virtual Entity which will help you access data residing in an external system via OData 4.0 API. You can build your own OData API for your database, plug it in to Dynamics 365 to access your data which would take significant development, maintenance and testing efforts. With Progress Hybrid Data Pipeline, you get an out of the box solution which will help you create an OData 4.0 endpoint, which you can configure as Virtual entity and access your data. In this tutorial, we will walk you through how you can use the virtual entity feature by showing how you can access SQL Server data using OData API generated by Progress Hybrid Data Pipeline.
Introduction
Microsoft introduced a new feature called Virtual Entity which will help you access data residing in an external system via OData 4.0 API. You can build your own OData API for your database, plug it in to Dynamics 365 to access your data which would take significant development, maintenance and testing efforts. With Progress Hybrid Data Pipeline, you get an out of the box solution which will help you create an OData 4.0 endpoint, which you can configure as Virtual entity and access your data. In this tutorial, we will walk you through how you can use the virtual entity feature by showing how you can access SQL Server data using OData API generated by Progress Hybrid Data Pipeline.
Download and Install Hybrid Data Pipeline Server
Download and Install Hybrid Data Pipeline Server
Creating OData 4 endpoint
Creating OData 4 endpoint
Configuring Dynamics CRM Virtual Entity
- In Dynamics CRM, Go to Settings -> Administration and Click on Virtual Entity Data Sources
- Now create a New OData4 datasource, by clicking on New. Fill up the form as shown below. For Authentication, Hybrid Data Pipeline Server uses a basic auth, so you need to calculate the basic Auth String and set a header with ParameterName Authorization and value as Basic base64encoded(username:password)

- Click on Save and Close.
Configuring Dynamics CRM Virtual Entity
- In Dynamics CRM, Go to Settings -> Administration and Click on Virtual Entity Data Sources
- Now create a New OData4 datasource, by clicking on New. Fill up the form as shown below. For Authentication, Hybrid Data Pipeline Server uses a basic auth, so you need to calculate the basic Auth String and set a header with ParameterName Authorization and value as Basic base64encoded(username:password)

- Click on Save and Close.
Create Entity
- Before going further down, let’s look at the entity that I am trying to access via Virtual Entity.
<?
xml
version
=
"1.0"
encoding
=
"UTF-8"
?>
<
edmx:DataServices
>
<
EntityType
Name
=
"Customer"
>
<
Key
>
<
PropertyRef
Name
=
"CustomerId"
/>
</
Key
>
<
Property
Name
=
"CustomerId"
Nullable
=
"false"
Type
=
"Edm.Guid"
/>
<
Property
Name
=
"FirstName"
Nullable
=
"false"
Type
=
"Edm.String"
MaxLength
=
"40"
/>
<
Property
Name
=
"LastName"
Nullable
=
"false"
Type
=
"Edm.String"
MaxLength
=
"20"
/>
<
Property
Name
=
"Company"
Type
=
"Edm.String"
MaxLength
=
"80"
/>
<
Property
Name
=
"Address"
Type
=
"Edm.String"
MaxLength
=
"70"
/>
<
Property
Name
=
"City"
Type
=
"Edm.String"
MaxLength
=
"40"
/>
<
Property
Name
=
"State"
Type
=
"Edm.String"
MaxLength
=
"40"
/>
<
Property
Name
=
"Country"
Type
=
"Edm.String"
MaxLength
=
"40"
/>
<
Property
Name
=
"PostalCode"
Type
=
"Edm.String"
MaxLength
=
"10"
/>
<
Property
Name
=
"Phone"
Type
=
"Edm.String"
MaxLength
=
"24"
/>
<
Property
Name
=
"Fax"
Type
=
"Edm.String"
MaxLength
=
"24"
/>
<
Property
Name
=
"Email"
Nullable
=
"false"
Type
=
"Edm.String"
MaxLength
=
"60"
/>
</
EntityType
>
<
EntityContainer
Name
=
"dbo"
>
<
EntitySet
Name
=
"Customers"
EntityType
=
"SQLServer.Customer"
/>
</
EntityContainer
>
</
Schema
>
</
edmx:DataServices
>
</
edmx:Edmx
>
- As you can see, I have an entity named Customer with a primary key CustomerId of data type Edm.Guid. Your entity’s data type should be Edm.Guid, else you can’t access your data via Dynamics CRM.
- Some Important things you need to keep in mind when configuring Fields in Entity:
- The Column Names are Case Sensitive, you need to set it to same name in the External Name of the field as in your OData 4.0 metadata.
- If the Column has Nullable=false, you need to set Field Requirement to Business Required.
- If the Column has Nullable=true, you need to set Field Requirement to Optional.
- If your column has MaxLength property in OData 4.0 metadata, then set Maximum length to same value in your field configuration.
- Now Go to -> Settings -> Customizations -> Customize the System and Create a New Entity. Configure it as shown below.
- Display Name: Customer
- Plural Name: Customers
- External Name: Customer (should be same as the EntityType Name="Customer” in your OData 4.0 config)
- External Collection Name: Customers (should be same as the EntitySet Name="Customer” in your OData 4.0 config)
- Check the Virtual Entity and now you should see new field called Data Source, and in the drop down you should find the Virtual Entity data source you have configured above.
- Click Save

- Once you have saved entity, it’s time to create Fields. Click on Fields tab on the side bar. You should see two default columns already there.

- Click on column new_customerid, which is a primary key and you should see a Field Configuration as shown below. Set External Name: CustomerId, it should be same as the Primary key column name in your OData 4.0 metadata. Note, the column name is case sensitive.

- Click on column new_name and configure it as shown below.
- External Name: FirstName (same as my OData 4 metadata)
- Field Requirement: Business Required (as my metadata has Nullable=false)
- Maximum Length: 40 (As FirstName in my Metadata has a maximum length 40)

- Similarly, you need to create all the fields in your metadata, here are my configurations for the remaining fields.










- After saving all the fields, Publish all Customizations
- Go to Views, All Customers - Add Columns and add all columns to the view, and Save the view.
- Go to Advanced View and run a query on Customers and you should see your data as below.

We hope this tutorial helped you in accessing your external data in SQL Server or on-premises database from Dynamics CRM. Feel free to try Progress Hybrid Data Pipeline and let us know if you have questions or issues.
Create Entity
- Before going further down, let’s look at the entity that I am trying to access via Virtual Entity.
<?
xml
version
=
"1.0"
encoding
=
"UTF-8"
?>
<
edmx:DataServices
>
<
EntityType
Name
=
"Customer"
>
<
Key
>
<
PropertyRef
Name
=
"CustomerId"
/>
</
Key
>
<
Property
Name
=
"CustomerId"
Nullable
=
"false"
Type
=
"Edm.Guid"
/>
<
Property
Name
=
"FirstName"
Nullable
=
"false"
Type
=
"Edm.String"
MaxLength
=
"40"
/>
<
Property
Name
=
"LastName"
Nullable
=
"false"
Type
=
"Edm.String"
MaxLength
=
"20"
/>
<
Property
Name
=
"Company"
Type
=
"Edm.String"
MaxLength
=
"80"
/>
<
Property
Name
=
"Address"
Type
=
"Edm.String"
MaxLength
=
"70"
/>
<
Property
Name
=
"City"
Type
=
"Edm.String"
MaxLength
=
"40"
/>
<
Property
Name
=
"State"
Type
=
"Edm.String"
MaxLength
=
"40"
/>
<
Property
Name
=
"Country"
Type
=
"Edm.String"
MaxLength
=
"40"
/>
<
Property
Name
=
"PostalCode"
Type
=
"Edm.String"
MaxLength
=
"10"
/>
<
Property
Name
=
"Phone"
Type
=
"Edm.String"
MaxLength
=
"24"
/>
<
Property
Name
=
"Fax"
Type
=
"Edm.String"
MaxLength
=
"24"
/>
<
Property
Name
=
"Email"
Nullable
=
"false"
Type
=
"Edm.String"
MaxLength
=
"60"
/>
</
EntityType
>
<
EntityContainer
Name
=
"dbo"
>
<
EntitySet
Name
=
"Customers"
EntityType
=
"SQLServer.Customer"
/>
</
EntityContainer
>
</
Schema
>
</
edmx:DataServices
>
</
edmx:Edmx
>
- As you can see, I have an entity named Customer with a primary key CustomerId of data type Edm.Guid. Your entity’s data type should be Edm.Guid, else you can’t access your data via Dynamics CRM.
- Some Important things you need to keep in mind when configuring Fields in Entity:
- The Column Names are Case Sensitive, you need to set it to same name in the External Name of the field as in your OData 4.0 metadata.
- If the Column has Nullable=false, you need to set Field Requirement to Business Required.
- If the Column has Nullable=true, you need to set Field Requirement to Optional.
- If your column has MaxLength property in OData 4.0 metadata, then set Maximum length to same value in your field configuration.
- Now Go to -> Settings -> Customizations -> Customize the System and Create a New Entity. Configure it as shown below.
- Display Name: Customer
- Plural Name: Customers
- External Name: Customer (should be same as the EntityType Name="Customer” in your OData 4.0 config)
- External Collection Name: Customers (should be same as the EntitySet Name="Customer” in your OData 4.0 config)
- Check the Virtual Entity and now you should see new field called Data Source, and in the drop down you should find the Virtual Entity data source you have configured above.
- Click Save

- Once you have saved entity, it’s time to create Fields. Click on Fields tab on the side bar. You should see two default columns already there.

- Click on column new_customerid, which is a primary key and you should see a Field Configuration as shown below. Set External Name: CustomerId, it should be same as the Primary key column name in your OData 4.0 metadata. Note, the column name is case sensitive.

- Click on column new_name and configure it as shown below.
- External Name: FirstName (same as my OData 4 metadata)
- Field Requirement: Business Required (as my metadata has Nullable=false)
- Maximum Length: 40 (As FirstName in my Metadata has a maximum length 40)

- Similarly, you need to create all the fields in your metadata, here are my configurations for the remaining fields.










- After saving all the fields, Publish all Customizations
- Go to Views, All Customers - Add Columns and add all columns to the view, and Save the view.
- Go to Advanced View and run a query on Customers and you should see your data as below.

We hope this tutorial helped you in accessing your external data in SQL Server or on-premises database from Dynamics CRM. Feel free to try Progress Hybrid Data Pipeline and let us know if you have questions or issues.