Dynamic XMLA using T-SQL for SQL Server Analysis Services

By:   |   Comments (22)   |   Related: > Analysis Services Development


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.

Partition created

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): http://msftdbprodsamples.codeplex.com/downloads/get/258486

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...
     
    Add 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).
     
    Create SSAS linked server

  4. Make sure RPC and RPC Out are set to True in order to communicate between SQL Server and Analysis Services
     
    linked server configuration

  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.
     
    connect to SSAS

  2. Go to Databases > AdventureWorksDW2012Multidimensional-EE > Cubes > Adventure Works > Measure Groups > Internet Sales > Partitions > Internet_Sales_2005
     
    Partition created
  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:
     
    generate xmla

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

    XMLA to create partitions

  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.

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

  6. And finally, execute the code in the linked server:
      
    Exec (@myXMLA) At SSAS;
  7. That's it. You can go to the partitions in SSMS to verify that the new partition was created:
     
    Partition 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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Daniel Calbimonte 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


Comments For This Article




Friday, April 28, 2017 - 4:54:57 AM - Seija Back To Top (55266)

 Hi,

I'm trying to fetch metadata from SSAS 2012 multidimensional cubes via T-sql (SqlServer 2012 database engine) - but unfortunately not succeeded.
Linked server works fine if I send MDX script, but it doesn't work with XMLA script.

Here is my code and the error message:

DECLARE @xmla     nvarchar(MAX); 
 
SET @xmla = N'
 DISCOVER_XML_METADATA
 
  
   ktmt_BOP16_MACRO
   BOP_ACA
  
 
 
 ' ;
 
 -- PRINT      @xmla
 
  EXEC  (@xmla) AT SSAS; 

  --  --> RESULT:
  OLE DB provider "MSOLAP" for linked server "SSAS" returned message "The Discover element at line 1, column 336 (namespace urn:schemas-microsoft-com:xml-analysis)
  cannot appear under Envelope/Body/Execute/Command.".
  Msg 7215, Level 17, State 1, Line 18
  Could not execute statement on remote server 'SSAS'.


I want to add parametres and execute script in stored procedure.
Have you any idea what is missing /wrong?  This 'discover' -xmla script works fine in AnalysisServices.

Thank you!
Regards,
Seija

 

 

 

Thursday, December 18, 2014 - 2:46:21 PM - Daniel Back To Top (35670)

No, usually we create SSIS tasks to import unstructed data to a structured data in SSAS.

 

Thursday, December 18, 2014 - 1:11:43 PM - mital Back To Top (35666)

Thats right. But for the storage of unstructured or semistructured (multimedia) data they use xml warehouse concept and i dnt knw from where can i build xml based cube or xml warehouse. Is there any way to build xml based datawarehouse?

Thursday, December 18, 2014 - 8:34:17 AM - Daniel Back To Top (35661)

Internally the SSAS is based in XML files. 

If you check the physical files you will find a lot of XML files.

Thursday, December 18, 2014 - 5:41:48 AM - mital Back To Top (35658)

In many research papers, i have seen the concept of xml warehouse.

instead of using tables they are using xml files.

for fact they are having separate xml file, for each dimensions they are having different xml files and for cube also they are having cube xml file. 

is it possible to create xml based warehouse using sql server?

Monday, July 7, 2014 - 12:27:44 PM - Syed Back To Top (32576)

Is it possible that I only pocess todays Data that suppose to run on every 15 minutes.

 

And when it processed then merge it with previous data.

 

There is no updation and deletion happening in Dimensions ... All are new add record in dimensions and Fact...

 

Please advise as my cube is so huge

 

Thanks

Friday, May 9, 2014 - 5:20:59 AM - Sega Back To Top (30695)

Hi Daniel,

Hope you can share how to validate if a cube partition already exists.

Thanks,

Sega

Tuesday, April 22, 2014 - 10:10:49 AM - Chandra Back To Top (30470)

 

Hi I am trying to create multiple partitions using batch query in the link server month wise same approach as above. I am able to create two partitions but not able to create more then two. could you please advise if you have tried.

 

Thanks,

chandra

Monday, October 14, 2013 - 5:26:25 AM - Asaduzzaman Back To Top (27141)

ok I have done all of them except "How can I overwrite or delete the cube if it exist"?

if you please give me the code, which I will have to add in the existing t-sql code?

Monday, October 14, 2013 - 3:15:28 AM - Asaduzzaman Back To Top (27140)

Hello again,

I need some information, if I want to create the cube as,

1. I have data in different dates, like 2010-10-23, 2012-12-06, 2013-02-13. So When I will run the code then I will have 3 cubes according to those days.

2. Like today is 2013-10-14, so when I will run the code for several times, it will overwrite the cube.

3. Its creating cube according to current date. But if I want to create the cube according to my table data date, then how can I do that? I have the dates like above Q1.

 

If you please give me the solution?

 

Regards,

Sunday, October 13, 2013 - 9:37:40 AM - Asaduzzaman Back To Top (27139)

It works! I missed the line

Set @myXMLA =N''

 

by the way.. thnx a lot for the nice tutorial :)

Saturday, October 12, 2013 - 8:32:06 AM - Asaduzzaman Back To Top (27131)

I have database and my MS Analysis Server is connected. I am doing in the save way you wrote but getting error on the first line last line bellow. N.B: I am trying to run the code to create partition, if I am not worng!

 

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='Data_new'+FORMAT (getdate(), 'yyyy')

 

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

    <ParentObject>

        <DatabaseID>my_Cube_Performance_DB</DatabaseID>

        <CubeID>my_Cube_Performance</CubeID>

        <MeasureGroupID>Data</MeasureGroupID>

    </ParentObject>

    <ObjectDefinition>

        <Partition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400">

            <ID>'+@value+'</ID>

            <Name>'+@value+'</Name>

            <Source xsi:type="QueryBinding">

                <DataSourceID>MY_DS</DataSourceID>

                <QueryDefinition>SELECT [dbo].[Data].[id],[dbo].[Data].[CreateDate],[dbo].[Data].[ChangeDate],[dbo].[Data].[HotDealOnInvoice],[dbo].[Data].[CampDiscOnInvoice],[dbo].[Data].[TaxOnInvoice],[dbo].[Data].[InvoiceSale],[dbo].[Data].[NetSale],[dbo].[Data].[SalesMargin],[dbo].[Data].[Tag],[dbo].[Data].[Brand1ID]

FROM [dbo].[Data]

WHERE Changedate = '''+@date+''' </QueryDefinition>

            </Source>

            <StorageMode>Molap</StorageMode>

            <ProcessingMode>Regular</ProcessingMode>

            <ProactiveCaching>

                <SilenceInterval>-PT1S</SilenceInterval>

                <Latency>-PT1S</Latency>

                <SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>

                <ForceRebuildInterval>-PT1S</ForceRebuildInterval>

                <Source xsi:type="ProactiveCachingInheritedBinding" />

            </ProactiveCaching>

            <EstimatedRows>2424800</EstimatedRows>

            <AggregationDesignID>AggregationDesign</AggregationDesignID>

        </Partition>

    </ObjectDefinition>

</Create>

Exec (@myXMLA) At SSAS;

 

ERROR MESSAGE:

Msg 102, Level 15, State 1, Line 10

Incorrect syntax near '<'.

Msg 343, Level 15, State 1, Line 10

Unknown object type 'xmlns' used in a CREATE, DROP, or ALTER statement.

Msg 132, Level 15, State 1, Line 17

The label 'xmlns' has already been declared. Label names must be unique within a query batch or stored procedure.

Msg 132, Level 15, State 1, Line 17

The label 'xmlns' has already been declared. Label names must be unique within a query batch or stored procedure.

Msg 132, Level 15, State 1, Line 17

The label 'xmlns' has already been declared. Label names must be unique within a query batch or stored procedure.

Msg 132, Level 15, State 1, Line 17

The label 'xmlns' has already been declared. Label names must be unique within a query batch or stored procedure.

Msg 132, Level 15, State 1, Line 17

The label 'xmlns' has already been declared. Label names must be unique within a query batch or stored procedure.

Msg 132, Level 15, State 1, Line 17

The label 'xmlns' has already been declared. Label names must be unique within a query batch or stored procedure.

Msg 132, Level 15, State 1, Line 17

The label 'xmlns' has already been declared. Label names must be unique within a query batch or stored procedure.

Msg 132, Level 15, State 1, Line 17

The label 'xmlns' has already been declared. Label names must be unique within a query batch or stored procedure.

Msg 132, Level 15, State 1, Line 17

The label 'xmlns' has already been declared. Label names must be unique within a query batch or stored procedure.

Msg 132, Level 15, State 1, Line 17

The label 'xmlns' has already been declared. Label names must be unique within a query batch or stored procedure.

Msg 102, Level 15, State 1, Line 24

Incorrect syntax near '<'.

Msg 132, Level 15, State 1, Line 33

The label 'xsi' has already been declared. Label names must be unique within a query batch or stored procedure.

 

Friday, October 11, 2013 - 5:24:16 PM - Daniel Back To Top (27127)

Extension .sql...please send us the error.

You will need the linked server to SSAS and make sure that your linked server works and you call that linked server in your queries.

Friday, October 11, 2013 - 3:40:04 PM - Asaduzzaman Back To Top (27126)

I saved in .sql and also in XMLA extension, but getting erro! 

So if you please let me know the extension ?

Friday, October 11, 2013 - 10:33:10 AM - Daniel Back To Top (27122)

It is T-SQL.

It is in your SQL Server Database Engine.

Friday, October 11, 2013 - 9:47:59 AM - Asaduzzaman Back To Top (27120)

Hi,

Thanks for the nice example!

Can you please let me know, where sould I add these lineof code?

 

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'
Exec (@myXMLA) At SSAS;
If its in XMLA file then, I am getting error!
If its in differengt file and have to call the XMLA to execute, then how can I do that?
in which format I should save the file? Same as before XMLA ?

Sorry for lots of questions.

Regards,
 

Friday, April 12, 2013 - 10:51:53 AM - Elena Back To Top (23329)

Thank you for an excellent article and very detailed accurate step descriptions. It helped me to  create code to automate SSAS tabular databases backups in less than 10 minutes.

Monday, April 8, 2013 - 9:27:28 PM - Daniel Calbimonte Back To Top (23245)

I used the Adventureworks database as datasource. I do not usually work with the templates.

In this sample I have downloaded the adventureworks database.

Sunday, April 7, 2013 - 9:10:32 AM - Olawale Back To Top (23221)

Thank You so muchfor the reply, really helps alot.............I  do still have one more questionthough. Please is there a way to load data into cube which is developed without a datasource but based on a particular template? For instance, if i build a cube based on the Enterprised edition of adventureworks template, how to i load the data into the cube and dimension...

Thanks once again

 

Regards

Olawale

Wednesday, April 3, 2013 - 8:59:02 PM - Daniel Calbimonte Back To Top (23149)

Sure, it is just a T-SQL. You can run it in an agent. In fact that's the reason to use it.

To delete the partition in the management studio right click on the partition and press delete.

Wednesday, April 3, 2013 - 3:02:22 PM - Yipi Back To Top (23146)

Thanks for your post. One question though: how to delete the partition first if it already exists?

Wednesday, April 3, 2013 - 11:55:52 AM - Olawale Back To Top (23138)

Hi, Thanks for this Tutoria but i would like to ask this question.....Is it possible to automate the code using SQL agent? Thanks














get free sql tips
agree to terms