Fact table join Dimension table with condition

Hi all,

I am new guy to SQL 2000 AS. Right now I met a problem about how to populate dimension table.

My situation is:

Fact table structure:

website_id, type_ID,Text_id
100 , 10, 3001
100 , 10, 3002
101 , 10, 3001
200 , 10, 3001
200 , 10, 4001
200 , 10, 4002
...

Text_ID is coming from different Dimension table.

Some websites include the following text items,
Dimension Table 1:
Text_ID, Text
3001 , 'How old are you?'
3002 , 'How much do you weigh?'
...

the two websites include the following text items, such as '200'

Dimension Table 2:
Text_ID, Text
3001 , What medication treatment ...?'
3002 , 'Tofu or not tofu'
4001 , 'How old are you?'
4002 , 'How much do you weigh?'
...

Traditionly, I can use query OLTP to get the number of hits of 'How old are you?' and 'How much do you weigh?'.
But, we are planning to move all the web traffic reports to AS, How can I get the number of 'How old are you?' and 'How much do you weigh?' through AS?


Thanks a lot.

What you basically have is a Fact table with no measures. You can achieve what you want by creating a Count measure in your Fact Table.

Raulie

1 suggestion to you is may be you can combine the dimension table 1 and dimension table to in 1 dimension table with this design.

websiteid,website,textid,text

With this design when you fact table link with this dimension using 2 link (websiteid and textid).

Besides , at the combined dimension, you can do 2 level with this design

All
--website
---text