SQL Sentry Performance Advisor for SQL Server delivers an advanced performance dashboard with relevant Windows and SQL Server metrics in a single view along with detailed insight of heavy SQL, blocking, deadlocks, and disk bottlenecks. Performance Advisor is packed with ground-breaking features that are not found in any other performance monitoring software, all designed with the singular goal of simplifying the process of optimizing your SQL Server performance.
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.
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.
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)
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
Open SQL Server Management Studio
Go to Server Objects > Linked Server right click and select New Linked Server...
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).
Make sure RPC and RPC Out are set to True in order to communicate between SQL Server and Analysis Services
Another option instead of going through steps 2 to 4 is to run T-SQL like below to create the Linked Server to SSAS.
/****** 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
EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'rpc', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'rpc out', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'sub', @optvalue=N'false'
EXer.dbo.sp_serveroption @server=N'SSAS', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'use remote collation', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'remote proc transaction promotion', @optvalue=N'true'
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.
In SQL Server Management Studio, connect to the Analysis Services server that you used to create the Linked Server.
Go to Databases > AdventureWorksDW2012Multidimensional-EE > Cubes > Adventure Works > Measure Groups > Internet Sales > Partitions > Internet_Sales_2005
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:
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>
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
Monday, April 08, 2013 - 9:27:28 PM - Daniel Calbimonte