Pipe SQL Analysis Services Data to CSV in PowerShell

Ready to get started?

Download for a free trial:

Download Now

Learn more:

SQL Analysis Services Cmdlets

An easy-to-use set of PowerShell Cmdlets offering real-time access to SQL Analysis Services. The Cmdlets allow users to easily connect to live data - just like working with SQL server.

Use standard PowerShell cmdlets to access SQL Analysis Services tables.

The CData Cmdlets Module for SQL Analysis Services is a standard PowerShell module offering straightforward integration with SQL Analysis Services. Below, you will find examples of using our SSAS Cmdlets with native PowerShell cmdlets.

Creating a Connection to Your SQL Analysis Services Data

To connect, provide authentication and set the Url property to a valid SQL Server Analysis Services endpoint. You can connect to SQL Server Analysis Services instances hosted over HTTP with XMLA access. See the Microsoft documentation to configure HTTP access to SQL Server Analysis Services.

To secure connections and authenticate, set the corresponding connection properties, below. The data provider supports the major authentication schemes, including HTTP and Windows, as well as SSL/TLS.

  • HTTP Authentication

    Set AuthScheme to "Basic" or "Digest" and set User and Password. Specify other authentication values in CustomHeaders.

  • Windows (NTLM)

    Set the Windows User and Password and set AuthScheme to "NTLM".

  • Kerberos and Kerberos Delegation

    To authenticate with Kerberos, set AuthScheme to NEGOTIATE. To use Kerberos delegation, set AuthScheme to KERBEROSDELEGATION. If needed, provide the User, Password, and KerberosSPN. By default, the data provider attempts to communicate with the SPN at the specified Url.

  • SSL/TLS:

    By default, the data provider attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store. To specify another certificate, see the SSLServerCert property for the available formats.

You can then access any cube as a relational table: When you connect the data provider retrieves SSAS metadata and dynamically updates the table schemas. Instead of retrieving metadata every connection, you can set the CacheLocation property to automatically cache to a simple file-based store.

See the Getting Started section of the CData documentation, under Retrieving Analysis Services Data, to execute SQL-92 queries to the cubes.

$conn = Connect-SSAS  -User "$User" -Password "$Password" -URL "$URL"

Selecting Data

Follow the steps below to retrieve data from the Adventure_Works table and pipe the result into to a CSV file:

Select-SSAS -Connection $conn -Table Adventure_Works | Select -Property * -ExcludeProperty Connection,Table,Columns | Export-Csv -Path c:\myAdventure_WorksData.csv -NoTypeInformation

You will notice that we piped the results from Select-SSAS into a Select-Object cmdlet and excluded some properties before piping them into an Export-Csv cmdlet. We do this because the CData Cmdlets append Connection, Table, and Columns information onto each "row" in the result set, and we do not necessarily want that information in our CSV file.

The Connection, Table, and Columns are appended to the results in order to facilitate piping results from one of the CData Cmdlets directly into another one.

Deleting Data

The following line deletes any records that match the criteria:

Select-SSAS -Connection $conn -Table Adventure_Works -Where "Fiscal_Year = FY 2008" | Remove-SSAS

Inserting and Updating Data

The cmdlets make data transformation easy as well as data cleansing. The following example loads data from a CSV file into SQL Analysis Services, checking first whether a record already exists and needs to be updated instead of inserted.

Import-Csv -Path C:\MyAdventure_WorksUpdates.csv | %{
  $record = Select-SSAS -Connection $SSAS -Table Adventure_Works -Where ("Id = `'"+$_.Id+"`'")
    Update-SSAS -Connection $ssas -Table Adventure_Works -Columns ("Fiscal_Year","Sales_Amount") -Values ($_.Fiscal_Year, $_.Sales_Amount) -Where ("Id = `'"+$_.Id+"`'")
    Add-SSAS -Connection $ssas -Table Adventure_Works -Columns ("Fiscal_Year","Sales_Amount") -Values ($_.Fiscal_Year, $_.Sales_Amount)

As always, our goal is to simplify the way you connect to data. With cmdlets users can install a data module, set the connection properties, and start building. Download Cmdlets and start working with your data in PowerShell today!