Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Creating dimension from Fact table.


Creating dimension from Fact table.

Author
Message
suresh.muppidi
suresh.muppidi
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
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.
tomtom2434
tomtom2434
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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.
richykong
richykong
SSC-Enthusiastic
SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)

Group: General Forum Members
Points: 134 Visits: 621
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.
boxta
boxta
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 238
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
VeeKay
VeeKay
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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
Ells
Ells
Right there with Babe
Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)

Group: General Forum Members
Points: 786 Visits: 931
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.
MMartin1
MMartin1
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1084 Visits: 2013
Create the dimension tables. What if you want to know what stores generated no sales in some particular time frame?

----------------------------------------------------
How to post forum questions to get the best help
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search