Are there any differences, performance related or other wise, between having
a clustered primary key vs. a clustered unique key?
I'm am trying to ready a SQL2K server for replication. Some of the tables
in database A don't have a primary key but they do have a cluster unique
index. The index is named pk_table_A. I guess what I'd like to do, is drop
this index and recreated it as a primary key with the same columns and name.
Would this pose any type of possible problem ?
TIA,
Billy
That should be fine (assuming you don't already have a PK on the table). The PK carries with it a
unique index (you have control over whether it is to be a clustered index or not).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BillyDees" <BillyDees@.discussions.microsoft.com> wrote in message
news:1F6AAF9E-5E99-4776-ACF4-54B7FDB0EF89@.microsoft.com...
> Are there any differences, performance related or other wise, between having
> a clustered primary key vs. a clustered unique key?
> I'm am trying to ready a SQL2K server for replication. Some of the tables
> in database A don't have a primary key but they do have a cluster unique
> index. The index is named pk_table_A. I guess what I'd like to do, is drop
> this index and recreated it as a primary key with the same columns and name.
> Would this pose any type of possible problem ?
> TIA,
> Billy
|||There is one thing to look out for and that is that unique indexes can be on
nullable columns (although they only allow one NULL value), but primary keys
can't. But if the columns in the unique index are non-nullable, it can be
replaced with a primary key, provided, as you say, that there isn't one
already on the table.
Jacco Schalkwijk
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e$YRdIcRFHA.904@.tk2msftngp13.phx.gbl...
> That should be fine (assuming you don't already have a PK on the table).
> The PK carries with it a unique index (you have control over whether it is
> to be a clustered index or not).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "BillyDees" <BillyDees@.discussions.microsoft.com> wrote in message
> news:1F6AAF9E-5E99-4776-ACF4-54B7FDB0EF89@.microsoft.com...
>
|||Good catch, Jacco!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote in message
news:ueX1vNcRFHA.996@.TK2MSFTNGP09.phx.gbl...
> There is one thing to look out for and that is that unique indexes can be on nullable columns
> (although they only allow one NULL value), but primary keys can't. But if the columns in the
> unique index are non-nullable, it can be replaced with a primary key, provided, as you say, that
> there isn't one already on the table.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:e$YRdIcRFHA.904@.tk2msftngp13.phx.gbl...
>
|||Thanks to all of you for your replies. You confirmed what I thought but I
needed to be sure.
Billy
"Tibor Karaszi" wrote:
> Good catch, Jacco!
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote in message
> news:ueX1vNcRFHA.996@.TK2MSFTNGP09.phx.gbl...
>
>
Showing posts with label related. Show all posts
Showing posts with label related. Show all posts
Friday, March 9, 2012
PK vs. Unique clustered indexes
Are there any differences, performance related or other wise, between having
a clustered primary key vs. a clustered unique key?
I'm am trying to ready a SQL2K server for replication. Some of the tables
in database A don't have a primary key but they do have a cluster unique
index. The index is named pk_table_A. I guess what I'd like to do, is drop
this index and recreated it as a primary key with the same columns and name.
Would this pose any type of possible problem ?
TIA,
BillyThat should be fine (assuming you don't already have a PK on the table). The PK carries with it a
unique index (you have control over whether it is to be a clustered index or not).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BillyDees" <BillyDees@.discussions.microsoft.com> wrote in message
news:1F6AAF9E-5E99-4776-ACF4-54B7FDB0EF89@.microsoft.com...
> Are there any differences, performance related or other wise, between having
> a clustered primary key vs. a clustered unique key?
> I'm am trying to ready a SQL2K server for replication. Some of the tables
> in database A don't have a primary key but they do have a cluster unique
> index. The index is named pk_table_A. I guess what I'd like to do, is drop
> this index and recreated it as a primary key with the same columns and name.
> Would this pose any type of possible problem ?
> TIA,
> Billy|||There is one thing to look out for and that is that unique indexes can be on
nullable columns (although they only allow one NULL value), but primary keys
can't. But if the columns in the unique index are non-nullable, it can be
replaced with a primary key, provided, as you say, that there isn't one
already on the table.
--
Jacco Schalkwijk
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e$YRdIcRFHA.904@.tk2msftngp13.phx.gbl...
> That should be fine (assuming you don't already have a PK on the table).
> The PK carries with it a unique index (you have control over whether it is
> to be a clustered index or not).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "BillyDees" <BillyDees@.discussions.microsoft.com> wrote in message
> news:1F6AAF9E-5E99-4776-ACF4-54B7FDB0EF89@.microsoft.com...
>> Are there any differences, performance related or other wise, between
>> having
>> a clustered primary key vs. a clustered unique key?
>> I'm am trying to ready a SQL2K server for replication. Some of the
>> tables
>> in database A don't have a primary key but they do have a cluster unique
>> index. The index is named pk_table_A. I guess what I'd like to do, is
>> drop
>> this index and recreated it as a primary key with the same columns and
>> name.
>> Would this pose any type of possible problem ?
>> TIA,
>> Billy
>|||Good catch, Jacco!
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote in message
news:ueX1vNcRFHA.996@.TK2MSFTNGP09.phx.gbl...
> There is one thing to look out for and that is that unique indexes can be on nullable columns
> (although they only allow one NULL value), but primary keys can't. But if the columns in the
> unique index are non-nullable, it can be replaced with a primary key, provided, as you say, that
> there isn't one already on the table.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:e$YRdIcRFHA.904@.tk2msftngp13.phx.gbl...
>> That should be fine (assuming you don't already have a PK on the table). The PK carries with it a
>> unique index (you have control over whether it is to be a clustered index or not).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "BillyDees" <BillyDees@.discussions.microsoft.com> wrote in message
>> news:1F6AAF9E-5E99-4776-ACF4-54B7FDB0EF89@.microsoft.com...
>> Are there any differences, performance related or other wise, between having
>> a clustered primary key vs. a clustered unique key?
>> I'm am trying to ready a SQL2K server for replication. Some of the tables
>> in database A don't have a primary key but they do have a cluster unique
>> index. The index is named pk_table_A. I guess what I'd like to do, is drop
>> this index and recreated it as a primary key with the same columns and name.
>> Would this pose any type of possible problem ?
>> TIA,
>> Billy
>>
>|||Thanks to all of you for your replies. You confirmed what I thought but I
needed to be sure.
Billy
"Tibor Karaszi" wrote:
> Good catch, Jacco!
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote in message
> news:ueX1vNcRFHA.996@.TK2MSFTNGP09.phx.gbl...
> > There is one thing to look out for and that is that unique indexes can be on nullable columns
> > (although they only allow one NULL value), but primary keys can't. But if the columns in the
> > unique index are non-nullable, it can be replaced with a primary key, provided, as you say, that
> > there isn't one already on the table.
> >
> > --
> > Jacco Schalkwijk
> > SQL Server MVP
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> > news:e$YRdIcRFHA.904@.tk2msftngp13.phx.gbl...
> >> That should be fine (assuming you don't already have a PK on the table). The PK carries with it a
> >> unique index (you have control over whether it is to be a clustered index or not).
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "BillyDees" <BillyDees@.discussions.microsoft.com> wrote in message
> >> news:1F6AAF9E-5E99-4776-ACF4-54B7FDB0EF89@.microsoft.com...
> >> Are there any differences, performance related or other wise, between having
> >> a clustered primary key vs. a clustered unique key?
> >>
> >> I'm am trying to ready a SQL2K server for replication. Some of the tables
> >> in database A don't have a primary key but they do have a cluster unique
> >> index. The index is named pk_table_A. I guess what I'd like to do, is drop
> >> this index and recreated it as a primary key with the same columns and name.
> >> Would this pose any type of possible problem ?
> >>
> >> TIA,
> >> Billy
> >>
> >>
> >
> >
>
>
a clustered primary key vs. a clustered unique key?
I'm am trying to ready a SQL2K server for replication. Some of the tables
in database A don't have a primary key but they do have a cluster unique
index. The index is named pk_table_A. I guess what I'd like to do, is drop
this index and recreated it as a primary key with the same columns and name.
Would this pose any type of possible problem ?
TIA,
BillyThat should be fine (assuming you don't already have a PK on the table). The PK carries with it a
unique index (you have control over whether it is to be a clustered index or not).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BillyDees" <BillyDees@.discussions.microsoft.com> wrote in message
news:1F6AAF9E-5E99-4776-ACF4-54B7FDB0EF89@.microsoft.com...
> Are there any differences, performance related or other wise, between having
> a clustered primary key vs. a clustered unique key?
> I'm am trying to ready a SQL2K server for replication. Some of the tables
> in database A don't have a primary key but they do have a cluster unique
> index. The index is named pk_table_A. I guess what I'd like to do, is drop
> this index and recreated it as a primary key with the same columns and name.
> Would this pose any type of possible problem ?
> TIA,
> Billy|||There is one thing to look out for and that is that unique indexes can be on
nullable columns (although they only allow one NULL value), but primary keys
can't. But if the columns in the unique index are non-nullable, it can be
replaced with a primary key, provided, as you say, that there isn't one
already on the table.
--
Jacco Schalkwijk
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e$YRdIcRFHA.904@.tk2msftngp13.phx.gbl...
> That should be fine (assuming you don't already have a PK on the table).
> The PK carries with it a unique index (you have control over whether it is
> to be a clustered index or not).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "BillyDees" <BillyDees@.discussions.microsoft.com> wrote in message
> news:1F6AAF9E-5E99-4776-ACF4-54B7FDB0EF89@.microsoft.com...
>> Are there any differences, performance related or other wise, between
>> having
>> a clustered primary key vs. a clustered unique key?
>> I'm am trying to ready a SQL2K server for replication. Some of the
>> tables
>> in database A don't have a primary key but they do have a cluster unique
>> index. The index is named pk_table_A. I guess what I'd like to do, is
>> drop
>> this index and recreated it as a primary key with the same columns and
>> name.
>> Would this pose any type of possible problem ?
>> TIA,
>> Billy
>|||Good catch, Jacco!
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote in message
news:ueX1vNcRFHA.996@.TK2MSFTNGP09.phx.gbl...
> There is one thing to look out for and that is that unique indexes can be on nullable columns
> (although they only allow one NULL value), but primary keys can't. But if the columns in the
> unique index are non-nullable, it can be replaced with a primary key, provided, as you say, that
> there isn't one already on the table.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:e$YRdIcRFHA.904@.tk2msftngp13.phx.gbl...
>> That should be fine (assuming you don't already have a PK on the table). The PK carries with it a
>> unique index (you have control over whether it is to be a clustered index or not).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "BillyDees" <BillyDees@.discussions.microsoft.com> wrote in message
>> news:1F6AAF9E-5E99-4776-ACF4-54B7FDB0EF89@.microsoft.com...
>> Are there any differences, performance related or other wise, between having
>> a clustered primary key vs. a clustered unique key?
>> I'm am trying to ready a SQL2K server for replication. Some of the tables
>> in database A don't have a primary key but they do have a cluster unique
>> index. The index is named pk_table_A. I guess what I'd like to do, is drop
>> this index and recreated it as a primary key with the same columns and name.
>> Would this pose any type of possible problem ?
>> TIA,
>> Billy
>>
>|||Thanks to all of you for your replies. You confirmed what I thought but I
needed to be sure.
Billy
"Tibor Karaszi" wrote:
> Good catch, Jacco!
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote in message
> news:ueX1vNcRFHA.996@.TK2MSFTNGP09.phx.gbl...
> > There is one thing to look out for and that is that unique indexes can be on nullable columns
> > (although they only allow one NULL value), but primary keys can't. But if the columns in the
> > unique index are non-nullable, it can be replaced with a primary key, provided, as you say, that
> > there isn't one already on the table.
> >
> > --
> > Jacco Schalkwijk
> > SQL Server MVP
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> > news:e$YRdIcRFHA.904@.tk2msftngp13.phx.gbl...
> >> That should be fine (assuming you don't already have a PK on the table). The PK carries with it a
> >> unique index (you have control over whether it is to be a clustered index or not).
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "BillyDees" <BillyDees@.discussions.microsoft.com> wrote in message
> >> news:1F6AAF9E-5E99-4776-ACF4-54B7FDB0EF89@.microsoft.com...
> >> Are there any differences, performance related or other wise, between having
> >> a clustered primary key vs. a clustered unique key?
> >>
> >> I'm am trying to ready a SQL2K server for replication. Some of the tables
> >> in database A don't have a primary key but they do have a cluster unique
> >> index. The index is named pk_table_A. I guess what I'd like to do, is drop
> >> this index and recreated it as a primary key with the same columns and name.
> >> Would this pose any type of possible problem ?
> >>
> >> TIA,
> >> Billy
> >>
> >>
> >
> >
>
>
PK vs. Unique clustered indexes
Are there any differences, performance related or other wise, between having
a clustered primary key vs. a clustered unique key?
I'm am trying to ready a SQL2K server for replication. Some of the tables
in database A don't have a primary key but they do have a cluster unique
index. The index is named pk_table_A. I guess what I'd like to do, is drop
this index and recreated it as a primary key with the same columns and name.
Would this pose any type of possible problem ?
TIA,
BillyThat should be fine (assuming you don't already have a PK on the table). The
PK carries with it a
unique index (you have control over whether it is to be a clustered index or
not).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BillyDees" <BillyDees@.discussions.microsoft.com> wrote in message
news:1F6AAF9E-5E99-4776-ACF4-54B7FDB0EF89@.microsoft.com...
> Are there any differences, performance related or other wise, between havi
ng
> a clustered primary key vs. a clustered unique key?
> I'm am trying to ready a SQL2K server for replication. Some of the tables
> in database A don't have a primary key but they do have a cluster unique
> index. The index is named pk_table_A. I guess what I'd like to do, is dr
op
> this index and recreated it as a primary key with the same columns and nam
e.
> Would this pose any type of possible problem ?
> TIA,
> Billy|||There is one thing to look out for and that is that unique indexes can be on
nullable columns (although they only allow one NULL value), but primary keys
can't. But if the columns in the unique index are non-nullable, it can be
replaced with a primary key, provided, as you say, that there isn't one
already on the table.
Jacco Schalkwijk
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e$YRdIcRFHA.904@.tk2msftngp13.phx.gbl...
> That should be fine (assuming you don't already have a PK on the table).
> The PK carries with it a unique index (you have control over whether it is
> to be a clustered index or not).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "BillyDees" <BillyDees@.discussions.microsoft.com> wrote in message
> news:1F6AAF9E-5E99-4776-ACF4-54B7FDB0EF89@.microsoft.com...
>|||Good catch, Jacco!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message
news:ueX1vNcRFHA.996@.TK2MSFTNGP09.phx.gbl...
> There is one thing to look out for and that is that unique indexes can be
on nullable columns
> (although they only allow one NULL value), but primary keys can't. But if
the columns in the
> unique index are non-nullable, it can be replaced with a primary key, prov
ided, as you say, that
> there isn't one already on the table.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:e$YRdIcRFHA.904@.tk2msftngp13.phx.gbl...
>|||Thanks to all of you for your replies. You confirmed what I thought but I
needed to be sure.
Billy
"Tibor Karaszi" wrote:
> Good catch, Jacco!
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wro
te in message
> news:ueX1vNcRFHA.996@.TK2MSFTNGP09.phx.gbl...
>
>
a clustered primary key vs. a clustered unique key?
I'm am trying to ready a SQL2K server for replication. Some of the tables
in database A don't have a primary key but they do have a cluster unique
index. The index is named pk_table_A. I guess what I'd like to do, is drop
this index and recreated it as a primary key with the same columns and name.
Would this pose any type of possible problem ?
TIA,
BillyThat should be fine (assuming you don't already have a PK on the table). The
PK carries with it a
unique index (you have control over whether it is to be a clustered index or
not).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BillyDees" <BillyDees@.discussions.microsoft.com> wrote in message
news:1F6AAF9E-5E99-4776-ACF4-54B7FDB0EF89@.microsoft.com...
> Are there any differences, performance related or other wise, between havi
ng
> a clustered primary key vs. a clustered unique key?
> I'm am trying to ready a SQL2K server for replication. Some of the tables
> in database A don't have a primary key but they do have a cluster unique
> index. The index is named pk_table_A. I guess what I'd like to do, is dr
op
> this index and recreated it as a primary key with the same columns and nam
e.
> Would this pose any type of possible problem ?
> TIA,
> Billy|||There is one thing to look out for and that is that unique indexes can be on
nullable columns (although they only allow one NULL value), but primary keys
can't. But if the columns in the unique index are non-nullable, it can be
replaced with a primary key, provided, as you say, that there isn't one
already on the table.
Jacco Schalkwijk
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e$YRdIcRFHA.904@.tk2msftngp13.phx.gbl...
> That should be fine (assuming you don't already have a PK on the table).
> The PK carries with it a unique index (you have control over whether it is
> to be a clustered index or not).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "BillyDees" <BillyDees@.discussions.microsoft.com> wrote in message
> news:1F6AAF9E-5E99-4776-ACF4-54B7FDB0EF89@.microsoft.com...
>|||Good catch, Jacco!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message
news:ueX1vNcRFHA.996@.TK2MSFTNGP09.phx.gbl...
> There is one thing to look out for and that is that unique indexes can be
on nullable columns
> (although they only allow one NULL value), but primary keys can't. But if
the columns in the
> unique index are non-nullable, it can be replaced with a primary key, prov
ided, as you say, that
> there isn't one already on the table.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:e$YRdIcRFHA.904@.tk2msftngp13.phx.gbl...
>|||Thanks to all of you for your replies. You confirmed what I thought but I
needed to be sure.
Billy
"Tibor Karaszi" wrote:
> Good catch, Jacco!
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wro
te in message
> news:ueX1vNcRFHA.996@.TK2MSFTNGP09.phx.gbl...
>
>
Saturday, February 25, 2012
Pivot Table Bug with AdventureWorks
A user pointed out an odd bug / inconsistency that occurs when using Excel pivot tables. Depending on how attributes are related to each other, Excel does some odd things when cross joining attributes from the same dimension on an axis.
Example 1. "Correct" cross join of attributes:
Connect to AdventureWorks from Excel pivot table. Drop in "Internet Sales Amount".
On rows add "State-Province" and "Total Children". Row 4 should read "State-Province, Total Children, Total".
Click the drop-down on "Total Children" and select "1".
The results should be filtered so that you only see states where there are internet sales for families with 1 child.
Example 2. "Incorrect" cross join of attributes:
Connect to AdventureWorks from Excel pivot table. Drop in "Internet Sales Amount".
On rows add "State-Province" and "Country". Row 4 should read "State-Province, Country, Total".
Click the drop-down on "Country" and select "United States".
The results *should* be filtered so that you only see states belonging to the United States. Instead, we still see states belonging to other countries.
Shouldn't the "United States" selection on the rows preclude the states from other countries? I don't understand why the pairs of attributes in examples 1 and 2 interact so differently. I would appreciate any meaningful explanation than I can pass on to the user.
Are you using Excel 2003 or Excel 2007? Not sure if I recreated the examples exactly or not, but in Excel 2007, it seems to behave as you expect it to. In the first example, I get the states filtered to just those that had customers with a total children value of 1. And in the second example, I get the states filtered to just those in the United States.
Unless I'm doing something wrong, this seems to work in Excel 2007. I don't have Excel 2003 loaded on my current machine, but could get to another machine to test this if you are indeed using Excel 2003...
Another question would be what service pack level you have installed for Analysis Services. I have SP2 installed (which I don't think would change the behaviour of Excel, but...) So, if you are using Excel 2007, what service package are you running for SSAS?
Dave Fackler
|||I started with Excel 2003. I saw the bizarre behavior. Then I saved the document and opened in Excel 2007. And still saw the bizarre behavior.
In 2007 I have to enable "classic layout" to get both "State-Province" and "Country" to both show up side-by-side (stacked) on rows. Then I just uncheck all countries and check only "United States". However the states and provinces of other countries are still displayed in the first column.
I'm on SP2 of SSAS. Thanks in advance.
|||
Is it possible that you have the Country attribute from the Geography dimension selected with the State-Province attribute from the Customer dimension (or vice versa)? Both dimensions contain geography attributes. While they are named the same, they represent attributes in different dimensions. Thus, they are related only through the fact table. That means that filtering of one will not filter values from the other (although the filtering does affect the fact data as expected).
Dave Fackler
|||The problem happens when the "State-Province" is added in rows with the "Country" of the *same* (Customer) dimension. We actually noticed the problem first in our production cubes and then reproduced it in AdventureWorks.
To be clear, add "State-Province" in rows so it shows in the *first* column, add "Country" in the *second* column, and then try to filter the country by the "United States". Unfortunately this doesn't preclude states from other countries.
This is such an obviously inconsistency but it only occurs when attributes are related to each other in the way that "State-Province" and "Country" are. I'm surprised that I haven't heard of other complaints. Also unusual is the fact that my own MDX expressions with these two attributes never return the same data that Excel is displaying.
Anybody else see this problem?
Subscribe to:
Posts (Atom)