Analysis Services Stored Procedure Project

Updated Wiki: Home

furmangg — Tue, 24 Jun 2008 13:46:37 GMT

ASSP - Analysis Services Stored Procedure Project

The Analysis Services Stored Procedure project is a set of sample stored procedures for Analysis Services 2005. These samples have been written in C# and sample MDX queries are included that demonstrate the use of the procedures against the Adventure Works DW sample database. It was developed by a group of community volunteers with 2 main aims in mind:

To provide a set of useful extensions to Analysis Services 2005.
To provide a variety of example source code for people looking to write their own stored procedures.

The project currently contains the following samples:

Class	Permissions	Version	Functions
AsymmetricSet	S	1.0	AsymmetricSet
CellTimings	S	1.0	TimeToCalculate
CubeInfo	U	1.0	GetCubeLastProcessedDate
EfficientToDate	S	1.0	GetEfficientPeriodsToDateSet, GetMostGranularHierarchyCurrentMember
FindCurrentMembers	S	1.0	FindCurrentMember, FindCurrentMemberVerbose, FindCurrentType
Multiplication	S	1.0	Multiply
StringFilters	S	1.0	RegExFilter, Like
ListFunctions	U	1.1	ListFunctions
SetOperations	S	1.1	Order, ReverseSet, RandomSample, InverseHierarchility, AsymmetricDrillDown
Parallel	S	1.1	Parallel
LinkMember	S	1.1	HierarchyLinkMember, LevelLinkMember
ClusterNaming	U	1.1	AutoNameClusters, DistinguishingCharacteristicsForClusters
Partition	U	1.1	CreatePartitions
Partition	U	Future	CreateDistinctCountPartitions, CreateStringDistinctCountPartitions
XmlaDiscover	U	1.1	Discover, ClearCache, Cancel, DiscoverXmlMetaData
XmlaDiscover	U	1.2	DMV
MemoryUsage	U	1.1	SnapshotMemoryUsageTotals
DimensionHealthCheck	U	1.1	ListDimensionErrors, ListDimensionsWithErrors
StrToSet	S	1.2	KeysStrToSet, CompositeKeysStrToSet
PartitionHealthCheck	U	1.2	DiscoverPartitionSlices

Permissions: denotes the minimum required permissions level for the function in the class to work. If you deploy the assembly with a lower permission level than that required by a given function, that function will simply throw a security exception when you attempt to execute it, all the other functions will execute normally.

S - Safe
U - Unrestricted

Version: denotes the initial release version for the class.

For details on how to install the compiled release see the Installation Instructions

For a list of other resources and links see:

Additional Resources

Updated Wiki: Home

furmangg — Mon, 23 Jun 2008 23:24:48 GMT

ASSP - Analysis Services Stored Procedure Project

To provide a set of useful extensions to Analysis Services 2005.
To provide a variety of example source code for people looking to write their own stored procedures.

The project currently contains the following samples:

Class	Permissions	Version	Functions
AsymmetricSet	S	1.0	AsymmetricSet
CellTimings	S	1.0	TimeToCalculate
CubeInfo	U	1.0	GetCubeLastProcessedDate
EfficientToDate	S	1.0	GetEfficientPeriodsToDateSet, GetMostGranularHierarchyCurrentMember
FindCurrentMembers	S	1.0	FindCurrentMember, FindCurrentMemberVerbose, FindCurrentType
Multiplication	S	1.0	Multiply
StringFilters	S	1.0	RegExFilter, Like
ListFunctions	U	1.1	ListFunctions
SetOperations	S	1.1	Order, ReverseSet, RandomSample, InverseHierarchility, AsymmetricDrillDown
Parallel	S	1.1	Parallel
LinkMember	S	1.1	HierarchyLinkMember, LevelLinkMember
ClusterNaming	U	1.1	AutoNameClusters, DistinguishingCharacteristicsForClusters
Partition	U	1.1	CreatePartitions, CreateDistinctCountPartitions, CreateStringDistinctCountPartitions
XmlaDiscover	U	1.1	Discover, ClearCache, Cancel, DiscoverXmlMetaData
XmlaDiscover	U	1.2	DMV
MemoryUsage	U	1.1	SnapshotMemoryUsageTotals
DimensionHealthCheck	U	1.1	ListDimensionErrors, ListDimensionsWithErrors
StrToSet	S	1.2	KeysStrToSet, CompositeKeysStrToSet
PartitionHealthCheck	U	1.2	DiscoverPartitionSlices

S - Safe
U - Unrestricted

Version: denotes the initial release version for the class.

For details on how to install the compiled release see the Installation Instructions

For a list of other resources and links see:

Additional Resources

Updated Wiki: Partition

furmangg — Mon, 23 Jun 2008 23:16:15 GMT

Partition

The CreatePartitions function allows an Analysis Services administrator to quickly prototype various partitioning schemes. For example, you could quickly build one partition per year by running the following:

call ASSP.CreatePartitions("Adventure Works", "Internet Sales", "[Date].[Calendar Year].[Calendar Year].Members")

Or you could do more complex partitioning schemes. If you wanted to partition by years through 2003 then months after that, you could run the following:

call ASSP.CreatePartitions("Adventure Works", "Internet Sales", "{null:[Date].[Calendar].[CY 2003]} + {[Date].[Calendar].[January 2004]:null}")

Or you could partition on year and product category:

call ASSP.CreatePartitions("Adventure Works", "Internet Sales", "[Date].[Calendar Year].[Calendar Year].Members*[Product].[Category].[Category].Members")

Or you could partition in a more ad-hoc manner by creating your own groupings. For instance, if you wanted to partition by the first character of the product name, you could run:

call ASSP.CreatePartitions("Adventure Works", "Internet Sales", "[Product].[Product].[Product].Members", "Left([Product].[Product].CurrentMember.Name,1)")

Finally, if you want to go back to a single partition, you can run the following:

call ASSP.CreatePartitions("Adventure Works", "Internet Sales", "[Date].[Calendar Year].[All Periods]")

The signature of this function is:

CreatePartitions(CubeName, MeasureGroupName, SetString, [PartitionGrouperExpressionString])

Run this function from the database containing the cube you are trying to modify. The first parameter (CubeName) is a string specifying which cube to modify. The second parameter (MeasureGroupName) is a string specifying the measure group to modify. The third parameter (SetString) is a string which is a valid MDX set expression. For each tuple in that set, one partition will be created (unless a fourth parameter is specified). The optional fourth parameter (PartitionGrouperExpressionString) is a string which is a valid MDX string expression. If specified, the tuples will be grouped according to the value of this expression such that one partition will be built per unique grouper value.

Distinct Count Partitioning

Note: The distinct count partitioning is not in the current release of ASSP. You must download the source code from the Source Code tab and compile it yourself.

ASSP also allows you to partition distinct count measure groups in a manner that will achieve optimal performance by creating partitions which have non-overlapping ranges of distinct count values. This segmentation can be done on top of any partitioning mentioned above.

You could partition by year then further partition into 4 sub-partitions automatically based on the range of data currently in the dimension that corresponds to the distinct count measure (i.e. the Customer dimension in this Adventure Works example).

call ASSP.CreateDistinctCountPartitions("Adventure Works", "Internet Customers", "[Date].[Calendar Year].[Calendar Year].Members", "", 4)

where CustomerKey <= 15620
where CustomerKey between 15621 and 20240
where CustomerKey between 20241 and 24860
where CustomerKey >= 24861

Or you could specify the range of int/bigint values and then subdivide that range into 3 equal segments:

call ASSP.CreateDistinctCountPartitions("Adventure Works", "Internet Customers", "[Date].[Calendar Year].[Calendar Year].Members", "", 3, 10000, 30000)

where CustomerKey <= 16666
where CustomerKey between 16667 and 23332
where CustomerKey >= 23333

The signature of this function is:

CreateDistinctCountPartitions(CubeName, MeasureGroupName, SetString, PartitionGrouperExpressionString, NumSubPartitions, [MinDistinctValue], [MaxDistinctValue])

Run this function from the database containing the cube you are trying to modify. The first parameter (CubeName) is a string specifying which cube to modify. The second parameter (MeasureGroupName) is a string specifying the measure group to modify. The third parameter (SetString) is a string which is a valid MDX set expression. For each tuple in that set, one partition will be created (unless a fourth parameter is specified). If only wanting to partition on the distinct count value, simply specify an All member. The fourth parameter (PartitionGrouperExpressionString) is a string which is a valid MDX string expression. If specified (i.e. if not an empty string), the tuples will be grouped according to the value of this expression such that one partition will be built per unique grouper value. The fifth parameter (NumSubPartitions) specifies how many further sub-partitions to divide each previous partition into based on non-overlapping ranges of distinct count values. The optional sixth and seventh parameters (MinDistinctValue and MaxDistinctValue) let you specify the range of distinct count values which will be used instead of having ASSP scan the dimension that corresponds to the distinct count measure to determine the range.

Restriction: To use CreateDistinctCountPartitions, your distinct count measure must be an int or bigint (or the unsigned equivalent) data type. If you do not specify the MinDistinctValue and MaxDistinctValue parameters, your measure group must have a dimension tied to the column that the distinct count measure is based upon. ASSP will scan this dimension to determine the min and max distinct count value. (Actually, to ensure good performance, it only scans the first and last million members looking for the min and max value, respectively.)

You can also do distinct count partitioning based on strings by specifying the boundary values. This example partitions by year, then further partitions into 3 sub-partitions:

where SalesOrderNumber < 'SO49410'
where SalesOrderNumber >= 'SO49410' and SalesOrderNumber < 'SO69410'
where SalesOrderNumber >= 'SO69410'

call ASSP.CreateStringDistinctCountPartitions("Adventure Works", "Internet Orders", "[Date].[Calendar Year].[Calendar Year].Members", "", "SO49410", "SO69410")

The signature of this function is:

CreateStringDistinctCountPartitions(CubeName, MeasureGroupName, SetString, PartitionGrouperExpressionString, BoundaryValue 1 - 9)

Run this function from the database containing the cube you are trying to modify. The first parameter (CubeName) is a string specifying which cube to modify. The second parameter (MeasureGroupName) is a string specifying the measure group to modify. The third parameter (SetString) is a string which is a valid MDX set expression. For each tuple in that set, one partition will be created (unless a fourth parameter is specified). If only wanting to partition on the distinct count value, simply specify an All member. The fourth parameter (PartitionGrouperExpressionString) is a string which is a valid MDX string expression. If specified (i.e. if not an empty string), the tuples will be grouped according to the value of this expression such that one partition will be built per unique grouper value. The subsequent parameters provide the boundary values for sub-partitioning on the distinct count column. You may specify one to nine boundary values meaning you can have up to ten distinct count sub-partitions.

Notes

General Note: The ASSP assembly must be registered with unrestricted permissions for the CreatePartitions/CreateDistinctCountPartitions/CreateStringDistinctCountPartitions function to succeed.

Important Note: You may want to backup your cube before running this code as it deletes all the existing partitions in the measure group you are partitioning.

Best Practices:

This function is not intended to be used for more than prototyping and quickly testing various partitioning schemes.
This function deletes existing partitions and creates new ones matching the partitioning scheme you have specified. After the function completes, you need to right click on the measure group in Management Studio and do a Full Process to process all these new partitions. It should probably not be used in a production environment because running this function will cause you to have to reprocess all partitions in the measure group.
Be sure that the set you specify as the third parameter exactly covers 100% of the fact table. No tuples in that set should contain duplicate data. Nor should there be any fact rows which do not appear in one of those tuples.
This function does not provide any mechanisms for planning for members which will be added in the future. For instance, when you arrive at a new year and add a new Year member to your Date dimension, if you have partitioned by year using this function, no partition for that new year will already exist.
The cube you are partitioning must be processed before you run the CreatePartitions/CreateDistinctCountPartitions/CreateStringDistinctCountPartitions function. At a minimum, run a Process Structure command on the cube to get it in the "processed" state.

Source code checked in, #34097

furmangg — Mon, 23 Jun 2008 22:38:40 GMT

adding CreateDistinctCountPartitions and CreateStringDistinctCountPartitions functions

CLOSED ISSUE: String filter 'like' issue

dgosbell — Thu, 29 May 2008 21:44:25 GMT

If 'like' filter is passed string - "[Test]%" then matching fails. I think '[',']' needs to be escaped in LikeToRegEx fn.
Comments: Original poster says that this is not an issue

COMMENTED ISSUE: String filter 'like' issue

dgosbell — Thu, 29 May 2008 21:43:34 GMT

If 'like' filter is passed string - "[Test]%" then matching fails. I think '[',']' needs to be escaped in LikeToRegEx fn.
Comments: ** Comment from web user: dgosbell **

Since you say this is not an issue I will close it.

If you want to ask a question, the discussions tab would be the place to ask. Also when you download the project there should be some example .mdx files in there and there is one for the string filters that has examples that work against the Adventure Works sample database

COMMENTED ISSUE: String filter 'like' issue

chandra — Thu, 29 May 2008 20:39:08 GMT

If 'like' filter is passed string - "[Test]%" then matching fails. I think '[',']' needs to be escaped in LikeToRegEx fn.
Comments: ** Comment from web user: chandra **

Ah, I realized this is not a issue.
And this may be not the right place to ask question like 'how to use wildcard characters in pattern'.

CREATED ISSUE: String filter 'like' issue

chandra — Thu, 29 May 2008 17:42:37 GMT

If 'like' filter is passed string - "[Test]%" then matching fails. I think '[',']' needs to be escaped in LikeToRegEx fn.

NEW POST: DISCOVER_MEMORYUSAGE

furmangg — Mon, 10 Mar 2008 19:00:03 GMT

LowMemoryLimit and TotalMemoryLimit are SSAS server settings that are used to put an upper limit on memory usage by SSAS. So in that sense, the data in the MemoryUsage cube should never be higher than those settings. But actual SSAS memory usage will usually be below LowMemoryLimit.

NEW POST: DISCOVER_MEMORYUSAGE

kskistad — Sat, 08 Mar 2008 14:02:58 GMT

Yes, sorry I shouldn't have said "it ran fine" because I put the sproc call in a sql agent job, and it continued to populate the cube with data, so I assumed it was working, when in fact it cause the error every time it ran.

I got the latest code from codeplex and compiled it and that seemed to fix my out of memory error for some reason.

Does some of the data align with the LowMemoryLimit and TotalMemoryLimit configuration options?

NEW POST: DISCOVER_MEMORYUSAGE

furmangg — Fri, 07 Mar 2008 09:06:48 GMT

Didn't you get an OutOfMemory exception when you ran it?
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2956414&SiteID=1

If so, let us know how it looks after you're able to get it enough memory to succeed.

CLOSED ISSUE: Memory Usage Issue

furmangg — Thu, 06 Mar 2008 23:55:14 GMT

When I execute the procedure, I get:

Execution of the managed stored procedure SnapshotMemoryUsageTotals failed with the following error: Exception has been thrown by the target of an invocation.Exception of type 'System.OutOfMemoryException' was thrown..

This happens about 17 secs into the process. When I look at the cube, it appears data has been populated for the new snapshot, but the "Disk Used" is always null. All other measures have new values.
Comments: won't fix

COMMENTED ISSUE: Memory Usage Issue

furmangg — Thu, 06 Mar 2008 23:55:03 GMT

It's out of memory. Not much we can do about that without completely rearchitecting how the sproc uses resources, which I don't think is feasible.

I posted some suggestions for troubleshooting or resolving the low memory situation here:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2956414&SiteID=1

NEW POST: DISCOVER_MEMORYUSAGE

kskistad — Thu, 06 Mar 2008 22:58:15 GMT

I tried using the MemoryUsage sproc, which seems to work fine, but now I am struggling with deciphering what it all means. My total memory is around 200Mb throughout the day, but when I look at the msmdsrv.exe process, it is at 1.7G shouldn't these numbers be roughly close?

CREATED ISSUE: Memory Usage Issue

kskistad — Thu, 06 Mar 2008 17:58:06 GMT

UPDATED WIKI: Additional Resources

furmangg — Thu, 21 Feb 2008 18:32:17 GMT

Additional Resources

For instructions on creating and registering Analysis Services stored procedures, see the Books Online topic Creating Stored Procedures.

For information on using stored procedures in conjunction with association rules data mining to perform social network analysis, see the whitepaper and download the code by John C. Hancock.

For several other data mining related stored procs, see Jamie MacLennan's blog and the sqlserverdatamining.com site:

Here is one that's useful for finding the shortest or longest path to a leaf node in a decision tree.
Here is one that builds the DMX CREATE MINING MODEL statement for an existing model.
Here is one that calculates the goodness of fit of a regression model.
Here is one that calculates covariance/correlation matrixes.
Here is one that creates a data source object.
Here is one that extracts information from a logistic regression model.

For an example of how to use a config file to store settings for Analysis Services stored procs, see a blog entry by Teo Lachev.

For some performance tips and other sproc best practices, see a blog entry by Mosha Pasumansky.

For information about debugging sprocs, see a blog entry by Darren Gosbell.

Source code checked in

cwebb — Fri, 08 Feb 2008 22:34:21 GMT

Source code checked in

cwebb — Fri, 08 Feb 2008 22:33:08 GMT

UPDATED WIKI: Additional Resources

furmangg — Mon, 04 Feb 2008 15:43:54 GMT

Additional Resources

Here is one that's useful for finding the shortest or longest path to a leaf node in a decision tree.
Here is one that builds the DMX CREATE MINING MODEL statement for an existing model.
Here is one that calculates the goodness of fit of a regression model.
Here is one that calculates covariance/correlation matrixes.
Here is one that creates a data source object.

Source code checked in

cwebb — Mon, 28 Jan 2008 21:39:55 GMT

Adding first draft of WriteToProfiler