This article talks about automating the processing of the Analysis Services database in SQL Server. Automation is one of the most important tasks managed by database administrators or DevOps engineers.

Additionally, we’ll get an overview of Analysis Services databases and how they differ from SQL Server databases.

This article also highlights the importance of automating tasks in SQL Server to minimize the errors and maximize the productivity, particularly while working with complex scenarios like data warehouse business intelligence solutions.

About Analysis Services Databases

Let’s take a look at Analysis Services databases so that we can understand the importance of automating its processing in the context of data warehouse business intelligence solutions and complex data analysis scenarios.

What is an Analysis Services Database?

An Analysis Services database is a highly optimized database for analysis and reporting which is often prepared and updated as a part of data warehouse business intelligence solutions.

How Analysis Services Databases are different from SQL Server Databases

An Analysis Services database is different from a SQL Server database because it works on a different language (DAX/MDX) and offers many built-in business intelligence functions for analysis and reporting along with features like data mining and time intelligence.

How Many Types of Analysis Services Databases are there

The two main types of Analysis Services databases are as follows:

1. Multi-dimensional mode (Cube)
2. Tabular mode (Data Model)

So an Analysis Services database is either a deployed Cube or a deployed Data Model. But in both forms it serves requests ranging from simple to complex data analysis scenarios and allows reports to be built on top of it.

What is SQL Server Analysis Services (SSAS)

SQL Server Analysis Services also known as SSAS is a Microsoft server instance which allows hosting Analysis Services databases.

What is an Analysis Services Database in a Data Warehouse

An analysis service database in the context of data warehouse is often a final product which can be exposed to business users for self-service reporting and real-time analysis.

In simple words, once the data undergoing different transformations and stages of a data warehouse reaches the Analysis Services database, it is considered ready for analysis and reporting.

What does Processing an Analysis Services Database mean?

Processing an Analysis Services database means to check the sources for new data load it to the database. Analysis Services databases need to be updated with time as the source(s) they pull data from frequently get new data.

Why Should We Automate the Processing of Analysis Services Databases

The next important question one can ask is this: why do we need to automate the processing of an Analysis Services database if we can easily run it manually?

The simple answer is that we need to ensure that the Analysis Services database is up to date without manual intervention, thus saving both time and effort – particularly in the context of a data warehouse business intelligence solution when the project is deployed to a live server.

Automating the Analysis Services Database Processing

Now, let’s go through the main steps of automating the processing of an Azure Analysis Services database.

Pre-requisites

This article assumes that the readers are familiar with the basic concepts of data warehouse business intelligence solutions, including deployment of data models to a SQL Analysis server or Azure Analysis Services.

Since creating and managing SQL databases and analysis service projects is usually a developer’s job, we are going to be focused on the processing and automation of the processing of Analysis Services databases or SSAS databases from the perspective of a DBA or a DevOps engineer.

This article assumes the following:

1. A sample database called SQLDevBlogV5 source has already been setup
2. A tabular data model using an Analysis Services Project has already been created
3. An Analysis Services database called SQLDevBlogTabularProject based on the tabular model has already been deployed

Please note that the source database and an Analysis Services database mentioned above are for reference purposes only, so you can change these names as per your requirements.

You can now jump directly to the next heading. However, if you want to cover both the developer and DBA perspective, you can perform the above mentioned steps using the following sample database:

-- Create the sample database (SQLDevBlogV5)
CREATE DATABASE SQLDevBlogV5;
GO


USE SQLDevBlogV5;

-- (1) Create the Article table in the sample database
CREATE TABLE Article (
  ArticleId INT PRIMARY KEY IDENTITY (1, 1)
 ,Category	VARCHAR(50)
 ,Author VARCHAR(50)
 ,Title VARCHAR(150)
 ,Published DATETIME2
 ,Notes VARCHAR(400)  
)

GO

-- (2) Populating the Article table
SET IDENTITY_INSERT [dbo].[Article] ON
INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (1, N'Development', N'Atif', N'Introduction to SQL Server Analysis Services (SSAS)', N'2019-01-01 00:00:00', NULL)
INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (2, N'Testing', N'Peter', N'Processing SSAS database', N'2019-01-10 00:00:00', NULL)
INSERT INTO [dbo].[Article] ([ArticleId], [Category], [Author], [Title], [Published], [Notes]) VALUES (3, N'DLM', N'Sadaf', N'Automating Analysis Services Database Processing', N'2019-01-20 00:00:00', NULL)
SET IDENTITY_INSERT [dbo].[Article] OFF

Next, create a new Analysis services tabular project, import the data source into the model, and deploy the model to the SSAS server:

Compatibility Level

In this example, we are using SQL Server 2014 / SQL Server 2012 SP1 (1103) compatibility level for the data model. However, you can specify a different compatibility level according to your requirements.

Connect to Analysis Services

Open SSMS (SQL Server Management Studio) and connect to an Analysis server by entering your credentials:

Go to Analysis Services Database (deployed)

Once the connection is established, expand the Databases node in the Object Explorer and then expand the deployed Analysis Services database node by clicking the plus icon next to it:

Process the Analysis Services Database

Right-click the Analysis Services database and click Process Database:

Keep the default processing mode and click OK:

The Analysis Services database was successfully processed:

Script the Database Processing

Next, we need to get the script that is used to process the Analysis Services database.

One of the ways to automate the processing of an Analysis Services database is to script the task which does the processing and run it as a SQL job (step) in SQL Server.

Click Process Database again, but don’t press OK this time.

In the Script dropdown menu on the top left side of the window, click Script Action to New Query Window as shown below:

The processing script is going to be opened up in a new window called XMLA query window:

Run the Process Database script

Press F5 to run the XMLA script which begins processing the Analysis Services database:

SQL Server Agent Check

Now connect to the SQL server database engine and make sure SQL Server agent is running.

Set up SQL Server Agent Access

Another important thing is to make sure that the account which is running the SQL Server Agent has access to the Analysis Services database.

In our case, NT Service\SQLAgent account is running SQL Server agent – it means that this account must have permissions to process the Analysis Services database.

Connect to the Analysis services tabular instance and go to the desired Analysis Services database. Then, create a new Role Database Runner by adding NT Service\SQLAgent account and giving it the Process Database permission:

Please note that one of the standard ways to achieve this is to create a proxy and map the credentials to that proxy. However, the details of this process are beyond the scope of this article.

Copy XMLA Script to Process the Database

Copy the XMLA script from the SQL Analysis Services XMLA Query Window we used to process the Analysis Services database.

Create a Job Step

Go back to SQL Server Agent and create a new job step as Process Analysis Services Database SQLDevBlogV5.

Create a new step by providing the following information and Click OK:

Please remember that you have to type in the name of your server and database and then paste the script that you copied from the XMLA Query Editor.

Start the Job

Right-click the job and click Start Job at Step…

The job step is going to run successfully as shown below:

Schedule the Job to Automate Processing

Next, schedule the job to automate the processing of your Analysis Services database.

Congratulations! You have successfully automated Analysis Services database processing, saving the time and effort spent by a DBA or a DevOps engineer to process the database each time when data needs to be updated from the source.

Things to do

Now that you can automate the processing of an Analysis Services database, please try the following things to improve your skills further:

1. Schedule the Analysis Services database processing job mentioned in this article to run daily and add more data to the sample with time
2. Implement the complete solution by doing the following things:
   1. Setup a sample database
   2. Create a Tabular Data Model
   3. Import a sample database into the Tabular Data Model
   4. Deploy the Tabular Data Model to create an Analysis Services database
   5. Automate the processing of the Analysis Services database using SQL Agent
3. Connect to the Analysis Services database through Excel to view the data after the Analysis Services database is updated automatically using the SQL job