Processing an OLAP cube with a T-SQL Stored Procedure

Here's a simple SQL Server stored procedure that you can call to process an OLAP cube using T-SQL.  The parameters should be self-explanatory.  To me, this is a little easier and more flexible than processing using DTS packages.

create procedure ProcessCube

    @Database varchar(100),

    @Cube varchar(100),

    @Partition varchar(100)  = null, -- If NULL, process the entire Cube

    @Server varchar(100) = 'localhost'

as

 

/*

    Author:        Jeff Smith

    Version:    10/27/2008

*/

 

/* variables used to store object handles */

declare @o_svr int, @o_db int, @o_cube int, @o_part int, @o_mds int

declare @hr int

 

/* different cube processing options. This SP uses "default" */

declare @PROCESS_DEFAULT int

declare @PROCESS_FULL int

declare @PROCESS_REFRESH_DATA int

 

set @PROCESS_DEFAULT = 0

set @PROCESS_FULL = 1

set @PROCESS_REFRESH_DATA = 2

 

-- create a DSO.Server object:

exec @hr = sp_OACreate 'DSO.Server', @o_svr out

if @hr <> 0

    begin

        print 'Error at create server:'

        exec sp_OAGetErrorInfo @o_svr

        goto cleanup

    end

 

-- Connect to the server:

exec @hr = sp_OAMethod @o_svr, 'Connect', null, @Server

if @hr <> 0

    begin

        print 'Error at connect to server:'

        exec sp_OAGetErrorInfo @o_svr

        goto cleanup

    end

 

-- Get the MDStores property from the Server:

exec @hr = sp_OAGetProperty @o_svr,'MDStores', @o_mds OUT

if @hr <> 0

    begin

        print 'Error at get getting Server MDStores:'

        exec sp_OAGetErrorInfo @o_svr

        goto cleanup

    end

 

-- Get the database from the MDStores:

exec @hr = sp_OAGetProperty @o_mds,'Item',@o_db OUT,@Database

if @hr <> 0

    begin

        print 'Error at get database:'

        exec sp_OAGetErrorInfo @o_mds

        goto cleanup

    end

 

-- get the MDStores property from the database:

exec sp_OADestroy @o_mds

exec @hr = sp_OAGetProperty @o_db,'MDStores', @o_mds OUT

if @hr <> 0

    begin

        print 'Error at get database MDStores:'

        exec sp_OAGetErrorInfo @o_db

        goto cleanup

    end

 

-- get the Cube from the MDStores

exec @hr = sp_OAGetProperty @o_mds,'Item',@o_cube OUT, @Cube

if @hr <> 0

    begin

        print 'Error at get Cube:'

        exec sp_OAGetErrorInfo @o_mds

        goto cleanup

    end

 

if @Partition is null -- Process the entire Cube, not just a single partition

    begin

        exec @hr = sp_OAMethod @o_cube, 'Process', null, @PROCESS_DEFAULT

        if @hr <> 0

            begin

                print 'Error at process Cube:'

                exec sp_OAGetErrorInfo @o_cube

                goto cleanup

            end

    end

 

else    -- just process the specified Partition

 

    begin

        -- Get the MDStores property of the Cube:

        exec sp_OADestroy @o_mds

        exec @hr = sp_OAGetProperty @o_cube,'MDStores', @o_mds OUT

        if @hr <> 0

            begin

                print 'Error at get Cube MDStores:'

                exec sp_OAGetErrorInfo @o_cube

                goto cleanup

            end

 

        -- Get the partition to process:

        exec @hr = sp_OAGetProperty @o_mds,'Item',@o_part OUT, @Partition

        if @hr <> 0

            begin

                print 'Error at get Parition:'

                exec sp_OAGetErrorInfo @o_mds

                goto cleanup

            end

 

        -- Process the partition:

        exec @hr = sp_OAMethod @o_part, 'Process', null, @PROCESS_DEFAULT

        if @hr <> 0

            begin

                print 'Error at process Partition:'

                exec sp_OAGetErrorInfo @o_part

                goto cleanup

            end

    end

 

-- And unlock all objects on the server:

exec @hr = sp_OAMethod @o_svr, 'UnlockAllObjects'

if @hr <> 0

    begin

        print 'Error at unlock all server objects:'

        exec sp_OAGetErrorInfo @o_svr

        goto cleanup

    end

 

cleanup:

 

if @o_mds is not null exec sp_OADestroy @o_mds

if @o_Part is not null exec sp_OADestroy @o_Part

if @o_cube is not null exec sp_OADestroy @o_cube

if @o_db is not null exec sp_OADestroy @o_db

if @o_svr is not null exec sp_OADestroy @o_svr

167 Responses to “Processing an OLAP cube with a T-SQL Stored Procedure”


  1. PillSpot.org. Canadian Health&Care.No prescription online pharmacy.Special Internet Prices.Best quality drugs. No prescription pills. Order pills online

    Buy:Cialis.Viagra Super Active+.Levitra.Tramadol.Viagra Super Force.Viagra Soft Tabs.Maxaman.Propecia.Viagra Professional.Cialis Soft Tabs.VPXL.Cialis Super Active+.Super Active ED Pack.Soma.Cialis Professional.Zithromax.Viagra….


  2. MedicamentSpot.com. Canadian Health&Care.Special Internet Prices.No prescription online pharmacy.Best quality drugs. High quality pills. Order drugs online

    Buy:Cozaar.Buspar.Acomplia.Zocor.Ventolin.Prozac.Wellbutrin SR.Lipitor.SleepWell.Nymphomax.Aricept.Advair.Zetia.Benicar.Amoxicillin.Female Cialis.Seroquel.Female Pink Viagra.Lipothin.Lasix….

  3. unit http://lheatingb-j.02JEEPPARTS.US/tag/conditioner+heater+window+unit+air+and/ : and…

    and…


  4. CheapTabletsOnline.com. Canadian Health&Care.Best quality drugs.No prescription online pharmacy.Special Internet Prices. Low price drugs. Buy pills online

    Buy:Buspar.Amoxicillin.Benicar.Zocor.Female Cialis.SleepWell.Lasix.Ventolin.Zetia.Nymphomax.Cozaar.Seroquel.Lipothin.Wellbutrin SR.Aricept.Acomplia.Female Pink Viagra.Lipitor.Advair.Prozac….

  5. mpg http://ygw.rix.zba.vg : mpg…

    cars…

  6. ████████►BUY VIAGRA◀███████…

    ████████▲▲▲▲▲▲▲▲▲████████…

  7. order cialis in canada online

    Buyno prescription…

  8. avodart@in.usa” rel=”nofollow”>.

    Buyno prescription…

  9. generic@Cialis.now” rel=”nofollow”>.

    Buynow…

  10. dicyclomine@and.imodium” rel=”nofollow”>…

    Buygeneric drugs…

  11. depakote@classification.now” rel=”nofollow”>..

    Buygeneric drugs…

  12. e-code for zometa

    Buyit now…

  13. cats zanaflex

    Buydrugs without prescription…

  14. Zaditor

    Buydrugs without prescription…

  15. Zyrtec

    Buyit now…

  16. zyprexa relprevv j-code

    Buygeneric drugs…

  17. Zyprexa

    Buynow it…

  18. Prozac

    Buygeneric meds…

  19. Tylenol@Tylenol.Tylenol” rel=”nofollow”>…

    Buygeneric meds…

  20. Topamax@Topamax.Topamax” rel=”nofollow”>.

    Buyit now…

  21. Slimfast@Slimfast.Slimfast” rel=”nofollow”>…

    Buynow…

  22. Cozaar@Cozaar.Cozaar” rel=”nofollow”>.

    Buynow it…

  23. .

    Buynow…

  24. rhinocort@vs.beconase” rel=”nofollow”>.

    Buyno prescription…

  25. can@cymbalta.cause.vaginal.itching.and.irritation” rel=”nofollow”>..

    Buydrugs without prescription…

  26. advair@side.effects” rel=”nofollow”>.

    Buyit now…

  27. .

    Buyno prescription…

  28. long@term.prilosec.use” rel=”nofollow”>.

    Buygeneric drugs…

  29. abilify@and.olanzapine” rel=”nofollow”>.

    Buyit now…

  30. side@effects.of.dog.medication.famotidine.and.flagyl” rel=”nofollow”>…

    Buygeneric drugs…

  31. pediatric@dosage.of.prednisolone” rel=”nofollow”>…

    Buyno prescription…

  32. Buygeneric drugs…

  33. buy@generic.VIAGRA” rel=”nofollow”>…

    Need cheap generic VIAGRA?…

  34. Get@Coral.Calcium.Online” rel=”nofollow”>..

    Buygeneric drugs…

  35. Order@Abana.Online” rel=”nofollow”>…

    Buywithout prescription…

  36. Buy@Discount.Abilify” rel=”nofollow”>…

    Buywithout prescription…

  37. Order@Abilify.Online” rel=”nofollow”>.

    Buynow it…

  38. Purchase@Discount.Abilify” rel=”nofollow”>..

    Buyit now…

  39. pictures@of.abilify” rel=”nofollow”>..

    Buyno prescription…

  40. Order@Abilify.Without.Prescription” rel=”nofollow”>..

    Buynow it…

  41. Buy@Cheap.Acai” rel=”nofollow”>..

    Buydrugs without prescription…

  42. Buy@Discount.Acai” rel=”nofollow”>..

    Buynow it…

  43. Order@Discount.Acai” rel=”nofollow”>…

    Buywithout prescription…

  44. Buy@Generic.Acai.Without.Prescription” rel=”nofollow”>…

    Buygeneric drugs…

  45. Cheap@Acai.Without.Prescription” rel=”nofollow”>..

    Buygeneric meds…

  46. Buy@Discount.Coral.Calcium” rel=”nofollow”>.

    Buygeneric meds…

  47. Buy@Discount.Abilify” rel=”nofollow”>..

    Buygeneric pills…

  48. Buy@Acai.Online” rel=”nofollow”>.

    Buywithout prescription…

  49. Buy@Discount.Acai” rel=”nofollow”>…

    Buynow…

  50. Order@Cheap.Acai” rel=”nofollow”>…

    Buyit now…

  51. Purchase@Acai.Online” rel=”nofollow”>…

    Buynow it…

  52. Purchase@Cheap.Acai” rel=”nofollow”>…

    Buyit now…

  53. Purchase@Cheap.Acai” rel=”nofollow”>..

    Buywithout prescription…

  54. Order@Generic.Acai” rel=”nofollow”>..

    Buydrugs without prescription…

  55. Generic@Acai.500mg.Without.Prescription” rel=”nofollow”>.…

    Buyno prescription…

  56. Buy@Cheap.Energy.Boost” rel=”nofollow”>.…

    Buygeneric drugs…

  57. Buy@Accupril.Online” rel=”nofollow”>.…

    Buygeneric meds…

  58. Purchase@Accutane.Online” rel=”nofollow”>.…

    Buynow it…

  59. Purchase@Accutane.Without.Prescription” rel=”nofollow”>.…

    Buygeneric drugs…

  60. Cheap@Generic.Accutane.10mg.20mg” rel=”nofollow”>.…

    Buyno prescription…

  61. Cheap@Aciphex.Online” rel=”nofollow”>.…

    Buynow it…

  62. Buy@Aciphex.20mg” rel=”nofollow”>.…

    Buygeneric drugs…

  63. Buy@Generic.Aciphex.20mg” rel=”nofollow”>.…

    Buynow it…

  64. Cheap@Generic.Aciphex.20mg” rel=”nofollow”>.…

    Buygeneric drugs…

  65. Order@Acomplia.Online” rel=”nofollow”>.…

    Buygeneric meds…

  66. Order@Discount.Acomplia” rel=”nofollow”>.…

    Buygeneric drugs…

  67. Buy@Actonel.35mg” rel=”nofollow”>..

    Buygeneric drugs lzt…

  68. Buy@Actos.Online” rel=”nofollow”>.

    Buygeneric drugs zfk…

  69. Purchase@Actos.Online” rel=”nofollow”>..

    Buygeneric meds qmo…

  70. Purchase@Cheap.Actos” rel=”nofollow”>..

    Buynow it vhx…

  71. Buy@Generic.Actos” rel=”nofollow”>..

    Buygeneric drugs eet…

  72. Buy@Generic.Actos.Without.Prescription” rel=”nofollow”>..

    Buygeneric drugs krg…

  73. Order@Adalat.Online” rel=”nofollow”>..

    Buygeneric meds qhr…

  74. Purchase@Coral.Calcium.Online” rel=”nofollow”>.

    Buyno prescription luw…

  75. Buy@Abana.Online” rel=”nofollow”>..

    Buynow it tly…

  76. Cheap@Generic.Abilify.5mg.10mg.15mg.20mg.30mg” rel=”nofollow”>.

    Buywithout prescription yyn…

  77. Buy@Acai.Online” rel=”nofollow”>…

    Buydrugs without prescription vrg…

  78. Get@Acai.Online” rel=”nofollow”>…

    Buynow it zdg…

  79. Purchase@Generic.Acai” rel=”nofollow”>..

    Buydrugs without prescription faj…

  80. Order@Energy.Boost.Online” rel=”nofollow”>.

    Buygeneric drugs axm…

  81. Cheap@Energy.Boost.Online” rel=”nofollow”>…

    Buydrugs without prescription jlg…

  82. Get@Energy.Boost.Online” rel=”nofollow”>..

    Buyno prescription jui…

  83. Get@Aciphex.Online” rel=”nofollow”>.

    Buygeneric drugs nih…

  84. Purchase@Generic.Aciphex” rel=”nofollow”>..

    Buydrugs without prescription ygy…

  85. Purchase@Aciphex.Without.Prescription” rel=”nofollow”>.

    Buydrugs without prescription xab…

  86. Cheap@Aciphex.20mg” rel=”nofollow”>.

    Buyit now ojb…

  87. Buy@Aciphex.20mg” rel=”nofollow”>.

    Buygeneric drugs mby…

  88. Purchase@Generic.Aciphex.20mg” rel=”nofollow”>…

    Buywithout prescription ccw…

  89. Order@Actonel.Online” rel=”nofollow”>…

    Buynow fjl…

  90. Order@Cheap.Advair” rel=”nofollow”>…

    Buygeneric drugs vbr…

  91. Purchase@Cheap.Advair” rel=”nofollow”>.

    Buygeneric pills oir…

  92. Get@Advair.Online” rel=”nofollow”>.

    Buygeneric drugs hwi…

  93. Buy@Generic.Advair.Without.Prescription” rel=”nofollow”>..

    Buyno prescription jwi…

  94. Order@Generic.Advair” rel=”nofollow”>..

    Buydrugs without prescription hak…

  95. Buy@Discount.Aggrenox” rel=”nofollow”>..

    Buyno prescription wkp…

  96. Order@Discount.Aggrenox” rel=”nofollow”>..

    Buyno prescription lka…

  97. Purchase@Albenza.Online” rel=”nofollow”>…

    Buydrugs without prescription ttp…

  98. coral@calcium.vitamin.explanation” rel=”nofollow”>.

    Buywithout prescription…

  99. aleve@cold.tablets” rel=”nofollow”>…

    Buygeneric pills…

  100. aloe@vera.juice.benefits.best” rel=”nofollow”>.

    Buygeneric meds…

  101. arava@adverse.events” rel=”nofollow”>…

    Buygeneric meds…

  102. coral@calcium.supplements” rel=”nofollow”>…

    Buygeneric meds…

  103. aciphex@muscle.pain” rel=”nofollow”>..

    Buygeneric pills…

  104. actos@side.effects.edema” rel=”nofollow”>..

    Buygeneric drugs…

  105. how@to.monitor.anafranil” rel=”nofollow”>.

    Buyno prescription…

  106. hiv research being conducted in usa

    Buy_generic pills…

  107. stage 3 ovarian cancer

    Buy_drugs without prescription…

  108. alberta alcohol and drug abuse commission

    Buy_generic meds…

  109. the process of methanogens metabolism

    Buy_drugs without prescription…

  110. advair and contraindications

    Buy_generic meds…

  111. canine heat cycles

    Buy_without prescription…

  112. apo amitriptyline

    Buy_generic pills…

  113. recipes for gastric bypass patients

    Buy_now…

  114. citalopram length of use

    Buy_drugs without prescription…

  115. conquer online hacking tools

    Buy_generic drugs…

  116. symptoms of prescription drug abuse

    Buy_drugs without prescription…

  117. weight gain stories feeders

    Buy_drugs without prescription…

  118. ept pregnancy test

    Buy_generic meds…

  119. accutane birth defects

    Buy_it now…

  120. menstrual patterns in menopause

    Buy_no prescription…

  121. market drugs edmonton

    Buy_now it…

  122. dependent disorder anxiety

    Buy_generic pills…

  123. adrenal cancer symptoms

    Buy_generic drugs…

  124. indigestion and chest pain

    Buy_now it…

  125. herbal flee collars for dogs

    Buy_generic pills…

  126. working and twin pregnancy

    Buy_now it…

  127. how to embed pictures ms office

    Buy_drugs without prescription…

  128. youtube monavie cancer

    Buy_generic meds…

  129. adult survivors of childhood cancer

    Buy_generic pills…

  130. singapore secure psychiatric forensic unit

    Buy_no prescription…

  131. mini insulin fridge

    Buy_generic drugs…

  132. decongestants hypertension safe

    Buy_now it…

  133. high calorie diet for babies

    Buy_generic drugs…

  134. cheap fetal heart rate monitor

    Buy_generic meds…

  135. cures for fingernail fungi

    Buy_generic meds…

  136. avelox treats for bacteria

    Buy_drugs without prescription…

  137. drug treatment for bipolar disorder

    Buy_generic pills…

  138. canine kidney failure symptoms

    Buy_generic pills…

  139. bacterial staphylococcus aureus meningitis symptoms

    Buy_without prescription…

  140. st joseph aspirin coupon

    Buy_generic pills…

  141. insulin receptor inhibition

    Buy_without prescription…

  142. sample tlc diets

    Buy_generic drugs…

  143. hot spots or cancer

    Buy_without prescription…

  144. cancer chemotherapy combination drugs

    Buy_generic meds…

  145. build muscle mass diet

    Buy_drugs without prescription…

  146. south beach diet foods to avoid

    Buy_no prescription…

  147. rebound phenomenon during gradual clonidine withdrawal

    Buy_drugs without prescription…

  148. kids weight gain

    Buy_generic drugs…

  149. 100 mile diet store

    Buy_generic meds…

  150. list of anti anxiety drugs

    Buy_generic meds…

  151. pain relief until root canal

    Buy_generic meds…

  152. free diabetes logbooks to print

    Buy_generic drugs…

  153. altitude and pregnancy

    Buy_it now…

  154. glucophage powered by vbulletin version 2.2.1

    Buy_generic pills…

  155. hiv quality of life survey tools

    Buy_drugs without prescription…

  156. obesity in moorhead minnesota

    Buy_now…

  157. interactions with azithromycin and astragalus

    Buy_it now…

  158. medical nebulizer

    Buy_generic meds…

  159. dog retching vomiting weight loss

    Buy_generic drugs…

  160. kim kardashian weight gain

    Buy_now…

  161. breast cancer site is having trouble

    Buy_drugs without prescription…

  162. space herpes

    Buy_now…

  163. boost metabolism foods

    Buy_now…

  164. drug citalopram side effects

    Buy_generic drugs…

  165. remeron dreams

    Buy_it now…

  166. fruity taste and diabetes

    Buy_it now…

  167. Download@alternative.Rock” rel=”nofollow”>.

    Search rock UK Charts…