Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Creating dimension from Fact table. Expand / Collapse
Author
Message
Posted Tuesday, July 17, 2012 1:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 28, 2013 1:34 AM
Points: 27, Visits: 157
Hi,

I have 2 tables in my warehouse database one fact table and one dimension table.

1. TransFact
2. TimeDimension

TransFact (Columns)
-----------------------
Id (PK)
TransDate (FK --> TimeDimension.date)
StoreId
StoreName
ItemId
ItemDwescription
Qty
Cost
Price

I have created a cube with these 2 tables.
Here I have to create 2 more dimensions ItemDim and StoreDim. I am verymuch new to the OLAP.

Do I need to create 2 more new tables in warehouse OR can we create dimensions in the cube directly from the fact table.

What is the difference between 'Creating dimension tables in warehosue and then adding in the DSV' and 'Adding diensions from the fact table'?

Please give your suggestions.

Thanks in advance.
Suresh.


Post #1330541
Posted Tuesday, July 17, 2012 1:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 03, 2012 1:30 AM
Points: 4, Visits: 69
Hi,

a few weeks ago I had the same problem.
In my fact table, there was a column "CALMON" (01,02,03,04,.....)

And I asked me the same question, if I had to create a new table in my datahouse or just create the Dimension from the fact table.

After all I just create the DIM from the fact table, I think there are no problems with it.
Post #1330548
Posted Thursday, July 19, 2012 10:18 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 9:46 AM
Points: 132, Visits: 556
If possible, I would suggest creating 2 new dimension tables.

Using the Fact table as a dimension source would mean your using a DISTINCT or GROUP BY query for the dimension definition. This means that every time you want to process your dimension, the SSAS processing will have run that query against your fact table. Depending on how large your fact table gets, it can have some serious performance issues, especially if you try to parallel process both dimensions.

Another risk of doing your dimensions this way is how to setup up the key columns of your dimension. If two records are entered into the fact with the same StoreID but with different StoreNames, the dimension processing could break if you set the StoreID as the only key column.

Breaking out separate dimension tables will allow you to reduce the overall size of your fact table and allows you to build a more intelligent SSAS processing mechanism where you would only have to process the dimensions when there is an INSERT/UPDATE to the dimension data.

Post #1332687
Posted Tuesday, July 24, 2012 3:30 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 10, 2014 5:30 PM
Points: 46, Visits: 234
definitely create new dimension table.....

if anything.. it could just be a simple sql that u run every time before cube processing
such as merge dimItem using (select distinct item from fact) on xxx when not matched insert (highly simplified, but you get the idea)

if you fact table ever gets large... the dimension processing would be very inefficient

then you simply add your new table to dsv, then add new cube dimension from it, after cube dimension is created.. then simply go to cube and update the dimension usage

and you are done


Post #1334809
Posted Friday, February 15, 2013 10:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 30, 2013 6:41 PM
Points: 1, Visits: 25
Hi,

I'm running into a similar situation. Can you please provide some direction/example of the methods to create a DIMENSION from the column in the FACT table? E.g. I'm having a patient discharges FACT table which has 'Age' as a column. I want to create a dimension on this column without having to create a separate look up table for it as a dimension.

Thanks,
VK
Post #1420668
Posted Monday, February 25, 2013 3:03 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 9:59 AM
Points: 692, Visits: 754
VeeKay (2/15/2013)
Hi,

I'm running into a similar situation. Can you please provide some direction/example of the methods to create a DIMENSION from the column in the FACT table? E.g. I'm having a patient discharges FACT table which has 'Age' as a column. I want to create a dimension on this column without having to create a separate look up table for it as a dimension.

Thanks,
VK


What I did ( and I have done the patient age dimension) is I created a table with one row for each year (say 0 - ??) . Then I had a column for 5 Year age bands , 10 year age bands, and a column for invalid age (Our data had patients that were older than 150 years so I thought that these people needed to be identified as suspect )

Generally if I have a single fact table that contains multiple dimension then I create a view over the fact table to create views for single dimensions. So create a view for Customer, one for product etc all based on the fact table.

Thiow works well, and is clear to understand and maintain. I use fact dimensions only when the granularity needs to be at the lowest level and try to avoid these like the plague.

E.
Post #1423514
Posted Friday, May 17, 2013 3:34 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 10, 2014 6:20 PM
Points: 220, Visits: 695
Create the dimension tables. What if you want to know what stores generated no sales in some particular time frame?

Post #1454200
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse