How can i write a dimension with snow flake schema ? . Details below

Question

There are three tables -

1) Student - My fact Table (References Addresses with FK ADDRESS_ID

2) Addresses - This table Contains FK COUNTRY_ID references COUNTRY

3) Country - this has a NAME COLUMN which i would display. (PK IDENTIFIER)

I have written this but Not Sure if it is Correct

Basically i want to join Student (FACT TABLE) to COUNTRY Consosts of Fact --- Def .. then this

<Dimension foreignKey="ADDRESS_ID" name="COUNTRY">
  <Hierarchy name="COUNTRY NAME" hasAll="true" primaryKey="IDENTIFIER" primaryKeyTable="ADDRESSES">
    <Join leftKey="IDENTIFIER" rightKey="IDENTIFIER">
      <Table name="ADDRESSES" >
      </Table>
      <Table name="COUNTRIES" >
      </Table>
    </Join>
    <Level name="Country Name" visible="true" table="COUNTRIES" column="NAME" nameColumn="NAME" uniqueMembers="false">
    </Level>
  </Hierarchy>
</Dimension>

score 0 · Answer 1 · answered Nov 28 '12 at 06:40

You haven't made the join correctly. I'm going to assume your relational schema looks like this:

.-STUDENT-------.  
| IDENTIFIER PK |    .-ADDRESSES-----.
| ADDRESS_ID FK |----| IDENTIFIER PK |    .-COUNTRIES-----.
                     | COUNTRY_ID FK |----| IDENTIFIER PK |
                                          | NAME          |

The JOIN element needs to make a join between the lowest-granularity dimension table ADDRESS and the next level up (COUNTRY). The keys that join these tables are COUNTRY_ID (for ADDRESS) and IDENTIFIER (COUNTRY). leftKey then needs to be set to COUNTRY_ID.

Also, you're using the Level's nameColumn attribute, but this is actually used to set the level's own name, not the member names. I would remove this. In all you'd en up with something looking like this:

<Dimension foreignKey="ADDRESS_ID" name="COUNTRY">
  <Hierarchy name="COUNTRY NAME" hasAll="true" primaryKey="IDENTIFIER" primaryKeyTable="ADDRESSES">
    <Join leftKey="COUNTRY_ID" rightKey="IDENTIFIER">
      <Table name="ADDRESSES" >
      </Table>
      <Table name="COUNTRIES" >
      </Table>
    </Join>
    <Level name="Country Name" visible="true" table="COUNTRIES" column="NAME">
    </Level>
  </Hierarchy>
</Dimension>

How can i write a dimension with snow flake schema ? . Details below

1 Answers1