Conformed Degenerate Dimension?
2 posters
Page 1 of 1
Conformed Degenerate Dimension?
Hello,
I am currently considering design options for a fact table that will contain Order receipts. The grain: Payment from a customer triggers a transaction in our system the records a receipted amount. One row represents one receipted amount.
I wish to create this as the OrderReceipts Fact table. I currently have an Order fact table, quite large 110,000,000 rows. For performance reasons I have kept it thin. Of course the fact tables will share conformed dimension, in particular Customer, Date, GeographicRegion (which includes currency attributes to the region) . For the most part access to the warehouse data is through Analysis services, however from time to time power users will want to run SQL queries directly against the warehouse tables. Linking the two data subject areas (Fact tables) can of course be done by rolling up data by a date, product or region and combining said sets of data. I intend to have a degenerate dimension OrderNo in the OrderReceipts fact table along with CurrencyNo, ReceiptNo, PaymentType, CustomerNo + Measures etc.... I also already have OrderNo in the Order Fact table as a degenerate dimension.
Given the following assumptions:
- Orders will not always have a receipted amount, if a customer does not respond to an order we never receive any money.
- If a customer does respond a receipted amount is recorded.
Someone may ask the question:
- How many orders that were receipted last month originated from the United Kingdom Region
Given that:
- Region information is only recorded against the order, not the Receipt.
If I wanted to see for example a count of customer orders and total receipt amount I could drill across using the conformed Customer dimension and that's that. Take a count of orders for a particular customer from the Order Fact table as data set 1, aggregate the receipted amounts from the order receipts fact table for said customer as data set two and combine.
In this case I am using the Customer conformed dimension to drill across.
Question: Is it wise to use a degenerate dimension in the same manner. For example I want to see all Orders where the receipted amount was of paymentType Credit Card. Again I would generate my two sets of data and then join the two sets using the two degenerate dimension attributes (OrderNo) from the Order and OrderReceipts fact tables respectively. In this case instead of using an actual separate table (as with the conformed customer dimension in the previous example) to drill across I am essentially using the degenerate dimension attributes from both fact tables to join the data sets.
Any thoughts?
I am currently considering design options for a fact table that will contain Order receipts. The grain: Payment from a customer triggers a transaction in our system the records a receipted amount. One row represents one receipted amount.
I wish to create this as the OrderReceipts Fact table. I currently have an Order fact table, quite large 110,000,000 rows. For performance reasons I have kept it thin. Of course the fact tables will share conformed dimension, in particular Customer, Date, GeographicRegion (which includes currency attributes to the region) . For the most part access to the warehouse data is through Analysis services, however from time to time power users will want to run SQL queries directly against the warehouse tables. Linking the two data subject areas (Fact tables) can of course be done by rolling up data by a date, product or region and combining said sets of data. I intend to have a degenerate dimension OrderNo in the OrderReceipts fact table along with CurrencyNo, ReceiptNo, PaymentType, CustomerNo + Measures etc.... I also already have OrderNo in the Order Fact table as a degenerate dimension.
Given the following assumptions:
- Orders will not always have a receipted amount, if a customer does not respond to an order we never receive any money.
- If a customer does respond a receipted amount is recorded.
Someone may ask the question:
- How many orders that were receipted last month originated from the United Kingdom Region
Given that:
- Region information is only recorded against the order, not the Receipt.
If I wanted to see for example a count of customer orders and total receipt amount I could drill across using the conformed Customer dimension and that's that. Take a count of orders for a particular customer from the Order Fact table as data set 1, aggregate the receipted amounts from the order receipts fact table for said customer as data set two and combine.
In this case I am using the Customer conformed dimension to drill across.
Question: Is it wise to use a degenerate dimension in the same manner. For example I want to see all Orders where the receipted amount was of paymentType Credit Card. Again I would generate my two sets of data and then join the two sets using the two degenerate dimension attributes (OrderNo) from the Order and OrderReceipts fact tables respectively. In this case instead of using an actual separate table (as with the conformed customer dimension in the previous example) to drill across I am essentially using the degenerate dimension attributes from both fact tables to join the data sets.
Any thoughts?
des_77- Posts : 3
Join date : 2014-05-15
Age : 46
Location : QLD Australia
Re: Conformed Degenerate Dimension?
Yes. A degenerate dimension is just as much a dimension as a full one. It just has less attributes.
'Conformance' itself has more to do with consistent attributes values, not dimension keys. So, if my aggregate required me to use a State Code from another dimension to ensure I can integrate with another fact table that contains a degenerate State Code value, so be it. Conformance dictates the values between the two instances represent the same thing.
'Conformance' itself has more to do with consistent attributes values, not dimension keys. So, if my aggregate required me to use a State Code from another dimension to ensure I can integrate with another fact table that contains a degenerate State Code value, so be it. Conformance dictates the values between the two instances represent the same thing.
Re: Conformed Degenerate Dimension?
Ok great thanks, that makes sense.
des_77- Posts : 3
Join date : 2014-05-15
Age : 46
Location : QLD Australia
Similar topics
» Conformed dimension or Degenerate dimension?
» Question on breaking out Degenerate Dimension to separate dimension
» Should I use a degenerate dimension or create a junk dimension?
» Replacing Aggregate dimension with Conformed base dimension at lowest level.
» Remove degenerate dimension?
» Question on breaking out Degenerate Dimension to separate dimension
» Should I use a degenerate dimension or create a junk dimension?
» Replacing Aggregate dimension with Conformed base dimension at lowest level.
» Remove degenerate dimension?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|