I am designing the persistence strategy for a data mart which consists of two facts tables and five dimension tables. This is based on the classic star schema design.

Each fact table contains a composite primary key comprised of each dimension table's single primary key. The elements of the fact table primary key is also a foreign key to each respective dimension table.

Initially, I was thinking that this would be a "one-to-many" relationship(one dimension to many facts). The problem I am having is, seeing that I have to persist the fact data after each dimension record is created because of the primary key dependencies, how is this accomplished in the mapping?

Any help is appreciated! DDL is listed below.

Mark

eg:
DDL for one of the facts tables:
create table SalesFact
{
CompanyId INTEGER NOT NULL,
TerritoryId INTEGER NOT NULL,
SalesPersonId INTEGER NOT NULL,
ProductId INTEGER NOT NULL,
TimeId INTEGER NOT NULL,
UnitsSold INTEGER,
SaleAmt DECIMAL(10,2)
PRIMARY KEY (CompanyId, TerritoryId, SalesPersonId,
ProductId, TimeId),
FOREIGN KEY (CompanyId) REFERENCES CompanyDimension(CompanyId) ON DELETE RESTRICT,
FOREIGN KEY (TerritoryId) REFERENCES TerritoryDimension(TerritoryId) ON DELETE RESTRICT,
FOREIGN KEY (SalesPersonId) REFERENCES SalesDimension(SalesPersonId) ON DELETE RESTRICT,
FOREIGN KEY (ProductId) REFERENCES ProductDimension(ProductId) ON DELETE RESTRICT,
FOREIGN KEY (TimeId) REFERENCES TimeDimension(TimeId) ON DELETE RESTRICT,

}

DDL for the five dimension tables:
create table CompanyDimension
{
CompanyId INTEGER NOT NULL PRIMARY KEY,
Name VARCHAR(50),
Type VARCHAR(20),
ActiveCode TINYINT
}

create table TerritoryDimension
{
RegionId INTEGER NOT NULL PRIMARY KEY,
Name VARCHAR(50),
ActiveCode TINYINT
}

create table SalesDimension
{
SalesPersonId INTEGER NOT NULL PRIMARY KEY,
Name VARCHAR(50),
Level TINYINT,
DateActive DATETIME,
ActiveCode TINYINT
}

create table ProductDimension
{
ProductId INTEGER NOT NULL PRIMARY KEY,
Name VARCHAR(50),
Category TINYINT,
SubCategory TINYINT
}

create table TimeDimension
{
TimeId INTEGER NOT NULL PRIMARY KEY,
DayOfWeek DATETIME,
DayOfMonth DATETIME,
DayOfYear DATETIME,
Month DATETIME,
Quarter DATETIME,
Year DATETIME,
Holiday TINYINT,
Weekend TINYINT
}

I believe this may involve some form of polymorphic persistence mapping, possibly including the use of a one-to-any structure, but I am very new to Hibernate, esp 3.0, and would appreciate some guidance.

Thanks!

Mark

one-to-many is the same as many-to-one the other way round:



http://www.hibernate.org/hib_docs/v3/reference/en/html/mapping.html#mapping-declaration-manytoone

Mickey:

Got that part ok, but I am at a loss as to how to handle the persistence of the fact table, seeing that it's composite primary key values depend on the dimemsion table primarry key values. In other words, the fact table would have to be the last thing persisted.

How can this be mapped?

Mark

Just save() the Dimension objects, then the Fact object? Hibernate does the right order automatically.

Would it simply be a bi-directional one-to-many relationship, using a collection or something similar? Or would it have to be more complex, like a one-to-any type of structure?

Could you post some sample mappings or point me to a link where something similar has been done?

Thanks,
Mark

I think you want <key-many-to-one/> within the <composite-id/>:

http://www.hibernate.org/hib_docs/v3/reference/en/html/mapping.html#mapping-declaration-compositeid

I will check out that link and get back to you w/any questions this weekend, thanks so much!

Mark

After reviewing the link above, I think I have a grasp on the basics. Sounds like I could map this similar to the following:

<hibernate-mapping default-cascade="none" default-access="property" default- lazy="true" auto-import="true">
<class name="ami.server.dto.datamart.billing.SongAggregationFactsDTO" table="billing_data_mart_db.SongAggregationFacts" mutable="true" abstract="false" polymorphism="implicit" dynamic-update="false" dynamic-insert="false" select-before-update="false" optimistic-lock="version">
<composite_id>
<key-many-to-one name=”CompanyId” column=”company_id”/>
<key-many-to-one name=”ContractId” column=”contract_id”/>
<key-many-to-one name=”JukeboxId” column=”jukebox_id”/>
<key-many-to-one name=”SongId” column=”song_id”/>
<key-many-to-one name=”TimeId” column=”time_id/”>
</composite_id>
<property name= . . ./>
</class>

However, I am a bit concerned about the following clause in the section referrred to by the attached link; "Unfortunately, this approach to composite identifiers means that a persistent object is its own identifier. There is no convenient "handle" other than the object itself. You must instantiate an instance of the persistent class itself and populate its identifier properties before you can load() the persistent state associated with a composite key"

It sounds like the approach outlined in Section 9.4 may be a more structured alternative. My question is, does anyone have any suggestions on which way is better? If the "Components as Composite Identifiers" is the best approach, how would that be set up in relation to my schema(A Fact Table w/a composite primary key which depends on all five of the Dimension tables primary key values? How would the "key-many-to-one" be implemented in this case?

Mark

Do it like this:

http://www.hibernate.org/hib_docs/v3/re ... posite-key

But I do not have a many-to-many relationship from my fact to dimention tables, just a many-to-one. So, why would I want to include such things as an association table as mentioned in the example?