Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've started a new role which deals with large amount of related data. Our source of all this data is various Excel dumps pulled from databases we do not have access to. The previous person filling this role used a dozen or so Excel files to gather these data files, manipulate them, and create reports.

I have started moving the dumps to an Access database. I have noticed a lot of the Excel data is related and should probably be normalized. What I have currently been doing is creating a table for every data dump, and importing them to Access, and using a number of queries to replicate the dozens of data manipulations and reporting.

Are there still benefits to normalizing data where my only source are Excel dumps out of a warehouse?

How would I normalize data, when I do not have the ability to change the format of how the dumps are sent to me?

Additionally, my plan (budget-depending) is to move from Access to a MS SQL Database.

share|improve this question

migrated from stackoverflow.com May 2 '13 at 6:27

This question came from our site for professional and enthusiast programmers.

4  
Normally, you do NOT want to normalize data that is non-transactional and will only be used for reporting. If the data is coming out of a data warehouse, they already did the hard work for you. Just load it into Access and query away. –  HardCode May 1 '13 at 19:14
    
Just to clarify, do the Excel dumps contain the raw data, or is it pre-aggregated/report data? Is the data source coming from a vendor export of some kind (i.e., are you able to communicate with whoever designed the data structures involved)? –  Jon Seigel May 7 '13 at 17:13
    
Excel dumps are from either the vendor or our own data warehouse, and so it is either raw data, or raw data with minimal amount of transformations (e.g. 0/1 changes to Yes/No, or Acct Code mapped to Account Description). I'm able to communicate with those who designed the data structures but have no say in how they are designed. To give some context, I am not a DBA, but I'm trying to find the best solution for our team given budgetary and political constraints. –  multiphrenic May 7 '13 at 17:35

1 Answer 1

up vote 2 down vote accepted
+50

Yes there are significant benefits to data normalization if you are willing to do the work to achieve and maintain it. The two fundamental benefits to normalization are:

  • Data Integrity
  • Query Flexibility

The simple approach to normalization is to create a table for each person, place, thing, concept, or event. By doing this, you have each characteristic of each person, place, thing, concept, or event of interest to you in one and only one place in the database. The benefit of data integrity is achieved for when you insert and update data. Because you have normalized you don't have redundant copies of the same characteristic in many places across the database, each with a different value, that you have to remember to update and keep in sync. Second, you have only one place in the database to program checks when inserting or updating data to make sure the data is valid. Since your data is coming in from many excel spreadsheets, normalizing your data gives you the opportunity to implement data integrity checks on the load to make sure you analysis is based on accurate data. The benefit of query flexibility is achieved for when you want to read and analyze the data. Because you have normalized the data you can connect up the tables in a flexible way based upon the question about the data you want answered, including only what you need to answer the specific question. Second, this enables the database to return the answers to your questions much faster than if it had to scan through all the data, including the data not relevant to your question, in your un-normalized tables .

Access is a simplified DBMS and does include a basic SQL processor that allows you to write queries and thus take advantage of the benefits of normalized data. If you are eventually going to move to SQL Server, which is a full featured DBMS, then normalizing your data now will ease the transition and let you take advantage of the full capabilities of SQL Server and its very rich implementation of SQL.

As I mentioned in the beginning, to achieve these benefits you must be willing to do the up front programming to translate the data coming in from your various excel dumps and map the rows and columns in those spreadsheets to your normalized tables. This is not a trivial exercise but doable using Access programming. One approach would be to create tables that replicate the data as in the source and load the data into them. These are known as stage tables. Once you have the un-normalized data in Access tables you can then more easily write access code using SQL to extract the data from those stage tables, normalize it, identify data quality issues (say the same characteristic in two different excel dumps that should have the same value but do not), and load it into your normalized tables. This is the common method for normalizing data coming from an un-normalized source very common in subject area based data warehouses.

You will find this additional level of effort to be well worth it though once you have high quality, normalized data in your access database. You report consumers will see that you are a real data professional when you show them examples where data quality was poor and you discovered that fact so it can be corrected in the sources. Likewise, when they ask for a new report that analyzes the data in a very different way, you can rapidly create the new report using SQL to combine the data in the normalized tables in this very different way that wasn't originally anticipated. They will be very impressed that you are able to do this quickly and easily!

I hope this helps explain why normalization would be of benefit to you.

share|improve this answer
    
Thank you very much! A beautiful answer that has be me convinced to put in the upfront effort to get this mess cleaned up. I wonder if you have any recommended books or other sources to read up on best-practices for database normalization and other important concepts? –  multiphrenic May 9 '13 at 14:08
1  
Glad I could be of help. Here is a decent link on database design and normalization in general - sqa.org.uk/e-learning/MDBS01CD/page_01.htm. This is pretty good because it keeps things simple and is the easiest to understand of many examples I've seen. A good book, especially since you may move to SQL Server, is Louis Davidson's "Pro SQL Server 2012 Relational Database Design and Implementation ". This book contains best practices in addition to normalization and gives examples for SQL Server. For staging, check out "The Data Warehouse ETL Toolkit" by Ralph Kimball. Good luck! –  Todd Everett May 10 '13 at 0:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.