Many-to-Many Self Joins ?

Hi Folks,
I need to implement a many-to-many self join for a table of acronyms ie each
acronym may _reference_ one or more other acronyms and, in addition, each
acronym may _be referenced by_ one or more other acronyms. I tried to
utilize the code in
"http://www.tomjewett.com/dbdesign/dbdesign.php?page=recursive.php&imgsize=medium"
see DDL and data below . However the results I got from the query don't
appear correct. Can you see where I've gone wrong?
Thanks, Simon
CREATE TABLE [Acronyms] (
[Acronym_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Acronym] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Description] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
CONSTRAINT [PK_Acronyms] PRIMARY KEY CLUSTERED
(
[Acronym_ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Acronym_ID Acronym Description
1 ABC ABC
2 DEF DEF
3 XYZ XYZ
CREATE TABLE [Synonyms] (
[Acronym_ID] [int] NOT NULL ,
[Synonym_ID] [int] NOT NULL ,
[Start_Date] [datetime] NOT NULL ,
[End_Date] [datetime] NULL ,
CONSTRAINT [PK_Synonyms] PRIMARY KEY CLUSTERED
(
[Acronym_ID],
[Synonym_ID],
[Start_Date]
) ON [PRIMARY] ,
CONSTRAINT [FK_Synonyms_Acronyms] FOREIGN KEY
(
[Acronym_ID]
) REFERENCES [Acronyms] (
[Acronym_ID]
),
CONSTRAINT [FK_Synonyms_Acronyms1] FOREIGN KEY
(
[Synonym_ID]
) REFERENCES [Acronyms] (
[Acronym_ID]
)
) ON [PRIMARY]
GO
Acronym_ID Synonym_ID Start_Date End_Date
2 1 01-Jan-05
3 1 06-Jun-05
1 2 02-Feb-05
1 3 02-Feb-05
SELECT TOP 100 PERCENT A.Acronym AS Acronym, R.Acronym AS Synonym
FROM dbo.Acronyms A LEFT OUTER JOIN
dbo.Synonyms S INNER JOIN
dbo.Acronyms R ON S.Acronym_ID = R.Acronym_ID ON
A.Acronym_ID = S.Acronym_ID
ORDER BY A.Acronym
Results...
Acronym Synonym
ABC ABC
ABC ABC
DEF DEF
XYZ XYZ
Hi Simon,
What's your expected result? Could you clarify more? In the following
article, the author will show a simple example that traverses a hierarchy
and displays the output in indented format.
Working with hierarchical data in SQL Server databases
http://vyaskn.tripod.com/hierarchies..._databases.htm
BTW, In SQL Server 2005, we support Common Table Expression support
recursive directly.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Michael,
I don't believe my problem is simply hierarchical given that an entity can
have more than one parent
Simon
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:af9Cmm26FHA.1236@.TK2MSFTNGXA02.phx.gbl...
> Hi Simon,
> What's your expected result? Could you clarify more? In the following
> article, the author will show a simple example that traverses a hierarchy
> and displays the output in indented format.
> Working with hierarchical data in SQL Server databases
> http://vyaskn.tripod.com/hierarchies..._databases.htm
> BTW, In SQL Server 2005, we support Common Table Expression support
> recursive directly.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
|||Hi Simon,
I cannot see the image in link
"http://www.tomjewett.com/dbdesign/dbdesign.php?page=recursive.php&imgsize=m
edium", would you please clarify the structure of your samples more?
What's your expected result?
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Many-to-Many Self Joins ?

Hi Folks,
I need to implement a many-to-many self join for a table of acronyms ie each
acronym may _reference_ one or more other acronyms and, in addition, each
acronym may _be referenced by_ one or more other acronyms. I tried to
utilize the code in
"http://www.tomjewett.com/dbdesign/dbdesign.php?page=recursive.php&imgsize=m
edium"
see DDL and data below . However the results I got from the query don't
appear correct. Can you see where I've gone wrong?
Thanks, Simon
CREATE TABLE [Acronyms] (
[Acronym_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Acronym] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT N
ULL ,
[Description] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
NULL ,
CONSTRAINT [PK_Acronyms] PRIMARY KEY CLUSTERED
(
[Acronym_ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Acronym_ID Acronym Description
1 ABC ABC
2 DEF DEF
3 XYZ XYZ
CREATE TABLE [Synonyms] (
[Acronym_ID] [int] NOT NULL ,
[Synonym_ID] [int] NOT NULL ,
[Start_Date] [datetime] NOT NULL ,
[End_Date] [datetime] NULL ,
CONSTRAINT [PK_Synonyms] PRIMARY KEY CLUSTERED
(
[Acronym_ID],
[Synonym_ID],
[Start_Date]
) ON [PRIMARY] ,
CONSTRAINT [FK_Synonyms_Acronyms] FOREIGN KEY
(
[Acronym_ID]
) REFERENCES [Acronyms] (
[Acronym_ID]
),
CONSTRAINT [FK_Synonyms_Acronyms1] FOREIGN KEY
(
[Synonym_ID]
) REFERENCES [Acronyms] (
[Acronym_ID]
)
) ON [PRIMARY]
GO
Acronym_ID Synonym_ID Start_Date End_Date
2 1 01-Jan-05
3 1 06-Jun-05
1 2 02-Feb-05
1 3 02-Feb-05
SELECT TOP 100 PERCENT A.Acronym AS Acronym, R.Acronym AS Synonym
FROM dbo.Acronyms A LEFT OUTER JOIN
dbo.Synonyms S INNER JOIN
dbo.Acronyms R ON S.Acronym_ID = R.Acronym_ID ON
A.Acronym_ID = S.Acronym_ID
ORDER BY A.Acronym
Results...
Acronym Synonym
ABC ABC
ABC ABC
DEF DEF
XYZ XYZHi Simon,
What's your expected result? Could you clarify more? In the following
article, the author will show a simple example that traverses a hierarchy
and displays the output in indented format.
Working with hierarchical data in SQL Server databases
http://vyaskn.tripod.com/hierarchie...r_databases.htm
BTW, In SQL Server 2005, we support Common Table Expression support
recursive directly.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Michael,
I don't believe my problem is simply hierarchical given that an entity can
have more than one parent
Simon
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:af9Cmm26FHA.1236@.TK2MSFTNGXA02.phx.gbl...
> Hi Simon,
> What's your expected result? Could you clarify more? In the following
> article, the author will show a simple example that traverses a hierarchy
> and displays the output in indented format.
> Working with hierarchical data in SQL Server databases
> http://vyaskn.tripod.com/hierarchie...r_databases.htm
> BTW, In SQL Server 2005, we support Common Table Expression support
> recursive directly.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Simon,
I cannot see the image in link
"http://www.tomjewett.com/dbdesign/dbdesign.php?page=recursive.php&imgsize=m
edium", would you please clarify the structure of your samples more?
What's your expected result?
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.sql

Many-to-Many Self Joins ?

Hi Folks,
I need to implement a many-to-many self join for a table of acronyms ie each
acronym may _reference_ one or more other acronyms and, in addition, each
acronym may _be referenced by_ one or more other acronyms. I tried to
utilize the code in
"http://www.tomjewett.com/dbdesign/dbdesign.php?page=recursive.php&imgsize=medium"
see DDL and data below . However the results I got from the query don't
appear correct. Can you see where I've gone wrong?
Thanks, Simon
CREATE TABLE [Acronyms] (
[Acronym_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Acronym] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Description] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
CONSTRAINT [PK_Acronyms] PRIMARY KEY CLUSTERED
(
[Acronym_ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Acronym_ID Acronym Description
1 ABC ABC
2 DEF DEF
3 XYZ XYZ
CREATE TABLE [Synonyms] (
[Acronym_ID] [int] NOT NULL ,
[Synonym_ID] [int] NOT NULL ,
[Start_Date] [datetime] NOT NULL ,
[End_Date] [datetime] NULL ,
CONSTRAINT [PK_Synonyms] PRIMARY KEY CLUSTERED
(
[Acronym_ID],
[Synonym_ID],
[Start_Date]
) ON [PRIMARY] ,
CONSTRAINT [FK_Synonyms_Acronyms] FOREIGN KEY
(
[Acronym_ID]
) REFERENCES [Acronyms] (
[Acronym_ID]
),
CONSTRAINT [FK_Synonyms_Acronyms1] FOREIGN KEY
(
[Synonym_ID]
) REFERENCES [Acronyms] (
[Acronym_ID]
)
) ON [PRIMARY]
GO
Acronym_ID Synonym_ID Start_Date End_Date
2 1 01-Jan-05
3 1 06-Jun-05
1 2 02-Feb-05
1 3 02-Feb-05
SELECT TOP 100 PERCENT A.Acronym AS Acronym, R.Acronym AS Synonym
FROM dbo.Acronyms A LEFT OUTER JOIN
dbo.Synonyms S INNER JOIN
dbo.Acronyms R ON S.Acronym_ID = R.Acronym_ID ON
A.Acronym_ID = S.Acronym_ID
ORDER BY A.Acronym
Results...
Acronym Synonym
ABC ABC
ABC ABC
DEF DEF
XYZ XYZHi Simon,
What's your expected result? Could you clarify more? In the following
article, the author will show a simple example that traverses a hierarchy
and displays the output in indented format.
Working with hierarchical data in SQL Server databases
http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm
BTW, In SQL Server 2005, we support Common Table Expression support
recursive directly.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Michael,
I don't believe my problem is simply hierarchical given that an entity can
have more than one parent
Simon
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:af9Cmm26FHA.1236@.TK2MSFTNGXA02.phx.gbl...
> Hi Simon,
> What's your expected result? Could you clarify more? In the following
> article, the author will show a simple example that traverses a hierarchy
> and displays the output in indented format.
> Working with hierarchical data in SQL Server databases
> http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm
> BTW, In SQL Server 2005, we support Common Table Expression support
> recursive directly.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Simon,
I cannot see the image in link
"http://www.tomjewett.com/dbdesign/dbdesign.php?page=recursive.php&imgsize=m
edium", would you please clarify the structure of your samples more?
What's your expected result?
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Many-to-Many dimensions

How to implement M-to-M dimensions in SQL2000 Analysis Services?
there is some solutions regarding what are your data and how you want to
aggregate your data.
Classic example:
Car 1 --> n Property n <-- 1 Sales
(1 car can have 1 or more property)
In this type of situation, you don't want to have 10 * sales if your car has
10 properties.
Parent-child dimension with data for non-leaf members is 1 option
2 cubes (1 for the detail and 1 for the aggregate) merged into 1 virtual
could also solve the problem.
but, can you explain what are your expected results?
"ps" <ps@.discussions.microsoft.com> wrote in message
news:17E69934-3EE0-47E7-8B85-77264E64865A@.microsoft.com...
> How to implement M-to-M dimensions in SQL2000 Analysis Services?

Many-to-Many dimensions

How to implement M-to-M dimensions in SQL2000 Analysis Services?there is some solutions regarding what are your data and how you want to
aggregate your data.
Classic example:
Car 1 --> n Property n <-- 1 Sales
(1 car can have 1 or more property)
In this type of situation, you don't want to have 10 * sales if your car has
10 properties.
Parent-child dimension with data for non-leaf members is 1 option
2 cubes (1 for the detail and 1 for the aggregate) merged into 1 virtual
could also solve the problem.
but, can you explain what are your expected results?
"ps" <ps@.discussions.microsoft.com> wrote in message
news:17E69934-3EE0-47E7-8B85-77264E64865A@.microsoft.com...
> How to implement M-to-M dimensions in SQL2000 Analysis Services?

many to many relationship

hi all,
how to implement the many to many relation ship...
i get stucked it......
plz help
thanx
sajjadhere's a good example -- http://www.dbforums.com/t1094761.html|||thanx for reply man
can u explain the briefly background theory of Many to Many relation ship|||http://www.utexas.edu/its/windows/database/datamodeling/rm/rm4.html

Many tables-> bad?

Currently, I can chose two ways to implement a function:
The easy way would lead to many (300) tables.
The harder way would lead to considerable fewer tables.
Is there a reason to chose the harder way? Are there any penalties, if you
have many tables (with few entries) compared to few tables with many entries
?
Every input is welcome.Explain why implementing a function would require implementing new tables.
Do these tables store some type of meta data?
"the friendly display name"
<thefriendlydisplayname@.discussions.microsoft.com> wrote in message
news:99C532C3-3F4D-46F9-8F86-BF5EE6930441@.microsoft.com...
> Currently, I can chose two ways to implement a function:
> The easy way would lead to many (300) tables.
> The harder way would lead to considerable fewer tables.
> Is there a reason to chose the harder way? Are there any penalties, if you
> have many tables (with few entries) compared to few tables with many
> entries?
> Every input is welcome.|||I will attempt to answer this in haiku:
Since I cannot clearly see
What your problem is
The answer is 42
[url]http://en.wikipedia.org/wiki/ The_Answer_to_Life,_the_Universe,_and_Ev
erything[/url
]
On a serious note, there are a lot of ways to answer this question; if
you're building a data warehouse and using attribute splitting, 300
tables in a partitioned view is a perfectly acceptable practice. 300
joins may not be.
What is the function of which you speak? Can you post more
information, and perhaps we can come up with some suggestions on how to
implement what you are trying to do.
Stu|||Are you familiar with relational design principles and the concept of
normalization? That should be your guide to the logical design.
Physical design comes after logical but since you've told us nothing
about your logical design we can't help you.
If you aren't familiar with design principles then you need to take and
course or study some books. It's much too big a topic for a newsgroup.
David Portas
SQL Server MVP
--|||If you can figure out what is an entity and what is an attribute you will
answer your own question.
"the friendly display name"
<thefriendlydisplayname@.discussions.microsoft.com> wrote in message
news:99C532C3-3F4D-46F9-8F86-BF5EE6930441@.microsoft.com...
> Currently, I can chose two ways to implement a function:
> The easy way would lead to many (300) tables.
> The harder way would lead to considerable fewer tables.
> Is there a reason to chose the harder way? Are there any penalties, if you
> have many tables (with few entries) compared to few tables with many
> entries?
> Every input is welcome.

Many - Many Currency Conversion

I ran the "Currency conversion" BI wizard to implement Many to Many currency conversion but get the following error when I click on Finish in the wizard. Looks like the wizard is trying to generate some thing wrong. Any clue ?

The 'DimensionAttribute' with 'Name' = 'Reporting Currency' doesn't exist in the collection

Cheers,

Arun

Got this to working by creating a "Role Playing dimension" for currency that is used by the FX rates measure groups. Now, I have a bigger problem.

1. er the wizard generates the dimensions, calculation etc., we need to define the relationship b/w the generated "Reporting Currency" dimension and the measure groups. (Else the new dimension will not be displayed in the client). But, logically there is no relationship. How do we overcome this ?

measure group1 : Balance

Dimension : Calendar, Currency, ......

measure group2 : Rates (The fact table contains rates for all currencies from EUR for all days. cols - Date, CurrencyCode, Rate)

Dimension : Calendar, ToCurrency, ....

Newly generated dimension : Reporting Currency

2. Also a member called "Local" is used in calcualtion. Can some one explain the need for this ?

Any help is appreciated.

|||

Could you clarify your goal? Are you saying you want to see the "To Currency" (cube) dimension against the Balance measure group? If so, could you confirm a (many to many) relationship exists between the "To Currency" dimension and the Balance measure group after the wizard is finished?

Thanks,

Bryan

|||

Well, my goal is to get the many to many currency conversion working.

One of the problems is that once the wizard is finished, I can see a new "Reporting Currency" dimension created and no relationship exists in Dimension Usage and hence I'm not able to see this dimension through Excel or from any other client.

Thanks,

Arun

|||

I am familiar with currency conversion in theory, but I have not used the wizards. I suspect all you need to do is set a many-to-many relationship between the "To Currency" (cube) dimension and the Balance measure group.

To do this, open the cube editor and select the dimension usage tab. Click on the grey-box at the intersection of the Balance measure group and the "To Currency" dimension. This should give you a button in the right-hand side of this box. Click that button to get the Define Relationship dialog.

In the Define Relationship dialog, set relationship type to many-to-many and set the Rates measure group as the intermediate measure group. If the Rates measure group is not available in the drop down, SSAS can't identify a set of shared dimensions between these two measure groups. This indicates a deeper problem and I suspect you might need to revisit how you've used the Currency Conversion wizard.

BTW, the SSAS 2005 Step-by-Step book covers currency conversion with reasonable detail. That could be a good resource for getting started.

Bryan

|||

I have a ToCurreny dimension (I created this myself. Has relationship only to Rates) + another "Reporting Currency" dimension generated by the wizard.

In all the examples given in the book, they have the above 2 dimensions as the same but in real-world scenario it cant be the same. ToCurrency - Will contain all currencies for which rates are available. They will have to include rates for all transaction currencies in the fact table. (in my case 100 currencies)

Reporting Currency - should include only the set of currencies for which we need the conversion (in my case only some 6 currencies)

But the problem is that, there is no logical relationship b/w Reporting Currency and Rates or Balance measure group as that dimension will only be used for on-the-fly calculation. So, I'm in a fix now to set the dimension usage for this dimension whithout which , this cannot be viewed from the client.

thanks,

Arun

|||

Problem solved.

Solution:

I had created 2 diff measures (so 2 diff measure groups)

1) Rates_ToEUR ==> Will contain rates for all currencies to EUR for all days

2) Rates_FromEUR ==> Will contain rates from EUR to only the reporting currencies

Dimension Usage :

Measure group - Rates_ToEUR -- Currency (same one as used by balance measure group) and Calendar

Measure group - Rates_FromEUR -- Calendar and ReportingCurrency. Also, Balance measure group has many to many relation ship with ReportingCurrency dim through this measure group.

Also, I have simplified the calculation to below :

Scope ( { Measures.[DLY Value Reporting CCY], Measures.[MTD Value Reporting CCY], Measures.[YTD Value Reporting CCY], Measures.[LTD Value Reporting CCY]});

Scope( Leaves([Calendar]) ,Leaves([Currency]), [Reporting Currency].[Currency].Members);

This = Measures.CurrentMember * Measures.[Rate_ToEUR] * (Measures.[Rate_FromEUR], [Reporting Currency].[Currency].CurrentMember) ;

end scope;

End Scope; // Measures

The above solution works perfectly till now. Any one can see any issues?

So, I would suggest implementing currency conversion by yourself without using the wizard (BI) which does not help much.

Cheers,

Arun

sql