Discussion: How to determine a Fact Table algorithmically

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Girish (gbajaj_at_tietronixinc.com)
Date: 03/03/04

  • Next message: Vikrant V Dalwale [MSFT]: "RE: Discussion: How to determine a Fact Table algorithmically" 
    Date: Wed, 3 Mar 2004 16:35:37 -0600

    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: Vikrant V Dalwale [MSFT]: "RE: Discussion: How to determine a Fact Table algorithmically"

    Relevant Pages

    • Discussion: How to determine a Fact Table algorithmically
      ... of application domain expert to identify the fact tables in a relational ... database so that you can eventually build a cube around it. ... new one-click cube feature - It seems pretty magical that you can ... Can anyone take a crack at this algorithm? ...
      (microsoft.public.sqlserver.olap)
    • Re: BulkCopy or Insert ignoring Primary key
      ... I admit your problem is not entirely clear to me. ... Of course you could write an algorithm that would insert data in the way you describe, but that would probably not be very performant. ... It seems to me you are asking this question in relation to a particular feature that the database backend you are using has, but then you don't mention what database that is or what feature you're talking about. ...
      (microsoft.public.dotnet.framework)
    • Import Errors Table Messages
      ... I am importing ... Unless I can turn this feature off, ... end up populating my database with error tables. ... I dont' ...
      (microsoft.public.access.externaldata)
    • Re: General Opinion on a how to?
      ... You could store the name of the algorithm in the database along with some ... Load into a prize pool object instance for that game ...
      (microsoft.public.dotnet.languages.csharp)
    • Re: out of memory
      ... read only the smaller file into a hash. ... the smaller file will fit into RAM. ... Depending upon the sorting algorithm this would be Ologor ... put your relevant data into a database and use ...
      (comp.lang.perl.misc)