|
Home > Archive > MS SQL Data Warehousing > October 2005 > Analysis services question
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Analysis services question
|
|
|
| We have a db with the following schema:
TableA
TableAB (serves as a many-to-many relationship between A and B)
TableB
TableBC (serves as a many-to-many relationship between B and C)
Table C
The foreign->primary keys are properly set between the tables:
TableA primary key is a foreign key in TableAB
TableB primary key is a foreign key in TableAB
TableB primary key is a foreign key in TableBC
TableC primary key is a foreign key in TableBC
What we want:
To be able to do is show how many C records are related to each A record.
Here is the equivalent sql statement:
select TableA.AKey,count(*)
from TableA,TableAB,Table
BC,TableC
where TableA.AKey = TableAB.AKey and
TableAB.BKey = TableBC.BKey and
TableBC.CKey = TableC.CKey
group by TableA.AKey
Issuess with SSAS:
We could not create a dimention with a hierarchy of the form A-B-C as it is
not a snowflake schema (not a one-to-many relationship).
We were able to create a many-to-many relation between A as a dimention and
B as a measure table using TableAB as an intermediate measure table but we
could not create a relation between A and C.
What possibilities do we have using SSAS to achieve the equivalent of the
sql statement mentioned above
| |
| Dave Wickert [MSFT] 2005-10-12, 11:23 am |
| AS2K does not support structures like this. However you can in AS2K5. This
is one of our core new features.
--
Dave Wickert [MSFT]
dwickert@online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ron" <ron@newsgroups.nospam> wrote in message
news:eNO0OGezFHA.1444@TK2MSFTNGP10.phx.gbl...
> We have a db with the following schema:
>
> TableA
> TableAB (serves as a many-to-many relationship between A and B)
> TableB
> TableBC (serves as a many-to-many relationship between B and C)
> Table C
>
> The foreign->primary keys are properly set between the tables:
> TableA primary key is a foreign key in TableAB
> TableB primary key is a foreign key in TableAB
> TableB primary key is a foreign key in TableBC
> TableC primary key is a foreign key in TableBC
>
> What we want:
> To be able to do is show how many C records are related to each A record.
>
> Here is the equivalent sql statement:
> select TableA.AKey,count(*)
> from TableA,TableAB,Table
BC,TableC
> where TableA.AKey = TableAB.AKey and
> TableAB.BKey = TableBC.BKey and
> TableBC.CKey = TableC.CKey
> group by TableA.AKey
>
> Issuess with SSAS:
>
> We could not create a dimention with a hierarchy of the form A-B-C as it
> is
> not a snowflake schema (not a one-to-many relationship).
>
> We were able to create a many-to-many relation between A as a dimention
> and
> B as a measure table using TableAB as an intermediate measure table but we
> could not create a relation between A and C.
>
> What possibilities do we have using SSAS to achieve the equivalent of the
> sql statement mentioned above
>
>
>
| |
|
| Dave,
I am trying this on 2005.
Ron
"Dave Wickert [MSFT]" <dwickert@online.microsoft.com> wrote in message
news:uUmS5pezFHA.2348@TK2MSFTNGP15.phx.gbl...
> AS2K does not support structures like this. However you can in AS2K5. This
> is one of our core new features.
> --
> Dave Wickert [MSFT]
> dwickert@online.microsoft.com
> Program Manager
> BI Systems Team
> SQL BI Product Unit (Analysis Services)
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
>
> "Ron" <ron@newsgroups.nospam> wrote in message
> news:eNO0OGezFHA.1444@TK2MSFTNGP10.phx.gbl...
record.[color=darkred]
we[color=darkred]
the[color=darkred]
>
>
| |
| Darren Gosbell 2005-10-12, 11:23 am |
| Couldn't you create a view that joins the AB and BC tables giving you
vwAC and then use that to join A to C in a standard many-to-many
reationship?
--
Regards
Darren Gosbell [MCSD]
< dgosbell_at_yahoo_do
t_com>
Blog: http://www.geekswithblogs.net/darrengosbell
In article <ezICQFfzFHA.3660@TK2MSFTNGP15.phx.gbl>,
ron@newsgroups.nospam says...[color=darkred]
> Dave,
>
> I am trying this on 2005.
>
> Ron
>
> "Dave Wickert [MSFT]" <dwickert@online.microsoft.com> wrote in message
> news:uUmS5pezFHA.2348@TK2MSFTNGP15.phx.gbl...
> rights.
> record.
| |
|
| Thank you. Your solution gave the desired effect.
"Darren Gosbell" <xxx@xxx.com> wrote in message
news:MPG. 1db5b96da37460c29897
26@news.microsoft.com...
> Couldn't you create a view that joins the AB and BC tables giving you
> vwAC and then use that to join A to C in a standard many-to-many
> reationship?
>
> --
> Regards
> Darren Gosbell [MCSD]
> < dgosbell_at_yahoo_do
t_com>
> Blog: http://www.geekswithblogs.net/darrengosbell
>
> In article <ezICQFfzFHA.3660@TK2MSFTNGP15.phx.gbl>,
> ron@newsgroups.nospam says...
This[color=darkred]
>
|
|
|
|
|