Degenerate Dimensions and Common Degenerate Dimensions
A degenerate dimension is based on one or more columns within a fact dataset. This type of dimension can also be based on one or more columns that are common to two or more fact datasets (in which case, the dimension is a common degenerate dimension). The dimension columns are not normalized into a separate dataset.
Degenerate dimensions
When you have your dimensional data and fact data in the same dataset, you can still model logical dimensions in AtScale. For example, in the model below, the Geography dimension, which is a degenerate dimension, is embedded in the clickstream fact dataset.
A degenerate dimension can be modeled on one or several columns of the fact dataset. In this case, the modeling workflow is similar to that of a normal dimension, except that the table or view backing the dimension is the fact dataset itself.
Logical dimensions that are degenerate dimensions are represented with a gray header on the main cube canvas.
Common degenerate dimensions
Suppose that a cube has two fact datasets: factinternetsales_1
and
factinternetsales_2
. You want to be able to view the data for both
fact datasets by a shared set of dimensions: a Date
dimension, a
Product
dimension, and a Currency_Key
dimension. The data for the
first two dimensions are in separate datasets. The data for the
Currency_Key
dimension are in the currencykey
column of each fact
dataset. Currency_Key
is therefore a degenerate dimension and, to use
it with both fact datasets, you'll need to make it a common degenerate
dimension.
Figure 1. The factinternetsales_1 fact dataset, the columns of which are identical to the columns in the factinternetsales_2 fact dataset
The two fact datasets have the same columns. The main difference is that
they have different values for the currencykey
column.
Table | Values in the currencykey column |
---|---|
factinternetsales_1 | 6, 19, 29 |
factinternetsales_2 | 39, 98, 100 |
First, you choose to take care of the regular dimensions. You include
the Date
dimension and you join orderdatekey
in each fact dataset to
datekey
in the Date Month
hierarchy. You next include the Product
dimension and you join productkey
in each fact dataset to productkey
in the dimension. Your cube now looks like this:
Figure 2. Relationships from the two fact datasets to the Date and Product dimensions
You also create two new measures, one on each fact dataset, based on the
column salesamount
.
Figure 3. Creating the measure Sales Amount Fact 1 on the fact dataset factinternetsales_1
Then, you create a calculated measure that is the sum of the two measures you just created.
Figure 4. Total Sales, a calculated measure that is the sum of the two measures just created
Now, you create the common degenerate dimension that is based on the
currencykey
column in factinternetsales_2
. To do so, you drag the
currencykey
column on to the Dimensions palette, as you would normally
do to create a degenerate dimension.
Figure 5. Creating a degenerate dimension that is based on the currencykey column in factinternetsales_2
The degenerate dimension appears in the upper-left corner of the canvas.
Figure 6. The new degenerate dimension appearing in the upper-left corner of the canvas
You drag it to the center of the canvas.
Figure 7. The Currency_Key degenerate dimension between the fact datasets
To also use this degenerate dimension with the fact dataset
factinternetsales_1
and make it a common degenerate dimension, you
drag currencykey
from that table on to the dimension.
Figure 8. Dragging currencykey from factinternetsales_1 to the degenerate dimension
When you create a common degenerate dimension, you must ensure that it conforms to these constraints:
- You must use the same number of columns from each fact dataset.
- The data types must be consistent. Key columns used from each fact dataset must all use the same data types. Value columns used from each fact dataset must all use the same data types.
- If an order column is selected in one dataset, the order column from each of the other fact tables must be selected, too.
Note: As of version 7.4, AtScale supports the creation of hierarchies with more than one level for shared degenerate dimensions. See the Add a Degenerate Dimension or a Common Degenerate Dimension topic for detailed instructions.
After dragging the column, you now see that both fact datasets have relationships that connect them to the common degenerate dimension.
Figure 9. Relationships now exist from each fact dataset to the common degenerate dimension
If you now publish the project and connect to the .tds
file from
Tableau, place Total Sales
on the Rows shelf, Product Category
on
the Columns shelf, and highlight the values of Currency_Key
by color,
you can see the frequency of the use of the different currency keys
across the product categories.
Figure 10. The frequency of the use of the different currency keys across the product categories
As indicated by this legend, the full range of currency keys in both fact datasets is being used in the display.
Figure 11. The color legend for the bar chart
Moreover, if you drag Currency_Key
into the Filters box, the full
range of values is also available for filtering.
Figure 12. The selection box for filtering on the values of Currency_Key