MS SSAS 2008 Unleashed - Chapter 9. Multidim - Models and BI Development Studio

2012-8-16 15:24| 发布者: demo| 查看: 1244| 评论: 0

摘要: Chapter 9. Multidimensional Models and Business Intelligence Development Studio In This Chapter Creating a Data SourceDesigning a Data Source ViewDesigning a DimensionDesigning a CubeConfiguring and D ...

Designing a Data Source View

A Data Source View (DSV) is an Analysis Services object that represents an abstraction level on top of a relational database schema. (For detailed information about DSVs, see Chapter 18 .) You might create a DSV that includes only the tables and relationships relevant to your multidimensional model. You can also create named queries and calculated columns in the DSV so that you can extend the relational schema.

Creating a New Data Source View

To create a new DSV, you use the Data Source View Wizard. When you are in the wizard, you have to choose a data source. If you need to create a new data source, you can do that from the Data Source View Wizard—the Data Source View Wizard takes you into the Data Source Wizard and then returns you to where you came from. (Yes, you are going in circles. Do not ask.)

Note

You can also start with the Data Source View Wizard to create a new data source and then create a DSV for it.

Launch the Data Source View Wizard

1.	In the Solution Explorer, right-click the Data Source Views node to launch the Data Source View Wizard. Breeze through the Welcome page as usual.
2.	On the Select a Data Source page, under Relational Data Sources, select the data source you want to use a DSV for, and then click Next. If you need to create a new data source, click New Data Source; doing so takes you to the Data Source Wizard. Complete the Data Source Wizard. (You can find the steps for this in the preceding section.) When you finish the Data Source Wizard, it sends you back to Select a Data Source page of the Data Source View Wizard. Select your new data source and click Next.
3.	If the Data Source View cannot find relationships between tables in the relational database, go to the Name Matching page, where you will find options for creating logical relationships by matching columns in the relational database.
4.	On the Select Tables and Views page, the available tables and views are listed under Available Objects. There is another list under Included Objects, blank in the beginning, of the tables or views included in your DSV. Between the two lists are two arrows, one pointing left and one pointing right. To add tables or views from the Available Objects list to the Included Objects list, click the arrow that points to the right. You can see the Select Tables and Views page in Figure 9.3. Figure 9.3. Use the arrows to move tables from one column to the other.
5.	On the Completing the Wizard page, you can name your DSV.

You can make your life easier if the first table you add is the one you want to use as a fact table. Use the arrow to add that table to your DSV. Then, click the Add Related Tables button. The related dimension tables will appear in the Included Objects list. If you want to also include tables that are related to the dimension tables, click the Add Related Tables button again. Now tables are added to the list under Included Objects.

Modifying a DSV

We are going to use FoodMart 2008 to demonstrate how to modify a DSV. Our DSV is a pretty simple one; it includes only about 20 tables. But, some DSVs can get much more complex, especially when your multidimensional model is created on top of a relational schema with a lot, maybe hundreds, of tables and relationships.

To work with the FoodMart 2008 DSV in the DSV Designer, start in BI Dev Studio. In the Solution Explorer, right-click FoodMart 2008.dsv. From the menu that appears, select View Designer. Figure 9.4 shows you what you will see as BI Dev Studio shifts to its DSV Designer. The DSV Designer consists of multiple UI elements described in Table 9.1.

Figure 9.4. You can modify the properties of your DSV in the DSV Designer of BI Dev Studio.

[View full size image]

Table 9.1. The DSV Designer User Interface
UI Space	Description
Center pane	Tables and the relationships between them. For each table, the table columns are listed.
Left pane	By default, the Diagram Organizer contains an All Tables diagram—one that contains all the tables in the DSV. To create a new diagram, right-click anywhere in the Diagram Organizer space. From the menu that appears, select the type of diagram you want to create. Under Tables is a list of all the tables that are included in your DSV. Expand a table node to see all the columns in the table. Click a column to show the column’s properties in the Properties pane.
Properties pane	Beneath the Solution Explorer on the right, the Properties pane contains the properties of one column of the table that is expanded in the tables.

You can use the DSV Designer to refine your DSV, as follows:

You can drag and drop to rearrange tables and relationships.
You can delete tables from the DSV by selecting them and pressing the Delete key.
You can add tables to the DSV by right-clicking anywhere in the center pane and selecting Add/Remove Tables from the resulting menu.
You can use that same menu to create a named query that will appear as a new table in the DSV. (Select New Named Query.)
To create a new named calculation, which will appear as a new column in a table, right-click the table and select New Named Calculation from the resulting menu.

Editing a Named Calculation

You can edit the properties of a named calculation in the Edit Named Calculation dialog box. You can reach that dialog box by double-clicking the column that represents the named calculation. The dialog box already contains values for each property; you can change any or all of them.

In Figure 9.5 , you can see the Edit Named Calculation dialog box that displays when you double-click the WeekOfYear column in the time_by_day table.