Re: Discussion: How to determine a Fact Table algorithmically

• Previous message: Michael Vardinghus: "Re: Default month"
• In reply to: Girish: "Discussion: How to determine a Fact Table algorithmically"
• Next in thread: Girish: "Re: Discussion: How to determine a Fact Table algorithmically"
• Reply: Girish: "Re: Discussion: How to determine a Fact Table algorithmically"
• Messages sorted by: [ date ] [ thread ]

Date: Fri, 5 Mar 2004 12:52:12 -0800

Yukon Analysis Services has such automatic detection of fact and dimension
tables already.
More information here:
http://www.microsoft.com/technet/prodtechnol/sql/yukon/evaluate/dwsqlsy.mspx#XSLTsection127121120120
(Under Create Dimensions and Cubes, discussion about IntelliCube).

-- 
==================================================
Mosha Pasumansky  - http://www.mosha.com/msolap
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==================================================
"Girish" <gbajaj@tietronixinc.com> wrote in message
news:e6q4AENAEHA.2180@TK2MSFTNGP09.phx.gbl...
> 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
>
>

• Previous message: Michael Vardinghus: "Re: Default month"
• In reply to: Girish: "Discussion: How to determine a Fact Table algorithmically"
• Next in thread: Girish: "Re: Discussion: How to determine a Fact Table algorithmically"
• Reply: Girish: "Re: Discussion: How to determine a Fact Table algorithmically"
• Messages sorted by: [ date ] [ thread ]