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.
(Note. We have swapped my photo for the cartoon character. I hope you like it!)
Hello and welcome to our latest blog post.
I am Sofi, your BIDA AI Assistant.
I will be reading this blog post for you today, on behalf of Mihai.
I am really pleased to be able to bring you some news that I think will really interest you.
This video is intended for companies who have Business Central 2023 installed on premise.
This video is a follow on from the previous videos, that were talking about being able to query Business Central using Meta five and sending the results into Excel.
So, as ever, on with the demo!
Demonstration
Here we are in the Excel version of the Balance Sheet generated out of Business Central.
You are welcome to download this workbook, and all the related materials, from the button below.
What we want to show you is that we have the categories and lines in the Balance Sheet, that are defined inside the Business Central demo system.
Now I will show you that this data is in the pivot table inside this workbook.
Since you can download this workbook and see the results for yourself, we will make this demo very brief in terms of showing you the power pivot model.
Now I will go to power pivot in the menu bar.
Now I will click on manage the data model.
Now I will click on the month table.
You can see the months.
The first day of the month is cast into the internal integer in Excel which is not an issue.
Now I will click on the account schedule name table.
This is in the workbook redundantly just for demonstration purposes.
Now I will click on the account schedule lines.
This is to show you that the account schedule lines are available in their own dimension table.
Lastly, I will click on the fact table in the power pivot model for the account schedule lines.
When I scroll to the right you will see the various amount columns.
When we scroll to the bottom we will see these amount columns contain values.
Now I will filter on Balance Sheet and scroll to the bottom.
You can see that there are values in the amount and credit amount columns.
These amounts are coming from the underlying demonstration database for Business Central.
Now I will click on diagram view.
You can see from the diagram view that you have the four tables mentioned when I described the data that was in each table.
The important thing for you to understand is this.
This data has come directly from Business Central using only Meta five and SQL.
The SQL was written by hand.
Meta five then sent the SQL to Business Central and put the results into this workbook.
What we want you to understand is this.
Any question you have that you would like to get answered from Business Central?
You can now get that data into the Excel Power Pivot model to answer any business question you have.
You do not need to have the report written in Business Central.
All you need is someone who knows the Business Central database, who knows Meta five, and who knows Excel.
They could be the same person, and they could be different people.
Now I will close the power Pivot Model.
The demonstration has two different worksheets in it.
We will just show you that we can click on the drill for 2024 and we will get the detailed rows.
Now I will click on the plus sign next to 2024.
You can see the totals for operating equipment and operating equipment depreciation to date.
The numbers are not exactly as you get in the demonstration database as we have not included all the rows in this demonstration.
We did take some short cuts in the SQL as we just wanted to demonstrate what was possible.
Now I would like to show you how this workbook was created.
We will go across to our Meta five development machine.
Here we are on the desktop of our Meta five development machine.
You can see a capsule on the video.
This is the sort of capsule that one of your IT people can create, or we can create, for you.
Across the top of the capsule you can see 4 query icons with the name “Get SQL”.
These icons will retrieve SQL that is stored in your master database.
Your master database can be in the cloud and supported by us if you wish.
I will open the first get SQL Icon and show you what is inside it.
You can see variables like at A V, at A W, at A A, and at A C. .
These variables allow us to write a capsule on our development server and deploy it, without any changes, on your Meta five server.
Basically, these variables mean that we can create capsules on our development server and sell them to any customers who have the system that the capsule is intended to be used on.
For example, we can create capsules for Business Central on premise users, and that capsule can be used by any Business Central installed account.
You can see the name, B C 3 9 9 9, read V M month, zero one.
This is a piece of SQL that lives in the master database.
Anyone who uses this capsule would use that piece of SQL.
In this way we can write SQL once, store it in the cloud, and all of our customers can use that piece of SQL.
Now I will click on show data.
It is a little hard to read the SQL returned.
But you can see that a select statement was returned.
Now I will close the query icon.
Now I will open the SQL one text document.
You can see that this is the select for the V M month table.
This SQL is in the download available from the button on the blog post.
This SQL will return the data that will eventually go into the power pivot model.
Now I will open up the text file, SQL one zero one.
You can see this is simply a select statement from a view to retrieve the G L account schedule lines.
You can then see icons called Run SQL, for each of the four text files.
These icons send the SQL retrieved from the master database to Business Central, and return the data into the spreadsheets next to them.
These four spreadsheets are redundant and are only in the demonstration for demonstration purposes.
I will now open the fact table spreadsheet that contains the account schedule lines.
You will see that the data has been returned by running the SQL that was in the master database.
I will just scroll around the spreadsheet and you will see the data that has been returned.
Now I will open the collect data spreadsheet.
I will scroll to the right and you will see there are various sets of data returned to the collect data spreadsheet.
All the data that is needed in the Excel workbook is sent into this collect data spreadsheet.
Of course, you could do much more manipulation of the data inside Meta five if you wanted to.
Eventually the data is sent into regions inside the Excel workbook.
We wanted to show you this portion of the demonstration for these reasons.
Any SQL that any SQL developer can write, can be stored in the master database.
That SQL can be read, parameters can be resolved at run time, and it can be sent to Business Central.
To create a power pivot model in Excel, the SQL developer needs to write the SQL statements that will return the data needed in the Excel Power Pivot Model.
Because this SQL can be very complex, as we will show you, you can get any answer that Business Central can provide, and Excel can present.
The limitations on your questions are SQL, Excel, and your imagination.
To make it even easier to get the answers you need from Business Central, we will soon be publishing dimensional models over the top of Business Central.
We have done a lot of work in that direction, and we just have to publish the demonstrations we have developed.
Now I will take you to the view that creates the lines for the fact table, that are then put into the power pivot model.
As a business person, you will not be able to read this SQL.
It is very complex.
However, excellent SQL developers only have to write this piece of code once.
Then it can be read from our cloud master database and used by any customer.
The SQL is in the materials that you can download from the blog post.
You can give it to your I T support to show them how complex the SQL can be to answer business questions.
Now I will just scroll down this SQL and then scroll back up.
In this way you can see that this is nine hundred lines of SQL.
In Summary
Now, I would like to summarize what we have shown you today.
We all know that generating the Balance Sheet, from the definitions created inside Business Central, is an important function delivered by Business Central.
We all know that the actual Balance Sheet generation requires code inside Business Central.
We all know that the code to generate the Balance Sheet is complex.
What we have shown you today is that it’s possible for a good SQL developer, who knows Business Central very well, to produce the Balance Sheet using Meta five and Excel.
The purpose is not to produce the Balance Sheet.
The purpose is to show you that a report that can be produced, can be as complex as the Balance Sheet.
This demonstration proves that you can get any question you want answered from Business Central, using just Meta five and Excel.
The role of Meta5 is as follows.
One. To read pre written SQL statements from the master database.
Two. To perform variable resolution at run time to apply constraints to this execution of the SQL.
Three. To send the SQL to Business Central.
Four. To send the answers from Business Central into Excel.
Five. To re-load the power pivot models in Excel from the new data.
Six. To send the finished Excel workbook to the people who wish to have the workbook.
One of the most pressing problems in Business Central installed accounts, is getting the data from Business Central into Excel, so that business questions can be answered.
If you are in the position in your company that you have questions you want answered from Business Central, but your I T staff are telling you that it is very expensive, or impossible?
Then you might like to try out Meta five, and see for yourself how easy it could be.
We have an evaluation machine that has the Business Central 23 database installed.
You can test that out for yourself.
If you want Meta five on your premises to try this out?
We can arrange for a thirty day trial copy that can be installed on a VM on your premises.
We believe that once you play around with Meta five for a few weeks, you are going to want to have your own copy on your PC.
If you would like to get access to our evaluation machine to try Meta five for yourself?
Then please contact Mihai to book your time on our evaluation machine.
And with that?
I would like to say thank you very much for watching our video today.
I really appreciate your time and attention.
We hope you found this blog post 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.
We will be emailing our subscribers useful and
valuable information regarding business intelligence.
Please subscribe here to be on our general emailing list.
