SQL Server Performance

Fact table join Dimension table with condition

Discussion in 'Analysis Services/Data Warehousing' started by whygh, Apr 30, 2007.

  1. whygh New Member

    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.




  2. satya Moderator

    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.
  3. Raulie New Member

    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



  4. tboonleong Member

    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

Share This Page