Retrieve SSAS Properties Using Analysis Services Management Objects and SSIS

By: Daniel Calbimonte   |   Comments   |   Related: > Analysis Services Administration

Problem

I want to be able to query details about my SQL Server Analysis Services (SSAS) database and automate this process with a SQL Server Integration Services (SSIS) Package. How can this be done?

Solution

In this tip, we will talk about AMO (Analysis Services Management Objects) that you can use to get values of SSAS objects, process databases, cubes, dimensions, etc. You can also create objects, drop objects and more. We will use C# to get SSAS properties and store the values in a SQL Server table.

In this example, we will do the following:

1. Create a SQL Server relational table to store the SSAS properties
2. Create a SSIS package and variables to get SSAS values
3. In SSIS we will use the script task to write C# code
4. Add the Microsoft.AnalysisServices reference
5. Get the values and store in the variables using C#
6. Use the Execute SQL Task to store the values in the table

Requirements

1. SQL Server 2008 or later. In this example, we are using SQL Server 2016.
2. SSDT installed
3. An AdventureworksDW database
4. A SSAS Adventureworks database installed
5. SSIS installed

Building the SSIS Package

Create a SQL Server relational table to store SSAS properties

We will first create a table named ssas_properties in the SQL Server database engine:

CREATE TABLE [dbo].[ssas_properties](
   [id] [int] IDENTITY(1,1) NOT NULL,
   [Database name] [nchar](100) NULL,
   [CompatibilityLevel] [int] NULL,
   [LastProcessed] [datetime] NULL,
   [LastUpdate] [datetime] NULL,
   [State] [smallint] NULL,
 CONSTRAINT [PK_ssas_properties] PRIMARY KEY CLUSTERED 
(
   [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO	

In this table named ssas_properties we will store the following SSAS properties:

1. An id
2. The database name
3. The compatibility levels
4. Last Process date
5. Last Update date
6. The state of the database

In SSIS create variables to get SSAS values

In SSDT, create a new SSIS project.

In the SSIS project go to the SSIS menu and Variables and create the following variables. These variables will be used to store the SSAS properties.

In SSIS we will use the script task to write code

In the SSIS project drag and drop the Script Task to the design pane:

On the Script page, press the browse button in the ReadWriteVariables as shown below:

Select the following user variables and click OK:

Press the Edit Script button:

In the code, expand the namespace section:

In the namespaces add the following namespace. This namespace will help us to work with SSAS.

using Microsoft.AnalysisServices;			

Add the Microsoft.AnalysisServices reference

In Solution Explorer, right click on references and select Add Reference:

In Assemblies, select Extensions and check the Analysis Management Objects as shown below:

By default, the Analysis Services Management Objects are installed with SQL Server 2016, if it is not installed, you can download them here: Microsoft® SQL Server® 2016 Feature Pack.

In the Reference Manager press Browse:

Add the Microsoft.AnalysisServices.Core.dll in SQL Server, it is usually in a path like the following:

• C:\Program Files (x86)\Microsoft SQL Server\130\SDK\Assemblies\Microsoft.AnalysisServices.Core.DLL

Get the values and store in the variables using C#

In the Script Task go to the code section and add the following code:

public void Main()
{
    // TODO: Add your code here

    try
    {
        // TODO: Add your code here
        //Create a new Analysis Server
        Microsoft.AnalysisServices.Server DM_Server = new Server();

        //Create a new database instance
        Database AS_Database = new Database();

        //Specify connection properties 
        DM_Server.Connect("Data Source=local;Initial Catalog=Adventureworks");

        //Get the AS Database
        AS_Database = DM_Server.Databases["Adventureworks"];

        //Save the SSAS properties in SSIS variables
        Dts.Variables["User::databaseName"].Value = AS_Database.Name;
        Dts.Variables["User::compatibilityLevel"].Value = AS_Database.CompatibilityLevel;
        Dts.Variables["User::lastProcessed"].Value = AS_Database.LastProcessed;
        Dts.Variables["User::lastUpdate"].Value = AS_Database.LastUpdate;
        Dts.Variables["User::State"].Value = AS_Database.State;
    }
    catch (NullReferenceException e)
    {
        Dts.Events.FireError(18, "Null Error: ", e.ToString(), "", 0);
        Dts.TaskResult = (int)ScriptResults.Failure;
    }
    catch (Exception ex)
    {
        Dts.Events.FireError(18, "An error occurred: ", ex.ToString(), "", 0);
        Dts.TaskResult = (int)ScriptResults.Failure;
    }
}		

This code will get the database name, compatibility level, process date, last update date and database status and store in the variables that were created. These values will be stored later in a SQL Server table.

If there are errors, the catch functions will show the errors.

Use the Execute SQL Task to store the values in a table

Drag and drop the Execute SQL Task to the design pane and connect the script task with the Execute SQL Task as shown below.

In the connection specify the SQL Server name and the database name. In this example, we are using the local host as the SQL Server and Adventureworks as the database name. Adventureworks is the database where we created the table.  In the SQL Statement, we will write an insert statement that will insert the SSIS variables into a SQL Server table named ssas_properties:

INSERT INTO [dbo].[ssas_properties] VALUES (?,?,?,?,?)			

We are inserting into the table created at the beginning of the tip with the values of the 5 SSIS variables that were created. To do this, we need to use 5 "?", one for each of the 5 parameters:

On the Parameter Mapping page, add the 5 variables with the direction, data type and parameter name as specified below:

Now that the package is complete, we can run the package:

.

In SSMS, or any tool of your preference, run the following query against the ssas_properties table:

SELECT TOP (1000) 
   [id]
  ,[Database name]
  ,[CompatibilityLevel]
  ,[LastProcessed]
  ,[LastUpdate]
  ,[State]
FROM [Adventureworks].[dbo].[ssas_properties]			

The following values will be displayed:

In this tip, we learned how to get SSAS properties using the SSIS Script Task with C# code and we stored the SSAS properties in SSIS variables and then inserted the variable values into a table named ssas.properties.

Next Steps

• Expand the process to pull in a list of servers and databases to dynamically connect to get the status for all SSAS databases in your environment.
• For more information about working with SSAS AMO and C#, refer to these links:
  • Create SQL Server Analysis Services Partitions using AMO
  • Microsoft.AnalysisServices Namespace
  • Microsoft.AnalysisServices.Core Namespace

About the author

Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips