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.
Demonstration
Here we are on our main BIDA development machine.
We are inside the Business Central twenty twenty three Direct Query Database.
You can see that there are three thousand and sixty five views in this Business Central Direct Query database.
That is a lot of views.
This is how many views are needed over the top of Business Central to get even the first few fact tables to work.
Just so you know.
Because this is our main development machine we are hiding many database names.
Now.
This demonstration is a little bit technical, so I would like to firstly tell you the two problems that we have solved.
Problem number one.
Today everyone is talking about artificial intelligence.
The number one problem with AI in the enterprise is that the data inside the enterprise is housed in large operational systems.
These are usually E R Ps, but telcos have billing systems, banks have banking systems, and so on.
In 2024 we live in an era where practically all actions inside a company are tracked by putting transactions into computer systems.
Those systems can be generally referred to as Large Operational Systems.
The data models inside these databases are so complicated, that trying to train an AI using these data models has proven extremely dangerous, in that bad data fed to the AI provides bad results.
The first problem we have solved is the problem of making sure the data being sent into the AI, from the large operational system, is correct and good data.
Or, at least, we have vastly reduced the likelihood of errors.
Everyone who wants to do AI inside the enterprise will want to watch this video.
Problem number two.
In the eighties, relational databases were touted as the next big thing.
Relational databases, we were told, would allow business people to ask their own questions of their databases.
“All business people will learn SQL. They will answer their own business questions”, was the pitch from IBM and Oracle in particular.
Just like COBOL before it, this didn’t happen.
As the eighties went on, and then the nineties went on, operational systems exploded in processing complexity and in the number of tables and columns they contained.
In the late eighties, the era of the data warehouse was born to put all this data back together in some consistent form.
This was to make it possible to ask questions from all these operational systems, that were costing tens of millions of dollars to implement.
These systems have data models that even experts in the systems struggle with some days.
It is simply not possible for a business person to ask questions of large operational systems, and hope to get a correct answer.
Dimensional models for data warehouses made life that much easier.
Business people think in dimensions, it is natural.
So dimensional models became the standard way business people interacted with their data.
Now, the first thing I am going to show you are some dimension tables over the top of Business Central.
Please note, when I say tables we include views so that we do not have to make that distinction over and over again.
Please, remember, this could be any large operational system.
Now, on with the demonstration.
I am going to filter the views displayed to those that begin with the letters V M. .
These are dimension tables.
From the views list at the bottom of the video you can see there are one thousand one hundred and sixty four views that begin with V M. .
That is a lot of views.
These are all the tables that can be interpreted as dimensions in Business Central.
We will be the first to admit that because there are well over one thousand one hundred views, there are likely to be some mistakes.
We have put a dimension table view over the top of every table in Business Central, that we think might be queried as a dimension table.
We have a process that makes it quite easy to add more views.
We will go down to the item table because everyone understands companies sell items.
We will view the items.
Here we are with the items in the Business Central database in front of us.
You can see that there is something called a primary key for the item table.
It is a sequential number.
This number goes into your Excel workbook for the power pivot join to fact tables like sales transactions.
You can see the item number.
You can see the demonstration database is selling bicycles and bicycle parts.
I will scroll to the right so that those of you who do not know Business Central, can see how much information is stored about items.
Now we will go to item categories.
Now I will open item categories.
You can see there are some categories for office supplies.
Now, since this is a presentation for business people, I do not want to get too technical.
On the first row of the item categories you can see there is a default row.
It has a key of zero and the data in the columns are defaulted.
This is one of the design features of dimensional models.
They have a default row for when a join is not found.
So, if an item does not have a category, and many of the bicycles do not, you do not lose the sales row when you are trying to look at the categories.
Also, you are not stuck with a null.
You get a specific value of not applicable which is much easier to understand and work with.
This means you are told bicycles have quote, not applicable, end quote, as their item category.
This zero row is very important.
This is how we make sure no data is accidentally lost, and no extra rows are accidentally generated, by queries.
Because you will be able to download the database, and give it to your I T colleagues, there is no need to explain this to your I T people.
They already know about these things.
Now, I want to take you to some other tables that you will understand immediately.
The next table is the day table.
Now, I will open the day table.
It is not in order.
Now I will scroll to the right to show you the columns in the day table.
Now I will go to the month table.
Now I will open the month table.
You can see that the month table only has rows at each month level and the day table has rows for each day.
So, when you want to query data at the month level, you go to the month table and when you want to query data at the day level you go to the day table.
We have added tables to this query database to extend the functionality of the data model.
For example, we have a table for age bands.
If your large operational system records the birth date of your customers, it is possible to link them to the age band table for analysis by age bands.
There are many such tables in this model.
Now I will change to the views of fact tables.
As you can see, we only have eight fact tables defined at the moment.
We wanted to get this first video out to the public as fast as possible, because this is so revolutionary.
I want to show you the sales invoice line table.
Of course, companies sell items and the sales are recorded on the sales invoice lines.
Now I will select the sales invoice lines table and I will talk about that.
On the video now you can see lots of numbers.
These are the generated integer keys, that are going to link the sales invoice line, with all the dimension tables that have descriptive information in them.
For example, you see sell to customer in the middle of the screen.
You can see the keys are numbers like 7 , 8 , 18 and similar. .
These keys go to the customer table from which you can get the details about the customer.
You don’t have to worry about how that is done, it’s done for you by us or your I T colleagues.
You can see that there are a lot of these keys.
This is a template that can be used to select just what you want.
You will understand that if you have this many dimensions to join to, that you can slice and dice your item sales lines in any way you please.
You can answer any questions that your database is able to answer.
Now, I will scroll to the right.
You will see fields like header bill to name, header bill to contact and similar.
What this view does is expose the sales header on the first half of the row, and the sales line on the second half of the row.
So now we will scroll further to the right.
Now we can see fields like line number, line location code, line description.
These are the sales invoice lines.
They are individual items that are being sold.
To the right of the screen you can see line quantity and line unit price.
What you are seeing is what is called a fact table.
One type of fact table records all the details about a transaction.
This is one of those transaction fact tables.
It is recording every scrap of data we can about sales transactions, and it is joining that data to all the dimension tables we can properly join it to.
So, essentially, what you are seeing is a set of views where you, or anyone, can query the sales transactions, and present data to any tool, and you can know you are not losing any data, or accidentally getting any extra rows.
For training an AI, this means you will have far less chance of sending the AI false information.
For querying, this means you will not be able to accidentally lose rows or add rows to your results.
So, let us take a look at the actual code behind this sales line.
This is just to show it to you, we do not expect business people to understand this code.
Your I T colleagues can download this code and see it for themselves.
You can see on the screen that there is a word called coalesce, and this is used for all these keys.
Where you see the zero, this simply means that if nothing was found in the database for this key, then default to zero.
Then, every dimension table has a zero row, that will join to this row, to tell you that nothing was found for the row.
So, when an item is sold like a bicycle, and there is no item category for it?
Rather than lose the sales row, you will get the sales row with a Not Applicable value for the item category.
Now, I will scroll down in this view.
You will see how long it is.
Now we are at the point where your I T colleagues will be more interested in this segment of the video.
But we just want to show this section for completeness.
You can see that we are selecting the data from the direct query database, and we are querying the sales invoice line.
You can see that we are joining the sales invoice line to the sales invoice header.
You can see we are doing a very long list of left joins.
You don’t need to worry about these joins.
Your I T colleagues will understand.
All you will want to know is that someone, like us, has prepared these joins for you to use.
I will scroll down to the bottom so you can see how many joins are here.
This view is 491 lines of code.
So, it is quite substantial.
Of course, for performance reasons you might not want to do all the joins for every query.
So, of course, your I T colleagues could create SQL that just reads the data you need to read, to save processing time.
Now, I will scroll back to the top of the view.
Now I want to show you how such a data model can be queried and the results sent into Excel Power Pivot Models.
We are on our Meta five development desktop.
We have a very simple capsule in front of us.
The simplest way to take advantage of what we have now created is this.
Someone who knows the large operational system can write efficient SQL and store that SQL in a database.
The Meta five capsule can read that SQL, set any variables like begin and end dates for the report, and send it to Business Central.
Business Central can then return the dimension tables and the fact tables that are mentioned in the SQL queries.
These data streams are sent into the collect data spreadsheet.
Then, when all data has been collected, Excel is started inside Meta five and all data is sent into the correct worksheet inside Excel.
From the worksheet Meta five sends commands to Excel to refresh the power pivot model.
Then Meta five can send a command to Excel to delete the worksheets the data was sent into.
What you end up with is an Excel workbook containing a power pivot model with the data you want.
From that position you can do anything you like because you are an expert in Excel.
There are many reasons you want to use Meta five to put your data into your power pivot model.
However, we won’t go into them in this video.
You can just trust me that you can have Meta five run batches of reports, each night, and it can send out the updated Excel spreadsheets to whoever needs them.
It is also possible to use the Meta five query tool to query these dimensional models.
We have not yet created the dictionary needed to support that.
Also, if you are querying your production large operational system?
Your I T colleagues will want to review any SQL that is being sent into it, to make sure that no harm will be done.
In Summary
Now I would like to explain what you have seen because it’s pretty amazing.
As far as we know, no one has ever tried this before.
This is because the idea itself is simply so out of this world.
People have been doing direct query for a long time.
But no one has created a layer of views that allows the direct query to operate as a pseudo dimensional model.
At least we have never heard of such.
The original question was.
Is it possible to put a dimensional model over the top of Business Central, and make it work?
The answer is now proven to be yes.
This also means that it is possible to put a dimensional model over the top of any large operational system, and make it work.
That dimensional model can sit over the top of the production database, or it can sit over the top of a replica database.
So, all that you need to get started using this idea today, is a set of views and a Meta five workstation.
Then you can send the queries into your large operational system, and send the results into Excel power pivot models.
With Meta five, and this idea, you can get data out of your large operational system and into your Excel workbook, where you can analyze it in any way you want.
If you are using Business Central twenty twenty three?
You can get started today from what we have provided on this blog post.
Your I T colleagues can download the demonstration databases and get started today.
With this idea you will be able to get questions answered in minutes, or hours, rather than days or weeks.
If your database server is fast enough?
You can even use the Meta five query tool directly and not even need SQL to be written by your I T colleagues.
Then you can get answers to your questions in minutes, without having to ask anyone else for help.
Of course, we are going to continue creating views for the fact tables in Business Central.
This is time consuming work.
And it means that all Business Central users will be able to use those views.
What you have seen today is the new “entry point” to being able to get questions answered from your large operational systems more easily.
We want to make sure you understand this is not as good as having a real data warehouse.
We want to make sure you understand this is a short cut that allows you to get started very cheaply.
If you run these views on your production business central server, the only outlay for you is a meta five workstation.
If your I T colleagues say that you must run these queries on another server because of performance issues?
Then your outlay is for another server, and another SQL Server Standard Edition license.
Then your IT colleagues can turn on replication from your production Business Central, and send the production updates into your replica database.
Today you can buy a 16 core machine, with 164 Giga Bytes of memory, and 10 terra bytes of solid state disc, and the SQL Server Standard Edition License, for approximately one hundred thousand euros.
With a three year commitment, you can rent such a machine on Azure for around three thousand euros per month.
That is a very cheap entry point, to be able to answer any question you can think of, from your Business Central database.
One last point we want to make is this.
You might want to create a staging area rather than just use a replica of Business Central.
If you want to do that?
We will create staging areas, for free, for the first ten qualified customers.
After the first ten qualified customers we will charge one thousand euros to create your staging area for you.
We will give you all the software you need to run your staging area.
If you then wish to sign up for technical support>
We will charge very reasonable fees.
We have done another post on why you might want a staging area rather than a simple replica production database.
We can share that with you if you are interested.
In finishing.
Ladies and gentlemen, we have come to the end of our very big announcement.
We are more than a little excited about this.
This is a big idea whose time has come.
We think a lot of people are going to adopt this idea.
If you do adopt this idea?
We would please just ask that you give us proper credit when you mention it to other people.
We at BIDA invented the idea, so please respect our efforts.
We thank you in advance for doing so.
When we started BIDA we had the idea that BIDA was going to be a well known brand world wide like Nike, or Google.
Nike has the slogan just do it. .
You google things on the internet.
We set out with the vision that BIDA will come to mean, you ask your data a question.
BIDA your data today, is the idea we had when we founded our company.
With this idea of pseudo dimensional models over large operational systems?
You can BIDA your data today.
You can ask your large operational system any questions you want, and you can get answers back in minutes or hours rather than days or weeks.
To BIDA your data is to ask it a question, and get the answer you need to the “just thought of question”.
We are very pleased, and proud, to bring you this idea.
You can download the demonstration databases from the buttons on the blog post.
If you do not have the Business Central twenty twenty three demonstration database?
We have also included the download for it on a separate button.
Your I T colleagues can restore the Business Central twenty twenty three demonstration database, and the direct query database, on a development SQL Server for you.
Then you can review it to understand how this all works.
Your I T colleagues will understand what has been done.
They can also explain it to you.
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.
