Showing posts with label multiple. Show all posts

Thursday, March 29, 2012

Dropping Multiple system tables

What is the best way to drop the 100's of %conlict% tables from a database
left over after replication was removed?Highlight them in EM and hit DELETE?
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
> What is the best way to drop the 100's of %conlict% tables from a database
> left over after replication was removed?|||EM doesn't allow you to delete "system" tables which is what they are.
"Michael C#" wrote:
> Highlight them in EM and hit DELETE?
> "Monte" <Monte@.discussions.microsoft.com> wrote in message
> news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
> > What is the best way to drop the 100's of %conlict% tables from a database
> > left over after replication was removed?
>
>|||Hence the question mark. Sorry.
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:ADD9FBFA-DA36-4EC1-B9C5-617A85002977@.microsoft.com...
> EM doesn't allow you to delete "system" tables which is what they are.
> "Michael C#" wrote:
>> Highlight them in EM and hit DELETE?
>> "Monte" <Monte@.discussions.microsoft.com> wrote in message
>> news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
>> > What is the best way to drop the 100's of %conlict% tables from a
>> > database
>> > left over after replication was removed?
>>|||Have you seen this article?
http://support.microsoft.com/default.aspx?scid=kb;en-us;324401 and
http://www.mcse.ms/archive95-2004-7-876589.html
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:ADD9FBFA-DA36-4EC1-B9C5-617A85002977@.microsoft.com...
> EM doesn't allow you to delete "system" tables which is what they are.
> "Michael C#" wrote:
>> Highlight them in EM and hit DELETE?
>> "Monte" <Monte@.discussions.microsoft.com> wrote in message
>> news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
>> > What is the best way to drop the 100's of %conlict% tables from a
>> > database
>> > left over after replication was removed?
>>

Sunday, March 25, 2012

Droping active connections

I have a database that is not droping connections when a
user logs out. Is there anyway to drop multiple
connections to a database? Or is there a way to drop all
connections for a specific user?
Thanks in advance,
SteveJeff,
Thanks. This worked great. It did exactly what I wanted
it to do with just a little bit of tweaking.
Thanks Again,
Steve
>--Original Message--
>there is no built-in way to drop all connections for a
>database or a user, but you can write your own stored
>procedure to exactly do that.
>-- Example
>declare @.dbname sysname
>set @.dbname = 'pubs'
>declare c1 cursor for
>select cast('kill ' as nchar(6)) + cast(spid as nchar
(5))
>KillCommand
> from master..sysprocesses
> where spid > 50
> and spid <> @.@.spid
> and program_Name not like 'SQLAgent%'
> and dbid = db_id(@.dbname)
>declare @.KillCommand nvarchar(60)
>open c1
>fetch c1 into @.KillCommand
>while @.@.fetch_status = 0
>begin
> print @.KillCommand
> exec sp_executesql @.KillCommand
> fetch c1 into @.KillCommand
>end
>close c1
>deallocate c1
>--
>>--Original Message--
>>I have a database that is not droping connections when
a
>>user logs out. Is there anyway to drop multiple
>>connections to a database? Or is there a way to drop
all
>>connections for a specific user?
>>Thanks in advance,
>>Steve
>>.
>.
>

Dropdownlist with multiple fields

How can I get two fields to appear side by side in the DropDownList? Ex. FName and LName

Thanks

Here'sone example.

Sunday, March 11, 2012

DROP IDENTITY from tables

Hello,
I have two questions.
My goal is to create a script which drops identities for multiple tables in
a database. So for example if: alter table alter column X int NO IDENTITY
was valid...which is not I would be all set. Dropping and Recreating the
column could work as long as the column gets added in the same order from
where it was deleted, but I was hoping for something sleaker (see code below).
In process of testing some scenarios for the above I uncovered the following
"weirdness"
I created a test table with an identity field and I execute the following
select:
select * from syscolumns c inner join sysobjects o on c.id = o.id
where o.name = 'test'
The result set shows one row for each field. The data on the identity field
row seems ok up to column "usertype" but all remaining data values are
shifted one position out. So value for "prec" shows under "scale".. and so
on for 32 fields
I bet you can recreate this issue as well. Create a table with one identity
field and run the select sql below.
Question 1: Is this is a bug?
Question 2: Is the code below appropriate to drop identities or are there
any issues with updating the syscolumns table this way.
sp_configure 'allow updates', 1;
RECONFIGURE WITH OVERRIDE;
update syscolumns
set colstat = 0,
autoval = NULL
where id = (select id from sysobjects where name = 'test')
and colstat = 1
sp_configure 'allow updates', 0;
RECONFIGURE;
Thank You in advance,
John
John,
Updating the system table is usually not a good practice. Can you just
create a new table and load the data from the existing table (once for each
table)? If you do decide to update the system tables I'd recommend you
backup the database first.
HTH
Jerry
"John K" <JohnK@.discussions.microsoft.com> wrote in message
news:8AC208F0-EB75-424B-A2D8-9D94A01885D5@.microsoft.com...
> Hello,
> I have two questions.
> My goal is to create a script which drops identities for multiple tables
> in
> a database. So for example if: alter table alter column X int NO IDENTITY
> was valid...which is not I would be all set. Dropping and Recreating the
> column could work as long as the column gets added in the same order from
> where it was deleted, but I was hoping for something sleaker (see code
> below).
> In process of testing some scenarios for the above I uncovered the
> following
> "weirdness"
> I created a test table with an identity field and I execute the following
> select:
> select * from syscolumns c inner join sysobjects o on c.id = o.id
> where o.name = 'test'
> The result set shows one row for each field. The data on the identity
> field
> row seems ok up to column "usertype" but all remaining data values are
> shifted one position out. So value for "prec" shows under "scale".. and
> so
> on for 32 fields
> I bet you can recreate this issue as well. Create a table with one
> identity
> field and run the select sql below.
> Question 1: Is this is a bug?
> --
> Question 2: Is the code below appropriate to drop identities or are there
> any issues with updating the syscolumns table this way.
> sp_configure 'allow updates', 1;
> RECONFIGURE WITH OVERRIDE;
> update syscolumns
> set colstat = 0,
> autoval = NULL
> where id = (select id from sysobjects where name = 'test')
> and colstat = 1
> sp_configure 'allow updates', 0;
> RECONFIGURE;
> --
> Thank You in advance,
> John
|||"John K" <JohnK@.discussions.microsoft.com> wrote in message
news:8AC208F0-EB75-424B-A2D8-9D94A01885D5@.microsoft.com...
> Hello,
> I have two questions.
> My goal is to create a script which drops identities for multiple tables
> in
> a database. So for example if: alter table alter column X int NO IDENTITY
> was valid...which is not I would be all set. Dropping and Recreating the
> column could work as long as the column gets added in the same order from
> where it was deleted, but I was hoping for something sleaker (see code
> below).
> In process of testing some scenarios for the above I uncovered the
> following
> "weirdness"
> I created a test table with an identity field and I execute the following
> select:
> select * from syscolumns c inner join sysobjects o on c.id = o.id
> where o.name = 'test'
> The result set shows one row for each field. The data on the identity
> field
> row seems ok up to column "usertype" but all remaining data values are
> shifted one position out. So value for "prec" shows under "scale".. and
> so
> on for 32 fields
> I bet you can recreate this issue as well. Create a table with one
> identity
> field and run the select sql below.
> Question 1: Is this is a bug?
> --
> Question 2: Is the code below appropriate to drop identities or are there
> any issues with updating the syscolumns table this way.
> sp_configure 'allow updates', 1;
> RECONFIGURE WITH OVERRIDE;
> update syscolumns
> set colstat = 0,
> autoval = NULL
> where id = (select id from sysobjects where name = 'test')
> and colstat = 1
> sp_configure 'allow updates', 0;
> RECONFIGURE;
> --
> Thank You in advance,
> John
Updating system tables directly is the easiest route to corrupt and
unrecoverable data.
Add a new column. Populate it. Drop the old column. Forget about modifying
system tables.
David Portas
SQL Server MVP
|||John K wrote:
> Hello,
> I have two questions.
> SNIP
Create a new table with the same ddl, sans identity attribute. Insert
the data from old to new. Create indexes. Drop the old table. Rename the
new table. Run sp_recompile on each procedure that accesses the table.
If you have any DRI involved, it makes the process more difficult.
Messing with the system tables is a surefire way to cause the database
to be marked suspect or cause any number of other operational / support
issues.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Ok, no system table changes.
I don't care to move the data, but I do have hundreds of tables and I do
need the new column to be at the same physical order the old one existed. So
how do I script this operation to create a new column of int and drop the old
identity one in the same spot (column order)?
John
"David Gugick" wrote:

> John K wrote:
> Create a new table with the same ddl, sans identity attribute. Insert
> the data from old to new. Create indexes. Drop the old table. Rename the
> new table. Run sp_recompile on each procedure that accesses the table.
> If you have any DRI involved, it makes the process more difficult.
> Messing with the system tables is a surefire way to cause the database
> to be marked suspect or cause any number of other operational / support
> issues.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||John K wrote:
> Ok, no system table changes.
> I don't care to move the data, but I do have hundreds of tables and I
> do need the new column to be at the same physical order the old one
> existed. So how do I script this operation to create a new column of
> int and drop the old identity one in the same spot (column order)?
You need to create a new table as I mentioned in the previous post. You
can use the INFORMATION_SCHEMA.COLUMNS view to query the table and sort
by ORDINAL_POSITION to get the correct order. You can do this in your
favorite development tool (my preference) or do this from a stored
procedure using dynamic sql (a valid alternative, but the coding and
debugging is less elegant).
Indexes are more involved, but that information is also available from
the INFORMATION_SCHEMA views. Keys make it more involved as well. Also,
pay attention to the location of tables / clustered indexes and
non-clustered indexes / keys so you don't end up creating these objects
in a different location from where they were originally located.
If you want to see how SQL Enterprise does this, create a test table
with an identity column and change the identity attribute. Rather than
applying the change, have SQL EM script out the T-SQL.
Why is the physical order important? Physical positioning should not be
of any importance. You can always query the columns in the order you'd
like to see them returned.
David Gugick
Quest Software
www.imceda.com
www.quest.com

DROP IDENTITY from tables

Hello,
I have two questions.
My goal is to create a script which drops identities for multiple tables in
a database. So for example if: alter table alter column X int NO IDENTITY
was valid...which is not I would be all set. Dropping and Recreating the
column could work as long as the column gets added in the same order from
where it was deleted, but I was hoping for something sleaker (see code below
).
In process of testing some scenarios for the above I uncovered the following
"weirdness"
I created a test table with an identity field and I execute the following
select:
select * from syscolumns c inner join sysobjects o on c.id = o.id
where o.name = 'test'
The result set shows one row for each field. The data on the identity field
row seems ok up to column "usertype" but all remaining data values are
shifted one position out. So value for "prec" shows under "scale".. and so
on for 32 fields
I bet you can recreate this issue as well. Create a table with one identity
field and run the select sql below.
Question 1: Is this is a bug?
Question 2: Is the code below appropriate to drop identities or are there
any issues with updating the syscolumns table this way.
sp_configure 'allow updates', 1;
RECONFIGURE WITH OVERRIDE;
update syscolumns
set colstat = 0,
autoval = NULL
where id = (select id from sysobjects where name = 'test')
and colstat = 1
sp_configure 'allow updates', 0;
RECONFIGURE;
Thank You in advance,
JohnJohn,
Updating the system table is usually not a good practice. Can you just
create a new table and load the data from the existing table (once for each
table)? If you do decide to update the system tables I'd recommend you
backup the database first.
HTH
Jerry
"John K" <JohnK@.discussions.microsoft.com> wrote in message
news:8AC208F0-EB75-424B-A2D8-9D94A01885D5@.microsoft.com...
> Hello,
> I have two questions.
> My goal is to create a script which drops identities for multiple tables
> in
> a database. So for example if: alter table alter column X int NO IDENTITY
> was valid...which is not I would be all set. Dropping and Recreating the
> column could work as long as the column gets added in the same order from
> where it was deleted, but I was hoping for something sleaker (see code
> below).
> In process of testing some scenarios for the above I uncovered the
> following
> "weirdness"
> I created a test table with an identity field and I execute the following
> select:
> select * from syscolumns c inner join sysobjects o on c.id = o.id
> where o.name = 'test'
> The result set shows one row for each field. The data on the identity
> field
> row seems ok up to column "usertype" but all remaining data values are
> shifted one position out. So value for "prec" shows under "scale".. and
> so
> on for 32 fields
> I bet you can recreate this issue as well. Create a table with one
> identity
> field and run the select sql below.
> Question 1: Is this is a bug?
> --
> Question 2: Is the code below appropriate to drop identities or are there
> any issues with updating the syscolumns table this way.
> sp_configure 'allow updates', 1;
> RECONFIGURE WITH OVERRIDE;
> update syscolumns
> set colstat = 0,
> autoval = NULL
> where id = (select id from sysobjects where name = 'test')
> and colstat = 1
> sp_configure 'allow updates', 0;
> RECONFIGURE;
> --
> Thank You in advance,
> John|||"John K" <JohnK@.discussions.microsoft.com> wrote in message
news:8AC208F0-EB75-424B-A2D8-9D94A01885D5@.microsoft.com...
> Hello,
> I have two questions.
> My goal is to create a script which drops identities for multiple tables
> in
> a database. So for example if: alter table alter column X int NO IDENTITY
> was valid...which is not I would be all set. Dropping and Recreating the
> column could work as long as the column gets added in the same order from
> where it was deleted, but I was hoping for something sleaker (see code
> below).
> In process of testing some scenarios for the above I uncovered the
> following
> "weirdness"
> I created a test table with an identity field and I execute the following
> select:
> select * from syscolumns c inner join sysobjects o on c.id = o.id
> where o.name = 'test'
> The result set shows one row for each field. The data on the identity
> field
> row seems ok up to column "usertype" but all remaining data values are
> shifted one position out. So value for "prec" shows under "scale".. and
> so
> on for 32 fields
> I bet you can recreate this issue as well. Create a table with one
> identity
> field and run the select sql below.
> Question 1: Is this is a bug?
> --
> Question 2: Is the code below appropriate to drop identities or are there
> any issues with updating the syscolumns table this way.
> sp_configure 'allow updates', 1;
> RECONFIGURE WITH OVERRIDE;
> update syscolumns
> set colstat = 0,
> autoval = NULL
> where id = (select id from sysobjects where name = 'test')
> and colstat = 1
> sp_configure 'allow updates', 0;
> RECONFIGURE;
> --
> Thank You in advance,
> John
Updating system tables directly is the easiest route to corrupt and
unrecoverable data.
Add a new column. Populate it. Drop the old column. Forget about modifying
system tables.
David Portas
SQL Server MVP
--|||John K wrote:
> Hello,
> I have two questions.
> SNIP
Create a new table with the same ddl, sans identity attribute. Insert
the data from old to new. Create indexes. Drop the old table. Rename the
new table. Run sp_recompile on each procedure that accesses the table.
If you have any DRI involved, it makes the process more difficult.
Messing with the system tables is a surefire way to cause the database
to be marked suspect or cause any number of other operational / support
issues.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Ok, no system table changes.
I don't care to move the data, but I do have hundreds of tables and I do
need the new column to be at the same physical order the old one existed. S
o
how do I script this operation to create a new column of int and drop the ol
d
identity one in the same spot (column order)?
John
"David Gugick" wrote:

> John K wrote:
> Create a new table with the same ddl, sans identity attribute. Insert
> the data from old to new. Create indexes. Drop the old table. Rename the
> new table. Run sp_recompile on each procedure that accesses the table.
> If you have any DRI involved, it makes the process more difficult.
> Messing with the system tables is a surefire way to cause the database
> to be marked suspect or cause any number of other operational / support
> issues.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||John K wrote:
> Ok, no system table changes.
> I don't care to move the data, but I do have hundreds of tables and I
> do need the new column to be at the same physical order the old one
> existed. So how do I script this operation to create a new column of
> int and drop the old identity one in the same spot (column order)?
You need to create a new table as I mentioned in the previous post. You
can use the INFORMATION_SCHEMA.COLUMNS view to query the table and sort
by ORDINAL_POSITION to get the correct order. You can do this in your
favorite development tool (my preference) or do this from a stored
procedure using dynamic sql (a valid alternative, but the coding and
debugging is less elegant).
Indexes are more involved, but that information is also available from
the INFORMATION_SCHEMA views. Keys make it more involved as well. Also,
pay attention to the location of tables / clustered indexes and
non-clustered indexes / keys so you don't end up creating these objects
in a different location from where they were originally located.
If you want to see how SQL Enterprise does this, create a test table
with an identity column and change the identity attribute. Rather than
applying the change, have SQL EM script out the T-SQL.
Why is the physical order important? Physical positioning should not be
of any importance. You can always query the columns in the order you'd
like to see them returned.
David Gugick
Quest Software
www.imceda.com
www.quest.com

DROP IDENTITY from tables

Hello,
I have two questions.
My goal is to create a script which drops identities for multiple tables in
a database. So for example if: alter table alter column X int NO IDENTITY
was valid...which is not I would be all set. Dropping and Recreating the
column could work as long as the column gets added in the same order from
where it was deleted, but I was hoping for something sleaker (see code below).
In process of testing some scenarios for the above I uncovered the following
"weirdness"
I created a test table with an identity field and I execute the following
select:
select * from syscolumns c inner join sysobjects o on c.id = o.id
where o.name = 'test'
The result set shows one row for each field. The data on the identity field
row seems ok up to column "usertype" but all remaining data values are
shifted one position out. So value for "prec" shows under "scale".. and so
on for 32 fields
I bet you can recreate this issue as well. Create a table with one identity
field and run the select sql below.
Question 1: Is this is a bug?
--
Question 2: Is the code below appropriate to drop identities or are there
any issues with updating the syscolumns table this way.
sp_configure 'allow updates', 1;
RECONFIGURE WITH OVERRIDE;
update syscolumns
set colstat = 0,
autoval = NULL
where id = (select id from sysobjects where name = 'test')
and colstat = 1
sp_configure 'allow updates', 0;
RECONFIGURE;
--
Thank You in advance,
JohnJohn,
Updating the system table is usually not a good practice. Can you just
create a new table and load the data from the existing table (once for each
table)? If you do decide to update the system tables I'd recommend you
backup the database first.
HTH
Jerry
"John K" <JohnK@.discussions.microsoft.com> wrote in message
news:8AC208F0-EB75-424B-A2D8-9D94A01885D5@.microsoft.com...
> Hello,
> I have two questions.
> My goal is to create a script which drops identities for multiple tables
> in
> a database. So for example if: alter table alter column X int NO IDENTITY
> was valid...which is not I would be all set. Dropping and Recreating the
> column could work as long as the column gets added in the same order from
> where it was deleted, but I was hoping for something sleaker (see code
> below).
> In process of testing some scenarios for the above I uncovered the
> following
> "weirdness"
> I created a test table with an identity field and I execute the following
> select:
> select * from syscolumns c inner join sysobjects o on c.id = o.id
> where o.name = 'test'
> The result set shows one row for each field. The data on the identity
> field
> row seems ok up to column "usertype" but all remaining data values are
> shifted one position out. So value for "prec" shows under "scale".. and
> so
> on for 32 fields
> I bet you can recreate this issue as well. Create a table with one
> identity
> field and run the select sql below.
> Question 1: Is this is a bug?
> --
> Question 2: Is the code below appropriate to drop identities or are there
> any issues with updating the syscolumns table this way.
> sp_configure 'allow updates', 1;
> RECONFIGURE WITH OVERRIDE;
> update syscolumns
> set colstat = 0,
> autoval = NULL
> where id = (select id from sysobjects where name = 'test')
> and colstat = 1
> sp_configure 'allow updates', 0;
> RECONFIGURE;
> --
> Thank You in advance,
> John|||"John K" <JohnK@.discussions.microsoft.com> wrote in message
news:8AC208F0-EB75-424B-A2D8-9D94A01885D5@.microsoft.com...
> Hello,
> I have two questions.
> My goal is to create a script which drops identities for multiple tables
> in
> a database. So for example if: alter table alter column X int NO IDENTITY
> was valid...which is not I would be all set. Dropping and Recreating the
> column could work as long as the column gets added in the same order from
> where it was deleted, but I was hoping for something sleaker (see code
> below).
> In process of testing some scenarios for the above I uncovered the
> following
> "weirdness"
> I created a test table with an identity field and I execute the following
> select:
> select * from syscolumns c inner join sysobjects o on c.id = o.id
> where o.name = 'test'
> The result set shows one row for each field. The data on the identity
> field
> row seems ok up to column "usertype" but all remaining data values are
> shifted one position out. So value for "prec" shows under "scale".. and
> so
> on for 32 fields
> I bet you can recreate this issue as well. Create a table with one
> identity
> field and run the select sql below.
> Question 1: Is this is a bug?
> --
> Question 2: Is the code below appropriate to drop identities or are there
> any issues with updating the syscolumns table this way.
> sp_configure 'allow updates', 1;
> RECONFIGURE WITH OVERRIDE;
> update syscolumns
> set colstat = 0,
> autoval = NULL
> where id = (select id from sysobjects where name = 'test')
> and colstat = 1
> sp_configure 'allow updates', 0;
> RECONFIGURE;
> --
> Thank You in advance,
> John
Updating system tables directly is the easiest route to corrupt and
unrecoverable data.
Add a new column. Populate it. Drop the old column. Forget about modifying
system tables.
--
David Portas
SQL Server MVP
--|||John K wrote:
> Hello,
> I have two questions.
> SNIP
Create a new table with the same ddl, sans identity attribute. Insert
the data from old to new. Create indexes. Drop the old table. Rename the
new table. Run sp_recompile on each procedure that accesses the table.
If you have any DRI involved, it makes the process more difficult.
Messing with the system tables is a surefire way to cause the database
to be marked suspect or cause any number of other operational / support
issues.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Ok, no system table changes.
I don't care to move the data, but I do have hundreds of tables and I do
need the new column to be at the same physical order the old one existed. So
how do I script this operation to create a new column of int and drop the old
identity one in the same spot (column order)?
--
John
"David Gugick" wrote:
> John K wrote:
> > Hello,
> >
> > I have two questions.
> >
> > SNIP
> Create a new table with the same ddl, sans identity attribute. Insert
> the data from old to new. Create indexes. Drop the old table. Rename the
> new table. Run sp_recompile on each procedure that accesses the table.
> If you have any DRI involved, it makes the process more difficult.
> Messing with the system tables is a surefire way to cause the database
> to be marked suspect or cause any number of other operational / support
> issues.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||John K wrote:
> Ok, no system table changes.
> I don't care to move the data, but I do have hundreds of tables and I
> do need the new column to be at the same physical order the old one
> existed. So how do I script this operation to create a new column of
> int and drop the old identity one in the same spot (column order)?
You need to create a new table as I mentioned in the previous post. You
can use the INFORMATION_SCHEMA.COLUMNS view to query the table and sort
by ORDINAL_POSITION to get the correct order. You can do this in your
favorite development tool (my preference) or do this from a stored
procedure using dynamic sql (a valid alternative, but the coding and
debugging is less elegant).
Indexes are more involved, but that information is also available from
the INFORMATION_SCHEMA views. Keys make it more involved as well. Also,
pay attention to the location of tables / clustered indexes and
non-clustered indexes / keys so you don't end up creating these objects
in a different location from where they were originally located.
If you want to see how SQL Enterprise does this, create a test table
with an identity column and change the identity attribute. Rather than
applying the change, have SQL EM script out the T-SQL.
Why is the physical order important? Physical positioning should not be
of any importance. You can always query the columns in the order you'd
like to see them returned.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Drop Down Lists

I'm want to be able to choose(including multiple selections) names from a drop down list that are fetched from the database(from table "Employees") and add them to table "Biotechnology".

Employees(ID,FirstName,LastName)
Biotechnology(ID,EmployeeID)

**assuming that EmployeeID in "Biotechnology" is the ID from "Employees" table

Example: We add Employees.ID(23, 33) to Biotechnology.EmployeeID

would be

Biotechnology:
[ID] [EmployeeID]
01 - 23
02 - 33Looks like an INSERT statement to me, possibly complicated by your user interface. What's the problem?

Friday, February 24, 2012

Drillthrough with multiple tables

Hi,

I'm having a bit of trouble with drillthrough, hope someone can help.

When the user drills through, I would like them to retrieve some records that are located in another table - not in the fact table.

E.g. if they drill down on one record of with a data value of 100, they should see 4 related source records that reside in another table.

At the moment, when the user drills down, they always see the entire contents of the other table. Of course it should only show you the records that apply to the value you're drilling on.

I've tried several things, setting up a relationship in the cube schema, and setting up a relationship in SQL Server between the two tables. Either way I always get the whole table back.

Can anyone help

Thanks
Jeremy

If you're talking about SQL Server 2005, then see the MSDN whitepaper http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5_anservdrill.asp. It has an example of drillthrough to an alternate fact table.

If this is SQL Server 2000, you can troubleshoot by running Profiler and capturing the drillthrough SQL query issued by Analysis Services. Maybe its not joining the alternate table with the fact table. Try including a drillthrough column from the fact table as well.

Drillthrough returning sets of duplicate rows

Drillthrough appears to be showing multiple rows for each single record that should be displayed.

For example, in my fact table, there is a single record for each trip. The following MDX query returns a single cell with a trip count of three.

SELECT NON EMPTY { [CP Date].[Date by Month].[Year].&[2002] } ON COLUMNS ,
NON EMPTY { [Commodity].[Commodity by Type and Group].[Commodity Type].&[Other] } ON ROWS
FROM [Barge Trip]
WHERE ( [Measures].[Trip Count] )

2002

Other

If I drill through on that cell, exactly three records should be returned. However, in actuality, 12 rows are returned - three sets each having four identical rows. To drill through, I am simply adding the DRILLTHROUGH keyword to the MDX query shown above as follows:

DRILLTHROUGH
SELECT NON EMPTY { [CP Date].[Date by Month].[Year].&[2002] } ON COLUMNS ,
NON EMPTY { [Commodity].[Commodity by Type and Group].[Commodity Type].&[Other] } ON ROWS
FROM [Barge Trip]
WHERE ( [Measures].[Trip Count] )

The relationships between my fact table and my dimension tables are quite simple. Can anyone suggest any theories about what might be causing this?

Are you using AS 2000 or AS 2005 - there was once an issue like this in AS 2000, when the cube schema had been optimized...|||I am using AS2005.|||In that case, did you configure MOLAP or ROLAP storage for the fact dimension of the measure group containing [Measures].[Trip Count] - if it's ROLAP you should be able to trace the relational query to the fact table? And is [Measures].[Trip Count] a straight "count" measure on the measure group?|||

For the record, I am now executing my drill throughs from ProClarity and am still getting back the four rows for each "real" fact record.

With regard to the second question question about [Measures].[Trip Count], it is a straight "count" measure on the measure group.

With regard to MOLAP/ROLAP, my partition was originally configured for MOLAP. Per your response, I configured the single partition associated with the measure group in question to "ROLAP 30%". I ran Query Profiler to attempt to trace the query for the drill through. I started two profiler windows - one for Analysis Services and one for the database engine. I then executed the drill through operation. I can see the MDX query in the Analysis Services profiler trace, but I see no Transact SQL query in the database engine trace. The MDX query I am seeing is...

DRILLTHROUGH MAXROWS 1000 SELECT { ( [CP Date].[Date by Month].[Year].&[2005] ) } ON COLUMNS ,
{ ( [Commodity].[Commodity by Type and Group].[Commodity Type].&[Unspecified] ) } ON ROWS
FROM [Barge Trip]
WHERE ( [Measures].[Trip Count], [Barge].[Barge].[All], [Barge].[Hull Depth].[All], [Barge].[Cover Type].[All], [Barge].[Barge Type].[All], [Barge].[Year Built].[All], [Barge].[Shipyard Built].[All], [Barge].[Model Name].[All], [Barge].[Model Description].[All], [Barge Trip].[Barge Trip].[All], [Barge Trip].[Direction].[All], [Barge Trip].[Company].[All], [Barge Trip].[Cover Status].[All], [Barge Trip].[Load Status].[All], [Commodity].[Commodity Type].[All], [Commodity].[Commodity Group].[All], [CP Date].[Date by Week].[All], [CP Date].[Day of Week].[All], [CP Date].[Week of Year].[All], [CP Date].[Month of Year].[All], [CP Date].[Quarter of Year].[All], [CP Date].[Day Of Month].[All], [CP Date].[Date by Month CP Date Calculations].&[Current CP Date], [Customer Freight].[Customer Name].[All], [Trip Origin Location].[By Area].[All], [Trip Origin Location].[By River].[All], [Trip Origin Location].[Facility Name].[All], [Trip Origin Location].[Facility Category].[All], [Trip Origin Location].[Facility Type].[All], [Trip Origin Location].[Town].[All], [Trip Origin Location].[River Segment].[All], [Trip Origin Location].[Cycle Area].[All], [Trip Origin Location].[Owner].[All], [Trip Origin Location].[Operator].[All], [Prior Unload Location].[By Area].[All], [Prior Unload Location].[By River].[All], [Prior Unload Location].[Facility Name].[All], [Prior Unload Location].[Facility Category].[All], [Prior Unload Location].[Facility Type].[All], [Prior Unload Location].[Town].[All], [Prior Unload Location].[River Segment].[All], [Prior Unload Location].[Cycle Area].[All], [Prior Unload Location].[Owner].[All], [Prior Unload Location].[Operator].[All], [Release Date].[Date by Month].[All], [Release Date].[Date by Week].[All], [Release Date].[Day of Week].[All], [Release Date].[Week of Year].[All], [Release Date].[Month of Year].[All], [Release Date].[Quarter of Year].[All], [Release Date].[Date by Month Release Date Calculations].&[Current Release Date], [Trip Destination Location].[By Area].[All], [Trip Destination Location].[By River].[All], [Trip Destination Location].[Facility Name].[All], [Trip Destination Location].[Facility Category].[All], [Trip Destination Location].[Facility Type].[All], [Trip Destination Location].[Town].[All], [Trip Destination Location].[River Segment].[All], [Trip Destination Location].[Cycle Area].[All], [Trip Destination Location].[Owner].[All], [Trip Destination Location].[Operator].[All], [Trip Start Date].[Date by Month].[All], [Trip Start Date].[Date by Week].[All], [Trip Start Date].[Day of Week].[All], [Trip Start Date].[Week of Year].[All], [Trip Start Date].[Month of Year].[All], [Trip Start Date].[Quarter of Year].[All], [Trip Start Date].[Date by Month Trip Start Date Calculations].&[Current Trip Start Date], [Trip End Date].[Date by Month].[All], [Trip End Date].[Date by Week].[All], [Trip End Date].[Day of Week].[All], [Trip End Date].[Week of Year].[All], [Trip End Date].[Month of Year].[All], [Trip End Date].[Quarter of Year].[All], [Trip End Date].[Date by Month Trip End Date Calculations].&[Current Trip End Date] )

Thanks for your ongoing assistance.

|||

Actually, what I had in mind was configuring ROLAP storage for the "Fact" dimension of the measure group (for example, the [Internet Sales Order Details] ROLAP dimension in Adventure Works, which has a "Fact" relation to the "Internet Sales" measure group) - not for the measure group partition (though that should be OK either way). See if you could trace the relational SQL query, when you do that?

|||

I presume a "fact dimension" is simply a dimension based on the fact table. I did not have one for this cube, so I have attempted to set one up. My Rolap fact dimension processes fine, but when I try to browse it in the dimension browser, I get the following errors:

Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.
Errors in the OLAP storage engine: An error occurred while the 'Barge Trip Key' attribute of the 'Fact Barge Trip' dimension from the 'MEMCO Analysis' database was being processed.
Errors in the OLAP storage engine: Maximum number of ROLAP rows to process in one query has been exceeded.
Errors in the OLAP storage engine: Execution limit reached
Execution complete

If I change the dimension's storage mode to Molap and reprocess, then I can browse the dimension with no problem and it displays the dimension members I expect. In fact, I can incorporate it into the cube when it is in Molap storage mode and it aggregates correctly. Do you have any idea what's going on? I realize this is just a step toward solving the real problem, but I guess I need help with the step.

Thanks.

|||Since it seems that you may not be familiar with fact dimensions, you can study the configuration of [Internet Sales Order Details] in Adventure Works for guidance. After creating the fact dimension, you should define the Dimension Usage relation to its Measure Group as "Fact". Then you can retry your original DrillThrough query (with either MOLAP or ROLAP storage), to see if its results have changed.|||

I've been gone for a couple days, but now am back.

When I set up the fact dimension earlier this week, I did study the [Internet Sales Order Details] dimension as you suggest. In light of that example, I also had defined the "Fact" relationship between the [Barge Trip] measure group my new fact dimension [Fact Start Year] (I renamed the fact dimension from [Fact Barge Trip] to [Fact Start Year] since my prior entry). Unlike the Adventure Works example, my fact dimension is very simple involving only the existing fact table to which I added a single calculated column to provide a reasonable dimension. The new dimension has eight members.

When the new fact dimension is set to Molap, I can drill through in the same manner as previously described, and I still get the "multiple rows" returned for each underlying fact record. Here's the DRILLTHROUGH query that references my new "fact" dimension:

DRILLTHROUGH
SELECT { [Fact Start Year].[Trip Start Year].&[2005] } ON COLUMNS ,
{ [Commodity].[Commodity by Type and Group].[Commodity Type].&[Unspecified] } ON ROWS
FROM [Barge Trip]
WHERE ( [Measures].[Trip Count] )

However, if I modify the [Fact Start Year] dimension in the dimension editor changing the Storage Mode to Rolap, and then reprocess it, I am unable to browse the dimension. I get the errors that I previously mentioned when I try to browse the dimension. I can still reprocess the measure group. If I do, and then execute an MDX query against it, that also gets the same error...

Executing the query ...
Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.
Errors in the OLAP storage engine: An error occurred while the 'Trip Start Year' attribute of the 'Fact Start Year' dimension from the 'MEMCO Analysis' database was being processed.
Errors in the OLAP storage engine: Maximum number of ROLAP rows to process in one query has been exceeded.
Errors in the OLAP storage engine: Execution limit reached
Execution complete

Thoughts?

|||

A couple of thoughts:

- To resolve the ROLAP rows error, I recall that this SSAS Server Property has to be increased (can't find the relevant thread right now, but you can try tweaking it):

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssasproperties.mspx

SQL Server 2005 Analysis Services (SSAS) Server Properties

Published: June 26, 2006

SQL Server Technical Article

Writers: Anthony T. Mann, Edward Melomed

...

ROLAPDimensionProcessingEffort

ROLAPDimensionProcessingEffort is a server property that controls the maximum number of rows SSAS will obtain from a relational database in an attempt to resolve a query to a ROLAP dimension. If Analysis Server determines that the number of records that must be processed from the underlying database is greater than the value specified by this property, an error is returned to the client.

Property Name

General Page: OLAP \ Process \ ROLAPDimensionProcessingEffort

Default Value

300000

Unit of Measure

Data records

Data Type

Integer

Minimum Value

0 – No query to a ROLAP dimension will succeed.

Maximum Value

2147483647

Requires Restart

Alternate GUI Tool

None

Special Notes

None

Security Implications

None

- For the multiple rows returned in MOLAP mode, only thing I can think of checking is that the key attribute configured for the fact dimension is unique.

|||

Increasing ROLAPDimensionProcessingEffort to be greater than the number of rows in my underlying fact table (factBargeTrip) does allow my MDX queries to complete successfully when the StorageMode for my fact dimension is set to "Rolap". Thanks for the hint.

Now, when I execute a drillthrough, I am seeing a Transact SQL statement come through in the Profiler. The MDX query is still returning four rows when it ought to return one - even now that I am in Rolap mode. But if I grab the Transact SQL Query from profiler and execute it against the relational database underlying the cube, it only returns one row. The fact key (FactBargeTripKey) returned by the Transact SQL query matches the fact key (FactBargeTripKey) returned four times by the MDX query which I also see come over in profiler (I have both a db engine window and an AS window open in Profiler), so it appears that I am looking at the right thing.

Here's my most recent MDX query:

DRILLTHROUGH
SELECT { [Fact Start Year].[Trip Start Year].&[2005] } ON COLUMNS ,
{ [Commodity].[Commodity by Type and Group].[Commodity Group].&[Forest Products] } ON ROWS
FROM [Barge Trip]
WHERE ( [Measures].[Trip Count] )
CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, ACTION_TYPE

And, here's the associated SQL query generated by Analysis Services which returns a single row...

exec sp_executesql N'
SELECT SUM ( [dbo_factBargeTrip].[dbo_factBargeTripDemurrageCostConst0_0] )
AS [dbo_factBargeTripDemurrageCostConst0_0], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripCostCleaning0_1] )
AS [dbo_factBargeTripCostCleaning0_1], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripCostFleetingOrigin0_2] )
AS [dbo_factBargeTripCostFleetingOrigin0_2], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripCostFleetingDestination0_3] )
AS [dbo_factBargeTripCostFleetingDestination0_3], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripCostFleetingIntermediate0_4] )
AS [dbo_factBargeTripCostFleetingIntermediate0_4], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripCostFleeting0_5] )
AS [dbo_factBargeTripCostFleeting0_5], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripCostOther0_6] )
AS [dbo_factBargeTripCostOther0_6], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripCostShiftingOrigin0_7] )
AS [dbo_factBargeTripCostShiftingOrigin0_7], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripCostShiftingDestination0_8] )
AS [dbo_factBargeTripCostShiftingDestination0_8], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripCostShiftingIntermediate0_9] )
AS [dbo_factBargeTripCostShiftingIntermediate0_9], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripCostShifting0_10] )
AS [dbo_factBargeTripCostShifting0_10], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripCostTowing0_11] )
AS [dbo_factBargeTripCostTowing0_11], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripCostFleetToFleet0_12] )
AS [dbo_factBargeTripCostFleetToFleet0_12], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripCostPriorEmpty0_13] )
AS [dbo_factBargeTripCostPriorEmpty0_13], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripRevenueStorage0_14] )
AS [dbo_factBargeTripRevenueStorage0_14], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripRevenueOriginDemurrage0_15] )
AS [dbo_factBargeTripRevenueOriginDemurrage0_15], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripRevenueDestinationDemurrage0_16] )
AS [dbo_factBargeTripRevenueDestinationDemurrage0_16], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripRevenueAllDemurrage0_17] )
AS [dbo_factBargeTripRevenueAllDemurrage0_17], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripRevenueTotalDemurrage0_18] )
AS [dbo_factBargeTripRevenueTotalDemurrage0_18], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripRevenueTotalFreight0_19] )
AS [dbo_factBargeTripRevenueTotalFreight0_19], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripRevenueOther0_20] )
AS [dbo_factBargeTripRevenueOther0_20], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripTonsActual0_21] )
AS [dbo_factBargeTripTonsActual0_21], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripTonsBilled0_22] )
AS [dbo_factBargeTripTonsBilled0_22], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripTripDays0_23] )
AS [dbo_factBargeTripTripDays0_23], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripTripDaysAveraging0_24] )
AS [dbo_factBargeTripTripDaysAveraging0_24], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripTripDaysDemurrage0_25] )
AS [dbo_factBargeTripTripDaysDemurrage0_25], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripTripDaysEmpty0_26] )
AS [dbo_factBargeTripTripDaysEmpty0_26], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripTripDaysLoaded0_27] )
AS [dbo_factBargeTripTripDaysLoaded0_27], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripTripDaysFree0_28] )
AS [dbo_factBargeTripTripDaysFree0_28], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripTripDaysFreight0_29] )
AS [dbo_factBargeTripTripDaysFreight0_29], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripTripDaysStorage0_30] )
AS [dbo_factBargeTripTripDaysStorage0_30], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripDaysToLoad0_31] )
AS [dbo_factBargeTripDaysToLoad0_31], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripDaysToUnload0_32] )
AS [dbo_factBargeTripDaysToUnload0_32],
COUNT_BIG ( [dbo_factBargeTrip].[dbo_factBargeTrip0_33] )
AS [dbo_factBargeTrip0_33], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripDemurrageCost0_34] )
AS [dbo_factBargeTripDemurrageCost0_34], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripCostTotal0_35] )
AS [dbo_factBargeTripCostTotal0_35], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripRevenueTotal0_36] )
AS [dbo_factBargeTripRevenueTotal0_36], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripMargin_x0020_With_x0020_Demurrage0_37] )
AS [dbo_factBargeTripMargin_x0020_With_x0020_Demurrage0_37], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripMargin_x0020_Without_x0020_Demurrage0_38] )
AS [dbo_factBargeTripMargin_x0020_Without_x0020_Demurrage0_38], SUM ( [dbo_factBargeTrip].[dbo_factBargeTrip0_39] )
AS [dbo_factBargeTrip0_39],[dbo_factBargeTrip].[dbo_factBargeTripFactBargeTripKey0_40]
AS [dbo_factBargeTripFactBargeTripKey0_40],[dbo_factBargeTrip].[dbo_factBargeTripTripStartYear0_41]
AS [dbo_factBargeTripTripStartYear0_41],[dbo_factBargeTrip].[dbo_factBargeTripBargeKey0_42]
AS [dbo_factBargeTripBargeKey0_42],[dbo_factBargeTrip].[dbo_factBargeTripBargeTripKey0_43]
AS [dbo_factBargeTripBargeTripKey0_43],[dbo_factBargeTrip].[dbo_factBargeTripCommodityKey0_44]
AS [dbo_factBargeTripCommodityKey0_44],[dbo_factBargeTrip].[dbo_factBargeTripCpDateKey0_45]
AS [dbo_factBargeTripCpDateKey0_45],[dbo_factBargeTrip].[dbo_factBargeTripCustomerFreightKey0_46]
AS [dbo_factBargeTripCustomerFreightKey0_46],[dbo_factBargeTrip].[dbo_factBargeTripCycleEndDateKey0_47]
AS [dbo_factBargeTripCycleEndDateKey0_47],[dbo_factBargeTrip].[dbo_factBargeTripCycleStartDateKey0_48]
AS [dbo_factBargeTripCycleStartDateKey0_48],[dbo_factBargeTrip].dbo_factBargeTripOriginLocationKey0_49]
AS [dbo_factBargeTripOriginLocationKey0_49],[dbo_factBargeTrip].[dbo_factBargeTripPriorUnloadLocationKey0_50]
AS [dbo_factBargeTripPriorUnloadLocationKey0_50],[dbo_factBargeTrip].[dbo_factBargeTripReleaseDateKey0_51]
AS [dbo_factBargeTripReleaseDateKey0_51],[dbo_factBargeTrip].[dbo_factBargeTripDestinationLocationKey0_52]
AS [dbo_factBargeTripDestinationLocationKey0_52],[dbo_factBargeTrip].[dbo_factBargeTripTripStartDateKey0_53]
AS [dbo_factBargeTripTripStartDateKey0_53],[dbo_factBargeTrip].[dbo_factBargeTripTripEndDateKey0_54]
AS [dbo_factBargeTripTripEndDateKey0_54],[dbo_dimBarge_2].[BargeNumber] AS [dbo_dimBargeBargeNumber2_0],[dbo_dimBarge_2].[HullDepth]
AS [dbo_dimBargeHullDepth2_1],[dbo_dimBarge_2].[CoverType] AS [dbo_dimBargeCoverType2_2],[dbo_dimBarge_2].[BargeType]
AS [dbo_dimBargeBargeType2_3],[dbo_dimBarge_2].[YearBuilt] AS [dbo_dimBargeYearBuilt2_4],[dbo_dimBarge_2].[ShipyardBuilt]
AS [dbo_dimBargeShipyardBuilt2_5],[dbo_dimBarge_2].[ModelName] AS [dbo_dimBargeModelName2_6],[dbo_dimBarge_2].[ModelDescription]
AS [dbo_dimBargeModelDescription2_7],[dbo_dimBarge_2].[BargeID] AS [dbo_dimBargeBargeID2_8],[dbo_dimDate_12].[WeekContextKey]
AS [dbo_dimDateWeekContextKey5_0],[dbo_dimDate_12].[IsWeekday] AS [dbo_dimDateIsWeekday5_1],[dbo_dimDate_12].[DayNameKey]
AS [dbo_dimDateDayNameKey5_2],[dbo_dimDate_12].[WeekNameKey] AS [dbo_dimDateWeekNameKey5_3],[dbo_dimDate_12].[MonthNameKey]
AS [dbo_dimDateMonthNameKey5_4],[dbo_dimDate_12].[QuarterNameKey] AS [dbo_dimDateQuarterNameKey5_5],[dbo_dimDate_12].[DayOfMonthKey]
AS [dbo_dimDateDayOfMonthKey5_6],[dbo_dimDate_11].[WeekContextKey] AS [dbo_dimDateWeekContextKey7_0],[dbo_dimDate_11].[IsWeekday]
AS [dbo_dimDateIsWeekday7_1],[dbo_dimDate_11].[DayNameKey] AS [dbo_dimDateDayNameKey7_2],[dbo_dimDate_11].[WeekNameKey] AS [dbo_dimDateWeekNameKey7_3],[dbo_dimDate_11].[MonthNameKey] AS [dbo_dimDateMonthNameKey7_4],[dbo_dimDate_11].[QuarterNameKey] AS [dbo_dimDateQuarterNameKey7_5],[dbo_dimDate_11].[DayOfMonthKey] AS [dbo_dimDateDayOfMonthKey7_6],[dbo_dimLocation_22].[RiverName] AS [dbo_dimLocationRiverName9_0],[dbo_dimLocation_22].[FacilityCategory] AS [dbo_dimLocationFacilityCategory9_1],[dbo_dimLocation_22].[FacilityType] AS [dbo_dimLocationFacilityType9_2],[dbo_dimLocation_22].[FacilityAddress] AS [dbo_dimLocationFacilityAddress9_3],[dbo_dimLocation_22].[RiverCode] AS [dbo_dimLocationRiverCode9_4],[dbo_dimLocation_22].[RiverStartMile] AS [dbo_dimLocationRiverStartMile9_5],[dbo_dimLocation_22].[RiverEndMile] AS [dbo_dimLocationRiverEndMile9_6],[dbo_dimLocation_22].[SegmentStartMile] AS [dbo_dimLocationSegmentStartMile9_7],[dbo_dimLocation_22].[SegmentEndMile] AS [dbo_dimLocationSegmentEndMile9_8],[dbo_dimLocation_22].[CycleArea] AS [dbo_dimLocationCycleArea9_9],[dbo_dimLocation_22].[Owner] AS [dbo_dimLocationOwner9_10],[dbo_dimLocation_22].[Operator] AS [dbo_dimLocationOperator9_11],[dbo_dimLocation_20].[RiverName] AS [dbo_dimLocationRiverName11_0],[dbo_dimLocation_20].[FacilityCategory] AS [dbo_dimLocationFacilityCategory11_1],[dbo_dimLocation_20].[FacilityType] AS [dbo_dimLocationFacilityType11_2],[dbo_dimLocation_20].[FacilityAddress] AS [dbo_dimLocationFacilityAddress11_3],[dbo_dimLocation_20].[RiverCode] AS [dbo_dimLocationRiverCode11_4],[dbo_dimLocation_20].[RiverStartMile] AS [dbo_dimLocationRiverStartMile11_5],[dbo_dimLocation_20].[RiverEndMile] AS [dbo_dimLocationRiverEndMile11_6],[dbo_dimLocation_20].[SegmentStartMile] AS [dbo_dimLocationSegmentStartMile11_7],[dbo_dimLocation_20].[SegmentEndMile] AS [dbo_dimLocationSegmentEndMile11_8],[dbo_dimLocation_20].[CycleArea] AS [dbo_dimLocationCycleArea11_9],[dbo_dimLocation_20].[Owner] AS [dbo_dimLocationOwner11_10],[dbo_dimLocation_20].[Operator] AS [dbo_dimLocationOperator11_11],[dbo_dimLocation_19].[RiverName] AS [dbo_dimLocationRiverName13_0],[dbo_dimLocation_19].[FacilityCategory] AS [dbo_dimLocationFacilityCategory13_1],[dbo_dimLocation_19].[FacilityType] AS [dbo_dimLocationFacilityType13_2],[dbo_dimLocation_19].[FacilityAddress] AS [dbo_dimLocationFacilityAddress13_3],[dbo_dimLocation_19].[RiverCode] AS [dbo_dimLocationRiverCode13_4],[dbo_dimLocation_19].[RiverStartMile] AS [dbo_dimLocationRiverStartMile13_5],[dbo_dimLocation_19].[RiverEndMile] AS [dbo_dimLocationRiverEndMile13_6],[dbo_dimLocation_19].[SegmentStartMile] AS [dbo_dimLocationSegmentStartMile13_7],[dbo_dimLocation_19].[SegmentEndMile] AS [dbo_dimLocationSegmentEndMile13_8],[dbo_dimLocation_19].[CycleArea] AS [dbo_dimLocationCycleArea13_9],[dbo_dimLocation_19].[Owner] AS [dbo_dimLocationOwner13_10],[dbo_dimLocation_19].[Operator] AS [dbo_dimLocationOperator13_11],[dbo_dimDate_7].[WeekContextKey] AS [dbo_dimDateWeekContextKey15_0],[dbo_dimDate_7].[IsWeekday] AS [dbo_dimDateIsWeekday15_1],[dbo_dimDate_7].[DayNameKey] AS [dbo_dimDateDayNameKey15_2],[dbo_dimDate_7].[WeekNameKey] AS [dbo_dimDateWeekNameKey15_3],[dbo_dimDate_7].[MonthNameKey] AS [dbo_dimDateMonthNameKey15_4],[dbo_dimDate_7].[QuarterNameKey] AS [dbo_dimDateQuarterNameKey15_5],[dbo_dimDate_7].[DayOfMonthKey] AS [dbo_dimDateDayOfMonthKey15_6],[dbo_dimDate_23].[WeekContextKey] AS [dbo_dimDateWeekContextKey17_0],[dbo_dimDate_23].[IsWeekday] AS [dbo_dimDateIsWeekday17_1],[dbo_dimDate_23].[DayNameKey] AS [dbo_dimDateDayNameKey17_2],[dbo_dimDate_23].[WeekNameKey] AS [dbo_dimDateWeekNameKey17_3],[dbo_dimDate_23].[MonthNameKey] AS [dbo_dimDateMonthNameKey17_4],[dbo_dimDate_23].[QuarterNameKey] AS [dbo_dimDateQuarterNameKey17_5],[dbo_dimDate_23].[DayOfMonthKey] AS [dbo_dimDateDayOfMonthKey17_6],[dbo_dimDate_21].[WeekContextKey] AS [dbo_dimDateWeekContextKey19_0],[dbo_dimDate_21].[IsWeekday] AS [dbo_dimDateIsWeekday19_1],[dbo_dimDate_21].[DayNameKey] AS [dbo_dimDateDayNameKey19_2],[dbo_dimDate_21].[WeekNameKey] AS [dbo_dimDateWeekNameKey19_3],[dbo_dimDate_21].[MonthNameKey] AS [dbo_dimDateMonthNameKey19_4],[dbo_dimDate_21].[QuarterNameKey] AS [dbo_dimDateQuarterNameKey19_5],[dbo_dimDate_21].[DayOfMonthKey] AS [dbo_dimDateDayOfMonthKey19_6],[dbo_dimDate_24].[WeekContextKey] AS [dbo_dimDateWeekContextKey21_0],[dbo_dimDate_24].[IsWeekday] AS [dbo_dimDateIsWeekday21_1],[dbo_dimDate_24].[DayNameKey] AS [dbo_dimDateDayNameKey21_2],[dbo_dimDate_24].[WeekNameKey] AS [dbo_dimDateWeekNameKey21_3],[dbo_dimDate_24].[MonthNameKey] AS [dbo_dimDateMonthNameKey21_4],[dbo_dimDate_24].[QuarterNameKey] AS [dbo_dimDateQuarterNameKey21_5],[dbo_dimDate_24].[DayOfMonthKey] AS [dbo_dimDateDayOfMonthKey21_6],[dbo_dimCustomerFreight_8].[CustomerKey] AS [dbo_dimCustomerFreightCustomerKey22_0],[dbo_dimCustomerFreight_8].[CustomerID] AS [dbo_dimCustomerFreightCustomerID22_1],[dbo_dimBargeTrip_5].[BargeTripID] AS [dbo_dimBargeTripBargeTripID25_0],[dbo_dimBargeTrip_5].[CycleNumber] AS [dbo_dimBargeTripCycleNumber25_1],[dbo_dimBargeTrip_5].[CyclePattern] AS [dbo_dimBargeTripCyclePattern25_2],[dbo_dimBargeTrip_5].[CycleTripCount] AS [dbo_dimBargeTripCycleTripCount25_3],[dbo_dimBargeTrip_5].[Direction] AS [dbo_dimBargeTripDirection25_4],[dbo_dimBargeTrip_5].[Company] AS [dbo_dimBargeTripCompany25_5],[dbo_dimBargeTrip_5].[CoverStatus] AS [dbo_dimBargeTripCoverStatus25_6],[dbo_dimBargeTrip_5].[LoadStatus] AS [dbo_dimBargeTripLoadStatus25_7]
FROM (
SELECT [DemurrageCostConst] AS [dbo_factBargeTripDemurrageCostConst0_0],[CostCleaning] AS [dbo_factBargeTripCostCleaning0_1],[CostFleetingOrigin] AS [dbo_factBargeTripCostFleetingOrigin0_2],[CostFleetingDestination] AS [dbo_factBargeTripCostFleetingDestination0_3],[CostFleetingIntermediate] AS [dbo_factBargeTripCostFleetingIntermediate0_4],[CostFleeting] AS [dbo_factBargeTripCostFleeting0_5],[CostOther] AS [dbo_factBargeTripCostOther0_6],[CostShiftingOrigin] AS [dbo_factBargeTripCostShiftingOrigin0_7],[CostShiftingDestination] AS [dbo_factBargeTripCostShiftingDestination0_8],[CostShiftingIntermediate] AS [dbo_factBargeTripCostShiftingIntermediate0_9],[CostShifting] AS [dbo_factBargeTripCostShifting0_10],[CostTowing] AS [dbo_factBargeTripCostTowing0_11],[CostFleetToFleet] AS [dbo_factBargeTripCostFleetToFleet0_12],[CostPriorEmpty] AS [dbo_factBargeTripCostPriorEmpty0_13],[RevenueStorage] AS [dbo_factBargeTripRevenueStorage0_14],[RevenueOriginDemurrage] AS [dbo_factBargeTripRevenueOriginDemurrage0_15],[RevenueDestinationDemurrage] AS [dbo_factBargeTripRevenueDestinationDemurrage0_16],[RevenueAllDemurrage] AS [dbo_factBargeTripRevenueAllDemurrage0_17],[RevenueTotalDemurrage] AS [dbo_factBargeTripRevenueTotalDemurrage0_18],[RevenueTotalFreight] AS [dbo_factBargeTripRevenueTotalFreight0_19],[RevenueOther] AS [dbo_factBargeTripRevenueOther0_20],[TonsActual] AS [dbo_factBargeTripTonsActual0_21],[TonsBilled] AS [dbo_factBargeTripTonsBilled0_22],[TripDays] AS [dbo_factBargeTripTripDays0_23],[TripDaysAveraging] AS [dbo_factBargeTripTripDaysAveraging0_24],[TripDaysDemurrage] AS [dbo_factBargeTripTripDaysDemurrage0_25],[TripDaysEmpty] AS [dbo_factBargeTripTripDaysEmpty0_26],[TripDaysLoaded] AS [dbo_factBargeTripTripDaysLoaded0_27],[TripDaysFree] AS [dbo_factBargeTripTripDaysFree0_28],[TripDaysFreight] AS [dbo_factBargeTripTripDaysFreight0_29],[TripDaysStorage] AS [dbo_factBargeTripTripDaysStorage0_30],[DaysToLoad] AS [dbo_factBargeTripDaysToLoad0_31],[DaysToUnload] AS [dbo_factBargeTripDaysToUnload0_32],1 AS [dbo_factBargeTrip0_33],(TripDaysDemurrage + TripDaysStorage) * DemurrageCostConst AS [dbo_factBargeTripDemurrageCost0_34],
CostCleaning + CostFleeting + CostOther + CostShifting + CostTowing + CostFleetToFleet AS [dbo_factBargeTripCostTotal0_35],RevenueTotalFreight + RevenueTotalDemurrage + RevenueStorage + RevenueOther AS [dbo_factBargeTripRevenueTotal0_36],(RevenueTotalFreight + RevenueTotalDemurrage + RevenueStorage + RevenueOther) - (CostCleaning + CostFleeting + CostOther + CostShifting + CostTowing + CostFleetToFleet) AS [dbo_factBargeTripMargin_x0020_With_x0020_Demurrage0_37],(RevenueTotalFreight + RevenueStorage + RevenueOther - RevenueTotalDemurrage) - (CostCleaning + CostFleeting + CostOther + CostShifting + CostTowing + CostFleetToFleet - ((TripDaysDemurrage + TripDaysStorage) * DemurrageCostConst) ) AS [dbo_factBargeTripMargin_x0020_Without_x0020_Demurrage0_38],
TripDays - TripDaysDemurrage - TripDaysStorage AS [dbo_factBargeTrip0_39],[FactBargeTripKey] AS [dbo_factBargeTripFactBargeTripKey0_40],left(cast(TripStartDateKey as varchar(8)), 4) AS [dbo_factBargeTripTripStartYear0_41],[BargeKey] AS [dbo_factBargeTripBargeKey0_42],[BargeTripKey] AS [dbo_factBargeTripBargeTripKey0_43],[CommodityKey] AS [dbo_factBargeTripCommodityKey0_44],[CpDateKey] AS [dbo_factBargeTripCpDateKey0_45],[CustomerFreightKey] AS [dbo_factBargeTripCustomerFreightKey0_46],[CycleEndDateKey] AS [dbo_factBargeTripCycleEndDateKey0_47],[CycleStartDateKey] AS [dbo_factBargeTripCycleStartDateKey0_48],[OriginLocationKey] AS [dbo_factBargeTripOriginLocationKey0_49],[PriorUnloadLocationKey] AS [dbo_factBargeTripPriorUnloadLocationKey0_50],[ReleaseDateKey] AS [dbo_factBargeTripReleaseDateKey0_51],[DestinationLocationKey] AS [dbo_factBargeTripDestinationLocationKey0_52],[TripStartDateKey] AS [dbo_factBargeTripTripStartDateKey0_53],[TripEndDateKey] AS [dbo_factBargeTripTripEndDateKey0_54]
FROM [dbo].[factBargeTrip] )
AS [dbo_factBargeTrip],[dbo].[dimBarge] AS [dbo_dimBarge_2],[dbo].[dimDate] AS [dbo_dimDate_12],[dbo].[dimDate] AS [dbo_dimDate_11],[dbo].[dimLocation] AS
[dbo_dimLocation_22],[dbo].[dimLocation] AS [dbo_dimLocation_20],[dbo].[dimLocation] AS [dbo_dimLocation_19],[dbo].[dimDate] AS [dbo_dimDate_7],[dbo].[dimDate] AS [dbo_dimDate_23],[dbo].[dimDate] AS [dbo_dimDate_21],[dbo].[dimDate] AS [dbo_dimDate_24],[dbo].[dimCustomerFreight] AS [dbo_dimCustomerFreight_8],[dbo].[dimBargeTrip] AS [dbo_dimBargeTrip_5],[dbo].[dimCommodity] AS [dbo_dimCommodity_6]
WHERE ( ( [dbo_factBargeTrip].[dbo_factBargeTripBargeKey0_42] = [dbo_dimBarge_2].[BargeKey] )
AND ( [dbo_factBargeTrip].[dbo_factBargeTripBargeTripKey0_43] = [dbo_dimBargeTrip_5].[BargeTripKey] )
AND ( [dbo_factBargeTrip].[dbo_factBargeTripCommodityKey0_44] = [dbo_dimCommodity_6].[CommodityKey] )
AND ( [dbo_factBargeTrip].[dbo_factBargeTripCpDateKey0_45] = [dbo_dimDate_7].[DateKey])
AND ( [dbo_factBargeTrip].[dbo_factBargeTripCustomerFreightKey0_46] = [dbo_dimCustomerFreight_8].[CustomerKey])
AND ( [dbo_factBargeTrip].[dbo_factBargeTripCycleEndDateKey0_47] = [dbo_dimDate_11].[DateKey] )
AND ( [dbo_factBargeTrip].[dbo_factBargeTripCycleStartDateKey0_48] = [dbo_dimDate_12].[DateKey] )
AND ( [dbo_factBargeTrip].[dbo_factBargeTripOriginLocationKey0_49] = [dbo_dimLocation_19].[LocationKey] )
AND ( [dbo_factBargeTrip].[dbo_factBargeTripPriorUnloadLocationKey0_50] = [dbo_dimLocation_20].[LocationKey] )
AND ( [dbo_factBargeTrip].[dbo_factBargeTripReleaseDateKey0_51] = [dbo_dimDate_21].[DateKey] )
AND ( [dbo_factBargeTrip].[dbo_factBargeTripDestinationLocationKey0_52] = [dbo_dimLocation_22].[LocationKey] )
AND ( [dbo_factBargeTrip].[dbo_factBargeTripTripStartDateKey0_53] = [dbo_dimDate_23].[DateKey] )
AND ( [dbo_factBargeTrip].[dbo_factBargeTripTripEndDateKey0_54] = [dbo_dimDate_24].[DateKey] )
AND ( [dbo_dimCommodity_6].[CommodityGroup] = @.Param1 )
AND ( [dbo_factBargeTrip].[dbo_factBargeTripTripStartYear0_41] = @.Param2 ) )
GROUP BY
[dbo_factBargeTrip].[dbo_factBargeTripBargeKey0_42],[dbo_dimBarge_2].[BargeNumber],[dbo_dimBarge_2].[HullDepth],[dbo_dimBarge_2].[CoverType],[dbo_dimBarge_2].[BargeType],[dbo_dimBarge_2].[YearBuilt],[dbo_dimBarge_2].[ShipyardBuilt],[dbo_dimBarge_2].[ModelName],[dbo_dimBarge_2].[ModelDescription],[dbo_dimBarge_2].[BargeID],[dbo_factBargeTrip].[dbo_factBargeTripCommodityKey0_44],[dbo_factBargeTrip].[dbo_factBargeTripCycleStartDateKey0_48],[dbo_dimDate_12].[WeekContextKey],[dbo_dimDate_12].[IsWeekday],[dbo_dimDate_12].[DayNameKey],[dbo_dimDate_12].[WeekNameKey],[dbo_dimDate_12].[MonthNameKey],[dbo_dimDate_12].[QuarterNameKey],[dbo_dimDate_12].[DayOfMonthKey],[dbo_dimDate_12].[DayOfMonthKey],[dbo_factBargeTrip].[dbo_factBargeTripCycleEndDateKey0_47],[dbo_dimDate_11].[WeekContextKey],[dbo_dimDate_11].[IsWeekday],[dbo_dimDate_11].[DayNameKey],[dbo_dimDate_11].[WeekNameKey],[dbo_dimDate_11].[MonthNameKey],[dbo_dimDate_11].[QuarterNameKey],[dbo_dimDate_11].[DayOfMonthKey],[dbo_dimDate_11].[DayOfMonthKey],[dbo_factBargeTrip].[dbo_factBargeTripDestinationLocationKey0_52],[dbo_dimLocation_22].[RiverName],[dbo_dimLocation_22].[FacilityCategory],[dbo_dimLocation_22].[FacilityType],[dbo_dimLocation_22].[FacilityAddress],[dbo_dimLocation_22].[RiverCode],[dbo_dimLocation_22].[RiverStartMile],[dbo_dimLocation_22].[RiverEndMile],[dbo_dimLocation_22].[SegmentStartMile],[dbo_dimLocation_22].[SegmentEndMile],[dbo_dimLocation_22].[CycleArea],[dbo_dimLocation_22].[Owner],[dbo_dimLocation_22].[Operator],[dbo_factBargeTrip].[dbo_factBargeTripPriorUnloadLocationKey0_50],[dbo_dimLocation_20].[RiverName],[dbo_dimLocation_20].[FacilityCategory],[dbo_dimLocation_20].[FacilityType],[dbo_dimLocation_20].[FacilityAddress],[dbo_dimLocation_20].[RiverCode],[dbo_dimLocation_20].[RiverStartMile],[dbo_dimLocation_20].[RiverEndMile],[dbo_dimLocation_20].[SegmentStartMile],[dbo_dimLocation_20].[SegmentEndMile],[dbo_dimLocation_20].[CycleArea],[dbo_dimLocation_20].[Owner],[dbo_dimLocation_20].[Operator],[dbo_factBargeTrip].[dbo_factBargeTripOriginLocationKey0_49],[dbo_dimLocation_19].[RiverName],[dbo_dimLocation_19].[FacilityCategory],[dbo_dimLocation_19].[FacilityType],[dbo_dimLocation_19].[FacilityAddress],[dbo_dimLocation_19].[RiverCode],[dbo_dimLocation_19].[RiverStartMile],[dbo_dimLocation_19].[RiverEndMile],[dbo_dimLocation_19].[SegmentStartMile],[dbo_dimLocation_19].[SegmentEndMile],[dbo_dimLocation_19].[CycleArea],[dbo_dimLocation_19].[Owner],[dbo_dimLocation_19].[Operator],[dbo_factBargeTrip].[dbo_factBargeTripCpDateKey0_45],[dbo_dimDate_7].[WeekContextKey],[dbo_dimDate_7].[IsWeekday],[dbo_dimDate_7].[DayNameKey],[dbo_dimDate_7].[WeekNameKey],[dbo_dimDate_7].[MonthNameKey],[dbo_dimDate_7].[QuarterNameKey],[dbo_dimDate_7].[DayOfMonthKey],[dbo_dimDate_7].[DayOfMonthKey],[dbo_factBargeTrip].[dbo_factBargeTripTripStartDateKey0_53],[dbo_dimDate_23].[WeekContextKey],[dbo_dimDate_23].[IsWeekday],[dbo_dimDate_23].[DayNameKey],[dbo_dimDate_23].[WeekNameKey],[dbo_dimDate_23].[MonthNameKey],[dbo_dimDate_23].[QuarterNameKey],[dbo_dimDate_23].[DayOfMonthKey],[dbo_dimDate_23].[DayOfMonthKey],[dbo_factBargeTrip].[dbo_factBargeTripReleaseDateKey0_51],[dbo_dimDate_21].[WeekContextKey],[dbo_dimDate_21].[IsWeekday],[dbo_dimDate_21].[DayNameKey],[dbo_dimDate_21].[WeekNameKey],[dbo_dimDate_21].[MonthNameKey],[dbo_dimDate_21].[QuarterNameKey],[dbo_dimDate_21].[DayOfMonthKey],[dbo_dimDate_21].[DayOfMonthKey],[dbo_factBargeTrip].[dbo_factBargeTripTripEndDateKey0_54],[dbo_dimDate_24].[WeekContextKey],[dbo_dimDate_24].[IsWeekday],[dbo_dimDate_24].[DayNameKey],[dbo_dimDate_24].[WeekNameKey],[dbo_dimDate_24].[MonthNameKey],[dbo_dimDate_24].[QuarterNameKey],[dbo_dimDate_24].[DayOfMonthKey],[dbo_dimDate_24].[DayOfMonthKey],[dbo_dimCustomerFreight_8].[CustomerKey],[dbo_factBargeTrip].[dbo_factBargeTripCustomerFreightKey0_46],[dbo_dimCustomerFreight_8].[CustomerID],[dbo_factBargeTrip].[dbo_factBargeTripBargeTripKey0_43],[dbo_dimBargeTrip_5].[BargeTripID],[dbo_dimBargeTrip_5].[CycleNumber],[dbo_dimBargeTrip_5].[CyclePattern],[dbo_dimBargeTrip_5].[CycleTripCount],[dbo_dimBargeTrip_5].[Direction],[dbo_dimBargeTrip_5].[Company],[dbo_dimBargeTrip_5].[CoverStatus],[dbo_dimBargeTrip_5].[LoadStatus],[dbo_factBargeTrip].[dbo_factBargeTripFactBargeTripKey0_40],[dbo_factBargeTrip].[dbo_factBargeTripFactBargeTripKey0_40],[dbo_factBargeTrip].[dbo_factBargeTripFactBargeTripKey0_40],[dbo_factBargeTrip].[dbo_factBargeTripTripStartYear0_41]',N'@.Param1 nvarchar(15),@.Param2 nvarchar(4)',@.Param1=N'Forest Products',@.Param2=N'2005'

I have checked the key attribute to ensure that the keys associated with the fact dimension are unique. To test this, I verify that the following query returns no rows:

Select FactBargeTripKey, count(*) From FactBargeTrip
Group By FactBargeTripKey Having count(*) > 1

Surely there's a way to figure out what's going on, but I am at a loss.

|||

The only odd thing I noticed in the SQL query was that [dbo_factBargeTrip].[dbo_factBargeTripFactBargeTripKey0_40] is repeated thrice in the GROUP BY - don't know why.

Since DrillThrough queries in AS 2005 can be translated (roughly) to regular MDX equivalents, do you get duplicate rows with something like:

SELECT { [Measures].[Trip Count] } ON COLUMNS ,
NON EMPTY [FactBargeTrip].[FactBargeTripKey].[FactBargeTripKey].Members ON ROWS
FROM [Barge Trip]
WHERE ( [Commodity].[Commodity by Type and Group].[Commodity Group].&[Forest Products], [Fact Start Year].[Trip Start Year].&[2005] )

(assuming that [FactBargeTrip] is the fact dimension, and [FactBargeTripKey] is its key attribute)?

|||

I find [dbo_factBargeTrip].[dbo_factBargeTripFactBargeTripKey0_40] in the query three times, but only once in the GROUP BY clause of the query. That SQL query was a lot to wade through, but the three occurrences of [dbo_factBargeTrip].[dbo_factBargeTripFactBargeTripKey0_40] make sense. The second occurrence is within a subquery and defines the alias, [dbo_factBargeTripFactBargeTripKey0_40], based on [FactBargeTrip].[BargeTripKey]. The first occurrence simply selects that column from the subquery. The third occurence is used in a GROUP BY clause for grouping of the result. So, I don't believe that the three occurrences of that string yield a clue.

I executed the query similar to the one you provided (correcting the dimension and key attribute as you suggested) as follows:

SELECT { [Measures].[Trip Count] } ON COLUMNS ,
NON EMPTY [Fact Start Year].[Trip Key].[Trip Key].Members ON ROWS
FROM [Barge Trip]
WHERE ( [Commodity].[Commodity by Type and Group].[Commodity Group].&[Forest Products],
[Fact Start Year].[Trip Start Year].&[2005] )

The dimension and key attribute do map to the underlying database table and column that you specified. The query yielded this result...

Trip Count

288235

Perhaps I should open a case with Microsoft.

|||Since the straight MDX seems to work, one other thing worth trying (if you haven't done this already) is to check the [Fact Start Year].[Trip Key] members returned in the Drillthrough - this could be done either with an explicit Return clause in the Drillthrough statement, or by configuring the Drillthrough columns in a default Drillthrough action (like the Internet Details action in Adventure Works). Is the member key: 288235 actually returned 4 times?|||

I had not tried that. But when I do, it yields four records repeating the key four times. Here's the query:

Here's the result:

[$Fact Start Year].[Trip Key]

288235

Drillthrough returning sets of duplicate rows

Drillthrough appears to be showing multiple rows for each single record that should be displayed.

For example, in my fact table, there is a single record for each trip. The following MDX query returns a single cell with a trip count of three.

2002

Other

The relationships between my fact table and my dimension tables are quite simple. Can anyone suggest any theories about what might be causing this?

For the record, I am now executing my drill throughs from ProClarity and am still getting back the four rows for each "real" fact record.

With regard to the second question question about [Measures].[Trip Count], it is a straight "count" measure on the measure group.

Thanks for your ongoing assistance.

|||

Thanks.

I've been gone for a couple days, but now am back.

Thoughts?

|||

A couple of thoughts:

- To resolve the ROLAP rows error, I recall that this SSAS Server Property has to be increased (can't find the relevant thread right now, but you can try tweaking it):

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssasproperties.mspx

SQL Server 2005 Analysis Services (SSAS) Server Properties

Published: June 26, 2006

SQL Server Technical Article

Writers: Anthony T. Mann, Edward Melomed

...

ROLAPDimensionProcessingEffort

Property Name

General Page: OLAP \ Process \ ROLAPDimensionProcessingEffort

Default Value

300000

Unit of Measure

Data records

Data Type

Integer

Minimum Value

0 – No query to a ROLAP dimension will succeed.

Maximum Value

2147483647

Requires Restart

Alternate GUI Tool

None

Special Notes

None

Security Implications

None

- For the multiple rows returned in MOLAP mode, only thing I can think of checking is that the key attribute configured for the fact dimension is unique.

|||

Here's my most recent MDX query:

And, here's the associated SQL query generated by Analysis Services which returns a single row...

I have checked the key attribute to ensure that the keys associated with the fact dimension are unique. To test this, I verify that the following query returns no rows:

Select FactBargeTripKey, count(*) From FactBargeTrip
Group By FactBargeTripKey Having count(*) > 1

Surely there's a way to figure out what's going on, but I am at a loss.

|||

The only odd thing I noticed in the SQL query was that [dbo_factBargeTrip].[dbo_factBargeTripFactBargeTripKey0_40] is repeated thrice in the GROUP BY - don't know why.

Since DrillThrough queries in AS 2005 can be translated (roughly) to regular MDX equivalents, do you get duplicate rows with something like:

(assuming that [FactBargeTrip] is the fact dimension, and [FactBargeTripKey] is its key attribute)?

|||

I executed the query similar to the one you provided (correcting the dimension and key attribute as you suggested) as follows:

The dimension and key attribute do map to the underlying database table and column that you specified. The query yielded this result...

Trip Count

288235

Perhaps I should open a case with Microsoft.

I had not tried that. But when I do, it yields four records repeating the key four times. Here's the query:

Here's the result:

[$Fact Start Year].[Trip Key]

288235

Thursday, March 29, 2012

Dropping Multiple system tables

Dropping Multiple system tables

Dropping Multiple system tables

Sunday, March 25, 2012

Droping active connections

Dropdownlist with multiple fields

Sunday, March 11, 2012

DROP IDENTITY from tables

DROP IDENTITY from tables

DROP IDENTITY from tables

Drop Down Lists

Friday, February 24, 2012

Drillthrough with multiple tables

Drillthrough returning sets of duplicate rows

SQL Server 2005 Analysis Services (SSAS) Server Properties

ROLAPDimensionProcessingEffort

Drillthrough returning sets of duplicate rows

SQL Server 2005 Analysis Services (SSAS) Server Properties

ROLAPDimensionProcessingEffort

DSO in SQL2005

Blog Archive

About Me