Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Building a data warehouse. Advice please?

Building a data warehouse. Advice please?

Building a data warehouse. Advice please?

Hello, I am building a data warehouse that will house information on communities around the globe. The database warehouse will initially start with U.S. data and then expand to global markets. It will contain a central dimension table with a list of cities and the corresponding states, metros (cbsas), counties, countries that they are within 2) individual dimension tables listing the elements of each geography type (for example - one with a list of cities, one with a list of states, one with a list of counties, etc) to be used for queries that are not city-based but for the other geography levels 3) various data fact tables housing demographics of various sorts (population, income levels, education levels, industry and occupation data), tax data, companies that are located in each city, schools in each city, anecdotal facts on each city.

The outputs will be tables comparing markets based on different combinations of factors, reports on individual markets (again showing different combinations of factors depending on the requirement).

The thing I am stuck on is table format (wide vs long). Do I structure these tables long for example? In a demographic table, there could be a hundred demographic variables for each city, each metro, each state, each county. Is that the most efficient way to do it. I picture tables with millions of records (and one data point) if we use long format. I have built a database in the past like this using wide format tables, but it was slow (ms access). The newer version will be in SQL, MySQL, or Snowflake (tbd) and will hopefully be faster.

I am not an expert in these technologies. What things do I need to take into consideration? Any advice would be appreciated.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close