x

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 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
show all comments (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
22.2k 8 11 21

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

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x17

asked: Sep 17 '12 at 06:26 AM

Seen: 868 times

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