Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips







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 (15)   |   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




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
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


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 - 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.


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


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.


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.


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, 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 - 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 - 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.


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.

 

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 :)


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,


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?


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



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 

Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.