RE: Discussion: How to determine a Fact Table algorithmically

From: Vikrant V Dalwale [MSFT] (vikrantd_at_online.microsoft.com)
Date: 03/04/04

  • Next message: Mike Kruchten: "Re: 64-bit performance vs 32-bit performance" 
    Date: Thu, 04 Mar 2004 19:12:56 GMT

    Hello Girish,

    A fact table is going to have a couple of characteristics:

    - It is going to contain foreign keys to the dimension tables.
    - It is going to contain measures.

    Usually, Look for a table where the majority of the columns are
    either FKs or numerics.

    I guess, since discussion is more related to the Yukon feature which
    you mentioned, i would suggest to post this question
    to microsoft.beta.yukon.analysisservices.olap ( if you are
    autherised beta user ) where you would get responses from Yukon olap
    experts.

    Thanks for using MSDN Newsgroup.

    Vikrant Dalwale

    Microsoft SQL Server Support Professional

    Microsoft highly recommends to all of our customers that they visit
    the http://www.microsoft.com/protect site and perform the three
    straightforward steps listed to improve your computer’s security.
    This posting is provided "AS IS" with no warranties, and confers no
    rights.

     

    --------------------
    >From: "Girish" <gbajaj@tietronixinc.com>
    >Subject: Discussion: How to determine a Fact Table algorithmically
    >Date: Wed, 3 Mar 2004 16:35:37 -0600
    >Lines: 49
    >Organization: gbajaj@tietronixinc.com
    >X-Priority: 3
    >X-MSMail-Priority: Normal
    >X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
    >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
    >Message-ID: <OagOb$WAEHA.740@TK2MSFTNGP12.phx.gbl>
    >Newsgroups: microsoft.public.sqlserver.datawarehouse
    >NNTP-Posting-Host: adsl-66-139-15-154.dsl.hstntx.swbell.net
    66.139.15.154
    >Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
    >Xref: cpmsftngxa06.phx.gbl
    microsoft.public.sqlserver.datawarehouse:19029
    >X-Tomcat-NG: microsoft.public.sqlserver.datawarehouse
    >
    >
    >I dont know the difference between OLAP and the datawarehouse
    newsgroups,
    >and i really dont know how many ppl frequent both the groups
    together - but
    >Im posting this topic here in hopes that it may start a discussion. I
    >apologies for any inconvenience. Thks. =)
    >
    >---------------------------------------------------------------------
    -------
    >---
    >Everyone knows what fact tables are. Everyone knows that you need
    some kind
    >of application domain expert to identify the fact tables in a
    relational
    >database so that you can eventually build a cube around it. A domain
    expert
    >would also probably identify the dimension tables as well as any
    measures.
    >
    >Anyways, so heres my question - and it came up after looking at sql
    yukons
    >new one-click cube feature - It seems (to me) pretty magical that
    you can
    >algorithmically determine what a fact table is and its corresponding
    >dimension tables are by just analyzing the database schema (note the
    word
    >schema and not data!). In order to unveil the magic (cause I know
    its really
    >not! :-) ), I have taken it up upon myself to determine what this
    algorithm
    >could be.
    >
    >Can anyone take a crack at this algorithm? Heres my initial attempt
    - but
    >its not good enough.
    >
    >POTENTIAL FACT TABLES
    >--------------------------------
    >1) Locate all Tables in database (and lets just stick with one db
    for now)
    >that have a foreign key constraint on them.
    >2) Locate all Tables that are alone and not connected to any other
    tables.
    >
    >My simple algo for now gets more fact tables than yukons one-click
    cube
    >feature. I did this test with the northwind database. If you want -
    I can
    >post what fact and dimension tables yukon analysis server came up
    with.
    >
    >Should be a interesting and thought provoking discussion dont you
    think? :-)
    >Unless the answer is so bloody simple that I cant see it cause im
    daft. But
    >I think not - cause if it were simple - wouldnt MS have included this
    >feature earlier on? :D And then also think about all the bad database
    >designs out there that you would probably need to consider as well
    right! To
    >add another twist - what about fact tables that are horizontally or
    >vertically partitioned? Can an algorithm detect this as well? Or is
    manual
    >the only way this can be done?
    >
    >thanks,
    >girish
    >
    >
    >
    >
    >

  • Next message: Mike Kruchten: "Re: 64-bit performance vs 32-bit performance"

    Relevant Pages

    • Re: How do I give ASP.NET process network credentials?
      ... >> Source Safe database is not a database in the traditional sense of the ... >> I have written an ASP.NET process (web services) which interacts with ... >> VSS API in order to access the VSS database on dimension. ...
      (microsoft.public.dotnet.framework.aspnet.security)
    • Re: How do I give ASP.NET process network credentials?
      ... What implications does asigning this user as a domain user have? ... >> Source Safe database is not a database in the traditional sense of the ... >> VSS API in order to access the VSS database on dimension. ...
      (microsoft.public.dotnet.framework.aspnet.security)
    • Re: Can you update/process AS2005 cube with AMO and SQL authentication
      ... the database instead of a secured access. ... Authentication while its required to access the AS server! ... ' Errors in the OLAP storage engine: ... the dimension, with the ID of 'Product', Name of 'Product' was being ...
      (microsoft.public.sqlserver.olap)
    • Re: Nearest neighbours
      ... All-nearest-neighbors algorithm ... My timing results indicate that the kd-tree outperforms all methods by ... dimension 4 brute force outperforms this algorithm. ...
      (comp.graphics.algorithms)
    • Re: Delete in a dimension table is very long...
      ... Then create a dimension off the view instead of the table. ... Also if you have logging enabled for the database that serves ... If your data warehouse is updated infrequently, you can set database loggin ...
      (microsoft.public.sqlserver.datawarehouse)