.NET连接到SSAS

Question

希望有人能回答这个问题：我想开始一个项目，其中.NET解决方案连接到SSAS（SQL Server Analysis Services）。但是在互联网上只有很少的主题或指南。 据我所知，我可以使用称为ADOMD.NET的C#库。但我不知道它是否足够适合我。 假设我将有数百万条记录的大量数据库，要获取其中的一些分析记录，我需要有能够创建表格模型（存储在内存中的“数据库”记录）的SSAS能力。 那么问题来了：我能否使用ADOMD.NET使用特定语言（如果存在）创建表格模型，并且是否有一些人有工作经验的建议？因为我需要将内存中存储的数据返回给用户并显示它。

- Andrius

Answer 1

如果您正在使用SSAS Tabular，我建议使用Tabular Object Model（TOM）库，这是一个扩展的AMO用于制表模型。请注意，这适用于兼容性级别1200或更高版本。以下示例创建了一个基本模型，其中包含一个维度和事实表、一个度量以及它们之间的关系，以从维度筛选事实表。之后，新模型会被部署到SSAS服务器，并进行处理以便可以使用。除了Microsoft.AnalysisServices.Tabular之外，您还需要添加对Microsoft.AnalysisServices.Core和Microsoft.AnalysisServices.AdomdClient的引用。

using Microsoft.AnalysisServices;
using Microsoft.AnalysisServices.Tabular;



string connStr = @"Data Source=ServerName";
string dataSource = "Data Source Name";
string measureExpression = @"SUM('FactTable'[Amount])";
using (Server serv = new Server())
{
    serv.Connect(connStr);

    string dbName = serv.Databases.GetNewName("New Tabular Model Name");

    Database db = new Database()
    {
        Name = dbName,
        ID = dbName,
        CompatibilityLevel = 1200,
        StorageEngineUsed = StorageEngineUsed.TabularMetadata
    };


    db.Model = new Model()
    {
        Name = "Model",
        Description = "Model Description"
    };

    //define data source
    db.Model.DataSources.Add(new ProviderDataSource()
  {
  Name = dataSource,
  Description = "Data Source Description",
  //for SQL server
  ConnectionString = @"Provider=SQLNCLI11;Data Source=ServerName;Initial Catalog=DatabaseName;Integrated Security=SSPI",
  ImpersonationMode = Microsoft.AnalysisServices.Tabular.ImpersonationMode.ImpersonateAccount,
  Account = @"AccountName",
  Password = "Password",
  });

    //add tables
    //dimension table
    db.Model.Tables.Add(new Table()
    {
        Name = db.Model.Tables.GetNewName("DimTable"),
        Description = "Dimension Table Description ",
        Partitions = {
        new Partition() {
            Name = "Partition 1",
            Source = new QueryPartitionSource() {
                DataSource = db.Model.DataSources[dataSource],
                Query = @"SELECT ID, NAME FROM DimensionTable",
            }
        }
    },
        Columns =
    {
        new DataColumn() {
            Name = "ID",
            DataType = DataType.Int64,
            SourceColumn = "ID",
        },
        new DataColumn() {
            Name = "Name",
            DataType = DataType.String,
            SourceColumn = "NAME",
        },
    }
    });

    //fact table
    db.Model.Tables.Add(new Table()
    {
        Name = db.Model.Tables.GetNewName("FactTable"),
        Description = "FactTable Description",
        Partitions = {
        new Partition() {
            Name = "Partition 1",
            Source = new QueryPartitionSource() {
                DataSource = db.Model.DataSources[dataSource],
                Query = @"SELECT ID, AMOUNT FROM FactTable",
            }
        }
    },
        Columns =
    {
        new DataColumn() {
            Name = "ID",
            DataType = DataType.Int64,
            SourceColumn = "ID",
        },
        new DataColumn() {
            Name = "Amount",
            DataType = DataType.Int64,
            SourceColumn = "AMOUNT",
        },
    }
    });

    //create column objects for relationship
    Column fromColumn = db.Model.Tables["FactTable"].Columns["ID"];
    Column toColumn = db.Model.Tables["DimTable"].Columns["ID"];

    //create relationship to filter fact table
    SingleColumnRelationship relationship = new SingleColumnRelationship()
    {
        Name = "FactTable_ID_DimTable_ID",
        ToColumn = toColumn,
        FromColumn = fromColumn,
        ToCardinality = RelationshipEndCardinality.One,
        FromCardinality = RelationshipEndCardinality.Many

    };
    db.Model.Relationships.Add(relationship);

    //create measure
    Measure measure = new Measure()
    { Name = "Total" };
    db.Model.Tables["FactTable"].Measures.Add(measure);
    measure.Expression = measureExpression;
    serv.Databases.Add(db);

    //deploy database to SSAS Server
    db.Update(UpdateOptions.ExpandFull);

    //process new model so it's available to query
    db.Model.RequestRefresh(Microsoft.AnalysisServices.Tabular.RefreshType.Full);
    db.Update(UpdateOptions.ExpandFull);
}

- userfl89

---

Microsoft.AnalysisServices.dll的版本是多少？我正在使用15.0版本。但是这个版本没有Connect()方法。 - Ashish-BeJovial