Forcing Calculated Measure to be calculated at processing

When is a calculated measure calculated? My understanding is that it depends on the aggregation level and can be pre-calculated if the aggregation level is high enough in the aggregation design.

I have a calculated measure that is core to all my processing and is the main reason for building a particular cube, Is there anyway I can force a calculated measure to always be calculated as part of the processing?

Only real measures and measures expressions are precomputed during processing. Calculated measures cannot be forced to be computed during processing.

|||

So how can I get a pre-calculated measure without having to do all the work in SQL as part of the DSV?

Can I create a real measure which is calculated via a MeasureExpression or something?

|||

You cannot.

P.S. Measure expressions only allow multiplication or division of two real measures, and it is just a way to do a join between measure groups.

Forcing a branch with decision trees

In a decision tree algorithm, is there a known way to force a branch at a top level? For exmaple, I have 30 known decision patterns that are going to be completely different and I don't want them to intermingle. I wanted to force a branch at the top node on one of the 30 patterns so I wouldn't have to create 30 mining models per client.

Brian

Interactive training of models (including partial tree definition) is not supported in SQL Server 2005 Data Mining.

In many cases, instead of 30 models per client it is possible to create a single model containing 30 trees, by using a nested table (which, effectively, has the same effect as forcing a branch at the top). This works easily, for instance, if your target variable has 30 states.

Could you please provide a few details on your known decision patterns?

|||

Bogdan, thanks for the answer!

The project essentially has two nested tables of terms (from SSIS' term extraction) that a specific item uses. Each item is in the parent case table 30 times (once per group,state or view based on your vocabulary you'd like to choose). The state (or group) is an input into the model and it does seem to work great for some of the states but not others and that's why I was trying to force it to break at the state level.

Essentially, we're trying to determine that if a record mentions term X, Y and Z that it's a great item to client. The problem is that it's a great item to show one group of clients but not others (based on their state).

Brian

|||I was able to get the model to branch by changing the scoring mechanism and by making the value that I was trying to force the break on discrete. Thanks again for your help. Is there any plans in future releases to enable users to force a break?sql

Forcing a branch with decision trees

In a decision tree algorithm, is there a known way to force a branch at a top level? For exmaple, I have 30 known decision patterns that are going to be completely different and I don't want them to intermingle. I wanted to force a branch at the top node on one of the 30 patterns so I wouldn't have to create 30 mining models per client.

Brian

Interactive training of models (including partial tree definition) is not supported in SQL Server 2005 Data Mining.

In many cases, instead of 30 models per client it is possible to create a single model containing 30 trees, by using a nested table (which, effectively, has the same effect as forcing a branch at the top). This works easily, for instance, if your target variable has 30 states.

Could you please provide a few details on your known decision patterns?

|||

Bogdan, thanks for the answer!

The project essentially has two nested tables of terms (from SSIS' term extraction) that a specific item uses. Each item is in the parent case table 30 times (once per group,state or view based on your vocabulary you'd like to choose). The state (or group) is an input into the model and it does seem to work great for some of the states but not others and that's why I was trying to force it to break at the state level.

Essentially, we're trying to determine that if a record mentions term X, Y and Z that it's a great item to client. The problem is that it's a great item to show one group of clients but not others (based on their state).

Brian

|||I was able to get the model to branch by changing the scoring mechanism and by making the value that I was trying to force the break on discrete. Thanks again for your help. Is there any plans in future releases to enable users to force a break?

Force Row Level locking in SQLServer 2000 ?

Hi

Is it possible to force row level locking in one or more tables in
some database. We have some problems when SQL Server decides to choose
page- or table-level locking.
We are using SQL Server 2000.

Best regards

AarnoArska (aarno.autio@.bof.fi) writes:
> Is it possible to force row level locking in one or more tables in
> some database. We have some problems when SQL Server decides to choose
> page- or table-level locking.
> We are using SQL Server 2000.

You can add a locking hint

SELECT * FROM tbl (ROWLOCK) WHERE col = 32

However, SQL Server may disregard that hint if row locks are possible
to achieve.

You may need to review you indexing strategy. For instance, in the example
above, I would not expect the hint to help if there is no index on col.
SQL Server will have to scan the entire table, so a tablock is called for.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Force Modification of a User Defined Function

Is there a way to force the modification of a User Defined Function upon which depend other objects.

This is the message:

Msg 3729, Level 16, State 3, Procedure FunctionName, Line 22

Cannot ALTER 'FunctionName' because it is being referenced by object 'OtherObject'.

I have 48 objects related to the function and each time I need modify the function is a big pain.

Any help will be much appreciated.

Best Regards

ggpnetwork

Sorry, but AFAIK there is no switch to do so. If you have a computede column are something else bound to that function you have to drop and recreate the referencing object / Column or something else.

HTH; Jens Suessmeyer.|||We have currently ran into this issue but found the following solution helpful.

Originally, we were adding a scalar function to a computed column in a table. We received the same error message whenever we attempted to modify the scalar function.

However, if we created a view of the same table and included the scalar function as a column, we were able to modify the scalar without problems. That is to say, we were able to modify the scalar function definition (not the view result) without problems.

Hope this helps,
Daniel

Force Modification of a User Defined Function

Is there a way to force the modification of a User Defined Function upon which depend other objects.

This is the message:

Msg 3729, Level 16, State 3, Procedure FunctionName, Line 22

Cannot ALTER 'FunctionName' because it is being referenced by object 'OtherObject'.

I have 48 objects related to the function and each time I need modify the function is a big pain.

Any help will be much appreciated.

Best Regards

ggpnetwork

Sorry, but AFAIK there is no switch to do so. If you have a computede column are something else bound to that function you have to drop and recreate the referencing object / Column or something else.

HTH; Jens Suessmeyer.|||We have currently ran into this issue but found the following solution helpful.

Originally, we were adding a scalar function to a computed column in a table. We received the same error message whenever we attempted to modify the scalar function.

However, if we created a view of the same table and included the scalar function as a column, we were able to modify the scalar without problems. That is to say, we were able to modify the scalar function definition (not the view result) without problems.

Hope this helps,
Daniel

for xml explicit question

I've written some for xml explicit sql, now it works fine but what i want to
do now is add another set of elements at level 2.
For example:
<root>
<thingy number="1"/>
<thingy number="2"/>
<thingy number="3"/>
</root>
is what works fine, but now what i want is to have:
<root>
<thingy number="1"/>
<thingy number="2"/>
<thingy number="3"/>
<blah number="1"/>
<blah number="2"/>
</root>
when i write the for xml explicit to do this, it says that the element for
level 2 is already defined. or something like that. the way i see it is
that "thingy" and "blah" are both at level 2 and so should both have a tag
of 2 and a parent of 1. i could make the tag of "blah" equal to 3 but
wouldnt that make it appear under "thingy"... as (according to my book) tag
is effectively the nesting level?
i can write out full example code for you, but just for now, is there
anything obvious that i'm missing? its amazing how little resources are out
there, that talk about unusual xml explicit code!
thanks
Paul
The number you assign in the query isn't a level - it's a tag identifier. So
tags, 1 and 2 can be at the same level, they're just different tags. The
thing that determines the level is the parent, and you can assign the same
parent to as many tags as you like.
here's an example from the Northwind database
SELECT 1 As TAG, NULL As Parent,
ProductID AS [thingy!1!Number],
NULL AS [blah!2!Number]
FROM Products
UNION
SELECT 2 AS TAG, NULL AS Parent,
NULL,
CategoryID
FROM Categories
FOR XML EXPLICIT
As you'll see from the results, both thingy and blah are at the same level.
Hope that helps,
Graeme
Graeme Malcolm
Principal Technologist
Content Master Ltd.
http://www.microsoft.com/mspress/books/6137.asp
"Paul" <removethisbitthenitspaulyates@.hotmail.com> wrote in message
news:c66778$8kic9$1@.ID-141222.news.uni-berlin.de...
> I've written some for xml explicit sql, now it works fine but what i want
to
> do now is add another set of elements at level 2.
> For example:
> <root>
> <thingy number="1"/>
> <thingy number="2"/>
> <thingy number="3"/>
> </root>
> is what works fine, but now what i want is to have:
> <root>
> <thingy number="1"/>
> <thingy number="2"/>
> <thingy number="3"/>
> <blah number="1"/>
> <blah number="2"/>
> </root>
> when i write the for xml explicit to do this, it says that the element for
> level 2 is already defined. or something like that. the way i see it is
> that "thingy" and "blah" are both at level 2 and so should both have a
tag
> of 2 and a parent of 1. i could make the tag of "blah" equal to 3 but
> wouldnt that make it appear under "thingy"... as (according to my book)
tag
> is effectively the nesting level?
> i can write out full example code for you, but just for now, is there
> anything obvious that i'm missing? its amazing how little resources are
out
> there, that talk about unusual xml explicit code!
> thanks
> Paul
>
|||Thanks! I adjusted my XML accordingly and it works, beautifully.
Its amazing how simple this all is, when you get your head round it (famous
last words until my next problem, hehe). btw I found removing the for xml
explicit part and looking at the virtual (?) table is a big help on the way
to enlightenment
Thanks again
Paul
Graeme Malcolm (Content Master Ltd.) wrote:[vbcol=seagreen]
> The number you assign in the query isn't a level - it's a tag
> identifier. So tags, 1 and 2 can be at the same level, they're just
> different tags. The thing that determines the level is the parent,
> and you can assign the same parent to as many tags as you like.
> here's an example from the Northwind database
> SELECT 1 As TAG, NULL As Parent,
> ProductID AS [thingy!1!Number],
> NULL AS [blah!2!Number]
> FROM Products
> UNION
> SELECT 2 AS TAG, NULL AS Parent,
> NULL,
> CategoryID
> FROM Categories
> FOR XML EXPLICIT
> As you'll see from the results, both thingy and blah are at the same
> level.
> Hope that helps,
> Graeme
>
> "Paul" <removethisbitthenitspaulyates@.hotmail.com> wrote in message
> news:c66778$8kic9$1@.ID-141222.news.uni-berlin.de...

FOR XML explicit

I have a need to output data from a simple database table to a XML file. The
FOR XML EXPLICITmode creates a 3 level xml output and seemed to work fine
with a small amount of data. At approx 256 characters; the output is cutoff.
What is causing this and can it be corrected? I need to build an XML file
much larger. Should I be doing something different? I would like to have a
SQL solution as I want to set this process up as part of a SQL Job.
Any help would be appreciated..."NewtoSQLXML" <NewtoSQLXML@.discussions.microsoft.com> wrote in message
news:4C1E7788-396B-4F4E-9759-731ABA472F1F@.microsoft.com...
> I have a need to output data from a simple database table to a XML file.
The
> FOR XML EXPLICITmode creates a 3 level xml output and seemed to work fine
> with a small amount of data. At approx 256 characters; the output is
cutoff.
> What is causing this and can it be corrected? I need to build an XML file
> much larger. Should I be doing something different? I would like to have
a
> SQL solution as I want to set this process up as part of a SQL Job.
> Any help would be appreciated...
In Query Analyzer, go to Tools->Options
Click the Results tab and set your Maximum characters per column to 8000.
Rick Sawtell|||The problem is that you are just viewing the output in SQL Server Query
Analyzer and it doesn't show the results of FOR XML queries too well. You
can increase the SQL Server Query Analyzer column width as Rick pointed out,
but that will only help if the total amount of XML is less than 8000
characters, what you really need to do is write some code (in VB, or .NET,
or even just some script) to run the query and put the output into a file.
There is an example of some script that you can run in DTS here
http://www.sqlxml.org/faqs.aspx?faq=10
Sean
"NewtoSQLXML" <NewtoSQLXML@.discussions.microsoft.com> wrote in message
news:4C1E7788-396B-4F4E-9759-731ABA472F1F@.microsoft.com...
>I have a need to output data from a simple database table to a XML file.
>The
> FOR XML EXPLICITmode creates a 3 level xml output and seemed to work fine
> with a small amount of data. At approx 256 characters; the output is
> cutoff.
> What is causing this and can it be corrected? I need to build an XML file
> much larger. Should I be doing something different? I would like to have
> a
> SQL solution as I want to set this process up as part of a SQL Job.
> Any help would be appreciated...

FOR XML explicit

I have a need to output data from a simple database table to a XML file. The
FOR XML EXPLICITmode creates a 3 level xml output and seemed to work fine
with a small amount of data. At approx 256 characters; the output is cutoff.
What is causing this and can it be corrected? I need to build an XML file
much larger. Should I be doing something different? I would like to have a
SQL solution as I want to set this process up as part of a SQL Job.
Any help would be appreciated...
"NewtoSQLXML" <NewtoSQLXML@.discussions.microsoft.com> wrote in message
news:4C1E7788-396B-4F4E-9759-731ABA472F1F@.microsoft.com...
> I have a need to output data from a simple database table to a XML file.
The
> FOR XML EXPLICITmode creates a 3 level xml output and seemed to work fine
> with a small amount of data. At approx 256 characters; the output is
cutoff.
> What is causing this and can it be corrected? I need to build an XML file
> much larger. Should I be doing something different? I would like to have
a
> SQL solution as I want to set this process up as part of a SQL Job.
> Any help would be appreciated...
In Query Analyzer, go to Tools->Options
Click the Results tab and set your Maximum characters per column to 8000.
Rick Sawtell
|||The problem is that you are just viewing the output in SQL Server Query
Analyzer and it doesn't show the results of FOR XML queries too well. You
can increase the SQL Server Query Analyzer column width as Rick pointed out,
but that will only help if the total amount of XML is less than 8000
characters, what you really need to do is write some code (in VB, or .NET,
or even just some script) to run the query and put the output into a file.
There is an example of some script that you can run in DTS here
http://www.sqlxml.org/faqs.aspx?faq=10
Sean
"NewtoSQLXML" <NewtoSQLXML@.discussions.microsoft.com> wrote in message
news:4C1E7788-396B-4F4E-9759-731ABA472F1F@.microsoft.com...
>I have a need to output data from a simple database table to a XML file.
>The
> FOR XML EXPLICITmode creates a 3 level xml output and seemed to work fine
> with a small amount of data. At approx 256 characters; the output is
> cutoff.
> What is causing this and can it be corrected? I need to build an XML file
> much larger. Should I be doing something different? I would like to have
> a
> SQL solution as I want to set this process up as part of a SQL Job.
> Any help would be appreciated...

FOR XML explicit

I have a need to output data from a simple database table to a XML file. The
FOR XML EXPLICITmode creates a 3 level xml output and seemed to work fine
with a small amount of data. At approx 256 characters; the output is cutoff.
What is causing this and can it be corrected? I need to build an XML file
much larger. Should I be doing something different? I would like to have a
SQL solution as I want to set this process up as part of a SQL Job.
Any help would be appreciated..."NewtoSQLXML" <NewtoSQLXML@.discussions.microsoft.com> wrote in message
news:4C1E7788-396B-4F4E-9759-731ABA472F1F@.microsoft.com...
> I have a need to output data from a simple database table to a XML file.
The
> FOR XML EXPLICITmode creates a 3 level xml output and seemed to work fine
> with a small amount of data. At approx 256 characters; the output is
cutoff.
> What is causing this and can it be corrected? I need to build an XML file
> much larger. Should I be doing something different? I would like to have
a
> SQL solution as I want to set this process up as part of a SQL Job.
> Any help would be appreciated...
In Query Analyzer, go to Tools->Options
Click the Results tab and set your Maximum characters per column to 8000.
Rick Sawtell|||The problem is that you are just viewing the output in SQL Server Query
Analyzer and it doesn't show the results of FOR XML queries too well. You
can increase the SQL Server Query Analyzer column width as Rick pointed out,
but that will only help if the total amount of XML is less than 8000
characters, what you really need to do is write some code (in VB, or .NET,
or even just some script) to run the query and put the output into a file.
There is an example of some script that you can run in DTS here
http://www.sqlxml.org/faqs.aspx?faq=10
Sean
"NewtoSQLXML" <NewtoSQLXML@.discussions.microsoft.com> wrote in message
news:4C1E7788-396B-4F4E-9759-731ABA472F1F@.microsoft.com...
>I have a need to output data from a simple database table to a XML file.
>The
> FOR XML EXPLICITmode creates a 3 level xml output and seemed to work fine
> with a small amount of data. At approx 256 characters; the output is
> cutoff.
> What is causing this and can it be corrected? I need to build an XML file
> much larger. Should I be doing something different? I would like to have
> a
> SQL solution as I want to set this process up as part of a SQL Job.
> Any help would be appreciated...