1

How to find the size, name and other details of an Analysis Services database ?

I have a Analysis Services server and I can see one database inside that.

I want to know the size and other basic details of this DB.

6

You could use powershell to connect to the database and list a lot of properties.

Use this script to find out what properties you can get from a database:

$loadInfo = [Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect("localhost")
$Server.Databases | Get-Member

Which outputs:

   TypeName: Microsoft.AnalysisServices.Database

Name                        MemberType Definition                                                                                                                        
----                        ---------- ----------                                                                                                                        
Disposed                    Event      System.EventHandler Disposed(System.Object, System.EventArgs)                                                                     
Backup                      Method     void Backup(string file), void Backup(string file, bool allowOverwrite), void Backup(string file, bool allowOverwrite, bool bac...
CanProcess                  Method     bool CanProcess(Microsoft.AnalysisServices.ProcessType processType), bool IProcessable.CanProcess(Microsoft.AnalysisServices.Pr...
Clone                       Method     Microsoft.AnalysisServices.Database Clone(), System.Object ICloneable.Clone()                                                     
CopyTo                      Method     Microsoft.AnalysisServices.Database CopyTo(Microsoft.AnalysisServices.Database obj)                                               
CreateBody                  Method     void IMajorObject.CreateBody()                                                                                                    
CreateObjRef                Method     System.Runtime.Remoting.ObjRef CreateObjRef(type requestedType)                                                                   
DependsOn                   Method     bool IMajorObject.DependsOn(Microsoft.AnalysisServices.IMajorObject obj)                                                          
Detach                      Method     void Detach(), void Detach(string password)                                                                                       
Dispose                     Method     void Dispose(), void IDisposable.Dispose()                                                                                        
Drop                        Method     void Drop(), void Drop(Microsoft.AnalysisServices.DropOptions options), void Drop(Microsoft.AnalysisServices.DropOptions option...
Equals                      Method     bool Equals(System.Object obj)                                                                                                    
GetDependents               Method     hashtable GetDependents(hashtable dependents), hashtable IMajorObject.GetDependents(hashtable dependents)                         
GetHashCode                 Method     int GetHashCode()                                                                                                                 
GetLifetimeService          Method     System.Object GetLifetimeService()                                                                                                
GetReferences               Method     hashtable GetReferences(hashtable references, bool forMajorChildrenAlso)                                                          
GetType                     Method     type GetType()                                                                                                                    
GetUpdateOverwrites         Method     hashtable GetUpdateOverwrites(bool fullExpansion)                                                                                 
InitializeLifetimeService   Method     System.Object InitializeLifetimeService()                                                                                         
LinkDimension               Method     Microsoft.AnalysisServices.Dimension LinkDimension(Microsoft.AnalysisServices.Dimension source, string dataSourceId), Microsoft...
OnDeserialization           Method     void IDeserializationCallback.OnDeserialization(System.Object sender)                                                             
OnDeserializationBegin      Method     void IDeserializationStartCallback.OnDeserializationBegin(System.Object sender)                                                   
Process                     Method     void Process(), void Process(Microsoft.AnalysisServices.ProcessType processType), void Process(Microsoft.AnalysisServices.Proce...
Refresh                     Method     void Refresh(), void Refresh(bool full), void Refresh(bool full, Microsoft.AnalysisServices.RefreshType type), void IMajorObjec...
Submit                      Method     void Submit(bool submitPermanently), void Submit()                                                                                
ToString                    Method     string ToString(), string IFormattable.ToString(string format, System.IFormatProvider formatProvider)                             
Update                      Method     void Update(), void Update(Microsoft.AnalysisServices.UpdateOptions options), void Update(Microsoft.AnalysisServices.UpdateOpti...
Validate                    Method     bool Validate(Microsoft.AnalysisServices.ValidationErrorCollection errors, bool includeDetailedErrors, Microsoft.AnalysisServic...
WriteRef                    Method     void IMajorObject.WriteRef(System.Xml.XmlWriter writer)                                                                           
Accounts                    Property   Microsoft.AnalysisServices.AccountCollection Accounts {get;}                                                                      
AggregationPrefix           Property   string AggregationPrefix {get;set;}                                                                                               
Annotations                 Property   Microsoft.AnalysisServices.AnnotationCollection Annotations {get;}                                                                
Assemblies                  Property   Microsoft.AnalysisServices.AssemblyCollection Assemblies {get;}                                                                   
BaseType                    Property   type BaseType {get;}                                                                                                              
Collation                   Property   string Collation {get;set;}                                                                                                       
CompatibilityLevel          Property   int CompatibilityLevel {get;set;}                                                                                                 
Container                   Property   System.ComponentModel.IContainer Container {get;}                                                                                 
CreatedTimestamp            Property   datetime CreatedTimestamp {get;set;}                                                                                              
Cubes                       Property   Microsoft.AnalysisServices.CubeCollection Cubes {get;}                                                                            
DatabasePermissions         Property   Microsoft.AnalysisServices.DatabasePermissionCollection DatabasePermissions {get;}                                                
DataSourceImpersonationInfo Property   Microsoft.AnalysisServices.ImpersonationInfo DataSourceImpersonationInfo {get;set;}                                               
DataSources                 Property   Microsoft.AnalysisServices.DataSourceCollection DataSources {get;}                                                                
DataSourceViews             Property   Microsoft.AnalysisServices.DataSourceViewCollection DataSourceViews {get;}                                                        
DbStorageLocation           Property   string DbStorageLocation {get;set;}                                                                                               
Description                 Property   string Description {get;set;}                                                                                                     
Dimensions                  Property   Microsoft.AnalysisServices.DimensionCollection Dimensions {get;}                                                                  
DirectQueryMode             Property   Microsoft.AnalysisServices.DirectQueryMode DirectQueryMode {get;set;}                                                             
DismissedValidationResults  Property   Microsoft.AnalysisServices.DismissedValidationResultCollection DismissedValidationResults {get;}                                  
DismissedValidationRules    Property   Microsoft.AnalysisServices.DismissedValidationRuleCollection DismissedValidationRules {get;}                                      
EstimatedSize               Property   long EstimatedSize {get;set;}                                                                                                     
FriendlyPath                Property   string FriendlyPath {get;}                                                                                                        
Host                        Property   System.IServiceProvider Host {get;set;}                                                                                           
ID                          Property   string ID {get;set;}                                                                                                              
ImagePath                   Property   string ImagePath {get;set;}                                                                                                       
ImageUniqueID               Property   string ImageUniqueID {get;set;}                                                                                                   
ImageUrl                    Property   string ImageUrl {get;set;}                                                                                                        
ImageVersion                Property   string ImageVersion {get;set;}                                                                                                    
IsLoaded                    Property   bool IsLoaded {get;}                                                                                                              
Language                    Property   int Language {get;set;}                                                                                                           
LastProcessed               Property   datetime LastProcessed {get;set;}                                                                                                 
LastSchemaUpdate            Property   datetime LastSchemaUpdate {get;set;}                                                                                              
LastUpdate                  Property   datetime LastUpdate {get;set;}                                                                                                    
MasterDataSourceID          Property   string MasterDataSourceID {get;set;}                                                                                              
MiningStructures            Property   Microsoft.AnalysisServices.MiningStructureCollection MiningStructures {get;}                                                      
ModelType                   Property   Microsoft.AnalysisServices.ModelType ModelType {get;set;}                                                                         
Name                        Property   string Name {get;set;}                                                                                                            
ObjectReference             Property   Microsoft.AnalysisServices.ObjectReference ObjectReference {get;}                                                                 
OwningCollection            Property   Microsoft.AnalysisServices.ModelComponentCollection OwningCollection {get;set;}                                                   
Parent                      Property   Microsoft.AnalysisServices.Server Parent {get;}                                                                                   
ParentDatabase              Property   Microsoft.AnalysisServices.Database ParentDatabase {get;}                                                                         
ParentServer                Property   Microsoft.AnalysisServices.Server ParentServer {get;}                                                                             
Path                        Property   string Path {get;}                                                                                                                
ProcessingPriority          Property   int ProcessingPriority {get;set;}                                                                                                 
ReadWriteMode               Property   Microsoft.AnalysisServices.ReadWriteMode ReadWriteMode {get;set;}                                                                 
ReturnPassword              Property   bool ReturnPassword {get;set;}                                                                                                    
Roles                       Property   Microsoft.AnalysisServices.RoleCollection Roles {get;}                                                                            
Site                        Property   System.ComponentModel.ISite Site {get;set;}                                                                                       
SiteID                      Property   string SiteID {get;set;}                                                                                                          
State                       Property   Microsoft.AnalysisServices.AnalysisState State {get;set;}                                                                         
StorageEngineUsed           Property   Microsoft.AnalysisServices.StorageEngineUsed StorageEngineUsed {get;set;}                                                         
Translations                Property   Microsoft.AnalysisServices.TranslationCollection Translations {get;}                                                              
Visible                     Property   bool Visible {get;set;}                                                                                                           

You can combine or loop over these properties as much as you want, for example this script should give you the size of a known database:

$loadInfo = [Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect("localhost")
$database = $server.Databases.FindByName("AdventureWorksDW2012Multidimensional-EE")
Write-Output("database size Size : {0}MB" -f ($database.EstimatedSize/1024/1024).ToString("#,##0"))

outputs:

database size Size : 24MB

If you wanted the size of all databases on the server you can loop over them too:

$loadInfo = [Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect("localhost")
foreach ($database in $server.Databases)
{
Write-Output("{0} size Size : {1}MB" -f $Database.Name, ($database.EstimatedSize/1024/1024).ToString("#,##0"))
}

Outputs:

AdventureWorksDW2012Multidimensional-EE size Size : 24MB
MultidimensionalProject1 size Size : 0MB

Mix and match to your liking to get information on all databases, a single database, cubes inside a database, dimensions inside a cube etc.

| improve this answer | |
2

No SQL Server version mentioned, just in case that makes a difference.

However, if you have sufficient rights you can connect to the server using the Object Explorer / Connect drop down and choose "Analysis Services..." rather than "Database Engine...".

This will expand to allow you to see some details of the Analysis Services databases. Just by using the Object Explore you can explore some details for the Analysis Services databases.

This is quite rudimentary, but may get you started. After that you need to learn how to query for the details.

Here is some guidance, based on the soon to be released SQL Server 2016.

https://msdn.microsoft.com/en-us/library/ms174556.aspx

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.