locked
SSAS Tabular Role Members RRS feed

  • Question

  • can we automate members addition and removal from tabular cube using AMO as explained below.

    ry
    {
    //connect to analysis server
    server = new Server();
    server.Connect(@"data source=<server name>");
    //create new database
    db = new Database("<database name>");
    db = server.Databases.Add("<database name>");
    db.Update();
    DataSource datasrc = db.DataSources.FindByName("<database name>");
    // Create the data source
    datasrc = db.DataSources.Add("<data source name>", "<data source id");
    datasrc.ConnectionString = @"Provider=SQLNCLI10.1;Data Source=<server name>;
    Integrated Security=SSPI;Initial Catalog=adventureworks"
    ;
    // Send the data source definition to the server.
    datasrc.Update();
    //add Data source view to the newly created data source
    DataSourceView dsv = null;
    dsv = db.DataSourceViews.Add();
    dsv.DataSourceID = "<data source id>";
    dsv.Name = "<data source view name>";
    dsv.Schema = new DataSet();
    System.Data.OleDb.OleDbConnection cn =
    new System.Data.OleDb.OleDbConnection(dsv.DataSource.ConnectionString);
    cn.Open();
    //add table
    System.Data.OleDb.OleDbDataAdapter daContact =
    new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM Person.Contact", cn);
    daContact.FillSchema(dsv.Schema, SchemaType.Mapped, "Contact");
    DataTable[] dts =
    daContact.FillSchema(dsv.Schema,SchemaType.Mapped, "Contact");
    DataTable dt = dts[0];
    dt.ExtendedProperties.Add("TableType", "Table");
    //this step will specify the schema name for table
    dt.ExtendedProperties.Add("DbSchemaName", "Person");
    dt.ExtendedProperties.Add("DbTableName", "Contact");
    //following step can be ommitted if we want to keep same friendly name
    dt.ExtendedProperties.Add("FriendlyName", "Contact");
    dsv.Update();
    }
    catch (Exception ex)
    {
    MessageBox.Show(ex.Message);
    }

    Another facility which AMO offers is to wok without connecting to the analysis services instance. This metadata can be kept for later usage for deployment.

    With following code we process partition, dimensions and cube without actually executing. Later we execute all in one batch in parallel.

    try
    {
    //connect to analysis server
    server = new Server();
    server.Connect(@"data source=<server name>");
    server.CaptureXml = true;
    //list all the databases from the server in listbox
    db = server.Databases["<database name>"];
    foreach (Cube cube in db.Cubes)
    {
    cube.Process(ProcessType.ProcessFull);
    foreach (MeasureGroup measuregrp in cube.MeasureGroups)
    {
    foreach (Partition partition in measuregrp.Partitions)
    {
    partition.Process();
    }
    }
    }

    foreach (Dimension dim in db.Dimensions)
    {
    dim.Process();
    }
    //stop capturing xml
    server.CaptureXml = false;
    //execute actually on server
    XmlaResultCollection results = server.ExecuteCaptureLog(true, true);
    foreach (XmlaResult result in results)
    {
    foreach (XmlaMessage message in result.Messages)
    {
    MessageBox.Show((message.Description));
    if (message is XmlaError)
    {

    // the processing failed, there is at
    //least one error
    }
    }
    }

    }
    catch (AmoException amoEx)
    {
    MessageBox.Show(amoEx.Message);
    }
    catch (Exception ex)
    {
    MessageBox.Show(ex.Message);

    }

    }


    Tuesday, June 9, 2020 11:47 AM

All replies

  • Hi, 

    Since the two code snippets are commented well, I think we could understand the code relatively easy.

    So I think the first snippet looks not like what you need about role management in SSAS Tabular.

    Check this document which shows us code sample about add different roles using AMO, see : Programming AMO security objects

    And for deleting roles check another thread: How to delete the role by using AMO(Analysis Management Object)?

    Regards,

    Lukas


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, June 10, 2020 2:00 AM
  • Hi

    The code that I had mentioned worked fine for Multidimensional cube.

    I am looking for similar code that would work on Tabular Model with 1400 compatibility of higher.

    Regards

    Wednesday, June 10, 2020 3:40 AM
  • We need to use TOM to develop for SSAS tabular, which is an extension of AMO.  Most of the code would be identical, and the Microsoft.AnalysisServices.Tabular.dll is needed. Tabular Object Model (TOM)


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, June 10, 2020 8:29 AM
  • Have tried it already, below is the code which is getting successful but member is not reflecting in the cube role.

    server = new Server();
                server.Connect("Provider=MSOLAP;Data Source=localhost;Integrated Security=SSPI;");
                Database db = server.Databases.FindByName("name");
                if (db == null)
                    return;
                else
                    System.Windows.Forms.MessageBox.Show("Found");
                var roles = db.Model.Roles;
               
                Microsoft.AnalysisServices.Tabular.ModelRole dbRole =null;
                dbRole = roles.Find("test");
                ModelRoleMember roleMember = new ExternalModelRoleMember();
                System.Windows.Forms.MessageBox.Show(dbRole.Name.ToString());
                roleMember.MemberName = "CRB\\xyz";
               

                dbRole.Members.Add(roleMember);

    Wednesday, June 10, 2020 9:08 AM
  • I think we could focus on your other thread in order to avoid duplicated posts.

    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, June 11, 2020 6:57 AM