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 am studying Data Warehousing in SQL Server 2012

I am attempting to learn the various tools included by practicing on the different tools separately.

I have practiced with:

  1. AdventureWorks samples using Integration Services.
  2. Analysis Services for creating cubes, mining structures and data quality services.
  3. Creating Domains and Knowledgebases with Integration services.

Can anyone give me some clear directions on how to start my first Data Warehousing project? Which tools do I use, and in what order?

share|improve this question

closed as not a real question by jcolebrand Sep 7 '12 at 19:40

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

    
Welcome to our site! I apologize for the edits, but I removed the bit about which tools to use as that is a very broad topic unless you have a specific question (in which case it would need a separate question anyway). For the rest of the question, I'm not really sure what you're asking for. Can you clarify what you're looking for regarding the 'data warehousing overall flow'? –  Derek Downey Jul 24 '12 at 14:07
add comment

2 Answers

Not 100% what you are getting at, whether you're talking about the order of developing a data warehouse or how the data flows into the warehouse and on to other things.

There is a camp that models all the data into the data warehouse (using dimensional modeling techniques, and only generally informed by the users roles and domain expertise) and then the end users build their analytics on it.

There is another camp that gathers end-user requirements and then builds the warehouse to meet that, identifying the data in the source system.

I tend to be more in the first camp, because users only know what they want once they play with the data and see what's available.

share|improve this answer
    
I need to order of developing a data warehouse via integration and analysis services with data quality services... –  kumar t Jul 26 '12 at 5:04
add comment

Here's what I would do in a greenfield data warehouse project for a small company. They're only roughly in order, in practice they'll overlap a lot.

  1. Gather requirements. Talk to the heavy report users. Find people who use and value data and get them to open up about what they'd like to do if they had all the data at their fingertips. The most important people are those who have the power to support or thwart your project.
  2. Set up your warehouse server, and create at least three databases: RawData, Staging, and Warehouse.
  3. Sketch out a normalized schema to cover as much of the data as possible, given time constraints. At least, cover a useful subset of the most important data.
  4. Establish credentials and connections to various data sources. This will range from easy (linked servers) to complicated (APIs).
  5. Set up ETL to import raw data into the RawData database. As time allowed, make it bulletproof. To start, you'll probably be fine with daily loads.
  6. Write SQL to import and normalize this data into your Staging database.
  7. Produce a few proof-of-concept reports using the collected normalized data.
  8. Sketch out a denormalized (e.g., star schema) database for the warehouse proper.
  9. Write SQL to clean data, create synthetic keys, and populate the Warehouse database. This should be fairly easy since you've already collated everything in the Staging database.
  10. Keep tossing out new reports occasionally so the powers that be have a sense of progress.
  11. Select and set up a reporting platform. Create a database or at least a schema with views and sprocs for this tool to use.
  12. Open up access. Some people may get direct (read-only) access to the warehouse itself, but most will use a reporting or analysis tool. It can be limited to just the views and sprocs set aside for it in the previous step.
  13. Set up near-real time ETL. You won't be able to update everything, so find out what most needs to be current.

All the way through, grill anyone you can find on What They Need to Know. Be aware that most people won't know what they need until you show them, so bring imagination and think big.

Start with data at the lowest possible grain, at least in your raw and staging data. If you find out that the customers need daily data, not just monthly, it should be simple to drill down a level and provide it.

It's lovely to build a warehouse with everything in it, and a good goal, but in the middle term you need to produce valuable reports for important people in several departments. You'll probably encounter some foot-draggers, and it helps if you can call in someone high up to twist arms: to get you access to a silo, to get time with people who know the dark corners of the data, to get users to give up an existing and inferior data mart, etc.

share|improve this answer
    
Hot to do it? by analysis services?8.Sketch out a denormalized (e.g., star schema) database for the warehouse proper. –  kumar t Jul 25 '12 at 10:32
2  
Read The Microsoft Data Warehouse Toolkit by Joy Mundy, and also the Kimball books, they give very good guidance. –  SqlACID Jul 25 '12 at 11:09
1  
What SqlACID said. The Data Warehouse Toolkit by Kimball is a good brief intro, but it's a huge topic, don't expect to be able to jump right in. –  Jon of All Trades Jul 25 '12 at 16:28
add comment

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