I am trying to provide drill across feature by linking two datamarts.
Basically I have TroubleTickets datamart and IndividualTransactions datamart.
It is interesting to users to see what kind of transactions produce what type
of tickets. Data Modelling wise I am thinking of providing a bridge table
TicketTransaction which will have FactTicketID and FactTransactionID. One
ticket could be related to zero to many transactions.

My questions is how should the drill across be modelled in Analysis Services?

If you're using AS 2005, the many-many dimension feature may help model
bridge table scenarios:

http://msdn.microsoft.com/library/de.../en-us/dnsql90
/html/sql2k5snapshotisol.asp

Richard Tkachuk
Microsoft Corporation

June 2005

Applies to:

Microsoft SQL Server 2005 Analysis Services

Summary: See an example of using the Many-to-Many dimension in SQL
Server 2005 Analysis Services to analyze sales data, and get ideas for
other uses such as treating medical conditions, software testing, and
more.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Thanks.

That might be helpful. I would appreciate a little more detail on that
thought. Would the facttable2 be a dimension in that scenario? What about
dimensions to Facttable2? How will they be available to facttable1?

"Deepak Puri" wrote:

I haven't implemented exactly such a scenario, but I can suggest some
ideas to try:

- Ticket would be both a measure group and dimension (with fact
relationship to the measure group) - the same for Transaction as well.

- The TicketTransaction table provides the intermediate measure group
for many-many dimension relationships.

- A dimension directly related to Ticket, like TicketType, would be a
referenced dimension for TicketTransaction, via the Ticket dimension.
Likewise for TransactionType, via the Transaction dimension.

- TicketType would then have a many-many relationship to the Transaction
measure group via the TickeTransaction intermediate. Likewise,
TransactionType would relate to the Trnasaction measure group on a
many-many basis.


You may get detailed guidance from a forthcoming paper by Marco Russo on
many-to-many dimensional modeling in AS 2005:

http://sqljunkies.com/WebLog/sqlbi/a.../21/22495.aspx


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***