SQLAgentMail: relationship

Showing posts with label relationship. Show all posts

Tuesday, March 20, 2012

Question on Relationships

Hello,
I'm interested to know whats the difference between a many to one
relationship and a one to many relationship. Presuming 2 tables A and B
A (1 to many) B relationship is different with B (many to 1) A relationship?
I've thought that they're the same but I was watching a BI webcast when they
say that the logical relationship cannot be reversed...
Can someone enlighten this?This is a multi-part message in MIME format.
--090208010402090701010204
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
They are the same relationship, just viewed from opposite perspectives.
I guess by saying the logical relationship cannot be reversed they mean
that if you can get the "1" row from a particular "many" row that does
not imply that you can then get back to the original "many" row just
given that "1" row (because there are now many rows that match up with
that 1 row). Does that make sense? (That's just a guess at what they
were trying to explain (not having any idea what they were saying
exactly) - maybe you could ask the presenter directly what they meant
precisely.)
--
*mike hodgson*
http://sqlnerd.blogspot.com
Nestor wrote:
>Hello,
>I'm interested to know whats the difference between a many to one
>relationship and a one to many relationship. Presuming 2 tables A and B
>A (1 to many) B relationship is different with B (many to 1) A relationship?
>I've thought that they're the same but I was watching a BI webcast when they
>say that the logical relationship cannot be reversed...
>Can someone enlighten this?
>
>
--090208010402090701010204
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>They are the same relationship, just viewed from opposite
perspectives. 
 
I guess by saying the logical relationship cannot be reversed they mean
that if you can get the "1" row from a particular "many" row that does
not imply that you can then get back to the original "many" row just
given that "1" row (because there are now many rows that match up with
that 1 row). Does that make sense? (That's just a guess at what they
were trying to explain (not having any idea what they were saying
exactly) - maybe you could ask the presenter directly what they meant
precisely.)</tt> 
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
<a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
Nestor wrote:
<blockquote cite="mideSQkMooYGHA.4620@.TK2MSFTNGP04.phx.gbl" type="cite">
<pre wrap="">Hello,
I'm interested to know whats the difference between a many to one
relationship and a one to many relationship. Presuming 2 tables A and B
A (1 to many) B relationship is different with B (many to 1) A relationship?
I've thought that they're the same but I was watching a BI webcast when they
say that the logical relationship cannot be reversed...
Can someone enlighten this?
</pre>
</blockquote>
</body>
</html>
--090208010402090701010204--

Question on relationship between Cube and Fact tables?

Hi, all,

Sorry for this title which may be a bit confused. But what I am trying to ask is: Any best practices on fact tables to be included in a cube? Is it better to create a separate cube for each fact table and its related dimension tables or any other ideas to put several fact tables and their related dimension tables into one cube?

Thanks in advance for your kind advices.

With best regards,

Yours sincerely,

Helen,

First Analysis your requirement, is there any clubed information you desired from multiple Fact Tables, How may shared dimensions are among them.

How frequently U require that aggregated [from both Fact Table] information etc.

Simply analysis what can have U benifit when make a cube having multiple fact tables.

Or other way when there is no share facts between to fact table donot make Cube having multiple Fact Tables.

Reference is below:

Cube Design Best Practices

Well-designed cubes will better match the needs of your end-users. The following best practices can improve usability and also performance of the cube.

Avoid including unrelated measure groups in the same cube

Having many measure groups in a cube can adversely affect the query performance of the cube, even for queries that only use a single measure group. If your DSV contains many potential fact tables and they will not usually be used together, consider creating multiple smaller cubes containing topic areas that are used together. Then, you can enable occasional cross-topic querying by creating linked measure groups that join together all measure groups in a special cross-topic cube.

Bhudev

|||

Dear Helen,

Depends on your system requirements.

But probably you only need one cube, and I think it because in the SQL 2005 you can process parts of the cube separately, in spite of all the cube...

For example, one thing I usually do, is in the datasource view, in spite of use a table from database, I use a view based on this table with only the field I will need in the cube. Only in case we have a lot of fields that will not used...

Other thing is used named calculations, or the compute calculation in database...

Regards!

|||

Hi, Bhudeve,

Thanks for you advices.

Yes, as there are shared dimensions for different measure groups and you never know when and what the users would like to see on their side. Thus, to optimally meet the querying needs, always put them together in a cube? But we can always create partitions for different measure groups if we want?

Does it make any sense? Looking forward to hearing from more of your advices.

With best regards,

Yours sincerely,

|||

Hi, PedroCGD,

Thanks for your suggestions.

Are you saying you put all data together in a single cube? And then partition the cube on different measure groups? Thanks a lot and would like to hear more from you for that.

With best regards,

Yours sincerely,

|||

Yes!

And I will try to explain in my blog, how I'm doing in my project!

Each FactTable will be your measure group... with only the measures you need for your cube!

regards!!

|||

Hi, PedroCGD,

Thanks a lot for your kind advices.

With best regards,

Yours sincerely,

|||

Helen,

I'm not against to keep separate Cubes when I do not know the actual requirement. But still there is need to consider all Pros and Cons before completing Design Document for your data Warehouse.

OLAP Design Best Practices for Analysis Services 2005

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/olapdbpssas2005.mspx#ELGAC

document states Having many measure groups in a cube can adversely affect the query performance of the cube, even for queries that only use a single measure group. If your DSV contains many potential fact tables and they will not usually be used together, consider creating multiple smaller cubes containing topic areas that are used together. Then, you can enable occasional cross-topic querying by creating linked measure groups that join together all measure groups in a special cross-topic cube.

Bhudev

|||

Hi, Bhudeve,

Thanks for that. Very helpful.

With best regards,

Yours sincerely,

Wednesday, March 7, 2012

Question on fact(degenerate) type of fact table relationship

Hi, all,

Could any of you give me some guidance on the fact(degenerate) type of fact tabel relationship between a dimension table and a fact table? Though its definition as: the dimension members are derived from the fact table. How does it really work?

We knwo there are degenerate dimensions which are included in fact tables e.g TransactionID etc in the underlying relational database. What is the idea of dealing with these scenario where we still want to see the degenerate dimensions in the cube dimensions instead of residing meaningless in the cube measure groups? Any best practices or suggestions?

Hope my question is clear for your help and I am again looking forward to hearing from you shortly for your advices.

With best regards,

Yours sincerely,

Hello Helen. When you build a dimension with the fact table as a data source these dimensions will not be different from dimensions with separate tables as data sources.

So you can start the dimension wizard and choose the fact table as a data source and pick the attributes that will be part of the degenerate dimension.

You can only have one degenerate dimension in a cube.

When you put them in a separate table you will have a one-to-one relation with the fact table at the leaf level so that approach is not that common.

Have a look at www.kimballgroup.com or google on degenerate dimensions.

From what I have seen they will take a little longer time to process than one-to-many dimensions.

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thanks a lot for your advices. So it works for all attributes in the dimension wizard with fact table selected as the data source? E.g. for some attibutes with only a few distinctive values (which we can put them into junk dimensions, then in this case, we dont necessary to create separate junk dimensions for these attributes which are originally residing in the fact table, instead we can use the dimension wizard to create new cube dimension with the fact table as data source)?

Therefore, in this case, we have to define the fact relationship as fact(degenerate) relationship for all cases where the dimension memembers are derived from the fact table?

Hope my question is clear.

With best regards,

Yours sincerely,

|||

Hi Helen.

You can create standard dimensions with attributes from the fact table without marking them as fact table dimension in the dimension usage tab. It also possible to mark one relation between the fact table /measure group and a dimension as a fact table dimension. Both ways will work and if you avoid the fact table relation type(dimension usage) you can make more than one.

Attributes with a few distinct values normally qualifies as standard dimensions and can be placed in a dimension table with a one-to-many relation with the fact table.

Junk or fact table dimensions normally have a one-to-one relation with the fact table.

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thank you very much for your very kind advices and help.

It then sounds like it is better to create standard dimensions with attributes from fact table which will enable more than one relationship types between the fact table and the derived dimension table.

Hopefully I am right.

Thank you again.

With best regards,

Yours sincerely,

Tuesday, March 20, 2012

Question on Relationships

Question on Relationships

Question on relationship between Cube and Fact tables?

Cube Design Best Practices

Avoid including unrelated measure groups in the same cube

Wednesday, March 7, 2012

Question on fact(degenerate) type of fact table relationship

SQLAgentMail

Blog Archive

About Me