Dynamic XMLA using T-SQL for SSAS

Problem

Sometimes it is necessary create SSAS partitions for multidimensional databases using dynamic values like the current date and time. Sometimes we need to create SSAS cube partitions daily using the current date. Usually these automation tasks in SSAS can be solved with AMO (Analysis Management Objects), but AMO requires programming skills that usually DBAs do not have (or do not like). This tip is for SSAS Administrators who do not like to program with AMO, but they need to automate their admin tasks with T-SQL.

Solution

In this sample we are going to create a partition according to the current date. For example, if today is November 2, 2012 the T-SQL script will create a partition named Internet_Sales_2012. If it were the year 2013, the partition name would be Internet_Sales_2013. The idea is to create partitions automatically using XMLA and variables.

The scripts used in this example can be downloaded here.

Requirements

For this sample, we are going to download and install the multidimensional database Adventureworks Multidimensional Model 2012 (you can apply this tip to SQL 2008 or 2005 as well).

Note: on my 64 bit machine it was necessary to install the Oledb Provider for 64 bit for Analysis Services: X64 Package (SQL_AS_OLEDB.msi)

Getting Started

In this tip, we are going to create a linked server in SQL Server to the Analysis Services database. After creating the linked server we are going to run a XMLA script with variables using T-SQL and XMLA. For this purpose, make sure that you have the above requirements installed.

Create a Linked Server

1. Open SQL Server Management Studio
2. Go to Server Objects > Linked Server right click and select New Linked Server…
   

3. In the New Linked Server Window, enter SSAS as the linked server name and select the Microsoft OLE DB Provider for Analysis Services 11 as the provider. In the DataSource enter the name of the Server with Analysis Services (in my case I used localhost).
   

4. Make sure RPC and RPC Out are set to True in order to communicate between SQL Server and Analysis Services
   
5. Another option instead of going through steps 2 to 4 is to run T-SQL like below to create the Linked Server to SSAS.

   USE [master]
   GO
   /****** Object:  LinkedServer [SSAS]    Script Date: 14/09/2012 04:28:40 p.m. ******/
   EXEC master.dbo.sp_addlinkedserver @server = N'SSAS', @srvproduct=N'', @provider=N'MSOLAP', @datasrc=N'localhost'
    /* For security reasons the linked server remote logins password is changed with ######## */
   EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SSAS',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
   GO
   EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'collation compatible', @optvalue=N'false'
   GO
   EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'data access', @optvalue=N'true'
   GO
   EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'dist', @optvalue=N'false'
   GO
   EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'pub', @optvalue=N'false'
   GO
   EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'rpc', @optvalue=N'true'
   GO
   EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'rpc out', @optvalue=N'true'
   GO
   EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'sub', @optvalue=N'false'
   GO
   EXer.dbo.sp_serveroption @server=N'SSAS', @optname=N'connect timeout', @optvalue=N'0'
   GO
   EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'collation name', @optvalue=null
   GO
   EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'lazy schema validation', @optvalue=N'false'
   GO
   EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'query timeout', @optvalue=N'0'
   GO
   EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'use remote collation', @optvalue=N'true'
   GO
   EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'remote proc transaction promotion', @optvalue=N'true'
   GO
   

Create Dynamic XMLA

In a transactional database, to create objects you use T-SQL. In multidimensional databases you use XMLA. XMLA is a XML extension used in Analysis Services to create objects. In this example we are going to generate an XMLA script and then modify it to accept dynamic parameters.

1. In SQL Server Management Studio, connect to the Analysis Services server that you used to create the Linked Server.
   

2. Go to Databases > AdventureWorksDW2012Multidimensional-EE > Cubes > Adventure Works > Measure Groups > Internet Sales > Partitions > Internet_Sales_2005
   
3. Right click on the partition and select Script Partition as > CREATE To > New Query Editor Window. This option generates the code to create the partition Internet_Sales_2005:
   

   The code generated is as follows and you can see where I highlighted the sections we want to make dynamic:

   

4. The idea is to create 3 things dynamically in the XMLA: the Name, ID and the Date in the query. We will create a T-SQL script that detects the current date and creates a partition with the current date. Below is the beginning of the code.  You can download the entire code sample here.

   DECLARE @myXMLA nvarchar(max), @value nvarchar(30), @date varchar(8), @year nvarchar(4)

SET @date=FORMAT (getdate(), ‘yyyyMMdd’)
SET @year=FORMAT (getdate(), ‘yyyy’)
SELECT @value=’Internet_Sales_’+FORMAT (getdate(), ‘yyyy’)

SET @myXMLA =N’

The code above updates the variable @date with the current date in the format yyyyMMdd. The @year variable stores the current year.

• Now, in the @myXMLA variable we are going to concatenate the @year and @date variables with the XMLA created in step 3:
  

• And finally, execute the code in the linked server:

  Exec (@myXMLA) At SSAS;

• That’s it. You can go to the partitions in SSMS to verify that the new partition was created:
  
• The code now generates a partition according to the current date. In this example we created a partition according to the current year. We used T-SQL to create the variables and get the current year. Then we concatenate the XMLA code with the variables to create partitions dynamically.P>
  

  Downloads

  In this section you can download the following components:

  1. The script to create a linked server to ssas: ssas linked server.sql
  2. The script to create the partition Internet_Sales_2005: Partition2005.xmla
  3. The script to create a partition with dynamic variables: Partition2012.sql

  Next Steps

  • Linked Servers to SSAS let you combine T-SQL with XMLA giving you a powerful combination of tools. For more information, review these links:
    • XMLA reference: http://msdn.microsoft.com/en-us/library/ms186604(v=110).aspx
    • Using dynamic values in XMLA: http://www.sqlsoldier.com/wp/sqlserver/usingdynamicvaluesinxmla
    • Creating a linked server for SSAS: http://sqlblog.com/blogs/stacia_misner/archive/2010/11/30/31193.aspx