Creating the Cross-Prediction Query in DMX

Question

Dear All ,
Below is from help menu for cross prediction. If you want to change the product,we have to update. Do I need to do by hand every time? What about if I want do this for hundred regions? Each time I need to do it by hand and get the result. There should be an easy way ?Any idea please?
SELECT
      ([All Regions].[Region]) as [Model Used],
      ('T-1000 Pacific') as [ModelRegion],
      (PredictTimeSeries([All Regions].[Avg Qty],5, REPLACE_MODEL_CASES)) as [Predicted Quantity]
     FROM [All Regions]
PREDICTION JOIN
    OPENQUERY([Adventure Works DW2003R2], 'SELECT [ReportingDate] FROM
      (
       SELECT  ReportingDate, ModelRegion, Quantity, Amount 
       FROM dbo.vTimeSeries 
       WHERE (ModelRegion = N''T1000 Pacific'')
       ) as [T1000 Pacific]    ') 
    AS t
ON 
[All Regions].[Reporting Date] = t.[ReportingDate] 
AND 
[All Regions].[Avg Qty] = t.[Quantity]

Answer 1

Here are a couple of examples of how to set up parameters in DMX queries 

Setting up using a CLR stored procedure

http://social.msdn.microsoft.com/Forums/uk/sqldatamining/thread/fe16c57b-2cc3-41e8-8f02-121540fb3eeb

The core is just 

System.Data.SqlClient.SqlCommand cmd = cn.CreateCommand();

cmd.CommandText = "SELECT TimeIndex, Quantity FROM v_VitalSignForecast " +

                             "WHERE HCVS_MemberId=@HCVS_MemberId AND HCVS_MeasureDate>=@From AND HCVS_MeasureDate<=@To ";

System.Data.SqlClient.SqlParameter param1 = cmd.CreateParameter();

param1.ParameterName = "HCVS_MemberID";

This would also be the easiest approach from a .NET app as you have true parameter support there. The example is showing how to create a CLR stored proc but you could take that approach directly from within a .NET app.

There is also dynamic SQL using a linked server

http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/2e576a88-ee20-4a7e-a24a-b64a619b093b

You should also be able to use old straight dynamic sql and OPENQUERY without having to setup a linked server.

---

Josh Ash