This one caught my eye on the Data Mining Forum and it is not the first time that this one has cropped up.
The question
"Hello
I have a DMX listed below. Now if i run this in the Sql Server Management Studio, it returns me the list of Customers that meet the specified criteria (in the Where clause, PredictProbability(Response) > 0.6) ). I would like to just get the count of customers who meet this criteria, dont need the actual Customer records. Since we dont have the equivalent of COUNT(*) in DMX, how would you go about doing this.
There must be a way to write a C# CLR Assembly to take the results of the select statement and count the number of records in the assembly, this way i dont have to bring all the results to the client and count. So i would write something like the following,
Select MYAssembly.GetRecordCount(t.[[Measures]].[Customer]]] )
FROM ............
However, when i write the GetRecordCount function, i am not sure what the datatype is for the customer records. The would probably do the trick
Is there another way to just get the record or case count ?"
This is the code that WAS POSTED. I have placed a nifty solution below: See THE SOLUTION.
select t.[[Measures]].[Customer]]]
FROM [PramanaDTModel]
PREDICTION JOIN
(
WITH
MEMBER MEASURES.[Customer]
AS [Customer Model Input].[Cust Id].MEMBER_CAPTION
MEMBER MEASURES.[Adult Age]
AS [Customer Model Input].[Cust Id].PROPERTIES('Adult Age')
MEMBER MEASURES.[Children Age]
AS [Customer Model Input].[Cust Id].PROPERTIES('Children Age')
MEMBER MEASURES.[Children Present Flag]
AS [Customer Model Input].[Cust Id].PROPERTIES('Children Present Flag')
MEMBER MEASURES.[Dwell Size]
AS [Customer Model Input].[Cust Id].PROPERTIES('Dwell Size')
MEMBER MEASURES.[Education Level]
AS [Customer Model Input].[Cust Id].PROPERTIES('Education Level')
MEMBER MEASURES.[Gender]
AS [Customer Model Input].[Cust Id].PROPERTIES('Gender')
MEMBER MEASURES.[Home Market Value]
AS [Customer Model Input].[Cust Id].PROPERTIES('Home Market Value')
SELECT {
MEASURES.[Customer],
MEASURES.[Adult Age],
MEASURES.[Children Age],
MEASURES.[Children Present Flag],
MEASURES.[Dwell Size],
MEASURES.[Education Level],
MEASURES.[Gender],
MEASURES.[Home Market Value]
} ON COLUMNS,
[Customer Model Input].[Cust Id].MEMBERS ON ROWS
FROM [PramanaCube]) AS t
ON [PramanaDTModel].[Adult Age] = t.[[Measures]].[Adult Age]]]
AND [PramanaDTModel].[Children Age] = t.[[Measures]].[Children Age]]]
AND [PramanaDTModel].[Children Present Flag] = t.[[Measures]].[Children Present Flag]]]
AND [PramanaDTModel].[Dwell Size] = t.[[Measures]].[Dwell Size]]]
AND [PramanaDTModel].[Education Level] = t.[[Measures]].[Education Level]]]
AND [PramanaDTModel].[Gender] = t.[[Measures]].[Gender]]]
AND [PramanaDTModel].[Home Market Value] = t.[[Measures]].[Home Market Value]]]
where
PredictProbability([PramanaDTModel].[Response]) > 0.6
THE SOLUTION
Hi
The answer is actually very simple. HERE IS THE TRICK. You must create a linked server against the OLAP data base and within management studio, create an 'outer query' against the mining model. Here is some working code.
BTW Dejan Sarka put me onto this technique. You might want to read his article on SQLServerCentral.
select G.Region,Count(G.[YearsInBlack])[Amount],[State]
--(sum(G.[YearsInBlack])) Average
--Select convert(decimal,(AVG(G.[YearsInBlack]))),G.Region,(count(G.[YearsInBlack])),(sum(G.[YearsInBlack]))
from (select dmx.yearsinblack,convert(varchar(2),dmx.[State_Province]) [State],
case convert(varchar(2),dmx.[State_Province])
--dmx.[State_Province]
WHEN 'PA' THEN 'NE'
WHEN 'WI' THEN 'NE'
WHEN 'IL' THEN 'NE'
WHEN 'NH' THEN 'NE'
WHEN 'MD' THEN 'SE'
WHEN 'MI' THEN 'NE'
WHEN 'TX' THEN 'SW'
WHEN 'KS' THEN 'SW'
WHEN 'DE' THEN 'SE'
WHEN 'IN' THEN 'NE'
WHEN 'CO' THEN 'SW'
WHEN 'VA' THEN 'SE'
WHEN 'ME' THEN 'NE'
WHEN 'AK' THEN 'NW'
WHEN 'RI' THEN 'NE'
WHEN 'NV' THEN 'SW'
WHEN 'OH' THEN 'NE'
WHEN 'CT' THEN 'NE'
WHEN 'LA' THEN 'SW'
WHEN 'MO' THEN 'SW'
WHEN 'SC' THEN 'SE'
WHEN 'WA' THEN 'NW'
WHEN 'MA' THEN 'NE'
WHEN 'NY' THEN 'NE'
WHEN 'SD' THEN 'NW'
WHEN 'NJ' THEN 'NE'
WHEN 'OR' THEN 'NW'
WHEN 'CA' THEN 'SW'
WHEN 'MT' THEN 'NW'
WHEN 'OK' THEN 'SW'
WHEN 'FL' THEN 'SE'
WHEN 'AR' THEN 'SW'
WHEN 'VT' THEN 'NE'
WHEN 'AL' THEN 'SE'
WHEN 'ND' THEN 'NW'
WHEN 'ID' THEN 'NW'
WHEN 'MN' THEN 'NW'
WHEN 'IA' THEN 'NW'
WHEN 'GA' THEN 'SE'
WHEN 'NC' THEN 'SE'
WHEN 'NE' THEN 'NW'
WHEN 'WY' THEN 'NW'
WHEN 'HI' THEN 'SW'
WHEN 'KY' THEN 'NE'
WHEN 'UT' THEN 'NW'
WHEN 'AZ' THEN 'SW'
WHEN 'MS' THEN 'SW'
WHEN 'TN' THEN 'SW'
WHEN 'WV' THEN 'SE'
WHEN 'NM' THEN 'SW'
else 'UK' end as "Region"
from
OPENQUERY (Linked_As1,
'SELECT
[Decision Tree - Moody Risk].[SSC Preferred],
t.[Customer_Name_Name],
t.[Address1],
t.[City],
t.[State_Province],
t.[AAA],
t.[YearsInBlack]
From
[Decision Tree - Moody Risk]
PREDICTION JOIN
OPENQUERY([MoodyRiskSSC],
''SELECT
[Customer_Name_Name],
[Address1],
[City],
[State_Province],
[AAA],
[YearsInBlack],
[Credit_Class],
[DealtWithBefore]
FROM
[dbo].[Moody]
'') AS t
ON
[Decision Tree - Moody Risk].[Credit Class] = t.[Credit_Class] AND
[Decision Tree - Moody Risk].[Years In Black] = t.[YearsInBlack] AND
[Decision Tree - Moody Risk].[Dealt With Before] = t.[DealtWithBefore]
WHERE
[Decision Tree - Moody Risk].[SSC Preferred] =''Y'' AND
-- t.[AAA] =''Y'' AND
t.[YearsInBlack] =2') AS dmx
) as G
where G.[YearsInBlack]>=0 and g.[State] in ('ME','NH','MA','CT','RI','VT')
--and G.Region = 'NE'
Group by G.State ,G.region
order by G.state
Should you have any questions or concerns, please feel free to contact me. Happy programming.
regards Steve