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/Range

    

2. 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.

    

3. 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.

    

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

    

5. 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 Excel

    

6. 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 Next

    

7. 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 step

   

    

8. 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.

    

   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.

    

   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. 
9. 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.

    

   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.

    

   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.

    

   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.

    

   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.

    

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

    

   Select it and select the Customer column only.

    

8. 

    

   
   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.
9. 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. 
10. 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.

     

    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.

     

    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.

     

    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.  

     

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.