Server-Side Stored Procedure for Simple DMX Statements

Question

Currently, I am using the below script to re-train and re-process my Association Rules model by applying another criteria (i.e. see below: UpdatedCriteria). It works pefectlt allright

What I am looking for is an extremely simple code that enables me to build a server side stored procedure that executes the below commands on SSAS. The procedure should accept only a single parameter which is the UpdatedCriteria string

I tried to search the Internet for this simple example, but couldn't find any. All what is there is how to select, how to predict... etc which added to my confusion.

Please help... I really don't want to waste too much time on this issue :-)

Thanks in advance

-- ------------------------------------------------------------------------
--
-- E X A M P L E    O N    R E F R E S H I N G   T H E   M O D E L   D A T A
--
-- C R I T E R I A -> T H E N   R E T R A I N    &   P R O C E S S
--
-- ------------------------------------------------------------------------

-- D E L E T E   S T R U C T U R E   &   M O D E L     C U R R E N T    D A T A
DELETE FROM MyDMModel
;
GO 

DELETE FROM [MyDMStruct]
;
GO

-- RE-T R A I N    T H E    D A T A    M I N I N G    S T R U C T U R E   F R O M   C U S T O M E R   O R D E R S
INSERT INTO [MyDMStruct]
(
  [OrderID],
  Products (SKIP,[ProductName])
)
SHAPE
{
  OPENQUERY
    ([MyDataSource],
     'SELECT DISTINCT [OrderID] AS [OrderID]
      FROM dbo.[vCustomerOrders]

      WHERE UpdatedCriteria    

      ORDER BY [OrderID]
     '
    )
}
APPEND
(
  {
   OPENQUERY
    ([MyDataSource],
     'SELECT [OrderID] AS [OrderID],
             [ProductName]  AS [ProductName]
      FROM dbo.[vCustomerOrders]

      WHERE UpdatedCriteria    

      ORDER BY [OrderID]
     '
    )
  }
  RELATE [OrderID] To [OrderID]
)
  AS [Products]
;
GO

-- R E - P R O C E S S    M O D E L
INSERT INTO MyDMModel
;
GO

 

---

Luai7

Answer 1

Following is the code :-)

Pending ToDO:

1. Implement the Try/Catch error handling

2. Remove the stepNumber parameter as it was used for debugging purposes

Off course, you can add other filter conditions (similar to ShopName) as required

You can call the function as follows:

MyDMXCustomLibrary.MyDMXCustomLibrary.MyDMXProc.MyRefreshModel('Main Shop',0)

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.AnalysisProducts.AdomdServer;
using System.Data;
using Microsoft.AnalysisProducts;

namespace MyDMXCustomLibrary
{
   // The aim of this function is to force retraining and reprocessing a specific data mining model by using an updated source criteria 
   public class MyDMXProc
    {
        [SafeToPrepare(true)]
        public static void MyRefreshModel(string ShopName,int stepNumber)
        {
          try
          {
            String sCommand = "";
            AdomdCommand cmd = new AdomdCommand();
           
            // Delete Structure Data
            if (stepNumber == 1 || stepNumber == 0)
            {
                cmd.CommandText = "DELETE FROM MyDMModel";
                cmd.ExecuteNonQuery();
            }
           
            // Delete Model Data
            if (stepNumber == 2 || stepNumber == 0)
            {
                cmd.CommandText = "DELETE FROM [MyDMStruct]";
                cmd.ExecuteNonQuery();
            }

           
            // Re-Train the Model
            if (stepNumber == 3 || stepNumber == 0)
            {
                sCommand = "";
                sCommand += "INSERT INTO [MyDMStruct]\r\n";
                sCommand += "  (\r\n";
                sCommand += "   [OrderID],\r\n";
                sCommand += "   Products (SKIP,[ProductName])\r\n";
                sCommand += "  )\r\n";
                sCommand += "SHAPE\r\n";
                sCommand += "  {\r\n";
                sCommand += "   OPENQUERY\r\n";
                sCommand += "    ([MyDataSource],\r\n";
                sCommand += "     'SELECT DISTINCT [OrderID] AS [OrderID]\r\n";
                sCommand += "      FROM dbo.[vCustomerOrders]\r\n";
                sCommand += "      WHERE IsNull([OrderID],''XXX'')<>''XXX''\r\n";
                if (ShopName.Equals("Default") || ShopName.Equals("All Shops"))
                    sCommand += "";
                else sCommand += "        AND ShopName=''" + ShopName + "''\r\n";
                sCommand += "      ORDER BY [OrderID]\r\n";
                sCommand += "     '\r\n";
                sCommand += "    )\r\n";
                sCommand += "  }\r\n";
                sCommand += "APPEND\r\n";
                sCommand += " (\r\n";
                sCommand += "  {\r\n";
                sCommand += "   OPENQUERY\r\n";
                sCommand += "    ([MyDataSource],\r\n";
                sCommand += "     'SELECT [OrderID] AS [OrderID],\r\n";
                sCommand += "             [ProductName]  AS [ProductName]\r\n";
                sCommand += "      FROM dbo.[vCustomerOrders]\r\n";
                sCommand += "      WHERE IsNull([OrderID],''XXX'')<>''XXX''\r\n";
                if (ShopName.Equals("Default") || ShopName.Equals("All Shops"))
                    sCommand += "";
                else sCommand += "        AND ShopName=''" + ShopName + "''\r\n";
                sCommand += "      ORDER BY [OrderID]\r\n";
                sCommand += "     '\r\n";
                sCommand += "    )\r\n";
                sCommand += "  }\r\n";
                sCommand += "  RELATE [OrderID] To [OrderID]\r\n";
                sCommand += " )\r\n";
                sCommand += "AS [Products]\r\n";
                //sCommand += "\r\n;\r\nGO\r\n";
                cmd.CommandText = sCommand;
                cmd.ExecuteNonQuery();
            }

            // Reprocess the Model
            if (stepNumber == 4 || stepNumber == 0)
            {
                cmd.CommandText = "INSERT INTO MyDMModel";
                cmd.ExecuteNonQuery();
            }
          }
         catch
          {

             // TO DO...

          }        

        }
    }
}

---

Luai7