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,TableBC,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
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,TableBC,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.[vbcol=seagreen]
>
> "Ron" <ron@.newsgroups.nospam> wrote in message
> news:eNO0OGezFHA.1444@.TK2MSFTNGP10.phx.gbl...
record.[vbcol=seagreen]
we[vbcol=seagreen]
the
>
|||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_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell
In article <ezICQFfzFHA.3660@.TK2MSFTNGP15.phx.gbl>,
ron@.newsgroups.nospam says...[vbcol=seagreen]
> 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.1db5b96da37460c2989726@.news.microsoft.com ...[vbcol=seagreen]
> 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_dot_com>
> Blog: http://www.geekswithblogs.net/darrengosbell
> In article <ezICQFfzFHA.3660@.TK2MSFTNGP15.phx.gbl>,
> ron@.newsgroups.nospam says...
This
>