MDX or SQL query to acsertain the partitioning of an OLAP cube

Question

 

How does one ascertain the partitions DYNAMICALLY that exist on the measure data (utlized by a cube) using T-SQL or via an MDX query.

 

 

Answer 1

Maybe this post could help you:

 

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3810360&SiteID=1

 

Other tool that could help you is Powershell for SSAS:

 

http://www.codeplex.com/powerSSAS

 

And this little proof of concept CLR function, to query partitions from Transact-SQL:

 

http://www.codeplex.com/TSSASM

 

 

 

 

Answer 2

Hi Leandro

This looks exactly what I am looking for.

I have however picked up the following error. Any ideas?

 

regards Steve

Msg 6260, Level 16, State 1, Line 1

An error occurred while getting new row from user defined Table Valued Function :

System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

System.Data.SqlTypes.SqlTypeException:

at System.Data.SqlTypes.SqlDateTime.FromTimeSpan(TimeSpan value)

at System.Data.SqlTypes.SqlDateTime.FromDateTime(DateTime value)

at System.Data.SqlTypes.SqlDateTime..ctor(DateTime value)

at CLRFunctions.FillPartitionRow(Object obj, SqlChars& cubename, SqlChars& measuregroupname, SqlChars& partitionid, SqlChars& partitionname, SqlDateTime& createdtimestamp, SqlDateTime& lastprocessed, SqlChars& aggregationdesignid, SqlChars& aggregationdesignname, SqlInt32& aggregationdesignaggrscount, SqlInt32& aggregationdesignestperfgain, SqlChars& slice, SqlInt64& estimatedsize, SqlInt64& estimatedrows, SqlChars& storagemode, SqlChars& storagelocation, SqlChars& processingmode, SqlChars& datasourcename, SqlChars& datasourceconn, SqlChars& datasourceviewname, SqlChars& remotedatasourcename, SqlChars& remotedatasourceid, SqlChars& description, SqlChars& errconfkeyerrorlogfile, SqlChars& errconfkeyduplicate, SqlChars& errconfkeyaction, SqlInt64& errconfkeyerrorlimit, SqlChars& errconfkeyerrorlimitaction, SqlChars& errconfkeynotfound, SqlChars& errconfnullkeyconvtounknown, SqlChars& errconfnullkeynotallowed)

.

Answer 3

Hi

I'll see what's going on. I guess is something related to regional configuration.