Creating dimension from Fact table.

Question

Post reply

Creating dimension from Fact table.

suresh.muppidi

Right there with Babe

Points: 761
More actions
July 17, 2012 at 1:00 am

#277869

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.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

tomtom2434 Valued Member Points: 64 More actions · Answer 1

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 SSCommitted Points: 1502 More actions · Answer 2

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 Ten Centuries Points: 1374 More actions · Answer 3

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 SSC Rookie Points: 47 More actions · Answer 4

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 SSCrazy Eights Points: 9901 More actions · Answer 5

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 One Orange Chip Points: 27795 More actions · Answer 6

Create the dimension tables. What if you want to know what stores generated no sales in some particular time frame?

----------------------------------------------------