Discussion: How to determine a Fact Table algorithmically

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,
girishHello 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 computers 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
>
>
>sql