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 latest blog post.
Firstly, I’d like to say thank you very much for coming along and watching our latest video today.
I really appreciate your time and attention, so thank you very much for that.
Today, we are launching a series of blog posts that showcase the dimensional databases that Ralph Kimball released in his now famous book.
The Data Warehousing Toolkit.
Of course, to be fair to Ralph, you can click the button below and purchase a copy of his book.
Even though Ralph is retired now, I am sure he would appreciate another sale of his book to add a few dollars to his retirement fund.
The Data Warehousing Toolkit remains the master work of dimensional modeling.
You really should have your own copy if you intend to build or query dimensional models.
Now that we have the advertisement done for Ralphs book, please allow me to explain what we have done.
We received a copy of Ralphs databases in SQL Server 2016 backup format.
We put them on to our demo server.
We created the ODBC Connections needed to link the Meta5 DBA tool to each of the databases.
We added the workstation tools data dictionary to each of the databases.
We then created all the entries needed in the workstation tools data dictionaries, using the DBA Tool, to be able to query these databases using the Meta5 query tool and reporter tool.
Now you can download an evaluation copy of Meta5 and you can go through Ralphs book and you can query the databases using his software Meta 5.
It’s kind of like a trip down the memory lane of Business Intelligence.
Why?
Because Ralph was a co-founder of the company that created Meta 5 way back in 1982.
It seems somehow poetic that, here we are, 40 years later, presenting his data models using his software.
Now.
Given there are 12 databases, we are going to create 12 blog posts.
One for each database.
And we are going to go into the database in a little bit of detail for each database.
Then, you can go to the blog post for the database you are interested in, and watch the video or read the blog post.
We are doing all these videos with voice generation so that you can watch and listen or read the blog post.
It also helps with google to have the text of the voice over on the blog post.
So here we go!
The first database is banking.
And we will show you that one first.
Firstly, you restore all the databases to SQL Server.
You will be seeing a screen with the databases being displayed in SQL server management studio now.
Then you create all the ODBC connections using 32 bit ODBC.
You will see the list of ODBC connections that we created on your screen now.
Then you restore the Meta5 folder to your Meta5 desktop.
You will see the demonstration desktop on your screen now.
So now we will open the folder, 13 Ralph Kimballs Databases.
You can see that the databases are numbered in a strange fashion.
These are the numbers of the databases on one of our development machines.
It was to keep the databases in order on that machine, so you can ignore the strange numbering.
You can see the folders range from, oh three, Bank oh one database, all the way up to, Twenty, Warehouse.
You can find each of these databases demonstrated on a different blog post on our site.
So now we are going to open up the folder, oh three, Bank oh one database.
Inside you can see a capsule called, oh one, data query icons.
We will open up this capsule.
Inside this capsule you can see the following.
There are four query icons, four reporter icons, and eight spreadsheets, that will receive data from the query icons and the reporter icons.
There is also a capsule run log and a DBA tool.
Each capsule you open will be similar.
There will be query and reporter tools for each category.
There will be spreadsheets to collect the data.
And there will be a DBA Tool that demonstrates the database itself.
Now.
Let us option the DBA tool.
We will click on other data.
In the field gateway you can see ODBC.
This tells you that we are asking the DBA tool to call ODBC in the supporting operating system.
You can see the database name is, Z, BANK oh one, underscore, M, 5, A..
This is the name of the ODBC connection for the DBA tool.
Now.
We will close the icon options for the DBA tool.
Now.
Let us open the DBA tool to show you what the data model looks like in the banking database.
We can see that there are four categories.
Each category will have one fact table in it.
This is not a general rule.
A category can have more than one fact table in it.
But in general, for simple data models, you will build one category per one fact table.
There are rules to follow when you want many fact tables in the one category.
The sample databases do not follow those rules.
And this is why we have one category per fact table.
You can see that the names of the categories are as follows.
Custom Checking.
Custom Mortgage.
House hold demographics.
House hold facts.
Now we can click on the custom checking category to select it.
And we click on the show joins button to show the data model diagram for the category.
And now you can see the tables in the category.
You can see the relationships between the tables.
You can see that the names of the tables have been altered to have a more meaningful print name than the underlying table name.
The dimension tables have been prefixed with the letter, D.
The fact table has been prefixed with the letter, F.
The category name is derived from the name of the fact table.
In the diagram on the screen you can see that the fact table is called, custom checking.
You can see that the dimension tables are named as follows.
Month.
Account.
Product.
Branch.
House hold.
Status.
You can read the column names on the screen.
The column names can be edited in the DBA tool and given user friendly names.
You can also put help text in to the DBA tool.
This information is stored in the workstation tools data dictionary.
So, if you would like to press pause, and read the diagram in more detail, please feel free to do so now.
Next we will move on to the custom mortgage model.
We will close the custom checking model and open the custom mortgage model.
You can see from the model that the dimension tables are the same.
Only the fact table has changed.
And this is how these models work.
You have conformed dimensions, with different fact tables being linked to those dimensions.
So, I will give you the chance to press pause, to read the custom mortgage fact table, to see what it contains.
Now.
We will close the custom mortgage model and open the house hold demographics model.
In this model you will notice there are some different dimension tables.
They are as follows.
Product.
House Hold.
Demographics.
You can see that the house hold demographics fact table contains just a few fact fields.
I will give you a chance to press pause to read this data model.
Now.
We will close the house hold demographics model and open the house hold facts model.
You can see that the fact table is now the house hold facts table.
You can see the joins that are defined in the data model.
So, for each category, you can easily open the category, and see what the data model looks like, because these are very simple models.
You can download much more complex data model pictures for our Navision B I Solution from our web site.
Now.
We are going to show you the all tables category just so you can see that this category contains all the tables accessible by this workstation tools data dictionary.
When we open the all tables category we can list the tables that are in the category.
You can also select a table to list the columns.
And you can select a column to edit the information stored about columns.
For example you can define a column to be a key for the table or not.
It is possible to click, show joins, for the all tables category.
We will do that now.
However, as you can see. This is not so useful as the diagram is a bit too complicated to read.
There are too many lines on the diagram.
When you have very complex models, trying to show all joins between them is far too much to put on one diagram.
Now, we will go to the query tools, and you can see how they work for the banking model.
We will open up the query icon, oh one, custom checking.
You can see that it has a query already created in it.
All the query icons provided to you have a simple query already created in them.
In this case you can see the columns are as follows.
Year.
Product description.
Status description.
Transaction count.
Now.
We will click on, show data.
You see there are 30 rows returned.
Now.
We will click on, show controls.
You can see a simple portion of the data model in the window.
Only those tables where fields are in the output query are shown in this window in the query tool.
Now.
We will click on, show query catalog.
You can see that all the tables in the category are visible.
We can select any field inside the category and place it in the query output.
There are more detailed lessons on how to create queries available on our web site.
Now.
We will close the query catalog window.
Now.
We will close the query control window.
Now.
We will select capture SQL, and we will run the query, and show you the SQL captured, when the query runs.
You are welcome to pause the video, and read the captured SQL, in some detail.
It is quite obvious, how the query tool has translated the graphic query in to SQL, to be run against the database.
The query icon and the reporter icon can talk to any ODBC connection.
There are also native gateways to many major databases.
These gateways provide higher performance, as well as some extra functionality.
Now.
You have seen the query icon for custom checking.
I will now show you the reporter icon for custom checking.
You will see some differences.
So, I will open the reporter icon for custom checking.
The first thing you will notice, is that the data is presented as a cross tab table, rather than in rows.
You have the years and transaction counts as heading columns.
You have the product description as the row heading.
For people who like excel, the reporter tool is closer to what they are used to, in terms of presenting data.
Where you see the letters, N, A, that means no data exists for that intersection of the heading and row columns.
Now.
We will click on, show set up.
We see that the interface to query the data is quite different.
In general, people who use the query tool, need to have some understanding of tables, queries and SQL.
With the reporter tool, a person can be a complete novice, as far as tables and SQL are concerned.
The user can simply copy fields, from the dimension index, to column headings or row headings as desired.
The user can copy fields, from the facts index, in to the column headings as desired.
No knowledge of tables or SQL is needed.
It is possible to create quite complex financial reports using the reporter tool.
Now.
We will click on, capture SQL, and then click on, get new data.
You will notice that it performed a select for the product description.
You will notice that it performed a select for the year.
You will notice it then performed the select to get the data.
You will notice that there is no sum, or group by, in the select statement, to select the data.
The reporter tool was designed to access Ralph Kimballs multi level databases, so that the data returned was already close to the aggregation levels required.
You can query the data models through the reporter tool, to see how that works.
Now.
We will close the windows and go back to the capsule with the data access icons in it.
Now, you have seen the examples for the banking models.
Most of the rest of the queries are very similar.
There is little point commenting about them along the way.
So we will play a little music as we go through the demonstration of each of the query tools and reporter tools.
Ok?
This concludes our demonstration of the banking database from Ralph Kimballs book.
As we said at the top of this video.
We would encourage you to buy a copy of his book, to have as your own reference in your career, whether that be as a data modeler, or a person who queries these databases.
You can download all these databases, and the Meta5 folder, to be able to query the databases, from the button below.
We sincerely hope you will download these databases, and the Meta5 evaluation edition.
We sincerely hope you will learn more about dimensional modeling, using the databases and software, Ralph Kimball played such a large part in bringing to the world.
Now you can log off your Meta5 desktop.
Meta5.
The better way.
We hope you liked this short demonstration video.
If you did like it, please hit the like button and please subscribe so that you will receive notifications when we release more videos.
Thank you very much for coming by our channel and watching our video, we really appreciate you.
I really appreciate your time and attention.
I wish you a good day!
Until the next blog post.
Goodbye for now.
