MS SQL Data Warehousing - Need suggestion in DataMart deisgn

Author

Need suggestion in DataMart deisgn

AnalsisCoder

2006-10-25, 6:00 am

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

CustomerRegistration
Fact(countryid,produ
ctid,jobid,TotalRegi
strations, <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

Bill Robinette

2006-10-25, 6:00 am

AnalsisCoder 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 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
>
> CustomerRegistration
Fact(countryid,produ
ctid,jobid,TotalRegi
strations, <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

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

Sponsored Links