HD0012 – How Do I Re-Create The Demographics Dashboard – 01

0
146

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

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

By the end of this series of videos, you will have all the information and experience you need, to decide whether you would like to get started with the Meta5 starter edition, or pro edition

In this training, you will build an Excel dashboard that is actually at the entry level of useful to businesses.

This dashboard and this sort of reporting is the sort of thing that businesses use and need.

Businesses need demographic analysis of their customer base.

This dashboard is actually useful for companies that sell to consumers where they know the identity of the consumer.

This includes all web stores, and retailers who have loyalty cards.

Profiling customers by demographics is actually useful to businesses like that.

We have been using this sort of reporting for nearly 30 years now.

They are still as useful today as they were 30 years ago.

These sorts of reports help to understand the customer base that retailers and other similar businesses have.

So if you want to evaluate Meta5?

This dashboard is a good way to do that.

Ok?

Now in this training some of these things are over done.

Some things are done twice.

Some are done in different places.

It is more than what you might see in a regular capsule.

They are over done for a purpose.

The purpose is to demonstrate that certain things you do could be done one way in one place, or could be done another way, in another place.

So you will learn how these things are done in multiple places.

I am speaking specifically about variances.

I will do other videos explaining variances to you, and why they are important.

This training video is to get you to recreate the demographics dashboard, piece by piece, icon by icon, statement by statement, all the way through.

By doing so, 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.

With that introduction complete?

Let us get on with the Training.

So.

Hello and welcome to the video training called, how do I recreate the demographics dashboard.

You should have this icon on your desktop.

12. H D 0 1 2. Demographics Demo.

You should be able to refer to it.

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

I have recorded every mouse click and every moment.

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 is 100% generated by a random number generator.

That is except the obvious things like years, months and made up product categories.

All the data itself, age band, income bands, and so on, are all generated data.

So the patterns that they produce don’t produce sensible patterns, in the creation of the charts and reports.

The reports do not tell you anything useful because they are based on truly randomly generated data.

So this is what you are going to build.

You can see how it works.

You can see that I have removed the query icons.

This is because these query icons contain the ODBC connection information for the customer site.

Anything that could possibly show who the customer, that this dashboard was build for, has been removed.

You can see the capsules.

We are going to build all these capsules.

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

The capsule collects data.

The data goes through to the spreadsheet.

It goes through to the P C directory.

You have got the Final spreadsheet.

You have been asked to go in to it and take a look around.

If you do not have it, then you should download it from our web site, and look around it before you start this training session.

So, as I said, we have rebuilt this capsule for you on the screen and recorded it.

You are going to see everything needed to build the demographics dashboard.

Here we go.

The first thing you need is a capsule.

You are going to call it, demographics testing demo.

You can put the capsule run log over to the right.

So you want to copy the things you need in to the capsule.

You should keep a text icon to the right for when you want to copy text.

Now you will put items in to the capsule.

The first thing you need is a capsule.

You are going to call that capsule Age Band.
You need a spreadsheet.

You are going to call the spreadsheet Age Band.

Inside the Age Band capsule we are going to put some icons.

Of course, inside the Age Bands capsule, you need the actual input data.

So you have to go over here.

You are going to open the sample demonstration capsule.

You are going to open Age Bands.

You are going to copy the three spreadsheets and the text icon.

You should be provided with those.

The spreadsheets will have the initial data in them.

You can see the data that the spreadsheets contain.

As I said earlier.

These numbers are manufactured.

Now we will go through what is in each spreadsheet.

Each of the 6 capsules you will build has 3 spreadsheets for data.

The spreadsheets in the same position in each capsule contain similar information.

They contain the year, the month information, and the product category information.

Then the spreadsheet will contain the data related to the subject area for this capsule.

For example, text of age band, text of income band, text for marital status, text for life stage and so on.

Then the capsule contains sales extended amount, which is the total revenue.

Then it will contain units extended amount, or total units sold for that category.

Of course, in the category there might be units sold that vary.

For example, if the retailer was selling jars of coffee, the jars might be different sizes, but all sales units would be included in the coffee category.

So, for this sort of analysis, sales extended amount is more important than the sales units.

Where the units are the same size all the time in the category, sales units is more important.

The second spreadsheet in each capsule drops the text field such as age band, income band, marital status and so on.

It just has the year, month details, product category, and then the total sales extended amount and total sales units.

These are the month level totals.

So the first spreadsheet has the sub totals at the break level for the subject area.

The second spreadsheet has the totals.

This is to be able to calculate variance of sub totals to totals.

Using these numbers, you can calculate the contribution of the sub total to total for subject area.

The third spreadsheet contains how many sub total elements were contained in the totals by month.

The number of sub total elements might change from month to month as well, and so they must also be available at month level.

Using the sub group counts, for example 18 sub groups for age band, it is possible to calculate the average contribution by age band.

It is the total contribution divided by 18.

Then, once you have that calculation, you can calculate the variance of the sub group to the average.

This tells you your over performing sub groups, and your under performing sub groups.

Both are very important to know in consumer sales and marketing.

They are especially important to know for cross -selling and up selling products and services in the consumer sector.

Next you see a piece of SQL.

This SQL is held in a text document and sent in to the database because the SQL can not be generated by the query icon.

Custom hand coded SQL like this can be saved in a capsule and sent in to a database and the results put in to a spreadsheet.

The SQL is run by using the SQL Entry icon, which contains the credentials to the database server.

This is how the data for the third spreadsheet was requested.

The count distinct on age bands can not be generated by the query icon.

So we generated SQL code that was nearly what we wanted.

Then we edited that SQL code and put it in a text icon.

And then we copied a SQL Entry Icon in to the capsule.

We put in the credentials to the database.

And then we connected the SQL Entry Icon to the spreadsheet.

You can see these icons in the P D F documentation available on our web site.

They are not in the capsule because the SQL Entry icon contained the customer database credentials.

So as your starting point you are getting this generated random data in these spreadsheets.

You are getting this data for all 6 capsules, which are for all 6 subject areas of demographics.

So the next thing you need is the multi-join.

That is in transformer icons.

It is in the aggregate and combine data folder.

So you will copy the multi join to your capsule.

You need another spreadsheet icon.

You need a spreadsheet icon for headings.

You need another spreadsheet icon at the end of the row.

You need a clean icon.

You need a compress icon.

You need an out icon at the very end of the capsule, to send the data out of the capsule.

You want to rename this spreadsheet to be called headings.

In the multi-join you have Table 1, Table 2, Table 3, Table 4, Table 5.

So you will connect them.

In a multi join you must always pass data to Table 5 to signify the last table.

If you have three tables you can not send them to Table 1 , Table 2 and Table 3.

You must send them to Table 1, Table 2 and Table 5.

Connect multi join to the spreadsheet icon.

In the transformer controls for the clean icon, you want to set the following setting.

There is 1 row for a heading row.

You want to clean rows, yes.

Columns to check.

You want to check column a.

In clean columns.

You want to say yes.

So the effect of the clean icon is that you will check column A.

If column A contains data then you will allow the data to flow through.

If there is no data in column A, then you will not allow the row to pass through to the next icon.

You will see why that is in a minute.

Connect the clean icon to the compress icon.

In the compress icon all you want to do is to delete the heading row.

So you say delete rows equals 1.

In the target spreadsheet we need to create 2 regions.

The first region is for headings.

So you click on A 1 and extend to Q 1.

Then click on the star in the top right of the spreadsheet headings.

Then click on name region option.

You will call the region headings.

Then you want to select A two.

And go to the star in the top right hand corner and select name region.

And in the spreadsheet options you want to set the region to A two to Q two.

And in the label field you want to set it to Data oh one.

Please be careful to use capitals at the beginning of region names.

Region names are case sensitive.

The compress icon is going to send data to the Data oh one region name.

In the headings spreadsheet we need to create the headings.

So you open the headings spreadsheet.

You select cell A one.

You click on the star.

You click on name region.

You put in A one to Q one for the headings.

In the label field you put the name Headings.

Then close the spreadsheet.

Connect the Headings spreadsheet to the last spreadsheet with an arrow.

Option the arrow.

Enter the region name Headings as both the, from, and, to, region names.

Now we are on the video where we correct the mistake of setting the last table name going in to multi join should be table 5.

You will see the correction being made on the video.

Now you need to go in to transformer control for multi-join.

The columns we want to join on are columns a, b, c, d and e.

You must copy these values for Table 1, Table 2 and Table 5, as shown in the video.

Number of heading rows should be set to 1.

You want to set join type to left join.

This is because the first table will have all possible records.

When there is no join we want the zero value to be put in to the data stream.

You want to set ignore case to yes.

Now. In the heading spreadsheet we need to put the headings in.

So please follow along on the screen and make sure you copy the headings exactly.

If you want to cheat a little, you can copy the column headings from the sample capsule.

We trust that you can easily remember how to type in column names.

Laugh Out Loud.

So now you should have the headings in your output data spreadsheet as shown on the video.

So now we are going to run the data from the three source spreadsheets, through to the final spreadsheet, and see if it works.

We open up the final spreadsheet.

We notice that the calculated columns are not in the final spreadsheet.

We have forgotten to put the calculated columns in to the spreadsheet after the multi join.

We do not have columns L through Q.

So we must go in to the spreadsheet that receives data from the multi join.

You can see the data that is the result of the multi-join.

So you select A1 and extend to K1.

Then go to name region.

You will know where that is by now.

And name the region A 1 to K 1 as Data oh one.

Then select cell L 1 and go to name region.

In the spreadsheet options set the coordinates to L 1 to Q 10 thousand.

Set the label to Calcs oh one.

So we are giving the calculation region a maximum of 10 thousand rows.

Now, we could make it 20 thousand rows, or 30 thousand rows.

We could even make it 100 thousand rows.

I am not sure what the maximum number of rows is for a spreadsheet in Meta5 now.

But for the sake of this demonstration, 10 thousand rows of calculations is more than enough.

So now we need headings in the calculations area.

So please follow along and put the headings in as demonstrated in the video.

Now we will add the calculations in the columns.

Average Units by Age Band is J two divided by K 2.

Extended Average Amount is I two divided by K 2.

Variance Units is H 2 minus L 2.

Percentage Variance Units is N 2 divided by L 2.

The number format symbol for the cell is the percentage sign.

Variance Revenue is G 2 minus M 2.

Percent Variance Revenue is P 2 divided by M 2.

The number format symbol for the cell is the percentage sign.

We will just close the spreadsheet to save it and then open it up again.

So how do we put the formulas in to the spreadsheet for 10 thousand rows.

We highlight an area and click on the replicate, from, button, on the header.

So watch closely on the video and you will see this happen.

Notice we need to replicate the formulas all the way down to row 10 thousand.

So we must copy blocks of the first column of the formula to get to 10 thousand rows.

Notice that line 3 8 9 1 comes up as an error.

This is correct and is what you should expect.

After we copy a couple of times we will be past 10 thousand rows.

So we just delete the error rows past the line of 10 thousand.

We then close the spreadsheet and open it again.

You will see the calculations.

You will see we can scroll down to line 10 thousand now.

You only have to go through this copying process for the first column in a set of calculations.

So we have 3,888 rows of data, and we have designed the spreadsheet to support up to 10 thousand rows of data, for this particular capsule.

You must make sure that the number of calculation rows you put in to the spreadsheet is always more than the actual data rows you might have returned from the database.

So now you can highlight the M 3 column.

Scroll to the bottom.

Right click to extend the selection.

Scroll back to the top.

Click on replicate from.

Click on M 2.

And you will replicate the calculation for M 2.

Now repeat for the other columns as you follow along on the screen.

So now I will close and open the spreadsheet to save it.

And I will demonstrate that the two regions are there.

Please note on the video.

We missed sending the data, to data oh one, when connecting the multi join to the following spreadsheet.

So we will set the arrow to point to data oh one, correct the workbook, and re-run.

And we will spare you the time of watching that.

Laugh out loud.

So, what the clean icon did, was to remove all the columns of calculations, where there was no data in column A.

The compress is removing the one heading row.

In the final spreadsheet you can see the headings are there and the data is there.

When we scroll to the bottom, we see there are 3,889 rows, in the final spreadsheet.

You have added 6 calculation columns.

You have completed your first capsule.

The data is going out of the out icon.

You need to connect the capsule to the spreadsheet, reading data from the out region of the capsule.

Then you will run the capsule again.

And you will see the data goes to the Age Bands Spreadsheet.

You can see it has taken 3 queries, a three way multi join, and a spreadsheet with a set of calculations in it, to produce this set of data that is going to go in to our Excel power pivot model, as part of our dashboard.

This is a very typical way of creating data to put in to an Excel power pivot model.

This is one set of data for one chart.

And now you have to do five more.

So, what I will do now, is I will end this video here.

I will start the next portion of the training on a new video.

Just before I go, I have one more piece of the blog post we wrote, that did not fit in to the video.

So I thought we would add it here at the end.

Please remember this piece of advice because it’s important.

Of course, in the category there might be units sold that vary.

For example, if the retailer was selling jars of coffee, the jars might be different sizes, but all sales units would be included in the coffee category.

So, for this sort of analysis, sales extended amount is more important than the sales units.

Where the units are the same size all the time in the category, sales units is more important.

And now?

I would like to say thank you very much for listening to, and watching, this training video.

I really hope you liked starting this first public training for Meta5.

Thank you very much, for your time and attention.

I will see you back for the next video.

I wish you a good day.