Creating facts and dimensions from raw data

View previous topic View next topic Go down

Creating facts and dimensions from raw data

Post  scha on Tue Feb 09, 2016 11:26 pm

TENDER


Last edited by scha on Thu Feb 18, 2016 1:54 pm; edited 1 time in total

scha

Posts : 3
Join date : 2016-02-09

View user profile

Back to top Go down

Re: Creating facts and dimensions from raw data

Post  ngalemmo on Wed Feb 10, 2016 2:23 am

A fact represents an event that is placed in context by its dimensions. Events may contain measures which are values that describe the magnitude of the event.

Take Traffic Count as an example. The event is a consumer entering a store. The context of that event is the time it happened and the store where it occurred. In an atomic fact table there would be a row for every such event. Traffic Count is an aggregation of multiple events, in this case a summary, of the total number of times for a particular day. Count is the measure and date and store are the dimensions.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Creating facts and dimensions from raw data

Post  scha on Wed Feb 10, 2016 9:49 am

So I'm thinking Item , online , purchase order entry will be my fact tables. So how can I connect these three and how can I add Traffic count to my fact table.Can you just tell me what fields go into my fact tables for my better understanding?
Thanks

scha

Posts : 3
Join date : 2016-02-09

View user profile

Back to top Go down

Re: Creating facts and dimensions from raw data

Post  zoom on Wed Feb 10, 2016 11:33 am

Here is a high level break down of your Dims and Fact. You have to familiarize yourself with dimensional modeling techniques to understand this… you need alot of reading on DM :-)

-- Here is a list of your Dims:
Store
Supplier
Customer
Date
Tender
Combine Item, Department, and category to create a 1 Dim.

-- Create a Transaction Fact table.

Fact table will have Ids from above Dims.

Combine PurchaseOrder, Purchase Order Entry, Transaction, Transaction Entry
Tender Entry , Online data into this Fact table.

Once you have that fact table then you can do this:
Select store_id, date, count(*) AS Traffic_count
From Fact
Group by store_id, date

zoom

Posts : 97
Join date : 2010-08-23
Location : Chicago

View user profile

Back to top Go down

Re: Creating facts and dimensions from raw data

Post  scha on Wed Feb 10, 2016 12:04 pm

Thanks


Last edited by scha on Thu Feb 18, 2016 1:55 pm; edited 1 time in total

scha

Posts : 3
Join date : 2016-02-09

View user profile

Back to top Go down

Re: Creating facts and dimensions from raw data

Post  zoom on Wed Feb 10, 2016 1:05 pm

Another name for “Item” is product. You have to store cost and quantity in Item dim because they are attributes of it. For example 1 stick of gum costs 99 cents, but 1 pack of gum costs $ 5.00.

You also need price and quantity in the Fact table to represent how much of it was bought by a customer. For example 1 stick of gum is on sale and it costs 99 cents but selling for 50 cents . Now a customer bought 2 gums (quantity) for a price of $ 1.00.

Down the road you have to deal how to represent data for item on sale or on discount price, so please keep reading Kimball book to find answers.

zoom

Posts : 97
Join date : 2010-08-23
Location : Chicago

View user profile

Back to top Go down

Re: Creating facts and dimensions from raw data

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