Problem with select every TOP 1 records from different group in a

Hi Expert,
I came across a situation where the query result returns all non-NULL
records even I use TOP 1 statement. Here is the SQL statement:
SELECT CONTRACT_NUMBER, STEP, STATUS_END_DATE, CIS_PK
FROM dbo.[VIEW1] T1
WHERE (CONTRACT_NUMBER = 'S07-123A' OR
CONTRACT_NUMBER = 'S07-127A' OR
CONTRACT_NUMBER = 'S07-129A') AND (STEP =
(SELECT TOP 1 STEP
FROM dbo.[VIEW2] T2
WHERE T2.CIS_PK = T1.CIS_PK AND
T2.STATUS_END_DATE IS NOT NULL
ORDER BY STEP ASC))
For the above SQL statement, "CIS_PK is key". "STEP" is unique within each
CONTRACT_NUMBER group.
The incorrect result that I got is:
CONTRACT_NUMBER STEP DATE
CIS_PK
S05-137A 4 8/11/2006
728
S05-137A 7 10/1/2006
731
I am expecting the right answer, which is:
S05-137A 4 8/11/2006
728
Thanks,
-adam
I just figured it out by myself. The where clause comparison
T2.CIS_PK=T1.CIS_PK should be T2.CONTRACT_NUMBER = T1.CONTRACT_NUMBER.
Thanks anyway.
-adam
"adam" wrote:

> Hi Expert,
> I came across a situation where the query result returns all non-NULL
> records even I use TOP 1 statement. Here is the SQL statement:
> SELECT CONTRACT_NUMBER, STEP, STATUS_END_DATE, CIS_PK
> FROM dbo.[VIEW1] T1
> WHERE (CONTRACT_NUMBER = 'S07-123A' OR
> CONTRACT_NUMBER = 'S07-127A' OR
> CONTRACT_NUMBER = 'S07-129A') AND (STEP =
> (SELECT TOP 1 STEP
> FROM dbo.[VIEW2] T2
> WHERE T2.CIS_PK = T1.CIS_PK AND
> T2.STATUS_END_DATE IS NOT NULL
> ORDER BY STEP ASC))
> For the above SQL statement, "CIS_PK is key". "STEP" is unique within each
> CONTRACT_NUMBER group.
> The incorrect result that I got is:
> CONTRACT_NUMBER STEP DATE
> CIS_PK
> S05-137A 4 8/11/2006
> 728
> S05-137A 7 10/1/2006
> 731
> I am expecting the right answer, which is:
> S05-137A 4 8/11/2006
> 728
> Thanks,
> -adam

Problem with parent-child dimension

I have to migrate OLAP database from AS2000 to AS2005. I have a problem with one of my dimension. This is how the situation looks like.

In AS2000 I have a parent child 'Users' dimension. Here is the structure of this dimension

Users

UniqueIdUser - Key

UniqueIdUserPrev - Parent

IDUser

UserName

The parent-chld relation is based on colums : UniqueIdUser and UniqueIdUserPrev. In AS2000 connection between this dim and the facts table is based on dim.IDUser - fact.IDUser. I know that this kind of connection gives me cartesian since dim.IDUser is not unique, but this is what I expected.

The question is : How can I do a parent child dimension in AS2005, where a parent child relation will be based on UniqueIdUser and UniqueIdUserPrev colums but connection to the fact table will be based on IDUser?

How about using a named query, which includes UniqueIdUser, for the fact table, like:

select fact.*, dim.UniqueIdUser

from fact

join dim on fact.IDUser = dim.IDUser

Corrsepondingly, the dimension table could now be a named query, like:

select distinct UniqueIdUser, UniqueIdUserPrev, UserName

from dim

|||

Hi

Thx for advice. Yes It will work, cube can be based on view like this. But my fact tables are quite big - something about 5-8 millions as well as dimUsers (about 20K). Inner join between this two tables will not work efficiently. There must be another solution. I have migrated cubes from AS2000 to AS2005 and AS2005 managed to convert this cube. But I can not do it manualy. AS2005 has created wired relation in 'Dimension Usage' between UniqueIDUser from 'Dimension Colums' and UniqueIDUser from 'Measure Group Colums' although there is no column UniqueIDUser in 'Measure Group Colums' (You can not choose it from drop down list)!!? If wizard could do this, I should mange to do this manualy also.

|||

Maybe you can inspect the migrated .cube XML file, to find out how the Granularity attribute is configured (there should be an element like: <Type>Granularity</Type>).

My understanding is that not all of the XML elements generated by the Migration Wizard can be manually recreated via the BIDS GUI. Of course, you could edit the .cube XML file directly, but then you'd have to understand it very well.

problem with orphaned "dbo" user of an attached database

Hi everybody,
I'm in a situation where I allow my users of SQL Server the creation of her
own databases. This is been done in order to allow the "creator" (the owner)
full acces to the created DB. Everything works fine, as long as the DB is
created from scratch, like:
CREATE DATABASE universe
But additionally, my users are also exchanging databases, so they will also
use
CREATE DATABASE universe ... FOR ATTACH
When attaching a database, the owner of the so created database is the login
that
performed the operation, which is fine. But, the "dbo" user in the attached
database is still associated with the original login (before detaching the
db). So, in that case, the "dbo" is orphaned, which leads to the situation
that the owner of the database is not able to access her own db. I know that
this can be fixed with
ALTER AUTHORIZATION ON DATABASE::universe to [login]
So, after attaching a database, the user can simply all ALTER AUTHORIZATION.
My problem is that ALTER AUTORIZATION requires "CONTROL SERVER" permission,
which is simply a synonym for "sysadmin" role membership and therefore not
what I want. All I want my users grant is "CREATE ANY DATABASE" permission.
Does anybody know a solution besides doing the CREATE DATABASE ... FOR
ATTACH with an adjacent ALTER AUTHORIZATION inside a stored procedure with a
regarding signature?
Thanks,
HolgerHolger (Holger@.discussions.microsoft.com) writes:
> I know that this can be fixed with ALTER AUTHORIZATION ON
> DATABASE::universe to [login] So, after attaching a database, the user
> can simply all ALTER > AUTHORIZATION. My problem is that ALTER
> AUTORIZATION requires "CONTROL SERVER" permission, which is simply a
> synonym for "sysadmin" role membership and therefore not what I want.
> All I want my users grant is "CREATE ANY DATABASE" permission. Does
> anybody know a solution besides doing the CREATE DATABASE ... FOR ATTACH
> with an adjacent ALTER AUTHORIZATION inside a stored procedure with a
> regarding signature?
You could put the ALTER AUTHORSIZATION statement in a stored procedure
that you sign with a certificate, and then you grant a login associated
with that cert CONTOL SERVER. Note that the login is not a real login,
that is, it cannot connect.
For a lot more detail on this, see this article on my web site:
http://www.sommarskog.se/grantperm.html
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Holger
> db). So, in that case, the "dbo" is orphaned, which leads to the situation
> that the owner of the database is not able to access her own db. I know
> that
What is authentication are you using?
See if this helps
http://dimantdatabasesolutions.blog...on.
html
"Holger" <Holger@.discussions.microsoft.com> wrote in message
news:73BD641F-C7DF-4EAF-A87E-523FC6F1F28F@.microsoft.com...
> Hi everybody,
> I'm in a situation where I allow my users of SQL Server the creation of
> her
> own databases. This is been done in order to allow the "creator" (the
> owner)
> full acces to the created DB. Everything works fine, as long as the DB is
> created from scratch, like:
> CREATE DATABASE universe
> But additionally, my users are also exchanging databases, so they will
> also
> use
> CREATE DATABASE universe ... FOR ATTACH
> When attaching a database, the owner of the so created database is the
> login
> that
> performed the operation, which is fine. But, the "dbo" user in the
> attached
> database is still associated with the original login (before detaching the
> db). So, in that case, the "dbo" is orphaned, which leads to the situation
> that the owner of the database is not able to access her own db. I know
> that
> this can be fixed with
> ALTER AUTHORIZATION ON DATABASE::universe to [login]
> So, after attaching a database, the user can simply all ALTER
> AUTHORIZATION.
> My problem is that ALTER AUTORIZATION requires "CONTROL SERVER"
> permission,
> which is simply a synonym for "sysadmin" role membership and therefore not
> what I want. All I want my users grant is "CREATE ANY DATABASE"
> permission.
> Does anybody know a solution besides doing the CREATE DATABASE ... FOR
> ATTACH with an adjacent ALTER AUTHORIZATION inside a stored procedure with
> a
> regarding signature?
> Thanks,
> Holger|||Hi Erland,
thanks for your answer. I know that this is a solution but the problem is
that, for ALTER AUTHORIZATION I need a login and a certificate inside the
master database and also a master key.
I'd like to fairly avoid storing information of any kind inside master -
regardless of what information this is. That's the reason why I was asking
for a solution without a signed stored procdure.
"Erland Sommarskog" wrote:

> Holger (Holger@.discussions.microsoft.com) writes:
> You could put the ALTER AUTHORSIZATION statement in a stored procedure
> that you sign with a certificate, and then you grant a login associated
> with that cert CONTOL SERVER. Note that the login is not a real login,
> that is, it cannot connect.
> For a lot more detail on this, see this article on my web site:
> http://www.sommarskog.se/grantperm.html
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Hi Uri,
thanks for your answer. As for our SQL Server - we use Windows
authentication. But I do know nothing about the source Server,so it should
work with all authentication modes. Also I guess, in order work with Windows
authentication, the prerequisite will certainly be that all servers reside i
n
one domain, which is not the case. I get databases from unkown sources from
all over the world and simply have to use those.
regards,
Holger
"Uri Dimant" wrote:

> Holger
> What is authentication are you using?
> See if this helps
> http://dimantdatabasesolutions.blog...o
n.html
>
>
> "Holger" <Holger@.discussions.microsoft.com> wrote in message
> news:73BD641F-C7DF-4EAF-A87E-523FC6F1F28F@.microsoft.com...
>
>|||Holger (Holger@.discussions.microsoft.com) writes:
> thanks for your answer. I know that this is a solution but the problem is
> that, for ALTER AUTHORIZATION I need a login and a certificate inside the
> master database and also a master key.
> I'd like to fairly avoid storing information of any kind inside master -
> regardless of what information this is. That's the reason why I was asking
> for a solution without a signed stored procdure.
If you prefer you can use impersonation in the procedure, that is EXECUTE
AS.
I'm not sure that I understand your reluctance against storing information
in master. After all, it is here you have information about logins,
databases, server-level permissions, the service master key in master.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx