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 back to the next exciting installment of our Meta5 training video.
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 the previous video, you completed the creation of the first capsule, to collect sales data by Age Bands.
There are five more similar capsules to create.
This video will have much less dialogue and much more on screen demonstration.
In this video, you should follow along closely on the screen, as you re-create the capsules, on your own Meta5 desktop.
This is a great way to evaluate Meta5 for use with Excel.
Ok?
Are you ready to get started?
So on with the demonstration!
Now, you will make five more copies of the Age Band capsule.
Now, you will make five more copies of the Age Band spreadsheet.
Now, you will rename the five capsules.
The other five names are income bands, life stage, marital status, gender, number dependents.
Now, you will rename the five spreadsheets.
Now, you need to connect the five capsules to the five spreadsheets.
You need to make sure you link the out region from the capsule in the arrow to the spreadsheet.
Now, you need to go through each of the five capsules, and replace the input data spreadsheets.
You replace them with the input data spreadsheets in the sample capsules.
Then, you need to put the headings in to the heading spreadsheet, and make all the other changes needed to update the five other capsules.
Please note, in the life stage capsule, there is an extra column, called life stage code.
The life stage code is required to get the life stage description to sort in the correct order.
As a general rule, any time you would like to sort a column in an order other than the column sorts itself, then you must put a sort column into the power pivot model as well.
So, as you create the spreadsheets in the life stage capsule, you must remember to add this extra column.
You will be able to see exactly how this is done in the video demonstration.
Notice, that to get the spreadsheets from the standard demo you can use the place holder function in meta 5.
By using the place holder function you will not have to replace the arrows.
You will see this demonstrated in the video.
Now.
You have done the 6 capsules to get the data needed from the database for this dashboard.
Now, you need to get the data for Years, Months and Product Categories.
You can follow along on the video and copy the actions on the video.
These three capsules are quite easy to create.
Please note, you are going to copy the years capsule, and update it to create the months, and product category capsules.
Now.
The next piece that you have to do, is the piece of collecting all the data from all these spreadsheets, and sending it into an Excel workbook, which will be placed on to a drive on the hosting PC.
You are going to create the collect data spreadsheet.
You need to go and copy a new spreadsheet out of the blank icons folder.
You will call it collect data.
You have to create regions in the collect data spreadsheet, to receive all the data from all the other spreadsheets.
You are going to do that by creating 6 regions for the fact data first.
Then You will create the three regions for the dimensions second.
All the data that will be sent to Excel will be collected in the one collect data spreadsheet.
This is why the spreadsheet is called collect data.
Please note.
When you are creating regions in a spreadsheet with no data in it, you may not be able to scroll to the right using the scroll bar at the bottom of the spreadsheet.
You may have to select a spreadsheet cell, and then press the tab key many times, to scroll to the right.
You should also be able to use the right arrow key to scroll to the right.
The first region is age band.
You need to click, and select, column, A, 1.
Now, go to name region.
In the label field you enter, age band, data, oh, one.
You will make the coordinates, A, 1, colon, Q, 1.
You will see on the spreadsheet the column names go up to Z, and then start again at, A, A.
So you will start each new region at the, A, column.
The next region is for income band.
You will click on, A, A.
You will click on name region.
You will set the coordinates to, A, A, 1, colon, A, Q, 1.
In the label field you will type in, income band, data, oh, one.
The next region is, life stage, data, oh, one.
There is one extra field in the life stage region, so you need to be careful to add the extra column.
Please follow along with the video to watch exactly what needs to be typed.
The next region is, Marital Status, Data, oh, one.
Please follow along with the video to watch exactly what needs to be typed.
The next region is, Gender Data, oh, one.
Please follow along with the video to watch exactly what needs to be typed.
The next region is, Number Dependents, Data, oh, one.
Please follow along with the video to watch exactly what needs to be typed.
Now you are up to the regions, for the dimension data.
Please follow along with the video to watch exactly what needs to be typed.
Now, you must remember the names you gave to all the regions.
This is because you are about to connect the nine spreadsheets, with the one collect data spreadsheet.
So you will select age bands, and click connect icons, and then click the collect data spreadsheet.
Then press F 4 to option the arrow.
In the copy data, to, collect data section, you want to select destination area other, and in the region name field you will enter age band data oh one.
You will repeat this process for the other eight spreadsheets.
Please make sure you get this correct, or you could damage your collect data spreadsheet.
In fact, it is a good idea to copy the collect data spreadsheet before you run this capsule again.
Now, you will run the arrow just for age bands.
Now, select the arrow between age bands, and collect data, and click run.
Now, open the collect data spreadsheet, and you should see the data for age bands, starting in column A, of the collect data spreadsheet.
Now, you will run the data from the next five spreadsheets, into the collect data spreadsheet.
You do this by highlighting each spreadsheet, and then clicking run.
You can also run the data from the three spreadsheets called years, months, and product categories.
Now, open up the collect data spreadsheet, and it should appear like the one on the video.
It should be properly populated with the data from the nine spreadsheets.
You will notice in the video I have not got the correct data in number dependents.
You will see me correct this problem on the video.
We left this small error in the video for you to learn from, as an example.
You will also see that the product categories dimension was not set properly.
You will see me put the correction into the capsule in the video.
Now, you have all the data you need in the collect data workbook.
Now, you need to send that data into an Excel workbook.
You should copy the spreadsheet name, from the completed demonstration capsule, using the text document in the top, right hand, corner.
This saves you typing such a long name.
Now, you need to get a new Excel spreadsheet from the blank icons folder, and place it next to collect data.
Now, you need to get a new PC directory icon from blank icons, and place it next to the Excel spreadsheet.
Now, you need to set up to send all the data from all the regions in the collect data spreadsheet, and send them to regions inside the Excel spreadsheet.
Now, you will create the nine regions in the Excel spreadsheet.
To make this a little easier, and a little less prone to error, you will copy all the region names from the arrows in to the text document.
Now, you will start Excel and you will create all the regions in the Excel workbook.
Please follow along with the video, to create the nine regions, exactly as specified on the video.
Now, you must link the collect data spreadsheet, to the Excel spreadsheet, using the connect icons function.
To make sure you copy all the region names absolutely correctly, you will create the list of names in the text document.
You will then cut and paste the list of region names, into the correct places, in the arrow options.
In this way, you will avoid typing mistakes.
So please watch the video carefully, and repeat exactly what you see on your desktop.
Just while we are here, I would like to mention something important.
Inside a meta 5 capsule, we can execute any program that can run on windows, as a batch command.
Inside a capsule, we can also run all the meta 5 tools, including all the statistical analysis tools.
So, we have very many things we can do, to perform calculations inside the meta 5 capsule, before we send the finished data into an Excel spreadsheet to be displayed.
You will see what I mean from watching the video.
Now, you will connect the collect data spreadsheet to the Excel spreadsheet, and you will cut and paste the region names correctly.
So, watch the video closely, and do exactly what you see.
Now, click on collect data, and click on run, and see if you set the arrow options correctly.
OK.
When you see the capsule has finished, you will go over to the C drive, and see if the spreadsheet is in the PC directory.
Now, you should open the spreadsheet on the hard drive, and you should verify that all the data was correctly transferred, to your spreadsheet, on your hard drive.
In the video, I will take you through the verification process.
You should also check the version of the spreadsheet, inside the meta 5 capsule, as demonstrated on the video.
To summarize what this capsule does, I have collected data through six different capsules, for mini fact tables.
I also collected the dimension data, that will be used in Excel slicers.
Inside meta five, we could collect as much data as we want, from as many different places as we want, and perform as many calculations on that data, as we want.
We can then send all that data, into an Excel workbook, ready to be presented, as dashboards and reports.
This is the major value you should see in meta 5.
The ability to get whatever data you need, into your spreadsheets.
Ok?
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.
Ok?
I wish you a good day.