Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old November 10th, 2004, 06:42 AM
Authorized User
 
Join Date: Apr 2004
Location: , , .
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default Many-to-many dimensional modeling

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


Reply With Quote
  #2 (permalink)  
Old September 7th, 2005, 06:57 PM
Friend of Wrox
 
Join Date: Aug 2004
Location: Orange County, CA, USA.
Posts: 385
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.....

Reply With Quote
  #3 (permalink)  
Old September 25th, 2005, 07:13 PM
Registered User
 
Join Date: Sep 2005
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hello,

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

Cheers
Myles

Reply With Quote
  #4 (permalink)  
Old September 29th, 2005, 08:20 PM
Friend of Wrox
 
Join Date: Aug 2004
Location: Orange County, CA, USA.
Posts: 385
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
  #5 (permalink)  
Old September 29th, 2005, 08:22 PM
Friend of Wrox
 
Join Date: Aug 2004
Location: Orange County, CA, USA.
Posts: 385
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
  #6 (permalink)  
Old April 14th, 2006, 11:22 AM
Registered User
 
Join Date: Feb 2005
Location: HOBOKEN, NJ, USA.
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.


Quote:
quote:Originally posted by robprell
 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.

Regards,
Fernando Sanchez
Reply With Quote
  #7 (permalink)  
Old September 22nd, 2006, 12:34 PM
Registered User
 
Join Date: Sep 2006
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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



Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
two-dimensional array Lizane Java Basics 4 May 23rd, 2007 09:35 AM
1 and 2 dimensional arrays jeff_cafm VB.NET 2002/2003 Basics 1 April 26th, 2006 05:02 PM
1 and 2 dimensional arrays jeff_cafm VB.NET 1 April 24th, 2006 03:05 PM
Modeling with restrictions on attribute value jacob XML 0 June 3rd, 2005 07:01 AM



All times are GMT -4. The time now is 06:56 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.