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 all business people, who work at a company, where you are frustrated that it is so hard to get answers from your operational systems.
If you are a business person, and you are frustrated that your I T department is telling you it takes days, or weeks, to get answers to questions that are urgent for you?
Then you will want to watch this video.
We are using Business Central twenty twenty three as an example, but what we will present today applies to the entire world of getting questions answered, from operational systems.
So, as ever, on with the demo!
Please note, because of their size the demonstration databases are stored on our public one drive. Anyone can download the two databases, but microsoft will require you to be logged into an outlook account or an office account for the link to work.
Please note the direct query database has been updated on 2024-07-07. We will update this note each time we update the direct query database.
Demonstration
Here we are on our main BIDA Azure Meta five evaluation machine.
You can get access to this machine by contacting Mihai, to book the machine, and get the login details.
I want you to know that you can go and try this out for yourself today.
On the button below you can download all the materials you will see in this demonstration.
So what are you looking at?
I will just go up to the top so you can see the IP address of this machine.
I will go to a command prompt and show you the IP configuration command.
You can see it’s a machine in the cloud.
I will also go to SQL server and show you the databases.
In this first window you can see that in the bida hosting services master database, we have one thousand seven hundred and ten SQL files loaded into the database.
Now I will click on explore object details.
You can see there are three thousand five hundred and seventy five views, available in the business central direct query database.
This is the number at the time of this video recording.
It will be larger if you review this machine for yourself.
Now I will go to the Meta five desktop.
This is a very clean desktop because it is the evaluation machine.
In the top left corner you can see the applications file drawer.
Now I will open up the applications file drawer.
We currently have applications only for Business Central.
Now I will open up Business Central.
We see there are a number of file drawers available to us.
As a business person you are only interested in what is in the first file drawer, the dashboards.
Now I will open the dashboards file drawer.
You can see there are currently fourteen folders.
These are fourteen reasonable sub sections of business central.
I will open up twelve, sales.
You can see the capsule B, C, twelve, zero, zero, three, sales lines.
This is the capsule we want to show you today.
Now, I will go into the sales lines capsule.
To make it possible to sell capsules over the web, and have them get their parameters from the customers master database, there is some technical stuff that needs to be done.
You can completely ignore this.
The capsule itself is embedded inside some wrappers, so that it can be reused by many customers.
So, I will just drill down to the capsule because that is what you are interested in.
Here we are inside the actual capsule that is of interest to you.
What can you see?
You can see lots of squares joined by arrows.
So, this capsule creates an Excel workbook, containing sales lines from the business central twenty twenty three, United Kingdom demonstration database.
The capsule also sends all of the associated dimensions into the workbook.
You can see there are fourteen capsules that are called get SQL.
We will go into the first one and show you how it works.
Now I will open the first get SQL icon.
You can see the variables at A V, at A W, at A A, and at A C. .
These are set when the capsule is installed at your site.
You can see B, C, twelve , nine, nine, nine, V M, order date SQL.
This is the name of a file of SQL that has been loaded into the master database.
Now I will option this SQL name.
Now I will press show constrained choices.
You can see that there are many files of SQL that the query icon knows about.
Of course, this list will get much longer as we develop more applications.
We can select any piece of SQL to be read by Meta five.
Now I will close the options windows.
Now I will press show data.
You can see that it has returned some SQL that is going to be used to read the order date dimension table.
This SQL can reside anywhere.
It can be inside your company, or on a machine in Azure.
It is stored in a SQL Server database.
Now I will close this query icon.
Now I will open up the SQL zero, zero, one, file.
You can see that there is SQL in the file.
This is the SQL that has been retrieved from the master database.
Because this is our evaluation machine we have copied the master database to this machine.
You can go to SQL Server Management Studio and look at the database if you want to.
Now I will close the SQL file.
Now I will open the SQL one, zero, one, file.
In this file you can see that the SQL is a lot more complicated.
I will scroll down just so you can see the SQL in the file.
This SQL is hand written.
When it is loaded into the database all comments and new lines are removed.
Now I will close the SQL File.
Now I will open the icon, V M order date.
You can see that the SQL from the SQL file is inside the icon.
Now I will click on show controls.
You can see that the SQL entry icon is reading it’s SQL from the SQL zero, zero, one file.
That is how this works.
You get the SQL from the master database and put it in a file.
Then you refer to that file in a SQL Entry icon, which can run the SQL against the database.
Now I will close SQL Controls.
Now I will press run on the V M order date SQL Entry icon.
You can see that it has returned the data from the select statement, including the heading line.
So.
Every SQL Entry icon is followed by a remove header icon, to remove the column names.
Now I will close the V M order date SQL Entry icon.
Now I will scroll down just a little bit.
Now you can see the collect data spreadsheet.
Simply put, all the SQL retrieved from the master database is executed by the SQL Entry icons.
The heading row is removed, and all the data from each query is sent into the collect data spreadsheet.
From there the data is sent into the sales invoice line excel spreadsheet.
It is sent into Excel regions.
You can download this spreadsheet to see exactly what we have done.
For each query there is a worksheet inside the workbook.
The worksheet has a heading region and a data region.
We only send data into the data region, because the headings need to be fixed, from the time of development.
The next step, the I Excel icon, takes the data from the data regions, and loads them into the power pivot model.
This overwrites the data in the power pivot model.
It will also update all dashboards, reports, and pivot tables based, on the data in the power pivot model.
Then there is an icon called delete multiple sheets.
What this does is remove the worksheets, that were used, to send the data into the power pivot model.
This means that the only data inside the spreadsheet, is in the power pivot model.
This data is highly compressed.
The delete multiple sheets capsule sends the final workbook to the P C Directory.
This can be any folder that the server has access to, including one drives.
Lastly, if this capsule has a signal file defined for it, it will delete the signal file so that the signal file has to be replaced before this capsule will run again.
Now, I will run the capsule so that you can see it running.
Since the capsule has so many icons in it, you can not see the icons at the top of the capsule.
I will also turn on the task manager so you can see what happens.
We will not pause the video, so that you can see how long this takes on this evaluation machine.
So, now I will click on the run button.
We can watch the capsule execute together.
We will just wait for the capsule to run.
Of course, any capsule can be scheduled to run in batch, via the meta five or BIDA scheduler.
Remember, you can have your I T colleagues write any SQL you want.
You can send any amount of data into the spreadsheet, so long as Excel can handle it.
When the volume of data is large, it’s best to have many collect data icons, and to send the data into Excel in multiple steps.
Now I will go to the directory the finished workbook has been written to.
I will open it up.
You can see there is only the reporting worksheet in the workbook.
Now I will open the power pivot model.
Now I will click on the diagram view.
Now you can see the power pivot model inside the Excel workbook.
Now I will just click on data view again.
Now I will just go along the tables along the bottom of the workbook.
You can see that there is a table for each query that was fetched from the master database.
Now I will close the power pivot model.
Now I will close the spreadsheet.
Now I will go back to the capsule.
In Summary
Now, please allow me to summarize what we have shown you.
What we have shown you, is that you can ask your I T colleagues to write SQL.
You can store that SQL in your master database.
You can read that SQL from the master database using Meta five.
You can resolve parameters inside Meta five.
Then you can send that SQL to any ODBC data source.
In this case we are using Business Central twenty twenty three, as an example.
But it can be any ODBC data source, including those supported by C Data.
The data that is returned is collected in the collect data spreadsheet.
It is then sent into Excel.
It is then loaded into the power pivot model.
Then the worksheets used for the transfer are deleted.
So, in the end you get a spreadsheet, with the data you want, in your power pivot model.
From there?
You are an Excel expert.
You know what Excel can do once your data is in your power pivot models.
You have seen the future of direct query against operational systems.
You can have this today, for your company, for the very modest cost of one Meta five desktop.
The Meta five desktop can be paid for monthly, annually, or outright purchased.
We are not publishing the current prices, because we have an agreement for some steep discounts from Meta five, that they do not want published.
It is sufficient to say that the price of a meta 5 desktop, on a daily basis, is negligible compared to being able to easily get any data you want, into your Excel workbooks.
As I mentioned.
This is our Meta five evaluation machine on azure.
You can book time on this machine with Mihai, and review everything we have shown you here, to make sure that it works just like in the demo.
There are no “tricks” to this.
The largest piece of work is just the setting up the regions, and tables, in Excel, because Excel needs to be told these things.
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.
