â

EZ Data Dictionary : Getting Started.

First, insert the EZ Data Dictionary CD and install the EZDD Administrator program. This will install both the administrator software and the inquiry software. Next, it is important to discuss a few concepts that will help explain the capabilities.

All of the metadata (data about data) for the source databases at a user site are contained in the EZ Data Dictionary Database. Initially, this is provided as a Microsoft Access 2000 database. After the user becomes familiar with the capability in that technology, the EZDD Database can be changed to SQL Server, Oracle, or UDB for Windows. It is necessary to first install EZDD using the Microsoft Access 2000 Database, and then it can be changed to another technology.

The diagram below outlines the relationship between the EZDD Software and the EZDD Database, as well as the source databases.

For the initial installation and demonstration, there are two source databases and one EZDD Database. The source databases are the user databases that are to be documented. The EZDD Database contains that documentation. The source databases are not modified; they are only available for read access. The software will do all the updates to the EZDD database.

The first step is to install the software on a machine that has Microsoft Access 2000 or 2002 installed. After the installation of the software is complete, run the EZDD_Administrator program, as shown on the screen below, and follow the steps on the following pages.

The first screen looks like this:

The default user code and password are shown. The user code is EZDD and the password is admin. These can be changed later, and the password can be prevented from showing at all, but this is designed to simplify the initial learning experience. User code EZDD has full authority to update everything in the database. A later activity is to start “tightening down” the security. Click on Login, and the following screen appears:

This is the main screen of EZDD_Administrator. There are two source databases in the EZDD database as a starting point. The first one is the EZDD database itself (this is always required to be database number 1). The second one is the Microsoft Access Northwind database (provided by Microsoft as a demonstration database). There are many functions of EZDD_Administrator, but the most common one is to add a new database. This capability will be demonstrated using the Microsoft SQL Server demonstration database called Northwind, but a new user should probably pick a database from their specific environment. The database chosen should be a database in Microsoft SQL Server, Oracle, Microsoft Access, or IBM’s UDB for windows. Some of the following screens may be slightly different, depending on the technology chosen, but they will be similar to this example.

Click on Add a New Database, and the following screen appears:

This contains some brief instructions. Click on the leftmost button called Connect. The following screen appears:

This is standard Microsoft capability that allows you to pick a database. Choose the Microsoft OLE DB Provider for SQL Server in this example, or choose a different technology if the database uses Oracle, UDB for Windows, or Microsoft Access (Microsoft Jet 4.0 OLE DB Provider). Click Next. The following screen appears:

fill in the server name in item 1. Choose the security method in item 2, and then the name of the database in item 3. The example used is:

You may test the connection, then click OK. The following screen appears:

The instructions at the top are for the next few steps on this screen. The concept of this screen is to choose an owner from the database. For EZ Data Dictionary purposes, a database is a collection of tables that have a common owner. This is typical for an application perspective.

If you need a USER_CODE and PASSWORD to sign on to the source database, enter them in the boxes shown. Make sure that the appropriate DBMS_NAME is highlighted, and then click Connect to Database. This will sign on to the database and find all the available owners of tables in the database. In this example, the only owner of tables in this database is dbo. By clicking connect, the screen will look like this:

The final step on this screen is to click on the owner that is appropriate, so that the owner will be shown in the box at the bottom titled: OWNER_SELECTED. After clicking on dbo, the screen will look like this:

Now, click Next. The screen below will appear:

This screen shows a number of items that describe the database. Since we previously had databases 1 and 2, this new database has been assigned a number of 3. It is not necessary to update anything on this screen, since it can all be changed later, but the simplest thing to do is to specify a name for the database. This is a name that means something to the users who will see this later. In our example, we will click on the cell in row 2, column 2, and type: Northwind Demo – SQL Server. The screen will look like this:

Now, click Next, and the following screen appears:

The database summary information is now stored in the EZDD database, but the information about the tables, columns, indexes and relationships are not yet in the database, so this screen will get all that information. There are some options on this screen that would apply later, but for now, simply click Begin Update. As the program progresses in collecting this information, the names of tables, indexes, relationships and columns appear on the screen to show the user that progress is being made. If the database chosen is a large one, this process can take several minutes. EZ Data Dictionary is counting the rows in all the tables during this process as well, so that can take a long time if the tables have hundreds of thousands of rows. The screen will look something like this as it is processing:

When the processing is complete, the following screen appears:

Click on Go Back. This will return to the main screen as shown below:

Notice that database number 3 has been added. At this point, click Exit. Now run the EZ Data Dictionary Inquiry program by clicking Start – Programs – EZ Data Dictionary 2008 – EZDD_Inquiry, as shown below:

the following screen appears:

Again, the default USER_CODE and PASSWORD appear. This demonstrates the program for a user who has full authority to update anything. The three databases appear, and database number 2 is highlighted, because the parameter DEFAULT_SELECT is set = Y for this database. You can change that setting in EZDD_Administrator. For now, we will choose that database for the demonstration. Click on Search Database Selected. The following screen appears:

The tables in the database appear on the left. Click on the Customers table, and the screen looks like this:

There are many options here, but it is important to understand a few things at this point. The Columns of the Customers table are listed in the physical order in which they are found in the database. In order to list them in alphabetical order, simply click on the gray column heading titled “COLUMN_NAME”. The screen will then look like this:

Notice that the columns are now in alphabetical order. By clicking on “COLUMN_NAME” again, they will be listed in reverse alphabetical order. By clicking on the “ORDER” column heading, they will be back in their original physical order. This same technique will apply to any of the grids in EZ Data Dictionary that you see (i.e the Database list, and many others that you will see on subsequent screens). For now, click on the column name “City”, and the following screen appears:

This provides a narrative description of the column “City”. The user can type into any of the boxes shown for more narrative information, and then click on Update, in order to update the database (only if the user code for the sign on has authority to update the appropriate data). For now, click on Preview Data. The following screen appears:

These are the first 200 unique values for City in this table. Now click on Back to Search.

The following screen appears:

Click on Table Details (in the upper right portion of the screen). The following screen appears:

This describes details about the table “Customers”. The items in the boxes can be filled in as additional items of documentation, and the Update Data button will update the EZDD Database with that information. Now click on “Display First 200 Records”, and the following screen appears.

This allows the user to see some sample data that is in the source database table. Click Go Back. The following screen appears:

Now click on All Column Details. The following screen appears:

This is showing all the columns in a spreadsheet format. The user can update the data in appropriate cells, and then click Update. Now click Back to Search. The following screen appears:

Now click on All Tables Details in the lower left corner of the screen. The following screen appears:

This shows all the tables in the database in a spreadsheet format. By scrolling to the right, the other columns appear. This may be a faster method to update TABLE_DEFINITIONS for all the tables. Now click on Go Back. The search screen appears again:

Now, click on Back to Login (near the bottom, middle of the screen). The following screen appears again:

Now, click on Compare two Databases. The following screen appears:

Compare the Northwind Database in Microsoft Access to the Northwind Database in SQL Server by clicking on database number 2 on the left side and database number 3 on the right side. The resulting screen should look like this:

Now, click on Find Differences, and the following screen appears:

This shows all the differences between the two databases. It will show Tables Dropped, Tables Added, Columns Dropped, Columns Added and Columns changed. Now click on Compare Row Counts. The following screen appears:

This shows the table that are in common between the two databases, and how many rows appear in each table, and the differences. It is easy to see that the Northwind SQL Server database has many more Order Details, but many of the other tables are the same.

There are many other capabilities of EZ Data Dictionary (such as Search all databases). Begin to explore them!!! If you have any questions, feel free to call 877-436-1919.

Good Luck!!!