Need suggestion in DataMart deisgn

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 produc
t
='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,produ
ctid,jobid,TotalRegistrations, <stil
l
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 Jul
y
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 RegardsAnalsisCoder wrote:
> 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 c
an
> select multiple values in the
> form and then finally submit. Once submitted the transaction is entered in
to
> 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 prod
uct
> ='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,produ
ctid,jobid,TotalRegistrations, <st
ill
> 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# 4
th
> July
> 2 Judy TechArea Vista 4t
h
> July
> 3 Judy TechArea VB 4t
h
> July
> 4 Judy ProgramArea Ecommerce 4th July
> 5 Judy ProgramArea Mobile 4th J
uly
> 6 Carol TechArea xml 5
th
> 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
Here's how I would do it with 1 fact table.
Have the customer info in a dim but have two tables realted to the
dim_customer and the dimension tables for the program and tech area.
cust_programarea
cust_id, program_id
cust_trecharea
cust_id, tech_id