Hello

I'm building a data warehouse and are using SQL server 2000 and Analysis Services.

I have a situation with a many-to-many relation between one dimension table and the fact table. I know that there are several different solutions to this problem but I have not found anyone that is specific for Analysis Services. (On Microsoft’s site I’ve only found one-to-many relations between the dimension and fact table, which is quite straightforward)

For example, Kimball uses a solution with a bridge table with a weighting factor. But how does this work with Analysis Services?

An other solution is to lower the grain of the fact table to the grain of the dimensional table, which I guess would work just fine with Analysis Services. But I don’t know if this is the "common" way of doing it or if there are other ways that are better.

Do you have any recommendations on how to solve this using Analysis Services? I would appreciate your input on how you solve this.

//Matte

There should never be a many to many relationship in a data warehouse project. It does not fit into either snowflake or star schema. That is why you don't see anything on it.....

Hello,

I am trying to do the same thing, ie many-to-many on SSAS2000.
Did you ever get an answer?

Cheers
Myles

there are no direct many to many relationsips in either SQL or data warehouse projects. In SQL you achieve a many to many relationship by making a one to many to one and the a one to many relationship. Thus you have a table in between to achieve a net many to man relationship. In a data warehouse project (star or snowflake schema, assuming your not talking about a staging area of a data warehouse project. All relationships are either one to one or one to many with the many always going tward the fact table. In Star schema all the relationships will be one to many(or concievably one) from the dimension to the fact table. In Snowflake it would be many to one from one dimension to the other then many to one again from the dimension table to the fact table. NEVER A MANY TO MANY.

Please post a description of what your trying to store if you want some suggestions. For example employees to ??? or sales to ??? etc.

and sorry for my topo's above. In SQL you do a many to many relationship by going from table a to b with a many to one relationship then from table b to c with a one to many relationship thus the net between a and c is a many to many. But you never have a direct many to many relationship between tables.

Hello everyone,
I have design a data model where I am using a bridge table in the RDBMS to capture the number of time a customer attended a company event. As you will see below, I capture the Event Type, Event, From Date and To Date. My fact table captures the charges/fees for every trade date for a particular customer. When I add the Event Type shared dimension in Analysis Service, I get duplicate records also.


The Customer and Event relationship looks something like this:

CUSTOMER --< COMMUNICATION EVENT >-- EVENT TYPE

When I add the dimension to the OLAP Cube, it incorporates the structures
automatically, which then I a get duplicate records.

Basically, I am interested in knowing how the customer charges and/or fees
(commission) increased or descreased since a customer attended the company's
conference.

Here are the following dimensions in the cube:
CUSTOMER
PERIOD
CHARGE_TYPE
DEAL_DETAIL
SALES_PERSON
SALES_TRADER
INDUSTRY_SEGMENT
COUNTRY

The EVENT_TYPE consist of various events held through out the year. It also
has a dummy record with a description "No Event
Attendence"

EVENT_TYPE
--EVENT_TYPE_KEY
--EVENT_DESC
- -EVENT_TYPE
--FROM_DATE
--TO_DATE

I have created a bridge table to track the various events that customer
attended:

COMMUNICATION_EVENT
--CUSTOMER_KEY
--EVENT_TYPE_KEY

Here are the following facts
CHARGE_ALLOCATION_FACT
--CUSTOMER_KEY
--END_MONTH_PERIOD_KEY
--CHARGE_TYPE_KEY
--DEAL_DETAIL_KEY
--SALES_PERSON_KEY
--SALES_TRADER_KEY
--INDUSTRY_S EGMENT_KEY
--COUNTRY_KEY
--CHARGE_AMT
--VOLUME_AMT

Although the CHARGE_ALLOCAITON_FACT has a record for every type of charge,
period key is associated with the end of month key or the last business day
in the month. However, I can still extract or identify the actual trade date
to show the daily charges. This is need because the business user wants to
select a range date to analyze the generate revenue from charges/fees daily.

Initially, my thoughts on the design,I will need to create another fact
table that will incorporate the EVENT_TYPE dimension, which would look like
this:

CHARGE_EVENT_FACT
--CUSTOMER_KEY
--FROM_DATE (Based on the trade date)
--TO_DATE (Base on the trade date)
--CHARGE_TYPE_KEY
--DEAL_DETAIL_KEY
--SALES_PERSON_KEY< br>--SALES_TRADER_KEY
--INDUSTRY_SEGMENT_KEY
--COUNTRY_KEY
-- EVENT_TYPE_KEY
--CHARGE_AMT
--VOLUME_AMT

However, I will probably run into problems when a customer attends more than
one event during the year or does not have any charges during that event but
afterward. I initially thought of just assigning the event key to the fact
records that fall under the From Date and To Date.

I realize that MS SQL Server does not like snowflak schemas.

Your feedback and insight is greatly appreciated.


Regards,
Fernando Sanchez

Hello All,

I need suggestion in designing of the datamart .I am using SQL Server 2005 Analysis Services



The scenario is :

Judy, Carol are two customers who have registered for 2 products say VB EXPRESS (carol) and C# Express(Judy)



During the registration time, they are provided with an online form to be filled and submitted.

For some questions (like here Programing area , Technical Area) the user can select multiple values in the

form and then finally submit. Once submitted the transaction is entered into the database as shown below.



I need to find the total number of registered users for a product for any combination of programigarea and technical area.

Like say:

Find the number of customers with techArea ='c#' and product ='C# Express';



Ans: 1 (Judy)



Find the number of customers with techArea ='c#,VB' and product ='C# Express';

Ans:1 (Judy)



Find the number of customers with techArea ='c#,VB,Vista' and product ='C# Express';

Ans:1 (Judy)



Find the number of customers with ProgramArea ='Ecommerce,Mobile' and product ='C# Express';

Ans:1 (Judy)



Find the number of customers with ProgramArea ='Ecommerce' and product ='C# Express';

Ans:1 (Judy)





I am trying to develop a datamart out of this OLTP database.My Dimension tables identified are

1.Country (id,name)

2.Product (id,name)

3.JobRole (id,name)

4.Time

5.TechArea

6.ProgramArea.



And fact table is

CustomerRegistrationFact(countryid,productid,jobid ,TotalRegistrations, <still need to add Techarea and Programarea>)



Since TechArea and ProgramArea are having 1:n relationships with customer i.e a customer can select mutliple tech areas and programing areas in the form,please suggest me how I design this datamart and the fact table.



The Existing (OLTP)Transaction Table



            (CustomerID) (qUESTIONid) (AnswerID)

ID customerName QuestionName AnswerName UpdatedDate

-----------------------------------------------------------------------

1 Judy TechArea c# 4th July

2 Judy TechArea Vista 4th July

3 Judy TechArea VB 4th July

4 Judy ProgramArea Ecommerce 4th July

5 Judy ProgramArea Mobile 4th July

6 Carol TechArea xml 5th July

7 Carol ProgramArea Internet 5th July




The issue:

Now am planning to build a xls report using the pivot table from Analysis cube.


Thanks and Regards