hierarchical or parent child dimension?
2 posters
Page 1 of 1
hierarchical or parent child dimension?
Hello,
The data I m dealing with is structured as follow.
A customer has many stores and each store has a POSSystem.
Customer
Stores
POSSystem
So now when I create Dimensions, will I create as:
TableName Cols
DimCustomer CustKey
DimStores StoreKey, CustKey
DimPOS POSKey, StoreKey, CustKey
Are these Hierarchical or Parent Child?
Now when I will create Fact table, will I have:
TableName Cols
Sample1: DimFactPOS CustKey,POSKey,StoreKey, #trans
OR
Sample2: DimFactPOS POSKey, #trans
If I do sample1, then why do I StoreKey, CustKey in DimPOS?
The data I m dealing with is structured as follow.
A customer has many stores and each store has a POSSystem.
Customer
Stores
POSSystem
So now when I create Dimensions, will I create as:
TableName Cols
DimCustomer CustKey
DimStores StoreKey, CustKey
DimPOS POSKey, StoreKey, CustKey
Are these Hierarchical or Parent Child?
Now when I will create Fact table, will I have:
TableName Cols
Sample1: DimFactPOS CustKey,POSKey,StoreKey, #trans
OR
Sample2: DimFactPOS POSKey, #trans
If I do sample1, then why do I StoreKey, CustKey in DimPOS?
saqib4u- Posts : 2
Join date : 2015-04-16
Re: hierarchical or parent child dimension?
Hi - it is normal practice to relate your Dims via the Fact table not directly. So each Dim has a single column as it's key and your fact table would have individual keys for Customer, Store and POS e.g.
TableName Cols
DimCustomer CustKey
DimStores StoreKey
DimPOS POSKey
TableName Cols
DimFactPOS CustKey,POSKey,StoreKey, #trans
TableName Cols
DimCustomer CustKey
DimStores StoreKey
DimPOS POSKey
TableName Cols
DimFactPOS CustKey,POSKey,StoreKey, #trans
nick_white- Posts : 364
Join date : 2014-01-06
Location : London
Re: hierarchical or parent child dimension?
Thanks nick for quick reply.
What you saying is there wont be relationship between Dimension tables.
We wont be creating any creating parent/child or hierarchical relationships via Dims.
We will be creating relationships via Fact tables. Is there any name for this methodology?
Now when we query fact table, How would I start my query?
select sum(#trans) from FactPOS f
inner join DimCustomer c on f.CustKey = c.CustKey
inner join DimStore s on f.StoreKey = c.StoreKey
inner join DimPOS p on f.POSKey = c.POSKey
where c.Name = 'Bakery1'
What you saying is there wont be relationship between Dimension tables.
We wont be creating any creating parent/child or hierarchical relationships via Dims.
We will be creating relationships via Fact tables. Is there any name for this methodology?
Now when we query fact table, How would I start my query?
select sum(#trans) from FactPOS f
inner join DimCustomer c on f.CustKey = c.CustKey
inner join DimStore s on f.StoreKey = c.StoreKey
inner join DimPOS p on f.POSKey = c.POSKey
where c.Name = 'Bakery1'
saqib4u- Posts : 2
Join date : 2015-04-16
Similar topics
» Dimension with hierarchical data - how to handle parent with no children
» parent child dimension model
» Dimension hierarchies having One child multiple parent
» Self referencing dimension - How to store Parent Key/Id as type II change
» Employee Hierarchical - Rolling down reporting
» parent child dimension model
» Dimension hierarchies having One child multiple parent
» Self referencing dimension - How to store Parent Key/Id as type II change
» Employee Hierarchical - Rolling down reporting
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum