Analysis Services Stored Procedure Project

Source code checked in, #55091

dgosbell — Fri, 19 Jun 2009 10:35:14 GMT

Moving SQLQuery to it's own project

Updated Wiki: SQLQuery

dgosbell — Fri, 19 Jun 2009 09:55:44 GMT

ExecuteSQL *

This function allows for the execution of an arbitrary SQL query, probably most useful for calling from a rowset action.

This routine would be a useful starting point for developing a custom rowset action, possibly integrating it with code from FindCurrentMembers to generate some SQL based on the current context.

ExecuteSQL( connection string , SQL ) *

The first parameter is the connection string to be used, any OLEDB provider can be used, the second parameter is the text of the SQL query.

A user can execute any SQL statement for that their SQL login would normally have the rights to execute.

NOTE: This code has been placed in a separate assembly and it should be deployed with the default option of impersonating the current user. If you deploy with the option of impersonating the Service Account there is a risk that the SQL statements could be executed with elevated privileges depending on the rights that have been given to the service account.

call SQLQuery.ExecuteSql(
    "provider=sqlncli;server=localhost;database=AdventureWorksDW;trusted_connection=yes"
    ,"Create TABLE myTable (id int)");
GO
call SQLQuery.ExecuteSql(
    "provider=sqlncli;server=localhost;database=AdventureWorksDW;trusted_connection=yes"
    ,"INSERT INTO myTable VALUES(1)");
GO
call SQLQuery.ExecuteSql(
    "provider=sqlncli;server=localhost;database=AdventureWorksDW;trusted_connection=yes"
    ,"SELECT * FROM myTable");
GO
call SQLQuery.ExecuteSql(
    "provider=sqlncli;server=localhost;database=AdventureWorksDW;trusted_connection=yes"
    ,"DROP TABLE myTable");

Updated Wiki: SQLQuery

dgosbell — Thu, 18 Jun 2009 21:22:14 GMT

ExecuteSQL *

ExecuteSQL( connection string , SQL ) *

The first parameter is the connection string to be used, any OLEDB provider can be used, the second parameter is the text of the SQL query.

A user can execute any SQL statement for that their SQL login would normally have the rights to execute.

NOTE: It is recommended that the assembly containing this routine is only ever deployed with the default option of impersonating the current user. If you deploy with the option of impersonating the Service Account there is a risk that the SQL statements could be executed with elevated privileges depending on the rights that have been given to the service account.

call assp.ExecuteSql(
    "provider=sqlncli;server=localhost;database=AdventureWorksDW;trusted_connection=yes"
    ,"Create TABLE myTable (id int)");
GO
call assp.ExecuteSql(
    "provider=sqlncli;server=localhost;database=AdventureWorksDW;trusted_connection=yes"
    ,"INSERT INTO myTable VALUES(1)");
GO
call assp.ExecuteSql(
    "provider=sqlncli;server=localhost;database=AdventureWorksDW;trusted_connection=yes"
    ,"SELECT * FROM myTable");
GO
call assp.ExecuteSql(
    "provider=sqlncli;server=localhost;database=AdventureWorksDW;trusted_connection=yes"
    ,"DROP TABLE myTable");

Updated Wiki: SQLQuery

dgosbell — Thu, 18 Jun 2009 21:14:56 GMT

ExecuteSQL

This function allows for the execution of an arbitrary SQL query, probably most useful for calling from a rowset action.

call assp.ExecuteSql(
    "provider=sqlncli;server=localhost;database=AdventureWorksDW;trusted_connection=yes"
    ,"Create TABLE myTable (id int)");
GO
call assp.ExecuteSql(
    "provider=sqlncli;server=localhost;database=AdventureWorksDW;trusted_connection=yes"
    ,"INSERT INTO myTable VALUES(1)");
GO
call assp.ExecuteSql(
    "provider=sqlncli;server=localhost;database=AdventureWorksDW;trusted_connection=yes"
    ,"SELECT * FROM myTable");
GO
call assp.ExecuteSql(
    "provider=sqlncli;server=localhost;database=AdventureWorksDW;trusted_connection=yes"
    ,"DROP TABLE myTable");

Updated Wiki: Home

dgosbell — Thu, 18 Jun 2009 21:13:25 GMT

ASSP - Analysis Services Stored Procedure Project

The Analysis Services Stored Procedure project is a set of sample stored procedures for Analysis Services 2005 and 2008. 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 and 2008.
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
SetOperations	S	Future	TopCountWithTies, BottomCountWithTies
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
FileSystemCache	U	Future	ClearFileSystemCache, GetFileSystemCacheBytes
SQLQuery	U	Future	ExecuteSQL

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

Source code checked in, #55028

dgosbell — Thu, 18 Jun 2009 21:10:48 GMT

Removing "Readonly=1" from the connection string as it was having no effect

Source code checked in, #54985

dgosbell — Thu, 18 Jun 2009 13:12:21 GMT

Adding SQLQuery class

Updated Wiki: Additional Resources

furmangg — Sat, 23 May 2009 17:12:20 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.

For some examples of returning images from a sproc, see Hilmar Buchta's blog here, here, and here. He also has a post about quantiles and sproc performance.

For an example of using C# to deploy an assembly to Analysis Services using the management API, see this forum thread. That thread also shows how to deploy a third-party DLL in addition to your assembly.

For an example of a stored procedure which allows you to retrieve members with identical names, see this blog post by Hilmar Buchta.

New Post: Newbie

dgosbell — Mon, 20 Apr 2009 22:09:21 GMT

You can remove the source control if you are working on your own version of ASSP. These are only links back to the codeplex repository.

The references may be in a different place on your machine. If you open the references folder in the solution it should show you which ones can't be located. You can either add a reference path to the project properties to tell it where else to look for these references or you can remove the existing references and add new ones that point to the location on your machine. If you don't have a local instances of SSAS on your machine you may need to copy the msmgdsrv.dll file from your server.

New Post: Newbie

mivar — Sun, 19 Apr 2009 15:02:47 GMT

Hi

I am trying out AS stored procedures but didnt get anywhere yet. The thing blocking me is seeing code work in Visual Studio.

The material I am using is

*) the project in here
*) this link

http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!586.entry

and i want to end up doing this:

http://sqlblog.com/blogs/mosha/archive/2008/09/01/drillthrough-on-calculated-measures.aspx

Because that is usable on solution I am working on.

However I have trouble gettin even the project in here to work on my machine. When I open the source project in VS it says

"the source control provider associated with this solution could not be found. The projects will be treated as not under source
control. Do you want to permanently remove the source control bindings from the projects ?"

I say no to this but then when i try to rebuild it give me long error list. One of errors is this reference doesnt work.

"Warning 2 Could not resolve this reference. Could not locate the assembly "msmgdsrv". Check to make sure the assembly exists on disk. If this reference is required by your code, you may get compilation errors. ASSP

Can someone help ?

Closed Issue: Function RegExFilter parameter caseSensitive

dgosbell — Sat, 18 Apr 2009 07:57:03 GMT

Hello!
Don't work this parameter.
On the function getCachedRegEx statement

Boolean caseSensitive = (opt | RegexOptions.IgnoreCase) == RegexOptions.IgnoreCase;

needed to replace

Boolean caseSensitive = (opt & RegexOptions.IgnoreCase) == RegexOptions.IgnoreCase;

Best of regards!

Source code checked in, #49226

dgosbell — Sat, 18 Apr 2009 07:56:59 GMT

fix for getCachedRegEx

New Post: Function RegExFilter parameter caseSensitive

dgosbell — Sat, 18 Apr 2009 07:48:06 GMT

This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.

Created Issue: Function RegExFilter parameter caseSensitive

dgosbell — Sat, 18 Apr 2009 07:48:05 GMT

New Post: Function RegExFilter parameter caseSensitive

dgosbell — Sat, 18 Apr 2009 07:47:23 GMT

Thanks Basilio, I have just checked in a fix for this.

New Post: Function RegExFilter parameter caseSensitive

Basilio — Wed, 15 Apr 2009 13:35:04 GMT

Hello!
Don't work this parameter.
On the function getCachedRegEx statement

Boolean caseSensitive = (opt | RegexOptions.IgnoreCase) == RegexOptions.IgnoreCase;

needed to replace

Boolean caseSensitive = (opt & RegexOptions.IgnoreCase) == RegexOptions.IgnoreCase;

Best of regards!

Commented Issue: Function GetMemberPropertyTableHTML

dgosbell — Tue, 14 Apr 2009 11:14:44 GMT

GOAL
I'd like to add a cube action, which generates a HTML page containing all member property attribute names (=visible attribute relationships) and their values for a specific dimension member as a HTML table.

APPROACH
Therefore I'm looking for a function, which extracts the relationship attribute names and values for a specific dimension attribute (passed by parameter) and then wraps the retrieved values with additional HTML-table tags (tr, td; or by prefix/suffix-parameter) and finally builds a concatenated string, which then can be used within the cube action definition. The table body and header columns itself are defined in the calling action definition and therefore must not be part of the concatenated string.

CHALLENGE
The visible attribute relationships for a specific dimension member can be extracted as data table with the existing Discover function of this project. But moreover I'm looking for a way or even better a (new?) ASSP function, which also extracts the related attribute values and builds the desired concatenated HTML string on the top of it.
In its most flexible version, the function would accept following parameters: 1) Catalog Name 2) Cube Name 3) Unique Dimension Member Name, and
4) Format String which defines the attribute wrapping pattern (example: '<tr><td>##Name##</td><td>##Value##</td></tr>',
where ##Name## and ##Value## is replaced with the existing member property attribute names and values.

As my skill focus is more on T-SQL and MDX rather than C# and its SSAS related object models, having a new ASSP function or working code sample would help most. I assume, that this function in its most flexible version (as suggested above), would also be useful for other purposes within MDX scripts and/or queries.
Based on the new function I could contribute MDX related usage examples to document the power of the resulting cube actions.

for more info, see the discussion http://asstoredprocedures.codeplex.com/Thread/View.aspx?ThreadId=52516
Comments: ** Comment from web user: dgosbell **

I was just thinking about the method signature and I think all it needs is a member reference and the format string (as we could setup a default format string as an optional overload). The catalog and cube would be implied by the context of the action.

New Post: Function GetMemberPropertyTableHTML

furmangg — Wed, 08 Apr 2009 19:26:36 GMT

This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.

Created Issue: Function GetMemberPropertyTableHTML

furmangg — Wed, 08 Apr 2009 19:26:35 GMT

New Post: Function GetMemberPropertyTableHTML

michael_ibax — Wed, 08 Apr 2009 06:51:06 GMT

CLIENT TOOL
The HTML actions are initiated thru ProClarity.
It might be, that also following good front end tools support HTML-actions:
Intelligencia (IT-Workplace), Cubeware Cockpit.
There are many other tools as well. To get a comprehensive picture maybe other
SSAS-specialists like Chris Webb and Mosha Pasumansky may provide some feedback.

FUNCTION
Moreover the function I'm looking for, could also be used to build a concatenated string,
which then could be used to create specific calculated measures/members with text content.

CONTRIBUTION
I'm pleased to contribute code. But as mentioned, my focus is on the cube design and MDX-side and not in creating assemblies (unfortunately). Some impressions about enhanced cube design and functionality as implemented by our team can be derived from the slides on www.aito.ch . If you or the community is interested in code logic and design behind specific features I can write some short articles.