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.

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