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, 2012 at 06:26 AM in Default

avatar image

Newface
0 1 1 1

have u considered updating the tables and processing the cube.

Sep 25, 2012 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, 2012 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, 2012 at 05:14 AM Pavel Pawlowski

I just want to update the cube using stored procedure.

Sep 26, 2012 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, 2012 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, 2012 at 06:11 AM

avatar image

Pavel Pawlowski
22.7k 10 15 26

(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

SQL Server Central

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

Topics:

x22

asked: Sep 17, 2012 at 06:26 AM

Seen: 1477 times

Last Updated: Sep 28, 2012 at 06:37 AM

Copyright 2016 Redgate Software. Privacy Policy