SQL Server Performance Forum – Threads Archive
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_id100 , 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.
Not sure how you achieve this but by creating the dimension you can acheive such results, you may check under books online for more information, Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
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
]]>