CODEWITHSUNDEEP
automationssas-tabular
Sananda Dutta
Sananda Dutta

Reputation: 21

Create Calculated tables in SSAS Tabular Model using Tabular Object Model Program Scripts

I am trying to create an SSAS Tabular database dynamically using the Tabular Object Model (TOM) C# scripts. I am able to create all the components of the tabular Model except for calculated tables and I am unable to find any solutions online.

Any insight on the same is highly appreciated. Thanks

Upvotes: 0

Views: 467

Answers (1)

userfl89
userfl89

Reputation: 4800

This can be done through executing a TMSL command that is the DDL for the calculated table using an OLE DB connection with the MSOLAP provider. First the command will need to be executed to create the calculated table. Next the table will need to be processed for it to be available for use, which is covered in the second part of the example below. The same connection string can be used for the both OLE DB and SSAS connection. Make sure properly escape any \ or " in the TMSL command, as well as set the Extended Properties and Provider properties accordingly in the connection string.

string connString = "Data Source=YourSSASInstance;Initial Catalog=YourTabularDatabase;Provider=MSOLAP.7;Integrated Security=SSPI;Extended Properties=Format=Tabular;";
     using (OleDbConnection oleDBConn =  new OleDbConnection(connString))    
       {
       OleDbCommand oleDBCmd = new OleDbCommand(commandWithTMSL);
       oleDBCmd.Connection = oleDBConn;
       oleDBConn.Open();
       oleDBCmd.ExecuteNonQuery();
       }
         using (Microsoft.AnalysisServices.Tabular.Server serv = new Microsoft.AnalysisServices.Tabular.Server())
         {
             serv.Connect(connString);
             Microsoft.AnalysisServices.Tabular.Database db = serv.Databases["YourTabularDatabase"];
             Model m = db.Model;
             m.Tables["NewCalculatedTable"].RequestRefresh(RefreshType.Full);
             m.SaveChanges();
         }

Upvotes: 0

Related Questions