Sunday, February 19, 2012

Hands-On Training

As with anything else, the way to become familiar with data management is to constantly work with data.  I would like to offer some of that through this blog, in addition to continuing to examine some of the concepts of data warehousing.  I plan to provide scripts that will create data in your own Oracle instance so that you can practice working with it.  If you do not have access to (and permission to use) an Oracle instance through your employer, your school, or some other entity, one option is to use a couple of free products explained below.
Oracle offers a free product called Oracle Database Express Edition that, among other things, allows users to become familiar with data management.  This is a smaller version of the Oracle database that can be loaded onto a local machine running Windows or Linux.  Oracle Database Express Edition can be downloaded here. 

Oracle also offers a free integrated development environment (IDE) called Oracle SQL Developer that allows you to easily query data and build database objects.  Oracle SQL Developer can be downloaded here.
As a test, download the very simple sql script hereThis will create one basic table and insert three records.  Here are two ways of running it…
From an IDE - Open the file from an IDE (such as Oracle SQL Developer although others may be used as well) and run the script.  In Oracle SQL Developer, this involves clicking the Run Script button on the tool bar or pressing F5.
Using SQLPlus (in Windows)
1.)    On the machine on which Oracle is installed go to the windows command line (from the Run box type CMD).
2.)    Navigate to the folder that contains the script using the cd syntax.
3.)    Type the following: sqlplus {schema name}/{password} @{filename}  So, if you are loading this into a schema called “myschema” with a password being “mypassword”, the command would look like this:  sqlplus myschema/mypassword @test_tbl  Including the “.sql” extension with the filename is not necessary.
4.)    Press Enter.
You should see that one table was created, three rows were created, and the transactions were committed.   You can simply drop the test_tbl table at this point.  We will still be looking at some of the theory behind data warehousing, but along with that I’ll provide some paths for hands-on training.  I don’t want to get lost in the mechanics of getting the data into the database, so if you have a better way of loading a script like this, by all means use it (and share with us if you would like).
Find an Oracle environment that you works well for you, or create one using the products above (or something else if you like).  In the next post or two, I’ll provide a star schema that we can look at.
As an aside, I’m not trying to endorse Oracle as being a better product for building a data warehouse than something else.  My experience is with this product so it is the easiest and quickest way for me to share data in this way.  If you would like to take my scripts and update them so that they are appropriate for another product, feel absolutely free to do that. 

Image: Ambro /

No comments:

Post a Comment