[collinsc]

A few questions:

1) We have numerous fact tables with surrogate keys which reference just one dimensional surrogate key. How does this work?

2) Are the ‘facts’ feeding data TO the ‘dimensions’ (back end warehousing)? Or are the ‘Dimensions’ feeding facts to the ‘facts’ tables for lookups!?

Nb: Im very inexperienced at database design.

Im really also using this thread to get contacts for future harder questions!

Thanks kindly

[andrewst]

1) I don't understand the question!

2) In data warehousing (DW), "dimensions" are ways the data is spliced and diced, and "facts" are the data of interest. So for a typical commercial sales DW database there may be dimensions such as "country", "state", "department", "financial year", "quarter", and facts such as "number_of_sales", "sales_value", "profit_amount", "percentage_profit" or whatever. There will typically be a value of each fact for each valid combination of dimensions - e.g. (country:US, state:Texas, department:Books, year:2007, quarter:2, number_of_sales:247).

[collinsc]

thank you for responding.

with regard to 1)
we have employeedimension.employeeskey in dimensions
and we have 3 facts say.
aaaemployee
abcemployee
defemployee

nb- these are fields.

and they ALL reference the employeedimension.employeeskey correct?
how do you diferentiate between aaa, abc and def!?

[andrewst]

Sorry, maybe someone else can help with question 1 - I'm not getting it.

[collinsc]

thansk for looking andrew.

anyone else understand me? i appreciate i may not be explaining myself very well.

thanks

[Pat Phelan]

Ok, I'll take a shot at this. Let's say you have four employees providing transactional data (from your OLTP system/systems): Bob, Carol, Ted, and Alice. They divvy up something like this:

Name	Sex	Dept	Net
Bob	M	101	1000
Carol	F	101	1250
Ted	M	107	2500
Alice	F	111	1600

The dimensions that are obvious to me are gender and department. The results would be:

Sex	Net
F	2850
M	3500

and

Dept	Net
101	2250
107	2500
111	1600

In terms of your fact tables, the employees would still exist, but from a dimensional perspective the individual employees are lost in the agregates.

-PatP

[aschk]

Are you dealing with a particular database system? From my understanding of "Data Warehousing" it's all about utilising data effectively to give the right reports/output (i.e. with meaning), however I think we need to help determine what you actually mean by dimensions and facts.

(imagine that you're talking abuot a big grid of data)
From your descriptions you are implying that your facts are columns, three of which are aaaemployee, abcemployee, and defemployee, however I suspect these are rows (actual data line by line). As for dimensions, i'm not quite sure what you mean.

I picture it a little like this (grid):

TABLE (GRID) NAME : employeedimension
===================================================
COLUMN NAMES => | employeeskey  | sex      | salary
================|==================================
ROW DATA        |  aaaemployee  |  MALE    | 23000
ROW DATA        |  abcemployee  |  FEMALE  | 34231
ROW DATA        |  defemployee  |  MALE    | 23423

[aschk]

AAAAAH, just seen Pat's post. I think I understand what is meant by dimensions now, you mean "meaningful" groupings. Like salary information about the male and female group divisions, or by department.

[Pat Phelan]

Exactly! The example that I posted is grossly simplified, but it gets the important ideas across... While the individual items might still exist in the fact tables, they are lost in the "dimensional perspective".

I like to think of the fact tables as irregular three dimensional shapes, kind of like a cloud in the sky. The database dimensions are like the X, Y, and Z dimensions in space. You can logically "slice" the cloud in my example above by sex or department.

In "real world" conditions, even the fact tables are usually agregates to some extent (the data from the OLTP system gets aggregated into the fact tables), then the dimensions within the datawarehouse (DW) are used to further aggregate the data in the fact tables. This basically allows the DW to aggregate the data and store those aggregates on disk to allow dramatically faster high level queries because the data gets pre-aggregated once instead of being aggregated every time a user requests it.

-PatP

[collinsc]

thanks very much for the responses- i will take a closer look tomorrow

[collinsc]

Thanks- have read through and it has confirmed i kind of know what im talking about..!

cheers