BI Example for SQL Server 2005

Hi all,

I'm trying to find an example on BI using SQL Server 2005. Ideally something that would allow me to analyze Customer and Product information like Sales per period, per product line, etc. Something really really standard that the user could query directly from Excel 2003.

I'm sure such simple example exists, I'm looking for the DB schema, cubes, dimensions already setup in SQL 2005. My question is if the are scripts available so my only task would be to import our ERP data into SQL and make sure it gets processed.

I'm not interested in really fancy stuff SQL can deliver, just something that is easy to start with and build on.

I'd appreciate it if you can point me to any direction...

Thanks,

ST

The AdventureWorks DW sample shipping with 2005 is meant for this very purpose.

BI Example for SQL Server 2005

Hi all,

I'm trying to find an example on BI using SQL Server 2005. Ideally something that would allow me to analyze Customer and Product information like Sales per period, per product line, etc. Something really really standard that the user could query directly from Excel 2003.

I'm sure such simple example exists, I'm looking for the DB schema, cubes, dimensions already setup in SQL 2005. My question is if the are scripts available so my only task would be to import our ERP data into SQL and make sure it gets processed.

I'm not interested in really fancy stuff SQL can deliver, just something that is easy to start with and build on.

I'd appreciate it if you can point me to any direction...

Thanks,

ST

The AdventureWorks DW sample shipping with 2005 is meant for this very purpose.

Best way for Stored Procedure to update fields conditionally?

I want to write a stored procedure that updates a record, only updating
the fields where the value passed is not null.
So ideally in the UPDATE statement I want something like
CREATE PROCEDURE myproc
@.param1 nvarchar(20),
@.param2 nvarchar(20)
UPDATE myrec
SET
IF @.param1 IS NOT null
param1=@.param1,
IF @.param2 IS NOT null
param1=@.param2
etc.
but I'm guessing I can't do that.
Obviously I don't want to have to read the record first to compare the
current values with the ones I'm passing in.
What's the best (shortest, most efficient) way to do this?
Thanks,
ChrisNHi,
what about set Col1 = COALESCE(@.Param1, Col1)
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||First off I would ask why you care? If you are going to update the row the
extra overhead to update columns that don't need to is extremely small and
trivial compared to the other operations that must occur for an update.
Second how can you check for NULL? Are you saying none of your columns
allow NULL's? If so how would you pass in a NULL?
--
Andrew J. Kelly SQL MVP
"ChrisN" <yeltsin27@.yahoo.co.uk> wrote in message
news:1159084549.818998.105970@.e3g2000cwe.googlegroups.com...
>I want to write a stored procedure that updates a record, only updating
> the fields where the value passed is not null.
> So ideally in the UPDATE statement I want something like
> CREATE PROCEDURE myproc
> @.param1 nvarchar(20),
> @.param2 nvarchar(20)
> UPDATE myrec
> SET
> IF @.param1 IS NOT null
> param1=@.param1,
> IF @.param2 IS NOT null
> param1=@.param2
> etc.
> but I'm guessing I can't do that.
> Obviously I don't want to have to read the record first to compare the
> current values with the ones I'm passing in.
> What's the best (shortest, most efficient) way to do this?
> Thanks,
> ChrisN
>|||If you want to update based on the values passed in, you can do something
such as this
IF @.param1 IS NOT NULL
BEGIN
UPDATE tabel
SET column1 = @.param1
WHERE ...
END
IF @.param2 IS NOT NULL
BEGIN
UPDATE table
SET column2 = @.param2
WHERE ..
END
Or you could split it up into multiple stored proceduces.
Why you would want to do this, I am not sure...
Keep in mind that when using IF conditions in a stored procedure, SQL is
less likely to re-use execution plans, lessening the benefit of using a
stored procedure.
"ChrisN" <yeltsin27@.yahoo.co.uk> wrote in message
news:1159084549.818998.105970@.e3g2000cwe.googlegroups.com...
>I want to write a stored procedure that updates a record, only updating
> the fields where the value passed is not null.
> So ideally in the UPDATE statement I want something like
> CREATE PROCEDURE myproc
> @.param1 nvarchar(20),
> @.param2 nvarchar(20)
> UPDATE myrec
> SET
> IF @.param1 IS NOT null
> param1=@.param1,
> IF @.param2 IS NOT null
> param1=@.param2
> etc.
> but I'm guessing I can't do that.
> Obviously I don't want to have to read the record first to compare the
> current values with the ones I'm passing in.
> What's the best (shortest, most efficient) way to do this?
> Thanks,
> ChrisN
>

Best way for Stored Procedure to update fields conditionally?

I want to write a stored procedure that updates a record, only updating
the fields where the value passed is not null.
So ideally in the UPDATE statement I want something like
CREATE PROCEDURE myproc
@.param1 nvarchar(20),
@.param2 nvarchar(20)
UPDATE myrec
SET
IF @.param1 IS NOT null
param1=@.param1,
IF @.param2 IS NOT null
param1=@.param2
etc.
but I'm guessing I can't do that.
Obviously I don't want to have to read the record first to compare the
current values with the ones I'm passing in.
What's the best (shortest, most efficient) way to do this?
Thanks,
ChrisNHi,
what about set Col1 = COALESCE(@.Param1, Col1)
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||First off I would ask why you care? If you are going to update the row the
extra overhead to update columns that don't need to is extremely small and
trivial compared to the other operations that must occur for an update.
Second how can you check for NULL? Are you saying none of your columns
allow NULL's? If so how would you pass in a NULL?
Andrew J. Kelly SQL MVP
"ChrisN" <yeltsin27@.yahoo.co.uk> wrote in message
news:1159084549.818998.105970@.e3g2000cwe.googlegroups.com...
>I want to write a stored procedure that updates a record, only updating
> the fields where the value passed is not null.
> So ideally in the UPDATE statement I want something like
> CREATE PROCEDURE myproc
> @.param1 nvarchar(20),
> @.param2 nvarchar(20)
> UPDATE myrec
> SET
> IF @.param1 IS NOT null
> param1=@.param1,
> IF @.param2 IS NOT null
> param1=@.param2
> etc.
> but I'm guessing I can't do that.
> Obviously I don't want to have to read the record first to compare the
> current values with the ones I'm passing in.
> What's the best (shortest, most efficient) way to do this?
> Thanks,
> ChrisN
>|||If you want to update based on the values passed in, you can do something
such as this
IF @.param1 IS NOT NULL
BEGIN
UPDATE tabel
SET column1 = @.param1
WHERE ...
END
IF @.param2 IS NOT NULL
BEGIN
UPDATE table
SET column2 = @.param2
WHERE ..
END
Or you could split it up into multiple stored proceduces.
Why you would want to do this, I am not sure...
Keep in mind that when using IF conditions in a stored procedure, SQL is
less likely to re-use execution plans, lessening the benefit of using a
stored procedure.
"ChrisN" <yeltsin27@.yahoo.co.uk> wrote in message
news:1159084549.818998.105970@.e3g2000cwe.googlegroups.com...
>I want to write a stored procedure that updates a record, only updating
> the fields where the value passed is not null.
> So ideally in the UPDATE statement I want something like
> CREATE PROCEDURE myproc
> @.param1 nvarchar(20),
> @.param2 nvarchar(20)
> UPDATE myrec
> SET
> IF @.param1 IS NOT null
> param1=@.param1,
> IF @.param2 IS NOT null
> param1=@.param2
> etc.
> but I'm guessing I can't do that.
> Obviously I don't want to have to read the record first to compare the
> current values with the ones I'm passing in.
> What's the best (shortest, most efficient) way to do this?
> Thanks,
> ChrisN
>

Best way for Stored Procedure to update fields conditionally?

I want to write a stored procedure that updates a record, only updating
the fields where the value passed is not null.
So ideally in the UPDATE statement I want something like
CREATE PROCEDURE myproc
@.param1 nvarchar(20),
@.param2 nvarchar(20)
UPDATE myrec
SET
IF @.param1 IS NOT null
param1=@.param1,
IF @.param2 IS NOT null
param1=@.param2
etc.
but I'm guessing I can't do that.
Obviously I don't want to have to read the record first to compare the
current values with the ones I'm passing in.
What's the best (shortest, most efficient) way to do this?
Thanks,
ChrisN
Hi,
what about set Col1 = COALESCE(@.Param1, Col1)
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||First off I would ask why you care? If you are going to update the row the
extra overhead to update columns that don't need to is extremely small and
trivial compared to the other operations that must occur for an update.
Second how can you check for NULL? Are you saying none of your columns
allow NULL's? If so how would you pass in a NULL?
Andrew J. Kelly SQL MVP
"ChrisN" <yeltsin27@.yahoo.co.uk> wrote in message
news:1159084549.818998.105970@.e3g2000cwe.googlegro ups.com...
>I want to write a stored procedure that updates a record, only updating
> the fields where the value passed is not null.
> So ideally in the UPDATE statement I want something like
> CREATE PROCEDURE myproc
> @.param1 nvarchar(20),
> @.param2 nvarchar(20)
> UPDATE myrec
> SET
> IF @.param1 IS NOT null
> param1=@.param1,
> IF @.param2 IS NOT null
> param1=@.param2
> etc.
> but I'm guessing I can't do that.
> Obviously I don't want to have to read the record first to compare the
> current values with the ones I'm passing in.
> What's the best (shortest, most efficient) way to do this?
> Thanks,
> ChrisN
>
|||If you want to update based on the values passed in, you can do something
such as this
IF @.param1 IS NOT NULL
BEGIN
UPDATE tabel
SET column1 = @.param1
WHERE ...
END
IF @.param2 IS NOT NULL
BEGIN
UPDATE table
SET column2 = @.param2
WHERE ..
END
Or you could split it up into multiple stored proceduces.
Why you would want to do this, I am not sure...
Keep in mind that when using IF conditions in a stored procedure, SQL is
less likely to re-use execution plans, lessening the benefit of using a
stored procedure.
"ChrisN" <yeltsin27@.yahoo.co.uk> wrote in message
news:1159084549.818998.105970@.e3g2000cwe.googlegro ups.com...
>I want to write a stored procedure that updates a record, only updating
> the fields where the value passed is not null.
> So ideally in the UPDATE statement I want something like
> CREATE PROCEDURE myproc
> @.param1 nvarchar(20),
> @.param2 nvarchar(20)
> UPDATE myrec
> SET
> IF @.param1 IS NOT null
> param1=@.param1,
> IF @.param2 IS NOT null
> param1=@.param2
> etc.
> but I'm guessing I can't do that.
> Obviously I don't want to have to read the record first to compare the
> current values with the ones I'm passing in.
> What's the best (shortest, most efficient) way to do this?
> Thanks,
> ChrisN
>