Building a Data Warehouse with SQL Server

Today, I am going to start a series of blogs on one of my favorite topics Data Warehousing with SQL Server.  I will start by defining a data warehouse.  I will then give some business cases that explain the benefits of a data warehouse.  Finally, I will start writing about the built in tools SQL Server provides to implement a data warehouse with SQL Server.

What is a Data Warehouse?

In its simplest form a Data Warehouse is a way to store data information and facts in an format that is informational.  Hopefully, you were able to pull this information from the photos above.   Personally, I like to think of a Data Warehouse as a tool used by decision makers to improve decision‐making.

Business cases for a Data Warehouse

The following are several reasons business cases that explain how “insert company name here” can benefit from a data warehouse.

• A Data Warehouse is fast to query. After the initial setup is complete, queries can run up to 1000% faster in an OLAP database than in an OLTP database.
• A Data Warehouses improves ROI by allowing end users to make more efficient use of enterprise information so many companies have all the information they need.
• A Data Warehouse is a good solution for application’s that are great for data entry but lacks in depth reporting and drilldown capabilities.
• A Data Warehouse could be used to bring several applications and/or data sources together.
• If you are a service company a data warehouse could be used to analyze work completed to estimate future flat fee engagements. (I can go on and on on these examples…)
• If you already own SQL Server you can implement a data warehouse solution with the built in tools.  This means no additional cost for software is needed.

What tools are needed to Build a Data Warehouse?

Both SQL Server 2005 and SQL Server 2008 include three tools that are very helpful towards implementing a Data Warehouse. These three tools allow you to create an killer data warehouse.

• Analysis Services (SSAS)
• Integration Services (SSIS)
• Reporting Services (SSRS).

We will go over these tools in great detail in future posts.  For now, let’s just think of SSAS as the tool that provides storage for the data used in cubes for your data warehouse. SSIS is the tool we will use to extract, transform and load (ETL) data into our data warehouse.  SSRS is a tool used to view the data in our data warehouse.

Next post : Introduction to Data Warehouse Dimensions