Hello and welcome back to the next exciting installment of our Meta5 training videos.
This will be our final training video in this series.
So, you have almost made it to the end now!
I am very pleased to see you come back to watch the next portion of our video training.
I want to say thank you very much for evaluating Meta5.
I want to say thank you very much for your time and attention.
Thank you.
In this video, there is not going to be a lot of dialog, because the creation of these next six dashboards is very easy, by comparison to the prior training we have presented.
You can very easily follow along with what is done in this video, even with only basic excel skills.
We have included this video, more for completeness, than for showing you anything particularly different from what you would find on any other training, for creating Excel Dashboards.
We already created this video for our internal training, so it seems sensible to include it in our public training.
Please note.
In this video, we are not going to use the pan and zoom at all, because the vast majority of the video is editing Excel dashboards.
This requires that the options panel, on the right of the screen, be visible for much of the video.
In this video you are going to build the drill down dashboards, and the drill down pivot tables, to give the user more information about the demographics analysis, of the product categories, over time.
These examples of drill down, inside excel, demonstrate how to create an excel dashboard with quite reasonable drill down capability.
Remember, as a user of Meta5, you would have access back to the underlying data warehouse, and all the detailed data that is in the data warehouse.
So, as a user of Meta5, you could go from the dashboard, all the way down to the detailed transactions, if you would like.
However, in the vast majority of cases, for business consumers of dashboards, it is enough to have a small subset of data inside the excel workbook, to be able to query, and analyze.
This is what you generally see in Business Intelligence .
The Business Intelligence tool tries to meet all the needs, of all the business users, via the one tool.
However, the needs of business users are quite diverse.
If business users are required to all use the same tool in the same company, the differences in needs means the tool must have features that many business users do not use.
This means those features must be paid for, even if they are not used.
Excel is already very widely used in businesses.
Every sizable business has business users who are proficient in Excel.
There are also thousands of Excel tutorials on you tube.
We are not suggesting that Excel is the one, and only, tool business users need, for Business Intelligence.
What we are suggesting is this.
Excel is already widely deployed, and is already a very useful Business Intelligence tool.
We are just suggesting adding Meta5 to the mix of tools.
Why?
Because, one of the biggest problems with using excel for dashboards and reporting, is getting the data in to the workbook, in the first place.
We are suggesting, that by using Meta5, to put data into the excel workbook, you will save time, and money, when building excel reports and dashboards.
As you evaluate Meta5 in this training, please remember this.
It only costs one dollar a day to have Meta5, so you can more easily build excel dashboards.
For just one dollar a day, you can use Meta5 to get data from anywhere, and put it in to Excel.
You can distribute those excel dashboards to your colleagues with no Meta5 costs.
For two dollars a day?
You get more meta5 disk space, and you get the feature of running reports, in batch, while you are not even at your PC.
Just to be clear.
You can develop capsules on the one dollar a day Meta5 desktop, and then deploy them to the two dollar a day Meta5 desktop.
Of course, you could also have 2 or 3 of the two dollar a day desktops.
These could be used, by different departments, to run Excel reporting, after ETL processing.
Of course, there is an Enterprise Edition, for those companies, who want 5 or more desktops.
We would expect some companies, to upgrade to the Enterprise Edition, to gain the extra value it offers.
Now that we have done our little bit of advertising in this final video.
On with the training!
In the prior video, you saw how to create the six charts you see on the dashboard in this video.
The dashboard you are working on, should look exactly like the dashboard in the video.
In this video, you are going to build 6 more dashboards .
They will be called, dashboard 11, through to, dashboard 16 .
You should have the example Excel dashboard, to work from, on your desktop.
You can use that example Excel dashboard, to compare what you are doing, precisely, in your Excel dashboard.
Now, the first thing you are going to do, is copy the dashboard called, dashboard oh one, 6 times.
You will rename the copies as, dashboard 11, through to, dashboard 16.
By copying dashboard oh one, you will copy all the graphics, you will copy the slicers, and you will copy the charts.
This will save you a great deal of time, in creating the next six dashboards.
Now, go to dashboard 11.
You should follow along closely with the video.
Please make exactly the same changes on your dashboard, as you see on the video.
Now, you have completed the two charts at the top of dashboard 11.
You saw that was very easy.
Now, you will create the three pivot tables.
Please follow along with the video, to create the first pivot table, on your dashboard 11 worksheet.
Now, to create the second two pivot tables, you will copy the first pivot table, and make changes .
In each of the six dashboards, you will have to create the first pivot table, and then copy it for the second, and third, pivot tables.
After you have created all three pivot tables, then you should set the drills as you would like them to appear when the business user opens the spreadsheet .
Because we will remove the background grid in Excel, we also want to put a border around each pivot table .
Now .
Because we have added pivot tables on to the worksheet, we will go and show you the year slicer .
You can see in the video, that there are three new names, in the report connections, for the year slicer.
In the end, you will have 18 new report connections to make between the slices, and all these pivot tables.
You will make these connections, at the end of creating the six dashboards .
Please notice, the charts have become a little wider.
This is because some of the columns, in the pivot tables, have become a little wider .
We will also make columns, eye, and queue, narrower on each workbook, to bring the pivot tables closer together.
Now, you have completed dashboard eleven.
The dashboard eleven you have on your desktop, should look identical to the dashboard 11 on the video.
Now, we will go through on the video, and show you exactly what you must do, to create the next five dashboards.
Please follow along very carefully with what you see on the video.
Here we go.
Now you should have all six new dashboards created, and you should be able to drill in to the pivot tables.
The next piece you are going to implement, is the drill down mechanism, from dashboard oh one, to the six lower level dashboards .
You will also implement the drill up mechanism .
Please note, the three dots, which are used as the picture, is just a small JPEG .
It is used to hyperlink between dashboard oh one, and the six lower level dashboards.
You can copy the three dots from the example worksheet .
You could also use any other small JPEG you would like.
For example, you could use a small arrow.
The red arrow, that is used to drill back up to the high level dashboard, is also just a small picture, created in PowerPoint.
You can also copy that red arrow from the sample workbook.
You could also use another small picture, such as a home icon .
How you design your navigation for drill up, and drill down, is really up to you.
So, here you go.
You will now implement the drill down, and drill up, as demonstrated in the video.
Please follow along on your desktop, and make every change we demonstrate, to your copy of your dashboard.
Please note that we made a small error.
The BIDA logo should be hard into the top left corner .
So, you will see us correct this error on the video .
OK. Now you have completed implementing the drill down, and drill up.
Now, you must come back to the slicers.
When you created the 18 pivot tables, on the six child dashboards, you did not connect them to the slicers.
Now, you must connect each of the three slicers, to each of the 18 pivot tables .
Now, please follow along with the video and do that in your workbook.
You will see on the video, that you only need to connect the slicers, on the high level dashboard oh one.
All the other slicers, on all the other dashboards, should inherit the new connections.
Now, we will do a little bit of final formatting .
You can follow along on the video.
Next you should remove the headings, and gridlines, to make the dashboards look nicer .
The next thing you want to do, to make sure the dashboard looks good, when delivered to users, is to hide all the worksheets that the user does not need to see .
So, now you will hide all the extra worksheets .
Please follow along closely with the video.
Now, This is your finished template dashboard .
Hooray .
The last little piece we need to do, is to send the template spreadsheet, into the eye Excel icon , send a dummy spreadsheet into the eye Excel icon, and then send the final spreadsheet to the PC directory.
So please follow along closely, with the demonstration on the video.
You want to get your capsule, on your desktop, to run the same way.
This last portion of the capsule will make the capsule re runnable.
It can be run on command, or as part of a daily batch stream .
For example, it may be desirable to run such capsules once per day, after the ETL processing.
In this way, the Excel dashboards, that are created by these capsules, would contain the latest data, from the ETL processing.
This is how Excel dashboards could be refreshed every day .
Please note.
You can only run capsules in batch streams on the two dollar a day Meta5 version.
You could have two or three desktops, spread across different departments, that run the daily Excel dashboards for each department, after the ETL finishes.
In this way, single user versions of Meta5 could be deployed in different departments, and each department could operate independently of each other.
Of course, when there were more than 5 copies of Professional Edition being used in one company, you would then consider upgrading to the Enterprise Edition, for the extra value it offers.
Please note .
To allow many capsules, to run at the same time in production, you should prefix the word, final, for the spreadsheet name, with the specific report name.
During the execution of the capsule, meta five will create a temporary copy of this spreadsheet, on the windows file system .
So, you need to be careful, to not create any conflicts, with spreadsheet names, in the temporary directory.
Now, we see that the data spreadsheet, in the working directory, is a couple of days old.
So we will rerun the placement, of the data spreadsheet, in the working directory .
Now, in the eye Excel icon, you need to option it, and go into show controls .
Now, you need to go down, to the input cell, for advanced commands.
In the advanced commands cell, you must place the following command.
Minus sign, followed by the letters, r, a, d.
This command tells Excel to refresh all data connections, to refresh all pivot tables, and to refresh all charts inside the Excel spreadsheet.
By doing this, the final Excel spreadsheet, that will be delivered to the PC directory, will be up to date.
The user will open up the updated spreadsheet, and will not need to refresh any data to see the charts.
On the first slicer click, the spreadsheet will load the power pivot model, into the memory of the PC.
If the dashboard is being viewed, in the web window of Office 365, then the first slicer click seems to be much faster.
Now, we will go out to the PC directory, and we will open up the dashboard, from the PC directory.
We will do a small demonstration of the dashboard in the PC directory.
This is just to prove that it has been properly placed in to the PC directory.
And that, ladies, and gentlemen, is your first Excel, slicer based, demographics, dashboard.
If you have followed along in detail, and you have been able to get your dashboard, to act exactly the same as the dashboard in the video, then I want to wish you, congratulations.
Congratulations !
Well done !
This concludes this training series, on how to create the demographics dashboard, we have released as a set of videos.
This set of design principles, to build Excel dashboards, will work with just about any data.
What you have seen, is the ability to gather data, from almost anywhere, and send it into the Excel power pivot model.
Once the data is placed into the power pivot model, all the functionality of Excel can be used.
You should consider meta 5, as a tool, that will collect data, from anywhere you want, in a repeatable, and parameterized fashion .
Then meta five can send that data into Excel, and refresh all the charts and tables in Excel, all ready for a user to use .
You should consider Meta 5 to simply be a better way of building Excel dashboards .
Of course, Meta5 does much more than that.
However, we want you to focus on the decision to acquire Meta5, to be the decision to spend one dollar a day, on making it easier, to get data in to your excel workbooks.
And, we want you to focus on the upgrade to 2 dollars a day, to get more disk space, and to run the capsules in batch, while you are not even at your PC.
We want you to know that you can develop capsules on the one dollar a day meta5, and deploy them to run in the daily batch, on the two dollar a day meta5.
We want you to know, that once you have 5 or more professional desktops running capsules, it might be about time to consider the upgrade to the Enterprise Edition.
Meta5 is Enterprise level software, that can scale out, much further than any company in Romania would ever need.
Now I would like to say thank you very much for your time and attention.
We really appreciate that you have taken the time to investigate how to build Excel dashboards using meta 5 .
We hope we have been able to teach you something new and valuable.
If you like what you saw in this demonstration, and evaluation, you can get started today with meta 5 with the single user starter edition.
You can buy the licenses for starter edition, and professional edition, directly from meta 5, on their website .
The full set of manuals, is also downloadable, from their website.
If you have any questions about Meta 5, that you would like answered, before you buy your license, we would be very pleased to answer your questions.
You will find our contact information, right here, on this blog post.
As I said at the beginning, this has been a major publication effort for us.
We have taken the internal training we use for our own staff, and we have made a public version, to teach you almost exactly what we teach our own staff.
Given the amount of effort we have put in to present this video training to you?
I really hope you liked it.
Laugh Out Loud!
Now, in finishing this epic video training.
I would like to say thank you.
Thank you very much for listening.
I really appreciate your time and your attention.
Thank you.
I wish you a good day.
