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 know and understand what data is available to you through your Meta5 desktop.
Of course, you are able to select data from many places using Meta5.
You can get data from web sites, from spreadsheets, from word documents and from many other places.
However, the vast majority of data that you are going to access using your meta5 desktop is data in relational databases.
At worse, the vast majority of the data that you will want to access will be accessed by a piece of software called ODBC.
You do not need to know much about what ODBC is and how it works.
You just need to know that it is a way to read data that is very common.
And that is what we are going to show you today.
We are going to show you how to access data in a relational database using ODBC as the way you will read the data.
In most cases your ODBC connections will be created for you by your technical support staff.
We have created a how two video on creating ODBC connections to the demonstration databases that you can review if you would like to.
So if you are ready?
Here we go!
Let us go to the demonstration.
Let us consider you are a marketing analyst for the Adventure Works company.
You have just been given your brand new Meta5 desktop and you have been told that there is some documentation to load and reed.
What would that documentation usually be?
Well, firstly you would likely be given some PDFs of the data models that you will have access to.
Rather than showing you PDFs we will show you the Database Administration Tool.
It is in the demonstration folder and you should be able to connect to the DBA tool in your demonstration database if you have Meta5.
So we will go in to the oh one Adventure Works folder and we will find the DBA tool.
It is in the capsule called ninety eight Define Workstation Tools Data Dictionary for Adventure Works.
We will open the capsule.
And we will find the DBA tool in the bottom left hand corner.
We will open the DBA tool.
Now we can see the different categories that are available to me.
Each category contains a set of tables that are related to each other.
The Database Administrators set up these tools for us so we don’t have to worry about how they are created.
We just need to use them.
Now down the left hand side we see the names of the categories.
At the top of any list of categories are the Orphans.
These are any tables that are not linked to any other tables and are not used in any categories.
Hence the name Orphans.
Your database administrators should have made sure there are no orphans in your data dictionary.
Next you see a line that says Adventure Works.
This is where the categories start that you are interested in.
So you can see these categories.
Oh one. Internet Sales Facts.
Oh two. Reseller Sales Facts.
Oh three. Sales Quota Facts.
Oh four. Product Inventory Facts.
Eleven. Call Center Facts.
Twelve. Survey Response Facts.
Thirteen. Finance Facts.
Fourteen. Currency Rate Facts.
Fifteen. New Currency Rate Facts.
You can also see a line called Administration Tools.
And you can see the Workstation Tools Data Dictionary.
So you can write queries against the Workstation Tools Data Dictionary if you want to.
You can also see a category called All Tables.
Now, if we come back to the category at the top of the list.
It is called Oh one. Internet Sales Facts.
We select the category by clicking on it.
Then we can select show joins in the header bar.
Meta5 responds by giving us a diagram of the tables in the category.
You can see all the table names, all the column names, and all the joins.
You can see the database administrators have placed the drawings in to a nice format for me so it is easier to reed.
Now we can go back to the category by clicking on oh one Internet Sales Facts in the header to close the database diagram window.
Now we can get a list of the tables in the category by clicking on oh one Internet Sales Facts in the list of categories.
We can see the list of tables.
Now we will click on the Dim product table.
It opens up and shows you all the columns in the product table.
Take a few seconds to read some of the column names.
By all means hit pause in the video to review the column names.
So let us choose a very normal and common column name.
Color.
We will option it using F four.
We see that we have some options in describing a field.
It has an internal name.
It has a print name.
It has a SQL name.
And it has a description.
The print name and the description can be changed by the database administrators according to the desires of the users.
In fact the users can enter these names in to excel workbooks and the database administrators will apply those changes.
So because your user representatives control the print name and the description, that helps you understand the contents of the fields.
In your job as a marketing analyst for Adventure Works you would have to make sure you spend time to learn all the fields that are available to you.
Now we will close the products table.
And we will open up the description of the Fact Internet Sales table.
We will look at some of the fields on this table.
We have such fields as.
Sales Order Number.
Sales Order Line Number.
Order Quantity.
Unit Price.
Extended Amount.
Sales Amount.
Tax Amount.
Customer Purchase Order Number.
Order Date.
Due Date.
Ship Date.
These are all very standard fields to have on an internet sales record.
As a marketing analyst for Adventure Works you will have to understand what these fields mean and how they work.
Now we will close the DBA tool.
And we will close the capsule.
Now we will go and look at some actual data.
In general, before releasing new areas of the data warehouse to users, the database administrators will create sample query icons for each of the categories and will put some test data in to a spreadsheet for you.
This is to give you more information to learn from without having to go and download data for your self.
The database administrators will also put constraints on these queries so that they only return a relatively small number of rows.
It is a common mistake for new users of query icons to simply ask for all data in a table to be put into a spreadsheet to see what is in the table.
Of course it is not possible to put millions of rows of data in to a spreadsheet.
So the database administrators will create these icons to make sure that is less likely to happen.
So we will open up the folder oh two Adventure Works Sample Query Icons by clicking on it twice.
Then we will open the capsule oh one sample query icons by clicking on it twice.
Now we can see nine sample query icons with another nine spreadsheets to capture the data from the query icons.
So let us option the oh one Internet Sales Fact query icon by clicking on it and pressing F four.
Then we will click on other data.
Now we can see that the database administrators have been kind to us.
They have set up the parameters in other data.
You will notice that the gateway is set to ODBC.
This is what we mean when we say we can access any data that is visible by ODBC.
Next we see the database name is set to Adventure Works data warehouse meta5 access database.
This is the workstation tools data dictionary database that your database administrators downloaded as a sample database and put on to your system for you.
You can see that the parameter to use the workstation tools data dictionary database has been set to yes.
The workstation tools data dictionary database owner has been set to D B O.
And you do not need to be concerned about security because the database administrators have taken care of that.
So we will close this window by clicking on icon options in the header.
Now we will open the query icon by clicking on it once.
We will click on show controls.
And here we can see a graphical representation of just the tables that have at least one field in the query output.
We will click on the star button in the top right hand corner of the query icon.
Then we will click on capture SQL.
Then we will resize the window and place it more toward the center of the screen.
Then we will click on show data.
We can see that the query tool generated SQL and sent the SQL to the database.
The database ran the SQL and returned the data to the query window.
And we have 555 items found in the database.
You will notice the fields are a little wide as the query tool allows for the maximum length of a field in the query window.
Now we will close the query log.
Now we will show the query catalog.
The query catalog shows all the tables and all the fields that are available to the current query.
In today’s world we often have very large numbers of tables and fields.
So it is common to give users access only to those fields that are commonly needed in the broadly distributed workstation tools data dictionaries.
Where access to fields that is not common is needed another dictionary can be used.
This is just to save many people the time of learning about fields that they will almost never use.
Next we will show the DBA catalog by clicking on that in the header.
Now, down the left hand side we can see each of the categories that we saw earlier in the database administration tool.
Notice the first three are as follows.
Oh one. Internet Sales Facts.
Oh two. Reseller Sales Facts.
Oh three. Sales Quota Facts.
Notice that the tables in the category oh one Internet Sales Facts are highlighted.
This means that they are in use, or available for use, in the current query icon.
By coming in to the DBA catalog and clicking on a category you can gain access to the tables in that category.
Just while we are here we will click on the star in the top right hand corner of the query catalog window.
And you will see the option read DBA catalog open up.
If the database administrators have changed something about the database that your query icon needs to know about you can refresh the DBA catalog for this query icon by clicking that option.
Notice that once a database category is rolled out to the user community the only changes that should be happening to it are the addition of fields or tables.
You should never be removing fields or tables because existing queries might be broken by such processes.
Now we will close the query catalog window.
And we will close the query control window.
Now we will click on the star at the top right hand corner of the query window again.
We will click on remove expert functions.
When we do that you will notice that the option called create column will disappear from the header of the query window.
Now we will click on the star again.
And we will click on add expert functions.
And we will see the create columns option come back.
Now we will click on clear data.
Now we will click on create column.
And you will see the computed column options window will open up.
You can place any snipped of SQL code that works for your database in that window.
Now we will close the computed column options window by hitting cancel.
We will open up show controls again.
And we will click on Fact Internet Sales field called Sales Amount to highlight it.
While it is highlighted we will click on Create Column in the query window.
Notice that the query icon has completed the form with the default operator sum, the formula containing the Sales Amount field, and it has already included the group by fields.
All we need to do is to add a label.
And we can add a label of our own choice or we can copy the Sales Amount field using the mouse functions.
We will do that just to demonstrate.
Ok?
That is a little about finding your way around the query icon.
Now we will cancel this computed column options window.
We will close the query control window.
We will close the oh one Internet Sales Facts window.
And we will open up the spreadsheet next to it by clicking on it twice.
Now we can see some of the data that was returned by the query icon only without all the blank spaces.
In this way you can write queries against the database and dump data in to spreadsheets and learn the data by looking at samples.
Looking at sample data is always the best way to learn about the data that is available to you.
Now we will close the spreadsheet.
Now we will open oh two reseller sales facts by clicking on the query icon twice.
We will click on show controls.
We will click on show query catalog.
We will click on show DBA catalog.
And now we can see that the tables in the category oh two reseller sales facts are highlighted.
You will notice that many of the tables have the same names as the tables in the oh one internet sales facts category.
And this is because they are mostly the same tables.
Tables like Dim Product, Dim Currency, Dim Order Date are all the same tables.
But in the oh two reseller sales facts category they are joined to the Fact Reseller Sales table and not the Fact Internet Sales table as they were in the oh one Internet Sales Facts category.
So you can see that if you are a marketing analyst for the Adventure Works Company you must get sales data for internet sales from the oh one Internet Sales Facts category and you must get reseller sales from the oh two Reseller Sales Facts category.
And, of course, to get total sales you must find a way to add these two together because that has not been done for you.
So while we have this query catalog open.
You can see that you can get the idea that each query icon generally gets data from one and only one category.
When we need to join data from many categories together, such as we want total sales and we need sales from oh one Internet Sales Facts and oh two Reseller Sales Facts, we have another tool called a join tool to do that for us.
In such cases we have to take care of the cases where there are not sales of products through both channels.
Perhaps there are some products we only sell via the internet and other products we only sell via resellers.
We have to be careful not to lose any sales data as we attempt to join the results of the two queries together.
We will show you how to do that in a later how two video.
Ok.
Now we will close the query catalog window.
We will close the query control window.
We will go across to the star and we will open the capture SQL window and bring it to the center of the screen to make it more readable.
We will click show data.
And there you have it.
Another SQL query generated and another set of data brought back to us by the query tool.
Now we will close the query log.
We will close the oh two Reseller Sales Facts query icon.
And we will open up the spreadsheet next to it by clicking on it twice.
And here we can see the data that was output by the query icon without all the extra spaces.
This is how you go about learning what data is available to you.
By building small queries and looking at the data that has been returned.
In the first instance the database administrators will provide you a set of such queries and spreadsheets as you have seen here.
After that you will have to explore your self or talk to your fellow business analysts and ask them if they have more details.
You may also be provided with reports or spreadsheets listing all the table names and their descriptions.
You may also be provided with reports or spreadsheets listing all column names and their descriptions.
All that information is available.
It is just a question as to whether it has been prepared in to reports for your use in your company.
Ok.
Now we will close the spreadsheet.
And we will close the oh one sample query icons window.
And we will close the oh one Adventure Works folder.
Meta 5.
The better way.
This concludes our demonstration of how to know and understand what data is available to you through 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.
