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