IN2BI, Microsoft Business Intelligence
data, stories & insights
rss | email | twitter

DWH DECK Part 3: Importing Source Information

Adapted for version 1.1 on 18/04/2011

This is the third part of a series on the DWH DECK. A tool that will allow you to build and maintain your data warehouse better and faster. This series will act as documentation and will give you some insides into the way the program works.

Overview of this series

Using Business Areas
In the DWH DECK we use business areas to group related information. (e.g. Finance, HRM, Sales)
In the model you can filter by business area and in the databases they are implemented as SCHEMAS.
 
Menu Application - New Business Area. Enter the name of the Business Area and click Add.

Using Linked Servers
After you have created the business areas you'll need you can start importing the source information.
If the source database is on another sql server you have to use a linked server.
(In management studio: under Server Objects, Right Click Linked Server and Select New Linked Server..
or use sp_addlinkedserver http://msdn.microsoft.com/en-us/library/ms190479.aspx)
 
Importing Source Information
Menu Application - New Data Source

New Source for DWH Deck

Type the server name of the source server, select the source database and select the business area that's most relevant for this data source. Next select the tables from the source database that you need and click Create.

The meta data of each table and each column in the table is now imported and a simple profile of each table and each column is done. This can take some time depending on the size of your source system. Progress is shown in the textbox. When finished: click Cancel.

New Source Ready for DWH Deck
 
Based on the metadata and profile information several choices are made. Expand the Source Database in the Source Tables Treeview and select a table. View the metadata and profile on table level:

  • Last modified (when was the table last modified)
  • Rows (The number of rows)
  • The primary- and foreign relations to other tables.

 

Source Table View In DWH Deck

Select a column in the source column list and view the metadata and profile information on column level:

  • Number of distinct values and % of total rows
  • Number of null values and % of total rows
  • The minimum and maximum value
  • The maximum length of the values in the column
  • The Data Type

Source Column View In DWH Deck

 
We'll discuss the choices and their impact in Part 4 to 6 when we discuss the staging area, the data vault area and the data mart database.

» Similar Posts

  1. DWH DECK Part 6: Data Vault
  2. DWH DECK Part 2: Creating your data warehouse
  3. DWH DECK Part 5: Staging

» Trackbacks & Pingbacks

    No trackbacks yet.

» Comments

    There are no comments.

Comments are closed