Update: 2023-09-22.
We have now tested 60K character SQL Statements and can confirm this is working.
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 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 still have Navision 2018 installed on premise.
We are also working on a similar demonstration for Business Central on premise, and in the cloud.
This video is a follow up to the prior video demonstration of Meta5 accessing the Navision 2018 demonstration database.
In the prior demonstration, we showed you how to query Navision 2018 using the Meta5 query tool.
The query tool reads the Meta5 dictionary to know how to query the tables.
Of course, if you know Navision, you know that sometimes the queries are very complicated.
You would know that you can’t expect a simple, easy to use, graphical query tool, to build the sort of complex SQL that is sometimes required to ask Navision a question.
The obvious question that arises from the prior demonstration is this.
“If we have a query that is too complicated to be created in the Meta5 query tool?
Then how do we run that query against Navision and get an answer?”
Well, I am glad you asked me that question, because that is what our video is about today.
So, as ever, on with the demo!
Demonstration
Here we are on the desktop of our Meta5 development machine.
This demo has not been put onto our evaluation machine yet.
At the top center of the screen you can see a capsule called “Test Nav DQs”.
This means, Test Navision 2018 Direct Queries.
This is what we will demonstrate today.
I will just open it up so that you can see the contents of the capsule.
I will open up the file SQL001.
Here you can see the SQL that we plan to run against the Navision 2018 demonstration database.
We are going to show you how this SQL gets into this text file.
We are going to show you how the SQL is then run, and the results are placed into an Excel workbook.
The first thing I will show you, is that we have a folder on this development server, that has the SQL statement in it.
We have the statement visible in notepad.
I will show you that now.
You can see in the SQL there are some comments.
You can see the SQL is well formatted to be readable.
You can use any editor you like to create the SQL to be sent to Navision.
Now I am going to show you that there is a spreadsheet open.
We will look at the variable substitution sheet.
In the variable substitution worksheet, you can see that there is a variable called, Financial Management.
This has the value of the directory the SQL statement is stored in.
Next, I will take you to the SQL Statement Load worksheet.
You can see that the load from directory and run time directory are both set to the variable called Financial Management.
You can see the File Name is set to the name of the file the demonstration SQL is in.
You can see the Load Flag is set to yes.
What this means, is that when this spreadsheet is processed, it will load the SQL statement into the BIDA dictionary.
From there, Meta5 can retrieve the SQL statement, and execute it, against the Navision database.
Next, I will take you to the processing worksheet.
Here you can see two processing directives.
One processing directive is, Load SQL Statement Comments.
The next processing directive is, Load SQL Statements With Pipe As Newline.
We are going to set both to “Yes”, the first time we load the statement as part of the demonstration.
Now we are going to go to the commands folder, and double click on the command, to process the spreadsheet.
This will happen in a flash, because the spreadsheet is very small.
Here we go.
Now we will go to SQL Server Management Studio, and show you the statement was loaded into the dictionary table ok.
Now I will copy the SQL statement, from the statements table, into notepad.
We can change the pipe characters to new lines.
We can recreate the statement pretty much as it is in the file.
That is just to show you that the statement really is loaded.
Now we will set the two parameters mentioned to, no, and we will re-run the command to process the workbook.
Now I will copy the sql statement to a new window in notepad.
This time you can see that the statement has no comments in it, and the pipes for newlines, are replaced with blanks.
This is done because Meta5 passes the SQL, to the ODBC connected database, “as is”.
There can be no comments, and no newlines, in the SQL statement sent to the ODBC connected database.
So.
Now we have the statement as we want it in the BIDA Dictionary.
We want to take that statement, run it against Navision, and get back the results.
Then we want to send the results into an Excel table, so that it is possible to load the data, into the Excel PowerPivot model.
Now we will go back to the Meta5 desktop.
We will click, once, on the query icon called “Get SQL”.
Then we will select the options for “Get SQL”.
Then we will click on “other data”.
Here you can see the name of the ODBC connection.
We will go over to the ODBC Administration Application.
We will click on the ODBC connection and click on configure.
You can see the server name is META5DEV01, which is this machine.
But, of course, that can be any server that is visible to this desktop.
Now we will close the icon options.
Now we will open the query icon called “Get SQL”.
We will click show data in the query icon, to retrieve the SQL statement from the dictionary.
You can see from the demo, that we have the name of the directory, and the name of the file, to retrieve the SQL.
We also have the current flag set to 1, so that only the current SQL Statement is retrieved.
This SQL statement can be up to 64K in total.
You can see there are 8 segments of SQL statements allowed and each segment can be up to 8K.
Please note.
Meta5 currently has a much smaller limitation of the length of a SQL Statement.
This will be increased in the future.
Now we will close the query icon.
We will click on the query icon, and run the steps to put the SQL in to the text document, called SQL001.
We will open the text icon SQL001.
In the text document, you can see that the SQL statement has made it through to be stored, in the text document safely.
You can see there are lots of blanks, and tabs, because these are not removed in any way.
Now we will close the text icon SQL001.
Now we will click once on the icon called Run SQL.
We will option it and then click on “other data”.
You can see the name of the ODBC connection, in the Database Name field.
Now we will go over to the ODBC Administration Application again.
We will select the ODBC connection and click on the configure button.
Again, you can see that the sql server that this ODBC connection connects to is META5DEV01.
However, it could be any server visible to this desktop.
Now we will click cancel on the DSN configuration panel.
Now we will close the icon options.
Now we will click on the icon called Run SQL to open it.
You can see the start of the SQL statement in the icon.
We will click on show controls, to show you, that the text file it is getting the SQL from is SQL001.
You can see that the Run SQL icon has the setting, “Obtain Program Source From”, set to SQLQ001.
This name only needs to be unique inside this capsule.
You can see there are some other options for the controls of this icon.
Now we will close the controls for this icon.
Now we will click run on the Run SQL icon, just to show you that it runs the SQL, against the Navision database.
We will scroll to the right to show you the data returned.
Now we will scroll back to the left.
Now we will close the Run SQL icon.
Now we will run just the template icon.
This will overwrite the target spreadsheet.
It happens very quickly so keep a close eye on it.
We will open the target spreadsheet, to show you that there is only one row of data in it.
Now we will close the target spreadsheet.
Now we will run the whole capsule.
It will fetch the SQL statement again.
It will then run the SQL statement, and send the results, into the Excel workbook.
So here we go.
Keep a close eye on the icons that are running.
You can see it only took a second or two.
Now we will open the spreadsheet again.
Now in the spreadsheet you can see there are many rows of data.
These are the rows returned by the query.
We can also have parameters in the query, such as the date range for transactions.
We can put a parameter on any value in the where clause if we want to.
For example, if you are a retailer and you have 10 region managers who get reports.
You can run the same capsule 10 times over, and produce the customized report, for each region manager.
You can also retrieve any data from any ODBC compliant data source, and bring it into your spreadsheet, including using parameters.
Now we will close the spreadsheet and close the capsule.
This concludes our demonstration today.
Summary
I would like to summarize what we have demonstrated to you today.
What we have shown you, is that you can write any SQL statement you like, to get data from Navision.
Indeed, you can write any SQL statement, that can be executed against any ODBC compliant database.
You just have to make sure, that if a column has a strange data type, you cast it into a simple data type, that Meta5 can read.
That SQL statement can be loaded into the BIDA dictionary, using a simple excel workbook, and command file, in windows.
You can remove the comment lines, and the new lines, using options in the excel workbook.
This means your SQL statement can be quite human readable in the file, and then the loaded statement is database readable.
You can then run a capsule to read the SQL statement, from the dictionary, execute it against the ODBC compliant database, and send the results into an Excel table inside Excel.
From there, the data can be used like any other data inside an Excel table.
You can provide parameters in the SQL query so that you can repeatedly run the query and get different results.
You can send the different results to different spreadsheets, and then on to different business users, using one drive as the delivery mechanism.
These features have all been demonstrated in prior videos.
In short.
What we have shown you today is a way to write complex SQL statements, against the Navision database, to ask the most complex questions.
You can do all this without downloading reports from Navision, and cutting and pasting data, from the Navision reports into Excel spreadsheets.
This can all be fully automated, and run on a daily basis, so that all your reports can be produced every morning, and delivered to the relevant business users via their one drives.
We believe this extension of our direct query capability, using the Meta5 Query tool, solves all the most common problems in trying to create new reports on data held in Navision.
These new reports can be written at a fraction of the time, and cost, of asking your IT department to write the report, and integrate it into the Navision system.
What we have shown you today, is a way to save a great deal of time, and money, in creating custom Excel reports, and getting data from your Navision system.
We hope you liked this demo.
We hope you are interested in what we have shown you.
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.
If you would like to get in touch with Mihai, his contact details are on our contact page.
I wish you a great day.
Thank you.
Sofi.
Your BIDA AI Assistant.
