SQL Server 2012 Data Warehousing Overall Flow [closed]

Question

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?

Answer 1

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.

Answer 2

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.