CAPÍTULO IV. LOS XIDHUUTU’N O “PRÁCTICAS DE CURACIÓN/ARMONIZACIÓN”
4.1 RECUENTO DE LAS DISCUSIONES ACTUALES Y ARGUMENTOS GENERALES
Once the DSV has been completed, the next step is to build the cube and dimensions. In Analysis Services 2000, there was no choice but to build the dimensions first and then build the cube. Analysis Services 2005 allows for the creation of the cube without first creating the dimensions. Instead, the cube building wizard can create the dimensions are part of the process. If it sounds tempting to simply walk through the cube building wizard and let it create the dimensions, it is. Note, however, that no matter how well the wizard does its work, it’s almost a certainty that the dimensions will require some tweaks after they have been generated.
Building a new cube can be as simple as launching a wizard. Cubes only understand DSVs, so the wizard will ask what DSV to use in creating the cube. Upon examining the dimensions in the DSV, the wizard can try to build attributes and hierarchies for the dimensions. There is nothing wrong with this, but by default nearly every column in the dimension table will become an attribute, and this can lead to many attributes that may confuse end users. Secondly, the hierarchies as determined by the wizard are often incomplete or just plain silly, so fixes are often required there.
The Cube Wizard, as it is called, reads the DSV and tries to determine which tables are dimension tables and which are fact tables. Note that the wizard makes no use of the table names, so having the words “fact” and “dim” in the title does nothing to help the wizard. Instead, it looks at the relationships and tries to determine which tables belong in each category. It’s also more complex than this, because a single table can act as both a fact and dimension table, but that is a discussion beyond the scope of this book.
After choosing the facts and dimensions, it is often necessary to map a Time dimension. Time or Date dimensions exist in almost every cube. There are certain functions built into Analysis Services that only work when a dimension has been identified as a Time dimension. These functions are useful for performing actions such as period over period growth calculations, moving averages, and so forth. The ability to map a Time dimension to expected time values is flexible and includes both calendar and fiscal categories.
The wizard next tries to determine the measures that exist in the fact tables. Normally the wizard does a decent job identifying the measures by looking in the fact tables for numeric columns. Sometimes it includes some of the foreign keys
5 6
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 7as measures because they are numeric fields, but key values are normally just automatically incrementing values, so making them measures doesn’t make sense. Fortunately, any fields chosen as measures that shouldn’t be there are easy enough to remove before continuing. The wizard also automatically adds a count to the measures for each fact table.
By default, each fact table has its measures placed into its own Measure Group. A measure group is a collection of one or more measures, and by default each fact table has its own measure group. One exception is for measures that are distinct counts; for example, a store might have 10,000 shoppers during a week, but some people come in multiple times, so the number of unique shoppers might only be 7500. Distinct counts are put in a separate measure group by default for performance reasons.
After selecting the measures the cube wizard next tries to detect hierarchies in the dimensions. As previously mentioned, this is an area in which the wizard often has problems. The good news is that the warehouse developer can modify the hierarchy design at this stage or after the wizard has completed.
The wizard not only tries to determine hierarchies, but determines attributes as well. Attributes are those items that help describe a dimension item. For example, a hierarchy for a customer might be Country, State/Province, City, and finally Customer. However, a significant amount of additional information might be stored for a customer, such as age, height, weight, income level, education level, and more. A hierarchy that goes from Country to State/Province to Height probably doesn’t make sense, and neither would going from Education Level to Weight. Therefore, many of these attributes are not part of a hierarchy but are still valid for analysis.
The good news is that with these attributes, end users have the flexibility to analyze by nearly anything by simply selecting the attribute when constructing a query. The bad news is that users can be quickly overwhelmed when presented with a list of dozens of attributes from which to choose. This gets back to the discussion in Chapter 1; know the users. Most users would want to see just the hierarchies, because they provide a nice guided path through the data. Analysts would likely want to see everything and have complete flexibility to create their own queries, even if those queries involved many single attributes not included in any hierarchy.
After completing the wizard, the cube is shown in a manner similar to that in Figure 3-10. Notice that the measures are shown along the left-hand side, broken into measure groups that match the fact tables. Also note that main window with the cube diagram is labeled Data Source View at the top. This is unfortunate and leads to much confusion among those new to Analysis Services. This is not the same as the data source view itself, but is just a representation of the tables from the DSV.
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