PredictSupport

Hi,

Can anyone explain what this function returns? What does the support value represent?

Thanks,

Dave

PredictSupport(<attribute>, <state>) returns the number of cases in the training set that support the predicted state for this attribute. If the state is not specified, the state with the highest predict probability is used. The general idea is that a high probability prediction with a larger support value may be more reliable.|||

Ah right, that makes sense.

Thanks!

sql

Prediction Join to MDX with nested table

If your prediction join is to a SQL datasource, you can easily write a SQL query which returns a nested table like:

SELECT
Predict([Subcategories],2) as [Subcategories]
FROM
[SubcategoryAssociations]
NATURAL PREDICTION JOIN
(SELECT
(SELECT 'Road Bikes' AS Subcategory
UNION SELECT 'Jerseys' AS Subcategory
) AS Subcategories
) AS t

What about if your datasource is a cube? Is there some special MDX syntax similar to the SQL syntax above? Or do you have to utilize the SHAPE/APPEND syntax as follows?

SELECT t.*, $Cluster as ClusterName
FROM [MyModel]
PREDICTION JOIN
SHAPE {
select [Measures].[My Measure] on 0,
[My Dimension].[My Attribute].[My Attribute].Members on 1
from MyCube
}
APPEND (
{
select [Measures].[Another Measure] on 0,
NON EMPTY [My Dimension].[My Attribute].[My Attribute].Members
*[Product].[Product].[Product].Members on 1
from MyCube
}
RELATE [[My Dimension]].[My Attribute]].[My Attribute]].[MEMBER_CAPTION]]]
TO [[My Dimension]].[My Attribute]].[My Attribute]].[MEMBER_CAPTION]]]
)
AS [My Nested Table] AS t
ON [MyModel].[Product].[Product] = t.[My Nested Table].[[Product]].[Product]].[Product]].[MEMBER_CAPTION]]]

Typically, for building models on top of cubes, it is much easier to use the tools (BI Dev Studio). This way you can define your model directly on top of the cube and lots of optimizations occur. With such models, you can even use the MDXPredict function to get prediction results inside MDX queries over the source cube.

The DMX SELECT statement supports as input rowset-returning Analysis Services statements (MDX or DMX). That means that dataset-returning statements are not supported. But many MDX queries can be flattened. Have you tried something like SELECT FLATTENED in the MDX query?

|||

Bogdan-

Thanks for the reply. Yes, BIDS worked great for building the model. I've got it trained. Now I want to do a prediction based upon data from a cube. From what I can tell, you can't do prediction queries off a cube using BIDS because it only lets you predict off a relational table source. Right?

I've been researching the MDX function "Predict" which you mentioned. But I'm having terrible trouble finding example queries using that function...

Here's what I'm looking for... we've built a clustering model to cluster our stores. Some of the attributes are just Store dimension attributes... some are from a nested table (stats about the sales volume from each product category). We trained the model with all the stores. Now we want to extract the cluster name for each store and save that to a table. So is there a straight MDX query using the Predict MDX function which will get me the cluster name for every store? I was having trouble seeing how the Predict MDX function was able to know how to do a prediction join to the Store dimension.

As a side note, we could almost do a natural prediction join back to (select * from Model.CASES) except that we don't want the Store Key to influence the clustering model so we didn't add that as an input to the model. (And marking Store Key as Ignore excludes it from the Model.CASES resultset.)

By the way, we're only talking about a couple hundred rows, so the performance of the SHAPE/APPEND syntax below is fine for my purposes... just seeing if there's a more elegant way to do it.

Thanks!

|||

Oh... and to answer your other question about trying "SELECT FLATTENED"...

It's my understanding that "SELECT FLATTENED" is DMX. I'm not sure how to write an MDX statement that starts with "SELECT FLATTENED". And I'm struggling to see how using the DMX "SELECT FLATTENED" would help me. The output of DMX prediction query I used in the examples at the beginning of the thread work fine. I suppose I could flatten the output, but that wouldn't help me much. It's the input to the prediction join that I'm concerned with.

Or did you mean that you can use an MDX query which is written to be flat and use that as input to a prediction query which expects nested tables? I just tried that but may not have been using the right syntax cause I couldn't get it to work. Suggestions?

|||

You kind of need to do it brute force -we use the flattening semantics of MDX when executing the query, so you have to reshape using SHAPE.

There is a little trick to help you out in building the queries. You can use DMX to examine the flattened structure of the MDX query. Just issue a query like this:

SELECT t.* FROM AnyModel NATURAL PREDICTION JOIN <My MDX Query> AS t

then you will be able to see how the DMX processor sees your MDX results.

|||

Jamie-

That trick is helpful for seeing how it refers to the results of an MDX query.

But how do I take a flat MDX query and shape it so it can be consumed by a prediction join which expects a nested table. See the MDX example at the top of this post. Is that the only way (tying two separate MDX queries together with SHAPE/APPEND)?

|||

Yes your original SHAPE/APPEND would be the way to go.

The implementation of SHAPE in the AS engine will cause the MDX query results to be automatically returned in a flattened manner without requiring any explicit flattening syntax in the query itself (in fact, there is no such syntax - flattening is requested as either a command property in XMLA or by requesting a rowset interface in OLE DB)..

Precedence of MAX and WHERE

Hi
I'd like to create a query which returns the MAX of a group of dates so long
as the number is less than a given date. For example :
SELECT MAX(date), username
FROM mydatatable
WHERE date < '01/01/2005'
GROUP BY username
Will this do what I expect and return the username and date which is the
most recent before 01/01/2005 ?
Thanks
AndrewHi,
Your query looks good.
Thanks
Hari
SQL Server MVP
"Andrew Webb" <andrew.webb@.eme-med.co.uk> wrote in message
news:OTlNYqfuFHA.1572@.TK2MSFTNGP10.phx.gbl...
> Hi
> I'd like to create a query which returns the MAX of a group of dates so
> long as the number is less than a given date. For example :
>
> SELECT MAX(date), username
> FROM mydatatable
> WHERE date < '01/01/2005'
> GROUP BY username
>
> Will this do what I expect and return the username and date which is the
> most recent before 01/01/2005 ?
> Thanks
> Andrew
>|||You could compare these queries and see which one yields the results you
want. Word problems are tough to solve, usually better to provide specs as
described in http://www.aspfaq.com/5006 . Also, "date" is a really bad name
for a column. Not only is it a reserved word, it is also very tough to
decipher it... date of WHAT? Finally, do not use m/d/y or d/m/y date
formats when hard-coding date strings. The safest approach here is to use
YYYYMMDD format, then this can't be by software or humans.
CREATE TABLE dbo.myDataTable
(
username VARCHAR(32),
eventDate SMALLDATETIME
)
GO
SET NOCOUNT ON
INSERT myDataTable SELECT 'bob','20040101'
INSERT myDataTable SELECT 'bob','20050201'
INSERT myDataTable SELECT 'frank','20040101'
INSERT myDataTable SELECT 'frank','20040725'
GO
SELECT username, MAX(eventDate)
FROM dbo.myDataTable
WHERE eventDate < '20050101'
GROUP BY username
SELECT username, MAX(eventDate)
FROM dbo.myDataTable
GROUP BY username
HAVING MAX(eventDate) < '20050101'
GO
DROP TABLE dbo.myDataTable
GO
"Andrew Webb" <andrew.webb@.eme-med.co.uk> wrote in message
news:OTlNYqfuFHA.1572@.TK2MSFTNGP10.phx.gbl...
> Hi
> I'd like to create a query which returns the MAX of a group of dates so
> long as the number is less than a given date. For example :
>
> SELECT MAX(date), username
> FROM mydatatable
> WHERE date < '01/01/2005'
> GROUP BY username
>
> Will this do what I expect and return the username and date which is the
> most recent before 01/01/2005 ?
> Thanks
> Andrew
>|||Andrew,

> Will this do what I expect and return the username and date which is the
> most recent before 01/01/2005 ?
It is correct, but it could be more than one. It will select each username
and the max date for those username with date values less than '20050101'. I
f
a username does not have date values in this range then it will not appear i
n
the result.
AMB
"Andrew Webb" wrote:

> Hi
> I'd like to create a query which returns the MAX of a group of dates so lo
ng
> as the number is less than a given date. For example :
>
> SELECT MAX(date), username
> FROM mydatatable
> WHERE date < '01/01/2005'
> GROUP BY username
>
> Will this do what I expect and return the username and date which is the
> most recent before 01/01/2005 ?
> Thanks
> Andrew
>
>

PP: XML Variable DataLength returns as 5

Try using
select CAST(@.xmlSourceDestinationAttributes AS VARBINARY(MAX))
and you will see the BOM that is at the beginning of the xml document.
Dan

> set nocount on
> Declare @.xmlSourceDestinationAttributes XML
> Select @.xmlSourceDestinationAttributes = ''
> --Select @.xmlSourceDestinationAttributes
> select Datalength(@.xmlSourceDestinationAttribut
es)Hi Folks
Here is what I found, when I execute this query
set nocount on
Declare @.xmlSourceDestinationAttributes XML
Select @.xmlSourceDestinationAttributes = ''
--Select @.xmlSourceDestinationAttributes
select Datalength(@.xmlSourceDestinationAttribut
es)
5
Question
=======
How come I get a value of 5 even tough I passed nothing.|||Try using
select CAST(@.xmlSourceDestinationAttributes AS VARBINARY(MAX))
and you will see the BOM that is at the beginning of the xml document.
Dan

> set nocount on
> Declare @.xmlSourceDestinationAttributes XML
> Select @.xmlSourceDestinationAttributes = ''
> --Select @.xmlSourceDestinationAttributes
> select Datalength(@.xmlSourceDestinationAttribut
es)sql

PP: XML Variable DataLength returns as 5

Hi Folks
Here is what I found, when I execute this query
set nocount on
Declare @.xmlSourceDestinationAttributes XML
Select @.xmlSourceDestinationAttributes = ''
--Select @.xmlSourceDestinationAttributes
select Datalength(@.xmlSourceDestinationAttributes)
--
5
Question
======= How come I get a value of 5 even tough I passed nothing.Try using
select CAST(@.xmlSourceDestinationAttributes AS VARBINARY(MAX))
and you will see the BOM that is at the beginning of the xml document.
Dan
> set nocount on
> Declare @.xmlSourceDestinationAttributes XML
> Select @.xmlSourceDestinationAttributes = ''
> --Select @.xmlSourceDestinationAttributes
> select Datalength(@.xmlSourceDestinationAttributes

Postage Calculation goes where?

When a customer proceeds to the checkout of the store I'm working on, the customer's ID is sent to a database stored procedure which returns the details of the order based on the shopping cart and customer account details stored in the database.

Should I have the stored procedure calculate the postage and packing for the order or should I wait till the order details are returned to the asp.net code, do some calculations, and then update the order?

Having this code in the asp.net page would not only require a trip to the server to do the update but also a trip to retrieve p&p information used for the calculations. These extra trips are my concern.

Any thoughts?::These extra trips are my concern.

Why? Are you working for Amazon?

How many hundred CHECKOUTS do you have per minute?

IMHO this is a non-issue, at least with some caching.

Possible?: Count(*) returned by EXEC

Hi all,

I have a stored procdure which does a select and returns the records
directly -i.e. Not in output parameters e.g:

CREATE PROCEDURE up_SelectRecs(@.ProductName nvarchar(30)) AS

SELECT *
FROM MyTable
WHERE [Name]=@.ProductName

In another stored procedure I need to do the following:

SELECT COUNT(*)
FROM MyTable
WHERE [Name]=@.ProductName

As the select queries are actually a lot more complex that this, I'd
rather not duplicate the select code in 2 sp's to save the maintenance
effort - I'm looking for a way to execute the first procedure from the
second and just count the records returned - something like:

SELECT Count(*)
FROM EXEC up_SelectRecs @.ProductName

Any way to achieve this?

Thanks all

--James"James" <Jamesmitchard@.yahoo.co.uk> wrote in message
news:19d01a84.0501261535.1d7c6dd7@.posting.google.c om...
> Hi all,
> I have a stored procdure which does a select and returns the records
> directly -i.e. Not in output parameters e.g:
> CREATE PROCEDURE up_SelectRecs(@.ProductName nvarchar(30)) AS
> SELECT *
> FROM MyTable
> WHERE [Name]=@.ProductName
> In another stored procedure I need to do the following:
> SELECT COUNT(*)
> FROM MyTable
> WHERE [Name]=@.ProductName
> As the select queries are actually a lot more complex that this, I'd
> rather not duplicate the select code in 2 sp's to save the maintenance
> effort - I'm looking for a way to execute the first procedure from the
> second and just count the records returned - something like:
> SELECT Count(*)
> FROM EXEC up_SelectRecs @.ProductName
> Any way to achieve this?
> Thanks all
> --James

See here:

http://www.sommarskog.se/share_data.html

If you have SQL 2000 (you didn't mention which version you have), a
table-valued UDF would probably work well in your case:

select * from dbo.MyFunc(@.ProductName)
select count(*) from dbo.MyFunc(@.ProductName)

Simon

Possible todo some form of DISTINCT filtering?

Hi guys, I have a dataset that returns rows that look like the following
field | value
--
1 | 7
1 | 7
2 | 4
8 | 90
Is it possible to remove the duplicate (1 | 7) row directly within a LIST
control (or similar?)? I cannot use a DISTINCT directly in the SQL as
elsewhere I need to display the duplicate row.
Thanks for any help
TazSilly me, thats what grouping is for lol.
Thanks anyways
Taz

Possible to OPENXML() from URL?

Is it possible to get the XML Document for a call to OPENXML() from a URL
directly?
Example: I have a .NET web service that returns XML based on parameters
submitted to the URL. I'd like to be able to write a TSQL batch that gets
XML documents from this server and processes them using OPENXML to update
rows in a SQL table.
Anyone ever done something like this?
TIA
Paul
Have you considered using an XML template? Templates accept parameters, much in the same way as stored procedures.
I've written an article on this topic, which you can find at the following URL:
http://www.sqljunkies.com/Article/53...3C8ECACDC.scuk
Hope this helps!
Denise.
Denise E. White
Technical Director
The Next Version UK
www.thenextversion.com
www.denisewhite.co.uk

possible to concatenate text to Where clause?

declare @.s varchar(300)
set @.s = ' recID = 100'
Select * from tbl1 Where + @.s
This returns a syntax error. Is it possible to concatenate text like this
to the Where clause? What does the correct syntax look like?
Thanks,
Richbetter yet - is it possible to use If/Else or select case in a where clause?
"Rich" wrote:

> declare @.s varchar(300)
> set @.s = ' recID = 100'
> Select * from tbl1 Where + @.s
> This returns a syntax error. Is it possible to concatenate text like this
> to the Where clause? What does the correct syntax look like?
> Thanks,
> Rich|||I seriously doubt this will help, but here is a small sample.
Any/every time I've tried to put a case statement in the where clause, I
fall flat.
But here is a small sample anyways.
You might want to check my article at:
http://www.sqlservercentral.com/col...lem.as
p
also.
declare @.price float
select @.price = 0.00
select @.price = 2.9900
--select @.price = null
select top 5 * , title, price
from pubs.dbo.titles
where
case
when @.price = 0 then price --here i am saying.. i didn't actually supply a
price.. so match the price with the price (aka, it gets everything because
price will always match price )
when @.price is null then price
else @.price -- aka, since i actually supplied a @.price, then match
the @.price with the price
end
= price -- the db field
----
---
-- the CASE statement above will translated into 1 of the two items below
select top 5 * , title, price
from pubs.dbo.titles where price = price -- aka, will always match, so you
get everything
select top 5 * , title, price
from pubs.dbo.titles where @.price = price --aka, only if the db value for
price matches the variable value for @.price
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:00B5807B-9AE6-4F4D-827E-63E926690110@.microsoft.com...
> better yet - is it possible to use If/Else or select case in a where
clause?
> "Rich" wrote:
>
this|||Thanks for your reply. This does help actually. What I was trying to do
was to use an SP as a recordsource for a form, but I changed my mind and
decided to go with an inline function. This works, and I can still use the
case statement inside the function.
May I ask, how do you make arguments optional for a function?
"sloan" wrote:

> I seriously doubt this will help, but here is a small sample.
> Any/every time I've tried to put a case statement in the where clause, I
> fall flat.
> But here is a small sample anyways.
> You might want to check my article at:
> http://www.sqlservercentral.com/col...lem.
asp
> also.
>
>
> declare @.price float
> select @.price = 0.00
> select @.price = 2.9900
> --select @.price = null
> select top 5 * , title, price
> from pubs.dbo.titles
>
> where
> case
> when @.price = 0 then price --here i am saying.. i didn't actually supply
a
> price.. so match the price with the price (aka, it gets everything because
> price will always match price )
> when @.price is null then price
> else @.price -- aka, since i actually supplied a @.price, then match
> the @.price with the price
> end
> = price -- the db field
> ----
--
> ---
> -- the CASE statement above will translated into 1 of the two items below
> select top 5 * , title, price
> from pubs.dbo.titles where price = price -- aka, will always match, so y
ou
> get everything
> select top 5 * , title, price
> from pubs.dbo.titles where @.price = price --aka, only if the db value fo
r
> price matches the variable value for @.price
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:00B5807B-9AE6-4F4D-827E-63E926690110@.microsoft.com...
> clause?
> this
>
>|||Rich
Unless I am completely wrong, I feel its fairly straightforward. try this.
declare @.s varchar(300)
set @.s = ' recID = 100'
exec('Select * from tbl1 Where ' + @.s)|||You can do this, but it's a horrible idea if @.s is
user-input or based on user-supplied data or metadata.
If @.s is something like '1=1; drop table tbl1' and the
code is executed with sufficient privilege, boom! Bye,
bye table 1. Worse and less noticeable things can
happen, too.
Read
http://www.*sommarskog*.se/dynamic_sql.html
http://www.unixwiz.net/techtips/sql-injection.html
Steve Kass
Drew University
Omnibuzz wrote:

>Rich
> Unless I am completely wrong, I feel its fairly straightforward. try this
.
>declare @.s varchar(300)
>set @.s = ' recID = 100'
>exec('Select * from tbl1 Where ' + @.s)
>
>