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

0
50

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

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.