Conformed Degenerate Dimension?

View previous topic View next topic Go down

Conformed Degenerate Dimension?

Post  des_77 on Wed Jun 03, 2015 12:11 am

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?



des_77

Posts : 3
Join date : 2014-05-15
Age : 39
Location : QLD Australia

View user profile

Back to top Go down

Re: Conformed Degenerate Dimension?

Post  ngalemmo on Wed Jun 03, 2015 6:14 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Conformed Degenerate Dimension?

Post  des_77 on Wed Jun 03, 2015 9:45 pm

Ok great thanks, that makes sense.

des_77

Posts : 3
Join date : 2014-05-15
Age : 39
Location : QLD Australia

View user profile

Back to top Go down

Re: Conformed Degenerate Dimension?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top


 
Permissions in this forum:
You cannot reply to topics in this forum