Does SSAS allow implementation of SCD?

Can I implement a slowly changing dimension type 2 in SSAS. I am looking at creating an SSAS cube which can pull data directly from an operational OLTP database. The source database does not maitain history of changes for the dimensions, and I wanted to know if SSAS will help me keep that history by defining certain dimensions as a SCD. If so how do I define that rule. All tutorials I have read only skim on that topic and don't describe the steps/ways to define it in SSAS. Any help would be appreciated.

While you can use type 2 SCDs (and any other implementation of an SCD) in SSAS, it is not the thing that implements them.

You need to implement them in a Datamart/Datawarehouse and SSAS will then read them from there. What might be of interest to you is the Slowly Changing Dimension task in SSIS. It's not the quickest of components, but it does allow you to use a GUI to define the columns that you want to tracking history on.

Does not Work --ETL Package Tutorial (Lesson 1: Creating the Project and Basic Package )

Hi,

I am new to SSIS. I followed the direction of the tutorial Creating Simple ETL Tutorial package in BooksOnline. I have tried more than five times and have done exactly as suggested in the tutorial but it does not work.

The URL is:

http://msdn2.microsoft.com/en-us/library/ms169917.aspx

I get these warnings and finally fails:

1)[Lookup [30]] Error: Row yielded no match during lookup.

2) [Lookup [30]] Error: The "component "Lookup" (30)" failed because error code 0xC020901E occurred, and the error row disposition on "output "Lookup Output" (32)" specifies failure on error. An error occurred on the specified object of the specified component.

3) [DTS.Pipeline] Error: The ProcessInput method on component "Lookup" (30) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

4) [DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0209029.

Can someone help me with this tutorial error? or Am I doing something wrong.

Thank you,

a lookup transform will trhow an error if there is no a match; check the 'joins' within the lookup and additionaly configure the error output to either 'ignore' or 'redirect' the errors. Redirect option will require to define a path for those no matched rows.

Rafael Salas

|||

Hi

I have the same problem as above ( I have done the tutorioal on two different machines) . I have looked at the sample data and the lookup value is there. The only thing that I have noticed is that the dates in the flat file appear to be US format. Is this the problem?

|||

As I suspected this is a date format problem. I have added a script component to modify the date and all is well. Script text is as below.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim YY, MM, DD As Integer

Dim StrYY, StrMM, StrDD As String

YY = Year(Row.CurrencyDate)

MM = Month(Row.CurrencyDate)

DD = Day(Row.CurrencyDate)

StrYY = Trim$(Str$(YY))

StrDD = Trim$(Str$(MM))

Select Case DD

Case 1

StrMM = "January"

Case 2

StrMM = "February"

Case 3

StrMM = "March"

Case 4

StrMM = "April"

Case 5

StrMM = "May"

Case 6

StrMM = "June"

Case 7

StrMM = "July"

Case 8

StrMM = "August"

Case 9

StrMM = "September"

Case 10

StrMM = "October"

Case 11

StrMM = "November"

Case 12

StrMM = "December"

End Select

Row.CurrencyDate = DateValue(StrMM & " " & StrDD & ", " & StrYY)

End Sub

End Class

|||

You can solve this problem configuring the LocaleID in "Sample Flat File Source Data" to "English (United States)" because this is the LocaleID of the Data Base.

The problem is that LocaleID in your Regional Settings is not 1033 (English (US))"

Does not Work --ETL Package Tutorial (Lesson 1: Creating the Project and Basic Package )

Hi,

I am new to SSIS. I followed the direction of the tutorial Creating Simple ETL Tutorial package in BooksOnline. I have tried more than five times and have done exactly as suggested in the tutorial but it does not work.

The URL is:

http://msdn2.microsoft.com/en-us/library/ms169917.aspx

I get these warnings and finally fails:

1)[Lookup [30]] Error: Row yielded no match during lookup.

2) [Lookup [30]] Error: The "component "Lookup" (30)" failed because error code 0xC020901E occurred, and the error row disposition on "output "Lookup Output" (32)" specifies failure on error. An error occurred on the specified object of the specified component.

3) [DTS.Pipeline] Error: The ProcessInput method on component "Lookup" (30) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

4) [DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0209029.

Can someone help me with this tutorial error? or Am I doing something wrong.

Thank you,

a lookup transform will trhow an error if there is no a match; check the 'joins' within the lookup and additionaly configure the error output to either 'ignore' or 'redirect' the errors. Redirect option will require to define a path for those no matched rows.

Rafael Salas

|||

Hi

I have the same problem as above ( I have done the tutorioal on two different machines) . I have looked at the sample data and the lookup value is there. The only thing that I have noticed is that the dates in the flat file appear to be US format. Is this the problem?

|||

As I suspected this is a date format problem. I have added a script component to modify the date and all is well. Script text is as below.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim YY, MM, DD As Integer

Dim StrYY, StrMM, StrDD As String

YY = Year(Row.CurrencyDate)

MM = Month(Row.CurrencyDate)

DD = Day(Row.CurrencyDate)

StrYY = Trim$(Str$(YY))

StrDD = Trim$(Str$(MM))

Select Case DD

Case 1

StrMM = "January"

Case 2

StrMM = "February"

Case 3

StrMM = "March"

Case 4

StrMM = "April"

Case 5

StrMM = "May"

Case 6

StrMM = "June"

Case 7

StrMM = "July"

Case 8

StrMM = "August"

Case 9

StrMM = "September"

Case 10

StrMM = "October"

Case 11

StrMM = "November"

Case 12

StrMM = "December"

End Select

Row.CurrencyDate = DateValue(StrMM & " " & StrDD & ", " & StrYY)

End Sub

End Class

|||

You can solve this problem configuring the LocaleID in "Sample Flat File Source Data" to "English (United States)" because this is the LocaleID of the Data Base.

The problem is that LocaleID in your Regional Settings is not 1033 (English (US))"

Does not Work --ETL Package Tutorial (Lesson 1: Creating the Project and Basic Package )

Hi,

I am new to SSIS. I followed the direction of the tutorial Creating Simple ETL Tutorial package in BooksOnline. I have tried more than five times and have done exactly as suggested in the tutorial but it does not work.

The URL is:

http://msdn2.microsoft.com/en-us/library/ms169917.aspx

I get these warnings and finally fails:

1)[Lookup [30]] Error: Row yielded no match during lookup.

2) [Lookup [30]] Error: The "component "Lookup" (30)" failed because error code 0xC020901E occurred, and the error row disposition on "output "Lookup Output" (32)" specifies failure on error. An error occurred on the specified object of the specified component.

3) [DTS.Pipeline] Error: The ProcessInput method on component "Lookup" (30) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

4) [DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0209029.

Can someone help me with this tutorial error? or Am I doing something wrong.

Thank you,

a lookup transform will trhow an error if there is no a match; check the 'joins' within the lookup and additionaly configure the error output to either 'ignore' or 'redirect' the errors. Redirect option will require to define a path for those no matched rows.

Rafael Salas

|||

Hi

I have the same problem as above ( I have done the tutorioal on two different machines) . I have looked at the sample data and the lookup value is there. The only thing that I have noticed is that the dates in the flat file appear to be US format. Is this the problem?

|||

As I suspected this is a date format problem. I have added a script component to modify the date and all is well. Script text is as below.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim YY, MM, DD As Integer

Dim StrYY, StrMM, StrDD As String

YY = Year(Row.CurrencyDate)

MM = Month(Row.CurrencyDate)

DD = Day(Row.CurrencyDate)

StrYY = Trim$(Str$(YY))

StrDD = Trim$(Str$(MM))

Select Case DD

Case 1

StrMM = "January"

Case 2

StrMM = "February"

Case 3

StrMM = "March"

Case 4

StrMM = "April"

Case 5

StrMM = "May"

Case 6

StrMM = "June"

Case 7

StrMM = "July"

Case 8

StrMM = "August"

Case 9

StrMM = "September"

Case 10

StrMM = "October"

Case 11

StrMM = "November"

Case 12

StrMM = "December"

End Select

Row.CurrencyDate = DateValue(StrMM & " " & StrDD & ", " & StrYY)

End Sub

End Class

|||

You can solve this problem configuring the LocaleID in "Sample Flat File Source Data" to "English (United States)" because this is the LocaleID of the Data Base.

The problem is that LocaleID in your Regional Settings is not 1033 (English (US))"

Does MSSQL support LIMIT

Hi,

I'm creating a software, and I have a dilemn... To check it I use MySQL, but I want my software to use a lot of kind of diffrent DB (oracle, DB2, informix, etc...)

But I have to know :

Does MSSQL support something like that (and if not how to do it) :

SELECT fields FROM Table LIMIT 0,500

??

thank you very much for a quick answer, because All development depend of the answer !!!

thank you,
bye.no, it doesn't

you'll have to use TOP instead, which is explained in the manual

Does MSDE fit my need?

I am creating an application that need store data locally (the new data
are downloaded from my webserver daily). Here is what I want:
1. Secure - Only my application can read the database. I don't want the
user be able to look at the data using other tools, or be able to
export the data for other purpose.
2. Handle large amount of data and be very fast.
3. How MSDE is distributed?
Thanks
John
hi,
Q. John Chen wrote:
> I am creating an application that need store data locally (the new
> data are downloaded from my webserver daily). Here is what I want:
> 1. Secure - Only my application can read the database. I don't want
> the user be able to look at the data using other tools, or be able to
> export the data for other purpose.
>
SQL Server/MSDE is secure as long as you provide an accurate login logic...
SQL Server uses a so called "2 phase" authentication policy:
first an SQL Server Login or a Windows login must be created of granted
access to the SQL Server instance... at the server level a login can be made
member of none, 1 or all of the fixed server roles, which include "sysadmin"
role and so on...
the second authentication phase is at database level, where each login will
be granted database access mapping to a database user... here access
permissions are set, as granting user/role SELECT/DELETE/EXECUTE (and so on)
privileges at an object level (or column level for tables and views)..
the mapping is performed in the JOIN database..sysusers.sid =
master..syslogins.sid , so the only link is the provided Login's sid, it's
Security IDentification number
so, the second phase regards a database security implementation... in order
to access a specified database the simple login existance does not provide
database access, but a (database) user must be mapped to the corresponding
login.. and is about verifying that at each object level (including
database, tables, views, columns, procedures and so on) the Login/User
association is permitted access to... please go on reading at
http://msdn.microsoft.com/library/de...urity_05bt.asp ,
http://msdn.microsoft.com/library/de...ar_da_0n77.asp
and following chapters..
but, back to the first phase, you can choose between 2 authentication modes:
WinNT (trusted) connections or SQL Server authenticated connections... the
latter always requires full user's credential such as "User
Id=sa;Password=pwd", the password can be NULL so it must not be specified,
but I strongly advise you always to ensure strong passwords are present...
WindowsNT authentication, on the contrary, does not requires user's
credential becouse it's directly provided by Windows via the logins'ID
(sid), which authenticate user's login at the windows login step... SQL
Server only needs to verify that the corresponding login and/or group is
granted to log on the instance...
Microsoft recommends to use the Windows NT (trusted) model as it grants more
and reliable security patterns
you can start reading about authentication modes at
other articles worth reading can be found at
http://www.sql-server-performaXnce.c...l_security.asp
http://www.microsoft.com/technet/pro.../sp3sec03.mspx
http://www.microsoft.com/technet/pro.../sp3sec00.mspx

> 2. Handle large amount of data and be very fast.
about large amount of data, MSDE is limited to 2gb data file per database..

> 3. How MSDE is distributed?
I do not understand this question... if it's about legal permissions, MSDE
is free to download and use, where you have to register (for free) at
http://www.microsoft.com/sql/msde/ho...stregister.asp for
redistribution rights...
frmo a technical point of view, it isa provided as a package including a
boostrap installer based on Windows Installer technology, to be run from a
command line prompt in order to provide all the required parameters
http://msdn.microsoft.com/library/de...stsql_84xl.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||About security the OP asked. His intention is not allowing user to see his
database design and data. On this regard, login logic only guard very
innocent users. For any reasonably knowledgable network/computer
administrator, who allows MSDE being installed or who does his own MSDE
installation (thanks to MSDE, many non-database administrators know how to
do it now), can install/uninstall, attach/detach your *.mdf , then be able
to see the database, unless some sort of encryption is applied. After all,
you cannot prevent an Administrator to install/uninstall MSDE. Say, your app
installs MSDE with SQL Security only with a long SA password. The user can
easily enable Win Security by going to Registry, or simply uninstall the
MSDE (note, user database *.mdf does not get erased during uninstallation)
and re-install it with Windows security. And he can create whatever login
and give the login whatever role he wants and then attach your database and
open it.
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3drhk9F6n6b9nU1@.individual.net...
> hi,
> Q. John Chen wrote:
> SQL Server/MSDE is secure as long as you provide an accurate login
logic...
> SQL Server uses a so called "2 phase" authentication policy:
> first an SQL Server Login or a Windows login must be created of granted
> access to the SQL Server instance... at the server level a login can be
made
> member of none, 1 or all of the fixed server roles, which include
"sysadmin"
> role and so on...
>
> the second authentication phase is at database level, where each login
will
> be granted database access mapping to a database user... here access
> permissions are set, as granting user/role SELECT/DELETE/EXECUTE (and so
on)
> privileges at an object level (or column level for tables and views)..
> the mapping is performed in the JOIN database..sysusers.sid =
> master..syslogins.sid , so the only link is the provided Login's sid, it's
> Security IDentification number
> so, the second phase regards a database security implementation... in
order
> to access a specified database the simple login existance does not provide
> database access, but a (database) user must be mapped to the corresponding
> login.. and is about verifying that at each object level (including
> database, tables, views, columns, procedures and so on) the Login/User
> association is permitted access to... please go on reading at
>
http://msdn.microsoft.com/library/de...urity_05bt.asp ,
>
http://msdn.microsoft.com/library/de...ar_da_0n77.asp
> and following chapters..
>
> but, back to the first phase, you can choose between 2 authentication
modes:
> WinNT (trusted) connections or SQL Server authenticated connections... the
> latter always requires full user's credential such as "User
> Id=sa;Password=pwd", the password can be NULL so it must not be specified,
> but I strongly advise you always to ensure strong passwords are
present...
> WindowsNT authentication, on the contrary, does not requires user's
> credential becouse it's directly provided by Windows via the logins'ID
> (sid), which authenticate user's login at the windows login step... SQL
> Server only needs to verify that the corresponding login and/or group is
> granted to log on the instance...
>
> Microsoft recommends to use the Windows NT (trusted) model as it grants
more
> and reliable security patterns
> you can start reading about authentication modes at
>
> other articles worth reading can be found at
> http://www.sql-server-performaXnce.c...l_security.asp
>
http://www.microsoft.com/technet/pro.../sp3sec03.mspx
>
http://www.microsoft.com/technet/pro.../sp3sec00.mspx
>
> about large amount of data, MSDE is limited to 2gb data file per
database..
>
> I do not understand this question... if it's about legal permissions, MSDE
> is free to download and use, where you have to register (for free) at
> http://www.microsoft.com/sql/msde/ho...stregister.asp for
> redistribution rights...
> frmo a technical point of view, it isa provided as a package including a
> boostrap installer based on Windows Installer technology, to be run from a
> command line prompt in order to provide all the required parameters
>
http://msdn.microsoft.com/library/de...stsql_84xl.asp
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||It seems that I can not use MSDE for my application thanks to you
answer. (unless I can find a way to remove your post :-))
Any recommendation on a local database that provide the security I
wanted. (not a good place to ask for an alternative here though).
Thanks
John
|||hi Norman,
I do not understand if you are just claiming for security...
Norman Yuan wrote:
> About security the OP asked. His intention is not allowing user to
> see his
> database design and data.
and of course you have to manage your WinNT users/groups accordingly to your
security needs and policy.. never give permissions your user is not
interested/accorded with..
and again, of course, we are dealing with SQL Server security and not OS
security, you should already know and manage accordingly to your needs

> On this regard, login logic only guard very
> innocent users. For any reasonably knowledgable network/computer
> administrator, who allows MSDE being installed or who does his own
> MSDE
> installation (thanks to MSDE, many non-database administrators know
> how to
> do it now), can install/uninstall, attach/detach your *.mdf , then be
> able
> to see the database, unless some sort of encryption is applied. After
> all,
> you cannot prevent an Administrator to install/uninstall MSDE.
do not understand this point... you can actually prevent your administrators
from logging in SQL Server... but you can not prevent them to uninstall SQL
Server... that's ok... but, what kind of employee do you have in your
organization? usually, if you can not trust your (fews) administrator, I
really think you have to fire them... the very same applys to SQL Server
(not os) administrator(s)..

>Say,
> your app
> installs MSDE with SQL Security only with a long SA password. The
> user can
> easily enable Win Security by going to Registry,
not the user, the local administrator...

>or simply uninstall the
> MSDE (note, user database *.mdf does not get erased during
> uninstallation)
> and re-install it with Windows security. And he can create whatever
> login
> and give the login whatever role he wants and then attach your
> database and
> open it.
again... I think you should fire your employees :D
as a local admin can do whatever operation he likes to do, you can not
prevent him to stop the server and trash your data.. he can perhaps even
eventually log on SQL Server, if you did not remove the
BUILTIN\Administrator login group (as I usually do) from the MSDE istance,
and of course, as part of the sysamin server role, even access the company
database and increase his salary by 20%...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||The original OP are concerned about protecting his software, including the
database design and data the software used. He does not want the software
user, be it individual or organization, to peek into his software logic by
openning database on MSDE. So my point is the software user has the power to
open a SQL Server database installed on his computer unless some encryption
is implemented on the database. It has nothing to do with how the employee
is behaves and is regulated.
Actually, from the point of view of pretecting software, lot of your unique
business logic are reflected on the database design. And when using MSDE in
your application, we are educated to use as much stroed procedures as
possible, meaning more business logic in the DB. Obviously, there is need to
protect them (I know and you know there are tools for encypting SPs). In
most cases of SQL Server being used in a organization, it is most likely
that app used there are custom-developed, so protecting software wouldn't be
a problem. But when you are developing a stand-alone app for sale, with MSDE
integrided, concerns on user peeking into the DB is understandable.
I developed a windows app package a couple of years ago and used MSDE with
tons of SPs in it. It was aimed to small business in certain business. There
is nothing to prevent them to find a knowledgable guy to get into the
database and uses those Tables/SPs and develop there new UI app, although
they did not do that. Since MSDE is a powerful data engine and very easy to
be integrited into your app, protecting software resulted in by this should
be a concern. How to safyly regulating SQL Server/MSDE in a organization is
not my topic here.
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3dsa5rF6tlqnqU1@.individual.net...
> hi Norman,
> I do not understand if you are just claiming for security...
> Norman Yuan wrote:
> and of course you have to manage your WinNT users/groups accordingly to
your
> security needs and policy.. never give permissions your user is not
> interested/accorded with..
> and again, of course, we are dealing with SQL Server security and not OS
> security, you should already know and manage accordingly to your needs
>
> do not understand this point... you can actually prevent your
administrators
> from logging in SQL Server... but you can not prevent them to uninstall
SQL
> Server... that's ok... but, what kind of employee do you have in your
> organization? usually, if you can not trust your (fews) administrator, I
> really think you have to fire them... the very same applys to SQL Server
> (not os) administrator(s)..
>
> not the user, the local administrator...
>
> again... I think you should fire your employees :D
> as a local admin can do whatever operation he likes to do, you can not
> prevent him to stop the server and trash your data.. he can perhaps even
> eventually log on SQL Server, if you did not remove the
> BUILTIN\Administrator login group (as I usually do) from the MSDE istance,
> and of course, as part of the sysamin server role, even access the company
> database and increase his salary by 20%...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||Norman,
You read my mind. The software I developed is an application analyzing
commodity trading data. The user is not of a single organization but in
different organization or individuals all over the world. What we are
selling is the data not the software ifself. So protect the data is the
first priority.
Still, I want thank Andrea for giving me all the information about
MSDE.
Again, thanks both.
John
|||Q. John Chen wrote:
> Norman,
> You read my mind. The software I developed is an application analyzing
> commodity trading data. The user is not of a single organization but in
> different organization or individuals all over the world. What we are
> selling is the data not the software ifself. So protect the data is the
> first priority.
> Still, I want thank Andrea for giving me all the information about
> MSDE.
> Again, thanks both.
> John
>
Hi,
My company sells data and ships an MSDE application. What we ended up
doing was coding in application encryption logic. Numbers are not
encrypted but databae column with anything in text (like say
COMPANYNAME) was encrypted.
We can't stop the end-users looking at the database through Access, but
what they do see is a garbled mess !
|||Encrypt the data before storing it in the database, and decrypt it on the
application side when you read it. Of course this will affect overall
performance, but if security is your primary concern, performance is going
to take a hit no matter what.
"Q. John Chen" <qjchen@.email.com> wrote in message
news:1115221772.287953.148830@.o13g2000cwo.googlegr oups.com...
> It seems that I can not use MSDE for my application thanks to you
> answer. (unless I can find a way to remove your post :-))
> Any recommendation on a local database that provide the security I
> wanted. (not a good place to ask for an alternative here though).
> Thanks
> John
>

Does It Matter...

MSSQl 2000
When creating a select statement with joins... does it matter where you plac
e additional where-clause criteria.
Considering the two examples below, is it more efficient to place additional
filtering criteria within the join section.? Does it weed out extra rows be
fore joining them? or should I put anything that is not pertinent to the joi
n itself down below in its own where clause?
EXAMPLE 1
SELECT
A.column_1
B.column_2
C.column_3
FROM
table_a A
inner join table_b B ON B.column_1 = A.column_1 and
B.column_2 = 1 and
B.column_3 = 'boys' and
B.column_4 between '01/01/06' and '01/31/06'
inner join table_c C ON C.column_2 = B.column_2
EXAMPLE 2
SELECT
A.column_1
B.column_2
C.column_3
FROM
table_a A
inner join table_b B ON B.column_1 = A.column_1 and
inner join table_c C ON C.column_2 = B.column_2
WHERE
B.column_2 = 1 and
B.column_3 = 'boys' and
B.column_4 between '01/01/06' and '01/31/06'For INNER JOINs, I like to put the JOIN criteria in the ON clause and the
filtering criteria in the WHERE clause. This makes it very clear to anyone
who inherits the code (or myself, when I go senile) which criteria are for
the relationship and which criteria are meant to limit the end result.
For OUTER JOINs, it can certainly matter, but it depends on your desired
result. You may exclude rows by moving criteria from ON to WHERE or vice
versa. I don't know of any situations in INNER JOIN where this is true, but
I bet Itzik or Steve will reproduce one if it exists.
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:u8ljCwnfGHA.764@.TK2MSFTNGP03.phx.gbl...
MSSQl 2000
When creating a select statement with joins... does it matter where you
place additional where-clause criteria.
Considering the two examples below, is it more efficient to place additional
filtering criteria within the join section.? Does it weed out extra rows
before joining them? or should I put anything that is not pertinent to the
join itself down below in its own where clause?
EXAMPLE 1
SELECT
A.column_1
B.column_2
C.column_3
FROM
table_a A
inner join table_b B ON B.column_1 = A.column_1 and
B.column_2 = 1 and
B.column_3 = 'boys' and
B.column_4 between '01/01/06' and '01/31/06'
inner join table_c C ON C.column_2 = B.column_2
EXAMPLE 2
SELECT
A.column_1
B.column_2
C.column_3
FROM
table_a A
inner join table_b B ON B.column_1 = A.column_1 and
inner join table_c C ON C.column_2 = B.column_2
WHERE
B.column_2 = 1 and
B.column_3 = 'boys' and
B.column_4 between '01/01/06' and '01/31/06'|||Pre SQL 2000 SP4 I would say "Yes" it does matter.
Post SP4 I would say "No" it doesn't matter.
SP4 has given some huge performance gains at my site.
However, try it for yourself. Use Profiler / view the Execution plan etc.
To establish your version use SELECT @.@.VERSION.
http://www.aspfaq.com/SQL2000Builds.asp
--
HTH. Ryan
"rmg66" <rgwathney__xXx__primepro.com> wrote in message news:u8ljCwnfGHA.764
@.TK2MSFTNGP03.phx.gbl...
MSSQl 2000
When creating a select statement with joins... does it matter where you plac
e additional where-clause criteria.
Considering the two examples below, is it more efficient to place additional
filtering criteria within the join section.? Does it weed out extra rows be
fore joining them? or should I put anything that is not pertinent to the joi
n itself down below in its own where clause?
EXAMPLE 1
SELECT
A.column_1
B.column_2
C.column_3
FROM
table_a A
inner join table_b B ON B.column_1 = A.column_1 and
B.column_2 = 1 and
B.column_3 = 'boys' and
B.column_4 between '01/01/06' and '01/31/06'
inner join table_c C ON C.column_2 = B.column_2
EXAMPLE 2
SELECT
A.column_1
B.column_2
C.column_3
FROM
table_a A
inner join table_b B ON B.column_1 = A.column_1 and
inner join table_c C ON C.column_2 = B.column_2
WHERE
B.column_2 = 1 and
B.column_3 = 'boys' and
B.column_4 between '01/01/06' and '01/31/06'|||Thanks Aaron,
Actually I'm more concerned with performance at this point.
Any thoughts on that...
Robert
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message news:OUZNW4nfGH
A.2032@.TK2MSFTNGP02.phx.gbl...
> For INNER JOINs, I like to put the JOIN criteria in the ON clause and the
filtering criteria in the WHERE clause. This makes it
> very clear to anyone who inherits the code (or myself, when I go senile) w
hich criteria are for the relationship and which
> criteria are meant to limit the end result.
> For OUTER JOINs, it can certainly matter, but it depends on your desired r
esult. You may exclude rows by moving criteria from ON
> to WHERE or vice versa. I don't know of any situations in INNER JOIN wher
e this is true, but I bet Itzik or Steve will reproduce
> one if it exists.
>
>
> "rmg66" <rgwathney__xXx__primepro.com> wrote in message news:u8ljCwnfGHA.7
64@.TK2MSFTNGP03.phx.gbl...
> MSSQl 2000
> When creating a select statement with joins... does it matter where you pl
ace additional where-clause criteria.
> Considering the two examples below, is it more efficient to place addition
al filtering criteria within the join section.? Does it
> weed out extra rows before joining them? or should I put anything that is
not pertinent to the join itself down below in its own
> where clause?
> EXAMPLE 1
> SELECT
> A.column_1
> B.column_2
> C.column_3
> FROM
> table_a A
> inner join table_b B ON B.column_1 = A.column_1 and
> B.column_2 = 1 and
> B.column_3 = 'boys' and
> B.column_4 between '01/01/06' and '01/31/06'
> inner join table_c C ON C.column_2 = B.column_2
>
> EXAMPLE 2
> SELECT
> A.column_1
> B.column_2
> C.column_3
> FROM
> table_a A
> inner join table_b B ON B.column_1 = A.column_1 and
> inner join table_c C ON C.column_2 = B.column_2
> WHERE
> B.column_2 = 1 and
> B.column_3 = 'boys' and
> B.column_4 between '01/01/06' and '01/31/06'
>
>
>
>|||I think as a rule, the optimizer will do the same thing regardless, although
as Ryan pointed out pre SQL 200 SP 4 it makes a difference. When optimizing
the engine will check only so many possible paths before determining which
one to use, so on larger more complex queries the order of the joins and
criteria can determine which paths get evaluated before it gives up and
chooses one.
I think the bottom line is theoretically it doesn't matter, but the only way
to be totally certain is to test it out both ways. Not just the location of
the criteria, but the order of the tables as well. For simpler queries with
a handful of joins and filter criteria, when the optimizer can afford to
calculate every possibility, it should work the same. If you have dozens of
tables and just as many filters involved, it is worth playing with different
scenarios to see if it makes a difference. With more complex queries the
optimizer can find literally billions of possible execution plans, and
influencing it to look at the right ones can be hit or miss.
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:O1pbODofGHA.4776@.TK2MSFTNGP05.phx.gbl...
> Thanks Aaron,
> Actually I'm more concerned with performance at this point.
> Any thoughts on that...
> Robert
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
message news:OUZNW4nfGHA.2032@.TK2MSFTNGP02.phx.gbl...
the filtering criteria in the WHERE clause. This makes it
which criteria are for the relationship and which
result. You may exclude rows by moving criteria from ON
where this is true, but I bet Itzik or Steve will reproduce
news:u8ljCwnfGHA.764@.TK2MSFTNGP03.phx.gbl...
place additional where-clause criteria.
additional filtering criteria within the join section.? Does it
is not pertinent to the join itself down below in its own
'01/31/06'
>|||I just did one of the MS Courses last w which covered this. Whilst the
reality seems to be what the others have said. MS considers is more correct
to place the items on the join itself, as this will help SQL to choose the
best execution plan. The idea is that placing more items on the join, means
that the selected table will return less results, before the Join is
executed.
The blurb says that the Join syntax is evaluated before the Where syntax.
The course is the optimising and tuning course for SQL 2005!
My personal view is to use the Join in preference to the Where clause, I
find that it helps to make the syntax clearer and easier to understand.
Regards
Colin Dawson
www.cjdawson.com
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:eqcg3SofGHA.2208@.TK2MSFTNGP05.phx.gbl...
>I think as a rule, the optimizer will do the same thing regardless,
>although
> as Ryan pointed out pre SQL 200 SP 4 it makes a difference. When
> optimizing
> the engine will check only so many possible paths before determining which
> one to use, so on larger more complex queries the order of the joins and
> criteria can determine which paths get evaluated before it gives up and
> chooses one.
> I think the bottom line is theoretically it doesn't matter, but the only
> way
> to be totally certain is to test it out both ways. Not just the location
> of
> the criteria, but the order of the tables as well. For simpler queries
> with
> a handful of joins and filter criteria, when the optimizer can afford to
> calculate every possibility, it should work the same. If you have dozens
> of
> tables and just as many filters involved, it is worth playing with
> different
> scenarios to see if it makes a difference. With more complex queries the
> optimizer can find literally billions of possible execution plans, and
> influencing it to look at the right ones can be hit or miss.
> "rmg66" <rgwathney__xXx__primepro.com> wrote in message
> news:O1pbODofGHA.4776@.TK2MSFTNGP05.phx.gbl...
> message news:OUZNW4nfGHA.2032@.TK2MSFTNGP02.phx.gbl...
> the filtering criteria in the WHERE clause. This makes it
> which criteria are for the relationship and which
> result. You may exclude rows by moving criteria from ON
> where this is true, but I bet Itzik or Steve will reproduce
> news:u8ljCwnfGHA.764@.TK2MSFTNGP03.phx.gbl...
> place additional where-clause criteria.
> additional filtering criteria within the join section.? Does it
> is not pertinent to the join itself down below in its own
> '01/31/06'
>|||> The blurb says that the Join syntax is evaluated before the Where syntax.
That is only the logical order. For inner joins, it doesn't matter, and I do
n't even think that the
optimizer know what join type you expressed (the query is transformed into a
tree structure before
the optimizer gets hold of it). The optimizer is free to transform the query
in any way as long as
it returns the same information as if it executed the query as per the rules
for the logical order.

> The course is the optimizing and tuning course for SQL 2005!
Interesting. Which one? There are two such courses, one for "admins" and one
for "developers". Also,
can you point to the module and perhaps even page number and I'll have a loo
k at how they phrase it.

> My personal view is to use the Join in preference to the Where clause, I f
ind that it helps to
> make the syntax clearer and easier to understand.
I absolutely agree.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:C1Icg.74278$wl.24621@.text.news.blueyonder.co.uk...
>I just did one of the MS Courses last w which covered this. Whilst the
reality seems to be what
>the others have said. MS considers is more correct to place the items on t
he join itself, as this
>will help SQL to choose the best execution plan. The idea is that placing
more items on the join,
>means that the selected table will return less results, before the Join is
executed.
> The blurb says that the Join syntax is evaluated before the Where syntax.
The course is the
> optimising and tuning course for SQL 2005!
> My personal view is to use the Join in preference to the Where clause, I f
ind that it helps to
> make the syntax clearer and easier to understand.
> Regards
> Colin Dawson
> www.cjdawson.com
>
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:eqcg3SofGHA.2208@.TK2MSFTNGP05.phx.gbl...
>|||Hello Tibor
The course is 2784A: Tuning and Optimising Database Queries User Microsoft
SQL Server 2005
The bit that I was referring two use in Unit 3. Specifically the Query
logical flow diagram on page 2.
Basically it shows the flow as
From & Join --> Where --> Select -- > .... (lots more stuff)
From experience I do agree that it doesn't seem to matter as the query
optimiser does make changes to the query as typed, into how it wants to
produce the results.
Regards
Colin Dawson
www.cjdawson.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OHntJPpfGHA.4304@.TK2MSFTNGP05.phx.gbl...
> That is only the logical order. For inner joins, it doesn't matter, and I
> don't even think that the optimizer know what join type you expressed (the
> query is transformed into a tree structure before the optimizer gets hold
> of it). The optimizer is free to transform the query in any way as long as
> it returns the same information as if it executed the query as per the
> rules for the logical order.
>
> Interesting. Which one? There are two such courses, one for "admins" and
> one for "developers". Also, can you point to the module and perhaps even
> page number and I'll have a look at how they phrase it.
>
> I absolutely agree.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
> news:C1Icg.74278$wl.24621@.text.news.blueyonder.co.uk...
>|||My personal preference is to use the primary key/foreign key relation
columns in the join clause, and all other predicates in the where
clause. This is a very consistent syntax that underscores the table
relations and automatically moves all filters to the where clause.
As mentioned before it is a different story for outer joins...
Gert-Jan
Tibor Karaszi wrote:
>
> That is only the logical order. For inner joins, it doesn't matter, and I
don't even think that the
> optimizer know what join type you expressed (the query is transformed into
a tree structure before
> the optimizer gets hold of it). The optimizer is free to transform the que
ry in any way as long as
> it returns the same information as if it executed the query as per the rul
es for the logical order.
>
> Interesting. Which one? There are two such courses, one for "admins" and o
ne for "developers". Also,
> can you point to the module and perhaps even page number and I'll have a l
ook at how they phrase it.
>
> I absolutely agree.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> "Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
> news:C1Icg.74278$wl.24621@.text.news.blueyonder.co.uk...|||Colin,

> The course is 2784A: Tuning and Optimising Database Queries User Microsoft
SQL Server 2005
> The bit that I was referring two use in Unit 3. Specifically the Query log
ical flow diagram on
> page 2.
Thanks. I had a quick look through the courses after I posted prior reply, a
nd I guessed this was
the one. The important part here is that it is the *logical* flow. Quote fro
m the same page:
"
Note that
although there is a guaranteed logical order, this is not true of the actual
physical order. The
query
processor can process the query in a different order but still ensure the sa
me results, if it can
find a
more efficient method for doing so.
"
If the optimizer had to respect the logical flow, then almost every query wo
uld give us horrendous
performance:
FROM, grab all columns, and even cross join if old style join syntax
WHERE remove the rows that doesn't satisfies the conditions (including the j
oin if old-style join)
GROUP BY
HAVING
SELECT, until now we had all the columns from all the tables
ORDER BY, not until now could we sort the rows
TOP, ouch, all rows had to be sorted until we throw away all but "top n".
:-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:VsIcg.74297$wl.32163@.text.news.blueyonder.co.uk...
> Hello Tibor
> The course is 2784A: Tuning and Optimising Database Queries User Microsoft
SQL Server 2005
> The bit that I was referring two use in Unit 3. Specifically the Query log
ical flow diagram on
> page 2.
> Basically it shows the flow as
> From & Join --> Where --> Select -- > .... (lots more stuff)
>
> From experience I do agree that it doesn't seem to matter as the query opt
imiser does make changes
> to the query as typed, into how it wants to produce the results.
> Regards
> Colin Dawson
> www.cjdawson.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:OHntJPpfGHA.4304@.TK2MSFTNGP05.phx.gbl...
>

Does creating KPI require cube reprocess?

The Analysis Service help says that after creating a KPI you need to process the cube in order to browse it. However, that's not the behavior I'm seeing - it looks like the KPI becomes available immediately. What behavior should I be expecting here?

The Books Online is being incorrect in this case. You dont need to re-process your cube after creating KPI's.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

does a table exist?

Does anyone know if you can check if an object (eg a table) exists. I am
having trouble with procs that drop tables before creating them. Other users
get the message, "cannot drop table tblTest ... doesn't exist in the system
catalog".
I need to do something like,
If tblTest exists
Drop tblTest
...
then recreate the table
ThanksNickl wrote:
> Does anyone know if you can check if an object (eg a table) exists. I am
> having trouble with procs that drop tables before creating them. Other use
rs
> get the message, "cannot drop table tblTest ... doesn't exist in the syste
m
> catalog".
> I need to do something like,
> If tblTest exists
> Drop tblTest
> ...
> then recreate the table
> Thanks
>
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Say I want to check if table "Departments" exists (from the "Generate
SQL Scripts" option in Enterprise Manager):
if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[Departments]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Departments]
Just substitute your table name (and owner [dbo]?) for Departments.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQgA9TYechKqOuFEgEQIBvwCgyWX4oG3wDVTW
URVNdrLkAz6/WKUAnRak
h8BmLTTPTkyE2MvNYl2mkVGH
=UYde
--END PGP SIGNATURE--|||Hello Nickl,
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tblTest'
Aaron Weiker
http://aaronweiker.com/
http://sqlprogrammer.org/

> Does anyone know if you can check if an object (eg a table) exists. I
> am
> having trouble with procs that drop tables before creating them. Other
> users
> get the message, "cannot drop table tblTest ... doesn't exist in the
> system
> catalog".
> I need to do something like,
> If tblTest exists
> Drop tblTest
> ...
> then recreate the table
> Thanks|||Or...
if object_id('your_tb','U') is not null
drop table your_tb
-oj
"Nickl" <Nickl@.discussions.microsoft.com> wrote in message
news:4A514243-6D69-427F-8F0B-2B6D1B4481E3@.microsoft.com...
> Does anyone know if you can check if an object (eg a table) exists. I am
> having trouble with procs that drop tables before creating them. Other
> users
> get the message, "cannot drop table tblTest ... doesn't exist in the
> system
> catalog".
> I need to do something like,
> If tblTest exists
> Drop tblTest
> ...
> then recreate the table
> Thanks
>|||Thanks everyone, good methods all. I really like yours oj, it looks very bri
ef
NIck Lindner
"oj" wrote:

> Or...
> if object_id('your_tb','U') is not null
> drop table your_tb
> --
> -oj
>
> "Nickl" <Nickl@.discussions.microsoft.com> wrote in message
> news:4A514243-6D69-427F-8F0B-2B6D1B4481E3@.microsoft.com...
>
>

Document Map

Problems with Document Map creating a side scroll to get to Up/Down Scroll for the Map itself. There are two scroll bars once you side scroll over in the map window. Anyone seen this or know how to fix? Or Anyone know how to change any of the properties on the Document Map or map window?

Thanks!!!!!

There has to be a way to change the properties on the Document Map Window - Come on MSFT guys......|||So no one knows how to change any configuration on the Document map or why it creates a side scroll and a seperate vertical scroll from the main report?|||seriously?!?!?!|||truely!?!?!?!|||Can you try posting a screen shot of what you are describing?|||How do I post a screen shot?|||

You can email it to me @. john[dot]gallardo [at] microsoft[dot]com.

|||

Hi, I wondered if you ever got a resolution on this. I have a similar problem that once I deploy the report the scroll bar for the document map does not let you scroll to the document map. It only allows you to scroll through what initially fits when the report is first displayed. Before it was deployed, when the report was previewed, the scrolling worked fine... it was only after it was deployed that I could not scroll.

Your issue was a little different, but since it did have to do with the scroll bar of the document map, I'm hoping you got a little further to see how/where/and if it can be configured.

Thank you very much, Julie

|||We are working on it....hopefully we get it resolved soon and I will let you know|||It is a known issue for which we have a repro for. Unfortunately there is not a very good workaround other than simply dragging the frame containing the document map larger.

Document Map

Problems with Document Map creating a side scroll to get to Up/Down Scroll for the Map itself. There are two scroll bars once you side scroll over in the map window. Anyone seen this or know how to fix? Or Anyone know how to change any of the properties on the Document Map or map window?

Thanks!!!!!

There has to be a way to change the properties on the Document Map Window - Come on MSFT guys......|||So no one knows how to change any configuration on the Document map or why it creates a side scroll and a seperate vertical scroll from the main report?|||seriously?!?!?!|||truely!?!?!?!|||Can you try posting a screen shot of what you are describing?|||How do I post a screen shot?|||

You can email it to me @. john[dot]gallardo [at] microsoft[dot]com.

|||

Hi, I wondered if you ever got a resolution on this. I have a similar problem that once I deploy the report the scroll bar for the document map does not let you scroll to the document map. It only allows you to scroll through what initially fits when the report is first displayed. Before it was deployed, when the report was previewed, the scrolling worked fine... it was only after it was deployed that I could not scroll.

Your issue was a little different, but since it did have to do with the scroll bar of the document map, I'm hoping you got a little further to see how/where/and if it can be configured.

Thank you very much, Julie

|||We are working on it....hopefully we get it resolved soon and I will let you know|||It is a known issue for which we have a repro for. Unfortunately there is not a very good workaround other than simply dragging the frame containing the document map larger.