ArthurSteijn

#Author - Arthur Steijn // Motion10 // 20200708
#Original from Mathias Wrobel // Innofactor A/S 

#Other sites to provide IPv4 public address with this type of request

<#
http://ipinfo.io/ip
http://ifconfig.me/ip
http://icanhazip.com
http://ident.me

ArthurSteijn

steps:
- task: AzurePowerShell@5
  inputs:
    azureSubscription: 'YourAzureSubscriptionOrServiceConnection'
    ScriptType: 'FilePath'
    ScriptPath: 'AddDevOpsIpToAAS.ps1'
    ScriptArguments: '-ResourceName "YourAnalysisServicesName" -ResourceGroup "YourResourceGroupName"'
    azurePowerShellVersion: 'LatestVersion'

ArthurSteijn

let window = AzureDiagnostics 
| where ResourceProvider == "MICROSOFT.ANALYSISSERVICES" and Resource =~ "<MyServerName>" and DatabaseName_s =~ "<MyDatabaseName>"
| where TimeGenerated > ago(14d); 
window 
| where OperationName has "CommandEnd" and TextData_s has "<Refresh "
| extend DurationMs=extract(@"([^,]*)", 1,Duration_s, typeof(long)) 
| project StartTime_t,EndTime_t,ServerName_s,OperationName,RootActivityId_g,TextData_s,DatabaseName_s,ApplicationName_s,Duration_s,Success_s,EffectiveUsername_s,User_s,EventSubclass_s,DurationMs 
| order by StartTime_t desc 

ArthurSteijn

let window = AzureDiagnostics 
| where ResourceProvider == "MICROSOFT.ANALYSISSERVICES" and Resource =~ "<MyServerName>" and DatabaseName_s =~ "<MyDatabaseName>"
| where OperationName !hasprefix "Discover" and OperationName !hasprefix "VertiPaq" and OperationName !hasprefix "LogMetric" 
| where TimeGenerated > ago(14d);  
window 
| extend DurationMs=extract(@"([^,]*)", 1,Duration_s, typeof(long)) 
| project TimeGenerated,RootActivityId_g, ResourceId, OperationName, DatabaseName_s, level_d, EventClass_s, EventSubclass_s, StartTime_t, EndTime_t, ProgressTotal_s, ObjectReference_s, ObjectName_s, ObjectID_s, ObjectPath_s, Duration_s, CPUTime_s, Severity_s, Success_s, Error_s, TextData_s,DurationMs   
| order by TimeGenerated desc

ArthurSteijn

CREATE VIEW [dbo].[vFactSalesOrder_Parquet] AS 
 SELECT SOH.[SalesOrderID]
      ,SOH.[RevisionNumber]
      ,SOH.[OrderDate]
      ,SOH.[DueDate]
      ,SOH.[ShipDate]
      ,SOH.[Status]
      ,SOH.[OnlineOrderFlag]
      ,SOH.[SalesOrderNumber]
      ,SOH.[PurchaseOrderNumber]

ArthurSteijn

-- Create a database master key if one does not already exist, using your own password. This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<PASSWORD>' ;

-- Create a database scoped credential with Azure storage account key as the secret.
CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
WITH IDENTITY='SHARED ACCESS SIGNATURE' , 
SECRET = '<SHARED ACCESS SIGNATURE KEY>'
GO
;
-- Create an external data source with CREDENTIAL option.

ArthurSteijn

- task: AzurePowerShell@5
  name: filepathPowershelltask
  inputs:
    azureSubscription: ${{parameters.azureSubscription}}
    ScriptType: 'FilePath'
    ScriptPath:  ${{parameters.scriptFilePath}}
    ScriptArguments: ${{parameters.scriptArguments}}
    azurePowerShellVersion: 'LatestVersion'

ArthurSteijn

# This PowerShell script triggers and monitors an Azure data factory pipeline outcome.  
[CmdletBinding()]
param(
    $resourceGroupName,
    $dataFactoryName,
    $DFPipelineName
    )

$RunId = Invoke-AzDataFactoryV2Pipeline `
  -DataFactoryName $DataFactoryName `

ArthurSteijn

- stage: deploy
  jobs:
  - deployment: DeploySyn
    displayName: deploy Synapse Workspace
    pool:
      vmImage: 'Windows-latest'
    # creates an environment if it doesn't exist
    environment: 'production'
    strategy:
      runOnce:

ArthurSteijn

# Azure PowerShell 
# https://docs.microsoft.com/en-us/azure/devops/pipelines/tasks/deploy/azure-powershell?view=azure-devops
# Run a PowerShell script within an Azure environment
- task: AzurePowerShell@5
  inputs:
    azureSubscription: <YourServiceConnection> # Required. Name of Azure Resource Manager service connection
    scriptType: 'inlineScript' # Optional. Options: filePath, inlineScript
    #scriptPath: # Optional
    inline: | # '# You can write your Azure PowerShell scripts inline here. # You can also pass predefined and custom variables to this script using arguments' # Optional
      # Import needed modules