Thursday, September 27, 2007

Automating Partition Creation

By Bryan C. Smith


The use of multiple partitions per measure group is promoted to improve both query and processing performance as well as the overall manageability of cubes. Partitioning strategies are often time-based, so that a single partition may represent a specific year, quarter, month, or some other unit of time.

One of the central challenges of partitioning a measure group on time (or any other non-static dimension) is that new partitions may be required over the life of the cube. If it can be predicted when these partitions might be needed, the required partitions can be manually created in advance by the admin. Where values cannot be predicted, an "overflow" partition can be created to catch any values not attributed to other, more precisely defined partitions.

Still, having the ETL layer query the database to determine the partitions required and then create those partitions on your behalf is a much more attractive approach. Below is a simplistic script demonstrating the use of AMO to do just that.

The script is written for execution within an SSIS Script task and requires a reference to the AMO library. Its goal is to add any year-based partitions to the Reseller Sales measure group within the Adventure Works cube of the Adventure Works DW multidimensional database. It is tested by dropping any partition from the Reseller Sales measure group.

Here is a quick rundown of the logic.

The script starts by establishing references to the Adventure Works cube and the Reseller Sales measure group within the Adventure Works DW multidimensional database. A query is issued against the DimTime table in the AdventureWorksDW relational database to determine which years exist within the table and the key ranges of the records associated with those years. (There is a critical assumption here that records in the DimTime table are sequenced by date. This may not be true within your data warehouse.)

The routine then loops through each partition in the Reseller Sales measure group to determine is a partition for that year exists. If not, a new partition is created with the appropriate query bindings. The first aggregation design used within the measure group is assigned to the new partition. (This is not a recommended approach but simply serves to demonstrate how aggregations can be assigned to new partitions.) The partition is then associated with the measure group and the change is submitted to SSAS. (It is important to note, the new partition is unprocessed at this time.)

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.AnalysisServices


Class ScriptMain


Sub Main()


'Locate Measure Group of Interest (and a Known Partition)


Dim MyServer As
New Server



Dim MyDatabase As Database = _

MyServer.Databases.GetByName("Adventure Works DW")

Dim MyCube As Cube = MyDatabase.Cubes.GetByName("Adventure Works")

Dim MyMeasureGroup As MeasureGroup = _

MyCube.MeasureGroups.GetByName("Reseller Sales")


'Retrieve Years in Dimension


Dim MyConnectionString As

MyConnectionString = "Server=localhost;" + _

"Database=AdventureWorksDW;Integrated Security=SSPI;"

Dim MySqlConnection As
New SqlClient.SqlConnection(MyConnectionString)

Dim MySqlCommand As
New SqlClient.SqlCommand( _

MySqlQueryString, MySqlConnection)



Dim MySqlReader As SqlClient.SqlDataReader = MySqlCommand.ExecuteReader


'Add Partitions That Don't Exist


While MySqlReader.Read


Dim MyYear As
String = MySqlReader.GetString(0)

Dim MyStartKey As Int32 = MySqlReader.GetInt32(1)

Dim MyEndKey As Int32 = MySqlReader.GetInt32(2)


Dim MyPartitionName As
String = "Reseller_Sales_" + MyYear


Dim MyPartition As Partition = _



Dim MyPartition As
New Partition

With MyPartition

.Name = MyPartitionName

If MyMeasureGroup.AggregationDesigns.Count > 0 Then

.AggregationDesignID = _



.Source = New QueryBinding(MyCube.DataSource.ID, _

MyParitionQueryString(MyStartKey, MyEndKey))











Dts.TaskResult = Dts.Results.Success



Function MySqlQueryString() As

Dim Ret As

Ret = "select CalendarYear," + _

"min(TimeKey),max(TimeKey) " + _

"from dbo.DimTime " + _

"Group by CalendarYear"

Return Ret



Function MyParitionQueryString(ByVal StartKey As Int32, _

ByVal EndKey As Int32) As

Dim Ret As

Ret = "SELECT " + _

"[dbo].[FactResellerSales].[ProductKey]," + _

"[dbo].[FactResellerSales].[OrderDateKey]," + _

"[dbo].[FactResellerSales].[DueDateKey]," + _

"[dbo].[FactResellerSales].[ShipDateKey]," + _

"[dbo].[FactResellerSales].[ResellerKey], " + _

"[dbo].[FactResellerSales].[EmployeeKey]," + _

"[dbo].[FactResellerSales].[PromotionKey]," + _

"[dbo].[FactResellerSales].[CurrencyKey]," + _

"[dbo].[FactResellerSales].[SalesTerritoryKey]," + _

"[dbo].[FactResellerSales].[SalesOrderNumber]," + _

"[dbo].[FactResellerSales].[SalesOrderLineNumber]," + _

"[dbo].[FactResellerSales].[RevisionNumber]," + _

"[dbo].[FactResellerSales].[OrderQuantity]," + _

"[dbo].[FactResellerSales].[UnitPrice]," + _

"[dbo].[FactResellerSales].[ExtendedAmount], " + _

"[dbo].[FactResellerSales].[UnitPriceDiscountPct], " + _

"[dbo].[FactResellerSales].[DiscountAmount], " + _

"[dbo].[FactResellerSales].[ProductStandardCost], " + _

"[dbo].[FactResellerSales].[TotalProductCost], " + _

"[dbo].[FactResellerSales].[SalesAmount], " + _

"[dbo].[FactResellerSales].[TaxAmt], " + _

"[dbo].[FactResellerSales].[Freight], " + _

"[dbo].[FactResellerSales].[CarrierTrackingNumber], " + _

"[dbo].[FactResellerSales].[CustomerPONumber]" + _

"FROM [dbo].[FactResellerSales] " + _

"WHERE OrderDateKey BETWEEN " + StartKey.ToString + " AND " + _



Return Ret




DBOwner said...

The code looks great but please tell me one thing - isn't it a bad practice to code that much in your catch block ????
as far as i know, catch blocks are not supposed to be the entire logic of the code.
if you could check if exists and then add new partition when necessary instead it would be better

Anonymous said...

Hello I just entered before I have to leave to the airport, it's been very nice to meet you, if you want here is the site I told you about where I type some stuff and make good money (I work from home): here it is

Telefone VoIP said...

Hello. This post is likeable, and your blog is very interesting, congratulations :-). I will add in my blogroll =). If possible gives a last there on my blog, it is about the Telefone VoIP, I hope you enjoy. The address is A hug.

Ajit Singh said...

Hello Brian,

Could you please change your blogspot layout to stretch one so that wider monitor users can utilize their screen width. it is just too narrow to read it comfortably.

Marc Beacom said...

When I paste your code into the script task, i get "Type 'server' is not defined". I do not know so not really sure what this error means. My guess is that this should be solved by the "Imports Microsoft.AnalysisServices" statement

Any ideas?

Anonymous said...

black mold exposureblack mold symptoms of exposurewrought iron garden gatesiron garden gates find them herefine thin hair hairstylessearch hair styles for fine thin hairnight vision binocularsbuy night vision binocularslipitor reactionslipitor allergic reactionsluxury beach resort in the philippines

afordable beach resorts in the philippineshomeopathy for big with great mineral makeup bargainsmineral makeup wholesalersprodam iphone Apple prodam iphone prahacect iphone manualmanual for P 168 iphonefero 52 binocularsnight vision Fero 52 binocularsThe best night vision binoculars here

night vision binoculars bargainsfree photo albums computer programsfree software to make photo albumsfree tax formsprintable tax forms for free craftmatic air bedcraftmatic air bed adjustable info hereboyd air bedboyd night air bed lowest pricefind air beds in wisconsinbest air beds in wisconsincloud air beds

best cloud inflatable air bedssealy air beds portableportables air bedsrv luggage racksaluminum made rv luggage racksair bed raisedbest form raised air bedsaircraft support equipmentsbest support equipments for aircraftsbed air informercialsbest informercials bed airmattress sized air beds

bestair bed mattress antique doorknobsantique doorknob identification tipsdvd player troubleshootingtroubleshooting with the dvd playerflat panel television lcd vs plasmaflat panel lcd television versus plasma pic the bestThe causes of economic recessionwhat are the causes of economic recessionadjustable bed air foam The best bed air foam

hoof prints antique equestrian printsantique hoof prints equestrian printsBuy air bedadjustablebuy the best adjustable air bedsair beds canadian storesCanadian stores for air beds

migraine causemigraine treatments floridaflorida headache clinicdrying dessicantair drying dessicantdessicant air dryerpediatric asthmaasthma specialistasthma children specialistcarpet cleaning dallas txcarpet cleaners dallascarpet cleaning dallas

vero beach vacationvero beach vacationsbeach vacation homes veroms beach vacationsms beach vacationms beach condosmaui beach vacationmaui beach vacationsmaui beach clubbeach vacationsyour beach vacationscheap beach vacations

bob hairstylebob haircutsbob layeredpob hairstylebobbedclassic bobCare for Curly HairTips for Curly Haircurly hair12r 22.5 best pricetires truck bustires 12r 22.5

washington new housenew house houstonnew house san antonionew house venturanew houston house houston house txstains removal dyestains removal clothesstains removalteeth whiteningteeth whiteningbright teeth

Anonymous said...

歐朋電視辣妹總動員 - 辣妹貼圖 - 辣媽辣妹 - 檳榔辣妹寫真圖片 - 台灣辣妹貼圖區 - 免費視訊辣妹 - 辣妹自拍俱樂部 - 台灣辣妹自拍網 - 脫衣女主播 - 鋼管辣妹 - 人妻辣妹自拍教流屋 - 080聊天室 - 我愛你聊天室 - 中壢交友 - 色情貼圖 - 色情分類圖片和電影 - 台灣kiss色情貼圖 - 007色情貼圖區 - 辣妹脫衣秀 - 視訊聊天 - 情色影片 - 0204情人辣妹視訊聊天 - 成人情色 - 情色影音 - 性愛聊天室 - 情色聊天 - 激情聊天室 - 成人色情a片 - 色情a片 - psp遊戲免費下載 -

Sam Kane said...

Here is this and some other articles on SSAS Partitions: