Analysis Services

https://learn.microsoft.com/en-us/analysis-services/
Admin
Analysis Services is a data modeling and analytics platform available in Azure, Power BI, and SQL...
Tokens:1,215,340
Snippets:11,717
Trust Score:10
Update:5 days ago
Show doc for...
Tokens:
Raw
### Install specific components via command line

Source: https://learn.microsoft.com/en-us/analysis-services/instances/install-windows/install-or-uninstall-the-power-pivot-for-sharepoint-add-in-sharepoint-2016

Install specific items by defining the ADDLOCAL property. This example installs all components except the configuration tool.

```bash
Msiexec /i spPowerPivot16.msi AGREETOLICENSE="yes" ADDLOCAL=" SQL_OLAPDM,SQL_ADOMD,SQL_AMO,SQLAS_SP_Common"
```

--------------------------------

### Example 1 - Delete a database

Source: https://learn.microsoft.com/en-us/analysis-services/tmsl/delete-command-tmsl

Example demonstrating how to delete a database.

```JSON
{
  "delete": {  
    "object": {  
      "database": "AdventureWorksDW2016"  
    }  
  }  
}
```

--------------------------------

### Install Analysis Services in Tabular mode via command line

Source: https://learn.microsoft.com/en-us/analysis-services/instances/install-windows/install-analysis-services

Use the Setup.exe command with the ASSERVERMODE parameter set to TABULAR to install Analysis Services. Ensure the INSTANCENAME is under 17 characters and replace all placeholder account values with valid credentials.

```bash
Setup.exe /q /IAcceptSQLServerLicenseTerms /ACTION=install /FEATURES=AS /ASSERVERMODE=TABULAR /INSTANCENAME=ASTabular /INDICATEPROGRESS /ASSVCACCOUNT=<DomainName\UserName> /ASSVCPASSWORD=<StrongPassword> /ASSYSADMINACCOUNTS=<DomainName\UserName>
```

--------------------------------

### Get an item from a database

Source: https://learn.microsoft.com/en-us/analysis-services/tom/list-existing-databases-on-a-tabular-server-analysis-services-amo-tom

This snippet shows how to retrieve a specific database by name and access a column within a table, including an example of checking an object type.

```csharp
var db = srv.Databases.GetByName("abc"); 

Column c1 = db.Model.Tables["foo"].Columns["bar"]; 
if (c1.ObjectType == ObjectType.Column) // always true 

MetadataObject obj; 

switch(obj.ObjectType) 
{ 
 case ObjectType.Table: 
  var t1 = (Table)obj; 
  break; 
} 
```

--------------------------------

### Create a Table, Column, and Partition using C#

Source: https://learn.microsoft.com/en-us/analysis-services/tom/create-tables-partitions-and-columns-in-a-tabular-model

This example connects to a local Analysis Services instance, creates a new database and model, adds a SQL Server data source, and defines a table with a partition that queries data from a SQL view.

```csharp
using System; 
using Microsoft.AnalysisServices; 
using Microsoft.AnalysisServices.Tabular; 
 
namespace TOMSamples 
{ 
    class Program 
    { 
        static void Main(string[] args) 
        { 
            // 
            // Connect to the local default instance of Analysis Services 
            // 
            string ConnectionString = "DataSource=localhost"; 
 
            // 
            // The using syntax ensures the correct use of the 
            // Microsoft.AnalysisServices.Tabular.Server object. 
            // 
            using (Server server = new Server()) 
            { 
                server.Connect(ConnectionString); 
 
                // 
                // Generate a new database name and use GetNewName 
                // to ensure the database name is unique. 
                // 
                string newDatabaseName = 
                    server.Databases.GetNewName("Database with a Table Definition"); 
 
                // 
                // Instantiate a new  
                // Microsoft.AnalysisServices.Tabular.Database object. 
                // 
                var dbWithTable = new Database() 
                { 
                    Name = newDatabaseName, 
                    ID = newDatabaseName, 
                    CompatibilityLevel = 1200, 
                    StorageEngineUsed = StorageEngineUsed.TabularMetadata, 
                }; 
 
                // 
                // Add a Microsoft.AnalysisServices.Tabular.Model object to the 
                // database, which acts as a root for all other Tabular metadata objects. 
                // 
                dbWithTable.Model = new Model() 
                { 
                    Name = "Tabular Data Model", 
                    Description = "A Tabular data model at the 1200 compatibility level." 
                }; 
 
                // 
                // Add a Microsoft.AnalysisServices.Tabular.ProviderDataSource object 
                // to the data Model object created in the previous step. The connection 
                // string of the data source object in this example  
                // points to an instance of the AdventureWorks2014 SQL Server database. 
                // 
                string dataSourceName = "SQL Server Data Source Example"; 
                dbWithTable.Model.DataSources.Add(new ProviderDataSource() 
                { 
                    Name = dataSourceName, 
                    Description = "A data source definition that uses explicit Windows credentials for authentication against SQL Server.", 
                    ConnectionString = "Provider=SQLNCLI11;Data Source=localhost;Initial Catalog=AdventureWorks2014;Integrated Security=SSPI;Persist Security Info=false", 
                    ImpersonationMode = Microsoft.AnalysisServices.Tabular.ImpersonationMode.ImpersonateAccount, 
                    Account = @".\Administrator", 
                    Password = "P@ssw0rd", 
                }); 
 
                //  
                // Add a table called Individual Customers to the data model 
                // with a partition that points to a [Sales].[vIndividualCustomer] view 
                // in the underlying data source. 
                // 
                dbWithTable.Model.Tables.Add(new Table() 
                { 
                    Name = dbWithTable.Model.Tables.GetNewName("Individual Customers"), 
                    Description = "Individual customers (names and email addresses) that purchase Adventure Works Cycles products online.", 
                    Partitions = { 
                        // 
                        // Create a single partition with a QueryPartitionSource for a query 
                        // that imports all customer rows from the underlying data source. 
                        // 
                        new Partition() { 
                            Name = "All Customers", 
                            Source = new QueryPartitionSource() { 
                                DataSource = dbWithTable.Model.DataSources[dataSourceName], 
                                Query = @"SELECT   [FirstName] 
                                                    ,[MiddleName] 
                                                    ,[LastName] 
                                                    ,[PhoneNumber]  
                                                    ,[EmailAddress] 

```

--------------------------------

### GET /refreshes/<refreshId>

Source: https://learn.microsoft.com/en-us/analysis-services/azure-analysis-services/analysis-services-async-refresh

Checks the status of a specific refresh operation using its refresh ID. Returns details such as start time, end time, type, status, and per-object status.

```APIDOC
## GET /refreshes/<refreshId>

### Description
Checks the status of a refresh operation using the refresh ID. If the operation is in progress, `inProgress` is returned in the status field.

### Method
GET

### Endpoint
/refreshes/<refreshId>

### Parameters
#### Path Parameters
- **refreshId** (string) - Required - The unique identifier of the refresh operation.

### Response
#### Success Response (200)
- **startTime** (string) - The start time of the refresh operation.
- **endTime** (string) - The end time of the refresh operation.
- **type** (string) - The type of refresh (e.g., full).
- **status** (string) - The status of the refresh (e.g., succeeded, inProgress).
- **currentRefreshType** (string) - The current refresh type.
- **objects** (array) - List of objects being refreshed.
  - **table** (string) - The table name.
  - **partition** (string) - The partition name.
  - **status** (string) - The status of the refresh for that object.

#### Response Example
{
  "startTime": "2017-12-07T02:06:57.1838734Z",
  "endTime": "2017-12-07T02:07:00.4929675Z",
  "type": "full",
  "status": "succeeded",
  "currentRefreshType": "full",
  "objects": [
    {
      "table": "DimCustomer",
      "partition": "DimCustomer",
      "status": "succeeded"
    },
    {
      "table": "DimDate",
      "partition": "DimDate",
      "status": "succeeded"
    }
  ]
}
```

--------------------------------

### Run Analysis Services Deployment Wizard at the command prompt

Source: https://learn.microsoft.com/en-us/analysis-services/deployment/deploy-model-solutions-using-the-deployment-wizard

Navigate to the installation directory and execute the deployment utility with the desired switches.

```bash
C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE
```

```bash
Microsoft.AnalysisServices.Deployment.exe
```

--------------------------------

### Example Properties Element with PropertyList

Source: https://learn.microsoft.com/en-us/analysis-services/xmla/xml-elements-properties/properties-element-xmla

Example of a Properties element specifying DataSourceInfo, Catalog, and Format properties. This example uses Provider=MSOLAP, Data Source=local, Catalog Foodmart 2000, and Format Multidimensional.

```XML
<Properties>  
   <PropertyList>  
      <DataSourceInfo>  
         Provider=MSOLAP;Data Source=local;  
      </DataSourceInfo>  
      <Catalog>  
         Foodmart 2000  
      </Catalog>  
      <Format>  
         Multidimensional  
      </Format>  
   </PropertyList>  
</Properties>  
```

--------------------------------

### Create Installation Log with Verbose Logging

Source: https://learn.microsoft.com/en-us/analysis-services/instances/install-windows/install-or-uninstall-the-power-pivot-for-sharepoint-add-in-sharepoint-2013

Uses the /L v switch to generate a verbose installation log file at the specified path.

```bash
Msiexec.exe /i SpPowerPivot.msi /L v c:\test\Install_Log.txt
```

--------------------------------

### Example 2 - Delete a connection

Source: https://learn.microsoft.com/en-us/analysis-services/tmsl/delete-command-tmsl

Example demonstrating how to delete a data source connection.

```JSON
{
  "delete": {  
    "object": {  
      "database": "AdventureWorksDW2016",  
      "dataSource": "SqlServer localhost AdventureworksDW2016"  
    }  
  }  
}
```

--------------------------------

### Backup database example

Source: https://learn.microsoft.com/en-us/analysis-services/tmsl/backup-command-tmsl

An example of the Backup command used to back up a database to the default backup folder with a password.

```json
{   
   "backup": {   
      "database":"AS_AdventureWorksDW2014",  
      "file":"AS_AdventureWorksDW2014.abf",  
      "password":"secret"  
   }  
}  

```

--------------------------------

### PropertyList Example

Source: https://learn.microsoft.com/en-us/analysis-services/xmla/xml-elements-properties/propertylist-element-xmla

An example showing the PropertyList element containing DataSourceInfo, Catalog, and Format properties.

```XML
<Properties>  
   <PropertyList>  
      <DataSourceInfo>  
         Provider=MSOLAP;Data Source=local;  
      </DataSourceInfo>  
      <Catalog>  
         Foodmart 2000  
      </Catalog>  
      <Format>  
         Multidimensional  
      </Format>  
   </PropertyList>  
</Properties>
```

--------------------------------

### List existing databases on a server

Source: https://learn.microsoft.com/en-us/analysis-services/tom/list-existing-databases-on-a-tabular-server-analysis-services-amo-tom

This example demonstrates connecting to a local Analysis Services instance and iterating through the Server.Databases collection to print properties for each database.

```csharp
using System; using Microsoft.AnalysisServices; 
using Microsoft.AnalysisServices.Tabular; 

 
namespace TOMSamples 
{ 
    class Program 
    { 
        static void Main(string[] args) 
        { 
            // 
            // Connect to the local default instance of Analysis Services 
            // 
            string ConnectionString = "DataSource=localhost"; 

             // 
            // The using syntax ensures the correct use of the 
            // Microsoft.AnalysisServices.Tabular.Server object. 
            // 
            using (Server server = new Server()) 
            { 
                server.Connect(ConnectionString); 

                 // 
                // List common properties for the databases on the server. 
                // 
                foreach (Database db in server.Databases) 
                { 
                    Console.WriteLine("Properties for database {0}:", db.Name); 
                    Console.ForegroundColor = ConsoleColor.Yellow; 
                    Console.WriteLine("Database ID:\t\t\t{0}", db.ID); 
                    Console.WriteLine("Database compatibility level:\t{0}", db.CompatibilityLevel); 
                    Console.WriteLine("Database collation:\t\t{0}", db.Collation); 
                    Console.WriteLine("Database created timestamp:\t{0}", db.CreatedTimestamp); 
                    Console.WriteLine("Database language ID:\t\t{0}", db.Language); 
                    Console.WriteLine("Database model type:\t\t{0}", db.ModelType); 
                    Console.WriteLine("Database state:\t\t\t{0}", db.State); 
                    Console.ResetColor(); 
                    Console.WriteLine(); 
                } 
            } 
            Console.WriteLine("Press Enter to close this console window."); 
            Console.ReadLine(); 
        } 
    } 
} 
```
Tokens:
Raw