How to extract OneStream metadata and how to automate metadata load using MMM – MMM Part III


We are going to look at another feature in MMM.

MMM can be used to extract OneStream metadata. You can extract the dimension as an OneStream XML file or as a parent-child file.

The important feature of extract metadata in MMM is its capability to extract hierarchies and an option to exclude or include(default OS behavior) orphan members while extracting dimensions.

It also shows how to automate the load using PowerShell scripts

Sample PowerShell for SQL metadata load.

Add-Type -Path "C:\Program Files (x86)\OneStream Software\OneStreamStudio\OneStreamClientApi.dll"
$xfApi = new-object OneStream.Client.Windows.OneStreamClientApi
# Script is using an encrypted password to login
$xfLogonInfo = $xfApi.Authentication.LogonAndOpenApplication("http://knowhere:50001/OneStreamWeb","admin","fT2I3zVqzGrR6EPvfNulJA==","A very basic sample",[OneStream.Shared.Wcf.XFClientAuthenticationType]::EncryptedPW)

# a dictionary is needed for running ADODataSet commands
$object = New-Object 'system.collections.generic.dictionary[string,string]'

if ($xfLogonInfo.IsAppOpen)
{
    # Provide the full path of the file located on OS server
    $xfResult = $xfApi.DataManagement.ExecuteStep("Metadata Automation (MMM)", "Execute Metadata load (MMM)","LoadType=MMMLoadFromSQL,ProfileName=TestLoadSQL,DimType=Account,DimName=AdventureWorksAccounts,LoadProfileOptions=New,ColumnMappingInfo=[ParentName = ParentAccountCodeAlternateKey,ChildName = AccountCodeAlternateKey,Description = AccountDescription, AccountType = AccountType],ExternalDB=AdventureWorksDW,SQLString=[SELECT distinct [AccountCodeAlternateKey]
      ,CASE WHEN [ParentAccountCodeAlternateKey] is NULL THEN 'root' ELSE CAST([ParentAccountCodeAlternateKey] as nvarchar) END as [ParentAccountCodeAlternateKey]
      ,[AccountDescription]
      ,CASE WHEN [AccountType] = 'Assets' THEN 'Asset' 
	  WHEN [AccountType] = 'Balances' THEN 'Balance' 
	  WHEN [AccountType] = 'Expenditures' THEN 'Expense' 
	  WHEN [AccountType] = 'Liabilities' THEN 'Liability'
	  WHEN [AccountType] = 'Statistical' THEN 'NonFinancial'
	  ELSE [AccountType] END as [AccountType]
  FROM [DimAccount]]")
    if ($xfResult.IsOK)
    {
        $xfResult.Message
    }
    Else{
        # Get the error description so that PS screen can be shown with the error occured during the load.
        $xfLogMessage = $xfApi.DataProvider.GetAdoDataSetForMethodCommand([OneStream.Shared.Wcf.XFCommandMethodTypeId]::BusinessRule, "{MMM_HelperQueries}{GetErrorDescriptionForTaskID}{}", "MetadataErrorLog" ,$object, $true)
        $xfResult.Message
        $xfLogMessage.Tables["MetadataErrorLog"].Rows[0].Desc
    }
}
Else
{
	"Error logging in user"
    $xfLogonInfo.ErrorMessage
}

Line 28 in the script above will look at any errors in the metadata XML and will show that on the PowerShell script.

Yes, it shows the line which caused the issue 🙂

Leave a comment

Your email address will not be published. Required fields are marked *