cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Drrickryp
Super User
‎09-18-2019 11:44 AM

Importing an external relational database into Common Data Service and recreating the relationships.

In this post, I have used data from the Northwind database that comes with MS Access.  It contains a number of tables with relationships.  I selected the Customers and Orders table for the example.  Following the example, I will explain how to bring a more complex database into the Common Data Service.  The first step was to bring in the tables from MS Access by exporting them to Excel and saving them in my OneDrive for Business.  In the folloiwng example, I am referring to the Customers table as the Parent and the Orders table as the Child.  The Primary key from the Parent is the Foreign key in the Child table.  The relationship is One to Many as one customer can have many orders but each order can have only one customer. 

  1. Start with the Parent table first by opening it in Excel. Then with the cursor inside of the table, select the Data tab. Then select the From Table/RangeOpening Power Query.png

     

 

  1. Once this option is selected Power Query will open and the table can be edited.  In this case, I have used the choose columns to eliminate some columns.Choose Column.png

     

 

  1. After using the Power Query functions to transform the table to exactly those fields that I want in my Customer entity, I changed the name of the table under Query Settings to Customers and saved the table using Close and Load.  It is important not to remove the Primary Key from the table, in this case ID.Saving Customer table.png

     

 

  1. This will close Power Query and return to Excel with a new sheet named Customers.NewSheet.png

     

 

  1. The File should then be saved to OneDrive for Business and closed.  The next step is to create the Entity for the modified table.  Open Dataflows in Powerapps and select New Dataflow from the upper left side of the screen.  You will then name the dataflow and proceed to the next screen where you will choose the data source, in this case ExcelChoose excel.png

     

 

  1. Proceed to the next screen and select the Excel file from the OneDrive for Business.   Then select the table modified and renamed in Power Query.  Table will open and before proceeding, you can make sure you have the right one.  Then choose Next in the lower right hand side of the screen.  At this point, check the columns, all of them will likely have ABC123 next to the name.  Change the column type by clicking on this icon and selecting the proper type.  This is important for the primary key field and dates.  The text columns can be left alone. After all transformations are done, select NextPrimary key.png

     

 

  1. The next screen gives the options for creating the entity, naming it and mapping fields.  Select create a new entity and in the upper right side, give the entity a name,  use the Primary key as the key field and select a descriptive column as the Primary name field.  All text fields are defaulted to multiline text but the name field must be single text.  I used the dropdown and changed the Company field to text.  Adjust the mapping appropriate to the data and proceed to the next stepMap key field.png

    Primary Name field.png

     

  1. Follow the prompts and create the new Entity.  Depending on the amount of data in the table, this can take anywhere from a few minutes to hours for very large tables.  You will be notified when the process is completed and the new entity can be found in PowerApps.Customers entity.png

     

    Open the entity and examine it. It contains all of the columns now with “Custom” type and many “Standard” type columns that are metadata of the entity.  Note that Company has a Primary Name  and that in addition to the ID column there is a new column called Customer and it is of the Unique type.  This is the GUID column that will be added to the Orders table in the next steps. Customer metadata.png

     

    Choose the Edit data in Excel and save the resulting file in OneDrive for Business.  Upon opening it, you can examine the custom columns as well as the metadata that has been added.  In the next steps, you will need this file so remember where it is. This is now the Parent Entity. 
  2. Open the Child (Orders) file in Excel and then open Power Query. Note that it contains the Foreign key field CustomerID. Use the Data tab and select from table/range to open Power Query. CustomerID in Orders.png

     

    Transform the table by eliminating unneeded data columns. Then select New Source in the upper right side of the screen and open the Excel file that was created from the entity.Selecting the merge file.png

     

    Examine the Parent table and amongst the metadata is a column Customers that contains the GUID generated by PowerApps for the Parent entity. Note the Queries column on the left side of the screen now contains both tables.Customer GUID.png

     

    Other than the Foreign key field this is the only column needed so remove all of the other ones using Choose columns.  Then switch back to the orders query by selecting it in the upper left side column. Open order query.png

     

    In the Orders query select the merge queries option and select the Customers query for the merge. Merge by selecting the Foreign key in Orders and the Primary key in Customers and select OK.Merge Query 2.png

     


    The merged query will appear with the merge column on the right There will be a double arrow icon at the upper right. MergeTable.png

     

    Select it and select the Customer column only.Adding Customer GUID.png

     

 

  1. CustomerGuidadded.png

     


    The Customer GUID is now in the Orders query.  Close and load it.  The Excel file if it is in OneDrive for Business should be autosaved.  If this option is not selected, then select it.
  2. Now create the Child entity from the Orders data using exactly the same steps as taken for creating the Parent entity (customers).  Start with Excel and use Power Query to choose the fields for the Entity.  Then create the Orders entity the same way the Customer entity was created using the Dataflow option in PowerApps.  I have not reproduced this as the steps are the same for creating the Parent entity. 
  3. Once the entity is created, open it and add a Lookup Field and point it to the Customer entity. This will automatically create the Many to One relationship between the entities. CustomerLU.png

     

    Now open the Child entity (orders) in Excel. Note that two new empty columns were created by adding the Lookup column.  The first will be on the left side of the table and will contain the name of the Lookup field “CustomerLU” followed by “(Lookup)”.  Ignore it and find the GUID column that was added in the last step and copy it.Adding the GUID to the Lookup field.png

     

    Locate the empty CustomerLU field amongst the metadata columns on the right side of the table. Paste the GUIDs into this column and Publish the table from the lower right hand side of the screen.Locate the empty CustomerLU column.png

     

    The Lookup field is now populated and since the Many to One relationship is now established and populated, additional data entered using this field will automatically be linked by the relationship. The Excel file can now be closed.  Populated Lookup field..png

     

The process for recreating a more complex database using CDS from external sources uses the steps above.  First, examine the external database for One to Many relationships.  Start with Tables that only include the One side and import these first. These will be Lookup Tables for the most part.  Then select tables that have lookup fields but are Parent tables to other Child tables.  Finally bring in the lowest level Child tables.   In my experience, this has been a very cumbersome process but appears to be the only way for importing a database with relationships as CDS uses its own GUIDs to define the relationships between entities. 

 

 

 

 

Comments
Power Participant
‎09-18-2019 12:26 PM

@Drrickryp 

I actually found a better method.

Since CDS is the backbone to Dynamics, all of the data import/manipulation features of Dynamics can actually be used.

  • Go to admin.powerplatform.microsoft.com
  • Click on Environments on the left menu, select the environment you want to build in and click the "More Environment Actions" elipsis. From there you can select:
    1. Open Environment. When the environment opens, click the gear in the top right. Select Advanced Settings. From the top menu, select the arrow next to Settings and then Customizations. Then select Customize the System. This will put you in the standard Dynamics customization panel, which is much more robust and reliable for building out entities, fields and relationships.
    2. Settings. From here you can use the Dynamics Data Import Wizard, as well as bulk deletion, Duplicate detection and other features. The import wizard from here in Dynamics allows you to map lookup fields more explicitly and map option set values. It can also handle empty values here, which the CDS for PowerApps version can't do.

Basically if you want to use CDS, follow these steps and sneak back into the Dynamics side to do all your work. It is much more reliable.