Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2009
    Posts
    7

    Unanswered: Schema design issue

    I am very new in data modelling. Sorry in advance if it is a silly question.

    I have to design an star schema. I have one table "A" that has its PK and some attributes.
    Another "B" table has it's own PK and some attributes in it, but it also has one Parent Key, this Parent key actually an equivalent of PK of Table "A".

    I have to join table A as a dimension with Fact table. I am confused where to join Table B with? Either with Table A as Parent/child relationship? or with fact table?

    I want to do reporting on both of them. They don't have any measures in them.
    Please advice what should I do.
    Thanks

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Let's first ask you to post the DDL of all the tables involved

    And then let's summon up the spirit of the blind dude

    That would probably happen around 8 or 9 EST
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Oct 2009
    Posts
    7
    please see the attached file
    Attached Files Attached Files

  4. #4
    Join Date
    Aug 2009
    Posts
    262
    Assalam o Aliykum ..


    first tell me are you a student or a pro . ?

  5. #5
    Join Date
    Aug 2009
    Posts
    262
    All i see are 3 tables .. with total of 4 pk .
    Count (int)
    Max
    Min
    Avg

    what are these count max, min , avg columns for ? the hold what values

  6. #6
    Join Date
    Oct 2009
    Posts
    7
    Quote Originally Posted by mishaalsy
    All i see are 3 tables .. with total of 4 pk .
    Count (int)
    Max
    Min
    Avg

    what are these count max, min , avg columns for ? the hold what values
    Wallaikum Assalam
    I am not an student but a new to this field.
    I have to model a structure like Star Schema.
    This is something to analyze the performance of the systems and the crystal reports running.
    How many times a report (and/or its instances) was viewed in a day or a week or a month and so.
    What is the average and min.and max time a report (and/or its instances)
    was viewed in a day or so.

    Report information is in Table A and the report instances information is in Table B.
    Table A and Table B are in parent/child relationship, but both of them have their own Primary Keys (PK).

    Table B has one ParentID field that corresponds to PK of Table A.
    There are other tables also involved like security, users and usergroups. I have to make all of them dimensions but I leaving that piece for later.
    Thanks

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Echo


    Quote Originally Posted by Brett Kaiser
    Let's first ask you to post the DDL of all the tables involved

    Explain to us, your understanding of what a star schema is
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Oct 2009
    Posts
    7
    Quote Originally Posted by Brett Kaiser
    Echo





    Explain to us, your understanding of what a star schema is
    Fact table has numeric fields in it alongwith fk of all the dimension tables that surrounds it. Dimensions are joined with the fact with one(dim side) to many(fact side) relationship.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Future coming attachments

    Originaly in Word Document Table form (Yuck)

    Code:
    Table A		Table B		Fact Table
    A_ID (PK)	B_ID  (PK)	A_ID (PK)
    NAME 	    	NAME 	    	B_ID  (PK)
    PARENTID 	STARTTIME 	Count (int)
    LAST_RUN_TIME 	ENDTIME 	Max
    MACHINE_USED  	MACHINE_USED  	Min
    SUBMITTER 	SUBMITTER 	Avg
    PROGID 	    	PROGID
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Oct 2009
    Posts
    7
    Quote Originally Posted by Brett Kaiser
    Future coming attachments

    Originaly in Word Document Table form (Yuck)

    Code:
    Table A		Table B		Fact Table
    A_ID (PK)	B_ID  (PK)	A_ID (PK)
    NAME 	    	NAME 	    	B_ID  (PK)
    PARENTID 	STARTTIME 	Count (int)
    LAST_RUN_TIME 	ENDTIME 	Max
    MACHINE_USED  	MACHINE_USED  	Min
    SUBMITTER 	SUBMITTER 	Avg
    PROGID 	    	PROGID
    Thanks Brett. So there is nothing wrong if I join parent table and the child table directly to the fact table? Will not they be on the same level then?
    This is my confusion. please correct me.
    Thanks

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Brett Kaiser
    Let's first ask you to post the DDL of all the tables involved

    And then let's summon up the spirit of the blind dude

    That would probably happen around 8 or 9 EST
    Who awakes me from my slumber?

    But really, I avoid nonsense like star schemas as much as I can....
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •