显示标签为“based”的博文。显示所有博文
显示标签为“based”的博文。显示所有博文

2012年3月8日星期四

BCP Exporting Duplicate Rows

Hi everyone.
I am BCP-ing out data from one table into separate files based on a date
range. Each file has anywhere from 15 to 30 million rows. The source table
has a primary key, and I have verified that there is no duplicate rows. I
have also double checked the date ranges and they are good.
I then bcp the data back into another empty table, with the same primary key
as the source table. But I get a primary key violation error. I then bcp-ed
the data into a temporary table with no primary key. Then I ran a query to
check for duplicates. And sure enough, there were over 300K duplicate rows.
Has anybody seen this kind of behavior with BCP?
OS: Window Server 2003 R2, Standard - SP2.
SQL: 2005 Enterprise, SP2
Thank you!
J> Has anybody seen this kind of behavior with BCP?
I haven't run into this. Can you provide more details on the BCP command
you are using (i.e. out, queryout)?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"DnBDBA" <DnBDBA@.discussions.microsoft.com> wrote in message
news:3E192B68-C642-4B34-A89A-9013C4FCC328@.microsoft.com...
> Hi everyone.
> I am BCP-ing out data from one table into separate files based on a date
> range. Each file has anywhere from 15 to 30 million rows. The source table
> has a primary key, and I have verified that there is no duplicate rows. I
> have also double checked the date ranges and they are good.
> I then bcp the data back into another empty table, with the same primary
> key
> as the source table. But I get a primary key violation error. I then
> bcp-ed
> the data into a temporary table with no primary key. Then I ran a query to
> check for duplicates. And sure enough, there were over 300K duplicate
> rows.
> Has anybody seen this kind of behavior with BCP?
> OS: Window Server 2003 R2, Standard - SP2.
> SQL: 2005 Enterprise, SP2
> Thank you!
> J|||My BCP statement is pretty simple. I am using queryout to select data from
date ranges. Here's the string:
bcp "select * from MARS.dbo.CardTransaction (nolock) where TRANSACTIONTIME
between '2006-10-02 05:00:00.001' and '2006-11-02 05:00:00.000'" queryout
"Q:\PartitionBCPFiles\CardTransaction2006_1102.nat" -e
"Q:\PartitionBCPFiles\bcperrors_2006_1102.txt" -n -SS-MARSDBPRD01\PRD01 -T
-a8192
Thanks!
"Dan Guzman" wrote:
> > Has anybody seen this kind of behavior with BCP?
> I haven't run into this. Can you provide more details on the BCP command
> you are using (i.e. out, queryout)?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "DnBDBA" <DnBDBA@.discussions.microsoft.com> wrote in message
> news:3E192B68-C642-4B34-A89A-9013C4FCC328@.microsoft.com...
> > Hi everyone.
> > I am BCP-ing out data from one table into separate files based on a date
> > range. Each file has anywhere from 15 to 30 million rows. The source table
> > has a primary key, and I have verified that there is no duplicate rows. I
> > have also double checked the date ranges and they are good.
> > I then bcp the data back into another empty table, with the same primary
> > key
> > as the source table. But I get a primary key violation error. I then
> > bcp-ed
> > the data into a temporary table with no primary key. Then I ran a query to
> > check for duplicates. And sure enough, there were over 300K duplicate
> > rows.
> > Has anybody seen this kind of behavior with BCP?
> >
> > OS: Window Server 2003 R2, Standard - SP2.
> > SQL: 2005 Enterprise, SP2
> >
> > Thank you!
> >
> > J
>|||If the table is updated during a scan with the NOLOCK hint (or READ
UNCOMMITTED transaction isolation level), rows may be skipped or duplicated
and you might get uncommitted data. This issue applies to any READ
UNCOMMITTED query, not just BCP.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"DnBDBA" <DnBDBA@.discussions.microsoft.com> wrote in message
news:78C37AA0-C36C-4C47-8210-A3069B975382@.microsoft.com...
> My BCP statement is pretty simple. I am using queryout to select data from
> date ranges. Here's the string:
> bcp "select * from MARS.dbo.CardTransaction (nolock) where TRANSACTIONTIME
> between '2006-10-02 05:00:00.001' and '2006-11-02 05:00:00.000'" queryout
> "Q:\PartitionBCPFiles\CardTransaction2006_1102.nat" -e
> "Q:\PartitionBCPFiles\bcperrors_2006_1102.txt" -n -SS-MARSDBPRD01\PRD01 -T
> -a8192
> Thanks!
> "Dan Guzman" wrote:
>> > Has anybody seen this kind of behavior with BCP?
>> I haven't run into this. Can you provide more details on the BCP command
>> you are using (i.e. out, queryout)?
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "DnBDBA" <DnBDBA@.discussions.microsoft.com> wrote in message
>> news:3E192B68-C642-4B34-A89A-9013C4FCC328@.microsoft.com...
>> > Hi everyone.
>> > I am BCP-ing out data from one table into separate files based on a
>> > date
>> > range. Each file has anywhere from 15 to 30 million rows. The source
>> > table
>> > has a primary key, and I have verified that there is no duplicate rows.
>> > I
>> > have also double checked the date ranges and they are good.
>> > I then bcp the data back into another empty table, with the same
>> > primary
>> > key
>> > as the source table. But I get a primary key violation error. I then
>> > bcp-ed
>> > the data into a temporary table with no primary key. Then I ran a query
>> > to
>> > check for duplicates. And sure enough, there were over 300K duplicate
>> > rows.
>> > Has anybody seen this kind of behavior with BCP?
>> >
>> > OS: Window Server 2003 R2, Standard - SP2.
>> > SQL: 2005 Enterprise, SP2
>> >
>> > Thank you!
>> >
>> > J|||Thanks for the reply.
This table is not updated, it's static data. I tried the same query with an
"insert into ... select" command, and it worked fine.
"Dan Guzman" wrote:
> If the table is updated during a scan with the NOLOCK hint (or READ
> UNCOMMITTED transaction isolation level), rows may be skipped or duplicated
> and you might get uncommitted data. This issue applies to any READ
> UNCOMMITTED query, not just BCP.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "DnBDBA" <DnBDBA@.discussions.microsoft.com> wrote in message
> news:78C37AA0-C36C-4C47-8210-A3069B975382@.microsoft.com...
> > My BCP statement is pretty simple. I am using queryout to select data from
> > date ranges. Here's the string:
> > bcp "select * from MARS.dbo.CardTransaction (nolock) where TRANSACTIONTIME
> > between '2006-10-02 05:00:00.001' and '2006-11-02 05:00:00.000'" queryout
> > "Q:\PartitionBCPFiles\CardTransaction2006_1102.nat" -e
> > "Q:\PartitionBCPFiles\bcperrors_2006_1102.txt" -n -SS-MARSDBPRD01\PRD01 -T
> > -a8192
> >
> > Thanks!
> >
> > "Dan Guzman" wrote:
> >
> >> > Has anybody seen this kind of behavior with BCP?
> >>
> >> I haven't run into this. Can you provide more details on the BCP command
> >> you are using (i.e. out, queryout)?
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "DnBDBA" <DnBDBA@.discussions.microsoft.com> wrote in message
> >> news:3E192B68-C642-4B34-A89A-9013C4FCC328@.microsoft.com...
> >> > Hi everyone.
> >> > I am BCP-ing out data from one table into separate files based on a
> >> > date
> >> > range. Each file has anywhere from 15 to 30 million rows. The source
> >> > table
> >> > has a primary key, and I have verified that there is no duplicate rows.
> >> > I
> >> > have also double checked the date ranges and they are good.
> >> > I then bcp the data back into another empty table, with the same
> >> > primary
> >> > key
> >> > as the source table. But I get a primary key violation error. I then
> >> > bcp-ed
> >> > the data into a temporary table with no primary key. Then I ran a query
> >> > to
> >> > check for duplicates. And sure enough, there were over 300K duplicate
> >> > rows.
> >> > Has anybody seen this kind of behavior with BCP?
> >> >
> >> > OS: Window Server 2003 R2, Standard - SP2.
> >> > SQL: 2005 Enterprise, SP2
> >> >
> >> > Thank you!
> >> >
> >> > J
> >>
>|||The only explanation I can think of is that the IAM pages (scanned due to
NOLOCK hint) are out-of-sync with the linked list (used with default READ
COMMITTED). Did you try the BCP queryout without the NOLOCK hint? You
might try running a DBCC CHECKDB.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"DnBDBA" <DnBDBA@.discussions.microsoft.com> wrote in message
news:9875560F-42C7-46F8-ABF4-44C10C76B310@.microsoft.com...
> Thanks for the reply.
> This table is not updated, it's static data. I tried the same query with
> an
> "insert into ... select" command, and it worked fine.
>
> "Dan Guzman" wrote:
>> If the table is updated during a scan with the NOLOCK hint (or READ
>> UNCOMMITTED transaction isolation level), rows may be skipped or
>> duplicated
>> and you might get uncommitted data. This issue applies to any READ
>> UNCOMMITTED query, not just BCP.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "DnBDBA" <DnBDBA@.discussions.microsoft.com> wrote in message
>> news:78C37AA0-C36C-4C47-8210-A3069B975382@.microsoft.com...
>> > My BCP statement is pretty simple. I am using queryout to select data
>> > from
>> > date ranges. Here's the string:
>> > bcp "select * from MARS.dbo.CardTransaction (nolock) where
>> > TRANSACTIONTIME
>> > between '2006-10-02 05:00:00.001' and '2006-11-02 05:00:00.000'"
>> > queryout
>> > "Q:\PartitionBCPFiles\CardTransaction2006_1102.nat" -e
>> > "Q:\PartitionBCPFiles\bcperrors_2006_1102.txt" -n -SS-MARSDBPRD01\PRD01
>> > -T
>> > -a8192
>> >
>> > Thanks!
>> >
>> > "Dan Guzman" wrote:
>> >
>> >> > Has anybody seen this kind of behavior with BCP?
>> >>
>> >> I haven't run into this. Can you provide more details on the BCP
>> >> command
>> >> you are using (i.e. out, queryout)?
>> >>
>> >> --
>> >> Hope this helps.
>> >>
>> >> Dan Guzman
>> >> SQL Server MVP
>> >>
>> >> "DnBDBA" <DnBDBA@.discussions.microsoft.com> wrote in message
>> >> news:3E192B68-C642-4B34-A89A-9013C4FCC328@.microsoft.com...
>> >> > Hi everyone.
>> >> > I am BCP-ing out data from one table into separate files based on a
>> >> > date
>> >> > range. Each file has anywhere from 15 to 30 million rows. The source
>> >> > table
>> >> > has a primary key, and I have verified that there is no duplicate
>> >> > rows.
>> >> > I
>> >> > have also double checked the date ranges and they are good.
>> >> > I then bcp the data back into another empty table, with the same
>> >> > primary
>> >> > key
>> >> > as the source table. But I get a primary key violation error. I then
>> >> > bcp-ed
>> >> > the data into a temporary table with no primary key. Then I ran a
>> >> > query
>> >> > to
>> >> > check for duplicates. And sure enough, there were over 300K
>> >> > duplicate
>> >> > rows.
>> >> > Has anybody seen this kind of behavior with BCP?
>> >> >
>> >> > OS: Window Server 2003 R2, Standard - SP2.
>> >> > SQL: 2005 Enterprise, SP2
>> >> >
>> >> > Thank you!
>> >> >
>> >> > J
>> >>

2012年2月18日星期六

bcp - Run time file name generation

Hi,
I am currently running a bcp command to dump a table at run-time. This
requirement has slightly changed and now I need to extract data based on
certain time interval (once in 5 minutes). Because of this, I need to name my
output files as <tablename>_<timestamp>.
My current bcp command looks like this
bcp "select * from asu..t_Skill_Group_Half_Hour WITH (NOLOCK) where
DbDateTime >= '2007-3-5 10:00:00' and DbDateTime < '2007-3-5 10:05:00'"
queryout "table_name.txt" -c -SMySQLServer -T
I need the table name as "table_name_<timestamp>.txt"
Can someone help me with this please?
Thank you.
Regards,
Karthik
Karthik (Karthik@.discussions.microsoft.com) writes:
> I am currently running a bcp command to dump a table at run-time. This
> requirement has slightly changed and now I need to extract data based on
> certain time interval (once in 5 minutes). Because of this, I need to
> name my output files as <tablename>_<timestamp>.
> My current bcp command looks like this
> bcp "select * from asu..t_Skill_Group_Half_Hour WITH (NOLOCK) where
> DbDateTime >= '2007-3-5 10:00:00' and DbDateTime < '2007-3-5 10:05:00'"
> queryout "table_name.txt" -c -SMySQLServer -T
> I need the table name as "table_name_<timestamp>.txt"
> Can someone help me with this please?
From where do you run this BCP command?
An CmdExec job in SQL Server Agent?
A stored procedure?
A BAT file?
Something else?
If you need to extract data as often as every five minutes, I can't escape
the reflection that it may be worth considering using replication.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Hi Erland,
I run this from a SQL Server Stored procedure (which is scheduled to run
once in 5 mins).
I can't use replication as I dont need deletes happening on the publisher to
be propogated. That is why I am using this way to pull the data based on the
datetime column in tables I pull only data that has been inserted and not
deleted.
Thank you.
Regards,
Karthik
"Erland Sommarskog" wrote:

> Karthik (Karthik@.discussions.microsoft.com) writes:
> From where do you run this BCP command?
> An CmdExec job in SQL Server Agent?
> A stored procedure?
> A BAT file?
> Something else?
> If you need to extract data as often as every five minutes, I can't escape
> the reflection that it may be worth considering using replication.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
|||Karthik (Karthik@.discussions.microsoft.com) writes:
> I run this from a SQL Server Stored procedure (which is scheduled to run
> once in 5 mins).
So your current code goes something like this:
SELECT @.cmd = 'bcp "select * from asu..t_Skill_Group_Half_Hour WITH ' +
'(NOLOCK) where DbDateTime >= ''' + convert(char(19), @.start, 126) +
''' and DbDateTime < ''' + convert(char(19), @.stop, 126) +
'queryout "table_name.txt" -c -SMySQLServer -T'
Just change it to:
SELECT @.time = convert(char(8), @.start, 112) +
replace(convert(char(8), @.start, 108), ':', '')
SELECT @.cmd = 'bcp "select * from asu..t_Skill_Group_Half_Hour WITH ' +
'(NOLOCK) where DbDateTime >= ''' + convert(char(19), @.start, 126) +
''' and DbDateTime < ''' + convert(char(19), @.stop, 126) +
'queryout "table_name_' + @.time + '.txt" -c -SMySQLServer -T'
Or am I miassing something?

> I can't use replication as I dont need deletes happening on the publisher
> to be propogated.
The little I know of replication tells me that is possible to configure
so that it does not happen. But it's true that would make the replication
venture more complex.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

2012年2月16日星期四

batching an update, use TRANSACTION?

I have a storedproc that does several UPDATEs to a table, some of which fire
and some of which don't based on various IFs through the proc. There's a
maximum f three, it's not THAT complex.
However there is a trigger on the table that records all UPDATEs into a
separate auditing log. I would like there to be only one trigger fire
regardless if one, two or three of the UPDATEs were called.
Does TRANSACTION do this? I do _not_ want to turn off the triggers in the
proc.
MauryIf I understand you correctly, TRANSACTION will not do it. Can you put in
some logic in your trigger to check whether a previous UPDATE has already
fired the trigger?
Linchi
"Maury Markowitz" wrote:
> I have a storedproc that does several UPDATEs to a table, some of which fire
> and some of which don't based on various IFs through the proc. There's a
> maximum f three, it's not THAT complex.
> However there is a trigger on the table that records all UPDATEs into a
> separate auditing log. I would like there to be only one trigger fire
> regardless if one, two or three of the UPDATEs were called.
> Does TRANSACTION do this? I do _not_ want to turn off the triggers in the
> proc.
> Maury|||"Linchi Shea" wrote:
> If I understand you correctly, TRANSACTION will not do it. Can you put in
> some logic in your trigger to check whether a previous UPDATE has already
> fired the trigger?
I guess, but only with peril.
Maury|||"Linchi Shea" wrote:
> If I understand you correctly, TRANSACTION will not do it. Can you put in
> some logic in your trigger to check whether a previous UPDATE has already
> fired the trigger?
Can I perhaps wrap the individual fields of the update in some sort of
conditional, and thereby combine them into one larger statement? Everything
is already loaded into local vars.
Maury

2012年2月13日星期一

Batch or command to run reports

I have a question regarding How to run reports through a batch file or a
command line command.
Scenario:
We have a structure of reports based on State. User wants these reports
based on Country (Which includeds all states). It is
not possible to render these reports by Country. We are allowing user
through UI to pick one State at a time and get report
for that state.
Question: Is there any way I can run these reports through Batch file or
Command line, so that reports will get generated in
.PDF files through batch for the Country and get saved in local folder?
Is there any utility or third party tool available to do this?
Thanks,
SamThe same UI you can use to generate reports. Btw have you used asp.net for
your UI. For scripts just check the samples which has a good eg.
Amarnath
"Sam" wrote:
> I have a question regarding How to run reports through a batch file or a
> command line command.
> Scenario:
> We have a structure of reports based on State. User wants these reports
> based on Country (Which includeds all states). It is
> not possible to render these reports by Country. We are allowing user
> through UI to pick one State at a time and get report
> for that state.
> Question: Is there any way I can run these reports through Batch file or
> Command line, so that reports will get generated in
> .PDF files through batch for the Country and get saved in local folder?
> Is there any utility or third party tool available to do this?
> Thanks,
> Sam

Batch file and use of isql command

Hi,
I'm trying to figure out how to be able to run a batch
file, go to the database, find the current application
version of our product and based on that result it
returns, run a specific script. We have a table that
holds all of the builds/releases of the application that
have been applied against the database. Based on the
information there (the max build/release number), I want
it to execute a specific SQL script that will upgrade them
to the next build/release.
For example, we have a table called DBVersion with one
column in it (AppsVersion). In this table, it has rows
that are as follows:
8.0.100.0
8.0.101.0
8.0.102.0
We have two upgrade paths that we will support. In the
example above, we can upgrade a customer who are in
8.0.100.0 or 8.0.102.0 to the new release number
8.0.103.0. We have two upgrade scripts...one that holds
all db changes from 8.0.100.0 to 8.0.103.0 and one that
holds all db changes from 8.0.102.0 to 8.0.103.0. I want
to create a batch file that finds the version they are on
and run a specific script to upgrade them to the new
release.
Thank you in advance,
Dee
Hi, this line of SQL maybe helps you
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'),
SERVERPROPERTY ('edition')
greatings, Cesar
"Dee" wrote:

> Hi,
> I'm trying to figure out how to be able to run a batch
> file, go to the database, find the current application
> version of our product and based on that result it
> returns, run a specific script. We have a table that
> holds all of the builds/releases of the application that
> have been applied against the database. Based on the
> information there (the max build/release number), I want
> it to execute a specific SQL script that will upgrade them
> to the next build/release.
> For example, we have a table called DBVersion with one
> column in it (AppsVersion). In this table, it has rows
> that are as follows:
> 8.0.100.0
> 8.0.101.0
> 8.0.102.0
> We have two upgrade paths that we will support. In the
> example above, we can upgrade a customer who are in
> 8.0.100.0 or 8.0.102.0 to the new release number
> 8.0.103.0. We have two upgrade scripts...one that holds
> all db changes from 8.0.100.0 to 8.0.103.0 and one that
> holds all db changes from 8.0.102.0 to 8.0.103.0. I want
> to create a batch file that finds the version they are on
> and run a specific script to upgrade them to the new
> release.
> Thank you in advance,
> Dee
>
|||Hi Cesar,
Thank you for your response. Actually, the version that I
was implying was our own product's application version and
not SQL Server's.
Thanks again,
Dee
>--Original Message--
>Hi, this line of SQL maybe helps you
>SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY
('productlevel'),[vbcol=seagreen]
>SERVERPROPERTY ('edition')
>greatings, Cesar
>"Dee" wrote:
that[vbcol=seagreen]
want[vbcol=seagreen]
them[vbcol=seagreen]
holds[vbcol=seagreen]
want[vbcol=seagreen]
on
>.
>

Batch file and use of isql command

Hi,
I'm trying to figure out how to be able to run a batch
file, go to the database, find the current application
version of our product and based on that result it
returns, run a specific script. We have a table that
holds all of the builds/releases of the application that
have been applied against the database. Based on the
information there (the max build/release number), I want
it to execute a specific SQL script that will upgrade them
to the next build/release.
For example, we have a table called DBVersion with one
column in it (AppsVersion). In this table, it has rows
that are as follows:
8.0.100.0
8.0.101.0
8.0.102.0
We have two upgrade paths that we will support. In the
example above, we can upgrade a customer who are in
8.0.100.0 or 8.0.102.0 to the new release number
8.0.103.0. We have two upgrade scripts...one that holds
all db changes from 8.0.100.0 to 8.0.103.0 and one that
holds all db changes from 8.0.102.0 to 8.0.103.0. I want
to create a batch file that finds the version they are on
and run a specific script to upgrade them to the new
release.
Thank you in advance,
DeeHi, this line of SQL maybe helps you
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'),
SERVERPROPERTY ('edition')
greatings, Cesar
"Dee" wrote:

> Hi,
> I'm trying to figure out how to be able to run a batch
> file, go to the database, find the current application
> version of our product and based on that result it
> returns, run a specific script. We have a table that
> holds all of the builds/releases of the application that
> have been applied against the database. Based on the
> information there (the max build/release number), I want
> it to execute a specific SQL script that will upgrade them
> to the next build/release.
> For example, we have a table called DBVersion with one
> column in it (AppsVersion). In this table, it has rows
> that are as follows:
> 8.0.100.0
> 8.0.101.0
> 8.0.102.0
> We have two upgrade paths that we will support. In the
> example above, we can upgrade a customer who are in
> 8.0.100.0 or 8.0.102.0 to the new release number
> 8.0.103.0. We have two upgrade scripts...one that holds
> all db changes from 8.0.100.0 to 8.0.103.0 and one that
> holds all db changes from 8.0.102.0 to 8.0.103.0. I want
> to create a batch file that finds the version they are on
> and run a specific script to upgrade them to the new
> release.
> Thank you in advance,
> Dee
>|||Hi Cesar,
Thank you for your response. Actually, the version that I
was implying was our own product's application version and
not SQL Server's.
Thanks again,
Dee
>--Original Message--
>Hi, this line of SQL maybe helps you
>SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY
('productlevel'),
>SERVERPROPERTY ('edition')
>greatings, Cesar
>"Dee" wrote:
>
that[vbcol=seagreen]
want[vbcol=seagreen]
them[vbcol=seagreen]
holds[vbcol=seagreen]
want[vbcol=seagreen]
on[vbcol=seagreen]
>.
>

2012年2月12日星期日

Basket Analysis

Hi everyone,

Who knows how to write a MDX to do basket analysis based on cube?

Thanks

Hello! I think that this is a task for the distinct count aggregation method on the measure you have choosen,

You have also the option to work with data mining in SSAS2005 for this analytic topic.

HTH

Thomas Ivarsson

|||

Incase if you use Predictive Modelling algorithm in datamining for Market Basket Analysis, Use DMX to achieve this.

Thanks

Subhash Subramanyam

|||

Sacha has just done a really good blog on this:

http://blogs.adatis.co.uk/blogs/sachatomey/archive/2007/08/22/basket-analysis-using-analysis-services-2005.aspx

HTH

Tim

Basket Analysis

Hi everyone,

Who knows how to write a MDX to do basket analysis based on cube?

Thanks

Hello! I think that this is a task for the distinct count aggregation method on the measure you have choosen,

You have also the option to work with data mining in SSAS2005 for this analytic topic.

HTH

Thomas Ivarsson

|||

Incase if you use Predictive Modelling algorithm in datamining for Market Basket Analysis, Use DMX to achieve this.

Thanks

Subhash Subramanyam

|||

Sacha has just done a really good blog on this:

http://blogs.adatis.co.uk/blogs/sachatomey/archive/2007/08/22/basket-analysis-using-analysis-services-2005.aspx

HTH

Tim