Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2007
    Posts
    11

    Understanding Dimension And Fact

    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

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    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).

  3. #3
    Join Date
    Aug 2007
    Posts
    11
    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!?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Sorry, maybe someone else can help with question 1 - I'm not getting it.

  5. #5
    Join Date
    Aug 2007
    Posts
    11
    thansk for looking andrew.

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

    thanks

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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:
    Code:
    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:
    Code:
    Sex	Net
    F	2850
    M	3500
    and
    Code:
    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

  7. #7
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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):
    Code:
    TABLE (GRID) NAME : employeedimension
    ===================================================
    COLUMN NAMES => | employeeskey  | sex      | salary
    ================|==================================
    ROW DATA        |  aaaemployee  |  MALE    | 23000
    ROW DATA        |  abcemployee  |  FEMALE  | 34231
    ROW DATA        |  defemployee  |  MALE    | 23423
    Last edited by aschk; 09-12-07 at 12:03.

  8. #8
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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

  10. #10
    Join Date
    Aug 2007
    Posts
    11
    thanks very much for the responses- i will take a closer look tomorrow

  11. #11
    Join Date
    Aug 2007
    Posts
    11
    Thanks- have read through and it has confirmed i kind of know what im talking about..!

    cheers

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •