DSVs and Object Bindings - Microsoft SQL Server 2008 Analysis Services - page 314

she has to create a username from two columns in the relational database: First Name and
Last Name. She would have to go back to the relational database design tools again. In
fact, she might have to go back again and again if she needs to create new views, new
calculated columns, and so on. The relational database schema would require many
tweaks before she could get it mapped into a structure of attributes and measures in a
cube.

Nothing much is more tedious to an application designer than the iterative nature of a
process like going back and forth between relational schema design tools and multidimen-
sional object design tools. To make matters worse, sometimes the person designing the
cube in Analysis Services has no access to the relational database so that he can make the
necessary tweaks.

If you're not yet convinced that you'll need the abstraction later, think about the problem
of change management. Let's say that you used Analysis Services to build a cube. It has
been deployed and working for a while. Now you want to point your cube to another
version of the relational database, perhaps because someone created a new version that
contains more data. But, the relational schema has been changed slightly. Now the cube
can't process simply because a table or a column name has changed. If your dimensions
have hundreds of attributes, a scenario such as this could be a nightmare.

You can use a DSV object to deal with all these problems. A DSV enables you to create a
view of a relational database schema inside a multidimensional model. You can create
tables, columns, relationships, calculated columns, and named queries that map to the
real tables and columns in your relational database. In Business Intelligence Development
Studio (BI Dev Studio), a DSV looks just like a relational schema, with tables, columns,
and relationships. Listing 18.1 is an example of a DSV.

LISTING 18.1

The Definition of a Foodmart2008 Data Source View

<DataSourceView>
<ID>Foodmart2008</ID>
<Name>Foodmart2008</Name>
<Annotations>
</Annotations>
<xs:schema id=”Foodmart2008 ....
<xs:element name=”Foodmart2008
<xs:complexType>
<xs:choice minOccurs=”0” maxOccurs=”unbounded”>
<xs:element name=”dbo_currency”
<xs:complexType>
<xs:sequence>
<xs:element name=”currency_id”msprop:FriendlyName=”currency_id”

msprop:DbColumnName=”currency_id” msprop:design-time-name=”a6b6df3d-0cd1-443e-

➥

942c-13c765410175” type=”xs:int” minOccurs=”0” />

➥