To help you learn about dimensions, let’s review a couple of the reports that you’ve already seen in the preceding section . Table 1-8 shows the Adventure Works Units Sold by Month report that you first saw in Table 1-3 . In this report, total Units Sold is subdivided by the members of the Month attribute . The number of months—four—determines the number of values in this report . This is analogous to calculating linear distances in the physical world:
the length of a line is simply its length .
TABLe 1-8 Adventure Works Units Sold by Month Jan 2011 Feb 2011 Mar 2011 Apr 2011
4 14 27 25
Table 1-9 shows the Adventure Works Units Sold by Model and Month report that first ap-peared in Table 1-7 . In this report, monthly Units Sold values are further subdivided by the members of the Model attribute . The potential number of values in the report equals the number of models multiplied by the number of months, so you now have up to 12 values to consider . This is analogous to calculating the area of a rectangle in the physical world: the area of a rectangle is equal to its length times its width . The report even looks like a rectangle .
TABLe 1-9 Adventure Works Units Sold by Model and Month Jan 2011 Feb 2011 Mar 2011 Apr 2011
Mountain-500 3 8 6 6
Road-750 15 16
Hitch Rack 1 6 6 3
The comparison to a rectangle, however, applies only to the arithmetic involved in calculating the number of values, not to the shape of the report . You could just as easily rearrange your report by placing the Month attribute on rows so that the report would look like Table 1-10 .
TABLe 1-10 Adventure Works Units Sold by Model and Month
Hitch Rack Jan 2011 1
Whether the values in your report form a rectangle as in Table 1-9 or they form a line as in Table 1-10, you still have the potential for 12 values if you have four members of the Month attribute and three members of the Model attribute . Your report has 12 potential values be-cause the members of the Model attribute and the members of the Month attribute are in-dependent . Each model gets its own sales value—even if that value is zero—for each month . Consider a new report . Suppose that Adventure Works sells products in two different states and you’d like to know the number of units of each model sold in each state . Also, in addition to seeing these numbers by month, you would also like a total for all months . To generate this report, your analyst adds the State attribute to the rows of the report and replaces the Month attribute with the Month attribute hierarchy . The new report is shown in Table 1-11 .
TABLe 1-11 Adventure Works Units Sold by State, Model, and Month
All Months Jan 2011 Feb 2011 Mar 2011 Apr 2011
WA Hitch Rack 9 4 3 2
The report now has two members of the State attribute, three members of the Model attri-bute, and five members of the Month attribute hierarchy . The number of potential values, 30, equals the number of states multiplied by the number of models multiplied by the number of months . This is analogous to calculating the volume of a cube in the physical world: the vol-ume of a cube is its length multiplied by its width multiplied by its height .
It’s clear that for this report the comparison to a cube applies to the arithmetic used to cal-culate the number of values in the report and doesn’t apply to the report’s physical shape . The values in Table 1-11 are arranged in a rectangle . Table 1-12 shows the first few rows of the same report, except the Month hierarchy has been placed on rows . This report shows the same information as Table 1-11, but the values are now arranged in a line .
TABLe 1-12 Adventure Works Units Sold by State, Model, and Month
WA Hitch Rack All Months 9
But whichever way you lay out your report, State, Model, and Month are independent at-tributes and hierarchies, and the total number of potential values in the report equals the number of members in the first independent attribute (two States) multiplied by the number of members in the second independent attribute (three Models) multiplied by the number of members in the independent attribute hierarchy (five Months) .
To calculate length, area, and volume, you multiply the lengths of an object’s dimensions . To calculate the potential values in a report, you multiply the number of members in the report’s independent attributes and hierarchies . The independent attributes and hierarchies are the dimensions of the report, so BI practitioners have borrowed the term dimension from mathematics . Dimensions contain attributes and hierarchies . For example, the report in Table 1-11 displays the State attribute that belongs to the Geography dimension, the Model attri-bute that belongs to the Product dimension, and the Month attriattri-bute hierarchy that belongs to the Date dimension . Although the term dimension is borrowed from mathematics, it is im-portant to remember that a data analysis dimension is very different from a physical dimen-sion . Using the term dimendimen-sion doesn’t imply anything about how attributes and hierarchies should be physically arranged on a report or even about how multidimensional data should be stored in a database .
A dimension may contain more than one attribute, but every dimension has one and only one key attribute . Any other attributes that are related to the key attribute belong in the same dimension . Because attributes belong to dimensions, attribute members are also di-mension members . So, for example, Product is the key attribute of the Product didi-mension . If you know the value of a Product attribute, you also know the value of the Model, Color, and Size attributes, so these attributes also belong in the Product dimension . Silver is a mem-ber of the Color attribute and 48 is a memmem-ber of the Size attribute, so Silver and 48 are also members of the Product dimension .
Dimensions also contain hierarchies . In the preceding section titled “Hierarchies in Data Analysis,” you learned that hierarchies are created by organizing attributes into levels . The at-tributes used to create a hierarchy must be in the same dimension and the hierarchy will also belong to that dimension . For example, the Product by Model hierarchy is composed of at-tributes contained in the Product dimension, so the Product by Model hierarchy also belongs in the Product dimension .
The dimension that contains measures is structured differently than all other dimensions . There is no Measures attribute and no hierarchies are created from measures . Instead, there is only a Measures dimension that contains the list of attribute labels that identify measures . Table 1-13 shows a report in which the Units Sold and Sales Dollars members of the Measures dimension are displayed on columns .
TABLe 1-13 Adventure Works Sales by State, Model, and Month
Jan 2011 Feb 2011 Mar 2011 Apr 2011
Units $ Units $ Units $ Units $
WA Hitch Rack 4 $480 3 $360 2 $240
Mountain-500 2 $1,105 6 $3,265 5 $2,775 5 $2,750
Road-750 9 $4,860 10 $5,400
OR Hitch Rack 1 $120 2 $240 3 $360 1 $120
Mountain-500 1 $565 2 $1,105 1 $540 1 $540
Road-750 6 $3,240 6 $3,240
Units = Units Sold, $ = Sales Dollars
The report now contains a fourth dimension . The number of values in the report still equals the product of the number of members in each dimension: 2 × 3 × 4 × 2, which equals 48 . But there is not—and there does not need to be—any kind of physical world analogue . Remember that dimensions are simply a convenient way of grouping related attributes and hierarchies, and having four (or 20 or 60) dimensions is just as realistic as having three . Adding dimensions just increases the number of values displayed on a report or stored in a database . In the physical world, the object you are measuring changes depending on how many dimensions there are . For example, a one-dimensional inch is a linear inch, but a two-dimensional inch is a square inch, and a three-two-dimensional inch is a cubic inch . A cubic inch is a completely different object from a square inch or a linear inch . In your report, however, the object that you measure as you add attributes and hierarchies from multiple dimensions is always the same: a numerical value—and a numerical value in a “four-dimensional” report and a numerical value in a “one-dimensional” report are the same . In the reporting world, an additional dimension simply creates a new, independent way to subdivide a measure .
Table 1-13 shows that adding a fourth dimension does not transform a rectangular report into a hyper-cube, but that’s not to say that adding a new dimension is trivial . Suppose that you start with a report with two dimensions: 30 products and 12 months, or 360 possible values . Adding three new members to the product dimension increases the number of values in the report to 396, a 10 percent increase . Adding a third dimension with three new mem-bers, however, increases the number of values in the report to 1,080, a 300 percent increase . Consider this extreme example: With 128 members in a single dimension, a report has 128 possible values, but with those same 128 total members split up into 64 dimensions—with two members in each dimension—a report has 18,446,744,073,709,551,616 possible values!
This section has introduced multidimensional data analysis . A dimensional data model has measures, numerical values that can be aggregated, and related attributes and hierarchies organized into dimensions . The dimensions provide metadata that turns the numerical data into information and makes analysis possible . The dimensions provide the descriptive labels for the numbers and let you know how the data should be grouped, sorted, formatted, and aggregated .
A dimensional data model can be implemented in a relational database management sys-tem (RDBMS) or in a multidimensional OLAP database . In fact, it is very common for a BI system to have both . Data from source systems is extracted, transformed, and loaded into a data warehouse stored in a RDBMS . Then the data is extracted from the data warehouse and loaded into a multidimensional OLAP database . In the next two sections, you will learn how a dimension model is implemented in each type of database .