{"id":45,"date":"2008-03-26T21:32:59","date_gmt":"2008-03-27T02:32:59","guid":{"rendered":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/45_analysis-services-2005-and-2008-with-powershell-it-works"},"modified":"2008-03-26T21:40:50","modified_gmt":"2008-03-27T02:40:50","slug":"analysis-services-2005-and-2008-with-powershell-it-works","status":"publish","type":"post","link":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/45_analysis-services-2005-and-2008-with-powershell-it-works","title":{"rendered":"Analysis Services 2005 and 2008 with PowerShell – it works"},"content":{"rendered":"

A few weeks ago I asked a question on Analysis Services 2008 forum<\/a>\u00a0if there will be PowerShell support for SSAS 2008. I got an answer from Microsoft, that no native support is planned. I know that Darren Gosbell released PowerShell Provider for SSAS 2005<\/a>, but for\u00a0the different reasons I cannot use it with all clients. But just this week Carl Rabeler (Microsoft)\u00a0posted a\u00a0new updated set of Analysis Services 2005\/2008 Samples<\/a>. One folder in these samples contained 4 PowerShell script examples that shows how to use it with Analysis Services. I tested these samples and they worked on a standard SSAS 2008 installation. Today\u00a0Carl Rabeler\u00a0actually updated these samples – fixed some minor issues and added one more sample – to get a size of Analysis Services database.<\/p>\n

<\/p>\n

So I grabbed that latest Carl’s example and added a few lines of code to iterate through cubes, measure groups, partitions and dimensions and print a simple list of names and sizes. I tested this code on my virtual SSAS 2008 machine and this worked without any problems. I got very excited\u00a0– so we do have some PowerShell support with SSAS 2008.<\/p>\n

Just to see an error message, I run the same code on SSAS 2005 machine (without additional providers installed) and to my surprise it worked as well. So we do have PowerShell support for Analysis Services 2005 and 2008.\u00a0From\u00a0PowerSSAS documentation I can see that\u00a0that provider allows you to navigate Analysis Services database like drive. Standard provider behaves differently, but it does let you to do quite a lot. In my previous posts I showed examples on how to use Windows Scripting Host (WSH) to write scripts for Analysis Services. But I know that WSH has many limitations. For example, Analysis Services supports different set of parameters passed to the same method. I had problems that WSH\u00a0was expecting just one set of parameters for each method (I believe). I do not know yet what are limitations are with PowerShell, but I hope that somebody who worked with PowerShell a lot (Darren Gosbell, maybe you?) will blog about this. If no, I’ll investigate and post here later myself.<\/p>\n

The good news is that Analysis Services 2005 and 2008 supports PowerShell. So it is definitely an option for your scripting needs.<\/p>\n

You might wonder why I am so excited about PowerShell? Some of our clients do not give us access to their production environment. So if you need to send client a small fix, it is much easier to script that fix and send it like file, instead of sending\u00a0a list of steps that describes what to do. Changes could be very simple – like add new translation to a specific attribute, or hide\/show a measure in the perspective or\u00a0cube.<\/p>\n

Anyway,\u00a0 here is my code that I tested on SSAS 2005 and 2008 versions:<\/p>\n

## Add the AMO namespace
\n$loadInfo = [Reflection.Assembly]::LoadWithPartialName(“Microsoft.AnalysisServices”)<\/p>\n

## Connect and get the edition of the local server
\n$connection = “localhost”
\n$server = New-Object Microsoft.AnalysisServices.Server
\n$server.connect($connection)<\/p>\n

foreach ($d in $server.Databases )
\n{
\n\u00a0Write-Output ( “`nDatabase: {0} Size: {1}`n” -f $d.Name, $d.EstimatedSize )<\/p>\n

\u00a0foreach ($cube in $d.Cubes) {
\n\u00a0 Write-Output ( “Cube: {0}” -f $Cube.Name )
\n\u00a0 foreach ($mg in $cube.MeasureGroups) {
\n\u00a0\u00a0 Write-Output ( ” Measure Group: {0} Size: {1}” -f $mg.Name, $mg.EstimatedSize )
\n\u00a0\u00a0 foreach ($part in $mg.Partitions) {
\n\u00a0\u00a0\u00a0 Write-Output ( ”\u00a0 Partition: {0} Size: {1}” -f $part.Name, $part.EstimatedSize )
\n\u00a0\u00a0 } # Partition
\n\u00a0 } # Measure group
\n\u00a0
\n\u00a0 foreach ($dim in $d.Dimensions) {
\n\u00a0\u00a0 Write-Output ( “Dimension: {0}” -f $dim.Name)
\n\u00a0 } # Dimensions<\/p>\n

\u00a0} # Cube<\/p>\n

} # Databases<\/p><\/blockquote>\n

Note: My blogging software replaces double quotes with different symbol, so if you’ll grab this code, then be sure to fix double quotes.\u00a0<\/p>\n

And here are results – this printout is from SSAS 2008 server with just Adventure Works DW database installed. SSAS 2005 results are very similar:<\/p>\n

PS C:\\Utilities\\Scripts> .\\dbinfo.ps1
\nDatabase: Adventure Works DW Size: 34535913
\nCube: Adventure Works
\n\u00a0Measure Group: Internet Sales Size: 2390949
\n\u00a0 Partition: Internet_Sales_2001 Size: 29642
\n\u00a0 Partition: Internet_Sales_2002 Size: 78911
\n\u00a0 Partition: Internet_Sales_2003 Size: 993042
\n\u00a0 Partition: Internet_Sales_2004 Size: 1153218
\n\u00a0Measure Group: Internet Orders Size: 1816480
\n\u00a0 Partition: Internet_Orders_2001 Size: 32637
\n\u00a0 Partition: Internet_Orders_2002 Size: 86898
\n\u00a0 Partition: Internet_Orders_2003 Size: 765137
\n\u00a0 Partition: Internet_Orders_2004 Size: 894744
\n\u00a0Measure Group: Internet Customers Size: 1223972
\n\u00a0 Partition: Customers_2001 Size: 10307
\n\u00a0 Partition: Customers_2002 Size: 27960
\n\u00a0 Partition: Customers_2003 Size: 538016
\n\u00a0 Partition: Customers_2004 Size: 610657
\n\u00a0Measure Group: Sales Reasons Size: 3215
\n\u00a0 Partition: Internet_Sales_Reasons Size: 1034
\n\u00a0Measure Group: Reseller Sales Size: 3308697
\n\u00a0 Partition: Reseller_Sales_2001 Size: 304565
\n\u00a0 Partition: Reseller_Sales_2002 Size: 801746
\n\u00a0 Partition: Reseller_Sales_2003 Size: 1223908
\n\u00a0 Partition: Reseller_Sales_2004 Size: 673284
\n\u00a0Measure Group: Reseller Orders Size: 1295396
\n\u00a0 Partition: Reseller_Orders_2001 Size: 140197
\n\u00a0 Partition: Reseller_Orders_2002 Size: 330174
\n\u00a0 Partition: Reseller_Orders_2003 Size: 480043
\n\u00a0 Partition: Reseller_Orders_2004 Size: 275198
\n\u00a0Measure Group: Sales Summary Size: 2987696
\n\u00a0 Partition: Total_Sales_2001 Size: 80094
\n\u00a0 Partition: Total_Sales_2002 Size: 441643
\n\u00a0 Partition: Total_Sales_2003 Size: 1144905
\n\u00a0 Partition: Total_Sales_2004 Size: 1118404
\n\u00a0Measure Group: Sales Orders Size: 2153776
\n\u00a0 Partition: Total_Orders_2001 Size: 133590
\n\u00a0 Partition: Total_Orders_2002 Size: 332047
\n\u00a0 Partition: Total_Orders_2003 Size: 831851
\n\u00a0 Partition: Total_Orders_2004 Size: 801419
\n\u00a0Measure Group: Sales Targets Size: 3656
\n\u00a0 Partition: Sales_Quotas Size: 1514
\n\u00a0Measure Group: Financial Reporting Size: 404464
\n\u00a0 Partition: Finance Size: 373520
\n\u00a0Measure Group: Exchange Rates Size: 293811
\n\u00a0 Partition: Currency_Rates Size: 289223
\nDimension: Promotion
\nDimension: Product
\nDimension: Customer
\nDimension: Geography
\nDimension: Reseller
\nDimension: Sales Territory
\nDimension: Employee
\nDimension: Scenario
\nDimension: Department
\nDimension: Organization
\nDimension: Account
\nDimension: Date
\nDimension: Source Currency
\nDimension: Sales Reason
\nDimension: Reseller Sales Order Details
\nDimension: Internet Sales Order Details
\nDimension: Sales Channel
\nDimension: Destination Currency
\nDimension: Sales Summary Order Details
\nPS C:\\Utilities\\Scripts><\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"

A few weeks ago I asked a question on Analysis Services 2008 forum\u00a0if there will be PowerShell support for SSAS 2008. I got an answer from Microsoft, that no native support is planned. I know that Darren Gosbell released PowerShell Provider for SSAS 2005, but for\u00a0the different reasons I cannot use it with all clients. […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[4,6],"tags":[],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts\/45"}],"collection":[{"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/comments?post=45"}],"version-history":[{"count":0,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts\/45\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/media?parent=45"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/categories?post=45"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/tags?post=45"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}