 | |
multiple tuples in a single UPDATE CUBE
microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
multiple tuples in a single UPDATE CUBE -
01-26-2004
, 02:00 PM
|
Another question for you George. The BNF for the update cube statement indicates that it should be possible to update more than one tuple with a single statement. I'm trying the construction bellow but the engine is complying about the comma between the two tuples assignments. UPDATE CUBE [Budget] SET ([Version].[Budget],[Period].[Months].[Jan 01],[Unit].[10002030],[Account].[4250045]) = 0, ([Version].[Budget],[Period].[Months].[Jan 01],[Unit].[10002030],[Account].[4250046]) = 0 |
#2
| |||
| |||
|
|
Back in Aug 2003 George Spofford said updating multiple tuples with a single UPDATE CUBE statement was not implemented (snippet below). Has this been improved this or must we still go through a round-trip for each bit of data? I'm using XMLA and the overhead is killing my performance. If the latter, is there a better way to update multiple cells with a single network operation other than going to the raw fact table directly? The use case would be something like updating Sales for the whole time horizon (Jan -> Dec). TIA -zoltan Copied post: Multiple values in one statement is documented but not implemented. Paulo Monteiro wrote: Another question for you George. The BNF for the update cube statement indicates that it should be possible to update more than one tuple with a single statement. I'm trying the construction bellow but the engine is complying about the comma between the two tuples assignments. UPDATE CUBE [Budget] SET ([Version].[Budget],[Period].[Months].[Jan 01],[Unit].[10002030],[Account].[4250045]) = 0, ([Version].[Budget],[Period].[Months].[Jan 01],[Unit].[10002030],[Account].[4250046]) = 0 |
#3
| |||
| |||
|
|
BTW - what is your scenario that you need to do writebacks over XMLA, and you cannot use OLEDB ? |
probably could write an IIS app and forward MSAS requests to that
secondary webapp (and bypass XMLA altogether for that matter) but I'd
rather avoid coding both a Java and .NET app if possible. XMLA serves me
so well for everything except the writeback.
-zoltan
Mosha Pasumansky [MS] <moshap (AT) online (DOT) microsoft.com> wrote:
Quote:
|
By "has this been improved" I interpret your question as "was it changed in one of the Analysis Services 2000 service packs". The answer is no. There is an efficient way to do it through OLEDB, but since you are using XMLA - you don't have a good solution other then writing some custom code to handle it. BTW - what is your scenario that you need to do writebacks over XMLA, and you cannot use OLEDB ? |
#4
| |||
| |||
|
|
By "has this been improved" I interpret your question as "was it changed in one of the Analysis Services 2000 service packs". The answer is no. There is an efficient way to do it through OLEDB, but since you are using XMLA - you don't have a good solution other then writing some custom code to handle it. BTW - what is your scenario that you need to do writebacks over XMLA, and you cannot use OLEDB ? -- ================================================== Mosha Pasumansky - http://www.mosha.com/msolap Development Lead in the Analysis Server team All you need is love (John Lennon) Disclaimer : This posting is provided "AS IS" with no warranties, and confers no rights. ================================================== "Zoltan Grose" <zgrose (AT) mac (DOT) com> wrote in message news:20040126120041580-0800 (AT) msnews (DOT) microsoft.com... Back in Aug 2003 George Spofford said updating multiple tuples with a single UPDATE CUBE statement was not implemented (snippet below). Has this been improved this or must we still go through a round-trip for each bit of data? I'm using XMLA and the overhead is killing my performance. If the latter, is there a better way to update multiple cells with a single network operation other than going to the raw fact table directly? The use case would be something like updating Sales for the whole time horizon (Jan -> Dec). TIA -zoltan Copied post: Multiple values in one statement is documented but not implemented. Paulo Monteiro wrote: Another question for you George. The BNF for the update cube statement indicates that it should be possible to update more than one tuple with a single statement. I'm trying the construction bellow but the engine is complying about the comma between the two tuples assignments. UPDATE CUBE [Budget] SET ([Version].[Budget],[Period].[Months].[Jan 01],[Unit].[10002030],[Account].[4250045]) = 0, ([Version].[Budget],[Period].[Months].[Jan 01],[Unit].[10002030],[Account].[4250046]) = 0 |
#5
| |||
| |||
|
|
Hi Mosha, what's the "efficient way to do it through OLEDB"? |
IMDDataSet) is the most efficient. But even doing UPDATE CUBE statements in
OLEDB is more efficient then in XMLA, because they don't translate into
roundtrips.
--
==================================================
Mosha Pasumansky - http://www.mosha.com/msolap
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==================================================
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.