BIDA0039 – BC365 OData Query Using Meta5

Please watch the video in 1080p because it is recorded in High Resolution.
Please note the script of the presentation is below so you can read along with the demonstration if you like.

Hello and welcome to our latest blog post.

I am really pleased you have come back to see our latest news!

I am pleased to bring you some news that I hope will interest you.

I am Sofi.

I am your BIDA AI Assistant.

I will be your hostess for this demonstration today.

This video is intended for companies who have Business Central 365, either on premise, or in the cloud.

This is the video we promised you recently, and we are pleased to be able to bring it to you today.

This video is a follow up, to the prior video demonstrations, of Meta5 accessing the Navision 2018 demonstration database.

We also have a video demonstration in development, where we access Business Central 365 on premise tables directly, using Meta5.

In the prior demonstrations, we showed you how to query Navision 2018, using the Meta5 query tool, and how to access Navision 2018 tables via direct sql queries.

In today’s video we are going to show you how we can query Business Central 365, using the supported O Data interface.

This is done using the C Data ODBC Driver for Business Central 365.

So, as ever, on with the demo!

Demonstration

Here we are on the desktop of our Business Central 365 development machine.

Firstly, I would like to show you the demonstration system we are going to query.

You can see that it is running on a VM on the S Drive.

You can see that the machine is called BIDA B C DEV zero one.

We have the standard Business Central 210 demonstration system installed on this machine.

Now we will go to the web services page.

You can see on the web services page that there is object I D, number 16.

This is the Chart of Accounts Object.

Now we will go to the page showing the results of clicking on the Chart of Accounts O Data interface.

If you have been working with O Data you will recognise the format of the data that is being shown on the screen.

This is the Chart of Accounts rendered as a page of data.

This is what tools such as Power B I, and Excel, receive back from calls to Business Central.

Now we will move across to the Meta5 development machine.

Here we are on the desktop of one of our Meta5 development machines.

We have expanded the capsule, that you can see, so that it covers up other items that are on the desktop.

That is the reason for not showing you the actual Meta5 desktop.

In the top left hand corner, you can see this machine is called Meta5 DEV zero one, and it is running on the I drive.

I want to make sure that you understand these are two different machines.

They can see each other on the LAN created by the host V M ware workstation.

Now I would like to show you the ODBC connections.

You can see that we have used the 32 bit ODBC Driver for Business Central from C Data.

You can see that we called it D 3 6 5 3 2.

The last 32 being to denote this is a 32 bit ODBC driver.

In the C Data Configuration Tool, you can see that we have entered the address of the O Data interface.

You can see that we have set the Authentication Scheme to Negotiate.

Further down in the settings we have put the User ID and Password into the parameters, but there is no need to show you that.

I think you can take it on trust we have put the User ID and Password of the Administration user into the parameters for the ODBC connection.

This could be the User ID and Password of the user of Business Central.

Or it could be that a second instance of Business Central is created, so that the O Data Interfaces could be increased, to allow more data to be queried through Meta 5, than through the normal Business Central 365 instance.

In the end, I am sure you get the idea.

This is the 32 bit ODBC Driver from C Data, which can also be purchased from us here at BIDA.

It is very simple to configure.

You just have to give it the O Data address that you can readily find inside Business Central.

So.

Now we will go back to the Meta5 desktop by clicking on it.

Now we will show you the ODBC connection used in Query 1.

You can see it is D 3 6 5 3 2.

That is all you have to tell Meta5.

You can see the setting, use workstation tools data dictionary, is set to no.

This means there is no dictionary to help Meta5 understand the O Data interface.

Of course, it is possible to create such a dictionary, and to include the joins between tables in the O Data interface.

I wanted to show you, that you do not have to create the dictionary, in order to read the O Data interface.

Now we will close the options window.

Now we will open the query.

We click on show controls.

We see that the prior query created is there.

We will click on clear query to empty out the query icon.

We will click on show query catalog.

We will click on show DBA catalog.

You can see that all the tables in the DBA catalog are the O Data Interfaces.

Meta5 treats these as tables.

For the remainder of this presentation we will refer to these O Data interfaces as tables.

The Chart of Accounts is highlighted because it was in the query previously.

Now I will close the Query Catalog with just the Chart Of Accounts Highlighted.

Now we will copy the Chart of Accounts from the Query Catalog to the output area of the query.

Now we will close the query catalog.

Now we will open up a capture SQL document.

Now we will click on show data.

If you like you can stop the video, and read the sql, that was generated, in detail.

This sql was generated by Meta5, and sent to the C Data ODBC driver, without any manual creation of any dictionary of any kind.

Now we will close the query log and the query control.

We will scroll to the right, and then back to the left, so you can see the data that was returned.

In the query tool, we allow for the longest string to be shown, and so there is a lot of white space.

You can pause the video to read any specific piece of data.

Now we will close query 1.

Now we will just run query 1 and send the data into spreadsheet 1.

Now we will open spreadsheet 1.

Now we will scroll all the way to the right, then back to the left, so you can see the data.

The spreadsheet makes the data a little easier to read.

Now we will close this spreadsheet.

Now we are going to open query 2.

In query 2, you can see that, the Chart of Accounts is joined to the G L Entries.

When there is no meta5 dictionary being used this join must be created manually.

Now I will open a capture SQL Window.

Now I will run this query.

Now I will highlight the join that was performed in the running of the SQL.

Obviously, for business users, it is better for these joins to be pre-defined in the Meta5 dictionary.

This can be done by using the Meta5 dictionary to generate the SQL to query the data.

This very easy to use query tool can already have all the joins needed defined in it.

That is one of the big advantages of using Meta5.

Now we will close the query 2 icon.

Now we will run the query 2 icon so the data is sent into spreadsheet 2.

Now we will open spreadsheet 2.

You can see all the data in spreadsheet 2.

This data will make sense if you have ever used Business Central.

Now I will close spreadsheet 2.

So far in this demonstration we have explained each step as we did it.

What we are going to do now is to build this query in front of you in the demo.

There are no tricks here.

You can do exactly this for yourself, on your Business Central, if you want to.

So please feel free to watch along as we create this query in this demonstration.

We will go about building it while we make more comments.

The columns we are going to put on the output report are as follows.

• Account Number.
• Account Category.
• General Business Posting Group.
• General Posting Type.
• General Product Posting Group.
• Entry Number.
• Document Date.
• Document Number.
• Document Type.

We are going to sum the following numeric fields.

• Amount.
• Credit Amount.
• Debit Amount.

You can see that when we sum a field we highlight it, click create column, and Meta5 gives us a choice of operations to perform.

The default is sum.

But we could also set it to count, average, minimum, or maximum.

You can also insert quite complex formulas into the dialog box.

You can provide any name you want for the output field.

We generally provide the same name as the input field.

Ok!

Now you have seen how to build a simple query that will return GL Entries joined to the Chart of Accounts.

Now we will show you one more thing before we conclude this demonstration.

For query 4 we want to show you that you can set constraints on the data coming back from business central.

In this case we will set the following GL account numbers as constraints.

• 1 1 1 0.
• 1 1 2 0.
• 1 1 3 0.
• 1 1 4 0.

We will capture the SQL that is generated.

Now I will click show data.

You will see there are just 7 items found.

You can see in the generated SQL there is a where clause for the GL Account Number.

Obviously, we can set a constraint for any field on any table.

The reason this is very important is as follows.

Let us say, you are a retailer with 300 stores across the country.

Let us say, you have 20 region managers and 300 store managers.

Let us say, you want to send each region manager and each store manager a set of reports each morning.

These reports will show each person the things they need to know.

Let us say, the tool of choice is Excel.

Using Meta5 you could generate all those Excel workbooks, as separate workbooks.

Each set of reports, for each person, would contain only the data that person needs.

That means each report itself is quite small, and the Excel workbook will be fast and responsive.

Because the data is extracted from Business Central and put into the Excel spreadsheet, the person can read it while not connected to the network.

This ability to create separate, distinct workbooks, that only contain the data the individual needs, is one of the strongest features of Meta5.

The ability for business people to easily ask business questions, without having to know how that happens, is also one of the strongest features of Meta5.

This very easy to use query tool can be used to fetch data, and get it into the other desktop tools, of Meta5.

Meta5 has a large suite of analysis tools that can help business users find and understand causal correlations.

And finally.

Meta5 can send data into Excel workbooks so that the result is the region managers and store managers receive Excel workbooks.

Excel is something everyone in business is very familiar with.

All business users can read and understand Excel reports, with no additional training or software costs.

All you need to get started is one Meta5 workstation with one C Data ODBC Driver license.

With just that?

You are on your way, to being able to ask your Business Central system, any question the data inside it can answer.

Summary

So please allow me to summarise what I have shown you today.

We have shown you, we have a Business Central Demonstration Machine running on a VM.

We have shown you, it has the O Data Services turned on, and it has about 70 O Data interfaces published.

We showed you, the Chart of Accounts data is displayable from inside Business Central.

We showed you, we have another virtual machine running on the same server.

This is a meta5 development machine.

The two machines can see each other on the LAN.

We showed you, the Meta5 development machine has the C Data ODBC driver installed.

We showed you, the address of the O Data Interface on the Business Central Demonstration Machine was entered into the configuration parameters.

We showed you, the Query tool in the meta5 desktop used the name of the C Data ODBC Driver as it’s database name.

We showed you, this can all be done with no Meta5 dictionary.

We showed you, without the Meta5 dictionary joins have to be manually added in the query tool.

Prior demonstrations show that if you use the Meta5 dictionary, then the joins can be pre-added so that business users do not need to add these joins themselves.

We showed you, a series of queries that demonstrated you can get data directly from the O Data interface, via the C Data driver.

We showed you, a detailed demonstration of exactly how to build a pretty simple query, to do something like read the GL Entries from Business Central.

And lastly, we showed you that these queries can be constrained with parameters like the G L Account number.

This shows that Excel reports can be created that will contain just the data that is required for the person reading the report.

I believe that is a reasonable summary of what we have shown you today.

This concludes our demonstration today.

You can see from our demonstration that it is very simple, and very easy to query Business Central directly using the Meta5 Graphical Query tool.

You can see from previous demonstrations that it is possible to create custom Excel reports from Meta5, and send them to the individual business user who needs the reports.

Most importantly, Meta5 allows you to answer any questions from Business Central quickly and easily.

If you would like to ask Mihai questions about how to try this out for yourself?

Please click the Contact Us button below.

You can contact Mihai directly, or you can use the “Ask Us Anything” form on our Contact Us page.

And with that?

I would like to say thank you very much for watching our demonstration today.

I really appreciate your time and attention.

We hope you found this video interesting and informative.

We would love to hear your comments, so please feel free to use the comments section below.

I wish you a great day.

Thank you.

Sofi.

Your BIDA AI Assistant.

Contact Us