none
Querying a cluster model: system sprocs vs. DMX RRS feed

  • Question

  • Hi Folks -

    My goal is to pull a complete cluster profile from my model in order to visualize it.

    System.Microsoft.AnalysisServices.System.DataMining.Clustering.GetClusterProfiles() seems perfect for this. However, for reasons I won't get into here, I need to call it via OpenQuery(). While OpenQuery() seems to work fine with some of the other SSAS DM-related sprocs, it barfs on GetClusterProfiles(). Example:

    SELECT * FROM OpenQuery (SSAS,'CALL System.Microsoft.AnalysisServices.System.DataMining.Clustering.GetClusterProfiles(''Customer Clusters'', 0.0005, true)')

    Returns:

    Msg 7353, Level 16, State 1, Line 1

    The OLE DB provider "MSOLAP" for linked server "SSAS" supplied inconsistent metadata. An extra column was supplied during execution that was not found at compile time.

    I'm guessing this occurs because GetClusterProfiles() can return a variable number of columns based on the number of clusters in the model, and OpenQuery() and/or the MSOLAP provider can't deal with that uncertainty.

    So, anyway the questions:

    -I've pretty much written off the method above as a mechanism to solve my problem. Can someone confirm I should "stick a fork in it"?

    Barring the use of GetClusterProfiles(), I think I'll have to write some DMX ala:

    SELECT FLATTENED NODE_NAME,
    (SELECT ATTRIBUTE_VALUE, [SUPPORT],[PROBABILITY] FROM NODE_DISTRIBUTION WHERE ATTRIBUTE_NAME = 'Yearly Income' and [SUPPORT] > 500)
    AS t
    FROM [Customer Clusters].CONTENT
    WHERE NODE_TYPE = 5

    ....The problem is I have 5-6 attributes I need to pull out of this model. Am I going to have to write multiple DMX statements (one for each Attribute) and then UNION them back together or otherwise reshape the results?

    Just curious if there's an easier way.

    Thanks!

     

     

    Friday, February 11, 2011 2:16 PM

All replies

  • Is it possilbe query the datasource direclty with T-SQL? From your query, since you just used SELECT FLATTENED, then I don't think you have to use DMX.

    - SSAS_user

    Wednesday, February 16, 2011 9:48 AM
  • Hi Russell,

    GetClusterProfiles() is a function the System.Microsoft.AnalysisServices.System.DataMining.Clustering stored procedures and is accessible from a DMX query via ADOMD using the following syntax:

    CALL

     

    System.Microsoft.AnalysisServices.System.DataMining.Clustering.GetClusterProfiles('Customer Clusters', '002',true)

    Invoking the stored procedure via a Linked Server is a slightly different proposition. I'm investigating with the development team to see if this can be called via a linked server.

    Regards,

    John Desch

     

    Monday, February 21, 2011 11:35 PM