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

Discussion about how to create a fact drillthrough dimension the best way..

I have been running into some troubles creating a fact drillthrough dimension. The scenario is as follows:

You have a fact table (in this case with around 50 million rows) holding accounting data. Within that facttable you have a column that holds some information in a free text field (let's say varchar(150) and name "Account_information") that is useful for the business user when they want to do a drillthrough on a cell in the cube.

Now how would you design your cube to be able to drillthrough to that information ?

The solution, the way i see it, is to create fact dimension containing those 50 million rows....

The fact table(Fact_Finance) looks like this

Unique_ID(int), Account_information (varchar(150)), Amount, Column_x....Column_y

In the DSV i then add a view that is a "Select Unique_ID, Account_information from Fact_Finance)

With that view i then create a dimension with

Attribute keycolumn = Unique_ID and

Attribute namecolumn = Account_information

Setting the dimension to processmode ROLAP often results in a timeout in the 3. part front-end tool, so in my case the only option is to use molap for the dimension.

To be able to add the Attribute in the Action i need to add the dimension in the dimension structure, binding the Unique_ID from the dimension to the Fact_Finance table. To me that seems to dramaticly slow down browsing the cube - comments ?

The Molap process of the drillthrough dimension becommes pretty heavy with a select distinct on the varchar column, even with index created though the Data Tuning Wisard.

I hope that someone can comment on their experience with creating a fact drillthrough dimension, since this approach has a big impact on both the processing and browsing of the cube

"To be able to add the Attribute in the Action i need to add the dimension in the dimension structure, binding the Unique_ID from the dimension to the Fact_Finance table. " - are you saying that the fact dimension is configured with a regular relation (not a fact relation) to the measure group? This shouldn't be necessary - for example, the "Reseller Details" action in Adventure Works returns attributes from the fact dimension.

Another option worth trying (if you have Enterprise Edition) is to create a (text) measure with AggregateFunction of "None" on the Account_Information field (rather than configuring it as the name of the fact key attribute). This measure can be included in the drillthorugh action return list.

|||

If I try that i'll get the following error:

Error 1 Errors in the metadata manager. The data type of the 'Account_Description' measure is not valid because the data type of the measure is a string type.

AggregationType set to None

Datatype is WChar

I'm trying to run in the Developer edition.

|||

Looks like you're right - when I set up a test scenario, I got the same error as well. This approach was suggested in an earlier thread here:

the attributes of Fact Table could not be displayed when drill through?

>>

...

Sure you have got a huge dimension.
But you shouldn't create dimension, you can make text measure.

...

You can also create a non aggregated measure and then use that when drilling through.
>>

So scratch that - here's another thread on a similar subject, with an approach from (ahem) a more authoritative source:

String measures in Analysis services cube

>>

...

The usual workaround in both AS2000 and AS2005 for such requirement is to create hidden dimension TextDescriptions with integer key and string name, i.e. you will have dimension table as

Key Name

1 Optimal

2 Needs Improvement

3 Failed

...

You will also have hidden measure DescriptionKey in the cube.

Then you use integers as a measure, and create calculation for it, like following:

CREATE MEMBER CURRENTCUBE.Measures.Description AS

' IIF(DescriptionKey = 0, "",

Filter(TextDescriptions.MEMBERS, Val(TextDescriptions.CurrentMember.Properties("Key"))=Measures.DescriptionKey).Item(0).Item(0).Name)

---

Mosha - http://www.mosha.com/msolap
>>

Another approach which I've used recently is to add a text attribute to the fact table, with its attribute hierarchy disabled. But in this case, since the client tool is Excel 2003, users don't access this text via drillthrough - instead, the fact dimension is directly added (after appropriate slices have been set, of course) on the rows of a pivot table. The text is then dropped in the data area, using a calculated measure which references the Member Property for the text. Excel 2003 allows a Member Property to be added to a pivot table as well, but I found this to be slower and less obvious than a calculated text measure.

The Analysis Services 2005 Performance Guide discusses when to disable an attribute hierarchy:

...

Reducing attribute overhead

...

Turn off the attribute hierarchy and use member properties

As an alternative to attribute hierarchies, member properties provide a different mechanism to expose dimension information. For a given attribute, member properties are automatically created for every attribute relationship. For the primary key attribute, this means that every attribute that is directly related to the primary key is available as a member property of the primary key attribute.

If you only want to access an attribute as member property, once you verify that the correct relationship is in place, you can disable the attribute’s hierarchy by setting the AttributeHierarchyEnabled property to False. From a processing perspective, disabling the attribute hierarchy can improve performance and decrease cube size because the attribute will no longer be indexed or aggregated. This can be especially useful for high cardinality attributes that have a one-to-one relationship with the primary key. High cardinality attributes such as phone numbers and addresses typically do not require slice-and-dice analysis. By disabling the hierarchies for these attributes and accessing them via member properties, you can save processing time and reduce cube size.

Deciding whether to disable the attribute’s hierarchy requires that you consider both the querying and processing impacts of using member properties. Member properties cannot be placed on a query axis in the same manner as attribute hierarchies and user hierarchies. To query a member property, you must query the properties of the attribute that contains the member property. For example, if you require the work phone number for a customer, you must query the properties of customer. As a convenience, most front-end tools easily display member properties in their user interfaces.

...

Discussion about how to create a fact drillthrough dimension the best way..

I have been running into some troubles creating a fact drillthrough dimension. The scenario is as follows:

You have a fact table (in this case with around 50 million rows) holding accounting data. Within that facttable you have a column that holds some information in a free text field (let's say varchar(150) and name "Account_information") that is useful for the business user when they want to do a drillthrough on a cell in the cube.

Now how would you design your cube to be able to drillthrough to that information ?

The solution, the way i see it, is to create fact dimension containing those 50 million rows....

The fact table(Fact_Finance) looks like this

Unique_ID(int), Account_information (varchar(150)), Amount, Column_x....Column_y

In the DSV i then add a view that is a "Select Unique_ID, Account_information from Fact_Finance)

With that view i then create a dimension with

Attribute keycolumn = Unique_ID and

Attribute namecolumn = Account_information

Setting the dimension to processmode ROLAP often results in a timeout in the 3. part front-end tool, so in my case the only option is to use molap for the dimension.

To be able to add the Attribute in the Action i need to add the dimension in the dimension structure, binding the Unique_ID from the dimension to the Fact_Finance table. To me that seems to dramaticly slow down browsing the cube - comments ?

The Molap process of the drillthrough dimension becommes pretty heavy with a select distinct on the varchar column, even with index created though the Data Tuning Wisard.

I hope that someone can comment on their experience with creating a fact drillthrough dimension, since this approach has a big impact on both the processing and browsing of the cube

"To be able to add the Attribute in the Action i need to add the dimension in the dimension structure, binding the Unique_ID from the dimension to the Fact_Finance table. " - are you saying that the fact dimension is configured with a regular relation (not a fact relation) to the measure group? This shouldn't be necessary - for example, the "Reseller Details" action in Adventure Works returns attributes from the fact dimension.

Another option worth trying (if you have Enterprise Edition) is to create a (text) measure with AggregateFunction of "None" on the Account_Information field (rather than configuring it as the name of the fact key attribute). This measure can be included in the drillthorugh action return list.

|||

If I try that i'll get the following error:

Error 1 Errors in the metadata manager. The data type of the 'Account_Description' measure is not valid because the data type of the measure is a string type.

AggregationType set to None

Datatype is WChar

I'm trying to run in the Developer edition.

|||

Looks like you're right - when I set up a test scenario, I got the same error as well. This approach was suggested in an earlier thread here:

the attributes of Fact Table could not be displayed when drill through?

>>

...

Sure you have got a huge dimension.
But you shouldn't create dimension, you can make text measure.

...

You can also create a non aggregated measure and then use that when drilling through.
>>

So scratch that - here's another thread on a similar subject, with an approach from (ahem) a more authoritative source:

String measures in Analysis services cube

>>

...

The usual workaround in both AS2000 and AS2005 for such requirement is to create hidden dimension TextDescriptions with integer key and string name, i.e. you will have dimension table as

Key Name

1 Optimal

2 Needs Improvement

3 Failed

...

You will also have hidden measure DescriptionKey in the cube.

Then you use integers as a measure, and create calculation for it, like following:

CREATE MEMBER CURRENTCUBE.Measures.Description AS

' IIF(DescriptionKey = 0, "",

Filter(TextDescriptions.MEMBERS, Val(TextDescriptions.CurrentMember.Properties("Key"))=Measures.DescriptionKey).Item(0).Item(0).Name)

---

Mosha - http://www.mosha.com/msolap
>>

Another approach which I've used recently is to add a text attribute to the fact table, with its attribute hierarchy disabled. But in this case, since the client tool is Excel 2003, users don't access this text via drillthrough - instead, the fact dimension is directly added (after appropriate slices have been set, of course) on the rows of a pivot table. The text is then dropped in the data area, using a calculated measure which references the Member Property for the text. Excel 2003 allows a Member Property to be added to a pivot table as well, but I found this to be slower and less obvious than a calculated text measure.

The Analysis Services 2005 Performance Guide discusses when to disable an attribute hierarchy:

...

Reducing attribute overhead

...

Turn off the attribute hierarchy and use member properties

As an alternative to attribute hierarchies, member properties provide a different mechanism to expose dimension information. For a given attribute, member properties are automatically created for every attribute relationship. For the primary key attribute, this means that every attribute that is directly related to the primary key is available as a member property of the primary key attribute.

If you only want to access an attribute as member property, once you verify that the correct relationship is in place, you can disable the attribute’s hierarchy by setting the AttributeHierarchyEnabled property to False. From a processing perspective, disabling the attribute hierarchy can improve performance and decrease cube size because the attribute will no longer be indexed or aggregated. This can be especially useful for high cardinality attributes that have a one-to-one relationship with the primary key. High cardinality attributes such as phone numbers and addresses typically do not require slice-and-dice analysis. By disabling the hierarchies for these attributes and accessing them via member properties, you can save processing time and reduce cube size.

Deciding whether to disable the attribute’s hierarchy requires that you consider both the querying and processing impacts of using member properties. Member properties cannot be placed on a query axis in the same manner as attribute hierarchies and user hierarchies. To query a member property, you must query the properties of the attribute that contains the member property. For example, if you require the work phone number for a customer, you must query the properties of customer. As a convenience, most front-end tools easily display member properties in their user interfaces.

...

Disappearing Measures?

Not sure what I could have done to make this happen ... but one of my simple measures (simply a row count of my fact table) no longer shows a value EXCEPT when I use it with one of my multi-level hierarchies I defined.

Has anyone seen this?

Is there a way to fix it?

I really have no idea what I coulda done. I've even commented out my calculated members and removed any non-regular dimensions and it still ain't working.

Thanks - WaydeDoes your calculations script contain a CALCULATE command? Without this no aggregation will be done and only the leaf cells will contain data.