BIDA0053 – BC Direct Query Using PowerBI Cloud

0

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 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.

If you are a business person using Business Central?

The really simple message for you is this.

We have invented a new and better way for you to get access to all your Business Central, On Premise data, through Power B I, in the cloud.

This new and better way also allows you to integrate any other data that Power B I can read, which is pretty much any data.

How we did this is a bit technical.

So, if you want faster, cheaper, easier access to all your data in Power B I to improve your companies profits?

All you really need to do is to pass this blog post along to your Business Central Technical Support people.

You can ask them if they can take a look at this and use it in your company.

By the way, this is all free.

The only bit you have to pay for is your Power B I Cloud Licenses if you don’t have them already.

Many Business Central On Premise customers want to use Power B I Cloud but it’s kind of hard to use.

We have solved the problem of, “it is kind of hard to use”.

Now, moving right along.

Details of Our Solution

This video is intended for more technical people who work with Business Central.

This is for those technical people whose business community want to use Power B I to access Business Central On Premise.

So.

Please allow me to start with a statement of what the problems are with using Power B I in the Cloud to access Business Central On Premise data.

The presumption is that the business community see value in having Power B I Cloud over having Power B I for Report Services.

The presumption is that the business users want to use Power B I as one of the tools for Business Central Reporting, and very likely, for reporting on other data not held in Business Central.

There are a number of issues with querying Business Central On Premise data.

Issue One.

The Business Central data model is very complicated.

To be able to answer any question you have to have access to all the data.

But there are over one thousand, eight hundred tables in Business Central. And that number is growing rapidly.

There are many cases where joins are complex.

By this I mean that the same field can be used to join to many different tables depending on the value of another field.

It is simply not possible to give anyone a copy of Power B I, the Business Central Data Model, and say, off you go, you have access to all the tables, go and write whatever report you want.

There are too many tables to manage in Power B I.

The joins between the tables are too complex.

This is why Business Central Programmers have to write code to produce even simple reports out of Business Central.

To even get the simplest of new reports out of Business Central, you need a programmer, not a Power B I developer.

Programmer time is expensive, not to mention you have to wait in the queue to get their time.

Issue Two.

Even if you could deal with issue number one, and some how find a way to simplify the joins, there are just too many tables to deal with using Power B I.

Even with the O Data interface, it quickly becomes obvious that there will be thousands of O data sources needed to query Business Central in the Cloud, or via the O Data Interface for On Premise.

That there are one thousand eight hundred tables, the vast majority of which are not related to each other, means that the number of tables presented to Power B I is overwhelming.

Not even the best Power B I developers, even with the best intentions and efforts, are going to be able to deal with that number of tables.

For a new report?

Until now, all roads have led back to the Business Central programming staff, their high expense, and their work backlog queue.

We have promised that we have solved these problems.

So, please allow me to explain.

Solving Issue One.

We have previously released a blog post talking about the idea of putting pseudo dimensional models over the top of the Business Central database.

We have been doing further work on this and we are finding that it works fine.

Sure, it’s going to consume CPU to run queries over such views, but it will be much easier to get the correct answer.

Of course, it will take a lot of time to build up the suite of views for direct query against Business Central.

Also, we have added the numbers zero one to the end of these views.

This is so that each company is able to create their own views for tables to meet their own needs for custom developed reports.

So, a company could add, for example, five one, five two, five three, as the suffixes for their own custom developed views.

Anything that SQL Server supports can be done in the view.

All that has to be presented out to Power B I is the view itself.

You can download the latest version of the dimensional model views on the button below.

BC Direct Query DB

BC Demo Database

BC023_DM03_01

You can review the prior blog post on the button below.

BIDA0050 Blog Post

By creating these pseudo dimensional views we make it an order of magnitude easier to perform the queries against the business central data.

But we still have the problem that there are just too many views to sensibly manage.

Also, there is the serious problem that you can not define referential integrity constraints between views.

This means the joins in the Power B I Models would have to be added manually.

Solving Issue Two.

So how do we solve issue two?

What we have tested, and found to work, is this.

We created what we have currently termed a “data mart data model data base”.

The “data mart data model data base” contains a set of tables that are commonly used together.

For the sake of demonstration purposes, we have chosen the General Ledger Entry table, and all the tables associated with it.

Now, it is important to note that there can be many “data mart data model data bases”, and their function is to make the Power B I Models simple to use.

So, you only need to put into one of these databases the tables that you need for a specific Power B I report.

If you are only going to use data from 10 tables, then you can create a separate “data mart data model data base” with just those 10 tables in it if you want to.

Next.

Once you have created the tables in the “data mart data model data base”, you can remove columns that are empty or never needed by your company.

You can remove the system only columns.

Or you can not create them in the tables in the first place.

Then, with the drop and create tables commands, you must alter the primary key of each dimension table to be big integer and not null.

You must alter the columns for all the lookup keys in the fact table to also be big integer and not null.

This is so that the referential integrity constraints will be able to be created.

All the data types for the fields that will be joined have to be the same, and they all have to be not null.

Once you have the columns you want in each table you can add the primary key constraints to all the dimension tables.

The following is an example.

Please see the blog post for the typed example.

alter table dbo.vm_vendor_01 add constraint pk_vm_vendor primary key ( pk_vm_vendor );

Once you have added all the primary key constraints you can add the referential integrity constraints.

The following is an example.

Please see the blog post for the typed example.

ALTER TABLE [dbo].[vf_g_l_entry_01] WITH CHECK ADD CONSTRAINT [vf_g_l_entry_01_c04] FOREIGN KEY([dk_vm_gl_account]) REFERENCES [dbo].[vm_gl_account_01] ([pk_vm_gl_account]);

We have done this work in a demonstration database for you.

You can click on the button below and download a SQL server backup of the sample database.

This backup was created in SQL Server two thousand and nineteen.

(Update 2024-09-25: The database was updated to chance the collation sequence to be the same as Business Central Demos. It was accidentally created with the default collation sequence of the host server it was created on)

BC023_DM03_01

We will create a video demonstration of all this soon.

If you restore the backup database, you will see that it is just a small database with some test data in it.

You will see that the name of the tables is the same as the name of the views in the direct query database.

More importantly, if you check the tables, you will see the primary keys are defined.

If you check the general ledger entries table, you will see that the referential integrity constraints have been defined.

What this means is that when you develop your dashboard, you can use this database as the test data source for development.

You can put indexes on tables as you like.

You can even try writing your own Power B I report, on this sample database, just to practice how this works.

Now comes the magic part.

You create your dashboard on your desktop using Power B I desktop.

When you connect to a data source you connect to this test database where ever it may be.

It has to be visible via a fixed I P address, or server name, from your desktop.

You say you want direct query to the database, when you create the connection.

This database may be on the production business central machine. It may be on a development server.

But you create the Power B I Semantic Model for this dataset using this test database.

You then create the dashboard using this test database, and any other test database that you need.

You test the report and make sure it appears to be working properly, as best as is possible, with the test data you have.

Then, before you publish the report to Power B I Cloud, you repoint the data source to the I P address of your on premise server and the direct query database.

This database must be visible from your desktop using a fixed IP address or server name.

When changing the connection, it is important to go to “advanced options” and turn off select related tables.

You just want to repoint the desktop report, from the test database where there are just those tables you want in the Power B I Semantic Model, to the direct query database where those tables will be presented as views.

Power B I can not tell the difference between a table and a view in this repointing process.

And because you have told it not to bring in related tables, it will not look for the referential integrity constraints, which will not be there anyway.

You then publish the report to Power B I Cloud.

It will give you a message saying that it does not know where to connect the semantic model to.

This is because you have used the I P address of the business central database inside your organization.

On your Business Central On Premise server, or a server with linked tables, or replica, however you have decided to do this.

You will run the Power B I Gateway.

You can run just one Gateway per machine.

So, your published Semantic Model, or models, for the report will now be in the cloud in Power B I.

But they will not be able to see your on premise business central database.

You go into the semantic model, or models, for your Power B I Dashboard, and you repoint the source of the data from the test database to the direct query database, which will be defined in your gate way.

And then?

Whola!

When you refresh your report it will send the generated SQL into the gate way, and query your direct query database, which will have at least one thousand eight hundred views in it. And possibly many more.

So, what we have figured out is that you can create a small “data mart data model data base”, to create the Power B I Semantic model, that contains just the tables you need.

Then you can repoint that semantic model to your on premise Business Central direct query database, which contains all the possible views for the whole company.

In this way any single dashboard will contain just the semantic models it needs, while all data in Business Central is visible to any Power B I report developer, who is authorized to access that data.

You would implement your security however it is you want to implement your security.

This idea of creating these small test databases, to serve as the input to the creation of the Power B I Semantic Models, means that you can limit the number of tables and number of columns in any given semantic model, to be viable and easy to use for developers.

And then, by repointing the semantic model to the direct query database, the report will have access to all the production data. You can answer any question that the data can support.

Another factor to take into consideration is this.

Because the views in the direct query database can contain common table expressions, these views can be as complex as you like.

You can literally create very sophisticated, and complex views, and present them out as simple views.

You can also put time constraints on some views to say, show last 90 days, last 180 days, last 365 days and perhaps last 2 years.

In this way, a dashboard that only needs the last 90 days of data does not need to have “drill down” to do that. The view for the fact table data could have that constraint in it. You just add another suffix number to the fact table view.

Just for completeness.

The professional level of Power B I has a limit of 1 million rows being returned for any single query.

If you need to return more than one million rows, you have to buy the higher level licensing for Power B I.

Also, because of the way that the SQL is generated by Power B I, we would encourage you to create views that only display the columns needed in each report.

And if a report is only needed at summary levels, such as weekly, monthly, quarterly and similar, it is better to do the summarization in the database in another view, and not let Power B I do the summarization from the full fact table itself.

This is because it will consume far more temporary space if this is allowed to happen.

To show you what the SQL looks like for all the work that goes into creating such a report?

We have included a trace of our testing.

If you are familiar with Power B I, and how it creates its semantic models, and how it performs direct query, the trace will make complete sense to you.

When we read the trace, we were very pleased with how easy it was to read and understand.

Please click on the button below, if you want to read the trace created, when we set this up.

Gateway Trace

Simply put.

The Power B I Semantic model is created from the “data mart data model data base”.

Then when the switch is made to the direct query database, and the referenced tables option is turned off, it revalidates the model against the direct query database.

If the revalidation works you can then publish your report.

Just for extra information.

From our testing it appears that the semantic model knows the name of the SQL Server database at publishing time.

We were not able to alter the database name inside Power B I cloud services.

This is why we recommend changing the name of the database prior to publishing.

Once published, you can simply repoint the semantic model to get it’s data from the On Premise gateway using the correct database name.

Your database gateway support people will know how to set that up.

In Summary

Power B I Cloud offers a very wide range of features to help business managers improve the profit of their businesses.

Power B I Cloud is far in advance of Excel and Power B I on premise.

Excel is great and is better than Power B I for many uses.

Power B I On Premise is also great and very useful.

But it lacks many of the features of Power B I Cloud.

So, if you have Business Central On Premise, and you want to use Power B I Cloud?

There was no really good solution to being able to take advantage of Power B I Cloud, at least that we are aware of.

By inventing the following two ideas?

We have made it viable to use Power B I Cloud with Business Central On Premise.

Those two ideas being.

One.

Placing pseudo dimensional models over the top of the direct query database.

Two.

Creating separate dedicated “data mart data model data bases”, which will be used to create the Power B I Semantic Model, for each report that needs to use that set of tables.

Just as further information.

Although we have used Business Central in this example, it should be noted that what we describe works for any SQL Server production system.

This is a fast, simple, cheap and easy way to get started using Power B I against SQL Server production databases, or replicas.

Lastly, we really must add some caveats.

Obviously, direct query against a production database, or replica, has it’s limitations.

This is not like having a proper staging area.

This is not like having a proper data warehouse.

But this idea is very cheap and easy to get started with.

For thousands and thousands of small companies?

This will be enough.

If you use this idea and you come to understand why separate staging areas and data warehouses offer greater value?

Then you will have more arguments available to put into your business case, to ask for the budget for your data warehouse project.

We see this idea of using Power B I, without any other software purchase necessary, as a good way to get started on your journey of learning how to use data to improve the profitability of your company.

And with that?

I would like to say thank you very much for listening to 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.

BIDA0052 – First Post To Business Central Forums

0

Hello and welcome! I am Mihai Neacsu and I am the Business Development Manager here at BIDA.

We are very pleased you are dropping by to see our latest blog post.

This blog post is intended to be our first post that makes it to the Business Central Forums for partners.

So this is a test.

BIDA BC Blog

If the test works? We would like to give you a valuable freebie for finding us!

That freebie is a renaming script for Business Central 2023 Wave 2.

You can listen to the video below and click on the button below to download the whole script.

It will give you easier to use and remember names for all the tables and fields in Business Central. We hope you like it.

And with that?

We are going to post this blog post and see if we have our very first blog post make it to the Business Central Blogs.

Please note the script of the presentation is below so you can read along with the video 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 news!

I am pleased to bring you some news that I hope will interest you.

I am Mihai Neacsu and I am the business development manager here at BIDA.

Today we have some good news for all those people who use Business Central on premise.

If you have Business Central on Premise and find it hard to write SQL against the underlying database?

We have some good news for you.

As you know, Business Central contains GUIDs in table names.

It also has field names that require brackets around the field names in almost all cases.

This makes writing SQL against the underlying database more difficult than it needs to be.

Today we are pleased to announce the release of an SQL Script that renames all tables and columns in the Business Central 2023 Release Wave 2 demonstration database.

You can run this script, start to finish, against the Demonstration 23 database of Business Central.

Just so you know, we used the Cronus UK database to create this script.

But it is easily edited to run against any other version of Business Central 2023.

We have tested it against that database and it works without error.

You can download this script from the button below and try it out for yourself.

Download Script

Of course, if you are on a different level of Business Central, or you have customized your Business Central, you have two options.

1. You can alter the script by hand.
2. You can send us your SQL Server dictionary tables and we can quickly run the process on a server here.

To make it more interesting for you?

We will do this script generation work for free for the first 20 companies who contact us and ask us to do that.

After the first 20?

We might ask to be paid a small fee just for our time.

We have implemented this script as part of developing our data warehousing product for Business Central on Premise.

This table and column renaming will make it a lot easier for us to build our data warehouse.

It will also make it a lot easier for us to develop SQL Direct Query Dashboards against Business Central.

We hope you will download and try out this script and consider using it in your company.

And with that?

I would like to say thank you very much for reading or listening to our blog post 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.

Mihai Neacsu

BIDA Business Development Manager.

We will be emailing our subscribers useful and
valuable information regarding business intelligence.
Please subscribe here to be on our general emailing list.

BIDA0051 – Business Central Dimensional Queries Sending Results To Excel

0

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!

Download Materials

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.

BC Direct Query DB

BC Demo 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.

BIDA0050 – Dimensional Models Over Business Central

0

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.

BC Direct Query DB

BC Demo Database

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.

BIDA0049 – Business Central Balance Sheet in Excel

0

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.

Download Materials

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.

BIDA0048 – Business Central Beginning of Time GL Balances in Excel

0

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.

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 an update on the BIDA forty six blog post.

We have updated the Excel workbook to make the demonstration even better.

So, as ever, on with the demo!

Demonstration

Here we are in the BIDA General Ledger Beginning of Time Balances Demonstration workbook.

You can download this workbook from the button below.

Download Materials

Firstly, you can see that we have allowed for 5 years of beginning of time balances.

The data being used is the version 23 demonstration database, that only has two years of data in it.

The headings and GL account name panes are frozen.

We will scroll to the right, so we are starting in 2023.

We will scroll down to 1999, fixed assets total.

This is because this line gives us a nice reporting line.

Now I will click on the plus sign next to the GL Account code.

Now I will click on the plus sign next to 2024.

You can see that there is no beginning of time amount for 2023 because it is zero.

An empty cell means zero.

In December 2023, the transaction amounts totaled one million, three hundred and twenty one thousand and two hundred and seventy eight dollars.

You can see this amount became the beginning of time amount for January 2024.

Then the transaction amount for document type zero, in January 2024, was thirty six thousand and forty five dollars.

This is added to the beginning of time amount to get the amount of one million, three hundred and fifty seven thousand and three hundred and twenty three dollars, for the beginning of time value for February.

We are pretty certain we do not need to explain to you how beginning of time balances work.

What we would like to explain to you, is that you can calculate such a complex thing as beginning of time balances going back 5 years, using a SQL query.

You can then send the data for that query in to Excel at a summary level.

Once you have the data in Excel you can present it in any way you please.

Now we will scroll down in the workbook, just to show you all the other GL accounts from the demonstration database are in the report.

Now we will click on the minus sign next to 2024.

Obviously, from this demonstration, you can see that you can select any combination of GL account codes from Business Central, and you can report on them in any way you like in Excel.

For most people who work in the accounting area of Business Central, you will not have seen that it is possible to calculate Beginning of Time Balances like this, without considerable effort from yourself or your IT department.

Such complex calculations as Beginning of Time Balances can now be done quite easily, if you use Meta five to send the query into Business Central and to send the results to Excel.

You can run this query right on top of your production Business Central database.

Or, you could ask your IT department to create a replica database, so that running this query does not interfere with production performance.

In either case, this demonstration shows that you can get started with much more complex queries than you might have thought possible.

Once you are doing more and more complex queries on your replica production database, and you have come to understand the value of creating a data warehouse?

Then you might be able to more easily get a budget approved to implement our data warehouse product, that is still under development at this time.

For small companies who use Business Central, it is very possible quite a wide array of reports can be written without the need to even create a replica copy of your Business Central database.

Please note.

We have included in our blog post, all the buttons needed to download all components of this report.

You are welcome to ask your IT colleagues to review the SQL that was used to create this report.

In Summary

Just to keep you interested?

We have another very interesting demonstration in development.

This demonstration shows you that it is possible to create the balance sheet from Business Central in Excel using this sort of idea.

So, if you want to be notified when that demonstration is available?

Please feel free to join our email list.

Or please grant us permission to put you on our email list.

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.

Please note. If you would like to ask your IT staff who support you, to read a more detailed blog post? You can give them the link on the button below.

BB0010 Blog Post

We will be emailing our subscribers useful and
valuable information regarding business intelligence.
Please subscribe here to be on our general emailing list.

BIDA0047 – BC 2023 Excel Dictionary Demo

0

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.

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 up to the prior video demonstration of the Navision 2018 Excel Dictionary database.

The obvious question that arises from prior demonstrations is this.

“If we have access to so many tables and fields in Business Central, how do we make it easier to find those fields because we can’t possibly remember them all.”

And that is where today’s video comes in.

If you are wondering how, you might be able to more easily search all the fields available in Business Central, then you should watch today’s video.

So, as ever, on with the demo!

Download Dictionary

Demonstration

Here we are in a simple Excel spreadsheet.

This spreadsheet has two sides to it, the left and the right.

You can download this spreadsheet using the button on the blog post.

On the left-hand side is a table of categories.

These at the same categories you saw in the prior demonstration.

They are drillable so you can immediately understand that I can click on a drill, and it will open up with the tables.

So, let’s drill on Financial Management.

Please note that the very first time you click, Excel will load the power query model, so it will take a second or two to respond.

Now you can see all the tables that are listed in the Financial Management Category.

We will scroll down to the GL Account table.

As you would expect, when we click on the GL Account table, we will get all the fields in the GL Account.

As an Excel user the rest would be obvious to you.

Since we would love you to download the workbook and play around with it yourself, we won’t go into too much detail of this simple drill down.

All the categories, all the tables, all the columns are there.

If we ever get good help text and descriptions?

Then we will include them in this workbook.

Now we will scroll back up to the top of the workbook.

We will close the Financial Management Category.

Please note at the bottom of the categories list we have two new categories.

They are.

One.

Ninety Eight, system tables.

And two.

Ninety nine, not in category.

In the prior Navision dictionary we excluded system tables.

We now think that it’s useful to have them in the dictionary.

For the Not in Category Tables, we have not yet gone through and moved these tables into categories.

If you think you have a good suggestion for what categories what tables should go in?

Please feel free to use the comments below or contact us and let us know your proposals.

Over time we expect to be able to move all the tables into a suitable category.

We know this is a matter of opinion and some people will have differing opinions.

Now we will show you something we think is really pretty cool.

Excel drills and searches are implemented in this workbook.

So, next to Table there is a drill.

I will click on this drill.

Now we can see there is a search option for all the tables in the dictionary.

I will enter G L and it will search for all the tables with G L in the name.

I will click off all “select all search results” to have no tables selected.

Then I will scroll down and select the GL Account table again.

When we click on the GL Account and click OK, we will be returned to the report.

The GL Account table is now selected but it may not be visible in the table.

You might have to click on Financial Management again to have GL Account visible.

Once you do that then you can click on GL Account to make the fields in the table visible.

Please note we have also numbered the columns in the order in which they appear in the tables.

Just like any other Excel report you might create; the dictionary is fully searchable in this table.

Now, we will clear those selections.

Now I would like to draw your attention to the right-hand side of the report.

You can see there is a bubble saying, “search a table”.

This provides a second way of searching for a table that you might like more.

You can see GL Account is selected.

In this search bar you can delete and type in your search term if you wish.

We recommend you download the spreadsheet and give it a try because it’s quite a new idea.

We have not seen this idea before.

What I will do is go to the search text and enter just GL.

I will again select GL Account.

Now it will show me what category GL Account is in, and it will show me all the fields in GL Account.

In this way you have a second way to search for tables using this filter and drop down.

We thought that in this first release of this workbook we would give you both options.

Please feel free to let us know which one you prefer in the comments section at the bottom of this blog post.

Of course, the value of this spreadsheet is this.

As you are creating queries on the Business Central database using SQL, you can refer to this spreadsheet.

This will help you to easily find the tables and fields that you are looking for.

Then, when you search the spreadsheet and know exactly what fields in what tables you want, you can more easily locate them in SQL Server Dictionary.

Also, the actual SQL names are the same as in this dictionary, only with underscores where the spaces are.

In our own development work, we have this spreadsheet on our left screen as we write SQL.

You might want to try that out for yourself.

If you would like to try this out for yourself on your PC?

Then please contact Mihai to book a time on the evaluation machine.

You can also download this spreadsheet from the button on the blog post.

You can try for yourself how easy it is to query data directly from Business Central using SQL while also having this dictionary available to you.

Further, if you would like one of these spreadsheets for your company to help you query your Business Central system?

We will prepare this spreadsheet for qualified Business Central installed companies.

So please feel free to contact me to see if you meet our qualification criteria.

This report takes a little while to create.

So, we will only create a limited number of these reports as “freebies”.

In Summary

So, to summarize what I have shown you today?

In our prior video we showed you that it is now possible to perform direct query to all data in your Business Central system using the Meta5 query tool and SQL.

However, because there are so many tables and so many columns it’s really handy to have a searchable dictionary right there on your left screen.

We created this report to be a searchable dictionary for any customized Business Central system.

It provides two ways to query what tables and columns you have available.

On the left-hand side, it allows you to drill down or to use the excel search and drill functions.

On the right-hand side, it allows you to search for tables containing the search string.

We are finding this very useful in our own development work.

We think you will find it useful too, even if you don’t have 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.

Download Dictionary

We will be emailing our subscribers useful and
valuable information regarding business intelligence.
Please subscribe here to be on our general emailing list.

BIDA0046 – Business Central Beginning of Time GL Balances in Excel

0

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.

BB0010 Blog Post

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.

BIDA0045 – Meta5 And Complex CTE Queries

0


(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 news!

This blog post will be very short so we have not created an audio.

Please note that this blog post is particularly technical and is addressed to technical people.

We did think about putting it only on BIDA Brains but we felt we would put it here as well.

For business people this post means:

You can ask your operational systems any question you like as long as it can handle the processing of the query.

Now that we have published everything needed to create “Meta5 as a Black Box” we just needed to test one last thing to make sure all was ok.

That test was to make sure Common Table Expressions worked from Meta5 to SQL Server.

Of course, we expected it to work, but we have to make sure by testing anyway.

So for all you technical people out there?

We have tested the CTE required to produce the Business Central Chart of Accounts report.

You can download the code from the button below.

Please note we formatted the SQL using Notepad++.

You might want to use Notepad++ to read it to get the spacing correct.

Download CTE SQL

The way we did this was as follows.

We turned on SQL Tracing and then ran the Chart of Accounts report.

We captured the SQL sent into SQL Server.

We then translated it to use the Direct Query table and column names we have defined.

We created the query using both outer apply and left joins.

We got the same result with both queries.

However, we are only using the demo database so there could still be errors in it.

Please note. In the Business Central report, the report itself creates the summaries for the parent GL Accounts.

So our CTE query includes the work to create the summaries for the parent GL Accounts.

What does this mean for technical people like you?

This means that you can use “Meta5 as a Black Box” to send any CTE query into any database that supports it and get the result back.

Basically, because CTEs can be so complex, you can get virtually any answer from any database that supports CTEs.

The obvious constraint is that running the query does not interfere with other processing the database is required to be doing.

This means “Meta5 as a Black Box” + CTEs + Excel / PowerBI can produce a very large array of reports from operational systems without the need to create the data warehouse first.

Sure, this is not a replacement for the data warehouse proper.

This is a way to create Excel / PowerBI reports by querying operational data where the interference on the operational processing is tolerable.

This is interesting to technical people because it means you can write the report one time and then sell it many times to many users of the operational system you are an expert in.

We believe this “Direct Query” using “Meta5 as a Black Box” sending CTEs into databases and then sending the resultant data into Excel / PowerBI is going to be a very large market.

No other product provides such seamless integration between Excel / PowerBI and complex CTE derived data as Meta5 does.

But don’t take my word for it.

Read the CTE from the button above.

And if you want to see it working for yourself?

We can give you access to our Meta5 Evaluation server and you can run the query for yourself.

Ok?

With that I would like to say thank you very much for reading our blog post today.

I really appreciate your time and attention.

Best Regards.

Mihai Neacsu.

Business Development Manager.

BIDA.

We will be emailing our subscribers useful and
valuable information regarding business intelligence.
Please subscribe here to be on our general emailing list.

BIDA0044 – Meta5 As A Black Box Demo

0

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 news!

I am pleased to bring you some news that I hope will interest you.

I am Sofi, your BIDA AI Assistant.

I will be reading this blog post for you today, on behalf of Mihai.

Today, we have some really big news, for former Metaphor employees and customers.

We have been very busy on a number of fronts.

One of those fronts is this.

We have been trying to find a way to create quote, Meta5 as a black box, end quote.

I am very pleased to report that we have been successful.

Today, we are releasing our very first customer demonstration of Meta5, as a Black Box.

So, if you are a former Metaphor employee, or customer?

We think you will really enjoy this demonstration.

So please watch our new demonstration.

If you are new to Metaphor, but you are knowledgeable in business intelligence?

We think you will also really enjoy this demonstration.

So we would also ask you, please watch our demonstration.

This demonstration shows how anyone, who has profound knowledge of a large operational system, could leverage that knowledge by selling Excel Dashboards, created from the data in that operational system.

What we are going to take you through today, is how Meta 5, as a black box, works.

So.

On with the demonstration!

Demonstration

Here we are on our development machine.

The first thing we want to show you, is that we have a master database, that contains parameters for Meta 5.

We mentioned this in a prior post.

On the video, you can see the master database.

You can see that inside the master database, there are just three tables.

You can see there is a SQL statements table.

You can also see there are two variable tables for meta 5.

These are the only three tables you need, in the master database, to be able to run meta 5 as a black box.

You will notice that we give them a schema name, so that this works on other databases, such as Oracle and DB2.

The first meta 5 variables table is for the site.

The second meta 5 variables table is for the individual capsules.

I will just take you through what is in each of the variables tables.

You can see that we have a simple select star from the master table variables one table.

You can see that what is in it is the name of the spreadsheet that it came from.

You can see it has a version number.

You can see it has a row number.

You can see it has a current flag.

So, if you wanted to keep a record, of all the values a variable has had, you can just set the current flag to 0 for the old records.

You can see there is a group variable.

This is to allow sets of 208 variables.

You can have many groups of variables, but you must set all 208 variables in each group.

A single capsule can only retrieve variables at the group level.

Then you can see the at variable name used by meta 5.

This is what Meta5 knows the variable name as.

Then you can see the actual value of the at variable.

Then the rest of the fields are documentation.

We allow for giving a variable a name, a short description, and a long description.

This is used to make it easier for developers, to know what variables they should be using.

So, these variables will contain these values, for capsules that go to this group, in the master table.

You can see that there are dots in each variable, that is not yet allocated.

That dot will be sent into the capsule.

You can see things like ODBC connections.

You can see things like query folders.

If you scroll down you can see things like ODBC drivers.

You can see reporting folders.

You can see a series of dates, such as three months prior and four months prior.

You can see the signal file directory.

You can see the signal file name, which we will get to.

You can see the delete signal file variable.

You can see the sleep seconds variable.

You can see the capsule iterations variable.

And 60 seconds by 240 iterations is 4 hours.

Lastly, you can see some system variables down at the bottom.

Anyone who has used meta 5 before, will understand these variables.

For people who are new to Meta5, this makes sense.

This is a set of site variables, that make sense, that are required to run meta 5 as a black box.

Now inside each capsule, you can also have variables.

The most common variable, inside a capsule, is the signal file.

The signal file is tied to the capsule name.

If you wish to delete the signal file, you also have to set the delete signal file variable to yes.

So, in this second variables table, it makes sense, that you have to set variables, for individual capsules.

The capsule we are going to test, is the all capsules template, and you can see it has a signal file defined.

You can see the delete signal file variable is set to yes.

And these two values will go to FB and FW.

Please note, we haven’t updated the descriptions and the timestamps, on this testing machine.

The real dashboard we’re going to show you, is the head office sales dashboard.

But we are going to show you the all capsules template first.

So with that, we will move over to the meta 5 desktop.

Here we are on our meta 5 desktop.

I have zoomed in to the upper left hand corner.

This is because this is a development machine, and there are objects on the desktop we do not want to publish.

So, if you are going to use meta 5 as a black box, this is how you would do it.

What we will do is take you through the example first.

You will have an applications file draw.

Our application that we are doing is business central.

You will have a file drawer for reference capsules.

These are analogous to shareable program objects.

You will have a file draw for template capsules.

You will also have file draws for dashboards, data models, and metadata.

These are virtual filing cabinets, just like you would find real filing cabinets, in an office.

I am going to take you into the template capsules.

I am going to show you the 0001 all capsules template.
Just to show you that this capsule works, what we are going to do, is show you the run file, and then show you the run file being deleted when the capsule runs.

To schedule a capsule, all you need to do, is to copy it from wherever it is, and drop it on the capsule service prompt.

In the example we will say run now.

It will immediately run.

And you can see the run yes signal file is gone.

So the capsule executed right away.

Now I will put the run yes file back.

Now I will show you, that you can copy a capsule onto the capsule service prompt, and then submit it under a specified alias.

An alias can be any of the items you see on the screen.

At installation time, you decide what aliases you would like to define, for your desktop.

These aliases can be anything, however you don’t want to create too many of them.

So we are going to set this capsule to run at midnight.

Now we can go into capsule service prompt, we can click on show queue, and we can see the capsule that is scheduled for midnight.

Now we will click on show aliases.

You can see that all the aliases are set to periodic.

This means the capsules will be submitted at that time every day.

You can see there are variables for year, month, date, day of week, hour, minute, and second.

Capsules can also be set to run once on a schedule.

However, as I said, you do not want to create too many aliases, even though so many options are available.

So what we have shown you, is you can create any capsule, drop it on the capsule service prompt, and run it on a schedule, either periodic or one time.

Capsules can also be started based on a windows command.

So you can just create a windows command to start a capsule, and then run it any time you like.

To run a capsule instantly, without going into the meta 5 desktop, you can just run a windows command.

Now I want to show you how this works.

The first icon that you need to be concerned about, gives the capsule its name.

So we will open this icon, and you can see the GA variable has the capsule name in it.

Meta 5 has an idea called the reference capsule.

This is basically a reusable programmable object.

So they can be invoked from other capsules, and not need to be written multiple times.

In this example the first reference capsule sets the capsule controls.

I will show you the controls.

You can see the values of at A, at B, and at C.

I will remove the first three values.

I will close and open the user input controls, so that you can see the three values are truly gone.

Then we will run the first two icons in the capsule.

I will open the controls again.

You can see the values of the first three at variables are back.

This shows you, that this reference capsule, retrieves all 208 variables stored in the first variables table.

You don’t really need to know how this works.

You can just trust that it does work.

We then need to set the capsule name.

If we go to variable GA, we will see that the capsule name defaults, to just capsule name.

We need to set the actual capsule name, so that we can retrieve the capsule variables.

I will demonstrate how the capsule name is set.

Then we can fetch the individual variables, that the capsule needs, from the master database.

This is done by running the second reference capsule, into the SQL target.

When we open the SQL target, you can see the two variables, for this capsule.

In this case, it is the signal file name, and the variable to say delete the signal file, after execution.

I will show you that FB and FW, are not yet changed.

The last two icons, must be executed to apply the changes, in the SQL file.

And now you can see that they have been updated.

This line of icons sets all the variables for the capsule.

Now, we can iterate up to 240 times.

That is, if the signal file is not yet in the signal file folder, the capsule will wait for it to arrive.

If the signal file has not turned up in 240 waits, the capsule will terminate, and give up it’s slot in the queue.

If you have three capsules waiting, and a 4th capsule starts?

Then it must wait, for one of the prior three capsules to end, to get a slot, in which to execute.

The first thing that happens in the first wrapper, is to look for the signal file.

If it decides to run, it will run wrapper 2.

If it does not run, the capsule will pause for sleep seconds.

Now we will go into wrapper 2.

Inside wrapper 2 you can see the capsule called, your actual capsule.

This is where you put the actual capsule, you want to build and sell, or perhaps even use for your company.

When your actual capsule is finished, the end capsule will terminate the capsule.

Of course, your actual capsule can contain anything.

At the very end of your actual capsule, you must put the final two icons to delete the signal file.

These two icons must be there for all capsules.

If the capsule variable for delete signal file is set to no, then this capsule to delete the signal file, simply will not run.

So if you want to run a capsule, say at midnight, regardless of the signal file.

Then you create the signal file at capsule installation time, and you leave the delete signal file default to no.

Then the capsule will run at midnight, every night, because the signal file will always be there.

Now I will close the sample capsule, and go back to the applications folder.

I will show you are real world example, of what a sellable capsule might look like.

Remember, as a former Meta5 employee or customer, you could build these sorts of applications and sell them in the marketplace.

Of course, we would prefer you not to do business central.

Here is our business central file drawer again.

The first folder is for business central dashboards.

We have arranged our dashboards into 14 groups.

These are groups that are well understood by business central installed accounts.

They are in line with the processing areas of business central.

We have done our first dashboard in sales.

It is called the Head Office Sales Dashboard.

This is against our data warehouse models.

So it would run every night after the data warehouse was updated.

I will open the Head Office Sales Dashboard capsule.

The first portions of the capsule look the same as the template.

When we open up the SQL file, we will see that variable GA, is set to this capsule name.

Now I will run the icons on the second row of the capsule.

We can go to show controls.

We can see the signal file name, is set to the signal file, for this capsule.

So you can see the second line of icons, retrieves the correct variables for this capsule.

You can see wrapper 1 looks the same.

What I will do now, is take you into the capsule, and show you the actual capsule, that will be run.

Now, we are in a realistic capsule, that you can write and sell.

This capsule will make sense to most people.

You have the periods.

You have the Outlets.

You have the product categories.

You have the currencies.

Everyone who has done BI, will be familiar with these sorts of dimensions.

You can see the capsule for Plan versus Actual by month.

The capsule called this month calculations, contains a lot of complex calculations, to perform variance analysis by a variety of months.

You will see some of these as we go through the demonstration.

The way in which capsules using Excel work is this.

You query all your source data, perform any calculations needed prior to collection, and then send all data into the collect data spreadsheet.

The limitation is about 100,000 rows.

Above 100,000 rows, the data transfers tend to be a little slow.

They do work, they are just a little slow.

You may be wondering, why we have an Excel workbook, at the top left of the capsule.

This is so that it will over-write the excel workbook target, so that there is just one row of data in each Excel table, in the workbook.

The data in the collect data spreadsheet is then sent into the Excel Spreadsheet.

There is one workbook per data set sent in.

So in this example, there will be 6 workbooks, with one data region in each workbook.

The data from collect data, will be sent into Excel, one region at a time.

However, the Excel workbook will only be opened once by Meta 5, and all 6 datasets will be sent into the workbook, in one session of Excel.

You do not need to worry too much about the bottom right section of the capsule.

The piece we will highlight is the delete multiple sheets.

Meta5 sends data into Excel data regions.

The data in these data regions is then sent into the power pivot model.

Then all reports are refreshed.

Then you have duplicate data inside the workbook.

The delete multiple sheets capsule, allows the developer, to delete the worksheets, containing the data regions.

In this way no duplicate data is required in the spreadsheet, that is sent to the business user.

The business user gets a spreadsheet with only his, or her, data in it, and only one copy of that data.

Lastly, the spreadsheet is sent to a PC Directory.

It could be sent to email or sent by power shell command.

The important part of the PC directory icon, is the target directory name.

This is defined by capsule level variables.

The actual workbook output can be sent to any PC directory, visible to the desktop.

This is very likely a one drive.

Also, what is important here, are the options for the arrow, from the spreadsheet to the PC Directory.

When I option the arrow, you can see that the region name contains variables.

This means that you can provide a base name for the report.

You can provide a secondary name for the report, for example the name of the person it is intended for.

And you can even name the file extension, for the report, if you are going to use PDFs, as well as Excel.

What is in region name, is the actual name of the object, when it is sent to the PC Directory.

So, for example.

If you were producing a dashboard, that was going to be sent to 20 region managers?

You could include the region manager name, in the name of the excel workbook itself.

And you could include the region manager name, in the directory structure.

The 20 reports, which include the region manager name, could be sent to 20 different one drive directories.

The region managers could be replicating the reports from the different drives.

Therefore, the reports would find their way to the device for the region manager, for which they were intended.

Those are the important design features, to take away from this example capsule.

What I want to show you now, is that we can actually run some queries, and show you some data.

However, we have removed the customer production data that we have tested on, and you can only see data that we can allow to be public.

So, we can open up the period table, and you can see the period data come back from the query.

Notice again, this report is on the data warehouse we are building, not on business central directly.

We cannot show you products or outlets because they contain customer data.

What we want to show you now, are the sorts of queries you can write in the query tool.

We want to show you how sophisticated the generated SQL can be, without having to resort to SQL, to write the queries.

One feature of Meta 5, is that business users themselves, can write their own queries, and not need IT staff to write their queries for them.

Of course, the same effect that you will see here, can be achieved by writing SQL.

It’s just that business users can not write SQL, to the level required, to replace these queries with SQL.

This capsule was developed, before we had developed the techniques of writing queries in SQL, and sending them to the database using Meta5.

When the goal is to sell the capsule many times over, it is faster and easier to maintain the capsule, if the SQL is in a database, as opposed to in a query tool.

For those of you who really want to see the SQL generated, and read a detailed blog post on what is happening in this capsule?

We have published a blog post, B B 6, on our technical BIDA Brains site.

You can go to that blog post on the button provided.

BB0006 Blog Post

Further, if you want to read how a SQL programmer can write SQL, that performs the same function, as what you are seeing in this demonstration?

You can go to the blog post, B B 7, on the button provided on this blog post.

BB0007 Blog Post

We were able to replicate the functionality, of these query icons, in one SQL statement, that is 280 lines long.

When you read that SQL, you will see why business users cannot write it.

Just for your knowledge this report has a report date of August 15th 2017.

We were using old production data, from the data warehouse, in the development of the dashboards.

The first capsule retrieves plan versus actual amounts, over the last 12 months, from the beginning of last month.

So, what we will do now, is just scroll through this capsule, on video, showing you all the queries.

We will show you some SQL.

And for details, please refer to the detailed blog post, B B 6, on the button on this blog post.

Now, I will open up the capsule called this month calcs.

The first capsule retrieves quarter to date and year to date.

The first query gets sales year to date.

The second query gets sales quarter to date.

Now I will open sales last periods.

There are four queries.

They are as follows.

Sales This Month Last Year.

Sales Last Month This Year.

Sales Last Month To Date.

Meaning to the reporting date.

Sales Last Month To Date.

Sales This Month Last Year To Date.

As I said, you can read a detailed blog post on BIDA Brains, that contains all the SQL generated by this capsule.

In the query, Sales Last Month To Date, you see there are so many things in the query window, that they are overlapping each other.

This does not matter, it will still write the SQL properly.

One of the problems in the B I world, is that the SQL has become so complicated, that it gets harder and harder, to present a visual representation,
of the query.

Even so, the Meta 5 query tool will still work, even in the face of too many items, to put on the pallet.

In planned sales this month, you can see the fact table is, the retail outlet sales plan.

One thing to note, is that plans are held at monthly level.

We have to pro-rate the planned sales, across the number of days, that have passed in the month so far.

This capsule simply divides the plan, by the number of days, that have passed in the month.

If we capture the SQL, we can see the reporting date is August 15th 2017, using the American formatting.

In summary.

We capture the periods, the outlets, the product categories and the currencies.

Then we capture a whole series of plans and sales measures, so that we can compare plans versus sales, across periods.

We send all these details out to a spreadsheet called collect data.

Then we send all that data into the Excel power pivot models.

Then we refresh all the charts in the Excel spreadsheet.

Then we send it to the business user however it is that they would like to receive it.

Usually this will be by one drive.

These are the sort of dashboards that business managers really want.

We are using the Steven Few bullet charts, to make the dashboards more attractive, and easier to understand.

So, that is what we wanted to show you.

A real world head office sales dashboard.

I wish I could show you the dashboard itself.

But it contains customer data.

I think you would all know, that the hardest part of developing dashboards for sale, is having access to production quality data.

If this capsule was taking data from business central directly, you could sell this capsule many times over.

If many customers had the same data warehouse data model, as we are planning to do, you could also sell this capsule many times over.

Now I will just take this capsule, and copy it, onto the capsule service prompt.

We can then schedule to run it at, say, three o’clock in the morning, every morning.

I will show you the queue, to show you that the capsule is scheduled to run, at three o’clock in the morning.

As we have mentioned, you can also run a capsule immediately from a windows command line.

So if there were some emergency data updates to the data warehouse during the day, and someone wanted this dashboard updated with the new data?

They could simply go to the command that schedules this capsule, and double click on it.

The capsule would then run.

We believe that a double click on a command file, is about the easiest way to request, the immediate running of a capsule.

Most importantly in this demonstration.

We have shown you that you can sell this capsule many times over.

To install a new capsule all you have to do is.

One. Copy the downloaded capsule file into the correct file drawer.

Two. Set the correct parameters for the capsule.

This is presuming the customer has already installed at least one capsule, and gone through the site setup process.

As we said at the beginning.

The barrier to entry is profound knowledge, of the operational system, you are reporting on.

By allowing the SQL to be written by IT professionals, who know these operational systems very well, you reduce the chances of invalid data, being
presented in the reports.

Summary

What have we shown you?

We have shown you that it is possible to build a capsule, where we retrieve all the variables for the capsule, from a control database.

We then run the capsule, to produce a dashboard, that is of value to the business users.

We deliver the dashboard, via such mechanisms as one drive, or others.

For business central, we will build dashboards across many areas of business central.

We will do this for both business central direct query, as well as using the data warehouse we are building.

And with that?
I would like to say thank you very much for watching our blog post 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.