Tips and Tricks for using BYOD Database with D365 for Finance and Operations

L

We were an early adopter of D365 and for most of the time since going live I’ve been a vocal critic of the reporting capabilities of the application.  Microsoft initially pointed us to OData using Power BI or Excel. This has some limitations, most notably you can only retrieve data from a single legal entity.  Microsoft will next point you to a BYOD database solution.  When I initially looked at this as a solution, I continued to be frustrated primarily because the available data entities were still falling short of my needs.  This has improved over the past 6-9 months, and I am now able to use BYOD to get some answers.  This post is articulate what I’ve learned.

At the time of this posting, we are on version 8.0/PU21.  I understand that there are a lot of improvements around BYOD in the 8.1 release.  Once we upgrade, I will attempt to update this post to reflect those improvements.

BYOD Setup

There are certainly better sources for how to setup the BYOD database.  I mainly want to say that getting the connection setup is very easy.  It can be done in under an hour, so do not immediately think that it is a project that needs to be planned.  After provisioning an Azure Sql Server database, you will configure a connection in the D365 Data Management workspace.  You can copy the connection string from the Sql Server page in the Azure portal.  One little quirk that you will discover, is that this string is encrypted by the D365 instance, thus after pasting the connection string into D365, you are able to edit the string (userid and password) and it will work fine.  However, if you refresh your D365 test instance from production, you can view and edit the connection string to point to a test BYOD database, but it won’t work because it is still using the encryption unique to the production environment.  You will have to cut and paste a new string from the Azure Portal.  In most cases, you’ll want to connect to a test BYOD database server anyway, so it’s just as well that it forces you to do a fresh copy.

Once you establish the connection to your BYOD database, it will appear as a new target data format when creating or editing an export job in the Data Management workspace.


Refresh Entity List

Sometimes you might encounter errors or warning when selecting, publishing or exporting data entities. This can often happen after an upgrade or platform update has occurred.  Many of these can be cleared up by refreshing the data entity list under Data Management/Framework parameters.  This really isn’t advertised anywhere and I don’t understand why Microsoft doesn’t make this part of the upgrade deployment process to save you the trouble.  It never hurts to do a refresh to keep the pipes clear.

Data Entity Refresh

Publishing Data Entities

To make a data entity available in your BYOD database, you need to first publish it.  This simply creates the table in the BYOD database.  To do this, go to Data Management/Data Entities and filter or search for the desired data entity.  Depending on the data entity, you should decide whether you will want to export the full table each time or incremental.  To do incremental, you will need to select the Track Changes menu for the data entity.  I always select the Entire Table.  I’ll do this until it causes performance problems.  Next, select the Publish menu to push the data entity to your BYOD database.  This will create a batch job.  For the most part, I’ve found that they run instantaneously.

Data Entity Publish Error

When publishing, you may get the following error:

I will confess that I do not know what this means and life is too short for me to find out.  This will prevent the data entity from publishing.  If you look at the target fields for the data entity, you will see columns for configuration keys and that some are blank.  Again, I can’t give much advise here.  I only care about the fields I want in my BYOD and so far, none of those have lacked a configuration key.  To resolve the issue, you will need to go to the data entity list, select the data entity cause problems, and select the Modify target mapping menu.  Remove the offending columns from the mapping and save.  This can be a little eye glazing because the field list can be long and it may not be obvious which field is causing trouble.  I remove everything but the fields I need to see in my BYOD database.


Exporting Data Entities

Exporting data entities is the same as exporting to Excel or other formats with the exception that you will select the BYOD database as your target.  You can then schedule a batch job to run on the required frequency to load data into the BYOD.  Prior to PU21, the export job was limited to a single legal entity, so if you needed, for example, Sales Invoices from three legal entities, you had to setup an export job for each one.  I haven’t played with the multiple legal entity export in PU21 yet, but it will be a big time saver.

You may encounter a primary key violation error when running an export job for a table.  This indicates that the data entity has not been designed to assure that there is a unique primary key.  This may matter to you, or it may not.  If it does not, a quick work around is to go to your BYOD database and drop the primary key on the table.  This will allow the export job to complete without errors.


Creating Views

Now that you have data in your BYOD database, you will discover, upon browsing the tables, that the table names are not particularly attractive.  Firstly, they all have been appended with the word “Staging”.  Secondly, the name may not describe the contents very well.  For example, the data entity “General Journal” exports to the slightly more cryptically named LedgerJournalEntityStaging table.  You will also find, if you browse the columns of a table, that you get a lot of meta data columns about the export that you probably don’t care about unless you are building a robust data integration.

To shelter yourself and other non-technical users from the ugly truth, you can create views of each table with a more useable name (e.g. vwGeneralJournal).  You can also limit the views to the useful columns.  


Reporting

You can now connect to your BYOD database with your chosen reporting tool (Excel, Power BI, SSRS, Access).  I’m going to focus here on Excel because I think this is most useful for the ad hoc reporting where you just want to get to your data quickly. 

Let’s say that you want to provide a parameterized report to your user through Excel that will allow them to update cells in a spreadsheet and refresh the data.  Write your query in SSMS with hardcoded values for the parameter items.  Once you are happy with the results, go to Excel. 

Querying the Sql Server database with parameters from Excel is not as obvious as it should be.  Your inclination will be to connect to the BYOD database as a Sql Server database.  You can do this and paste you query under the advance button while setting up the connection.  This will return data to your spreadsheet.  However, I have not found a way to replace the hardcoded values with parameters.  In fact, it is hard to get back to the Sql query in the form you originally typed it and editing the hardcoded values is tedious. 

Instead of selecting “Sql Server” as your connection type, choose Other Sources/From Microsoft Query.  You will then be presented a list of available databases on your BYOD server.  Select your BYOD database, and then select a table.  It can be any table.  The smaller, the better because you are going to replace it with your query.  Data for the selected table will populate the spreadsheet and will create a query under Data/Queries and Connections.  Right click on your query and select Properties.  This will present a setup dialog.  Go to the Definitions tab and paste your query in the Command Text field.  After pasting, replace your hardcoded values with a question mark (?) for each.  You will notice that the parameter button is initially disabled.  When you click Okay you will be prompted to select the cell from the spreadsheet to replace each question mark, in order.  

Your spreadsheet will populate using the cell values as parameters.  You can then change the cells, right click on the table, and select refresh to refresh the table using the new parameters.  If you go back to the Properties of the query, you will see that the Parameters button is now enabled.  You can change the cells used and also change the refresh behavior. 

You might notice that if you define your parameter cells in the upper left corner of the spreadsheet, that when the data is initially populated, the parameter cells are pushed to the right of the table.  This is a little annoying, but simply deleting the cells to the left of the parameter cells will reposition them and subsequent refreshes of the data will not push them back to the right of the table.


Stored Procedures

Another option available to you is to create a stored procedure in your BYOD database that is called from Excel.  Perhaps, the data you want to report on requires a bit more than a simple query.  You can create a stored procedure in the BYOD database that does the work and then returns the results.  SSRS developers often opt for this method regardless of query complexity to avoid putting business logic in the report.    

To do this, create your stored procedure and run it in SSMS.  Once you are happy, follow the same steps above to create a Microsoft Query connection.  When you want to replace your Command Text under the query Properties, you might expect to use the syntax “exec dbo.<stored procedure name> ?,?,?”.  You will find that this will not work.  Instead use the following syntax:

              {call dbo.<stored procedure name>(?,?,?)}

Again, you will be prompted to select the spreadsheet cell for each parameter and your spreadsheet will populate with data.I hope you find this post helpful.
. .

Leave a Reply