Invert values

How do I go about inverting values. That is:
-change all positive to negative and change all negative to positive
I have tried using abd function but that only changes negative to positive??
SET amt = Abs(amt)
What the best way of performing this in sql?Set Amt = Amt * -1

Invalid object name in stored procedure

-- In SQL Server 2000
--When run from Query Analyzer I correctly get identification of line
numbers having duplicate values of SKU_NameUsedBySCS:
-- LineNumber1 LineNumber2
-- 2 5
-- but when I try to create a stored procedure having this same code I get:
-- Invalid object name '#x'.
IF not EXISTS (SELECT name FROM sysobjects WHERE name = 'PermTable' )
begin
create table PermTable (scs_id int, sku_nameusedbyscs nvarchar(40))
insert into PermTable VALUES (11, 'name23')
insert into PermTable VALUES (11, 'name81')
insert into PermTable VALUES (11, 'name27')
insert into PermTable VALUES (11, 'name88')
insert into PermTable VALUES (11, 'name81')
end
declare @.SCS_ID int
set @.SCS_ID =11
set nocount on
select identity(int,1,1) as Sequence, scs_id,sku_nameusedbyscs into #x from
PermTable WHERE SCS_ID =@.SCS_ID
go
select top 40 min(Sequence) as LineNumber1, max(Sequence) as LineNumber2
from #x GROUP BY SKU_NameUsedBySCS having count(*) > 1
drop table #x
gohello steve, did you check previous error messages? is you database Case
Sensitive? If it is, then the select into statement must have the names of
your fields in lower case.
hope this helps.
"SteveInSC" wrote:

> -- In SQL Server 2000
> --When run from Query Analyzer I correctly get identification of line
> numbers having duplicate values of SKU_NameUsedBySCS:
> -- LineNumber1 LineNumber2
> -- 2 5
> -- but when I try to create a stored procedure having this same code I get
:
> -- Invalid object name '#x'.
>
> IF not EXISTS (SELECT name FROM sysobjects WHERE name = 'PermTable' )
> begin
> create table PermTable (scs_id int, sku_nameusedbyscs nvarchar(40))
> insert into PermTable VALUES (11, 'name23')
> insert into PermTable VALUES (11, 'name81')
> insert into PermTable VALUES (11, 'name27')
> insert into PermTable VALUES (11, 'name88')
> insert into PermTable VALUES (11, 'name81')
> end
> declare @.SCS_ID int
> set @.SCS_ID =11
> set nocount on
> select identity(int,1,1) as Sequence, scs_id,sku_nameusedbyscs into #x fro
m
> PermTable WHERE SCS_ID =@.SCS_ID
> go
> select top 40 min(Sequence) as LineNumber1, max(Sequence) as LineNumber2
> from #x GROUP BY SKU_NameUsedBySCS having count(*) > 1
> drop table #x
> go
>
>|||Hi
And where do you create the temporary table #x?
John
"SteveInSC" wrote:

> -- In SQL Server 2000
> --When run from Query Analyzer I correctly get identification of line
> numbers having duplicate values of SKU_NameUsedBySCS:
> -- LineNumber1 LineNumber2
> -- 2 5
> -- but when I try to create a stored procedure having this same code I get
:
> -- Invalid object name '#x'.
>
> IF not EXISTS (SELECT name FROM sysobjects WHERE name = 'PermTable' )
> begin
> create table PermTable (scs_id int, sku_nameusedbyscs nvarchar(40))
> insert into PermTable VALUES (11, 'name23')
> insert into PermTable VALUES (11, 'name81')
> insert into PermTable VALUES (11, 'name27')
> insert into PermTable VALUES (11, 'name88')
> insert into PermTable VALUES (11, 'name81')
> end
> declare @.SCS_ID int
> set @.SCS_ID =11
> set nocount on
> select identity(int,1,1) as Sequence, scs_id,sku_nameusedbyscs into #x fro
m
> PermTable WHERE SCS_ID =@.SCS_ID
> go
> select top 40 min(Sequence) as LineNumber1, max(Sequence) as LineNumber2
> from #x GROUP BY SKU_NameUsedBySCS having count(*) > 1
> drop table #x
> go
>
>|||At the time the proc is compiled the #x table does not exist as it's
created at runtime. So when you try to compile the code to get an
execution plan the query optimiser cannot create a plan involving #x
because it doesn't yet exist.
Try creating the temp table explicitly in the proc and then inserting
into it with a normal INSERT statement (rather than SELECT ... INTO).
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
SteveInSC wrote:

>-- In SQL Server 2000
>--When run from Query Analyzer I correctly get identification of line
>numbers having duplicate values of SKU_NameUsedBySCS:
>-- LineNumber1 LineNumber2
>-- 2 5
>-- but when I try to create a stored procedure having this same code I get:
>-- Invalid object name '#x'.
>
>IF not EXISTS (SELECT name FROM sysobjects WHERE name = 'PermTable' )
> begin
> create table PermTable (scs_id int, sku_nameusedbyscs nvarchar(40))
> insert into PermTable VALUES (11, 'name23')
> insert into PermTable VALUES (11, 'name81')
> insert into PermTable VALUES (11, 'name27')
> insert into PermTable VALUES (11, 'name88')
> insert into PermTable VALUES (11, 'name81')
> end
>declare @.SCS_ID int
>set @.SCS_ID =11
>set nocount on
>select identity(int,1,1) as Sequence, scs_id,sku_nameusedbyscs into #x from
>PermTable WHERE SCS_ID =@.SCS_ID
>go
>select top 40 min(Sequence) as LineNumber1, max(Sequence) as LineNumber2
>from #x GROUP BY SKU_NameUsedBySCS having count(*) > 1
>drop table #x
>go
>
>
>|||Hi Steve
If you have simply created the stored procedure by wrapping the SQL below in
a create procedure call then you have a GO in the middle of the declaration.
This will terminate the declaration.
Query Analyser will then try to run the later commands as immediate
commands. As the table is created by the SELECT ... INTO inside the
definition it will not find it for the later SELECT statement.
Try removing the GO statement in the middle of the declaration if there is
one.
As a separate point I would recommend using a Table variable (you know the
structure you want) as the scope is much better defined.
I hope this helps
Alasdair Russell
"SteveInSC" wrote:

> -- In SQL Server 2000
> --When run from Query Analyzer I correctly get identification of line
> numbers having duplicate values of SKU_NameUsedBySCS:
> -- LineNumber1 LineNumber2
> -- 2 5
> -- but when I try to create a stored procedure having this same code I get
:
> -- Invalid object name '#x'.
>
> IF not EXISTS (SELECT name FROM sysobjects WHERE name = 'PermTable' )
> begin
> create table PermTable (scs_id int, sku_nameusedbyscs nvarchar(40))
> insert into PermTable VALUES (11, 'name23')
> insert into PermTable VALUES (11, 'name81')
> insert into PermTable VALUES (11, 'name27')
> insert into PermTable VALUES (11, 'name88')
> insert into PermTable VALUES (11, 'name81')
> end
> declare @.SCS_ID int
> set @.SCS_ID =11
> set nocount on
> select identity(int,1,1) as Sequence, scs_id,sku_nameusedbyscs into #x fro
m
> PermTable WHERE SCS_ID =@.SCS_ID
> go
> select top 40 min(Sequence) as LineNumber1, max(Sequence) as LineNumber2
> from #x GROUP BY SKU_NameUsedBySCS having count(*) > 1
> drop table #x
> go
>
>|||Comment out the first "GO" and you will be all set.
Try this:
create procedure sp_abc as
IF not EXISTS (SELECT name FROM sysobjects WHERE name = 'PermTable' )
begin
create table PermTable (scs_id int, sku_nameusedbyscs nvarchar(40))
insert into PermTable VALUES (11, 'name23')
insert into PermTable VALUES (11, 'name81')
insert into PermTable VALUES (11, 'name27')
insert into PermTable VALUES (11, 'name88')
insert into PermTable VALUES (11, 'name81')
end
declare @.SCS_ID int
set @.SCS_ID =11
set nocount on
select identity(int,1,1) as Sequence, scs_id,sku_nameusedbyscs into #x
from
PermTable WHERE SCS_ID =@.SCS_ID
-- ############## MySQLServer ############ --go
select top 40 min(Sequence) as LineNumber1, max(Sequence) as
LineNumber2
from #x GROUP BY SKU_NameUsedBySCS having count(*) > 1
drop table #x
go
SteveInSC wrote:
> -- In SQL Server 2000
> --When run from Query Analyzer I correctly get identification of line
> numbers having duplicate values of SKU_NameUsedBySCS:
> -- LineNumber1 LineNumber2
> -- 2 5
> -- but when I try to create a stored procedure having this same code I get
:
> -- Invalid object name '#x'.
>
> IF not EXISTS (SELECT name FROM sysobjects WHERE name = 'PermTable' )
> begin
> create table PermTable (scs_id int, sku_nameusedbyscs nvarchar(40))
> insert into PermTable VALUES (11, 'name23')
> insert into PermTable VALUES (11, 'name81')
> insert into PermTable VALUES (11, 'name27')
> insert into PermTable VALUES (11, 'name88')
> insert into PermTable VALUES (11, 'name81')
> end
> declare @.SCS_ID int
> set @.SCS_ID =11
> set nocount on
> select identity(int,1,1) as Sequence, scs_id,sku_nameusedbyscs into #x fro
m
> PermTable WHERE SCS_ID =@.SCS_ID
> go
> select top 40 min(Sequence) as LineNumber1, max(Sequence) as LineNumber2
> from #x GROUP BY SKU_NameUsedBySCS having count(*) > 1
> drop table #x
> go|||You forgot to remove the "GO" before the "SELECT TOP 40".
Razvan

Invalid column name

I am writing sql script where I have to get colum values from table and
do some processing and then delete those column. This is a new version
of the application and we don't need those columns anymore.
But the problem is that I need to be able to run this script more then
once without any errors. So, second time when I run it, it gives an
error that Invalid column name.
Before I get values from those columns, I check if they exists or not
and then get the value, but still it gives error, so I put everything
as string and use EXEC to execute that string - but still.
I don't know what to do.
I am copying my code here.
DECLARE @.value VARCHAR(8000)
SELECT @.value = 'SELECT @.Dining_Mod = 0 ' +
' IF (EXISTS(SELECT * FROM dbo.syscolumns WHERE name IN
(''DINING_ROOM_MOD_SQFT'') ' +
' AND id = (SELECT id FROM dbo.sysobjects WHERE id =
object_id(N''[dbo].[RESTAURANT]'') AND OBJECTPROPERTY(id,
N''IsUserTable'') = 1))) ' +
' SELECT @.Dining_Mod = DINING_ROOM_MOD_SQFT ' +
' FROM RESTAURANT ' +
' WHERE RESTAURANT_ID = @.Restaurant_Id ' +
' SELECT @.Dining_Mod = ISNULL(@.Dining_Mod, 0) '
exec (@.value)
The error I get is Invalid column name 'DINING_ROOM_MOD_SQFT'.
Anybody has any idea?
Thanks
Adnan Masood
www.newbalanceindy.comThis is just an idea, so you'll have to do the coding yourself (let me
know if that's a problem), but perhaps it would be better to not try to
do so much control flow in the dynamic sql. instead you could use the
stored procedures sp_tables and sp_columns, a couple nested of cursors
looping through these basically gives you a map of your database (not
very efficient, but dynamic without using exec). Once you've got this
you could build up a much more lightweight part of the dynamic sql,
making it far easier to debug.
As for why you're getting that error - are you regenerating the dynamic
sql on the second run? perhaps using sp_executesql will be get around
it as it will regenerate the query plan.
Cheers
Will|||Print the contents of the @.value variable and see what is wrong. If you don'
t find it, post it here.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sehboo" <MasoodAdnan@.gmail.com> wrote in message
news:1145289437.588507.216360@.i39g2000cwa.googlegroups.com...
>I am writing sql script where I have to get colum values from table and
> do some processing and then delete those column. This is a new version
> of the application and we don't need those columns anymore.
> But the problem is that I need to be able to run this script more then
> once without any errors. So, second time when I run it, it gives an
> error that Invalid column name.
> Before I get values from those columns, I check if they exists or not
> and then get the value, but still it gives error, so I put everything
> as string and use EXEC to execute that string - but still.
> I don't know what to do.
> I am copying my code here.
> DECLARE @.value VARCHAR(8000)
> SELECT @.value = 'SELECT @.Dining_Mod = 0 ' +
> ' IF (EXISTS(SELECT * FROM dbo.syscolumns WHERE name IN
> (''DINING_ROOM_MOD_SQFT'') ' +
> ' AND id = (SELECT id FROM dbo.sysobjects WHERE id =
> object_id(N''[dbo].[RESTAURANT]'') AND OBJECTPROPERTY(id,
> N''IsUserTable'') = 1))) ' +
> ' SELECT @.Dining_Mod = DINING_ROOM_MOD_SQFT ' +
> ' FROM RESTAURANT ' +
> ' WHERE RESTAURANT_ID = @.Restaurant_Id ' +
> ' SELECT @.Dining_Mod = ISNULL(@.Dining_Mod, 0) '
> exec (@.value)
> The error I get is Invalid column name 'DINING_ROOM_MOD_SQFT'.
> Anybody has any idea?
> Thanks
> Adnan Masood
> www.newbalanceindy.com
>|||Here is my code:
DECLARE @.value VARCHAR(8000)
SELECT @.value =
'DECLARE @.Dining_Mod smallint ' +
' IF (EXISTS(SELECT * FROM dbo.syscolumns WHERE name IN
(''DINING_ROOM_MOD_SQFT'') ' +
' AND id = (SELECT id FROM dbo.sysobjects WHERE id =
object_id(N''[dbo].[RESTAURANT]'') AND OBJECTPROPERTY(id,
N''IsUserTable'') = 1))) ' +
' SELECT @.Dining_Mod = DINING_ROOM_MOD_SQFT ' +
' FROM RESTAURANT ' +
' WHERE RESTAURANT_ID = 1 '
print @.value
EXEC (@.value)
Here is what I get in the print
DECLARE @.Dining_Mod smallint
IF (EXISTS(SELECT * FROM dbo.syscolumns WHERE name IN
('DINING_ROOM_MOD_SQFT')
AND id = (SELECT id FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[RESTAURANT]') AND OBJECTPROPERTY(id, N'IsUserTable')
= 1)))
SELECT @.Dining_Mod = DINING_ROOM_MOD_SQFT
FROM RESTAURANT
WHERE RESTAURANT_ID = 1
...and here is the error:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'DINING_ROOM_MOD_SQFT'.
Because this column has been delete in the first run of the script. I
need to be able to run this as many times as I want.
Thanks
Any help will be appreciated.
Adnan Masood
http://www.newbalanceindy.com|||The problem is that when the parsing of the statement is performed, the prio
r IF statement isn't in
effect yet. So, the error that the column isn't there is returned at the par
sing stage, not
execution. Seems you have to break down this into two batches.
A more serious question would be why you need to do this check. A voilile sc
hema often indicates
problems with the data model.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sehboo" <MasoodAdnan@.gmail.com> wrote in message
news:1145295533.954382.286730@.t31g2000cwb.googlegroups.com...
> Here is my code:
> DECLARE @.value VARCHAR(8000)
> SELECT @.value =
> 'DECLARE @.Dining_Mod smallint ' +
> ' IF (EXISTS(SELECT * FROM dbo.syscolumns WHERE name IN
> (''DINING_ROOM_MOD_SQFT'') ' +
> ' AND id = (SELECT id FROM dbo.sysobjects WHERE id =
> object_id(N''[dbo].[RESTAURANT]'') AND OBJECTPROPERTY(id,
> N''IsUserTable'') = 1))) ' +
> ' SELECT @.Dining_Mod = DINING_ROOM_MOD_SQFT ' +
> ' FROM RESTAURANT ' +
> ' WHERE RESTAURANT_ID = 1 '
>
> print @.value
> EXEC (@.value)
> Here is what I get in the print
> DECLARE @.Dining_Mod smallint
> IF (EXISTS(SELECT * FROM dbo.syscolumns WHERE name IN
> ('DINING_ROOM_MOD_SQFT')
> AND id = (SELECT id FROM dbo.sysobjects WHERE id =
> object_id(N'[dbo].[RESTAURANT]') AND OBJECTPROPERTY(id, N'IsUserTable')
> = 1)))
> SELECT @.Dining_Mod = DINING_ROOM_MOD_SQFT
> FROM RESTAURANT
> WHERE RESTAURANT_ID = 1
> ...and here is the error:
> Server: Msg 207, Level 16, State 3, Line 1
> Invalid column name 'DINING_ROOM_MOD_SQFT'.
>
> Because this column has been delete in the first run of the script. I
> need to be able to run this as many times as I want.
> Thanks
> Any help will be appreciated.
> Adnan Masood
> http://www.newbalanceindy.com
>|||IF you need to drop a column, why not remove it from this script entirely
and handle the column in a different script that will only be run once? You
really shouldnt have production code running on a regular basis for a task
that will only be done once. Run once what needs to be run once, then never
reference the code again.
I can't think of a reason to write code in a reusable procedure for a field
that is not going to exist. If you explain why you are doing this and what
this field is for you will probably get some good advice on alternative
approaches.
"Sehboo" <MasoodAdnan@.gmail.com> wrote in message
news:1145289437.588507.216360@.i39g2000cwa.googlegroups.com...
> I am writing sql script where I have to get colum values from table and
> do some processing and then delete those column. This is a new version
> of the application and we don't need those columns anymore.
> But the problem is that I need to be able to run this script more then
> once without any errors. So, second time when I run it, it gives an
> error that Invalid column name.
> Before I get values from those columns, I check if they exists or not
> and then get the value, but still it gives error, so I put everything
> as string and use EXEC to execute that string - but still.
> I don't know what to do.
> I am copying my code here.
> DECLARE @.value VARCHAR(8000)
> SELECT @.value = 'SELECT @.Dining_Mod = 0 ' +
> ' IF (EXISTS(SELECT * FROM dbo.syscolumns WHERE name IN
> (''DINING_ROOM_MOD_SQFT'') ' +
> ' AND id = (SELECT id FROM dbo.sysobjects WHERE id =
> object_id(N''[dbo].[RESTAURANT]'') AND OBJECTPROPERTY(id,
> N''IsUserTable'') = 1))) ' +
> ' SELECT @.Dining_Mod = DINING_ROOM_MOD_SQFT ' +
> ' FROM RESTAURANT ' +
> ' WHERE RESTAURANT_ID = @.Restaurant_Id ' +
> ' SELECT @.Dining_Mod = ISNULL(@.Dining_Mod, 0) '
> exec (@.value)
> The error I get is Invalid column name 'DINING_ROOM_MOD_SQFT'.
> Anybody has any idea?
> Thanks
> Adnan Masood
> www.newbalanceindy.com
>

Invalid Characters when setting up a subscription

I have a report with some of the parameter values in French. When I
run the report from the view tab, selecting parameters works fine.
When I attempt to create a subscription, I get "There is an error in
XML document (1, 6319)".
When I view the log, I see mention of an invalid character.
Why would it work on the view tab and not in the subscription setup?
Any help is greatly appreciated.
Craigdid you ever figure this out i have the same issue
"Craig" wrote:
> I have a report with some of the parameter values in French. When I
> run the report from the view tab, selecting parameters works fine.
> When I attempt to create a subscription, I get "There is an error in
> XML document (1, 6319)".
> When I view the log, I see mention of an invalid character.
> Why would it work on the view tab and not in the subscription setup?
> Any help is greatly appreciated.
> Craig
>

Interval/Bucket Dimension

I am trying to find the most efficient way of handelling discreet buckets over continuous/discreet set of values.

In english I am looking to have a dimension that says if age is between 0 and 5 they are an infant, 6 to 18 child and > 18 adult. I know I can do this in a case statement but I have similar senarios that require multiple levels of nesting and the ability to easily change the banding criteria with many fact table sharing the same banding information.

I have done this in the past by preprocessing the fact table and updating the key value based on between joins, but this is very inefficient on large datasets (upwards of 30 million rows).

I also need to be able to do this across continuous values such a monetary amounts.

Can anyone help

Philip Coupar

Dear Philip,

Sorry for interrupting in. Actually, I'm facing the same problem as you did. Have you solved this by any mean? Please share with us if you do. Thanks!

Regards,
Alex|||

Take a look at the DiscretizationMethod property of an attribute

http://msdn2.microsoft.com/en-US/library/ms174810.aspx

Try and build a new attribute that discetizes the coninuous set of values.

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

|||Dear Edward,

I've tried looking into the DiscretizationMethod before. However, it only got 3 modes: automatic, EqualAreas and Clusters only. On the other hand, what I am trying to do is using a customizable bucket, said 0~49, 50~99, 100~149 and so on. I can't find any method to control the DiscretizationMethod in this way. Thanks!

Regards,
Alex|||

Your custom buckets are fitting into EqualAreas schenario :)

On the other hand you can create column in the relational database and create custom mapping yourself. Alternatively you can create a named calculation in DSV and use Case statement to map into the values you'd like. http://msdn2.microsoft.com/en-us/library/ms181765.aspx

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

|||Dear Edward,

I don't think the EqualAreas can do what I want. The Areas range are calculated by SSAS and I have no control over it. So, it's impossible to group in my way. It'll make sense to me if it provide an option for me to specify the size of each area.

By the way, I'm currently using the CASE method to handle my problem. The drawbacks are a long SQL as I need to break down from 0 to 1000 with an interval of 50. Moreover, I need to insert some dummy records into the Fact table. Otherwise, I will get a discontinuous list if there are not such value in the Fact table. I don't want my user to see the range jumping from 0~49 to 500~549.

Regards,
Alex|||

I agree it is possible to solve this issue as a case statement. It is also possible to solve this problem by using a table that specifies the start and end point of each bucket and allocate the surrogate key neccessary to have a nice dimension table.

However if we take the example where the fact table contains someone's age at the time of an event, we can see that we may first of all break this down by Adult/Child, then have further age bandings under this. The descritization functionality does not accomodate this as it will not build multiple levels, and you cannot assert the start and end ages for each bucket, required for this type of analysis.

I do not really want to find the surrogate key by doing a between join on a dimension table as this would lead to something quite inefficient over very large fact tables. It also does not make sense to me, in terms of performance, to have the case statement in the DSV as this will require a large amount of processing over a degenerate dimension attribute, a significant issue if you need to reprocess 250 Million fact rows.

The original post was asking for ideas on the most efficient ways of achieving this. Edward's posts may help some people who have different decretization requirements, or are handling smaller datasets. Alex is looking at the same issue I referred to in my original post. It may well be that there are no more effcient ways of doing this at the moment, if anyone else has any ideas of how to improve on this kind of requirement I am sure we are all looking forward to hearing from you.

|||

Check out my articles doing aging buckets this way:

... In SSAS with Named Calculations:

http://www.databasejournal.com/features/mssql/article.php/10894_3590866_7

... And a slightly different approach in MSAS 2000 (same logic applies in SSAS 2005):

http://www.databasejournal.com/features/mssql/article.php/3525516

I've done it other ways for clients with specific needs, too. Let me know if you wish further amplification, etc.

Good Luck!

William E. Pearson III
CPA, CMA, CIA, MCSE, MCDBA
Island Technologies Inc.
931 Monroe Drive
Suite 102-321
Atlanta, GA 30308

404.872.5972 Office

wep3@.islandtechnologies.com
wep3@.msas-architect.com

www.msas-architect.com
-- -- --

Publisher Sites:

http://www.databasejournal.com/article.php/1459531

http://www.sql-server-performance.com/bill_pearson.asp

http://www.informit.com/authors/bio.asp?a=862acd62-4662-49ae-879d-541c8b4d656f

http://www.2000trainers.com/section.aspx?sectionID=17

|||

I like the idea of adding the aging logic to the time dimension, this combines the best of the surrogate key approach with the simplicity of the embeded case statements, and would overcome the performance issues around large fact tables.

However this has a very limited application, as within a single UDM model there may be many different custom range requirements, so we may have both transaction aging and the person's age each of which would be banded very differently. and this approach would mean exposing both aging structures on every use of the time dimension.

Interval/Bucket Dimension

I am trying to find the most efficient way of handelling discreet buckets over continuous/discreet set of values.

In english I am looking to have a dimension that says if age is between 0 and 5 they are an infant, 6 to 18 child and > 18 adult. I know I can do this in a case statement but I have similar senarios that require multiple levels of nesting and the ability to easily change the banding criteria with many fact table sharing the same banding information.

I have done this in the past by preprocessing the fact table and updating the key value based on between joins, but this is very inefficient on large datasets (upwards of 30 million rows).

I also need to be able to do this across continuous values such a monetary amounts.

Can anyone help

Philip Coupar

Dear Philip,

Sorry for interrupting in. Actually, I'm facing the same problem as you did. Have you solved this by any mean? Please share with us if you do. Thanks!

Regards,
Alex|||

Take a look at the DiscretizationMethod property of an attribute

http://msdn2.microsoft.com/en-US/library/ms174810.aspx

Try and build a new attribute that discetizes the coninuous set of values.

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

|||Dear Edward,

I've tried looking into the DiscretizationMethod before. However, it only got 3 modes: automatic, EqualAreas and Clusters only. On the other hand, what I am trying to do is using a customizable bucket, said 0~49, 50~99, 100~149 and so on. I can't find any method to control the DiscretizationMethod in this way. Thanks!

Regards,
Alex|||

Your custom buckets are fitting into EqualAreas schenario :)

On the other hand you can create column in the relational database and create custom mapping yourself. Alternatively you can create a named calculation in DSV and use Case statement to map into the values you'd like. http://msdn2.microsoft.com/en-us/library/ms181765.aspx

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

|||Dear Edward,

I don't think the EqualAreas can do what I want. The Areas range are calculated by SSAS and I have no control over it. So, it's impossible to group in my way. It'll make sense to me if it provide an option for me to specify the size of each area.

By the way, I'm currently using the CASE method to handle my problem. The drawbacks are a long SQL as I need to break down from 0 to 1000 with an interval of 50. Moreover, I need to insert some dummy records into the Fact table. Otherwise, I will get a discontinuous list if there are not such value in the Fact table. I don't want my user to see the range jumping from 0~49 to 500~549.

Regards,
Alex|||

I agree it is possible to solve this issue as a case statement. It is also possible to solve this problem by using a table that specifies the start and end point of each bucket and allocate the surrogate key neccessary to have a nice dimension table.

However if we take the example where the fact table contains someone's age at the time of an event, we can see that we may first of all break this down by Adult/Child, then have further age bandings under this. The descritization functionality does not accomodate this as it will not build multiple levels, and you cannot assert the start and end ages for each bucket, required for this type of analysis.

I do not really want to find the surrogate key by doing a between join on a dimension table as this would lead to something quite inefficient over very large fact tables. It also does not make sense to me, in terms of performance, to have the case statement in the DSV as this will require a large amount of processing over a degenerate dimension attribute, a significant issue if you need to reprocess 250 Million fact rows.

The original post was asking for ideas on the most efficient ways of achieving this. Edward's posts may help some people who have different decretization requirements, or are handling smaller datasets. Alex is looking at the same issue I referred to in my original post. It may well be that there are no more effcient ways of doing this at the moment, if anyone else has any ideas of how to improve on this kind of requirement I am sure we are all looking forward to hearing from you.

|||

Check out my articles doing aging buckets this way:

... In SSAS with Named Calculations:

http://www.databasejournal.com/features/mssql/article.php/10894_3590866_7

... And a slightly different approach in MSAS 2000 (same logic applies in SSAS 2005):

http://www.databasejournal.com/features/mssql/article.php/3525516

I've done it other ways for clients with specific needs, too. Let me know if you wish further amplification, etc.

Good Luck!

William E. Pearson III
CPA, CMA, CIA, MCSE, MCDBA
Island Technologies Inc.
931 Monroe Drive
Suite 102-321
Atlanta, GA 30308

404.872.5972 Office

wep3@.islandtechnologies.com
wep3@.msas-architect.com

www.msas-architect.com
-- -- --

Publisher Sites:

http://www.databasejournal.com/article.php/1459531

http://www.sql-server-performance.com/bill_pearson.asp

http://www.informit.com/authors/bio.asp?a=862acd62-4662-49ae-879d-541c8b4d656f

http://www.2000trainers.com/section.aspx?sectionID=17

|||

I like the idea of adding the aging logic to the time dimension, this combines the best of the surrogate key approach with the simplicity of the embeded case statements, and would overcome the performance issues around large fact tables.

However this has a very limited application, as within a single UDM model there may be many different custom range requirements, so we may have both transaction aging and the person's age each of which would be banded very differently. and this approach would mean exposing both aging structures on every use of the time dimension.

Interrogating SQL Server Tables for Specific Values

Does anyone have a routine that interrogates tables looking for
specific values. I as a basic routine that works using MS Access. I
first create a table that has all the table names and field names per
table, like:
xyzField
Table Field
Table1 Field1
Table1 Field2
Table1 Field3
Table2 Field1
Table2 Field2
Table2 Field3
...
xyzResults has three text columns:
TableName, FieldName, SearchValue
The I run the routine:
Function InterrogateDB()
On Error GoTo Err_Line
Dim db As DAO.Database
Dim rsXYZFields As DAO.Recordset
Dim mTable As String
Dim mField As String
Dim strSQL As String
Dim strFIND As String
strFIND = InputBox("Enter the field name fragment:")
Set db = CurrentDb
'Open the Table/Fields table
Set rsXYZFields = db.OpenRecordset("xyzField", dbOpenSnapshot)
With rsXYZFields
.MoveFirst
Do Until .EOF
mTable = "[" & Trim(.Fields(0)) & "]"
mField = "[" & Trim(.Fields(1)) & "]"
If DCount("*", mTable, mField & " Like '*" & _
strFIND & "*'") > 0 Then
strSQL = "INSERT INTO xyzResults ( TableName, " &
_
"FieldName, SearchValue ) VALUES ( '" & mTable &
"', '" & _
mField & "', '" & strFIND & "' )"
db.Execute strSQL, dbFailOnError
End If
.MoveNext
Loop
End With
rsXYZFields.Close
Set rsXYZFields = Nothing
db.Close
Set db = Nothing
Exit Function
Err_Line:
MsgBox "Error occurred when inserting record"
Resume Next
End Function
==============================
It prompts me for the value that I am looking for, then interrogates
all the tables and fields for that value. Is there a procedure
similar to this I can use in SQL Server?
Any help appreciated!
Thanks,
RBollingerHi
"robboll" wrote:
> Does anyone have a routine that interrogates tables looking for
> specific values. I as a basic routine that works using MS Access. I
> first create a table that has all the table names and field names per
> table, like:
> xyzField
> Table Field
> Table1 Field1
> Table1 Field2
> Table1 Field3
> Table2 Field1
> Table2 Field2
> Table2 Field3
> ...
> xyzResults has three text columns:
> TableName, FieldName, SearchValue
> The I run the routine:
> Function InterrogateDB()
> On Error GoTo Err_Line
>
> Dim db As DAO.Database
> Dim rsXYZFields As DAO.Recordset
> Dim mTable As String
> Dim mField As String
> Dim strSQL As String
> Dim strFIND As String
> strFIND = InputBox("Enter the field name fragment:")
> Set db = CurrentDb
> 'Open the Table/Fields table
> Set rsXYZFields = db.OpenRecordset("xyzField", dbOpenSnapshot)
>
> With rsXYZFields
> .MoveFirst
> Do Until .EOF
> mTable = "[" & Trim(.Fields(0)) & "]"
> mField = "[" & Trim(.Fields(1)) & "]"
> If DCount("*", mTable, mField & " Like '*" & _
> strFIND & "*'") > 0 Then
> strSQL = "INSERT INTO xyzResults ( TableName, " &
> _
> "FieldName, SearchValue ) VALUES ( '" & mTable &
> "', '" & _
> mField & "', '" & strFIND & "' )"
> db.Execute strSQL, dbFailOnError
> End If
> .MoveNext
> Loop
> End With
> rsXYZFields.Close
> Set rsXYZFields = Nothing
> db.Close
> Set db = Nothing
> Exit Function
>
> Err_Line:
> MsgBox "Error occurred when inserting record"
> Resume Next
> End Function
> ==============================> It prompts me for the value that I am looking for, then interrogates
> all the tables and fields for that value. Is there a procedure
> similar to this I can use in SQL Server?
> Any help appreciated!
> Thanks,
> RBollinger
>
Have you checked out
http://www.users.drew.edu/skass/sql/SearchAllTables.sql.txt
John

Interrogating SQL Server Tables for Specific Values

Does anyone have a routine that interrogates tables looking for
specific values. I as a basic routine that works using MS Access. I
first create a table that has all the table names and field names per
table, like:
xyzField
Table Field
Table1 Field1
Table1 Field2
Table1 Field3
Table2 Field1
Table2 Field2
Table2 Field3
...
xyzResults has three text columns:
TableName, FieldName, SearchValue
The I run the routine:
Function InterrogateDB()
On Error GoTo Err_Line
Dim db As DAO.Database
Dim rsXYZFields As DAO.Recordset
Dim mTable As String
Dim mField As String
Dim strSQL As String
Dim strFIND As String
strFIND = InputBox("Enter the field name fragment:")
Set db = CurrentDb
'Open the Table/Fields table
Set rsXYZFields = db.OpenRecordset("xyzField", dbOpenSnapshot)
With rsXYZFields
.MoveFirst
Do Until .EOF
mTable = "[" & Trim(.Fields(0)) & "]"
mField = "[" & Trim(.Fields(1)) & "]"
If DCount("*", mTable, mField & " Like '*" & _
strFIND & "*'") > 0 Then
strSQL = "INSERT INTO xyzResults ( TableName, " &
_
"FieldName, SearchValue ) VALUES ( '" & mTable &
"', '" & _
mField & "', '" & strFIND & "' )"
db.Execute strSQL, dbFailOnError
End If
.MoveNext
Loop
End With
rsXYZFields.Close
Set rsXYZFields = Nothing
db.Close
Set db = Nothing
Exit Function
Err_Line:
MsgBox "Error occurred when inserting record"
Resume Next
End Function
==============================
It prompts me for the value that I am looking for, then interrogates
all the tables and fields for that value. Is there a procedure
similar to this I can use in SQL Server?
Any help appreciated!
Thanks,
RBollingerHi
"robboll" wrote:

> Does anyone have a routine that interrogates tables looking for
> specific values. I as a basic routine that works using MS Access. I
> first create a table that has all the table names and field names per
> table, like:
> xyzField
> Table Field
> Table1 Field1
> Table1 Field2
> Table1 Field3
> Table2 Field1
> Table2 Field2
> Table2 Field3
> ...
> xyzResults has three text columns:
> TableName, FieldName, SearchValue
> The I run the routine:
> Function InterrogateDB()
> On Error GoTo Err_Line
>
> Dim db As DAO.Database
> Dim rsXYZFields As DAO.Recordset
> Dim mTable As String
> Dim mField As String
> Dim strSQL As String
> Dim strFIND As String
> strFIND = InputBox("Enter the field name fragment:")
> Set db = CurrentDb
> 'Open the Table/Fields table
> Set rsXYZFields = db.OpenRecordset("xyzField", dbOpenSnapshot)
>
> With rsXYZFields
> .MoveFirst
> Do Until .EOF
> mTable = "[" & Trim(.Fields(0)) & "]"
> mField = "[" & Trim(.Fields(1)) & "]"
> If DCount("*", mTable, mField & " Like '*" & _
> strFIND & "*'") > 0 Then
> strSQL = "INSERT INTO xyzResults ( TableName, " &
> _
> "FieldName, SearchValue ) VALUES ( '" & mTable &
> "', '" & _
> mField & "', '" & strFIND & "' )"
> db.Execute strSQL, dbFailOnError
> End If
> .MoveNext
> Loop
> End With
> rsXYZFields.Close
> Set rsXYZFields = Nothing
> db.Close
> Set db = Nothing
> Exit Function
>
> Err_Line:
> MsgBox "Error occurred when inserting record"
> Resume Next
> End Function
> ==============================
> It prompts me for the value that I am looking for, then interrogates
> all the tables and fields for that value. Is there a procedure
> similar to this I can use in SQL Server?
> Any help appreciated!
> Thanks,
> RBollinger
>
Have you checked out
http://www.users.drew.edu/skass/sql...lTables.sql.txt
John

Interrogating SQL Server Tables for Specific Values

Does anyone have a routine that interrogates tables looking for
specific values. I as a basic routine that works using MS Access. I
first create a table that has all the table names and field names per
table, like:
xyzField
Table Field
Table1 Field1
Table1 Field2
Table1 Field3
Table2 Field1
Table2 Field2
Table2 Field3
...
xyzResults has three text columns:
TableName, FieldName, SearchValue
The I run the routine:
Function InterrogateDB()
On Error GoTo Err_Line
Dim db As DAO.Database
Dim rsXYZFields As DAO.Recordset
Dim mTable As String
Dim mField As String
Dim strSQL As String
Dim strFIND As String
strFIND = InputBox("Enter the field name fragment:")
Set db = CurrentDb
'Open the Table/Fields table
Set rsXYZFields = db.OpenRecordset("xyzField", dbOpenSnapshot)
With rsXYZFields
.MoveFirst
Do Until .EOF
mTable = "[" & Trim(.Fields(0)) & "]"
mField = "[" & Trim(.Fields(1)) & "]"
If DCount("*", mTable, mField & " Like '*" & _
strFIND & "*'") > 0 Then
strSQL = "INSERT INTO xyzResults ( TableName, " &
_
"FieldName, SearchValue ) VALUES ( '" & mTable &
"', '" & _
mField & "', '" & strFIND & "' )"
db.Execute strSQL, dbFailOnError
End If
.MoveNext
Loop
End With
rsXYZFields.Close
Set rsXYZFields = Nothing
db.Close
Set db = Nothing
Exit Function
Err_Line:
MsgBox "Error occurred when inserting record"
Resume Next
End Function
==============================
It prompts me for the value that I am looking for, then interrogates
all the tables and fields for that value. Is there a procedure
similar to this I can use in SQL Server?
Any help appreciated!
Thanks,
RBollinger
Hi
"robboll" wrote:

> Does anyone have a routine that interrogates tables looking for
> specific values. I as a basic routine that works using MS Access. I
> first create a table that has all the table names and field names per
> table, like:
> xyzField
> Table Field
> Table1 Field1
> Table1 Field2
> Table1 Field3
> Table2 Field1
> Table2 Field2
> Table2 Field3
> ...
> xyzResults has three text columns:
> TableName, FieldName, SearchValue
> The I run the routine:
> Function InterrogateDB()
> On Error GoTo Err_Line
>
> Dim db As DAO.Database
> Dim rsXYZFields As DAO.Recordset
> Dim mTable As String
> Dim mField As String
> Dim strSQL As String
> Dim strFIND As String
> strFIND = InputBox("Enter the field name fragment:")
> Set db = CurrentDb
> 'Open the Table/Fields table
> Set rsXYZFields = db.OpenRecordset("xyzField", dbOpenSnapshot)
>
> With rsXYZFields
> .MoveFirst
> Do Until .EOF
> mTable = "[" & Trim(.Fields(0)) & "]"
> mField = "[" & Trim(.Fields(1)) & "]"
> If DCount("*", mTable, mField & " Like '*" & _
> strFIND & "*'") > 0 Then
> strSQL = "INSERT INTO xyzResults ( TableName, " &
> _
> "FieldName, SearchValue ) VALUES ( '" & mTable &
> "', '" & _
> mField & "', '" & strFIND & "' )"
> db.Execute strSQL, dbFailOnError
> End If
> .MoveNext
> Loop
> End With
> rsXYZFields.Close
> Set rsXYZFields = Nothing
> db.Close
> Set db = Nothing
> Exit Function
>
> Err_Line:
> MsgBox "Error occurred when inserting record"
> Resume Next
> End Function
> ==============================
> It prompts me for the value that I am looking for, then interrogates
> all the tables and fields for that value. Is there a procedure
> similar to this I can use in SQL Server?
> Any help appreciated!
> Thanks,
> RBollinger
>
Have you checked out
http://www.users.drew.edu/skass/sql/SearchAllTables.sql.txt
John

interrelated report parameters

Is it possible to have parameter available values (coming from different
queries) BUT related to each other?
for example, we have two report parameters, ProductArea and ProductType,
with their available values coming from the queries:
select distinct ProductArea from SalesTable
select distinct ProductType from SalesTable
but not all product types are sold to all areas, so we need to use ONLY the
valid combinations of area and type.
Therefore, when a user selects an area from the drop-down box of available
areas, we need the second parameter to present only the types of products
actually sold in the selected area as available values, so the second query
should change to something like:
select distinct ProductType from SalesTable where ProductArea = <Selected
Value>
Is it possible to reference the selected value of a parameter at runtime in
the query?Yes, this is called Cascading Parameters in Reporting Services. All you need
to do is to set up a query parameter in your main query with the area
identifier. For example:
select Area, ProductType, Product, ListPrice FROM MyTable WHERE Area = @.area
AND ProductType = @.producttype
Your picklist for the area parameter will come from the query you have
provided below:
select distinct ProductArea from SalesTable
Your second parameter for ProductType will have a picklist defined by the
following query:
select distinct ProductType from SalesTable where Area = @.area
Because this query uses a parameter which will not be available until a
selection for the first parameter is made, Reporting Services will have the
listbox greyed out until a selection of Area has been made. And when the
selection for Area has been made, the picklist for the ProductType will be
populated with values which are only relevant to that area.
HTH
Charles Kangai, MCT, MCDBA
"vsiat" wrote:
> Is it possible to have parameter available values (coming from different
> queries) BUT related to each other?
> for example, we have two report parameters, ProductArea and ProductType,
> with their available values coming from the queries:
> select distinct ProductArea from SalesTable
> select distinct ProductType from SalesTable
> but not all product types are sold to all areas, so we need to use ONLY the
> valid combinations of area and type.
> Therefore, when a user selects an area from the drop-down box of available
> areas, we need the second parameter to present only the types of products
> actually sold in the selected area as available values, so the second query
> should change to something like:
> select distinct ProductType from SalesTable where ProductArea = <Selected
> Value>
> Is it possible to reference the selected value of a parameter at runtime in
> the query?
>

Interpolating a line for missing data points in line / bar graph

I know I'm not the only one with this problem. I have a set of data values I want to display as lines from left to right, grouped by month, and a series is specified. There is no aggregation as the series/month combination is unique. However, sometimes there is no data for a given series and month. When this happens, my line breaks at the previous month and starts again on the next month. I tried using a column chart and specifying plot data as line, but I still get the same result. Is there a way to get SQL Server Reporting Services 2000 to connect the dots with the existing data or do I have to alter my stored procedure and create an interpolation scheme for missing months?

If not, will this be addressed in 2005?

Thanks in advance

Please make sure you have RS 2000 SP2 installed (on the report server and the report designer machines).

On SQL Server 2005 it will work just fine.

-- Robert

|||

I have the opposite problem. I'm using SQL Server 2005. I have data to graph by month, but when I have no data for a given month, I'd like like to see the gap in the line. If I leave out the row, the graph just leaves the month out. If I make the data value null, it puts the month in, leaves the marker off, but still draws a line from the previous to the next month. Is there a trick to make the line break?

|||I have the same problem - I am plotting a financial instrument and want to overlay the plot with buy/sell activity. Thus I buy at, say 09:00 and sell at 09:30 before buying again at 10:00. The Chart plots a line between the end of the buy line at 09:30 and the next buy at 10:00. This is despite the result set having a a null value between these two times.
Has anyone an answer to this problem?

Interpolating a line for missing data points in line / bar graph

I know I'm not the only one with this problem. I have a set of data values I want to display as lines from left to right, grouped by month, and a series is specified. There is no aggregation as the series/month combination is unique. However, sometimes there is no data for a given series and month. When this happens, my line breaks at the previous month and starts again on the next month. I tried using a column chart and specifying plot data as line, but I still get the same result. Is there a way to get SQL Server Reporting Services 2000 to connect the dots with the existing data or do I have to alter my stored procedure and create an interpolation scheme for missing months?

If not, will this be addressed in 2005?

Thanks in advance

Please make sure you have RS 2000 SP2 installed (on the report server and the report designer machines).

On SQL Server 2005 it will work just fine.

-- Robert

|||

I have the opposite problem. I'm using SQL Server 2005. I have data to graph by month, but when I have no data for a given month, I'd like like to see the gap in the line. If I leave out the row, the graph just leaves the month out. If I make the data value null, it puts the month in, leaves the marker off, but still draws a line from the previous to the next month. Is there a trick to make the line break?