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.
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.
Perform the following steps:
AdventureWorkscomes to the rescue again. Use another link to download theAdventureWorksdata 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:After you have successfully downloaded the
AdventureWorksDW2012.zipfile, unpack its contents into the same directory as the previous file:C:\AdventureWorks\There should be two files in the archive:
AdventureWorksDW2012_Data.mdf—the database data fileAdventureWorksDW2012_Log.ldf—the database transaction log file
Open SQL Server Management Studio and click on the New Query… button in the uppermost tool bar.
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;
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:
ODSSTAGEAdventureWorks_OLTPAdventureWorks_DWH
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.
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.