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)



Find the number of customers with ProgramArea ='Ecommerce' and Tech
Area='c#' 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,
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



Question: 1.Since there exists a many to many relationship with Customer
bewtween Techarea and Program area,

I am planning to have two facttables one with all common dimensions (like
Country,JobRole,..) with Techarea and Another Fact table with all common
dimensions (like Country,JobRole,..) with ProgramArea.

And have a common cube which has Measuregroups from each fact table and
finally make a linked measure.

(or)

Should is it possible to develop a single fact table?If yes please suggest
how to do so with an intermediatery fact table



Thanks and Regards

Assuming that you're using AS 2005, you might find some helpful ideas in
this recent paper on many-many dimensional modelling - the "Survey"
scenario covers the analysis of questions with multiple answers:

http://www.sqlbi.eu/Home/tabid/36/ct...ID/7/Default.a
spx

This is the introduction of a paper that describes how to leverage the
many-to-many dimension relationships, a feature that debuted available
with Analysis Services 2005. After introducing the main concepts, the
paper discusses various implementation techniques in the form of design
patterns: for each model, there is a description of a business scenario
that could benefit from the model, followed by an explanation of its
implementation.

Two separate downloads (available on SQLBI.EU project page) contain the
full paper in PDF format and SQL Server database and Analysis Services
projects with the same sample data used in the paper.

Analysis Services 2005 (SSAS 2005) introduced the capability to handle
many-to-many relationships between dimensions. At a first glance, you
may tend to underscore the importance of this feature: after all,
Analysis Services 2000 and many other OLAP engines do not offer
many-to-many relationships. Yet, its lack did not limit their adoption
and, apparently, only a few businesses really require it. However, as
this paper shows, the UDM (Unified Dimensional Model) can leverage
many-to-many relationships helping you to present data from different
perspectives that are not feasible with a traditional star schema. This
opens a brand new world of opportunities that transcends the limits of
traditional OLAP.

We will explore many different uses of many-to-many relationships that
give us more choices to model effectively business needs, including:

Classical many-to-many
Cascading many-to-many
Survey
Distinct Count
Multiple Groups
Cross-Time
Transition Matrix
Multiple Hierarchies
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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