2

We've got a data warehouse design with four dimension tables and one fact table:

  • dimUser id, email, firstName, lastName
  • dimAddress id, city
  • dimLanguage id, language
  • dimDate id, startDate, endDate
  • factStatistic id, dimUserId, dimAddressId, dimLanguageId, dimDate, loginCount, pageCalledCount

Our problem is: We want to build the fact table which includes calculating the statistics (depending on userId, date range) and filling the foreign keys.

But we don't know how, because we don't understand how to use natural keys (which seems to be the solution to our problem according to the literature we read).

I believe a natural key would be the userId, which is needed in all ETL jobs which calculate the dimension data.

But there are many difficulties:

  • in the ETL jobs load(), we do bulk inserts with INSERT IGNORE INTO to remove duplicates => we don't know the surrogate keys which were generated
  • if we create meta data (including a set of dimension_name, surrogate_key, natural_key) this will not work because of the duplicate elimination

The problem seems to be the duplicate elimination strategy. Is there a better approach?

We are using MySQL 5.1, if it makes any difference.

s.froehlich
  • 863
  • 1
  • 11
  • 19
  • 1
    What is your fact table tracking? Logins by User / Address / Language / "Date Range"? It seems to me that Address and Language are attributes of user? And your date table has ranges? Why not just store individual dates on the fact table and aggregate? – N West Dec 20 '12 at 14:47
  • This is a simplified model of the real design. But it's basically logins and page calls per user (which has an address and a language). loginCount and pageCalledCount is aggregated by date ranges. – s.froehlich Dec 20 '12 at 15:04
  • 1
    I don't really understand your question, but if you're looking for a method to generate and populate surrogate keys then I suggested a fairly generic method using a mapping table in answer to [this question](http://stackoverflow.com/questions/12657674/sql-datawarehousing-need-help-populating-my-dimension-using-tsql-select-or-a-be). Reporting databases generally use artificial keys so I'm not sure why you want to use natural ones; it seems your problem is mainly implementing your ETL process, not your design, although I may be wrong. – Pondlife Dec 20 '12 at 15:32
  • I think I confused the terms natural key and business key. – s.froehlich Dec 21 '12 at 21:25
  • What I'm looking for is an algorithm, which I can use to fill my fact table. I had an approach, in which I'd use a separate mapping table which would look like this: mappingTable - dimensionId - dimensionName - businessKeyValue This data would be created from the ETL jobs which calculate the dimension data and used to fill the fact table(s). But my colleague said, this is a mix of the Inmon and the Kimball strategies (because we partly use single value business keys and concatenated multi value business keys) and would do us no good in terms of expandibility. – s.froehlich Dec 21 '12 at 21:25

2 Answers2

1

If your fact table is tracking logins and page calls per user, then you should have set of source tables which track these things, which is where you'll load your fact table data from. I would probably build the fact table at the grain of one row per user / login date - or even lower to persist atomic data if at all possible.

Here you would then have a fact table with two dimensions - User and Date. You can persist address and language as dimensions on the fact as well, but these are really just attributes of user.

Your dimensions should have surrogate keys, but also should have the source "business" or "natural" key available - either as an attribute on the dimension itself, or through a mapping table as your colleague suggested. It's not "wrong" to use a mapping table - it does make things easier when there are multiple sources.

If you store the business keys on a mapping table, or in the dimension as an attribue, then for each row to load in the fact, it's a simple lookup (usually via a join) against the dim or mapping table to get the surrogate key for the user (and then from the user to get the user's "current" address / language to persist on the fact). The date dimension usually hase a surrogate key stored in a YYYYMMDD or other "natural" format - you can just generate this from the date information on your source record that you're loading into the fact.

N West
  • 6,768
  • 25
  • 40
0

do not force for single query, try to load the data in separated queries and mix the data in some provider...

oxy
  • 1
  • I don't understand. We've already implemented the ETL jobs, which fill the dimension tables. But we don't know how to connect the dimension entries with each other in the fact table. – s.froehlich Dec 20 '12 at 15:15