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.
You can watch an overview demonstration of this dashboard here.
You can watch a detailed demonstration of this dashboard here.
Go to this blog post to learn how to download all the materials for this demonstration.
Click this button to download the demonstration files.
Hello and welcome to our new blog post.
I really appreciate you dropping by to watch this new blog post.
I really appreciate your time and attention.
Thank you.
This series of blog posts is a major publication for us.
This series of blog posts are intended for people who wish to consider using Meta5 to build Excel dashboards, faster, cheaper, and better, than you build them today.
If you have reviewed the previous blog posts, and you are interested in evaluating Meta5, to build Excel Dashboards?
Then this series of blog posts, will give you the chance to download the Meta5 evaluation edition, and to build a real world, useful dashboard, using Meta5 and Excel.
In this training, you will build an Excel dashboard, that is based on the Adventure Works Database.
It is not as complicated as the Demographics Dashboard, which is the following series of blog posts.
This dashboard is the entry level example of an Excel Dashboard.
We are publishing it for you as a piece of training material.
So, if you want to evaluate Meta5?
This dashboard is a good way to do that.
Ok?
This training video is to get you to recreate the Adventure Works dashboard, piece by piece, icon by icon, statement by statement, all the way through.
By doing so, you will have a good introduction, to how Meta5 can help you create Excel Dashboards.
The following training series, on how to build the demographics dashboard, is more complicated, and more detailed.
By doing both trainings, you will have all the information you need, to make your decision to buy the Meta5 Starter Edition, or Pro edition, to help you get your job done, in your company.
I am very happy, you are going to do this training, to evaluate Meta5, in detail.
Thank You.
Now.
With that introduction complete?
Let us get on with the Training.
Hello and welcome to the video training called, how do I recreate the Adventure Works dashboard?
As with prior demonstrations, I have the demonstration capsule on my left monitor, as I build the demonstration capsule on my right monitor.
That is just so you know how we are creating these demonstrations.
Of course, this voice over is being added after the video has been recorded.
I have rebuilt this capsule, from scratch, on my screen, and video recorded most actions.
When creating the video, I recorded a voice over.
We then transcribed the voice over, and edited it into the video, to make this public video.
We actually use the original video created for internal training.
However, for our employees we have included comments that are not appropriate for the public.
So you are watching the public version of our internal training video.
All the data you are going to see comes from the Adventure Works database.
So this is what you are going to build.
Now.
Let us get going!
You should have this icon on your desktop.
11..
Adventure Works, Example Report, Oh One.
You should be able to refer to it.
You can download it from our web site on the button below.
You can see how it works.
You can see the capsules.
You are going to build all these capsules.
You are going to build them all from scratch.
It is going to be fun for you!
There is quite a lot of work in here.
You can see on the screen, at the moment, that I am opening up the sample report.
You are going to rebuild this sample report.
So here we go.
The first thing you need to do is to create a new capsule.
You will get it from Blank Icons from inside New Icons.
You can call the capsule, My, A, V, First Dashboard.
So if you look at the sample capsule.
The first icon you need is a query icon for the sales territory.
You get a blank query icon, from blank icons, and put it in to your new capsule.
Call it sales territory.
In the options, for the sales territory icon, you need to set the database name, to be the 32 bit ODBC connection name.
You will have set up this connection, when you downloaded the Adventure Works database, and restored it to your SQL Server.
Now we open the query icon.
It is an empty query.
We can look in the sample report to see how to create the query.
I have the sample report on a second screen, that is not visible in your recording.
We do our video recording for demos, on a demo machine, that has nothing else on it.
Now.
When I direct you to look at the sample capsule, you will need to go to the sample capsule, on your desktop.
I hope that is all easy for you to understand.
Because we have a blank query icon, we don’t have anything in the DBA catalog.
So you need to click on, show DBA catalog.
We need the sales territory.
So click on, sales territory.
Then click on, show user catalog.
We need the Sales Territory Group.
This is the field, that is going to be passed to the second capsule.
We want one spreadsheet dashboard per sales territory group.
So I will demonstrate this briefly.
I will open the sales territory icon, in the sample capsule.
I will capture the sql.
You can see in the capture of the SQL, that I am selecting distinct sales territory group, from the sales territory dimension table.
I have a, where not equals to, N, A, clause set as well.
So we copy sales territory group to the query output for the query icon.
And we want to apply the constraint, not equal to, N, A,.
Now.
We want the sales territory group, to be sent in to the next capsule, as a variable.
And the variable will be, at, A, A.
The way this is done, is to option the sales territory group field, and set the field label to, at, A, A.
In meta5, the field label is synonymous with the field name.
We often use those terms interchangeably.
Now.
When you select sales territory group, you will see there are duplicates.
So you need to click on, unique rows.
This will add the distinct clause to the SQL generated.
When you click, show data, again, you will see unique values.
So now you have your first query, three items coming out of the query icon as, at, A, A.
So now you need a knew capsule called, A, W, capsule, Oh, One.
You go to new icons, blank icons, and copy out the capsule icon into your capsule.
You want to connect the sales territory icon, with the new capsule icon.
You may see the message, arrows to capsules must specify one destination region.
You must option the arrow, and in the field, copy data to capsule, region name, you must say , controls.
Now I will open, A, W, capsule, oh, one, and you will see the final capsule that you will build.
Inside the report capsule you need to copy five more capsules.
Please name the capsules as you see me name them on the screen.
You need a capsule called, sales facts, oh, one.
You need to create a series of spreadsheets.
Name the spreadsheets as you see me name them on the screen.
You need to connect all the icons.
So please follow along with the example on the screen.
We are not naming the regions, for all the arrows, that we are creating.
That is ok.
You will do that later.
Notice that the sales territory query icon, already has the information defined, to connect it to the adventure works database.
You will copy that icon to create queries.
You will copy the sales territory query icon, in to the years capsule, and you will rename the query icon to be, years.
You need to place three spreadsheets, in to the years capsule.
You need a, remove header, transformer.
That is in the transformer icons, filter data, folder.
And it is the compress icon.
You will rename the compress icon to be, remove header.
Just to show you this.
I will go into the sample capsule, and I will show you that the program name of the remove header icon, is compress.
Now, you will see me do that in the video.
You can see that the remove header icon is blue.
The colour coding of icons, helps you find the icons in the blank icons, and transformer icons, folders.
So.
You want a compress icon.
You will copy the, compress icon, in to your capsule.
You will copy the, out icon, in to your capsule.
Change the name of the top spreadsheet to be, headings.
Change the name of the clean icon to be, remove header.
Now, you will connect the icons, as you follow along on the screen.
You will rename the out icon to, years.
Now, open up the years query icon.
You have the old query for sales territory.
You need to build a new query for years.
So, click on, show query catalog.
Click on, dimension order date.
Click on, Query catalog, to go back.
Click on, clear query, in query control, to delete the existing query.
Copy, order calendar year, to the query output.
Click on, show data.
You will see you have 3,652 output rows for years.
Now, click on, unique rows.
Click on, show data.
Now, you will see you only have 10 unique rows.
Now, you are only interested in years, 20, 12, and 20, 13.
So option the field, order calendar year.
Click on, show all choices.
In choices, select, 20, 12, and, 20, 13.
Click on, clear data.
Click on, change sort settings.
Copy the field, calendar year, in to sort settings, sorting level 1, field.
Now click on, show data.
You will see you get, 20, 12, and 20, 13, in that order.
Now open, remove header.
Click on, show controls.
In the field, rows to delete.
Type the number, 1.
This will delete only the first row, which is the header row.
Close the remove header icon.
Now open the spreadsheet called, headings.
You want to have control of the headings.
This is so that you send the headings to Excel, and these can not be changed by anyone else.
The names of the fields in meta five can be changed by the DBAs.
If the name of a field is changed in meta 5, it will damage the capsule.
Now you will create a region called, Headings, in the Headings spreadsheet.
And you will put the value, Year, in the heading.
Now open the target spreadsheet.
You have to create two regions.
Create the first region called, Headings, by following along on the screen.
Create the second region called, Data, oh, one, by following along on the screen.
Now option the arrow, between the headings spreadsheet, and the target spreadsheet.
In the, copy data from spreadsheet, section, click on, other.
Type in, Headings.
In the, copy data to spreadsheet, section, click on, other.
Type in, Headings.
This will copy the headings, from the headings spreadsheet, to the target spreadsheet.
Now, option the arrow, between the remove header icon, and the target spreadsheet.
In the, copy data to spreadsheet, section, click on, other.
Type in, Data, oh, one.
Now, you can run the capsule.
You can click on the headings spreadsheet, and then right click on the years query icon, and then click on run.
You can open your final spreadsheet, and it should look like the one on the screen.
The data is going to flow out of the capsule, through the out icon, called years.
Now.
Go to the outer capsule.
Option the arrow, between the years capsule, and the years spreadsheet.
In the area, copy data from years, click on other.
In the region name field, enter, Years.
This will retrieve the data, from the years out icon, in the years capsule.
Now.
When you try and run the capsule, as it is, there is an error message, telling you that it can not run the capsule.
So, you will delete the arrows, that are stopping the capsule from running.
Now it will work correctly.
So follow along on the video to see how this works.
When I put the arrows in the capsule earlier, I did not realize it would not allow the capsule to run.
So.
The years capsule works.
It goes and gets you the years from the database and send them in to the years spreadsheet.
Now you are going to do the Months capsule.
So please follow along on the screen.
Now.
You copy the icons, from the years capsule, to the months capsule.
Rename the query icon, and the out icon, to months.
You need to edit the headings spreadsheet.
You will have two headings.
Month num.
Month.
Now, you need to connect the icons.
Now, you need to option the arrows, and set the regions, in the arrows, correctly.
Now, edit the months query.
Now, you can run the months capsule.
Now, you can connect the months capsule, to the months spreadsheet.
You need to set the arrow options correctly, as you see on the screen.
Now that you have the idea of how to do this.
You should follow along with, countries, product categories and currencies.
You can watch me on the video.
And you can do the editing on your meta5 desktop.
You can copy what I am doing, and you will learn how to do these capsules, by copying me.
Now.
When we are selecting countries, we want to have the variable, at, A, A, set for countries.
Now.
If you run the query, there is no sales region, where clause, set, so you will get all sales countries.
You must go and edit the variable, in the capsule, to set the variable, at, A, A, to, Europe.
And then, when you run the query again, you will just get the United Kingdom, France and Germany.
These are the sales countries in the Europe region.
You can run the query, with capture sql open, and you will see, the sales territory group equals Europe, is set as a where clause.
Meta5 is getting the value, Europe, from the, At, A, A, variable, and inserting it, into the SQL statement.
For product categories, and currencies, we will copy the years capsule, and then edit it.
I will show you how to create these capsules in the demonstration.
So now you want to delete the regions, and the data, that was sent into the spreadsheet, called, Collect Data.
Now.
You need to create regions in the spreadsheet called, collect data, to collect the data from the spreadsheets, created so far.
So follow along on the screen.
You will see how to create all the regions, that are needed in the collect data spreadsheet.
Now.
You need to connect all the spreadsheets, with the collect data spreadsheet, by setting the regions properly on the arrows.
Notice we had a problem with countries.
You will see me correct the problem, in the video.
The next significant portion of work, is to create, the sales facts, oh, one, spreadsheet.
Because it is a fair amount of work, I will break the video training here, because this is a logical break point.
This concludes this portion of the demonstration.
I hope you liked it.
Thank you very much for listening.
I really appreciate your time and your attention.
I will see you back, in the next exciting installment, of using Meta5, to make building Excel dashboards, faster, better, and cheaper.
I wish you a good day.
