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? 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

Yukon Analysis Services has such automatic detection of fact and dimension
tables already.
More information here:
http://www.microsoft.com/technet/pro...on127121120120
(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 (AT) tietronixinc (DOT) com> wrote

Hello Mosha,

Thanks for your reply.

Ive read that article, but Im an academic oriented person - and the article
says little about how the "intelligence" is implemented. That was mainly my
earlier question.

<snip>
The IntelliCube technology examines the database and data cardinality
relationships in the Data Source View, and intelligently characterizes
tables as fact tables, dimension tables, or dimension-to-fact bridge tables
that resolve a many-to-many relationship
</snip>

For my own self understanding - are there any research references you can
supply that the analysis server dev team used to build the IntelliCube
technology? Im really curious to know what the rules are to detect fact
tables since i am deeply involved in datawarehouse modeling/training and to
me, its a really tough job to determine these tables especially without any
domain knowledge! You guys have done a great job.

Any insight is appreciated.
Girish
gbajaj (AT) tietronix (DOT) com

"Mosha Pasumansky [MS]" <moshap (AT) online (DOT) microsoft.com> wrote