Loading ...
Sorry, an error occurred while loading the content.

RE: LDM Modeling Question

Expand Messages
  • Logan, Patrick D
    ... I find representing many-many relationships as star schemas simple. Star schemas implement a 2nd normal form. You can normalize the dimensions (in this
    Message 1 of 5 , Feb 1, 2005
    • 0 Attachment
      >What is the best way to represent the following situation in a
      >logical data model...

      I find representing many-many relationships as star schemas simple. Star
      schemas implement a 2nd normal form. You can normalize the "dimensions"
      (in this case employee, division hierarchy, benefit package, and
      location are the obvious dimensions. Others would include the start and
      end date dimensions of the specific benefits package.)

      The fact table, which is always fully normalized would contain a count
      as the fact. For example, each employee's benefit fact would have a
      count of 1. Then to count the number of employees with a specific
      benefit package, select for that package and count the related facts. To
      get this count for a specific division and/or location just include the
      additional constraints in the select statement.

      See http://ralphkimball.org for a lot more detail on star schemas if
      they are unfamiliar.

      The other aspect of the question is how does the model illustrate the
      constraints between certain packages, divisions, locations, etc. This
      could be done using the same dimension tables. I would put this
      information in a fact table separate from the employee/package counting
      table. Call this other fact table something like "allowable benefit
      packages" each fact has a count of 1 indicating an allowable
      combination. The dimensions would be the benefits package, the division,
      the location, and the start and end date dimensions the package is
      available for that particular combination.

      To count the number of divisions that offer a specific package, count
      this fact constrained to the desired division. Etc.

      -Patrick
    • amy_vrahas_hall
      Thanks for the suggestions. -Amy ... simple. Star ... the dimensions ... start and ... count ... facts. To ... include the ... if ... the ... This ...
      Message 2 of 5 , Feb 1, 2005
      • 0 Attachment
        Thanks for the suggestions.

        -Amy

        --- In agileDatabases@yahoogroups.com, "Logan, Patrick D"
        <patrick.d.logan@i...> wrote:
        > >What is the best way to represent the following situation in a
        > >logical data model...
        >
        > I find representing many-many relationships as star schemas
        simple. Star
        > schemas implement a 2nd normal form. You can normalize
        the "dimensions"
        > (in this case employee, division hierarchy, benefit package, and
        > location are the obvious dimensions. Others would include the
        start and
        > end date dimensions of the specific benefits package.)
        >
        > The fact table, which is always fully normalized would contain a
        count
        > as the fact. For example, each employee's benefit fact would have a
        > count of 1. Then to count the number of employees with a specific
        > benefit package, select for that package and count the related
        facts. To
        > get this count for a specific division and/or location just
        include the
        > additional constraints in the select statement.
        >
        > See http://ralphkimball.org for a lot more detail on star schemas
        if
        > they are unfamiliar.
        >
        > The other aspect of the question is how does the model illustrate
        the
        > constraints between certain packages, divisions, locations, etc.
        This
        > could be done using the same dimension tables. I would put this
        > information in a fact table separate from the employee/package
        counting
        > table. Call this other fact table something like "allowable benefit
        > packages" each fact has a count of 1 indicating an allowable
        > combination. The dimensions would be the benefits package, the
        division,
        > the location, and the start and end date dimensions the package is
        > available for that particular combination.
        >
        > To count the number of divisions that offer a specific package,
        count
        > this fact constrained to the desired division. Etc.
        >
        > -Patrick
      • richard.hansen@thomson.com
        The link http://ralphkimball.org does not work.
        Message 3 of 5 , Feb 1, 2005
        • 0 Attachment
          The link http://ralphkimball.org does not work.

          > -----Original Message-----
          > From: Logan, Patrick D [mailto:patrick.d.logan@...]
          > Sent: Tuesday, February 01, 2005 4:16 AM
          > To: agileDatabases@yahoogroups.com
          > Subject: [agileDatabases] RE: LDM Modeling Question
          >
          >
          >
          > See http://ralphkimball.org for a lot more detail on star schemas if
          > they are unfamiliar.
        • Bayley, Alistair
          Google is your friend... Although I m not sure specifically which articles Patrick was referring to:: http://www.rkimball.com/html/designtips.html ...
          Message 4 of 5 , Feb 1, 2005
          • 0 Attachment
            Google is your friend...

            Although I'm not sure specifically which articles Patrick was referring to::
            http://www.rkimball.com/html/designtips.html


            > -----Original Message-----
            > From: richard.hansen@... [mailto:richard.hansen@...]
            > Sent: 01 February 2005 13:47
            > To: agileDatabases@yahoogroups.com
            > Subject: RE: [agileDatabases] RE: LDM Modeling Question
            >
            >
            > The link http://ralphkimball.org does not work.
            >
            > > -----Original Message-----
            > > From: Logan, Patrick D [mailto:patrick.d.logan@...]
            > > Sent: Tuesday, February 01, 2005 4:16 AM
            > > To: agileDatabases@yahoogroups.com
            > > Subject: [agileDatabases] RE: LDM Modeling Question
            > >
            > >
            > >
            > > See http://ralphkimball.org for a lot more detail on star schemas if
            > > they are unfamiliar.

            -----------------------------------------
            *****************************************************************
            Confidentiality Note: The information contained in this message, and any
            attachments, may contain confidential and/or privileged material. It is
            intended solely for the person(s) or entity to which it is addressed. Any
            review, retransmission, dissemination, or taking of any action in
            reliance upon this information by persons or entities other than the
            intended recipient(s) is prohibited. If you received this in error, please
            contact the sender and delete the material from any computer.
            *****************************************************************
          Your message has been successfully submitted and would be delivered to recipients shortly.