Results 1 to 15 of 15
  1. #1
    Join Date
    May 2005
    Posts
    33

    Unanswered: OLAP Relational Structure

    I am building a data warehousing solution without using analysis services or OLAP or OLTP....

    Dont ask why ... but I have to....


    Ok so which table structure would be better ?


    dimention 1|dimention 2|dimention 3|matrix 1|matrix 2|matrix 3|matrix 4....


    or


    dimention 1|dimention 2|dimention 3|matrix name|matrix value



    I prefer the second one....

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Depends upon the data, but regardless, you are not building a data warehouse. A flat-file yes, a star schema maybe, but a data warehouse no. At best you could consider this a datamart.
    I can't stand the way the concept of a data warehouse has been watered down, and I blame Kimball for most of it.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK

    [climbs up the mountain of enlightenment]

    What is the secret of life...um warehousing

    [/climbs up the mountain of enlightenment]
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Short answer:
    Inmon = Data Warehouses, Kimball = Data Marts

    Kimball has found a succesful and lucrative marketing niche peddling seminars that tout star schemas as data warehouses. Those less in the know buy into this because it is a simple and easily implemented solution, but it falls far short of the capacity of a true data warehouse, and they are inevitably disappointed with the value they get from it.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2005
    Location
    Utah
    Posts
    73
    I have to say, I used star schemas at my last gig because that was what I was taught. At my new employer, you'd be hard pressed to find such, but they handle a much larger amount of data in a more elegant fashion. The question is, do star schemas serve a valuable purpose? From what I saw, they were time consuming to load and a lot of times you could get equal performance from a wisely designed database.
    Dandy
    Aspiring Database Dwarf

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Star schemas are simple to design, and easy to explain/understand. Star Schemas make great Data Marts for just that reason, they are easy to understand.

    Some people confuse Star Schemas with Data Warehouses. This confusion is a common (and often costly) trap for the inexperienced or unwary.

    -PatP

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by tianmingqing
    At my new employer, you'd be hard pressed to find such, but they handle a much larger amount of data in a more elegant fashion.
    care to enlighten us?

    what handles large amounts of data in a data warehouse more elegantly than star schemas?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2005
    Location
    Utah
    Posts
    73
    Essentially, it's a situation where we store all of the detail data, but we have a few tables for reporting purposes that contain pre-aggregated data. For the most part, there aren't traditional "dimension" tables that contain hierarchies of values that are joined to a fact. If the pre-aggregated tables don't contain the information you need, you either join to the detail tables (usually a select from said tables) or, if it's going to be a very common query, alter the structure of the pre-aggregated table to include the values you need (or some aggregation thereof).
    Dandy
    Aspiring Database Dwarf

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ah yes, very familiar with that concept, i call them summary tables

    best thing since sliced bread for improving query performance for data that doesn't have to be up-to-the-second
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Pat Phelan
    Star schemas are simple to design, and easy to explain/understand. Star Schemas make great Data Marts for just that reason, they are easy to understand.

    Some people confuse Star Schemas with Data Warehouses. This confusion is a common (and often costly) trap for the inexperienced or unwary.

    -PatP
    Absolutely, but what ticks me off is that Kimbal is selling his star-schema seminars as "one-size-fits-all" data warehouse designs. Kimball considers a data ware house to be a collection of star schemas. Bad.
    Inmon's strategy of a normalized schema, with pre-aggregated data as necessary, makes a much more robust and useful datasource. Then, smaller data-marts can be spun off the data warehouse (perhaps as star schemas) for faster but more focused OLAP. The key is that the Data Warehouse remains the datasource of record.
    In building a data warehouse, the schema chosen is MUCH less important than the policies, procedures, and best-practices put in place to make sure the schema remains flexible and not tied to any one input or output format.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    why do we have to blame all the ills in our world on one guy?
    If i had to blame one guy it would be DJabarov (with phelan pulling a close second)

    Im gonna put in my opinion NOW.

    Each of these solutions come from a realization that there is a distinct opposition between the two major operations of a DBMS. Data modification vs. Data Retrieval. the summary tables were the next logical extension of the relational (normalaized) database and the datamarts/Warehouses brought that concept to the level of dogma by creating a recognized model for the process. i dont think any of these are necessarily a "bad" or "good" thing just another opportunity to make something work.

    each has its place and blah blah blah. I have seen some DW's that are so awsome, people are silent when in its presence. and i have also seen some DBMS's that rival the same. i hate to cliche at this point but the best tool for the job applies in this case with your preference being the deciding factor.

    by the way <HE WHO IS NOT TO BE NAMED> (another crazy russian) blames it all on kimball as well.
    Last edited by Ruprect; 09-29-05 at 09:54.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i think he's romanian, but listen, you are not supposed to actually use his name, because as you may know, he has nothing better to do than run ego searches all day, and when he finds a thread where he's mentioned, he scours it for the slightest little thing which he can take out of context and post on his site as an example of how everybody's stupider than he is
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...except me, 'cause apparently he and I agree on data warehousing strategies. Gosh darn, the F.P. is actually a really smart guy!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    May 2005
    Posts
    33
    you guys give me the creeps !!!!

    so i should use the analysis services cube then ? instead of this table ?

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, go with your second table, just like you suspected
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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