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 really pleased you have come back to see our latest video!
I am Sofi, your BIDA AI Assistant.
I will be reading this blog post for you today, on behalf of Mihai.
In this blog post we would like to show you how it is possible to create Excel reports, from Business Central on premise, using direct query.
This demonstration is intended to be viewed by business analysts, who are responsible for asking questions and coming up with new business proposals, based on the answers to those questions.
For this reason, the demonstration will be less technical.
If you would like to ask your IT staff who support you, to read a more detailed demonstration, you can give them the link on the button below.
Demonstration
And now, on with the demonstration.
Here we are on our Meta five evaluation machine on Azure.
You can get access to this machine by contacting us and booking time on the machine.
We would like to show you a number of things.
Firstly, we will show you the Excel workbook, with the finished data in the workbook.
This is so that you know what the target data set is.
Then you can more easily understand the other things we will show you.
Here we are, inside the Meta5 capsule, that can be scheduled to run over night.
At the bottom right hand corner, we can see the Excel workbook.
So, we will open the Excel workbook.
Now I will show you each of the worksheets.
In the month worksheet, you can see we have a primary key, first day of month, month name and month description, as well as many more fields.
In the general ledger account worksheet, we have a copy of the business central general ledger accounts.
In the general ledger document types workbook, we have manually defined document types.
In the general ledger beginning of time account balances workbook, we have the data from the general ledger entries.
Now, we will go into the power pivot model, to show you that the data is also in the power pivot model.
We will just click on each table, to show you that the data is in the power pivot model.
We will also show you the power pivot model, so that you can see that the fact table is joined to the dimension tables.
You will notice that the keys on the fact table are at the end of the table, rather than at the front of the table.
This is because of how Meta five joins data.
This makes no difference to the performance of the power pivot model.
What we would like you to understand, from this portion of the demonstration, is this.
As long as it is possible for your business central system to support the query, you can get any data you ask for, into Excel.
The best ways to deal with such data is in Excel Tables, or Power Pivot Models.
It is important for us to mention that this is not as good as having your own data warehouse.
However, if you do not have a data warehouse, this is the best possible way to get data into Excel, or power bee eye, for complex questions that can not be answered other ways.
Next.
Now we will go back to the VM that this demonstration is running on.
We will show you that there is a Business Central demonstration database, for version 23, installed on this machine.
You can see the tables inside Business Central.
Don’t be too concerned about the strange names.
We will scroll down to the General Ledger Entry table.
We will select the top 1000 rows.
You can see the names of the columns and the data returned.
You can understand this is a demonstration copy, of what you will have installed, at your company.
Next.
We would like to show you that we have a direct query database, where the names of the tables are much easier to understand.
We will scroll down to the General Ledger Entries table.
This is so you can see the difference between the Business Central Table, and the one in this query database.
We will select the top 1000 rows of this query General Ledger Entries table, so that you can see it is the same data.
The renaming is just to make it faster, and easier, for technical people to write the SQL, to answer your questions.
We will just show you the view, that allows this simpler querying, of the database.
You can see on the left hand side, the business central names.
You can see on the right hand side, the easier to read names.
In the view, from clause, you can see the real name of the business central table.
Next.
We would like to show you that it is possible to add extra data, into the hosting master database.
This extra data can be joined to your Business Central data, or any other data, that you have access to.
The most common use of this extra data, is for fields for descriptive information for days, weeks, months, quarters, or years.
In the demonstration we will now show you the view in the hosting master database for days.
We will show you the view in the hosting master database for weeks.
We will show you the view in the hosting master database for months.
We will show you the view in the hosting master database for quarters.
We will show you the view in the hosting master database for years.
Your IT people can put any data you wish, into this database, to help produce better reports.
You can have other common tables put into this database, such as gender and salary ranges.
Because operational systems are often missing descriptive data, we have made it possible to store such descriptive data, in this hosting master database, and then use it in reports.
You can also use spreadsheets to load descriptive data into reports, that you can write or ask your IT staff to write.
Next.
As you know, one of the reasons that companies create data warehouses, is to allow business analysts like you, to answer your own questions.
Operational systems have databases that are too complex, for most business analysts to understand and query.
There is a significant chance that a business analyst will get the wrong answer from the database, if you write your own SQL, against an operational system database.
So, what you are about to see is a compromise, that allows you to get your questions answered, without your company having to invest, to build a full data warehouse.
You have seen the example workbook earlier in the demonstration.
We will briefly show you the query, that produces the resultant data.
We will scroll down this SQL.
You are welcome to stop the video and review the SQL.
You can also ask your technical support, to download it from our website, if you really want to read it.
You don’t need to worry about this SQL.
All you really need to know about the SQL code is that you, and your peers, can not write this code.
This code needs to be written by IT people, who have profound knowledge and experience, of Business Central.
However, what you can do is ask for this code to be written.
This code can generally be written in a day or two, depending on how complicated it is.
You have a choice to ask your own IT department, to write such code, or ask a third party, such as BIDA, to write this code for you.
The important part is that the code can usually be written quickly.
You can get your answers to your questions quickly.
If the report answers your questions, and you want the report to be completed and put into a regular schedule?
Then you can work with your IT department, or a third party, to improve and finalize the report.
Once you are satisfied with the report, you can have it added to the daily schedule.
Next.
We would like to go back and show you the capsule, that is used to create the report.
This is just to show you that it is not that difficult, to create such a capsule.
It is more difficult to write the SQL query, against the Business Central database.
The first line is fetching the SQL from the hosting master database.
SQL file one is for the query against the GL Entries.
SQL file two is to get the GL Account table itself.
The second line runs the SQL one file to fetch the GL Entries, producing the temporary beginning of time balances data stream, and sending it into a spreadsheet.
The third line fetches the month level keys, so that the posting date can be linked to these keys.
The join icons put the integer keys from the lookup tables into the data stream, for the beginning of time balances table.
The fourth line fetches the general ledger document type keys, to be able to join the document types, to the beginning of time balances data stream.
The fifth line runs the SQL two file which fetches the GL Account table, and puts it into the general ledger account, as a dimension table.
You don’t need to worry about the column select, remove header, and compress, on the fifth line.
You can just take it to be a small amount of data manipulation.
On the sixth line, you can see we fetch the month data from the host master database.
And on the seventh and last line, you can see we fetch the general ledger document type table, from the host master database.
You can see all this data is collected, into the collect data spreadsheet.
It is then sent into the Excel regions in the workbook.
Now we will just run the capsule as part of the demonstration so you can see it run.
This sort of report would be run over night.
You will simply have the resulting spreadsheet in your one drive each morning.
With Meta5, your IT department can create Excel reports from the underlying Business Central database, much more quickly than any other approach.
More importantly, third party vendors will be able to create complex analytical dashboards for Business Central, and sell them on the open market.
Given this, it is in the interest of all Business Central on premise customers to ask for, and encourage, third party vendors to compete, to develop dashboards similar to the one we have demonstrated today.
For our part, we are willing to make this limited time offer.
We will write dashboards for installed Business Central accounts against the demonstration database, for no charge.
This is as long as we are able to take that intellectual property, and resell it to other Business Central accounts.
We can create them on this Azure VM.
You or your technical people can review and evaluate the report, as it is being developed.
In this way early adopters can get free applications in a win-win situation.
If we are allowed access to your Business Central database, then that will help.
However, we can write most reports against the demonstration database.
Then you can test the query against your Business Central database.
This concludes our video demonstration today.
In Summary
In this demonstration video we have shown you that it is possible to write a complex Excel report against your on premise Business Central database faster, at a lower cost, than has ever been possible before.
Even a report as complex as a beginning of time, monthly level, general ledger report, can have the underlying queries written in a day or two.
Then the development of the Excel dashboard can be done.
Further, third parties can develop these reports, and sell them in to the marketplace at a very low cost.
All this is to say, Meta5 changes the game for getting an answer to the just thought of question, for Business Central installed accounts.
You can get answers to questions that you have never been able to answer before, in hours, or a day or two, using Meta5.
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.