Query dimension / fact table ID on SSAS

Question

I have SSAS server with a analytical tabular model. It has several dimensions and fact tables.

I want to create dynamic XMLA code for processing dimensions / fact tables. Using SSMS, I can script out sample XMLA as follows:

<Process xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Type>ProcessFull</Type>
  <Object>
    <DatabaseID>DB1</DatabaseID>
    <DimensionID>FactTable1_cd8ad531-34f1-42df-b34e-a535eed297c1</DimensionID>
  </Object>
</Process>

Where do I find the DatabaseID / DimensionID information of the model on SSAS so I can use it for purpose of dynamic script? Is there any DMVs (or similar) that would return me such information?

What tool or programming language is creating the dynamic XMLA? If you say T-SQL and linked servers my answer will be different than if you say SSIS and a C# script task. — GregGalloway, Nov 25 '15 at 01:33
I would use SSIS, but if the solution with T-SQL & linked servers would be more elegant, I would consider using that one. Could you maybe share both? — DNac, Nov 25 '15 at 11:46

score 0 · Answer 1 · answered Nov 25 '15 at 16:01

Create an Analysis Services connection manager in SSIS. Then create a new C# script task in SSIS and add a reference to AMO (Microsoft.AnalysisServices). I usually do that by choosing Add Reference... Browse... then pasting "C:\Windows\assembly\GAC_MSIL\Microsoft.AnalysisServices\" then looking for the version that will be installed on the server where I will deploy this SSIS package. Then add "using Microsoft.AnalysisServices;" to the top of your code. Here's the Main() function:

public void Main()
{
    // Get Server and Database name from DTS connection object
    string sServer = "";
    string sDatabase = "";
    foreach (ConnectionManager oConn in this.Dts.Connections)
    {
        if (oConn.CreationName.StartsWith("MSOLAP"))
        {
            sServer = (string)oConn.Properties["ServerName"].GetValue(oConn);
            sDatabase = (string)oConn.Properties["InitialCatalog"].GetValue(oConn);
            break;
        }
    }

    if (string.IsNullOrEmpty(sServer) || string.IsNullOrEmpty(sDatabase))
    {
        Dts.TaskResult = (int)ScriptResults.Failure;
        throw new Exception("Could not find or parse the SSAS connection string");
    }

    Server oServer = new Server();
    oServer.Connect(sServer);

    Database oDB = oServer.Databases.FindByName(sDatabase);
    if (oDB == null)
    {
        oServer.Disconnect();
        Dts.TaskResult = (int)ScriptResults.Failure;
        throw new Exception("Did not find expected SSAS database: " + sDatabase);
    }

    Dimension dim = oDB.Dimensions.GetByName("Employee");
    string sDimensionID = dim.ID;
    string sDatabaseID = oDB.ID;

    MessageBox.Show(sDatabaseID + " - " + sDimensionID);

    oServer.Disconnect();

    Dts.TaskResult = (int)ScriptResults.Success;
}

Incidentally, I would recommend not mucking with building XMLA. Just use AMO to do the processing:

dim.Process(ProcessType.ProcessFull);

I don't know how to get the Database ID cleanly with a DMV against SSAS Tabular other than by shredding a bunch of XML. So I wouldn't recommend a linked server.

Query dimension / fact table ID on SSAS

1 Answers1