1201
Chapter 53: Building Multidimensional Cubes in Analysis Services with MDX
53
Creating the Data Source View
Use the following basic steps to create a data source view:- Add needed tables and named queries to a data source view.
- Establish logical primary keys for tables without a primary key.
- Establish relationships between related tables where necessary.
- Change tables’ or columns’ friendly names and create named calculations.
Begin by creating the data source view via the wizard: Right-click the Data Source Views
folder, and select the New option. There are several pages in the wizard:■ (^) Select a Data Source: Choose a data source to add tables from. If more than one
data source is to be included in the data source view, then the fi rst data source
must be a SQL Server data source.
■ (^) Name Matching: This page appears only when no foreign keys exist in the
source database, providing the option to defi ne relationships based on a selec-
tion of common naming conventions. Matching can also be enabled via the
NameMatchingCriteria property after the data source view has been created,
identifying matches as additional tables added to an existing view.
■ (^) Select Tables and Views: Move tables to be included from the left (available
objects) to the right (included objects) pane. To narrow the list of available objects,
enter any part of a table name in the Filter box, and press the Filter button. To add
objects related to included objects, select one or more included objects, and press
the Add Related Tables button.
■ (^) Completing the Wizard: Specify a name for the data source view.
After you create the data source view , you can add more tables by right-clicking in the
diagram and choosing Add/Remove Tables. Use this method to include tables from addi-
tional data sources as well.
Similar to a SQL view, you can defi ne named queries , which behave as if they were tables.
Either right-click the diagram and choose New Named Query, or right-click a table and
choose Replace Table/with New Named Query. This brings up a Query Designer to defi ne the
contents of the named query. Using named queries avoids the need to defi ne views in the
underlying data sources and allows all metadata to be centralized in a single model.
As you add tables to the data source view, primary keys and unique indexes in the underly-
ing data source are imported as primary keys in the model. Foreign keys are automatically
imported as relationships between tables. For cases in which primary keys or relationships
are not imported, they must be manually defi ned.
For tables without primary keys, select one or more columns that defi ne the primary key,
right-click, and select Set Logical Primary Key. When primary keys are in place, you can
c53.indd 1201c53.indd 1201 7/31/2012 10:30:22 AM7/31/2012 10:30:22 AM
http://www.it-ebooks.info