hierarchical or parent child dimension?

View previous topic View next topic Go down

hierarchical or parent child dimension?

Post  saqib4u on Thu Apr 16, 2015 6:05 pm

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?

saqib4u

Posts : 2
Join date : 2015-04-16

View user profile

Back to top Go down

Re: hierarchical or parent child dimension?

Post  nick_white on Fri Apr 17, 2015 7:43 am

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

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: hierarchical or parent child dimension?

Post  saqib4u on Fri Apr 17, 2015 8:05 am

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'

saqib4u

Posts : 2
Join date : 2015-04-16

View user profile

Back to top Go down

Re: hierarchical or parent child dimension?

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