x
login about faq Site discussion (meta-askssc)

updating cube using stored procedure

Hi , Please help me on the below sp

CREATE PROCEDURE [dbo].[USP_UpdateDataFromCube]
 @Customer_Id VARCHAR(50),
 @Sales_Amount VARCHAR(50)
AS
BEGIN

 SET NOCOUNT ON

 DECLARE @MDX_QUERY AS VARCHAR(MAX)
 DECLARE @OPEN_QUERY AS NVARCHAR(MAX)
 DECLARE @LINKED_SERVER AS VARCHAR(MAX)
 DECLARE @CUBE_NAME AS VARCHAR(MAX)

 SET @LINKED_SERVER='[DSV1-TestCube]'
 SET @CUBE_NAME='[DSV1-TestCube]'

 SET @MDX_QUERY='UPDATE [AdventureWorksDW2008R2].[dbo].[FactInternetSales]
 SET LinkedServerName='+@LINKED_SERVER+',
 CubeName='+@CUBE_NAME+',
 SalesAmount=(SELECT * FROM OPENQUERY('+@LINKED_SERVER+',
 ''SELECT {[MEASURES].[PRODUCT KEY],
 [MEASURES].[UNIT PRICE],
 [MEASURES].[SALES AMOUNT]} 
 ON AXIS(0),
 {[DIM CUSTOMER].[CUSTOMER KEY].&[11007]} 
 ON AXIS(1)
 FROM '+@CUBE_NAME+''')) FIS INNER JOIN [AdventureWorksDW2008R2].[dbo].[DimCustomer] DC 
 ON FIS.CustomerKey=DC.CustomerKey WHERE DC.CustomerKey='+@Customer_Id+'' 

 SET @OPEN_QUERY='UPDATE OPENQUERY("'+@LINKED_SERVER+'","'+@MDX_QUERY+'")'

EXEC(@OPEN_QUERY)
 SET NOCOUNT OFF
END
GO

as at the time of execution i am getting some error message like below "

Msg 103, Level 15, State 4, Line 1
The identifier that starts with 'UPDATE [AdventureWorksDW2008R2].[dbo].[FactInternetSales]
 SET LinkedServerName=[DSV1-TestCube],
 CubeName=[DSV1-TestC' is too long. Maximum length is 128.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'UPDATE [AdventureWorksDW2008R2].[dbo].[FactInternetSales]
 SET LinkedServerName=[DSV1-TestCube],
 CubeName=[DSV1-TestC'.

"

more ▼

asked Sep 17 '12 at 06:26 AM in Default

Newface gravatar image

Newface
0 1 1

have u considered updating the tables and processing the cube.

Sep 25 '12 at 06:05 AM aRookieBIdev

nope,i haven't tried like your suggestion as it was asked to update the cube only and the cube will not be processed.

Sep 26 '12 at 05:07 AM Newface

Can you describe, what you want to achieve? The title tells update cube but in the stored proc it seems like you try to update data in table using data from cube.

Sep 26 '12 at 05:14 AM Pavel Pawlowski

I just want to update the cube using stored procedure.

Sep 26 '12 at 05:23 AM Newface

Dear Pavel,I am trying to update my cells value of my cube which is Write Back enabled and at the same time i am using OPENQUERY statement to execute this update but as you are aware that OPENQUERY alway takes a select statement so here what is the way out to update my cube's cells.

At the same time i am able to update my cube's cells with ADOMD.NET'S COMMANDTYPE.COMMANDTEXT(IF I DON'T USE STORED PROC) but i am not able to run the same update statement using STORED PROCEDURE.

Sep 26 '12 at 06:19 AM Newface
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

It depends what you mean by "Update Cube". If you mean updating cells values in the Write Back enabled cells and dimensions, then yoou have to first enable the WriteBack on the cells and/or dimensions.

Then you can use UPDATE CUBE statement (MDX). This could be probably possible to send from the OPENQUERY but didnt' tested it as currently don't have a cube with write back available.

If you mean re-process, then you hvae to send XMLA commands to the SSAS to instruct it to process (full process, process update,.....) particular cube in particular datase.

It could be possible to achieve this in stored proc using OLE, but much better would be to write a CLR Stored procedure and process the cube using AMO or ADOMD.NET.

Other option is to use eg. PowerShell to send XAML command for processing.

more ▼

answered Sep 26 '12 at 06:11 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
20.3k 5 10 20

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments



Facebook logo Follow Ask SSC on Facebook
Find Ask SSC on Google+
linkedin logo Find us on LinkedIn

Topics:

x11

asked: Sep 17 '12 at 06:26 AM

Seen: 444 times

Last Updated: Sep 28 '12 at 06:37 AM

Copyright © 2002-2012 Simple Talk Publishing. All Rights Reserved. If you have any queries, please contact the site administrators.
Ask SQL Server Central is a community service provided by Red Gate.