Load tabular Cube measures using PowerShell file

%3CLINGO-SUB%20id%3D%22lingo-sub-1075561%22%20slang%3D%22en-US%22%3ELoad%20tabular%20Cube%20measures%20using%20PowerShell%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1075561%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20ALL%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20new%20PowerShell%20scripts.%20below%20code%20downloaded%20and%20working.%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20load%20SSAS%20Tabular%20model%20document%20into%20table%20using%20PowerShell.%20while%20loading%20data%20into%20table%20i%20am%20not%20facing%20any%20issues.%20but%20data%20not%20loaded%20into%20db%20table.%20output%20is%20executed%20correctly%20in%20PowerShell%20IDE.%20i%20need%20data%20to%20be%20loaded%20in%20table%20or%20excel.%20please%20suggest%20and%20help%20me.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBelow%20is%20my%20PowerShell%20Query.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eparam(%3CBR%20%2F%3E%5Bint%5D%24BatchLogID%20%3D%201%3CBR%20%2F%3E%2C%20%5Bstring%5D%24ASServer%20%3D%20%22asazure%3A%2F%2Feastus.asazure.windows.net%2Fp21d18102578001%22%3CBR%20%2F%3E%2C%20%5Bstring%5D%24SSASTabularDB%20%3D%20%22p21d18102584002%22%3CBR%20%2F%3E%2C%20%5Bstring%5D%24SQLServer%20%3D%20%22p21d18102583001.database.windows.net%22%3CBR%20%2F%3E%2C%20%5Bstring%5D%24DBName%20%3D%20%22p21d18102584001%22%3CBR%20%2F%3E%2C%20%5Bstring%5D%24flgDisplayOnly%20%3D%20%22FALSE%22%3CBR%20%2F%3E)%3CBR%20%2F%3EWrite-Host%20-foreground%20%22magenta%22%20Write-Host%20%22400%20Measure*****************************************%22%3C%2FP%3E%3CP%3E%5BSystem.Reflection.Assembly%5D%3A%3ALoadWithPartialName(%22Microsoft.AnalysisServices.Tabular%22)%3CBR%20%2F%3E%24SSASTabularServer%20%3D%20New-Object%20Microsoft.AnalysisServices.Tabular.Server%3CBR%20%2F%3E%24SSASTabularServer.connect(%24ASServer)%3CBR%20%2F%3E%24TabularDB%20%3D%20%24SSASTabularServer.Databases.GetByName(%24SSASTabularDB)%3CBR%20%2F%3EIF%20(%24flgDisplayOnly%20-ne%20%22FALSE%22)%3CBR%20%2F%3E%7B%3CBR%20%2F%3E%24SQLConn%20%3D%20New-Object%20System.Data.SqlClient.SqlConnection%3CBR%20%2F%3E%24SQLConn.ConnectionString%20%3D%20(%22Data%20Source%3D%24SQLServer%3B%20Initial%20Catalog%3D%24DBName%3B%20Integrated%20Security%3DSSPI%22)%3CBR%20%2F%3E%24SQLConn.Open()%3CBR%20%2F%3E%24Command%20%3D%20New-Object%20System.Data.SQLClient.SQLCommand%3CBR%20%2F%3E%24Command.Connection%20%3D%20%24SQLConn%3CBR%20%2F%3E%7D%3CBR%20%2F%3E%24intRow%20%3D%201%20%23%20Row%20number%3CBR%20%2F%3E%5Bstring%5D%24strSQL%20%3D%20%22%22%3C%2FP%3E%3CP%3EWrite-Host%20''%3CBR%20%2F%3EWrite-Host%20'*********************************************************************'%3CBR%20%2F%3EWrite-Host%20%22-------%20Start%20Table%20-%20Measure%22%3CBR%20%2F%3EWrite-Host%20'*********************************************************************'%3C%2FP%3E%3CP%3Eforeach%20(%24TabTable%20in%20%24TabularDB.Model.Tables%20%7C%20Sort-Object%20Name)%3CBR%20%2F%3E%7B%3C%2FP%3E%3CP%3EWrite-Host%20%22%22%3CBR%20%2F%3EWrite-Host%20%22--------------------------------------------------------%22%3CBR%20%2F%3EWrite-Host%20%22%20%22%20%24TabTable.Name.PadRight(30)%20%22%20%22%20%24TabTable.ObjectType%3CBR%20%2F%3E%23%24TabTable%3C%2FP%3E%3CP%3Eforeach%20(%24Measure%20in%20%24TabTable.Measures%20%7C%20Sort-Object%20Name)%3CBR%20%2F%3E%7B%3CBR%20%2F%3E%3CBR%20%2F%3EWrite-Host%20%24intRow%20%22%20--%20%22%20%24Measure.Name.PadRight(30)%20%22%20%22%20%24Measure.Expression%3CBR%20%2F%3E%23%24Measure%3CBR%20%2F%3E%23%20unprocessed%20date%20come%20out%20as%20'12%2F30%2F1699%2019%3A00%3A00'%20but%20the%20SQL%20DateTime%20range%20is%20'1753-01-01'%3CBR%20%2F%3E%5BString%5D%24ModifiedTime%20%3D%20%24Measure.ModifiedTime%3CBR%20%2F%3E%5BString%5D%24StructureModifiedTime%20%3D%20%24Measure.StructureModifiedTime%3CBR%20%2F%3E%5BString%5D%24strExpression%20%3D%20%24Measure.Expression%3CBR%20%2F%3E%24strExpression%20%3D%20%24strExpression%20-replace%20%22'%22%20%2C%20%22''%22%3CBR%20%2F%3E%3CBR%20%2F%3EIF%20(%24flgDisplayOnly%20-ne%20%22FALSE%22)%3CBR%20%2F%3E%7B%3CBR%20%2F%3E%24nl%20%3D%20%5BEnvironment%5D%3A%3ANewLine%3CBR%20%2F%3E%24strSQL%20%3D%20%22INSERT%20INTO%20%5Bdbo%5D.%5BMeasure%5D%20%22%3CBR%20%2F%3E%24strSQL%20%3D%20(%24strSQL%20%2B%20%24nl%20%2B%20%22(%20%22)%3CBR%20%2F%3E%24strSQL%20%3D%20(%24strSQL%20%2B%20%24nl%20%2B%20%22%20%5BBatchLogID%5D%20%2C%20%5BRow%5D%20%22)%3CBR%20%2F%3E%24strSQL%20%3D%20(%24strSQL%20%2B%20%24nl%20%2B%20%22%2C%20%5BTable%20Name%5D%20%22)%3CBR%20%2F%3E%24strSQL%20%3D%20(%24strSQL%20%2B%20%24nl%20%2B%20%22%2C%20%5BMeasure%20Name%5D%20%22)%3CBR%20%2F%3E%24strSQL%20%3D%20(%24strSQL%20%2B%20%24nl%20%2B%20%22%2C%20%5BDataType%5D%20%22)%3CBR%20%2F%3E%24strSQL%20%3D%20(%24strSQL%20%2B%20%24nl%20%2B%20%22%2C%20%5BFormatString%5D%20%22)%3CBR%20%2F%3E%24strSQL%20%3D%20(%24strSQL%20%2B%20%24nl%20%2B%20%22%2C%20%5BExpression%5D%20%22)%3CBR%20%2F%3E%24strSQL%20%3D%20(%24strSQL%20%2B%20%24nl%20%2B%20%22%2C%20%5BIsSimpleMeasure%5D%20%22)%3CBR%20%2F%3E%24strSQL%20%3D%20(%24strSQL%20%2B%20%24nl%20%2B%20%22%2C%20%5BIsHidden%5D%20%22)%3CBR%20%2F%3E%24strSQL%20%3D%20(%24strSQL%20%2B%20%24nl%20%2B%20%22%2C%20%5BState%5D%20%22)%3CBR%20%2F%3E%24strSQL%20%3D%20(%24strSQL%20%2B%20%24nl%20%2B%20%22%2C%20%5BDisplayFolder%5D%20%22)%3CBR%20%2F%3E%24strSQL%20%3D%20(%24strSQL%20%2B%20%24nl%20%2B%20%22%2C%20%5BDescription%5D%20%22)%3CBR%20%2F%3E%24strSQL%20%3D%20(%24strSQL%20%2B%20%24nl%20%2B%20%22%2C%20%5BAnnotation%5D%20%22)%3CBR%20%2F%3E%24strSQL%20%3D%20(%24strSQL%20%2B%20%24nl%20%2B%20%22%2C%20%5BKPI%5D%20%22)%3CBR%20%2F%3E%24strSQL%20%3D%20(%24strSQL%20%2B%20%24nl%20%2B%20%22%2C%20%5BObjectType%5D%20%22)%3CBR%20%2F%3E%24strSQL%20%3D%20(%24strSQL%20%2B%20%24nl%20%2B%20%22%2C%20%5BModifiedTime%5D%20%22)%3CBR%20%2F%3E%24strSQL%20%3D%20(%24strSQL%20%2B%20%24nl%20%2B%20%22%2C%20%5BStructureModifiedTime%5D%20%22)%3CBR%20%2F%3E%24strSQL%20%3D%20(%24strSQL%20%2B%20%24nl%20%2B%20%22)%22)%3CBR%20%2F%3E%24strSQL%20%3D%20(%24strSQL%20%2B%20%24nl%20%2B%20%22VALUES%20(%20%22)%3CBR%20%2F%3E%24strSQL%20%3D%20(%24strSQL%20%2B%20%24nl%20%2B%20%22%20'%22%20%2B%20%24BatchLogID%20%2B%20%22'%20%2C%20'%22%20%2B%20%24intRow%20%2B%20%22'%20%22)%3CBR%20%2F%3E%24strSQL%20%3D%20(%24strSQL%20%2B%20%24nl%20%2B%20%22%2C%20'%22%20%2B%20%24TabTable.Name%20%2B%20%22'%20%22)%3CBR%20%2F%3E%24strSQL%20%3D%20(%24strSQL%20%2B%20%24nl%20%2B%20%22%2C%20'%22%20%2B%20%24Measure.name%20%2B%20%22'%20%22)%3CBR%20%2F%3E%24strSQL%20%3D%20(%24strSQL%20%2B%20%24nl%20%2B%20%22%2C%20'%22%20%2B%20%24Measure.DataType%20%2B%20%22'%20%22)%3CBR%20%2F%3E%24strSQL%20%3D%20(%24strSQL%20%2B%20%24nl%20%2B%20%22%2C%20'%22%20%2B%20%24Measure.FormatString%20%2B%20%22'%20%22)%3CBR%20%2F%3E%24strSQL%20%3D%20(%24strSQL%20%2B%20%24nl%20%2B%20%22%2C%20'%22%20%2B%20%24strExpression%20%2B%20%22'%20%22)%3CBR%20%2F%3E%24strSQL%20%3D%20(%24strSQL%20%2B%20%24nl%20%2B%20%22%2C%20'%22%20%2B%20%24Measure.IsSimpleMeasure%20%2B%20%22'%20%22)%3CBR%20%2F%3E%24strSQL%20%3D%20(%24strSQL%20%2B%20%24nl%20%2B%20%22%2C%20'%22%20%2B%20%24Measure.IsHidden%20%2B%20%22'%20%22)%3CBR%20%2F%3E%24strSQL%20%3D%20(%24strSQL%20%2B%20%24nl%20%2B%20%22%2C%20'%22%20%2B%20%24Measure.State%20%2B%20%22'%20%22)%3CBR%20%2F%3E%24strSQL%20%3D%20(%24strSQL%20%2B%20%24nl%20%2B%20%22%2C%20'%22%20%2B%20%24Measure.DisplayFolder%20%2B%20%22'%20%22)%3CBR%20%2F%3E%24strSQL%20%3D%20(%24strSQL%20%2B%20%24nl%20%2B%20%22%2C%20'%22%20%2B%20%24Measure.Description%20%2B%20%22'%20%22)%3CBR%20%2F%3E%24strSQL%20%3D%20(%24strSQL%20%2B%20%24nl%20%2B%20%22%2C%20'%22%20%2B%20%24Measure.Annotations%20%2B%20%22'%20%22)%3CBR%20%2F%3E%24strSQL%20%3D%20(%24strSQL%20%2B%20%24nl%20%2B%20%22%2C%20'%22%20%2B%20%24Measure.KPI%20%2B%20%22'%20%22)%3CBR%20%2F%3E%24strSQL%20%3D%20(%24strSQL%20%2B%20%24nl%20%2B%20%22%2C%20'%22%20%2B%20%24Measure.ObjectType%20%2B%20%22'%20%22)%3CBR%20%2F%3E%24strSQL%20%3D%20(%24strSQL%20%2B%20%24nl%20%2B%20%22%2C%20'%22%20%2B%20%24ModifiedTime%20%2B%20%22'%20%22)%3CBR%20%2F%3E%24strSQL%20%3D%20(%24strSQL%20%2B%20%24nl%20%2B%20%22%2C%20'%22%20%2B%20%24StructureModifiedTime%20%2B%20%22'%20%22)%3CBR%20%2F%3E%24strSQL%20%3D%20(%24strSQL%20%2B%20%24nl%20%2B%20%22)%20%22)%3CBR%20%2F%3E%24Command.CommandText%20%3D%20%24strSQL%3CBR%20%2F%3E%24Command.ExecuteNonQuery()%3CBR%20%2F%3E%7D%3CBR%20%2F%3E%24intRow%2B%2B%3CBR%20%2F%3E%7D%3CBR%20%2F%3E%7D%3CBR%20%2F%3EIF%20(%24flgDisplayOnly%20-ne%20%22FALSE%22)%3CBR%20%2F%3E%7B%3CBR%20%2F%3E%24SQLConn.Close()%3CBR%20%2F%3EWrite-Host%20%22-------%20SQL%20Connection%20Closed%20-----------------------------------------------%22%3CBR%20%2F%3E%7D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eplease%20let%20me%20know%20any%20more%20details%20required.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3ESKM%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1075561%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EPowerShell%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
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