none
DMX Query to Get Properly Formatted Bundled Products Information

    Question

  • I am new to SSAS data mining. I have started exploring the SSAS Microsoft Association Rules. This will be needed so as to be able to design a SSRS report that fetches useful information about product shopping basket statistics in particular bundled products, support size for each bundle and budle size.


    I found that the shopping basket analysis tool available in Excel 2007/2010 produces exactly what we want. The only problem is that it seems that Excel performs behinds the seens formatting to the returned DMX output in order to present the information in a user-friendly manner to the user.


    Through Internet articles, books and other sources, we were able to build the required mining structure and model in SSAS 2008 R2 through SSMS-DMX queries


    I reached a stage where I was able to acquire useful information but this needs to be properly formatted for proper display.


    Here is the DMX queries we used:


    -- 1. CREATE THE MINING STRUCTURE
    CREATE MINING STRUCTURE BundledProducts
    (
      [OrderID] TEXT KEY,
      Products TABLE
      (
        ProductName TEXT KEY
      )


    -- 2. TRAIN THE STRUCTURE FROM REAL DATA
    INSERT INTO [BundledProducts]
    (
      [OrderID],
      Products (SKIP,[ProductName])
    )
    SHAPE
    {
      OPENQUERY
        ([MyDatasource],
         'SELECT DISTINCT [OrderID] AS [OrderID]
          FROM dbo.[vCustomerOrders]
          ORDER BY [OrderID]
         '
        )
    }
    APPEND
    (
      {
       OPENQUERY
        ([MyDataSource],
         'SELECT [OrderID] AS [OrderID],
                 [ProductName]  AS [ProductName]
          FROM dbo.[vCustomerOrders]
          ORDER BY [OrderID]
         '
        )
      }
      RELATE [OrderID] To [OrderID]
    )
      AS [Products]


    -- 3. CREATE THE MINING MODEL
    ALTER MINING STRUCTURE BundledProducts
    ADD MINING MODEL ProductRecommendations
    (
        [OrderID],
        Products PREDICT
        (
            ProductName
        )
    )
    USING Microsoft_Association_Rules (Minimum_Probability = 0.4, MINIMUM_SUPPORT = 10)


    -- 4. QUERY THE MODEL TO GET BUNDLED PRODUCTS
    SELECT NODE_CAPTION,NODE_SUPPORT FROM ProductRecommendations.CONTENT WHERE NODE_TYPE=7

    The later query returns the following:

    NODE_CAPTION       NODE_SUPPORT
    ------------------------------------------------------------------------------
    ProductA = Existing      10200
    ProductB = Existing      5400
    ProductC = Existing      3800
    ProductA = Existing, ProductB = Existing   550
    ProductA = Existing, ProductC = Existing   340
    ProductB = Existing, ProductC = Existing   220
    ProductA = Existing, ProductB = Existing, ProductC = Existing 130


    Now, to properly format this, we need to:
    1. Get rid of the annoying "= Existing" substring
    2. Count the number of commas in the NODE_CAPTION so as to be able to know the BUNDLE_SIZE which should be an extra calculated column returned by the DMX. So if the number of commas=0, then bundle size=1, commas=1 then bundle size=2 and so on...


    The question is, how can we accomplish 1 & 2 through DMX so as to reurn formatted DMX column data that could be called and used from a SSRS report


    Thank in advance for your help...

       


    Luai7


    • Edited by luai7 Wednesday, April 11, 2012 12:29 PM
    Wednesday, April 11, 2012 12:28 PM

Answers

  • You can't do that with DMX. Why don't you write .net store procedure? (You know how to parse those strings using C#, don't you?)

    Tatyana Yakushev [PredixionSoftware.com]

    Wednesday, April 11, 2012 5:25 PM
    Answerer

All replies

  • You can't do that with DMX. Why don't you write .net store procedure? (You know how to parse those strings using C#, don't you?)

    Tatyana Yakushev [PredixionSoftware.com]

    Wednesday, April 11, 2012 5:25 PM
    Answerer
  • Thank you Tatyana for the quick reply.

    In fact I am more a VB.Net guy :-)

    But even if I do what you have suggested, will I be able to use that in SSRS by using the Stored Procedure calling method? Does SSRS support SP for DMX ??

    Can we do that through an ordinary T-SQL SP, because I can parse this information there, but will I be able to call DMX from there ??

    You see the constraint for me is that I need to call this DMX from SSRS

    Any ideas?

    Thanks a lot


    Luai7


    • Edited by luai7 Wednesday, April 11, 2012 6:06 PM
    Wednesday, April 11, 2012 6:03 PM
  • Wednesday, April 11, 2012 7:17 PM
    Answerer
  • Thank you Tatyana.

    In fact I know how to call a SP from SSRS :-)

    This is what I was looking for:

    http://technet.microsoft.com/en-us/library/bb895229(v=sql.105).aspx

    Anyways, I have another question please:

    Users of the report would like to have the chance to change the date range and shop(s) by applying these filters in the report parameters pane. So that the bundled products are bound to the selected user filters...

    So I am thinking of doing that by writing a CLR function that re-issues the following INSERT statements:

    DELETE FROM BundledProducts

    INSERT INTO [BundledProducts]
    (
      [OrderID],
      Products (SKIP,[ProductName])
    )
    SHAPE
    {
      OPENQUERY
        ([MyDatasource],
         'SELECT DISTINCT [OrderID] AS [OrderID]
          FROM dbo.[vCustomerOrders]

          WHERE  OrderDate>=.... and OrderDate<=....

              AND  ShopName IN (......)

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

          WHERE  OrderDate>=.... and OrderDate<=....

              AND  ShopName IN (......)

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

    The date boundaries and possible shops will be passed as parameters to the called SP from SSRS based on the selection used by the user.

    My question is:

    1.  is there a better way of doing that?

    2. If multiple users are running the same report at the same time, won't that command affect the running report for one of the users ?

    Any ideas here are really welcome


    Luai7



    • Edited by luai7 Thursday, April 12, 2012 7:52 AM
    Thursday, April 12, 2012 7:20 AM
  • Could somebody guide me pls to step-by-step instructions on buidling the below data mining model in BIDS 2008... I've been having all sorts of problems doing so today. I am able to generate a working model when the below is executed from SSMS, but I am not able to do so in BIDS... May be I am missing something there although I went through all the documentations etc...

    It is really frustrating :-(

    Is there a way to import a model from a built mining model in SSMS to BIDS project?

    -- 1. CREATE THE MINING STRUCTURE
    CREATE MINING STRUCTURE BundledProducts
    (
      [OrderID] TEXT KEY,
      Products TABLE
      (
        ProductName TEXT KEY
      )


    -- 2. TRAIN THE STRUCTURE FROM REAL DATA
    INSERT INTO [BundledProducts]
    (
      [OrderID],
      Products (SKIP,[ProductName])
    )
    SHAPE
    {
      OPENQUERY
        ([MyDatasource],
         'SELECT DISTINCT [OrderID] AS [OrderID]
          FROM dbo.[vCustomerOrders]
          ORDER BY [OrderID]
         '
        )
    }
    APPEND
    (
      {
       OPENQUERY
        ([MyDataSource],
         'SELECT [OrderID] AS [OrderID],
                 [ProductName]  AS [ProductName]
          FROM dbo.[vCustomerOrders]
          ORDER BY [OrderID]
         '
        )
      }
      RELATE [OrderID] To [OrderID]
    )
      AS [Products]


    -- 3. CREATE THE MINING MODEL
    ALTER MINING STRUCTURE BundledProducts
    ADD MINING MODEL ProductRecommendations
    (
        [OrderID],
        Products PREDICT
        (
            ProductName
        )
    )
    USING Microsoft_Association_Rules (Minimum_Probability = 0.4, MINIMUM_SUPPORT = 10)


    Luai7

    Thursday, April 12, 2012 4:14 PM
  • I don't think this is a good idea. As you said, this will not work correctly if multiple users are running the report at the same time. It is also very inefficient.

    How important is it for you to be able to specify random date range?

    Do you think you can build one model per shop (or a group of shops, for example from the same state)?

    To do this, you can have single mining structure with multiple models (one per shop) with a filter (e.g. ShopID='123').

    Then in the report or SP you can choose which model to use.


    Tatyana Yakushev [PredixionSoftware.com]

    Thursday, April 12, 2012 4:57 PM
    Answerer
  • You can connect to Analysis Services database from BIDS, where you will see all the mining structures and models that you created (in SSMS).

    When users create new mining structures and models in BIDS, they usually define data source and data source view, then go through wizard. Look at one of the tutorials.


    Tatyana Yakushev [PredixionSoftware.com]

    Thursday, April 12, 2012 5:01 PM
    Answerer
  • Thanks Tatyana

    Your suggested workaround is fine only if you have a fixed number of shops. However, when you have a very dynamic number of shops for every single customer, then it is impossible to maintain these models.

    I will go for the below solution although I know that it is not that efficient and 2 users cannot refresh the model at the same time... We will train users on this...

    http://social.msdn.microsoft.com/Forums/en-US/sqldatamining/thread/6aa3b816-e239-4d8c-ae28-a269f224875f


    Luai7



    • Edited by luai7 Wednesday, April 18, 2012 12:29 PM
    Wednesday, April 18, 2012 12:27 PM
  • hiiiii all,

    shopping basket analysis in excel  I get up to only bundle size 3

    I want to increase the bundle size like 4 or 5
    how can I please give me suggestion

    Thursday, August 16, 2012 11:58 AM
  • You cannot increase bundle size to 4 or 5 when using Shopping Basket Analysis Tool.

    You would have to use Data Mining tab and create association rules model, however, there you don't get the nice reports.


    Tatyana Yakushev [PredixionSoftware.com]

    Thursday, August 16, 2012 4:42 PM
    Answerer
  • thanks tatyana yakushev
    Friday, August 17, 2012 6:00 AM