Header and Footer in Informatica
Remove the footer from a flat file
November 21, 2017
Flat file target – Test scenarios
November 22, 2017
Show all

Where will the Degenerate Dimension’s data stored?

Degenerate Dimension

Degenerate Dimension

A customer (Cust_ID: 123) has purchased a product (Product_Id:456) and the order number of this purchase is (OrderNo 7890).

Customer and Product are the dimensions in this example.

Sales is the fact table and it contains all the information related to the dimension keys and the measures (Total Quantity, Total Price).

Degenerate Dimension

There is no specific dimension table for Order numbers to be stored, but that is needed for the Sales fact and it is populated directly into the fact as an attribute to make the transaction record complete. If you have to maintain the Order numbers separately as a dimension, you will have only order numbers attribute in the table and you will have exactly the same number of records as you have in the fact table. Because, you have to have an order number for each purchase/sale/transaction.

So, instead of storing it separately and assigning it a surrogate key (as done for other dimensions) to populate it in the fact, we directly use the value in the fact.

Where is it stored?

In the fact table (directly).

Where do we get it from?

From the data source. If this purchase is done online, you would get it either from the app or the web page. If this is done from the store, the store will generate a unique order id and that will be taken from the OLTP or from a file as an attribute.

If there is a possibility to add more attributes to make the order number meaning full with some descriptive or reference values, then this can be considered as a normal dimension and the surrogate key from the dimension can be used in the fact.

Is this a normal scenario which should be considered during the design or ETL load process?

For the requirements to create and store the transactional level fact tables, it is normal. Columns that have useful information to the business especially some attributes which can be loaded as is from the data sources will be part of the degenerate dimension.

Additionally, it will also reduce the burden on the reporting/dashboard tools to make an additional join to show a single attribute like the order number.