Data Management Automation With PowerShell

PowerShell is an object-oriented programming language and interactive command line shell for Microsoft Windows. It was designed to automate system tasks, such as batch processing and create systems management tools for common processes.PowerShell includes over 130 standard command line tools for functions that formerly required users to create scripts in VB, VBScript or C#.

PowerShell offers a variety of ways to automate tasks which include:

  • Cmdlets : Very small .NET classes that appear as system commands.

  • Scripts: Combinations of cmdlets and associated logic.

  • Executables: Standalone tools.

Instantiation of Standard .NET Classes

PowerShell integrates with the .NET environment and can be embedded in other applications. Over one hundred cmdlets are included and can be used separately or combined to automate more complex tasks. You can also create and share cmdlets.

PowerShell is built into Windows Server 2008 and Windows 7, provided as an optional feature during installation. You can use Windows Task Scheduler to automate PowerShell script execution. 

Using PowerShell Script Editor

To run PowerShell on Windows 7 or a later version:

  • Select Windows > Start, then enter "PowerShell".

  • Select All Programs > Accessories |Windows PowerShell. 

You can use these programs with PowerShell:

  • Windows PowerShell ISE: An integrated script editor you can use to type PowerShell commands and to edit and run PowerShell script files. These are text files with a ps1 extension. 

  • Windows PowerShell: A command line execution tool similar to a DOS prompt. You can use this tool to run commands or script files but you cannot create or modify scripts.  

Configuring PowerShell for the Client API

Perform these steps on each machine that will use PowerShell before it can be used with the Client API:

  1. Execute a PowerShell command enabling the execution of unsigned scripts. 

  2. Create or alter the PowerShell execution and IDE configuration files, so the script engine understands how to use the .Net Framework v4.0. 

  3. Install OneStream Studio on each machine executing PowerShell scripts.

Running Unsigned Scripts

Initially, this code needs to run in a PowerShell command prompt to enable PowerShell to run unsigned scripts created on the local computer:

set-executionpolicy remotesigned

Configuration for .Net Framework v.4.0

To use the OneStreamClientApi with PowerShell, PowerShell must be configured to use the .NET Framework v4.0. Perform these tasks:

  1. Modify or create these files if they do not exist in C:\Windows\System32\WindowsPowerShell\v1.0:

    • powershell.exe.config

    • powershell_ise.exe.config 

  2. Add this required content to each file:

    <?xml version="1.0"?> 
    <configuration> 
        <startup useLegacyV2RuntimeActivationPolicy="true"> 
            <supportedRuntime version="v4.0.30319"/> 
            <supportedRuntime version="v2.0.50727"/> 
        </startup> 
    </configuration>

    For more information, see: http://tfl09.blogspot.com/2010/08/using-newer-versions-of-net-with.html.

  3. Install OneStream Studio.

Learning PowerShell

Microsoft provides extensive resources to help IT professionals leverage PowerShell. For more information, see: http://technet.microsoft.com/en-us/scriptcenter/powershell.aspx

Using the Client API in a PowerShell Script

OneStreamClientApi specifically enables PowerShell scripts to call a function. This API exposes functions for authentication and Data Management. OneStream expanded the number of functions exposed to this API. The Client API component is installed with the Client API Installer. This API offers a simple set of functions enabling script writers to connect to the server, authenticate, execute OneStream Data Management Sequences, and perform basic data retrieval.

Client API Object Hierarchy

OneStreamClientAPI

LogonInfo

Type
LogonInfo

SI

Type
SessionInfo

Authentication

Logon

Parameters
string webServerUrl
string userName
string password
XFClientAuthenticationType clientAuthenticationType

Return Value
LogonInfo

Logoff

Parameters
None

Return Value
None

OpenApplication

Parameters
string Application

Return Value
LogonInfo

LogonAndOpenApplication

Parameters
string webServerUrl
string userName
string password
string application
XFClientAuthenticationType clientAuthenticationType

Return Value
LogoInfo

EncryptPassword

Parameters
string clearTextPassword
XFClientAuthenticationType clientAuthenticationType

Return Value
string

DataManagement

ExecuteSequence

Parameters
string sequenceName
string customSubstVarsAsCommaSeparatedPairs

Return Value
DataMgmtResult

ExecuteStep

Parameters
string dataMgmtGroupName
string stepName
string customSubstVarsAsCommaSeparatedPairs

Return Value
DataMgmtResult

DataProvider

GetAdoDataSetForCubeViewCommand

Parameters
string CubeViewName
bool dataTablePerCubeViewRow
CubeViewDataTableOptions dataTableOptions
string resultDataTableName
Dictionary<string, string> customSubstVars
bool throwExceptionOnError

Return Value
DataSet

GetAdoDataSetForMethodCommand

Parameters
XFCommandMethodTypeId xfCommandMethodType
string methodQuery
string resultDataTableName
Dictionary<string, string> cistomSubstVars
bool throwExceptionOnError

Return Value
DataSet