CAPITULO III. LA JUSTICIA ORAL Y COMUNAL TEPEHUANA
4.5 LOS PRINCIPIOS QUE GUÍAN UNA RESOLUCIÓN COMUNAL
One of the most powerful concepts in an Analysis Services project is that of the Data Source View, or DSV. A DSV is a logical construct that represents how the structure of the underlying database in the data source should look to Analysis Services. In its simplest form, it is merely a copy of the schema from the data source. Developers can simply add all the tables from the database in the data source and be done. The database schema is read, the joins between tables are automatically added to the DSV, and then the cube can be built.
The DSV has far greater capabilities than simply allowing a copy of the underlying database schema. First, not all the tables have to be brought in, as a subset will often suffice. This is especially true when building a data mart for a particular group. If the relational warehouse contains more than that group cares to see, the DSV would only include the tables relevant to that particular group.
Figure 3-7 shows part of the wizard that steps developers through the process of creating a DSV, using the Adventure Works DW sample database that ships with SQL Server 2005. Remember that with Analysis Services 2005, a single cube can contain multiple fact tables. This helps in situations such as recording sales at a daily level, but having a forecast or budget table at the month or quarter level. Figure 3-7 shows that there are two fact tables in this cube: FactSalesQuota and FactResellerSales. A subset of these dimensions has been added as well, in order to keep this example simple.
Note also in Figure 3-7 that there are three tables dealing with products:
DimProduct, DimProductCategory, and DimProductSubcategory. This is an example of snowflaking, as described earlier in this chapter. These snowflaked tables will be addressed shortly in order to simplify the view.
5 2
B u s i n e s s I n t e l l i g e n c e w i t h M i c r o s o f t O f f i c e P e r f o r m a n c e P o i n t S e r v e r 2 0 0 7After choosing the tables and views that will make up the DSV, the developer is presented with a graphical image of the DSV. This image is a schema diagram that matches the tables and views chosen in the wizard, and may look extremely complicated, depending on the number of items selected when creating the DSV. Figure 3-8 shows the DSV generated for the tables chosen in Figure 3-7.
Note that this doesn’t look like a typical star schema because there are two fact tables in it. DSV diagrams become wildly more complex as tables are added, especially when there are multiple fact tables. Fortunately, there is a way to create new diagrams that show only a subset of the tables. A good practice is to create one diagram per fact table. Each of those diagrams will then show that fact table and just the dimensions upon which that fact table relies. This means that developers will have a series of easier-to-use star schemas to work with as the process unfolds.
DSVs have additional features as well, two of which are the Named Query and the Named Calculation. Named queries act like a view in a relational database; they allow for the building a query that will act as a table. The underlying schema here is
Figure 3-7 The Data Source View Wizard steps developers through the process of creating a DSV, a logical view of the underlying physical database schema.
C h a p t e r 3 : D a t a W a r e h o u s i n g a n d B u s i n e s s I n t e l l i g e n c e
5 3
a snowflake schema wherein the product dimension is made up of three tables. It is a simple matter to write a query that combines those tables into a single view or, in DSV terms, a named query. In other words, writing a standard relational query, and then naming it, makes it appear on the DSV just like any other table.
Figure 3-9 shows the same DSV as before, but the three product tables have been replaced with a single named query called DimProductStar. DimProductStar contains the columns from DimProduct, DimProductSubcategory, and DimProductCategory in a single virtual table. Creating a named query changes absolutely nothing in the underlying database; it is merely a logical construct in the DSV. Named queries are easy to identify because the symbol next to the name looks like two small tables as opposed to a single large table icon used for physical tables.
Named calculations are similar to named queries in that they are logical constructs that exist only in the DSV. However, named calculations appear as a column in an existing table. As the name suggests, these new columns may be used to perform
Figure 3-8 A Data Source View representing two fact tables and several dimension tables
5 4
B u s i n e s s I n t e l l i g e n c e w i t h M i c r o s o f t O f f i c e P e r f o r m a n c e P o i n t S e r v e r 2 0 0 7mathematical calculations, or they may be used to concatenate strings. For example, the fact table might store the price of the item sold as well as its cost, so a simple calculation to get the gross profit on that item could be included in the fact table in the DSV.
In the case of this example, the named calculation is being used for string concatenation. The DimEmployee table has three columns for a person’s name: FirstName, MiddleName, and LastName. The named calculation shown in Figure 3-9 combines all the names into a single column called FullName. The fact that FullName is a named calculation is easy to see by the small calculator icon placed next to it.
The DSV is very powerful. Named queries can be added that, in effect, create new tables. Calculations can be added to existing tables, which can speed up cube queries later because the numbers are actually materialized in the cube itself. Tables in the
Figure 3-9 A new view of the DSV showing a named query has been used to replace the product dimension snowflake tables, and a named calculation has been added as well.
C h a p t e r 3 : D a t a W a r e h o u s i n g a n d B u s i n e s s I n t e l l i g e n c e
5 5
DSV can be joined or joins can be removed. Items can be renamed to friendlier names, and more. The DSV is very powerful and it is critically important because a cube can only be built from a DSV. Cubes have no knowledge of any data sources other than a DSV.