Conformed Dimensions

View previous topic View next topic Go down

Conformed Dimensions

Post  pkettley on Thu Jun 30, 2011 7:19 am

Hi,

My question is regarding having heterogenuous products and conformed dimensions where the surrogate key is used from one dimension (an overall product dimension) and applied to another dimension that contains more information regarding the specific product.

If I wanted the dimension in the overall product dimension to be type 1, and the specific product dimension to be type 2 SCD should I create a foreign key in the specific product dimension, like below.. as I want to make sure I have the drillable effect in reports where I want to see more detail of the product's transactions. Note the FeeValue is does not apply to other products and can change over time.

I've cut the tables down considerably.

Dimensions
create table dbo.DimProduct (ProductKey int, Title nvarchar (50))
create table dbo.DimGiftProduct (GiftProductKey int, ProductKey int, FeeValue numeric (18, 3), EffectiveDate datetime, DeletedDate datetime, Deleted char (1))


Fact Tables
create table dbo.FactOverallSales (ProductKey, SalesTotal numeric (18, 3))
create table dbo.FactGiftSales (GiftProductKey, FeeValueTotal numeric (18, 3), SalesValue numeric (18,3), NetTotal numeric (18,3))


Kind Regards,

Paul

pkettley

Posts : 5
Join date : 2011-06-30

View user profile

Back to top Go down

Re: Conformed Dimensions

Post  pkettley on Mon Jul 04, 2011 10:22 am

Anyone?

pkettley

Posts : 5
Join date : 2011-06-30

View user profile

Back to top Go down

Re: Conformed Dimensions

Post  ian.coetzer on Mon Jul 04, 2011 1:17 pm

Hi,
I did something similar for customers and customer demographics - these are called Mini-dimensions if I am not mistaken and work as follows:

"Remember that it is simpler to have a fact table with a foreign key to a dimension - than to go from fact -> dim 1 -> dim 2"

* DimCustomer
(CustomerKey int, CustomerDemographicKey int, CustomerFullName varchar(500) etc., RowIsCurrent char(1), etc.)

* DimCustomerDemographic
(CustomerDemographicKey int, City varchar(100), State varchar(100), Occupation varchar(50), Dependents int, Industry varchar(50))

* FactAffordability
(CustomerKey int, CustomerDemographicKey int, MonthlyIncome money etc.)

So in this case it stays a simple star schema and is easy to later on build SSAS cubes etc. off of this structure.

In your schema [FactGiftSales] must be joined to both dimensions to group by overall product title,
i would prefer it join to one dimension to get that grouping try not to create to many parent child relations.

Keep-it-straight-and-simple by creating a start schema of your data -
I was once in a Ralph Kimbal class were there is no problem linking to both those dimensions since they contain different attributes that describe the same fact record at different levels of aggregation (product title / product specific details)

avatar
ian.coetzer

Posts : 57
Join date : 2010-05-31
Age : 36
Location : South Africa

View user profile

Back to top Go down

Re: Conformed Dimensions

Post  ngalemmo on Tue Jul 05, 2011 3:51 pm

What is the difference between a product and a gift? What is the difference between a product sale and a gift sale?

Can a product be sold as a product and as a gift? Is the population of products and gifts mutually exclusive?

Why do you need two fact tables and two different keys?
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 Dimensions

Post  colealex141 on Wed Jul 13, 2011 10:48 am

I don't know but i want to know about this topic can any one provide us information for our better knowledge.

colealex141

Posts : 1
Join date : 2011-07-13
Age : 29
Location : San Diego

View user profile

Back to top Go down

Re: Conformed Dimensions

Post  rob.hawken on Wed Jul 13, 2011 6:17 pm

seems to me you are describing a Hot Swap dimension (brief article at http://www.kimballgroup.com/html/designtipsPDF/DesignTips200020KimballDT16HotSwappable.pdf) These are described in more detail in the Data Warehouse Toolkit book. For this to be the case then gift products are a subset of products but with sufficient differences to justify having their own table.

rob.hawken

Posts : 13
Join date : 2010-09-19

View user profile

Back to top Go down

Re: Conformed Dimensions

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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