Datasheet

Data Source Views
Data source views (DSVs) are a new concept to SQL Server 2005. This feature allows you to create a logical
view of your business data. They are a collection of tables, views, stored procedures, and queries that can
be shared across your project and leveraged in Analysis Services and Report Builder.
This is especially useful in large complex data models that are prevalent in ERP systems like Siebel or
SAP. These systems have column names like ER328F2 to make the data model flexible to support nearly
any environment. This complex model naming convention creates positions of people in companies who
specialize in just reading the model for reports. The business user, though, would never know what a
column like this means, so a DSV may map this column to an entity like LastPaymentDate. It also maps
the relationships between the tables that may not necessarily exist in the physical model.
DSVs also allow you to segment a large data model into more bite-sized chunks. For example, your
Siebel system may be segmented into a DSV called Accounting, Human Resource, and Inventory. One
example called Human Resource can be seen in Figure 1-5. As you can see in this figure, a friendly name
has been assigned to one column called Birth Date (previously named BirthDate without the space) in
the Employee entity. While this is a simplistic example, it’s especially useful for the ER328F2 column
previously mentioned.
Figure 1-5
DSVs are deployed as a connection manager. There are a few key things to remember with data source
views. Like data sources, DSVs allow you to define the connection logic once and reuse it across your
SSIS packages. Unlike connections, though, DSVs are disconnected from the source connection and are
not refreshed as the source structure changes. For example, if you change the Employee table in a
8
Chapter 1
04 584359 ch01.qxd 12/16/05 10:20 PM Page 8