Showing posts with label customer. Show all posts
Showing posts with label customer. Show all posts

Tuesday, March 27, 2012

Count Loyalty

Hi

I would like to count loyalty customer for a brand

E.g.

Category Brand Customer

1 2 A

1 3 A

1 2 A

We need to find out whether customer A is buy Brand 2 all the time.

In above example, Under Cat. 1, this is not a loyal customer.

Count Category =3

Count Brand 2 =2

Count Brand 3 = 1

So how should i compare the result,

The Count Category = Count Brand 2--> Loyal customer,

Thanks for anyone to give some hint for me to start....

Doing a count of the nonempty brands should be a good start.

Count(NonEmpty([Brand].[Brand].[Brand].Members))

This would give you a count of the number of brands for what ever other dimension members (customers, time, etc) are currently selected. A count of 1 would indicate a loyal customer over the selected time period, 0 would indicate no purchases and 2+ would indicate "disloyalty".

|||

Hi Darren,

Thanks for your little guide.

now i manage to come out some result which i think is useful. but now how should i count the "L" from the result

WITH MEMBER [Measures].Loyalty AS

CASE

WHEN [Measures].[Int Category Id Count] = 0 THEN NULL

WHEN [Measures].[Int Brand Id Count] <> [Measures].[Int Category Id Count] THEN "S"

WHEN [Measures].[Int Brand Id Count] = [Measures].[Int Category Id Count] THEN "L"

ELSE "C"

END

SELECT [Dim Customer].[Dim Customer] on 0,

NON EMPTY([Dim Brand].[Dim Brand].Members) on 1

FROM [CS DW Cube]

WHERE [Measures].Loyalty

This will generate a list of

All
All L
100002 L
100003 L
100004 L
100007 L
100008 L

How should I count this result?

Thanks again,

|||

Does this mean you want to get customer counts for each of these categories? You would probably need to break this into separate measures. For the "L" group it would look something like the following

WITH MEMBER [Measures].[Loyalty Count] AS

Count( FILTER(EXISTING [Customer].[Customer].[Customer].Members, [Measures].[Int Brand Id Count] = [Measures].[Int Category Id Count] ))

SELECT [Dim Customer].[Dim Customer] on 0,

NON EMPTY([Dim Brand].[Dim Brand].Members) on 1

FROM [CS DW Cube]

WHERE [Measures].[Loyalty Count]

|||

Hi Darren,

I'm really appreciate your help, now i'm sure i very close to the answer.

There are one more question regarding the [Measures].[Int Brand Id Count]

Currently this measures, is only count on one field named (intBrandId), would that possible to count on combination key, reason is because the (intBrandId) is not unique.

Is this Possible to count Like this --> Count ( intBrandId, intCategoryId)

Thanks again

|||

If you want to count the unique combination of these two fields, you would crossjoin the sets of attribute members that map to them.

eg

Count( crossjoin( intBrandid, intCategoryId) )

or you can use the * operator which is just shorthand for crossjoining

eg

Count( intBrandid * intCategoryId )

|||

Hi Darren,

Cross join, it seem like count number of intBrandID in brand Table multiply with number of intCategoryId in Category table.

But i want to calculate total for this composite key (intBrandid and intCateogry)

Thanks

|||

Sorry, if these are independant dimensions your only choice is to count the non empty crossjoined members.

count( nonempty( crossjoin(...) ) )

but it almost sounds like these two attributes should be modelled in the same dimension.

|||

Hi Darren,

Yes your are right, is from 2 dimension.

The reason i asked (count on Composite key), is because i had a fact table with (intBrandId and intCategoyId), so my idea was to find out number of loyalty customer by compare the count of (intBrandId and intCategoryId) with the count of (intCategoryId) in the fact table. Is that possible because i'm not able to find a way to count (2 fields) within the fact table, or is this the right way to find the answer?

How is it actually able to find the answer by cross join 2 dimesion tables (Brand and Category) to find answer for number of loyalty customer?

Please explain for me, because i'm still weak in concept of SSAS.

The reason is i think the fact table contain the information to find out the loyalty customer, so should it find answer from fact rather than cross join this 2 tables?

Thansk for you explaination so far.|||

As I have said before, if a brand belongs to a specific category, then these attribute should be modelled in the one dimension, which means that if this is the case the category key should not be in the fact table.

When you have these two attributes linked separately to the fact table you are essentially saying that it is logically valid for every brand to exist in every category. The crossjoin creates this theoretical subcube which we then need to filter down with the nonempty function to find only those combinations of attributes that have values.

The reason is i think the fact table contain the information to find out the loyalty customer, so should it find answer from fact rather than cross join this 2 tables?

If you are using MOLAP structures the tables to not come into play at all, they are read at processing time, but from then on SSAS creates it's own storage structures and returns the data from there. SSAS make use of bitmap indexes and crossjoins are common and are not usually as expensive as they would be in SQL and you are not actually "joining" tables.

An alternative approach might be to create a distinct count measure over your intBrandId, then you could just return this value for any given category. This might be an easier way to get the figures you want without crossjoins and without changing your data model too much.

Count Loyalty

Hi

I would like to count loyalty customer for a brand

E.g.

Category Brand Customer

1 2 A

1 3 A

1 2 A

We need to find out whether customer A is buy Brand 2 all the time.

In above example, Under Cat. 1, this is not a loyal customer.

Count Category =3

Count Brand 2 =2

Count Brand 3 = 1

So how should i compare the result,

The Count Category = Count Brand 2--> Loyal customer,

Thanks for anyone to give some hint for me to start....

Doing a count of the nonempty brands should be a good start.

Count(NonEmpty([Brand].[Brand].[Brand].Members))

This would give you a count of the number of brands for what ever other dimension members (customers, time, etc) are currently selected. A count of 1 would indicate a loyal customer over the selected time period, 0 would indicate no purchases and 2+ would indicate "disloyalty".

|||

Hi Darren,

Thanks for your little guide.

now i manage to come out some result which i think is useful. but now how should i count the "L" from the result

WITH MEMBER [Measures].Loyalty AS

CASE

WHEN [Measures].[Int Category Id Count] = 0 THEN NULL

WHEN [Measures].[Int Brand Id Count] <> [Measures].[Int Category Id Count] THEN "S"

WHEN [Measures].[Int Brand Id Count] = [Measures].[Int Category Id Count] THEN "L"

ELSE "C"

END

SELECT [Dim Customer].[Dim Customer] on 0,

NON EMPTY([Dim Brand].[Dim Brand].Members) on 1

FROM [CS DW Cube]

WHERE [Measures].Loyalty

This will generate a list of

All
All L
100002 L
100003 L
100004 L
100007 L
100008 L

How should I count this result?

Thanks again,

|||

Does this mean you want to get customer counts for each of these categories? You would probably need to break this into separate measures. For the "L" group it would look something like the following

WITH MEMBER [Measures].[Loyalty Count] AS

Count( FILTER(EXISTING [Customer].[Customer].[Customer].Members, [Measures].[Int Brand Id Count] = [Measures].[Int Category Id Count] ))

SELECT [Dim Customer].[Dim Customer] on 0,

NON EMPTY([Dim Brand].[Dim Brand].Members) on 1

FROM [CS DW Cube]

WHERE [Measures].[Loyalty Count]

|||

Hi Darren,

I'm really appreciate your help, now i'm sure i very close to the answer.

There are one more question regarding the [Measures].[Int Brand Id Count]

Currently this measures, is only count on one field named (intBrandId), would that possible to count on combination key, reason is because the (intBrandId) is not unique.

Is this Possible to count Like this --> Count ( intBrandId, intCategoryId)

Thanks again

|||

If you want to count the unique combination of these two fields, you would crossjoin the sets of attribute members that map to them.

eg

Count( crossjoin( intBrandid, intCategoryId) )

or you can use the * operator which is just shorthand for crossjoining

eg

Count( intBrandid * intCategoryId )

|||

Hi Darren,

Cross join, it seem like count number of intBrandID in brand Table multiply with number of intCategoryId in Category table.

But i want to calculate total for this composite key (intBrandid and intCateogry)

Thanks

|||

Sorry, if these are independant dimensions your only choice is to count the non empty crossjoined members.

count( nonempty( crossjoin(...) ) )

but it almost sounds like these two attributes should be modelled in the same dimension.

|||

Hi Darren,

Yes your are right, is from 2 dimension.

The reason i asked (count on Composite key), is because i had a fact table with (intBrandId and intCategoyId), so my idea was to find out number of loyalty customer by compare the count of (intBrandId and intCategoryId) with the count of (intCategoryId) in the fact table. Is that possible because i'm not able to find a way to count (2 fields) within the fact table, or is this the right way to find the answer?

How is it actually able to find the answer by cross join 2 dimesion tables (Brand and Category) to find answer for number of loyalty customer?

Please explain for me, because i'm still weak in concept of SSAS.

The reason is i think the fact table contain the information to find out the loyalty customer, so should it find answer from fact rather than cross join this 2 tables?

Thansk for you explaination so far.|||

As I have said before, if a brand belongs to a specific category, then these attribute should be modelled in the one dimension, which means that if this is the case the category key should not be in the fact table.

When you have these two attributes linked separately to the fact table you are essentially saying that it is logically valid for every brand to exist in every category. The crossjoin creates this theoretical subcube which we then need to filter down with the nonempty function to find only those combinations of attributes that have values.

The reason is i think the fact table contain the information to find out the loyalty customer, so should it find answer from fact rather than cross join this 2 tables?

If you are using MOLAP structures the tables to not come into play at all, they are read at processing time, but from then on SSAS creates it's own storage structures and returns the data from there. SSAS make use of bitmap indexes and crossjoins are common and are not usually as expensive as they would be in SQL and you are not actually "joining" tables.

An alternative approach might be to create a distinct count measure over your intBrandId, then you could just return this value for any given category. This might be an easier way to get the figures you want without crossjoins and without changing your data model too much.

Count Loyalty

Hi

I would like to count loyalty customer for a brand

E.g.

Category Brand Customer

1 2 A

1 3 A

1 2 A

We need to find out whether customer A is buy Brand 2 all the time.

In above example, Under Cat. 1, this is not a loyal customer.

Count Category =3

Count Brand 2 =2

Count Brand 3 = 1

So how should i compare the result,

The Count Category = Count Brand 2--> Loyal customer,

Thanks for anyone to give some hint for me to start....

Doing a count of the nonempty brands should be a good start.

Count(NonEmpty([Brand].[Brand].[Brand].Members))

This would give you a count of the number of brands for what ever other dimension members (customers, time, etc) are currently selected. A count of 1 would indicate a loyal customer over the selected time period, 0 would indicate no purchases and 2+ would indicate "disloyalty".

|||

Hi Darren,

Thanks for your little guide.

now i manage to come out some result which i think is useful. but now how should i count the "L" from the result

WITH MEMBER [Measures].Loyalty AS

CASE

WHEN [Measures].[Int Category Id Count] = 0 THEN NULL

WHEN [Measures].[Int Brand Id Count] <> [Measures].[Int Category Id Count] THEN "S"

WHEN [Measures].[Int Brand Id Count] = [Measures].[Int Category Id Count] THEN "L"

ELSE "C"

END

SELECT [Dim Customer].[Dim Customer] on 0,

NON EMPTY([Dim Brand].[Dim Brand].Members) on 1

FROM [CS DW Cube]

WHERE [Measures].Loyalty

This will generate a list of

All
All L
100002 L
100003 L
100004 L
100007 L
100008 L

How should I count this result?

Thanks again,

|||

Does this mean you want to get customer counts for each of these categories? You would probably need to break this into separate measures. For the "L" group it would look something like the following

WITH MEMBER [Measures].[Loyalty Count] AS

Count( FILTER(EXISTING [Customer].[Customer].[Customer].Members, [Measures].[Int Brand Id Count] = [Measures].[Int Category Id Count] ))

SELECT [Dim Customer].[Dim Customer] on 0,

NON EMPTY([Dim Brand].[Dim Brand].Members) on 1

FROM [CS DW Cube]

WHERE [Measures].[Loyalty Count]

|||

Hi Darren,

I'm really appreciate your help, now i'm sure i very close to the answer.

There are one more question regarding the [Measures].[Int Brand Id Count]

Currently this measures, is only count on one field named (intBrandId), would that possible to count on combination key, reason is because the (intBrandId) is not unique.

Is this Possible to count Like this --> Count ( intBrandId, intCategoryId)

Thanks again

|||

If you want to count the unique combination of these two fields, you would crossjoin the sets of attribute members that map to them.

eg

Count( crossjoin( intBrandid, intCategoryId) )

or you can use the * operator which is just shorthand for crossjoining

eg

Count( intBrandid * intCategoryId )

|||

Hi Darren,

Cross join, it seem like count number of intBrandID in brand Table multiply with number of intCategoryId in Category table.

But i want to calculate total for this composite key (intBrandid and intCateogry)

Thanks

|||

Sorry, if these are independant dimensions your only choice is to count the non empty crossjoined members.

count( nonempty( crossjoin(...) ) )

but it almost sounds like these two attributes should be modelled in the same dimension.

|||

Hi Darren,

Yes your are right, is from 2 dimension.

The reason i asked (count on Composite key), is because i had a fact table with (intBrandId and intCategoyId), so my idea was to find out number of loyalty customer by compare the count of (intBrandId and intCategoryId) with the count of (intCategoryId) in the fact table. Is that possible because i'm not able to find a way to count (2 fields) within the fact table, or is this the right way to find the answer?

How is it actually able to find the answer by cross join 2 dimesion tables (Brand and Category) to find answer for number of loyalty customer?

Please explain for me, because i'm still weak in concept of SSAS.

The reason is i think the fact table contain the information to find out the loyalty customer, so should it find answer from fact rather than cross join this 2 tables?

Thansk for you explaination so far.|||

As I have said before, if a brand belongs to a specific category, then these attribute should be modelled in the one dimension, which means that if this is the case the category key should not be in the fact table.

When you have these two attributes linked separately to the fact table you are essentially saying that it is logically valid for every brand to exist in every category. The crossjoin creates this theoretical subcube which we then need to filter down with the nonempty function to find only those combinations of attributes that have values.

The reason is i think the fact table contain the information to find out the loyalty customer, so should it find answer from fact rather than cross join this 2 tables?

If you are using MOLAP structures the tables to not come into play at all, they are read at processing time, but from then on SSAS creates it's own storage structures and returns the data from there. SSAS make use of bitmap indexes and crossjoins are common and are not usually as expensive as they would be in SQL and you are not actually "joining" tables.

An alternative approach might be to create a distinct count measure over your intBrandId, then you could just return this value for any given category. This might be an easier way to get the figures you want without crossjoins and without changing your data model too much.

Sunday, March 25, 2012

COunt Function in Report

I am creating a report that will print the customer's Invoices grouped by SalesPerson. I created a variable in the dataset so that it sets to the status of the invoce to Paid, Not Paid and Partial. All is working fine so far.

I have a groupfooter for each salesperson where I want to print the total no. of invoices for that SalesPerson that are PAID, NONPAID and Partaial.

So on the expressions of that fields, I have the following code. It allows me to print the report, but it prints #Error for the value of thoses field

I also want to print the the same fields in the report total area.

Any ideas ?

=Count(Fields!Document_No_.Value)having (Fields!InvStatus.Value = "PAID")

=Count(Fields!Document_No_.Value)having (Fields!InvStatus.Value = "UNPAID")

=Count(Fields!Document_No_.Value)having (Fields!InvStatus.Value = "PARTIAL")

Guys, I figured this one out.

Here is the solution :
=Sum(IIF Fields!InvStatus.Value = "PAID",1,0)

sql

Count by month in summarized data

create table calls
(
customer char(10),
call_date datetime,
called_number char(10),
call_ammount char(10)
)
insert calls values('Customer1','1/1/2005','555-1234','1.00')
insert calls values('Customer2','1/2/2005','555-1231','0.50')
insert calls values('Customer3','1/1/2005','555-1232','5.00')
insert calls values('Customer4','2/1/2005','555-1233','1.00')
insert calls values('Customer1','2/2/2005','555-1234','1.00')
insert calls values('Customer1','2/3/2005','555-1235','1.00')
insert calls values('Customer2','2/4/2005','555-1236','3.00')
insert calls values('Customer4','2/1/2005','555-1237','2.00')
insert calls values('Customer5','3/1/2005','555-1238','2.00')
I need to get a list of customers calling for > $2.50 a month.
select MONTH(call_date) as Months
, customer
,sum (cast(call_ammount as decimal(3,2))) as 'SUM $'
,count( distinct customer ) as 'No. of cust.'
from calls with(nolock)
where call_date >= '1/1/2005'
group by MONTH(call_date), customer
--with rollup
HAVING sum(cast(call_ammount as decimal(3,2))) > 2.5
order by Months
, 'SUM Ammount $' desc
(without the counts) will get what I need .
Months Customer SUM $ No. of cust.
1 Customer3 5.00 1
2 Customer2 3.00 1
2 Customer4 3.00 1
3 Customer5 3.00 1
Turns out , I 'd also need to get monthly counts (ideally with $
totals) of these customers .
This is where I get stuck.
When I add a count to the query I have the result as shown above but
what I'd need is
Months SUM $ No. of cust.
1 5.00 1
2 6.00 2
3 3.00 1
or totals like with Rollup but Rollup doesn't work with distinct count.
BTW yes, this is simplified, and I know this is bad (no) design but
there is nothing I can do about that at the moment.
TIA
ZTry using your query as a derived table.
select
months,
sum([SUM $]) as c1,
sum([No. of cust.]) as c2
from
(
select MONTH(call_date) as Months
, customer
,sum (cast(call_ammount as decimal(3,2))) as 'SUM $'
,count( distinct customer ) as 'No. of cust.'
from calls with(nolock)
where call_date >= '1/1/2005'
group by MONTH(call_date), customer
HAVING sum(cast(call_ammount as decimal(3,2))) > 2.5
)
group by
months
AMB
"zoranlee@.hotmail.com" wrote:

> create table calls
> (
> customer char(10),
> call_date datetime,
> called_number char(10),
> call_ammount char(10)
> )
> insert calls values('Customer1','1/1/2005','555-1234','1.00')
> insert calls values('Customer2','1/2/2005','555-1231','0.50')
> insert calls values('Customer3','1/1/2005','555-1232','5.00')
> insert calls values('Customer4','2/1/2005','555-1233','1.00')
> insert calls values('Customer1','2/2/2005','555-1234','1.00')
> insert calls values('Customer1','2/3/2005','555-1235','1.00')
> insert calls values('Customer2','2/4/2005','555-1236','3.00')
> insert calls values('Customer4','2/1/2005','555-1237','2.00')
> insert calls values('Customer5','3/1/2005','555-1238','2.00')
> I need to get a list of customers calling for > $2.50 a month.
> select MONTH(call_date) as Months
> , customer
> ,sum (cast(call_ammount as decimal(3,2))) as 'SUM $'
> ,count( distinct customer ) as 'No. of cust.'
> from calls with(nolock)
> where call_date >= '1/1/2005'
> group by MONTH(call_date), customer
> --with rollup
> HAVING sum(cast(call_ammount as decimal(3,2))) > 2.5
> order by Months
> , 'SUM Ammount $' desc
> (without the counts) will get what I need .
> Months Customer SUM $ No. of cust.
> 1 Customer3 5.00 1
> 2 Customer2 3.00 1
> 2 Customer4 3.00 1
> 3 Customer5 3.00 1
> Turns out , I 'd also need to get monthly counts (ideally with $
> totals) of these customers .
> This is where I get stuck.
> When I add a count to the query I have the result as shown above but
> what I'd need is
> Months SUM $ No. of cust.
> 1 5.00 1
> 2 6.00 2
> 3 3.00 1
> or totals like with Rollup but Rollup doesn't work with distinct count.
> BTW yes, this is simplified, and I know this is bad (no) design but
> there is nothing I can do about that at the moment.
> TIA
> Z
>

Saturday, February 25, 2012

could not deliver the snapshot to the subscriber

We have a customer w/ merge replication setup and has been functioning.
Their server's were hacked. We have spent 3 days on the phone w/ MS and
everything is working except for 1 client. When it is trying to deliver
the snapshot it is getting an error:
The process could not bulk copy into table {table name}
Violation of PRIMARY KEY constraint. Cannot insert duplicate key.
I understand what that means, so on that table (it is not a necessary
table, just a log), I deleted all records from that table and the
delivery got past that table to the next table in the list and displayed
the same message.
What can I do on the client to get this to work? I can't delete the info
in the second table because it is much more required and I don't want to
have it delete the same data on the main server. I know it is trying to
insert a new record that has the same primary key, but is it trying to
do that because the server record has one guid and the client has a
different guid (I don't really know how the guid's work in replication -
if the record has the same guid on all servers).
Darin
*** Sent via Developersdex http://www.codecomments.com ***
I would use a tool like RedGate's datacompare to reconcile the data to the
Publisher. You'll need to modify the scripts somewhat if you have PK issues
and then I'd reinitialize.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||I know they are duplicate keys. The client has a record w/ the key as 2
(identity column), so does the distributor. The snapshot is trying to
send that record down - why, and how can I make so the snapshot doesn't
try to send it down?
Darin
*** Sent via Developersdex http://www.codecomments.com ***
|||Hi.
In the publisher, mark that identity column which is acting as primary key
to NOT FOR REPLICATION in the design mode of that table.
in this case, the identity will be inserted independently at the subscriber
avoiding primary key errors.
"Darin" wrote:

> I know they are duplicate keys. The client has a record w/ the key as 2
> (identity column), so does the distributor. The snapshot is trying to
> send that record down - why, and how can I make so the snapshot doesn't
> try to send it down?
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***
>
|||The table on both the subscriber and the distrubtor are setup that way.
Darin
*** Sent via Developersdex http://www.codecomments.com ***
|||Can you outline your situation a little more. Usually a snapshot of the
publisher's data is sent from the publisher to the subscriber. Initially
there is a drop table executed on the subscriber. There are variations - one
of which is a nosync initialization, where all the required info already
exists on the subscriber and the publisher just sends the metadata and
system stored procedures/triggers required to run replication. There are
other possibilities where the table and its data remain and the new data is
appended. Of these, which is your requirement, or is your requirement
something quite different?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Friday, February 17, 2012

Cost of Sql Server - programmer - customer

Which the cost to have the sql server, for the programmer and which the cost
for my customer?
Is it very difficult of installing the sql server in the customer? is a
technician necessary, or does he have as a cd of installation of the system
or did he see code to leave the program rotating without a technician's
need?Depends. Am I your customer? Or the people who work at my wife's school.
If the user is moderately technically savvy, they might be able to get away
with it. The problem is not loading the software, it is making sure
everything is set up to be backed up, and such. Will you be giving them a
database to install? Or will they be doing that on their own? If the
latter, then I would not suggest it.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Frank Dulk" <fdulk@.bol.com.br> wrote in message
news:Ot%23i633TFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Which the cost to have the sql server, for the programmer and which the
> cost
> for my customer?
> Is it very difficult of installing the sql server in the customer? is a
> technician necessary, or does he have as a cd of installation of the
> system
> or did he see code to leave the program rotating without a technician's
> need?
>