Learn more about SQL Server tools

   
   








Learn more about SQL Server tools








Learn more about SQL Server tools


Dynamic XMLA using T-SQL for SQL Server Analysis Services

MSSQLTips author Daniel Calbimonte By:   |   Read Comments (21)   |   Related Tips: > 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


Last Update: 11/2/2012


About the author
MSSQLTips author Daniel Calbimonte
Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Thursday, December 18, 2014 - 2:46:21 PM - Daniel Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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 07, 2014 - 12:27:44 PM - Syed Read The Tip

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 09, 2014 - 5:20:59 AM - Sega Read The Tip

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 Read The Tip

 

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 Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

It is T-SQL.

It is in your SQL Server Database Engine.


Friday, October 11, 2013 - 9:47:59 AM - Asaduzzaman Read The Tip

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 Read The Tip

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 08, 2013 - 9:27:28 PM - Daniel Calbimonte Read The Tip

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 07, 2013 - 9:10:32 AM - Olawale Read The Tip

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 03, 2013 - 8:59:02 PM - Daniel Calbimonte Read The Tip

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 03, 2013 - 3:02:22 PM - Yipi Read The Tip

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


Wednesday, April 03, 2013 - 11:55:52 AM - Olawale Read The Tip

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




 

Sponsor Information