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

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

Need sql to return the result of a query as comma seperated values.

Hi,
I need a sql that returns thequery result as comma seperated list of values, instead of several rows. Below is the scenario...
Table Name - Customer
Columns - CustomerID, Join Date
Say below is the data of Customer table ...
CustomerID JoinDate
1 04/01/2005
2 01/03/2003
3 06/02/2004
4 01/05/2002
5 09/07/2005
Now i want to retrieve all the customerid's who have joined this year. Below is the query that i use for this case.
Select CustomerID from Customer where JoinDate between '01/01/2005' and GetDate()
This gives the below result as two rows.
CustomerID
1
5
But i need to get the result as '1,5' (comma seperated list of resulting values).
Any help is highly appreciated
Thanks in Advance
Ramesh

You need declare a variable :
declare @.value nvarchar(200)
Select@.value=case when @.value is null then '' else @.value+',' end+cast(CustomerID as varchar) from Customer where JoinDate between '01/01/2005' and GetDate()
|||Hi,
You can manage your goal by using COALESCE
Please check the following URLs for sample COALESCE usage for returning the column values of a tables in a string seperated by a delimeter character.
http://www.kodyaz.com/ShowPost.aspx?PostID=76
http://www.kodyaz.com/article.aspx?ArticleID=29

As a sample you can run the below code on Northwind database also
DECLARE @.s as nvarchar(4000)
DECLARE @.char as char(1)
SELECT @.char = ','
SELECT @.s = @.char
SELECT @.s = COALESCE(FirstName + @.char + @.s , '') FROM Employees
SELECT SUBSTRING(@.s, 0, LEN(@.s)-1) AS Employees

I hope this helps,
Eralper
|||

Hi All,

I Solved the problem with the below query...

DECLARE @.CustomerIDs VARCHAR(8000)

SELECT @.CustomerIDs = ISNULL(@.CustomerIDs + ',', '') + CAST(CustomerID AS VARCHAR(10))
FROM CUSTOMER
WHERE JoinDate BETWEEN '01/01/2005' and GetDate()

SELECT @.CustomerIDs AS CustomerID

|||It's great! It really worked fine. Thanks a lot...

Need SQL Performance Advice (Scenario Given)

Hey guys, having a bit of a urgency here. I need to make certain queries faster in our application, lemme give you a scoup. One of the queries i need to optimize or find a better way to do is the current scenario. We have jobs in our system, these jobs have applicants, some of them are direct applicants, some are matches (from searches), and some are applicants with resumes only.
Some of our clients have 100+ jobs, when they see the first page we list all the jobs with 6 counts next to them:

Job Title, Total Applicants, New Applicants, Total Matches, New Matches, Total Resumes, New Resumes

So we have 6 counts for each job, say you have 100+ jobs thats a lot of counts. Currently i'm performing a sub query for each of the counts since they represent different data points, the only thing that changes is the WHERE clause.

As you can see this can be very heavy and in some cases (100+ jobs) depending of number of applicants might take up to a minute to finish.

I would like to get some opinions from you guys on how to make this run faster, obviously caching cannot be considered since they employer must see the live data (counts)

Any help is greatly appreciated.indexes on the join columns?|||yes indexes are in place, i dont really thing that they are the problem,

i think the subqueries are killing it since one query needs to be run to get each of the count right? is there any other way to structure this thing?

thanks for your help|||depends

without seeing the subqueries, it's kinda difficult to tell what's wrong or whether an alternative structure is possible|||Without seeing your code, my first suggestion would be to "unwrap" the subqueries. Make a single pass through the data, doing a single join. Reimplement the counts as sums, and use CASE to provide the "smarts" to make it work.

As Rudy pointed out, without seeing what you are doing, we are pretty sorely limited in how we can help. This is kind of like calling someone on another continent and telling them "my stomach hurts" and asking them what you should do about it.

-PatP

Need Query for Latest Time per ID

I've been baffled over how to do this without using a script... I would like to get the info I need with a single query.

Here's my scenario, the table looks like this (simplified):

ID, TIME
101, 5am
101, 6am
104, 5am
260, 5am
104, 6am
260, 6am
101, 7am
260, 9am
104, 7am
101, 8am

So basically I have a column of identifications and a column of times. They won't necessarily be in order. I would like a query that gives me this:

101, 8am
104, 7am
260, 9am

It would order the IDs ascending, only showing the newest time assigned to that ID in the table.

Thanks in advance for any help :)select id
, time
from daTable as T
where time
= ( select max(time)
from daTable
where id = T.id )
order
by id|||select, id, max(time)
from daTable
group by id
order by id|||Thanks guys - I used the example that ronin gave because it looked simpler, and it works great. :D

Now I have to add to it. I would have posted this last night but I forgot about it. I have another table that associates IDs with NAMEs. So it looks like this:

101, Apple
104, Orange
260, Banana

What I would like to do is replace the ID with the NAME, so my final query result looked like this:

Apple, 8am
Orange, 7am
Banana, 9am|||if you only want to return those two columns, use the query in post #3

if there are any other columns in the table, and you want the entire row that has the latest date, use the query in post #3|||What, you don't like your own solution?|||if you only want to return those two columns, use the query in post #3

if there are any other columns in the table, and you want the entire row that has the latest date, use the query in post #3

I think there is a typo, both your statements say use #3. ;) I edited my post, I ended up using #3 - now I have something to add to the query. Then after that I think I'm done with it.|||thanks guys, yeah, that was a typo -- if you want other columns in the same row with the latest date, post #2 is the only way to get them correctly|||thanks guys, yeah, that was a typo -- if you want other columns in the same row with the latest date, post #2 is the only way to get them correctly

My table only has those two columns so it works out fine - but in the future I'm sure I will need your example for other larger tables.

Can you help with replacing the ID with the NAME? thanks :)

edit:
Okay I got it. My final query looks like this:

SELECT idTable.name, MAX(timeTable.time)
FROM time INNER JOIN
idTable ON timeTable.time = idTable.name
GROUP BY idTable.name
ORDER BY idTable.name

Thanks for all your help. :)|||Okay, now what I want to try is instead of getting the most recent timestamp using MAX - is it possible to get any that are newer than 9 minutes?|||This is pretty basic stuff, Rick. You need to review the information on SELECT statements, WHERE clauses, and AGGREGATE functions in Books Online, as well as become familiar with the various built-in datetime functions.
SELECT idTable.name,
timeTable.time
FROM time INNER JOIN
idTable ON timeTable.time = idTable.name
WHERE timeTable.time > dateadd(minute, -9, getdate())
ORDER BY idTable.name|||This is pretty basic stuff, Rick. You need to review the information on SELECT statements, WHERE clauses, and AGGREGATE functions in Books Online, as well as become familiar with the various built-in datetime functions.
SELECT idTable.name,
timeTable.time
FROM time INNER JOIN
idTable ON timeTable.time = idTable.name
WHERE timeTable.time > dateadd(minute, -9, getdate())
ORDER BY idTable.name

Basic stuff to someone that has dealth with it on a semi-regular basis. :P I'm pretty good with Access, but direct sql is still fairly new to me. I took classes is db theory before but still need more time hands-on.

In this scenario is WHERE better than HAVING?|||In this scenario is WHERE better than HAVING?oh yes, oh my goodness, yes

the fact that Access routinely uses a HAVING clause in places where it logically should be using a WHERE clause does not make it right|||oh yes, oh my goodness, yes

the fact that Access routinely uses a HAVING clause in places where it logically should be using a WHERE clause does not make it right

Okay - I used what blindman gave me, that works great. The only reason I asked about HAVING versus WHERE is because I was trying to search google for help/examples on this before asking you guys, and I was finding stuff that preferred HAVING but the examples didn't quite what I needed.

thanks again :) My query is finally complete, I'll let you guys go back to helping others for awhile. ;)|||HAVING is only meant to be used for criteria involving aggregate functions.