Hello... I have the follow tables:

Table: fact_1
fact_id int (autoincrement)
fact_qty int default = 1
date datetime

Table: dim_reason
reason_id int (autoincrement)
reason_desc varchar(20)

Table: bridge_fact_reason
bridge_id int (autoincrement)
fact_id int
reason_id int

+ dim_date as a regular data.


Between the dim_reason and the fact_1 I have a bridge_fact_reason
becuase I can have more than one dim_reason for each fact_id.

However, I count not make it work on SSAS 2K5. When I browse the cube
and just add the reason_desc and the count from bridge is ok. But if I
add the count from fact, it show the TOTAL count of fact in each line.

In the other hand, if I add the date and count of fact works fine. When
I add the count of bridge, it happen the same, namely, it show the TOAL
count of bridge for each line of the date.

Does anyone knows how to make this work?

Tks

This MSDN paper should help you:

http://msdn.microsoft.com/library/de.../en-us/dnsql90
/html/sql2k5_mmdiminas.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 ***

hi Deepak,
I have a question about design for MSAS 2k5 on one-to-many of facts, when
one fact became a dimension to another fact.

I have cube with Item fact table and transaction fact table.

one-to-many ( Item.transaction_key is FK to TRANSACTION fact table
whereTransaction.transaction_key is PK ).

I also have Date with date_key as PK.
Transaction.date_key is FK in Transaction table to Date table
and
Item.date_tend_key is FK in Item table to Date table

I have 5 dimensions around TRANSACTION fact table and 3 dimensions around
ITEM fact table.

When building the cube I've defined TRANSACTION fact table as a 'fact and
dimension',
and ITEM table as a 'fact' .

Every aggregation/slicing by all dimensions works OK ...
EXCEPT when I am using Date dimension (date hrchy) with Item
facts(measures).
(date dimension has 4 levels of calendar date hrchy,fiscal hrchy and week
hrchy - none works with Item fact measures).

when drilling I am getting the same total for single measure, for example,
$12199999 for any date level or any dimension that is join to Date dimension,
even for years(upper level of cal.date hrchy) that do not have any data for
items' aggregations(measures)...

I know the explanation of problem sounds very vague...many things could
happened but ...
data is correct and if I am building a separate cube for item (without
transaction fact) - everything works well for all Date slicing...

I can build 2 cubes based on 2 separate facts - transaction and item...
but before doing so ...I would appreciate any suggestions on what can be
wrong with design of One-to-Many Facts , i.e. when fact is a fact and
dimension with Shared date dimension...or smth else...

thank you ,
Alex Deiden


"Deepak Puri" wrote:

Hi Alex,

From the description, it sounds like the date_key in TRANSACTION fact
table need not be equal to the date_tend_key in the ITEM fact table. In
that case, does the Date dimension have 2 roles in the cube, one for the
Transaction and the other for the Item Measure Group? Also, what is the
Granularity Attribute on which the Date dimension relates to the Item
Measure Group?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Hi Deepak,
Thank you - you have given me absolutely right directions!
I have added(picked) the Date dimension to Item fact in the Dimension tab
layout of the cube in design time - somehow it has not been created during
the creation of the cube...
thnx a lot!
Alex

"Deepak Puri" wrote: