Tutorial 4: IT Computer Careers Data Warehouse/ Data Architect Professional

When reviewing the list of computer careers in lesson 15, I discovered that one of the most challenging and rewarding IT careers with very good growth potential was not listed. It is one of the IT careers where excellent business skills are as necessary as excellent technical skills. It is Data Warehouse/ Data Architecture Professional

According to Wikipedia, (http://en.wikipedia.org/wiki/Data_architect) some of the duties of a data architect or data warehouse designer are
• Logical Data modeling
• Physical Data modeling
• Development of a data strategy and associated polices
• Selection of capabilities and systems to meet business information needs
• Data Analysis Capabilities using SQL (Structures Query Language)

Data Professionals are also called upon to create or manage a Data Strategy. (including data best practices, metadata repositories, data retention policies and data stewardship.)


Below is a sample of a Data model and as SQL Data Analysis Query:



Sample SQL for Data Analysis


An original work by Theresa Fortnash



Sample Physical Data Model


An original work by Theresa Fortnash



With the exponential growth in all kinds of electronic data, the demand for professionals that can turn data into information is expanding. If you type in Data Warehouse or Data Architect in to Monster.com (http://jobsearch.monster.com/search/data-architect_5) you will find hundreds of very well paying jobs


Results
245 Data Architect jobs matched your search
706 Data Warehouse jobs matched your search

There is an important caveat. Almost all jobs in Data Architecture or data Warehousing require years of prior IT experience with large data systems.
(http://jobsearch.monster.com/search/data-architect_5)


An Excellent source for Networking, training and certifications for Data Professionals are:
The Data Washhouse Institute (http://tdwi.org/) (TDWI)
DAMA International (http://www.dama.org/i4a/pages/index.cfm?pageid=1)

Both organization have annual conventions and have regional chapters.

There are also certification available that can enhance your marketability as a Data Professional:

DAMA Certified Data Management Professional Certification Program (CDMP) (http://www.dama.org/i4a/pages/index.cfm?pageid=3399)
- Certified Data Management Professional (CDMP)
DAMA sponsored vendor-independent professional certification/designation. There are two levels of certification: Practitioner and Mastery level. This certification program has been offered by DAMA since 1993. The ICCP manages the delivery of the CDMP certification for DAMA.
Institute for the Certification of Computing Professionals (ICCP)
(http://www.dama.org/i4a/pages/index.cfm?pageid=3365#ICCP)
- Certified Computing Professional (CCP)
When taking the CDMP you can also look into this certification.

I have been working as a Data Professional for over 15 of my 25 years in Information Technology. I would recommend this path for anyone considering a career in IT.

Secure Email Project

Secure E-mail was a fun project and has given me a fun new tool for e-mail. I plan on continuing to use the Thunderbird e-mail. The only real issue I encountered the forwarding issue. I had my UF email forwarded to my home e-mail as that is the one a I check several times a day. When Dr. Means replied to my encrypted e-mail it was automatically forwarded to my home account so I had to turn off forwarding and have her resend the e-mail. Bow that I have thunderbird installed, I will probably not turn forwarding back on as it is quite easy for me to see my UF mail.

INBOX





Data Security


Data Security is a topic that is near and dear to my heart. I did a presentation for SEEMUG ( Southeast Erwin Modelers Users Group) a few years back where to topic was data as an asset to be protected and the case study I used was TJX (TJ Max and their subsidiaries) This link http://www.networkworld.com/columnists/2007/012207-bradner.html is an good primer article about the event. The author, Scott Bradner, of Network World gives a cursory explanation of the PCI (Payment Card Industry) security standards. The main point in the article is that there were 3 parties that share responsibility for a breach that is believed to have compromised over 40 million credit card numbers at an estimated cost of 7.2 billion dollars in damages. The author believes that the thieves of commission were the actual people that breached TJX’s non-compliant network and stole the numbers. But, he believe that there is also equal blame for the extremely lax network and data security employed by TJX and the lax enforcement by Fifth Third Bank, TJX’s acquiring bank. The acquiring back is the back that secures the funds for TJX from the credit card transaction and is the entity, according to the PCI standard, that is responsible for insuring compliance. Since the breach in 2006, most large companies have taken many extra precautions to insure that all PCI specification are met. At the company I work for, twice a year, I review all data warehouse databases to insure that there is no sensitive data stored in any of the reporting databases.

Database Project

Database design is my life’s work. I have been designing Relation and Dimensional models for over 20 years. My primary DMBS are DB2 and SQL Server (Relational Transactional Databases) and Oracle (Dimensional Analytics Databases). While I have worked in Access as a means for analyzing data from flat files that were over 64,000 rows and that I did not want to seat in my Oracle Databases, I have never worked with the forms or reporting aspects of Access. It was fun to see that you could create a small application without programming skills, and the reporting features may prove valuable for data research and validation that needs to go to my business users

There are a few things I would do to enhance this database. I would pull the address information out of the members table and create a members address table. This would allow the business to store more then one address for a member (home, office, or for students school address and permanent address) I would create a members phone number table. This would allow us to track multiple telephone numbers for a member (Home, Cell, Work, etc.). I would have a reference table for address type and I would encrypt the Birthdate Column I would create a State table that stores the industry standard state abbreviations and would make this column a pull down list to insure data integrity. Without business requirements, that is about as much as I would be willing to suggest.

Tutorial 3: Creating A Relational Data Model

For my third tutorial, I opted to take the Database assignment an create an actual small third normal form database based on this one table. I used Erwin to create an Logical Model also know as an Entity Relationship Diagram. (ERD)

The steps needed to normalize and expand a database from flat file data begin as follows

1. Profile the existing data. Evaluate what was truly dependent on the True Primary Key of the members table. It is my opinion that the true primary key was a compound key of the First Name + Last Name + Birth date

2. I looked to see if there were ways to make the data entry easier while also creating better data integrity. That led me to create a state table that could be a pull down menu from the entry forms.

3. I decide that individuals could have more then one address, so I created a Physical address table that would record all street addresses. I created an associative table that would allow an address to belong to multiple members (Family Membership) and allow each member to have more then one address (Home, Office, Dorm)

4. I thought that it might be important to store the member e-mail address. I decided that individuals could have more then one email address, so I created a email address table that would record all email addresses. I created an associative table that would allow an e-mail address to belong to multiple members (Family Membership) and allow each member to have more then one email address (Home, Office, Junk)

5. I created a Membership Transaction Table. I did this so that we could tract all of the different interaction that we have with a member. The table we have only tracked joined and left. I created a transaction type reference table that will store Joined and Left but also Renewed, Rejoined, Suspended, or any other type of transaction that a member might do.

6. The transaction table it associated to the member through the email and address table so that we can run reports based on the address and e-mail address the member used for the transaction.



This ER Diagram is an original work of Theresa Fortnash.

It is always best to have a business requirements document and access to a Subject Matter Expert (SME) when designing or modifying a database as there may be many hidden business rules that may need to be accommodated in the database or explained in the metadata. Metadata is the information about the tables columns entities, attributes and the database itself. There is both technical and business metadata. The ERD should contain both.

This is a very basics lesson in the thought process of a relational data modeler. If anyone interested in learning about creating a dimensional (Star Schema) Data warehouse database from this model, send me an e-mail or a comment and I will be happy to help.

Presentation Project

The best and most lasting growth comes when we move from our comfort zones. This presentation project was miles from mine. It made me understand how my mother felt when I gave her her first computer. I started the assignment excited because the project I chose was one that I wanted to do for a long time. The discomfort came from using tools that were very unfamiliar to me.

It my professional life, I spend all day long staring at a computer screen, but the time is spent writing queries or ddl or in ERwin designing databases. Even though I had designed hundreds of powerpoint presentations, I never used any of the fun frills like narration and slide timing or custom animation. With this assignment I used all of those and also stretched my creativity by learning a wireframe tool called MockFlow. The final step of the project, loading the presentation to the internet was a complete mystery to me. I am eternally grateful to the blog posters as I would never have figured it out without reading the posts.

I was reluctant to post my presentation for critique as it is a venture capital idea that I am actively pursuing (and you cannot copyright or patent an “idea”) however, self critiquing my presentation, I felt it had a very logical flow from idea to idea but that my transitions could have been smoother (both with my script and my narration tool). I shared information at a “30,000 foot” level as we say in IT. I felt that getting too technical would not hold the interest of venture capitalists.

As for anticipating questions, I did a high level idea presentation. It is my hope that there were unanswered process questions as I would hope to get to another meeting. The Purpose of the initial presentation was to spark interest in the idea. I also deliberately used minimal text and relied on narration. I felt that the photographs were the best way to get my point across about the diversity of choices and the need to be able to pare them down in a methodical manner.

While there are many things I will do differently in version 2 of this presentation, I feel I did a decent job of getting to the heart of my goal quickly and concisely. I am looking forward to refining this presentation and one day very soon presenting it to actual venture capitalists in a personal setting. I have a great deal of faith in this idea and I am fortunate to work with very talented developers and designers. I am going to bring this idea and this presentation to them and see if we can form the team to make this project a reality.

To view my Florida Staycation presentation, use the link :

http://www.authorstream.com/Presentation/tfortnash-874632-weekend-staycation/

Digitizing Music from Vinyl Records

I love music and have been collecting it since long before CD’s were invented. When I was a teenager and young adult, I was a professional musician and spent most of my discretionary income on record albums and 45 rmp singles. I have quite literally over one thousand recordings on vinyl (Analog). Many of them have just never been captured for sale on a digital format. Hence, the purpose of this blog. Please note, these are very basic instructions to help you get your music to your computer. Audacity software is quite complex and I am not an Audacity master. That being said, it is the best way I have found to inexpensively get my vinyl on to my computer. Without further ado, here are the steps I followed:

In order to convert Analog Vinyl to digital files you will need:

1. USB Turntable ( mine is Numark)
2. USB cable
3. Software to interface with the turntable (Audacity)
4. Patience
5. If your vinyl is in less then pristine condition, there are editing software available to help remove the ‘pops” and “hisses” including audacity and others.

Step I
Connect USB Turntable to a USB Port on your computer.

Step II
Load the Interface Software
Audacity is Freeware. There are version for Mac OS, Windows, and Linix/Unix. The Download is easy and quick. There is a new Beta Version from Windows 7 and good documentation about the new features on the website http://audacity.sourceforge.net/



Step III
Start The Audacity Program and your turntable and Record the song to your Computer. Press the red Record icon (circle) and put the stylus on your record. You will see the image of the wave as it is recording.


Step IV
Save your File, Open it and Edit it. Audacity has tons of editing function for changing pitch, speed (which I do as I record the songs fast and then slow them to the correct speed), Click removal, fade out. It is not as robust as a profession editing program but it still has a lot of features. All of the editing features are in the “effect” tab.

Step V
When you are finished editing, Audacity will allow you to save your song in either .mp3 or .wav format. I save mine in .wav to a 2 terabyte external drive so that I have a lossless copy for my archive. I then save a new copy as mp3 so that I can play it on my car stereo and ipod.

Excel Project

The Excel Project for Spring ISM3004 was an eye-opening, “ah-ha” moment for this “old dog”. The project and the lectures discussed a number of excel functions from basic to moderately advanced. I have been working with excel since the early 1990’s and yet, right after watching the lecture about the “Find” function, I became better at my job as a data professional.

I spend many hours of my professional life exporting very large quantities of data from Oracle Databases and evaluating it for user reported problems or data profiling. My tool of choice for this has always been Excel. Up until this class, I had used very basic techniques that got the job done but were very labor intensive. This week, I was evaluating database catalogs to determine if a particular piece of data was stored in any of about 50 database schemas. We use fairy consistent naming standards for our columns in most of our IT controlled Schemas, so I was able to use the “= Find” function to locate a particular naming standard “_nbr” and translate it to a constant that was easier to sort and evaluate. Needless to say, this reduced the number of columns that needed to be evaluated by about 90%. That one new technique saved me untold hours of tedium.

It seem that every lecture so far in this class has had a very positive impact on my professional life. I suspect that is because, once you have a method to get a job done, you rarely have the time or the inclination to look for a better way. It is the classic, “If it ain’t broke don’t fix it” axiom. I have a whole new mind set about that now. Thomas Edison used to walk up to his employees and say “There is a better way --- Find it” I plan on looking for that better way in all of the tools I use on a daily basis thanks to this lesson.

I have used pivot tables in a past career for writing reports. Presently, I perform those function with tools like Business Objects or Hyperion (Brio) as they can subscribe to the data without export (Oracle, DB2, SQLServer) but I think I may find some new uses for pivot tables in my daily work.