PowerSSAS - Exporting information to Excel

There was a comment recently on the post where I annouced the release of powerSSAS, asking if you could export the information from the PowerShell Analyzer Provider Explorer. While I don't think you can do it from the Provider Explorer, but you can use the Export-Csv cmdlet that is built-in to PowerShell. Something like the following would export a list of dimensions to a .csv file.

add-PSSnapin powerssas
new-PSDrive ssas powerssas localhost
cd ssas:
cd "Databases\Adventure Works DW\Dimensions"
# this lists out all the properties
dir | export-Csv c:\dims.csv 

And if you only wanted certain properties, you could change the last line to something like the following, using the select-object cmdlet (aliased as "select" in this example) to only return the specified properties:

dir | Select Name,State,LastSchemaUpdate,LastProcessed | export-Csv c:\dims2.csv 

If you really have special requirements, or if you simply need to use a delimiter other than a comma (eg. if your region uses commas for the decimal separator) then you could roll your own string and redirect that to a file.

add-PSSnapin powerssas
new-PSDrive ssas powerssas localhost
cd ssas:
cd "Databases\Adventure Works DW\Dimensions"
# loop through the dimensions and 
# redirect the constructed string to a file
  foreach ($d in get-ChildItem)
) > c:\data\dims4.txt 

Note that Powershell uses the backtick (`) as an escape character, so the `t is interpreted as a tab character and I am collecting the whole loop up inside an expression $(...) and redirecting that through to a file.

Finally if you wanted absolute control over the output, you could instantiate a copy of Excel and control it using the COM object model.

$objXL = New-Object -comobject Excel.Application

But there are issues with that approach, not the least of which is the fact that it does not work on my laptop! Blowing up with the unhelpful error:

"Exception calling "open" with "1" arguement(s): "Old format or
invalid type library. <Exception from HRESULT: 0x80028018 <Type_E_INVDATAREAD>>"

This is possibly because I am using a regional setting of en-AU, not en-US. As googling for this issue turned up the following KB article which was useful - http://support.microsoft.com/kb/320369.

Using the suggested InvokeMember work around looked promising and I did manage to get Excel working to a degree, but I am not going to bother posting that code here as it was pretty messy. And I could not get the second suggested work around of changing the culture of the current thread to work at all from PowerShell.

Print | posted on Monday, January 7, 2008 2:56 PM