Database Reference
In-Depth Information
Named Queries
Each data source view table is a select statement. You can modify these select statements and join data to them
from more than one SQL Server table. In our example, the Titles dimension includes both the DimTitles and
DimPublishers tables in a snowflake pattern (Figure
9-28
). We can combine these two tables into one data source
view table using SQL code. To do this, right-click the design surface and choose New Named Query from the
context menu (Figure
9-28
). This launches a query designer.
Figure 9-28.
Adding a named query
The query designers in SQL Server, SSIS, SSRS, and SSAS are all same. They allow you to create SQL code by
adding one or more tables to the user interface and provide checkboxes to select the columns you intend to use.
To add tables to the UI, right-click in the upper part of the Query Definition window and select Add Table.
Figure
9-29
displays a SQL join that includes DimTitles and DimPublishers. To include all the publishers
(even those without any associated titles), click the connecting relationship line and select the Select All Rows
from DimPublishers option from the context menu (Figure
9-29
).
After you create your query and close the Create Named Query dialog window, you will have a new table
in the data source view. If you created a new data source table that includes data duplicated in other tables, you
must remove those other tables from the data source view. Once that is complete, these multiple tables function
as a single table—at least as far as SSAS is concerned.
Tables from multiple databases can be combined as well. For example, because we did not include the city
and state in the DimStores table, we could add those columns now by joining the DimStores table to the Stores
table in the original Pubs database, based on the Store ID. This is not a commonly seen strategy, but it is an
example of the flexibility of the data source view.
■
For our purposes, within our exercises, we are leaving the DimTitles, DimPublishers, and DimStores as
they are. We continue to point out how the current design affects the Bi solution throughout the book.
Note
