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

0

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.

Download Demo

Go To Prior Demo

Get Dashboard

Get White Paper

Get Meta5 Trial

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.

Contact Us

We will be emailing our subscribers useful and
valuable information regarding business intelligence.
Please subscribe here to be on our general emailing list.

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

0

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.

Contact Us

We will be emailing our subscribers useful and
valuable information regarding business intelligence.
Please subscribe here to be on our general emailing list.

HD0011 – How Do I Re-Create The Adventure Works Demo Dashboard – 03

0

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.

Overview Demo

You can watch a detailed demonstration of this dashboard here.

Detailed Demo

Go to this blog post to learn how to download all the materials for this demonstration.

Install Meta5 Demos

Click this button to download the demonstration files.

MSFT Meta5 Demos

Get Meta5 Trial

Hello and welcome back to the next exciting, and final, installment of our Meta5 training videos.

I am very pleased, to see you come back, to watch the final 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.

Where you got up to in the last part was, getting all the data ready in an excel workbook, that is going to be the data workbook for the dashboard workbook.

Now, you are going to create the dashboard workbook.

This means that you will read the data from the data workbook, in to the dashboard workbook, and place it into power pivot.

Then, you will build the simple sample dashboard, on top of that power pivot model.

Of course, we are presuming that you already know Excel quite well.

There are thousands of good Excel demonstration videos, out on the web, that will show you how to do exactly what we will show you here.

There is no Meta5 work in the process of building the dashboard workbook.

You have all your data in the data workbook.

It was put there by Meta5.

The only portion of this capsule, that you can see in front of you, that is related to Meta5, is the portion that allows Meta5 to run the capsule for each region, and to produce three dashboards.

There will be one dashboard per region.

Each dashboard per region, will only contain the data, needed for that region.

That is the meaning of the 5 icons, in the bottom right hand corner, of the capsule.

The spreadsheet, E, X, Oh, one, sample report, oh one, in the bottom right of the capsule, is the dashboard spreadsheet.

So.

When you are ready?

Let us get on with the show.

We will not make a lot of comments on this excel work, because we are presuming you know how to do everything you are seeing us do here, with excel.

We will just make comments when we believe they are important.

Now.

Please follow along closely with the video, and create your dashboard workbook, to be the same as you see on the video.

Please note.

It has been a little while since I built this dashboard workbook, so I did make one or two small mistakes in the video recording.

So I apologize in advance for those mistakes, and I will note them as you see them.

This video training is four hours of video for our trainees, so you can understand why I don’t want to re-shoot large portions of it.

Laugh out loud.

Now.

The first thing to do is to introduce the data from the data workbook to the power pivot model.

So please repeat the steps you see on the video.

Notice you will click on, use first row as column headers.

This is where the heading spreadsheets come in to the picture.

Those names in the heading spreadsheets, will be used, as the column headings, in the power pivot models.

When you get to the table import wizard, you can see that there is an, underscore, s, for the sheets.

You want to get data from the excel regions, and not from the sheets.

Now.

You will import countries dim, as you see in the demo.

Now.

You will import the rest of the regions in to the power pivot model, as you see in the demo.

Now.

You will create the joins between the tables in the power pivot model.

Now.

You have built your self an excel mini data mart.

That is basically what this is.

Of course, there is a lot more you can do in power pivot.

Now.

You will build pivot tables by country and by category.

Now.

When I put years in my rows there is a row that is set to blank.

This was because I forgot to set the year constraint on the internet sales, and there were some internet sales for, 20, 11.

So please forgive the blank year.

And I hope you do not have it in your dashboard.

Please make sure you hit save regularly, in excel, to save your work.

Now.

You have completed the By Country Pivot Table, and the By Product Category Pivot Table.

Now you will create the simple dashboard on top of these pivot tables.

You can copy the dashboard we have provided to you, as you go along with the video.

Now.

You have finished creating the excel dashboards according to the demonstration.

Now, you need to make it possible to run the spreadsheet, many times, for each sales region.

Now.

You need a dummy spreadsheet, because you will not send data in to the excel integrator icon.

You will send the data into the workbook using the power pivot refresh.

Now.

Copy a meta5 spreadsheet from blank icons in to your capsule.

Next you need an excel integrator icon.

So get an excel integrator icon from new icons, integrator icons.

Copy it to your capsule.

The output is going to go in to a final excel spreadsheet.

So copy a new excel spreadsheet into your capsule.

You are going to send the finished spreadsheet to a directory on the hard drive.

So copy a PC Directory icon to your capsule.

Now.

Rename the spreadsheet, dummy, so you know it’s not doing anything.

Connect the dummy spreadsheet, to the data region, of the excel integrator icon.

Connect the template Excel workbook, to the region called, template.

The excel integrator icon sends the finished spreadsheet to the final spreadsheet.

Open the excel integrator.

In the parameters, go down to, advanced commands.

In the advanced commands parameter, put the values, minus, R, A, D. .

This will cause the excel integrator to perform a data refresh of the dashboard, as well as to update all charts in the dashboard, before it sends the finished workbook to the final spreadsheet.

Now.

Connect the final spreadsheet to the PC Directory icon.

Notice, you will include the region name, in the output spreadsheet, being sent to the PC Directory.

Notice, the region name data, needs to be Data, with a capital, D. .

Now you can run the last portion of the capsule again.

Now you can look in the PC directory, and you should find the spreadsheet, has been written to the PC directory.

I will show you the completed spreadsheet, in the PC Directory, in the demo.

I will open the spreadsheet, and you can see the message, external data connections have been disabled.

This is a standard error message for excel.

You have to allow external data connections, in the security options, if you want to remove that message.

Now.

I will click on, United Kingdom, and you will see the charts respond.

I will make a few selections on the slicers, and you will see the charts respond.

That is just one spreadsheet being produced.

What you need to test now, is whether we can run the capsule three times, once for each sales territory.

So I will run that now in the demo.

Now you can see three spreadsheets were created.

When I go into the North America spreadsheet, you can see the data is only for North America.

When I go in to the Pacific spreadsheet, you can see that it contains the data only for the pacific region.

And so there you have it.

Your first Excel Dashboard, being created, by sending data into it, using Meta5.

Everything that needs to be done, you have seen being done, in the demonstration.

On the employee training videos, I did a summary of what was done, at this point.

This was just under 4 hours of video demonstration for our trainees.

Because you have the full video available to you, I do not think you would wish a summary again by me.

Now.

If you followed along with the videos, and you were able to get your excel dashboard to work, just like the ones in the demo?

Fantastic!

Well done!

Of course, if you like what you see with Meta5 and excel?

You can get your Meta5 pro license directly from the Meta5 site.

There are lots more training videos on our web site you can learn from.

You can get a full set of the manuals from the Meta5 site.

Also, of course, we would love to help you create Excel dashboards using Meta5.

We think we have plenty of good advice, and guidance, to offer you, on what analysis you could do, to improve the long term sustainable profit growth, of your company.

Our staff are now experts in the use of Meta5.

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?

We really hope you liked it.

Laugh Out Loud!

Now, in finishing this 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.

Contact Us

We will be emailing our subscribers useful and
valuable information regarding business intelligence.
Please subscribe here to be on our general emailing list.

HD0011 – How Do I Re-Create The Adventure Works Demo Dashboard – 02

0

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.

Overview Demo

You can watch a detailed demonstration of this dashboard here.

Detailed Demo

Go to this blog post to learn how to download all the materials for this demonstration.

Install Meta5 Demos

Click this button to download the demonstration files.

MSFT Meta5 Demos

Get Meta5 Trial

Hello and welcome back, to the next exciting installment, of our Meta5 training videos.

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.

Where you got up to in the last part, was getting all the capsules for dimension data, created.

You also sent all the dimension table data, in to the collect data spreadsheet.

From that exercise, you know how to go and fetch any dimension data, from anywhere, and send it in to, the collect data spreadsheet.

Dimension data, is sent to the power query model, in excel, to support the creation of dashboards.

This is the data that will be used in slicers in the dashboard.

So, if this is your first time learning this area, in meta5, you learned a lot.

Now.

We are going to take you through the process, of querying fact tables, and sending fact data, in to the collect data spreadsheet.

So, please follow along closely, on the video.

You want the sales, for both internet sales, and reseller sales.

These sales transactions, are stored in two different fact tables, in the adventure works database.

I will open up the capsule, you are about to build, so you can see it.

You can see it has some extra icons.

It has some, sort icons.

It has one, join icon.

It has one, clean icon.

These icons will be new to you.

So. Please follow along closely with the video.

Please note.

We won’t put all the detailed comments, in the voice over, as it is pretty obvious how to do much of this work, directly from the video.

I will add voice over commentary, to explain the portions, that are not so obvious.

Now.

You need to start with two query icons.

And here you go!

Now, you need two sort icons.

Notice that when you look for sort icons, you see they are green.

So, you look in the green convert data folder, for the sort icons.

So you can learn how to find icons, using the colors of the folders.

Notice that the join icon is purple.

So, you look in the purple, aggregate and combine data, folder.

The headings are from, A, one, to, oh, one.

So, you can create that region in both the headings spreadsheet, and the final spreadsheet, in the capsule.

When you test run your reseller facts query, you should get 1,280 rows found in the database.

You should see the message ordered as located in database.

You will build internet sales facts the same way so that you get practice at doing this.

Notice you will have two different fields called Extended Amount coming from two different fact tables.

One will be for reseller sales.

One will be for internet sales.

So you will have to rename them properly in the headings spreadsheet.

Please note. In the query icon demonstration for oh one internet sales facts, I forgot to add the constraint on the order date table for years, 20, 12, and , 20, 13.

So please make sure you do not make the same mistake.

Please make sure, you put the constraint on the order date table, for years, 20, 12, and , 20, 13.

That small mistake shows up later in the training video as well.

This mistake is why there is a blank value in the years slicer.

Notice, that in the sort settings, in the query icon, you only have 7 levels of sort.

So, if you want to sort on more columns, you have to use the sort icon.

In this case we are really only using sort, to show it to you.

Please note.

You do not need all these spreadsheets.

They are there to show you that the data moves along the arrows.

You could remove these spreadsheets, and the capsule would still work.

Now, you can run your capsule, through to the second spreadsheets.

Notice, you want the output to be a row, where the internet sales extended amount, and the reseller sales extended amount, are two columns next to each other.

So, you have to have some way, of joining these two data streams together, to get one row for each item.

And you need this to work, where an item was sold by a reseller, and not sold by internet sales.

And you need this to work, where an item is sold by internet sales, and not sold by a reseller.

So, the join has to be sensitive, to missing sales records, for either, internet sales, or reseller sales.

This is one of the most common problems in business intelligence.

Receiving data from two different places, and needing to put them on the one output record, to be sent in to a presentation tool, like Excel.

Getting data from different places to join together, and to be able to run the report with new data every day, is one of the common problems meta5 solves so well.

So, you will set the controls of the join icon as presented in the video.

Notice, you want an outer join in this case.

There are many different join types.

You want to replace any non existent record amounts, with zero.

For example.

If you sell some products only by internet, and we do not sell them by resellers.

You must set the replacement value to zero, otherwise it will be null.

And you can’t put a null on a chart.

Notice, you are turning on the options to save the data.

When capsules are running properly, and tested, you do not save data, because the capsule will run faster, if you do not save data.

So now you can test your join icon.

You will see, in the data for internet sales, there are sales records for, 20, 11.

These few records cause a few problems in the demonstration at a later date.

However, the problems are so small, we left them in, rather than re-do the video.

So please don’t mind our small mistake.

In the output data, you will see, there are some items for some months, that are sold by internet sales, and by reseller sales.

You can see, that for some months, there are items, that are only sold, by one of internet sales, or reseller sales.

In this particular capsule, the clean icon, is not going to do anything.

However, we use it consistently so we don’t make mistakes.

The clean icon, will clean your data, when you are using formulas, and things like that.

You will see it used properly, in the next video series.

By putting the letter, a, in the field, columns to check.

This means if the, a, column does not have data in it, then it will not send any more rows through.

We suggest you always put the clean icon, in the capsule, as a habit, in case you need it.

The compress icon is to get rid of the heading row.

Now.

What headings do you need?

You can watch them typed on the screen.

Or you can copy them from the spreadsheet we have provided.

These are the headings, that will be sent in to the power pivot model, in Excel.

You can not change them, once you have sent them into the power pivot model.

That is the purpose of the headings spreadsheet.

Please Notice.

With a join, the extra columns in the second table, are placed to the right of the columns, in the first table.

Now.

You can test your capsule, to see if it sends the data and the headings, in to the final spreadsheet.

You can watch the test on the video.

Notice the extended amounts for the last two columns.

Notice there are 1,738 rows in the test spreadsheet.

Please notice the design principles.

All the numbers, that you want on your excel dashboard, come from fact tables, and flow through something like, the sales facts, oh, one, capsule.

All the slicers, that you want to put on your dashboard, come from the capsules at the top of the capsule.

In this way, you can create slicers, and mini fact tables, inside the power pivot model, and you can build any dashboard, that Excel can support.

The limitation is Excel, not Meta5.

And Microsoft is investing hundreds of millions of dollars, making Excel a better dashboarding tool.

The power pivot models, that are now supported by Excel, are only going to get better over time.

Now.

Of course.

I don’t need to have product categories, and other text fields, in the fact tables.

We have put them in to the demonstration, so you can see how this works.

In real reporting, you would put the integer keys in to the mini fact tables, and look up the values via the slicers, for nearly all textual data, you wanted on your dashboard.

Now.

You can test the sales facts being retrieved successfully.

If you have lots of mini fact tables to send in to Excel, you can go out to columns, B, A, then, C, A, then, D, A.

You get the idea.

You can collect data, in to the collect data spreadsheet, for many slicers, and many mini fact tables.

Now.

You have all the data that you need, in the collect data spreadsheet.

Now.

You want to send all the data, in the collect data spreadsheet, in to an Excel spreadsheet.

And you want to send it into regions, that are on separate tabs, in the Excel spreadsheet.

So you go and get a blank icon from the blank icons folder.

Use the new version of excel.

You also want to copy a PC Directory icon, in to the capsule, because we are going to send the excel spreadsheet, to the hard drive of the Meta5 file server.

Notice you can use variables in directories.

But, since Excel does not support variables for file paths, we have to define a path.

So, as standard we have used the C Drive, and the directory, A, B I D A, hosting.

You want to give the excel spreadsheet a name.

So follow along on the screen.

Please note.

If you have a large site running lots of spreadsheets, in capsules on the Meta5 file server, you must be careful to guarantee, that there will not be duplication, or clashes for spreadsheet names, in this directory.

So, you need to establish a naming standard, that ensures that the capsule name, or number, is embedded in the spreadsheet name, so no clashes will occur.

Notice you need to give the excel spreadsheet a file name, for when it is written to the PC Directory.

So you have to option the arrow between the spreadsheet, and the PC Directory.

In the area named, copy data to PC Directory, click on other, then type the excel file name in the field called, Region Name.

This will send the internal Meta5 copy of the Excel spreadsheet, to the hard drive of the meta5 file server.

Please remember, that means there are two copies of the spreadsheet.

One that is stored inside Meta5 on the meta5 file server, and one that is written to the PC directory of the server, that the Meta5 file server is running on.

Inside the Excel sample report there is nothing.

It is an empty spreadsheet.

So, I will open it up and show you that it is empty.

So what do you need?

You need a place for each of the regions in the collect data spreadsheet in the excel workbook.

So what you are going to do is this.

You are going to create worksheets, for each set of data, that is going to come in to Excel.

So, please follow along with the video, and do this in your workbook.

Now you have 6 sheets, to receive data into this spreadsheet.

Now you need to create 6 regions.

You will create one region per worksheet.

So, please follow along on the video, and create the regions, as you see them created in the video.

Now.

Save and close your spreadsheet.

Now.

You need to connect the collect data spreadsheet, to the excel spreadsheet.

So, the way you do that is to create a text document.

You will do the editing in the text document, so you do not make a mistake.

You will copy the region names, on all the arrows, to a text document.

You must put commas between the region names.

You know the region names are correct, because they are used in the capsule.

So, prepare a text document, as shown in the video.

Make sure there are no blanks between region names, and commas, by accident.

So, you will get the list of regions, to be sent from the collect data spreadsheet, to the excel spreadsheet.

So, now you create the arrow, between the two spreadsheets.

Then, you option the arrow.

Then, in copy data from, collect data, you click on, other.

Then in the region name, you paste the list of regions.

Then in the copy data to, excel spreadsheet name, click on other.

Then in the region name, you paste the list of regions.

Now you should have exactly the same list of region names, separated by commas, for the source and target spreadsheets.

Now you will see if it works.

You can click on the arrow, between the collect data spreadsheet, and the excel spreadsheet.

Then click on, run.

Then see what happens.

In the task manager, you will see excel and meta5, consuming CPU at different times.

That is Meta5 sending data into the excel spreadsheet.

Now, we will open the excel spreadsheet, and see if the data made it into the excel spreadsheet ok.

So, you saw how that worked.

The data was sent into the excel spreadsheet, from the collect data spreadsheet, using the join arrow, and the region names.

Now, you will go to the PC Directory, and see if the spreadsheet was sent to the PC directory successfully.

And here is the spreadsheet.

Now.

I will just open the capsule we are trying to re-create.

You can see that you have now done virtually all the Meta5 work.

You have delivered the finished data, to the data excel workbook.

The vast majority of the rest of this work is purely Excel work.

You need to create the dashboard workbook, that reads the data workbook, in to the power pivot model.

Then excel will present the data, from the power pivot model, in any way you would like.

You will create that simple dashboard, in the next training video.

After creating the dashboard excel workbook, you just have a small piece of Meta5 work, to make the capsule able to be repeatedly re-run, for different sales regions.

Now.

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.

Contact Us

We will be emailing our subscribers useful and
valuable information regarding business intelligence.
Please subscribe here to be on our general emailing list.

HD0011 – How Do I Re-Create The Adventure Works Demo Dashboard – 01

0

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.

Overview Demo

You can watch a detailed demonstration of this dashboard here.

Detailed Demo

Go to this blog post to learn how to download all the materials for this demonstration.

Install Meta5 Demos

Click this button to download the demonstration files.

MSFT Meta5 Demos

Get Meta5 Trial

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.

Contact Us

We will be emailing our subscribers useful and
valuable information regarding business intelligence.
Please subscribe here to be on our general emailing list.

HD0010 – How Do I Create A File Drawer On A Users Desktop

0

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!

Thank you for dropping by to watch our latest how two video for Meta5!

We are really pleased to see you drop by.

Today we are going to show you how to create a new file drawer and put it on your desktop.

File drawers act like a filing cabinet in an office.

You have filing cabinets to store documents that all the people with the key to the filing cabinet have access to.

So a filing cabinet in Meta5 acts just like a filing cabinet in an office.

You can get access to the filing cabinet by having it placed on your desktop and being given a key to the filing cabinet.

So, Now let’s go to the demonstration.

The first thing you have to do is to log on to the system desktop using the system user I D and the system password.

Now, as you can see we are on the system desktop.

You can see a system disk and a data oh one disk.

We want to create the new file drawer on the data oh one disk.

So we open up the data oh one disk by double clicking on it.

Then you can see there is a file drawer for desktops.

And there is a file drawer for file draws.

When we open up the file drawers we see that there is a blank file drawer.

We click on the blank file drawer to highlight it.

Then we press F three to copy it.

Then we click in the window to drop it in to the file drawers file drawer.

Then we can click on the new file drawer.

Then we can press F four to option it.

Then we can enter a name for it.

We will call it Sample File Drawer for Demonstration.

Then you have to set the security of the file drawer.

As you can see there are many security options.

We are going to set it to be able to be viewed by anyone and changed by anyone.

Then we will secure the file drawer by only putting it on to the desktops of the user that we want to give access to.

Then we press apply in the header of the icon options.

Then we click icon options to close the icon options.

Then we open the desktops file drawer.

Then we open the guest desktop.

Then we scroll down on the guest desktop.

Then we can copy the Sample File Drawer for Demonstration.

Then we can drop it on the guest desktop.

It will not actually create a copy of the file drawer because file drawers can not be copied in Meta5.

It will make a link to the file drawer on the guest desktop.

Now the guest desktop can put objects in to the Sample File Drawer for Demonstration.

So you can organize all your Meta5 icons and documents this way.

They can all go in to file drawers and be made available to people who are approved to have access to them.

Now we will go to the guest desktop and log on.

We will get a message telling us that there are icons outside the view of the screen.

To be able to see the new File Drawer you may have to press alt on your keyboard and hold it down.

You might then need to go across to the star button on the bottom right hand corner and click on the star button.

Then go to the Refresh Display Option.

Then click on the Refresh Display Option.

Then the Sample File Drawer for Demonstration should be visible in your desktop.

Now we will open up the new file drawer.

We will click on the oh one Adventure Works folder.

We will press F three to copy it.

And we will click inside the file drawer.

Meta5 will now make a complete copy of the oh one Adventure Works folder in the file drawer.

In this way people can share content in file drawers in the Enterprise Version of the product.

Or people can put content in to their own file drawers if you are sharing the single user version of the product.

This concludes our demonstration of how to create a new file drawer and place it on to the desktop of a user.

We hope you liked this short how two video.

If you did like it, please hit the like button and please subscribe so that you will receive notifications when we release more how two videos.

Thank you very much for coming by our channel and watching our how two video, we really appreciate you.

I wish you a good day!

Thank you.

Contact Us

We will be emailing our subscribers useful and
valuable information regarding business intelligence.
Please subscribe here to be on our general emailing list.

HD0007 – How Do I Set Character Field Lengths In the WTDD

0

Hello and welcome!

Thank you for dropping by to read our latest How To Blog Post.

This one is quite simple.

In many databases today you will find a lot of character fields set to varchar or nvarchar.

They are often set to lengths such as 255 because in virtually all applications it makes no difference that the field is defined to be much longer than the longest character string placed in it.

However, in the Meta5 query tool it allocates the maximum string length in the output window as individual columns are not scrollable or truncatable.

So these varchar 255 fields tend to take up much more space than is needed.

So what we do to solve this is to run a simple query to set the length of character fields to be the length of the longest field found.

Obviously if you are adding longer fields you need to re-run this sql.

SELECT

concat ('update WT_COLUMNS set PRECISIO = ' ,'(select max (len ( ',COLUMNNAME,')) from ' ,TABLENAME, ' )

where TABLENAME = ''', TABLENAME , ''' and COLUMNNAME = ''' , COLUMNNAME , ''' ;' )

FROM [dbo].[WT_COLUMNS]

where not tablename like 'WT%'

and datatype = 2 ;

The sql statement above can be run on the WTDD tables in SQL Server.

Obviously other databases have similar but different syntax to achieve the same result.

On SQL Server this will produce update statements similar to the following:

update WT_COLUMNS set PRECISIO = (select max (len ( promotion_name)) from dbo.Promotion ) where TABLENAME = 'dbo.Promotion' and COLUMNNAME = 'promotion_name' ;

update WT_COLUMNS set PRECISIO = (select max (len ( price_reduction_type)) from dbo.Promotion ) where TABLENAME = 'dbo.Promotion' and COLUMNNAME = 'price_reduction_type' ;

And then you simply run those statements which will set the length of the field in the WTDD.

We hope this saves you some time and trouble figuring this one out for your self!

With that?

I would like to say thank you very much for reading our blog post today!

We really appreciate your time and attention!

Best Regards.

Mihai Neacsu.

Contact Us

We will be emailing our subscribers useful and
valuable information regarding business intelligence.
Please subscribe here to be on our general emailing list.

HD0006 – How Do I Set Up O365 Email For Meta5

0

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!

Thank you for dropping by to watch our latest how two video for Meta5!

We are really pleased to see you drop by.

Today we are going to show you how to set up O 3 6 5 email for Meta5.

This is a very simple and easy thing to do, so we are sure you will understand it very quickly.

Ok?

So let us go to the demo.

In the previous blog post you saw how to set up SMTP email in Meta5.

To set up O 3 6 5 email is almost exactly the same.

So we are not going to go through the same level of details, as in the previous blog post.

We are just going to show you what is different.

Meta5 only supports SMTP email.

So you have to configure Meta5 to use the SMTP interface to O 3 6 5.

This is done by using the address for the O 3 6 5 SMTP server in the SMTP server name parameter.

You will see that on the screen.

That is the main difference you need to be aware of to use O 3 6 5.

Then you set your from address to be as you wish.

You set your user name to be your email user name that O 3 6 5 will accept.

And you set your password to the password of your O 3 6 5 email address.

You must set the Enable S S L option to Yes.

If you do not set the Enable S S L option to Yes, then the email will not be sent successfully through O 3 6 5.

So now I will go across to a small capsule that is going to send the email.

When I open the envelope you can see that the email is addressed from peter to demo user at our email address on O 3 6 5.

Now, I will just run the capsule.

Now, I will go across to the web mail for demo user.

Now, you can see that the email arrived.

You can see that the attachment is with the email.

In this way it is possible to email reports to anyone who wishes to have reports emailed to them.

A good example of using email is this.

Perhaps a manager wants to be emailed a specific report if some important out of bounds situation occurs.

Meta5 can run capsules repeatedly, at very short intervals.

These capsules can check for out of bounds situations, even in production systems.

And if the out of bounds situation is found, Meta5 can send a small report in an email, to bring the situation to the managers notice, more quickly than simply putting a report on a one drive.

This is closer to an alert than just a report.

Meta5 can also send messages via S M S services for more urgent alerts.

This will be covered in a later video.

So, this video was short and simple.

This concludes our demonstration of how to set up O 3 6 5 email for Meta5.

We hope you liked this short how two video.

If you did like it, please hit the like button and please subscribe so that you will receive notifications when we release more how two videos.

Thank you very much for coming by our channel and watching our how two video, we really appreciate you.

I wish you a good day!

Thank you.

Contact Us

We will be emailing our subscribers useful and
valuable information regarding business intelligence.
Please subscribe here to be on our general emailing list.

HD0005 – How Do I Set Up SMTP Email For Meta5

0

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!

Thank you for dropping by to watch our latest how two video for Meta5!

We are really pleased to see you drop by.

Today we are going to show you how to set up SMTP email for Meta5.

This is a very simple and easy thing to do, so we are sure you will understand it very quickly.

Ok?

So let us go to the demo.

There are two ways to set up SMTP email in Meta5.

You can set up your own email credentials on an individual mail box.

Or you can set up a single set of credentials for a Meta5 server, and an email box can use that set of credentials.

We will show you both ways today.

The first way that we will show you is by configuring an email outbox with your own credentials.

So, you will go to the new icons folder and double click to open the new icons folder.

Then you will go to blank icons and click twice on blank icons to open up blank icons.

Then scroll down to the email outbox icon.

Click once on the email outbox icon to select it.

Press F 4 to copy it.

Then drop the copy of the email outbox on your desktop.

Then click once on the email outbox on your desktop to select the email out box.

Then press F 4 to open the options window.

Then click on other data.

You will see the Use Global Settings option in the other data window.

If you set Use Global Settings to Yes you will use the global settings for the installation of Meta5.

Now click on Use Global Settings equals No.

Now you will see the parameters for the SMTP email server that you wish to use.

You can complete these parameters according to the settings that you are given by your email administration colleagues.

It just so happens that I have completed these parameters in another email outbox.

This is so you do not have to watch me type in the parameters.

This is the email outbox that I have set these parameters correctly in.

So I will option this email outbox and select other data.

Now you can see that the SMTP parameters are correctly set.

You can pause the video to check the settings I am using.

You can see that these parameters are all quite normal.

So now you must create a simple capsule to test sending your email.

So you click on New Icons twice to open New Icons.

Then click on blank icons twice to open blank icons.

Then click on the capsule icon once to select it.

Then press F 3 to copy the capsule icon.

Then drop it on your desktop.

Then click on the file drawer icon in the header bar of blank icons to close the file drawer.

Let us call the capsule, capsule two.

So option the capsule and add the number two to the name of the capsule.

Then close the icon options by clicking on icon options in the icon options header window.

Now click on capsule two twice to open it.

Notice that capsule two is starting out blank.

One thing we need to put in to capsule two is an email outbox.

So select the email outbox you have just configured by clicking on it once.

Then press F 3 to copy it.

Then drop the copy of the email outbox in the capsule.

Another item we need to copy in to the capsule is an envelope.

So we must go to blank icons to get an envelope.

By now you will be familiar with the routine.

Open New Icons.

Open Blank Icons.

Click once on the envelope icon.

Press F 3 to copy the envelope icon.

Then drop the envelope icon just to the left of the email outbox.

Then click on the file drawer icon in the header bar of blank icons to close the file drawer.

And then we need something to send in the envelope.

I just happen to have created another capsule where we have the Final spreadsheet from the Adventure Works Demonstration we have provided on a prior blog post.

So I will copy the Final spreadsheet from the prior test capsule I created and drop it into this new capsule.

Now I am going to close the test capsule.

Now connect the spreadsheet with the envelope.

Click once on the spreadsheet.

Click once on Connections in the header.

Click once on the envelope icon,

And you will see an arrow drawn between them.

Repeat to connect the email icon to the email outbox.

Now, when I open the envelope, there are no details in the envelope.

There is no from address, no to address, no subject, no text in the envelope.

Obviously you would type these things in here now.

I am sure you do not want to watch me type these things in, so I have a sample envelope with these items already filled in.

So I will copy that envelope in to this position while you watch.

You would complete these items for your example.

Ok?

I am going to show attachments.

I am going to delete the attachment.

And now I am going to connect these icons.

So now we have copied the sample envelope and reconnected the icons.

And we will move the icons one step to the left.

So now I have a spreadsheet going in to an email envelope.

In the email envelope I have the from address, the to address, a subject, and some text.

The email outbox is set with these parameters.

Now we can click the run button.

Now we will open up the capsule run log.

And you can see that the capsule was successful in sending the email.

So now we are going to go to the email for demo user and see if the email arrived.

And here is the email.

I open it up.

Here is my Final spreadsheet inside the email.

You can see the text that was sent with the email.

I can click on the Final spreadsheet and outlook will show me a preview of the spreadsheet.

Of course, I can also download the spreadsheet.

So we can see the spreadsheet arrived in my email.

Now let us close the email and return to our Meta5 desktop.

Now, we are going to demonstrate configuring the email for the server, and not just the individual email mail box.

So what we need to do is to go down to the bottom right hand corner of our server window.

Click on the up arrow for services.

Right click on the meta5 icon.

Then click on Configure Meta5 Starter Edition.

A window will pop up for the Meta5 Starter Edition Configuration Parameters.

Click on email in the header.

And you can see a set of parameters that it is asking for in the email parameters.

These are essentially the same parameters requested for the individual email outbox.

We are going to demonstrate this setting with the option HTML Formatted Messages both set to on and off to show you the effect.

We do not want to bore you with watching us type in the parameters so we will skip all that.

Please take note that the port for the SMTP mail server is added to the end of the SMTP Server parameter after a colon.

In our case there is a colon followed by the number 5 8 7.

Now we have added the parameters we can test the connection to the SMTP server.

And you can see it was successful.

We click on apply.

Now we must stop and restart the Meta5 server to have these settings take effect.

So log off your Meta5 desktop.

Go to the services arrow in the bottom right hand corner of your server.

Right click on the Meta5 icon.

Click on stop Meta5 Starter Edition.

You will need to click on the yes to confirm.

You will need to give it a little time to shut down all services.

Then double click on the Meta5 icon to start Meta5 again.

It will take a little time to start because it is re-starting all the services.

Now we will log on.

We will get another email box from the blank icons folder.

We are sure you know the routine now.

We will set the parameters for the email box in the options, other data window.

Notice that the option Use Global Settings is set to yes.

If we set Use Global Settings to No we will get these parameters.

In this case we will say Use Global Settings is Yes.

We will copy capsule two by selecting it, pressing F 3, and then dropping the copy just below it.

We will call this capsule three.

We click apply, then click the icon options in the header to close the icon options window.

We open up capsule 3.

We will delete the current email outbox and replace it with the new email outbox we just configured.

Each time we will use the function pad you can see on the screen to manipulate these icons.

And we are going to check that the email outbox we are using now has the Use Global Settings parameter set to Yes.

Now we are going to run this capsule.

And you can see by the Capsule Run Log that it ran successfully.

And now we will go to the email for demo user and we will see if the email has arrived.

Notice that the time is 1 34 P M.

You can see that the text in the email has not been formatted properly.

This is because we said in the options we would use HTML email formatting.

So now we are going to run through the process of altering that option and rebooting the Meta5 server and resending the email.

You can follow along on the screen because it’s really quite simple.

We just wanted to make sure we demonstrated this to you.

Now we will go to the browser to see the email has arrived.

And here is the new email with the text formatted correctly.

And here is the prior email with the text not formatted correctly because it is not using HTML.

So, this completes the demonstration of setting up the email outbox using local parameters on the email outbox, or using the Global Settings for the Meta5 server.

You can now log off your desktop after a job well done!

Meta5.

The Better Way.

This concludes our demonstration of how to set up SMTP email for Meta5.

We hope you liked this short how two video.

If you did like it, please hit the like button and please subscribe so that you will receive notifications when we release more how two videos.

Thank you very much for coming by our channel and watching our how two video, we really appreciate you.

I wish you a good day!

Thank you.

Contact Us

We will be emailing our subscribers useful and
valuable information regarding business intelligence.
Please subscribe here to be on our general emailing list.

HD0004 – How Do I Copy Objects Between Windows And Meta5

0

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!

Thank you for dropping by to watch our latest how two video for Meta5!

We are really pleased to see you drop by.

Today we are going to show you how to copy Meta5 objects from files on your computer across to your Meta5 desktop.

This is a very simple and easy thing to do, so we are sure you will understand it very quickly.

We have published a previous blog post called, H D 0 0 0 2, How Do I Install The Microsoft Meta5 Demonstrations.

In this blog post we demonstrated how to copy files from windows to the meta5 desktop.

However, we are going to repeat that process in this short video for those who want to see just this process.

Ok?

Let us go to the demo.

We are going to use the Microsoft Meta5 demonstration files that we have provided to you for this how two video.

So we are going to first go to the downloads folder for that demonstration.

This is where you would start with your own meta5 archive that has been copied to windows.

You can see 6 files.

They are as follows.

Oh one. Adventure Works.

Fifty one. W W I, Demos.

ADVENTURE WORKS D W M 5 A dot back.

ADVENTURE WORKS D W dot back.

WIDE WORLD IMPORTERS D W M 5 A dot back.

WIDE WORLD IMPORTERS D W dot back.

We have started with our meta5 desktop open.

Next you will need to move the Meta5 desktop to one side so that you can see the downloads folder.

Now put the mouse over the heading bar of the Meta5 desktop.

Now left click on the mouse and hold down the mouse button.

Now move the Meta5 desktop to the right so that the downloads folder in windows explorer is visible.

Now put the mouse over the file, oh one, Adventure Works.

Select the file oh one, Adventure Works, by left clicking the mouse and holding down the mouse button.

While holding the left mouse button down, drag the oh one, Adventure Works file from the external Microsoft operating system on to the Meta5 desktop.

Now release the mouse button to drop the file on to the Meta5 desktop.

Meta5 will now translate the archived Meta5 file to the required files on the Meta5 file server.

This process might take a little while for large Meta5 archives.

Next hover over the second file.

Fifty One, W W I, Demos.

Then press down on the left mouse button.

While holding the left mouse button down drag the file from the windows operating system across to the Meta5 desktop.

Release the left mouse button to drop the file on to the Meta5 desktop.

Meta5 will now translate the archived Meta5 file to the required files on the Meta5 file server.

This process might take a little while for large Meta5 archives.

Now you can edit the name of each of the folders and add an X to the end of the names.

So now select the folder, oh one, Adventure Works, by left clicking on the folder once.

Now press F 4 to open up the options for the folder.

Now click at the end of the name.

Add an X to the end of the name of the folder.

Then click on icon options in the header to close the folder.

Now select the folder, fifty one, W W I Demos, by left clicking on the folder once.

Now press F 4 to open up the options for the folder.

Then click at the end of the name.

Add an X to the end of the name of the folder.

Then left click on icon options in the header to close the folder.

Now left click once on the folder called, oh one, Adventure Works X, to select it.

Now click left once more and hold the mouse down to be able to copy it.

Now drag the folder to the windows explorer window you would like to drop it in.

Now release the left mouse button to drop the folder in the windows explorer window.

Now left click once on the folder called, fifty one, W W I Demos X, to select it.

Now click left once more and hold the mouse down to be able to copy it.

Now drag the folder to the windows explorer window you would like to drop it in.

Now release the left mouse button to drop the folder in the windows explorer window.

Now You will see that the two folders copied from the meta5 desktop are in the windows explorer window.

Now you have seen how to copy meta5 archives from a windows explorer window to a Meta5 desktop.

And you have seen how to copy a meta5 object from the meta5 desktop to a windows explorer window.

Now you can log off your meta5 desktop.

Meta5.

The Better Way.

This concludes our demonstration of how to copy Meta5 objects from files on your computer across to your Meta5 desktop.

We hope you liked this short how two video.

If you did like it, please hit the like button and please subscribe so that you will receive notifications when we release more how two videos.

Thank you very much for coming by our channel and watching our how two video, we really appreciate you.

I wish you a good day!

Thank you.

Contact Us

We will be emailing our subscribers useful and
valuable information regarding business intelligence.
Please subscribe here to be on our general emailing list.