Load tabular Cube measures using PowerShell file

Occasional Visitor

Hi ALL,

 

I am new PowerShell scripts. below code downloaded and working.

I am trying to load SSAS Tabular model document into table using PowerShell. while loading data into table i am not facing any issues. but data not loaded into db table. output is executed correctly in PowerShell IDE. i need data to be loaded in table or excel. please suggest and help me.

 

Below is my PowerShell Query.

 

param(
[int]$BatchLogID = 1
, [string]$ASServer = "asazure://eastus.asazure.windows.net/p21d18102578001"
, [string]$SSASTabularDB = "p21d18102584002"
, [string]$SQLServer = "p21d18102583001.database.windows.net"
, [string]$DBName = "p21d18102584001"
, [string]$flgDisplayOnly = "FALSE"
)
Write-Host -foreground "magenta" Write-Host "400 Measure*****************************************"

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.Tabular")
$SSASTabularServer = New-Object Microsoft.AnalysisServices.Tabular.Server
$SSASTabularServer.connect($ASServer)
$TabularDB = $SSASTabularServer.Databases.GetByName($SSASTabularDB)
IF ($flgDisplayOnly -ne "FALSE")
{
$SQLConn = New-Object System.Data.SqlClient.SqlConnection
$SQLConn.ConnectionString = ("Data Source=$SQLServer; Initial Catalog=$DBName; Integrated Security=SSPI")
$SQLConn.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $SQLConn
}
$intRow = 1 # Row number
[string]$strSQL = ""

Write-Host ''
Write-Host '*********************************************************************'
Write-Host "------- Start Table - Measure"
Write-Host '*********************************************************************'

foreach ($TabTable in $TabularDB.Model.Tables | Sort-Object Name)
{

Write-Host ""
Write-Host "--------------------------------------------------------"
Write-Host " " $TabTable.Name.PadRight(30) " " $TabTable.ObjectType
#$TabTable

foreach ($Measure in $TabTable.Measures | Sort-Object Name)
{

Write-Host $intRow " -- " $Measure.Name.PadRight(30) " " $Measure.Expression
#$Measure
# unprocessed date come out as '12/30/1699 19:00:00' but the SQL DateTime range is '1753-01-01'
[String]$ModifiedTime = $Measure.ModifiedTime
[String]$StructureModifiedTime = $Measure.StructureModifiedTime
[String]$strExpression = $Measure.Expression
$strExpression = $strExpression -replace "'" , "''"

IF ($flgDisplayOnly -ne "FALSE")
{
$nl = [Environment]::NewLine
$strSQL = "INSERT INTO [dbo].[Measure] "
$strSQL = ($strSQL + $nl + "( ")
$strSQL = ($strSQL + $nl + " [BatchLogID] , [Row] ")
$strSQL = ($strSQL + $nl + ", [Table Name] ")
$strSQL = ($strSQL + $nl + ", [Measure Name] ")
$strSQL = ($strSQL + $nl + ", [DataType] ")
$strSQL = ($strSQL + $nl + ", [FormatString] ")
$strSQL = ($strSQL + $nl + ", [Expression] ")
$strSQL = ($strSQL + $nl + ", [IsSimpleMeasure] ")
$strSQL = ($strSQL + $nl + ", [IsHidden] ")
$strSQL = ($strSQL + $nl + ", [State] ")
$strSQL = ($strSQL + $nl + ", [DisplayFolder] ")
$strSQL = ($strSQL + $nl + ", [Description] ")
$strSQL = ($strSQL + $nl + ", [Annotation] ")
$strSQL = ($strSQL + $nl + ", [KPI] ")
$strSQL = ($strSQL + $nl + ", [ObjectType] ")
$strSQL = ($strSQL + $nl + ", [ModifiedTime] ")
$strSQL = ($strSQL + $nl + ", [StructureModifiedTime] ")
$strSQL = ($strSQL + $nl + ")")
$strSQL = ($strSQL + $nl + "VALUES ( ")
$strSQL = ($strSQL + $nl + " '" + $BatchLogID + "' , '" + $intRow + "' ")
$strSQL = ($strSQL + $nl + ", '" + $TabTable.Name + "' ")
$strSQL = ($strSQL + $nl + ", '" + $Measure.name + "' ")
$strSQL = ($strSQL + $nl + ", '" + $Measure.DataType + "' ")
$strSQL = ($strSQL + $nl + ", '" + $Measure.FormatString + "' ")
$strSQL = ($strSQL + $nl + ", '" + $strExpression + "' ")
$strSQL = ($strSQL + $nl + ", '" + $Measure.IsSimpleMeasure + "' ")
$strSQL = ($strSQL + $nl + ", '" + $Measure.IsHidden + "' ")
$strSQL = ($strSQL + $nl + ", '" + $Measure.State + "' ")
$strSQL = ($strSQL + $nl + ", '" + $Measure.DisplayFolder + "' ")
$strSQL = ($strSQL + $nl + ", '" + $Measure.Description + "' ")
$strSQL = ($strSQL + $nl + ", '" + $Measure.Annotations + "' ")
$strSQL = ($strSQL + $nl + ", '" + $Measure.KPI + "' ")
$strSQL = ($strSQL + $nl + ", '" + $Measure.ObjectType + "' ")
$strSQL = ($strSQL + $nl + ", '" + $ModifiedTime + "' ")
$strSQL = ($strSQL + $nl + ", '" + $StructureModifiedTime + "' ")
$strSQL = ($strSQL + $nl + ") ")
$Command.CommandText = $strSQL
$Command.ExecuteNonQuery()
}
$intRow++
}
}
IF ($flgDisplayOnly -ne "FALSE")
{
$SQLConn.Close()
Write-Host "------- SQL Connection Closed -----------------------------------------------"
}

 

 

please let me know any more details required.

 

Regards,

SKM

 

0 Replies