Conformed Dimensions

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

Anyone?

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)

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?

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

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.