How to kill or disconnect session or users on SSAS server

There are multiple ways to do this. We will explore using hybrid approach as mentioned in this Microsoft article.

  1. In SQL Server Management Studio, connect to an Analysis Services instance.
  2. Paste any one of the following DMV queries in an MDX query window to get a list of all sessions, connections, and commands that are currently executing:

    Select * from $System.Discover_Sessions

    Select * from $System.Discover_Connections

    Select * from $System.Discover_Commands

  3. Press F5 to execute the query.

    The DMV query returns session and connection information in a tabular result set that is easier read and copy from.

    Keep the query window open. In the next step, you will want to return to this page to copy the SPIDs of the session you want to disconnect.

    To end a session, open a second XMLA query window.

  4. Paste the following syntax into an MDX query window, replacing the ConnectionID, SessionID, or SPID placeholder with a valid value copied from the previous step.

<Cancel xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”&gt;
<SPID>595618</SPID>
<CancelAssociated>1</CancelAssociated>
</Cancel>

or

<Cancel xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”><ConnectionID>111</ConnectionID&gt;
<SessionID>222</SessionID>
<SPID>333</SPID>

<CancelAssociated>1</CancelAssociated>
</Cancel>

Thus you can terminate the long running queries or memory hogging sessions.

 

SSAS Dimension (Time, Currency) Attribute Types

Defining attribute type for dimension is very important. Attribute types help classify an attribute in terms of business functionality.

Some attribute types also have specific meaning to Analysis Services. For example, some attribute types (QuarterOfYear, Quarters, DayOfMonth, etc.) identify attributes that represent time periods in various calendars for time dimensions.

General Attribute Types

Attribute Type Value Description
Address Represents an address.
AddressBuilding Represents a building identifier for an address.
AddressCity Represents a city for an address.
AddressCountry Represents a country or region for an address.
AddressFax Represents a fax telephone number.
AddressFloor Represents a floor identifier for an address.
AddressHouse Represents a house number for an address.
AddressPhone Represents a telephone number.
AddressQuarter Represents a quarter for an address.
AddressRoom Represents a room identifier for an address.
AddressStateOrProvince Represents a state or province for an address.
AddressStreet Represents the street for an address.
AddressZip Represents a ZIP Code or Postal Code for an address.
BomResource Represents a resource for a bill of materials (BOM).
Caption Represents a caption.
CaptionAbbreviation Represents an abbreviation.
CaptionDescription Represents a description.
Channel Represents a channel.
City Represents a city.
Company Represents a company.
Continent Represents a continent.
Country Represents a country or region.
County Represents a county.
CustomerGroup Represents a group of customers.
CustomerHousehold Represents a household of customers.
Customers Represents a customer.
DateCanceled Represents a cancellation date.
DateDuration Represents a duration.
DateEnded Represents an end date.
DateModified Represents a modification date.
DateStart Represents a start date.
DeletedFlag Indicates whether a member is or should be deleted (in terms of business functionality.)

Note: Analysis Services does not use this attribute type to determine whether a member should be deleted. Instead, this attribute type is intended to be used by client applications for display purposes only.

FormattingColor Represents the color used in formatting.
FormattingFont Represents the font used in formatting.
FormattingFontEffects Represents the font effects used in formatting.
FormattingFontSize Represents the font size used in formatting.
FormattingOrder Represents the order used in formatting.
FormattingSubtotal Represents a subtotal.
GeoBoundaryBottom Represents the bottommost value of a geographic boundary.
GeoBoundaryFront Represents the value at the front of a geographic boundary.
GeoBoundaryLeft Represents the leftmost value of a geographic boundary.
GeoBoundaryPolygon Represents the polygon definition of a geographic boundary.
GeoBoundaryRear Represents the rearmost value of a geographic boundary.
GeoBoundaryRight Represents the rightmost value of a geographic boundary.
GeoBoundaryTop Represents the topmost value of a geographic boundary.
GeoCentroidX Represents an X-axis centroid for a geographic region.
GeoCentroidY Represents a Y-axis centroid for a geographic region.
GeoCentroidZ Represents a Z-axis centroid for a geographic region.
ID Represents an identifier (ID) or key.
Image Represents an image in an undefined graphic format.
ImageBmp Represents an image in bitmap graphic format.
ImageGif Represents an image in Graphics Interchange Format (GIF) graphic format.
ImageJpg Represents an image in Joint Photographic Experts Group (JPEG) graphic format.
ImagePng Represents an image in Portable Network Graphics (PNG) graphic format.
ImageTiff Represents an image in Tagged Image File Format (TIFF) graphic format.
OrganizationalUnit Represents an organizational unit.
OrgTitle Represents an organizational title.
PercentOwnership Represents a percent of ownership.
PercentVoteRight Represents a percent of voting rights.
Person Represents a person.
PersonContact Represents contact information for a person.
PersonDemographic Represents demographic information for a person.
PersonFirstName Represents the first name of a person.
PersonFullName Represents the full name of a person.
PersonLastName Represents the surname (last name) of a person.
PersonMiddleName Represents the middle name of a person.
PhysicalColor Represents a color.
PhysicalDensity Represents density.
PhysicalDepth Represents depth.
PhysicalHeight Represents height.
PhysicalSize Represents a size.
PhysicalVolume Represents volume.
PhysicalWeight Represents weight.
PhysicalWidth Represents width.
Point Represents a point.
PostalCode Represents a postal code.
Product Represents a product.
ProductBrand Represents a product brand.
ProductCategory Represents a product category.
ProductGroup Represents a product group.
ProductSKU Represents a product stock keeping unit (SKU).
Project Represents a project.
ProjectCode Represents a project code.
ProjectCompletion Represents the completion status of a project.
ProjectEndDate Represents a project end date.
ProjectName Represents a project name.
ProjectStartDate Represents a project start date.
Promotion Represents a promotion.
QtyRangeHigh Represents the highest value of a range of quantities.
QtyRangeLow Represents the lowest value of a range of quantities.
Quantitative Represents a quantitative attribute.
Rate Represents a rate.
RateType Represents a rate type.
Region Represents a customer-defined region.
Regular Represents a regular attribute.
RelationToParent Represents a relation to a parent.
Representative Represents a representative.
Scenario Represents a scenario.
Sequence Represents a sequence attribute.
ShortCaption Represents a short caption.
StateOrProvince Represents a state or province.
Utility Represents a utility.
Version Represents a version.
WebHtml Represents HTML content.
WebMailAlias Represents an e-mail alias.
WebUrl Represents a URL address.
WebXmlOrXsl Represents XML or XSL content.

Account Dimension Attribute Types

Attribute Type Value Description
Account Represents the parent of an account. This attribute type is typically applied to the parent attribute of an account dimension.
AccountName Represents the name of an account. This attribute type is typically applied to the key attributes of an account dimension.
AccountNumber Represents the number of an account.
AccountType Represents the type of an account. This attribute type identifies the aggregation function of an account member in an account type dimension in the Analysis Services database.

Currency Dimension Attribute Types

Attribute Type Value Description
CurrencyDestination Represents the destination currency of a currency exchange. This attribute type is typically applied to the key attribute of a reporting dimension, for use in currency conversion. For more information about currency conversion, see Currency Conversions (Analysis Services).
CurrencyIsoCode Represents the International Standards Organization (ISO) code of a currency. For more information about currency conversion, see Currency Conversions (Analysis Services).
CurrencyName Represents the name of a currency. For more information about currency conversion, see Currency Conversions (Analysis Services).
CurrencySource Represents the source currency of a currency exchange. This attribute type is typically applied to the key attribute of a currency dimension, for use in currency conversion. For more information about currency conversion, see Currency Conversions (Analysis Services).

Slowly Changing Dimension Attribute Types

Attribute Type Value Description
ScdEndDate Represents the effective end date for a member in a slowly changing dimension.
ScdOriginalID Represents the original identifier for a member in a slowly changing dimension.
ScdStartDate Represents the effective start date for a member in a slowly changing dimension.
ScdStatus Represents the effective status of a member in a slowly changing dimension.

Time Dimension Attribute Types

Attribute Type Value Description
Date Represents a date. This attribute type is typically applied to the key attribute of a time dimension or server time dimension.
DayOfHalfYear Represents the day ordinal of a half-year.
DayOfMonth Represents the day ordinal of a month.
DayOfQuarter Represents the day ordinal of a quarter.
DayOfTenDays Represents the day ordinal of a ten-day period.
DayOfTrimester Represents the day ordinal of a trimester.
DayOfWeek Represents the day ordinal of a week.
DayOfYear Represents the day ordinal of a year.
Days Represents days.
FiscalDate Represents a date in a fiscal calendar.
FiscalDayOfHalfYear Represents the day ordinal of a half-year in a fiscal calendar.
FiscalDayOfMonth Represents the day ordinal of a month in a fiscal calendar.
FiscalDayOfQuarter Represents the day ordinal of a quarter in a fiscal calendar.
FiscalDayOfTrimester Represents the day ordinal of a trimester in a fiscal calendar.
FiscalDayOfWeek Represents the day ordinal of a week in a fiscal calendar.
FiscalDayOfYear Represents the day ordinal of a year in a fiscal calendar.
FiscalHalfYears Represents half-years in a fiscal calendar.
FiscalHalfYearOfYear Represents the half-year ordinal of a year in a fiscal calendar.
FiscalMonths Represents months in a fiscal calendar.
FiscalMonthOfHalfYear Represents the month ordinal of a half-year in a fiscal calendar.
FiscalMonthOfQuarter Represents the month ordinal of a quarter in a fiscal calendar.
FiscalMonthOfTrimester Represents the month ordinal of a trimester in a fiscal calendar.
FiscalMonthOfYear Represents the month ordinal of a year in a fiscal calendar.
FiscalQuarters Represents quarters in a fiscal calendar.
FiscalQuarterOfHalfYear Represents the quarter ordinal of a half-year in a fiscal calendar.
FiscalQuarterOfYear Represents the quarter ordinal of a year in a fiscal calendar.
FiscalTrimesters Represents trimesters in a fiscal calendar.
FiscalTrimesterOfYear Represents the trimester ordinal of a year in a fiscal calendar.
FiscalWeeks Represents weeks in a fiscal calendar.
FiscalWeekOfHalfYear Represents the week ordinal of a half-year in a fiscal calendar.
FiscalWeekOfMonth Represents the week ordinal of a month in a fiscal calendar.
FiscalWeekOfQuarter Represents the week ordinal of a quarter in a fiscal calendar.
FiscalWeekOfTrimester Represents the week ordinal of a trimester in a fiscal calendar.
FiscalWeekOfYear Represents the week ordinal of a year in a fiscal calendar.
FiscalYears Represents years in a fiscal calendar.
HalfYears Represents half-years.
HalfYearOfYear Represents the half-year ordinal of a year.
Hours Represents hours.
IsHoliday Indicates whether a date is a holiday.
ISO8601Date Represents a date in an ISO 8601 calendar.
ISO8601DayOfWeek Represents the day ordinal of a week in an ISO 8601 calendar.
ISO8601DayOfYear Represents the day ordinal of a year in an ISO 8601 calendar.
ISO8601Weeks Represents weeks in an ISO 8601 calendar.
ISO8601WeekOfYear Represents the week ordinal of a year in an ISO 8601 calendar.
ISO8601Years Represents years in an ISO 8601 calendar.
IsPeakDay Indicates whether a date is a peak day.
IsWeekDay Indicates whether a date is a weekday.
IsWorkingDay Indicates whether a date is a working day.
ManufacturingDate Represents a date in a manufacturing calendar.
ManufacturingDayOfHalfYear Represents the day ordinal of a half-year in a manufacturing calendar.
ManufacturingDayOfMonth Represents the day ordinal of a month in a manufacturing calendar.
ManufacturingDayOfQuarter Represents the day ordinal of a quarter in a manufacturing calendar.
ManufacturingDayOfTrimester Represents the day ordinal of a trimester in a manufacturing calendar.
ManufacturingDayOfWeek Represents the day ordinal of a week in a manufacturing calendar.
ManufacturingDayOfYear Represents the day ordinal of a year in a manufacturing calendar.
ManufacturingHalfYears Represents half-years in a manufacturing calendar.
ManufacturingHalfYearOfYear Represents the half-year ordinal of a year in a manufacturing calendar.
ManufacturingMonths Represents months in a manufacturing calendar.
ManufacturingMonthOfHalfYear Represents the month ordinal of a half-year in a manufacturing calendar.
ManufacturingMonthOfQuarter Represents the month ordinal of a quarter in a manufacturing calendar.
ManufacturingMonthOfTrimester Represents the month ordinal of a trimester in a manufacturing calendar.
ManufacturingMonthOfYear Represents the month ordinal of a year in a manufacturing calendar.
ManufacturingQuarters Represents quarters in a manufacturing calendar.
ManufacturingQuarterOfHalfYear Represents the quarter ordinal of a half-year in a manufacturing calendar.
ManufacturingQuarterOfYear Represents the quarter ordinal of a year in a manufacturing calendar.
ManufacturingWeeks Represents weeks in a manufacturing calendar.
ManufacturingWeekOfHalfYear Represents the week ordinal of a half-year in a manufacturing calendar.
ManufacturingWeekOfMonth Represents the week ordinal of a month in a manufacturing calendar.
ManufacturingWeekOfQuarter Represents the week ordinal of a quarter in a manufacturing calendar.
ManufacturingWeekOfTrimester Represents the week ordinal of a trimester in a manufacturing calendar.
ManufacturingWeekOfYear Represents the week ordinal of a year in a manufacturing calendar.
ManufacturingYears Represents years in a manufacturing calendar.
Minutes Represents minutes.
Months Represents months.
MonthOfHalfYear Represents the month ordinal of a half-year.
MonthOfQuarter Represents the month ordinal of a quarter.
MonthOfTrimester Represents the month ordinal of a trimester.
MonthOfYear Represents the month ordinal of a year.
Quarters Represents quarters.
QuarterOfHalfYear Represents the quarter ordinal of a half-year.
QuarterOfYear Represents the quarter ordinal of a year.
ReportingDate Represents a date in a reporting calendar.
ReportingDayOfHalfYear Represents the day ordinal of a half-year in a reporting calendar.
ReportingDayOfMonth Represents the day ordinal of a month in a reporting calendar.
ReportingDayOfQuarter Represents the day ordinal of a quarter in a reporting calendar.
ReportingDayOfTrimester Represents the day ordinal of a trimester in a reporting calendar.
ReportingDayOfWeek Represents the day ordinal of a week in a reporting calendar.
ReportingDayOfYear Represents the day ordinal of a year in a reporting calendar.
ReportingHalfYears Represents half-years in a reporting calendar.
ReportingHalfYearOfYear Represents the half-year ordinal of a year in a reporting calendar.
ReportingMonths Represents months in a reporting calendar.
ReportingMonthOfHalfYear Represents the month ordinal of a half-year in a reporting calendar.
ReportingMonthOfQuarter Represents the month ordinal of a quarter in a reporting calendar.
ReportingMonthOfTrimester Represents the month ordinal of a trimester in a reporting calendar.
ReportingMonthOfYear Represents the month ordinal of a year in a reporting calendar.
ReportingQuarters Represents quarters in a reporting calendar.
ReportingQuarterOfHalfYear Represents the quarter ordinal of a half-year in a reporting calendar.
ReportingQuarterOfYear Represents the quarter ordinal of a year in a reporting calendar.
ReportingTrimesters Represents trimesters in a reporting calendar.
ReportingTrimesterOfYear Represents the trimester ordinal of a year in a reporting calendar.
ReportingWeeks Represents weeks in a reporting calendar.
ReportingWeekOfHalfYear Represents the week ordinal of a half-year in a reporting calendar.
ReportingWeekOfMonth Represents the week ordinal of a month in a reporting calendar.
ReportingWeekOfQuarter Represents the week ordinal of a quarter in a reporting calendar.
ReportingWeekOfTrimester Represents the week ordinal of a trimester in a reporting calendar.
ReportingWeekOfYear Represents the week ordinal of a year in a reporting calendar.
ReportingYears Represents years in a reporting calendar.
Seconds Represents seconds.
TenDayOfHalfYear Represents the ten-day period ordinal of a half-year.
TenDayOfMonth Represents the ten-day period ordinal of a month.
TenDayOfQuarter Represents the ten-day period ordinal of a quarter.
TenDayOfTrimester Represents the ten-day period ordinal of a trimester.
TenDayOfYear Represents the ten-day period ordinal of a year.
TenDays Represents ten-day periods.
Trimesters Represents trimesters.
TrimesterOfYear Represents the trimester ordinal of a year.
UndefinedTime Represents an undefined time period.
WeekOfYear Represents the week ordinal of a year.
Weeks Represents weeks.
WinterSummerSeason Indicates whether the date is part of the winter/summer season.
Years Represents years.

Is SSAS instance Tabular or MultiDimensional

The easiest way to determine server mode is to connect to the server in SQL Server Management Studio and note the icon next to the server name in Object Explorer. The following illustration shows three instances of Analysis Services deployed in Multidimensional, Tabular, and Power Pivot modes:

Object Explorer icons for each server mode

The ServerMode property can tell us if its Multidimensional, SharePoint and Tabular values.

Reference: Determine the Server Mode of an Analysis Services Instance

Analysis Services SSAS Processing Architecture details and associated XMLA commands

Really nice article by T. K. Anand on SSAS processing

Below are some of the highlights in case, the msdn article stops working.

XMLA Commands for Processing

<Process>

The Process command tells the Analysis server to process an object. It has the following syntax:

<Process xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  
    Adventure Works DW
    Adventure Works DW
    Fact Internet Sales
    Internet_Sales
  
  <Type>ProcessFull</Type>
</Process>

<Batch>

The Batch command is a convenient way to send multiple commands to the server in a single XMLA request. It has the following syntax:

<Batch Transaction="true" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Process>
    
      Adventure Works DW
      Customer
    
    <Type>ProcessFull</Type>
  </Process>
  <Process>
    
      Adventure Works DW
      Product
    
    <Type>ProcessFull</Type>
  </Process>
  <Process>
    
      Adventure Works DW
      Adventure Works DW
    
    <Type>ProcessFull</Type>
  </Process>
</Batch>

<Parallel>

Batch commands are always executed sequentially. However, the Parallel command within a Batch allows you to specify multiple Process commands that are executed in parallel. Parallel is a special command in that it can only appear inside a Batch; it cannot be sent by itself. It cannot contain any command other than Process. It has the following syntax:

Following is an example of a Batch that processes two dimensions in parallel and then two partitions in parallel.

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Parallel>
    <Process>
      
        Adventure Works DW
        Customer
      
      <Type>ProcessFull</Type>
    </Process>
    <Process>
      
        Adventure Works DW
        Product
      
      <Type>ProcessFull</Type>
    </Process>
  </Parallel>
  <Parallel>
    <Process>
      
        Adventure Works DW
        Adventure Works DW
        Fact Internet Sales
        Internet_Sales_2001
      
      <Type>ProcessFull</Type>
    </Process>
    <Process>
      
        Adventure Works DW
        Adventure Works DW
        Fact Internet Sales
        Internet_Sales_2002
      
      <Type>ProcessFull</Type>
    </Process>
  </Parallel>
</Batch>

Processing Options

The Type element in the Process command specifies the type of processing to be done. This is also referred to as the “processing option”. Choosing the right processing option is very important since it typically affects large volumes of data. This section describes the different processing options for each type of object.

Following are the types of Analysis Services objects that can be processed:

  • Database
  • Dimension
  • Cube
  • Measure group
  • Partition
  • Mining structure
  • Mining model

Before we look at the processing options, we first need to understand what exactly processing is doing. Processing generally sends queries to the relational data source and uses the results to populate the Analysis Services object. Following are the storage contents that are built by processing for each object type. A complete discussion of the Analysis server storage format is beyond the scope of this document.

  • Database
    • None (a database is just a container of dimensions, cubes, and mining structures; it has no data storage of its own)
  • Dimension
    • Attribute stores
    • Hierarchy stores
    • Bitmap indexes
  • Cube
    • MDX Script cache
  • Measure group
    • None (a measure group is just a container of partitions; it has no data storage of its own)
  • Partition
    • Fact data
    • Aggregation data
    • Bitmap indexes
  • Mining structure
    • Training data
  • Mining model
    • Algorithm content

When an object is processed, the processing option specifies what part of the storage content of the object is to be built and how. The following table contains the list of all processing options and which objects they apply to.

Processing Option Database Dimension Cube Measure Group Partition Mining Structure Mining Model
ProcessFull X X X X X X X
ProcessClear X X X X X X X
ProcessDefault X X X X X X X
ProcessData X X X X
ProcessIndexes X X X X
ProcessUpdate X
ProcessAdd X X
ProcessStructure X X
ProcessScriptCache X
ProcessClearStructureOnly X

ProcessFull

ProcessFull applies to all objects. It discards the storage contents of the object and and rebuilds them. ProcessFull is recursively applied to all descendants of the object as well.

ProcessClear

ProcessClear applies to all objects. It discards the storage contents of the object. ProcessClear is recursively applied to all descendants of the object as well.

ProcessDefault

ProcessDefault applies to all objects. It does the bare minimum required to bring the object to a fully processed state. In other words, it builds only the storage contents that are not currently built. For example, if a partition has fact and aggregation data, then ProcessDefault will only build the bitmap indexes.

The only exception to the above rule is bindings. When you change the bindings of an object (e.g., KeyColumns of a dimension attribute), the object retains its data, but remembers that the bindings have changed. ProcessDefault on the object will discard all the storage contents and rebuild them.

The server handles ProcessDefault by analyzing the object and dynamically converting it to another processing option such as ProcessFull, ProcessIndexes, etc. ProcessDefault is recursively applied to all descendants of the object as well.

ProcessData

ProcessData applies only to the OLAP objects, i.e. dimension, cube, measure group and partition. It discards the storage contents of the object and rebuilds only the “data”. For dimensions, it builds only the attribute and hierarchy stores. For partitions, it builds only the fact data.

Essentially ProcessData builds the bare minimum required for the object to be available for queries. Indexes are considered optional and affect only the query performance. ProcessData is recursively applied to all descendants of the object as well.

ProcessIndexes

ProcessIndexes applies only to the OLAP objects, i.e., dimension, cube, measure group, and partition. It requires that the object must already have its “data” built; otherwise, it raises an error. ProcessIndexes preserves the data and rebuilds the “indexes”. For dimensions, it builds the bitmap indexes. For partitions, it builds the aggregation data and bitmap indexes. ProcessIndexes is recursively applied to all descendants of the object as well.

ProcessUpdate

ProcessUpdate applies only to dimensions. It is the equivalent of incremental dimension processing in Analysis Services 2000. It sends SQL queries to read the entire dimension table and applies the changes—member updates, additions, deletions.

Since ProcessUpdate reads the entire dimension table, it begs the question, “How is it different from ProcessFull?” The difference is that ProcessUpdate does not discard the dimension storage contents. It applies the changes in a “smart” manner that preserves the fact data in dependent partitions. ProcessFull, on the other hand, does an implicit ProcessClear on all dependent partitions. ProcessUpdate is inherently slower than ProcessFull since it is doing additional work to apply the changes.

Depending on the nature of the changes in the dimension table, ProcessUpdate can affect dependent partitions. If only new members were added, then the partitions are not affected. But if members were deleted or if member relationships changed (e.g., a Customer moved from Redmond to Seattle), then some of the aggregation data and bitmap indexes on the partitions are dropped. The cube is still available for queries, albeit with lower performance.

ProcessAdd

ProcessAdd applies only to dimensions and partitions.

ProcessAdd is a new processing option for dimensions that did not exist in Analysis Services 2000. It essentially optimizes ProcessUpdate for the scenario where only new members are added. ProcessAdd never deletes or updates existing members. It only adds new members. The user can restrict the dimension table so that ProcessAdd reads only the new rows.

ProcessAdd for partitions is the equivalent of incremental partition processing in Analysis Services 2000. The user typically specifies an alternate fact table or a filter condition pointing to the new rows. ProcessAdd internally creates a temporary partition, processes it with the specified fact data, and merges it into the target partition.

 

ProcessStructure

ProcessStructure applies only to cubes and mining structures.

ProcessStructure for cubes is the equivalent of the Analysis Services 2000 processing option, processBuildStructure, in DSO. It discards the storage contents of the cube and its partitions. It implicitly does a ProcessDefault on all dimensions of the cube and marks the cube as processed. At this point, the cube is available to queries but it will not return any fact data. This is supported mostly for backward-compatibility reasons. It was useful in Analysis Services 2000 for parallel processing utilities. Once the “structure” of a cube is processed, its partitions can be processed in parallel by multiple client sessions without running into locking conflicts.

ProcessStructure on a mining structure discards its storage contents (training data) and rebuilds them. It does not affect the contents of the mining models under the mining structure.

ProcessScriptCache

ProcessScriptCache applies only to cubes. The MDX script in a cube can contain CACHE statements. ProcessScriptCache evaluates the script and persists the results for the CACHE statements.

ProcessClearStructureOnly

ProcessClearStructureOnly applies only mining structures. It clears the storage contents (training data) of the mining structure while preserving the contents of its mining models.

Dependencies and Impact Analysis

Processing an object can produce side effects by affecting the storage contents of other objects. To understand this better, we need to first list the dependencies between objects.

  • Database
    • None
  • Dimension
    • A data-mining dimension depends on its source-mining model.
  • Cube
    • A cube depends on the dimensions that it uses.
  • Measure group
    • A measure group depends on the dimensions that it uses (typically a subset of the dimensions used by the parent cube).
    • A linked measure group depends on its source measure group.
  • Partition
    • A partition depends on the same dimensions as its parent measure group.
  • Mining structure
    • An OLAP mining structure depends on its source cube.
  • Mining model
    • An OLAP mining model depends on its source cube.

The following table lists the side effects produced by each processing option for each object type. Since ProcessDefault is dynamically converted to another processing option, no side effects are listed for it.

Object Type Processing Option Objects that it depends on Object that depend on it
Database ProcessFull
ProcessClear
Dimension ProcessFull ProcessDefault ProcessClear
ProcessClear ProcessClear
ProcessData ProcessDefault ProcessClear
ProcessIndexes
ProcessUpdate Clear affected indexes and aggregations on partitions
ProcessAdd
Cube ProcessFull ProcessDefault ProcessClearStructureOnly
ProcessClear ProcessClearStructureOnly
ProcessData ProcessDefault ProcessClearStructureOnly
ProcessIndexes
ProcessStructure ProcessDefault ProcessClearStructureOnly
ProcessScriptCache
Measure Group ProcessFull ProcessDefault ProcessClearStructureOnly / ProcessFull *
ProcessClear ProcessClearStructureOnly / ProcessClear
ProcessData ProcessDefault ProcessClearStructureOnly / ProcessFull
ProcessIndexes
Partition ProcessFull ProcessDefault ProcessClearStructureOnly / ProcessFull
ProcessClear ProcessClearStructureOnly / ProcessFull
ProcessData ProcessDefault ProcessClearStructureOnly / ProcessFull
ProcessIndexes
ProcessAdd ProcessClearStructureOnly / ProcessFull
Mining Structure ProcessFull ProcessDefault
ProcessClear
ProcessStructure ProcessDefault
ProcessClearStructureOnly
Mining Model ProcessFull ProcessDefault ProcessClear
ProcessClear ProcessClear

* ProcessClearStructureOnly is applied to OLAP mining structures. ProcessFull or ProcessClear is applied to linked measure groups.

From the above table, we see that when an object is processed, it can have an adverse effect (loss of data, aggregations, indexes, etc.) on other objects that depend on it. The ProcessAffectedObjects attribute on the Batch command tells the server to reprocess these objects using the ProcessDefault option. This is a convenient way to process an object and ensure that other objects are not adversely affected.

Out of Line Bindings

The Bindings, DataSource and DataSourceView elements in the <Process> and <Batch> commands are collectively referred to as “out of line bindings”. This is an advanced processing feature that provides the ability to specify or override the bindings of an object at processing time. Note that the object does not persist or remember these bindings after the processing has completed. The bindings need to be re-specified in every processing command as appropriate.

Following are some scenarios where out of line bindings are useful.

ProcessAdd

ProcessAdd on a partition is the operation in which new fact table rows are added to the partition. It is important to identify the new rows by specifying an alternate fact table or a filter condition. This is done using out of line bindings. The following command (scripted out from the Process dialog) illustrates this.

<Process xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  
    Adventure Works DW
    Adventure Works DW
    Fact Internet Sales
    Internet_Sales_2004
  
  <Type>ProcessAdd</Type>
  <Bindings>
    <Binding>
      <DatabaseID>Adventure Works DW</DatabaseID>
      <CubeID>Adventure Works DW</CubeID>
      <MeasureGroupID>Fact Internet Sales</MeasureGroupID>
      <PartitionID>Internet_Sales_2004</PartitionID>
      <Source xsi:type="QueryBinding">
        <DataSourceID>Adventure Works DW</DataSourceID>
        <QueryDefinition>SELECT * FROM [dbo].[FactInternetSales] WHERE OrderDateKey &gt; '1280'</QueryDefinition>
      </Source>
    </Binding>
  </Bindings>
</Process>

ProcessAdd on dimensions is similar in concept but more complicated in practice. Unlike partitions, dimensions can be built out of multiple (snowflaked) tables. Hence the Dimension object does not have a single <Source> property that can be overridden in the out of line bindings. The only way to do this is to specify an out of line DataSourceView in which the dimension table is filtered appropriately. The following command illustrates this. Details of the DataSourceView are omitted in the interest of clarity.

<Process xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Type>ProcessAdd</Type>
  
    Adventure Works DW
    Adventure Works DW
    Fact Internet Sales 1
    Internet_Sales_2004
  
  <DataSource xsi:type="PushedDataSource">
    <root Parameter="InputRowset"/>
    <EndOfData Parameter="EndOfInputRowset"/>
  </DataSource>
  <Bindings>
    <Binding>
      <DatabaseID>Adventure Works DW</DatabaseID>
      <CubeID>Adventure Works DW</CubeID>
      <MeasureGroupID>Fact Internet Sales</MeasureGroupID>
      <CubeDimensionID>Dim Product</CubeDimensionID>
      <AttributeID>Product Name</AttributeID>
      <KeyColumns>
        <KeyColumn>
          <Source xsi:type="ColumnBinding">
            <TableID/>
            <ColumnID>ProductKey</ColumnID>
          </Source>
        </KeyColumn>
      </KeyColumns>
    </Binding>
    ...
    <Binding>
      <DatabaseID>Adventure Works DW</DatabaseID>
      <CubeID>Adventure Works DW</CubeID>
      <MeasureGroupID>Fact Internet Sales</MeasureGroupID>
      <MeasureID>Sales Amount</MeasureID>
      <Source xsi:type="ColumnBinding">
        <TableID/>
        <ColumnID>SalesAmount</ColumnID>
      </Source>
    </Binding>
    ...
  </Bindings>
</Process>

Push-Mode Processing

Processing is usually done by Analysis Services in pull-mode, i.e., the Analysis server pulls the data out of the relational data source and populates the cube. Push-mode processing is the opposite where the client pushes the data into the Analysis server. Push-mode processing is done by sending a rowset as a parameter to the <Process> command. The rowset itself can be chunked across multiple <Process> requests. Out of line bindings are required for push-mode processing in order to map the columns of the rowset parameter to the object being processed.

The Integration Services Partition Processing Data Flow Item uses chunked push-mode processing to pump data into a partition. The following command (extracted from Profiler during an Integration Services package execution) illustrates push-mode processing. The PushedDataSource references two parameters, InputRowset and EndOfInputRowset. The InputRowset parameter contains the rowset chunk in the XMLA rowset format. The EndOfInputRowset parameter is a boolean that indicates if this is the last chunk.

<Process xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Type>ProcessAdd</Type>
  
    Adventure Works DW
    Adventure Works DW
    Fact Internet Sales 1
    Internet_Sales_2004
  
  <DataSource xsi:type="PushedDataSource">
    <root Parameter="InputRowset"/>
    <EndOfData Parameter="EndOfInputRowset"/>
  </DataSource>
  <Bindings>
    <Binding>
      <DatabaseID>Adventure Works DW</DatabaseID>
      <CubeID>Adventure Works DW</CubeID>
      <MeasureGroupID>Fact Internet Sales</MeasureGroupID>
      <CubeDimensionID>Dim Product</CubeDimensionID>
      <AttributeID>Product Name</AttributeID>
      <KeyColumns>
        <KeyColumn>
          <Source xsi:type="ColumnBinding">
            <TableID/>
            <ColumnID>ProductKey</ColumnID>
          </Source>
        </KeyColumn>
      </KeyColumns>
    </Binding>
    ...
    <Binding>
      <DatabaseID>Adventure Works DW</DatabaseID>
      <CubeID>Adventure Works DW</CubeID>
      <MeasureGroupID>Fact Internet Sales</MeasureGroupID>
      <MeasureID>Sales Amount</MeasureID>
      <Source xsi:type="ColumnBinding">
        <TableID/>
        <ColumnID>SalesAmount</ColumnID>
      </Source>
    </Binding>
    ...
  </Bindings>
</Process>

Integration Services

SQL Server Integration Services is a high performance ETL (Extraction, Transformation, and Load) component that can be used for a variety of data transformation and movement tasks. Integration Services can be used for automating and scheduling complex data warehouse maintenance operations.

Processing is a typical maintenance operation in a data warehouse and Integration Services offers two mechanisms for processing Analysis Services objects.

Analysis Services Processing Task

The Analysis Services Processing Task connects to the specified Analysis server and processes one or more objects with the specified processing options. It can be included in an Integration Services package workflow along with other data warehouse maintenance tasks such as data cleansing, transformation, etc.

ms345142.sql2k5_asprocarch_4(en-US,SQL.90).gif

Analysis Services Data Flow Items

Integration Services offers three Analysis Services Data Flow Items:

  • Partition Processing
  • Dimension Processing
  • Data Mining Model Training

These items can be included in an Integration Services Data Flow task as a destination. The package designer chooses the destination object and maps the columns of the data flow to it. When the task is executed, the data flows through the pipeline and into the object on the Analysis server. This is accomplished using the chunked push-mode processing mechanism in Analysis Services.

The following pictures show the editor for the partition-processing item. The dimension processing and data-mining model training items are similar.

ms345142.sql2k5_asprocarch_5(en-US,SQL.90).gif

ms345142.sql2k5_asprocarch_6(en-US,SQL.90).gif

SSAS – Errors in the back-end database access module. The size specified for a binding was too small, resulting in one or more column values being truncated

The key is to find out which attribute of which dimension is giving the error:

Errors in the back-end database access module. The size specified for a binding was too small, resulting in one or more column values being truncated.

Usually when through SQL Server agent, it will be the first one.

Using SSMS do the processing and navigate to the dimension processing errors and find out which attribute of which dimension is this error associated with.

Once we know that we should be able to fix it by:

  • Increasing the value of the property called DataSize for the attribute under both NameColumn and KeyColumns.
  • Associate if any of the NameColumn or KeyColumns, is missing

MSDN Reference