Creating a target data warehouse

Finally, this is the time to create our target data warehouse system. The data warehouse structures and tables will be used by end users with the help of various reporting tools to make sense of the data and analyze it. As a result, it should help business users to make strategic decisions, which will hopefully lead to business growth.

We should not forget that the main purpose of a data warehouse, and hence that of our ETL system, is to serve business needs.

Getting ready

The data warehouse created in this recipe will be used as a target database populated by the ETL processes developed in SAP Data Services. This is where the data modified and cleansed by ETL processes will be inserted in the end. Plus, this is the database that will mainly be accessed by business users and reporting tools.

How to do it…

Perform the following steps:

1. AdventureWorks comes to the rescue again. Use another link to download the AdventureWorks data warehouse data file, which will be mapped in the same manner to our SQL Server Express database engine in order to create a local data warehouse for our own learning purposes. Go to the following URL and click on the AdventureWorksDW for SQL Server 2012 link:

   https://msftdbprodsamples.codeplex.com/releases/view/105902

2. After you have successfully downloaded the AdventureWorksDW2012.zip file, unpack its contents into the same directory as the previous file:

   C:\AdventureWorks\

3. There should be two files in the archive:
   • AdventureWorksDW2012_Data.mdf—the database data file
   • AdventureWorksDW2012_Log.ldf—the database transaction log file
4. Open SQL Server Management Studio and click on the New Query… button in the uppermost tool bar.
5. Enter and execute the following command in the SQL Query window:

   CREATE DATABASE AdventureWorks_DWH ON 
   (FILENAME = 'C:\AdventureWorks\AdventureWorksDW2012_Data.mdf'), (FILENAME = 'C:\AdventureWorks\AdventureWorksDW2012_Log.ldf') FOR ATTACH;

6. After a successful command execution, right-click on the Databases icon and choose the Refresh option in the opened menu list. This should refresh the contents of your object library, and you should see the following list of databases:
   • ODS
   • STAGE
   • AdventureWorks_OLTP
   • AdventureWorks_DWH

How it works…

Get yourself familiar with the tables of the created data warehouse. Throughout the whole book, you will be using them in order to insert, update, and delete data using Data Services.

There are also some diagrams available that could help you see the visual data warehouse structure. To get access to them, open SQL Server Management Studio, expand the Databases list in the Object Explorer window, then expand the AdventureWorks_DWH database object list, and finally open the Diagrams tree. Double-clicking on any diagram in the list opens a new window within Management Studio with the graphical presentation of tables, key columns, and links between the tables, which shows you the relationships between them.

There's more…

In the next recipe, we will have an overview of the knowledge resources that exist on the Web. We highly recommend that you get familiar with them in order to improve your data warehousing skills, learn about the data warehouse life cycle, and understand what makes a successful data warehouse project. In the meantime, feel free to open New Query in SQL Server Management Studio and start running the SELECT commands to explore the contents of the tables in your AdventureWorks_DWH database.

Note

The most important asset of any DWH architect or ETL developer is not the knowledge of a programming language or the available tools but the ability to understand the data that is, or will be, populating the data warehouse and the business needs and requirements for this data.