Dutch Data Dude - BI, Big Data and Advanced Analytics blog

Power BI and Cortana integration explored

on December 2, 2015

With the big news of the Power BI and Cortana integration I could not wait until next week to publish this short video of me demo-ing this cool technology! In the video I ask Cortana a couple of questions on stats from my a part of my blog that I record using Google Analytics. How cool is that? This shows the unique ability of Microsoft to integrate a BI technology such as Power BI with Windows to make it very easy for users to get the information they need when they need it where they need it. Do you speak BI? Great stuff don’t you think?

Automatically building a Microsoft BI machine using PowerShell – Active Directory Setup (post #7)

on December 1, 2015

by Jeroen ter Heerdt 3 Comments

This post is #7 in the series to automatically build a Microsoft BI machine using PowerShell – see the start of series.

In this series so far:

Start of series – introduction and layout of subjects
Post #2 – Preparation: install files using Azure disk
Post #3 – Preparation: install files using Azure File Service
Post #4 –Preparation: logging infrastructure
Post #5 – Master script
Post #6 – Disabling Internet Explorer Enhanced Security Configuration

In this step we will set up Active Directory. This script has been inspired on http://blogs.technet.com/b/ashleymcglone/archive/2013/04/18/touch-free-powershell-dcpromo-in-windows-server-2012.aspx.

#Set up Active Directory
#source: http://blogs.technet.com/b/ashleymcglone/archive/2013/04/18/touch-free-powershell-dcpromo-in-windows-server-2012.aspx
Function SetupActiveDirectory {
    Param(
        [Parameter(Mandatory=$true,HelpMessage="Domain name required, please specify in format yyy.zzz")]
        [ValidateNotNullOrEmpty()]
        $DomainName
    )
    Write-Log -Verbose  "Step 2: Set up Active Directory"
    
    try {
        Install-WindowsFeature -Name AD-Domain-Services -IncludeManagementTools
        if ($global:DoAllTasks) {
            Set-Restart-AndResume $global:script "3"
        }
    }
    catch {
        Write-Log -Verbose  "Failed to set up Active Directory. Error: $_.Exception.Message"
    }
}
Function SetupActiveDirectoryPart2 {
    Param(
        [Parameter(Mandatory=$true,HelpMessage="Domain name required, please specify in format yyy.zzz")]
        [ValidateNotNullOrEmpty()]
        $DomainName
    )
    Write-Log -Verbose  "Step 2: Set up Active Directory"
    
    try {
        Import-Module ADDSDeployment
        $dotposition = $DomainName.LastIndexOf('.')
        $netbiosname = $DomainName.Substring(0,$dotposition)
        $result = Install-ADDSForest -DomainName $DomainName -InstallDNS:$true -Confirm:$false -NoRebootOnCompletion:$true -Force:$true -DatabasePath "C:\Windows\NTDS" -DomainMode Win2012R2 -ForestMode Win2012R2 -LogPath "C:\Windows\NTDS" -SysvolPath "C:\Windows\SYSVOL" -DomainNetbiosName $netbiosname
        Write-Log -Verbose  "Active Directory set up done"
        if ($global:DoAllTasks) {
            Set-Restart-AndResume $global:script "4"
        }
    }
    catch {
        Write-Log -Verbose  "Failed to set up Active Directory. Error: $_.Exception.Message"
    }
}

#Set up Active Directory

#source: http://blogs.technet.com/b/ashleymcglone/archive/2013/04/18/touch-free-powershell-dcpromo-in-windows-server-2012.aspx

Function SetupActiveDirectory {

Param(

[Parameter(Mandatory=$true,HelpMessage="Domain name required, please specify in format yyy.zzz")]

[ValidateNotNullOrEmpty()]

$DomainName

)

Write-Log -Verbose "Step 2: Set up Active Directory"

try {

Install-WindowsFeature -Name AD-Domain-Services -IncludeManagementTools

if ($global:DoAllTasks) {

Set-Restart-AndResume $global:script "3"

}

catch {

Write-Log -Verbose "Failed to set up Active Directory. Error: $_.Exception.Message"

}

Function SetupActiveDirectoryPart2 {

Param(

[Parameter(Mandatory=$true,HelpMessage="Domain name required, please specify in format yyy.zzz")]

[ValidateNotNullOrEmpty()]

$DomainName

)

Write-Log -Verbose "Step 2: Set up Active Directory"

try {

Import-Module ADDSDeployment

$dotposition = $DomainName.LastIndexOf('.')

$netbiosname = $DomainName.Substring(0,$dotposition)

$result = Install-ADDSForest -DomainName $DomainName -InstallDNS:$true -Confirm:$false -NoRebootOnCompletion:$true -Force:$true -DatabasePath "C:\Windows\NTDS" -DomainMode Win2012R2 -ForestMode Win2012R2 -LogPath "C:\Windows\NTDS" -SysvolPath "C:\Windows\SYSVOL" -DomainNetbiosName $netbiosname

Write-Log -Verbose "Active Directory set up done"

if ($global:DoAllTasks) {

Set-Restart-AndResume $global:script "4"

}

catch {

Write-Log -Verbose "Failed to set up Active Directory. Error: $_.Exception.Message"

}

Next step: configuring a very permissive password policy.

Automatically building a Microsoft BI machine using PowerShell – Disabling Internet Explorer Enhanced Security Configuration (post #6)

on November 24, 2015

by Jeroen ter Heerdt 2 Comments

This post is #6 in the series to automatically build a Microsoft BI machine using PowerShell – see the start of series.

In this series so far:

In this step we will disable the Internet Explorer Enhanced Security Configuration. In general IEESC is a great idea, but on demo machines it is not very useful and makes the demo less usable. This script comes from http://itproctology.blogspot.nl/2013/09/powershell-to-disable-ie-enhanced.html:

#Disables Internet Explorer Enhanced Security Configuration
#source: http://itproctology.blogspot.nl/2013/09/powershell-to-disable-ie-enhanced.html
Function DisableIEESC {
    Write-Log -Verbose "Step 1: Disable Internet Explorer Enhanced Security"
    try {
        $AdminKey = “HKLM:\SOFTWARE\Microsoft\Active Setup\Installed Components\{A509B1A7-37EF-4b3f-8CFC-4F3A74704073}”
        $UserKey = “HKLM:\SOFTWARE\Microsoft\Active Setup\Installed Components\{A509B1A8-37EF-4b3f-8CFC-4F3A74704073}”
        Set-ItemProperty -Path $AdminKey -Name “IsInstalled” -Value 0
        Set-ItemProperty -Path $UserKey -Name “IsInstalled” -Value 0
        Stop-Process -Name Explorer
        Write-Log -Verbose  "IE ESC succesfully disabled"
        if ($global:DoAllTasks) {
            Set-Restart-AndResume $global:script "2"
        }
    }
    catch {
        Write-Log -Verbose  "Failed to disable IE ESC. Error: $_.Exception.Message"
    }
}

#Disables Internet Explorer Enhanced Security Configuration

#source: http://itproctology.blogspot.nl/2013/09/powershell-to-disable-ie-enhanced.html

Function DisableIEESC {

Write-Log -Verbose "Step 1: Disable Internet Explorer Enhanced Security"

try {

$AdminKey = “HKLM:\SOFTWARE\Microsoft\Active Setup\Installed Components\{A509B1A7-37EF-4b3f-8CFC-4F3A74704073}”

$UserKey = “HKLM:\SOFTWARE\Microsoft\Active Setup\Installed Components\{A509B1A8-37EF-4b3f-8CFC-4F3A74704073}”

Set-ItemProperty -Path $AdminKey -Name “IsInstalled” -Value 0

Set-ItemProperty -Path $UserKey -Name “IsInstalled” -Value 0

Stop-Process -Name Explorer

Write-Log -Verbose "IE ESC succesfully disabled"

if ($global:DoAllTasks) {

Set-Restart-AndResume $global:script "2"

}

catch {

Write-Log -Verbose "Failed to disable IE ESC. Error: $_.Exception.Message"

}

Next step: set up Active Directory.

Automatically building a Microsoft BI machine using PowerShell – master script (post #5)

on November 17, 2015

by Jeroen ter Heerdt 0 Comments

This post is #5 in the series to automatically build a Microsoft BI machine using PowerShell – see the start of series.

In this series so far:

Now that we have our preparation completed, it is time to present the master script. This script will be called by the user with parameters specifying what to install; also this script will call other scripts to install components and potentially reboot the machine and resume working. My master script is called ‘SetupMSBIDemoMachine.ps1’. It has one master switch called -DoAllTasks, what does as it says. Also, it provides switches to just executed a part of the total install, such as just installing SQL Server by specifying –InstallSQLServer. Optionally, this script can do automatic reboots of the server and auto-resume working after the reboot; very useful when –DoAllTasks is specified.

A sample call that would complete the full install with a certain domainname and passphrase (for SharePoint) and also auto reboots the machine would look like this:

.\SetupMSBIDemoMachine -DoAllTasks -DomainName mydomain.local -passphrase pass@word1 -AutoReboot

1	.\SetupMSBIDemoMachine -DoAllTasks -DomainName mydomain.local -passphrase pass@word1 -AutoReboot

Just running .\SetupMSBIDemoMachine -? returns the following info, which shows all the parameters available. The parameters map to the steps outline in the start of this series. Again, -DoAllTasks would mean just executing these steps in turn.

NAME
    C:\Users\jterh\OneDrive - Microsoft\Demo Machine\SetupMSBIDemoMachine.ps1
    
SYNOPSIS
    Installs and sets up a MSBI Demo Machine in a number of steps
    
    
SYNTAX
    C:\Users\jterh\OneDrive - Microsoft\Demo Machine\SetupMSBIDemoMachine.ps1 [-DisableIEESC] 
    [-SetupActiveDirectory] [[-DomainName] ] [-ConfigurePasswordPolicy] 
    [-InstallSystemCenterEndpointProtection] [-InstallSQLServer] [-InstallSharePoint] 
    [-InstallPowerPivot] [-ConfigurePowerPivot] [-ConfigurePowerPivotPart2] [[-passphrase] ] 
    [-DoAllTasks] [[-Password] <String>] [[-Step] <String>] [-AutoReboot] [<CommonParameters>]
    
    
DESCRIPTION
    

RELATED LINKS

REMARKS
    To see the examples, type: "get-help C:\Users\jterh\OneDrive - Microsoft\Demo 
    Machine\SetupMSBIDemoMachine.ps1 -examples".
    For more information, type: "get-help C:\Users\jterh\OneDrive - Microsoft\Demo 
    Machine\SetupMSBIDemoMachine.ps1 -detailed".
    For technical information, type: "get-help C:\Users\jterh\OneDrive - Microsoft\Demo 
    Machine\SetupMSBIDemoMachine.ps1 -full".

NAME

C:\Users\jterh\OneDrive - Microsoft\Demo Machine\SetupMSBIDemoMachine.ps1

SYNOPSIS

Installs and sets up a MSBI Demo Machine in a number of steps

SYNTAX

C:\Users\jterh\OneDrive - Microsoft\Demo Machine\SetupMSBIDemoMachine.ps1 [-DisableIEESC]

[-SetupActiveDirectory] [[-DomainName] ] [-ConfigurePasswordPolicy]

[-InstallSystemCenterEndpointProtection] [-InstallSQLServer] [-InstallSharePoint]

[-InstallPowerPivot] [-ConfigurePowerPivot] [-ConfigurePowerPivotPart2] [[-passphrase] ]

[-DoAllTasks] [[-Password] <String>] [[-Step] <String>] [-AutoReboot] [<CommonParameters>]

DESCRIPTION

RELATED LINKS

REMARKS

To see the examples, type: "get-help C:\Users\jterh\OneDrive - Microsoft\Demo

Machine\SetupMSBIDemoMachine.ps1 -examples".

For more information, type: "get-help C:\Users\jterh\OneDrive - Microsoft\Demo

Machine\SetupMSBIDemoMachine.ps1 -detailed".

For technical information, type: "get-help C:\Users\jterh\OneDrive - Microsoft\Demo

Machine\SetupMSBIDemoMachine.ps1 -full".

Part 1: Parameter binding

[CmdletBinding()]
Param(
[switch]$DisableIEESC,
[switch]$SetupActiveDirectory,
[string]$DomainName,
[switch]$ConfigurePasswordPolicy,
[switch]$InstallSystemCenterEndpointProtection,
[switch]$InstallSQLServer,
[switch]$InstallSharePoint,
[switch]$InstallPowerPivot,
[switch]$ConfigurePowerPivot,
[switch]$ConfigurePowerPivotPart2,
[string]$passphrase,
[switch]$DoAllTasks,
[string]$Password="pass@word1",
[string]$Step="1",
[switch]$AutoReboot=$false
)

[CmdletBinding()]

Param(

[switch]$DisableIEESC,

[switch]$SetupActiveDirectory,

[string]$DomainName,

[switch]$ConfigurePasswordPolicy,

[switch]$InstallSystemCenterEndpointProtection,

[switch]$InstallSQLServer,

[switch]$InstallSharePoint,

[switch]$InstallPowerPivot,

[switch]$ConfigurePowerPivot,

[switch]$ConfigurePowerPivotPart2,

[string]$passphrase,

[switch]$DoAllTasks,

[string]$Password="pass@word1",

[string]$Step="1",

[switch]$AutoReboot=$false

)

This part of the script binds to the parameters and specifies defaults for the password to be used for service accounts and the internal $Step variable. Also, note that by default AutoReboot is disabled.

Part 2: Imports

# -------------------------------------
# Imports
# -------------------------------------
$global:script = $myInvocation.MyCommand.Definition
$scriptPath = Split-Path -parent $global:script
. (Join-Path $scriptpath RestartAndResumeFunctions.ps1)
. (Join-Path $scriptpath DisableIEESC.ps1)
. (Join-Path $scriptPath Set-Restart-AndResume.ps1)
. (Join-Path $scriptPath SetupActiveDirectory.ps1)
. (Join-Path $scriptPath ConfigurePasswordPolicy.ps1)
. (Join-Path $scriptPath InstallSystemCenterEndpointProtection.ps1)
. (Join-Path $scriptPath CreateServiceAccount.ps1)
. (Join-Path $scriptPath InstallSQLServer.ps1)
. (Join-Path $scriptPath InstallSharePoint.ps1)
. (Join-Path $scriptPath InstallPowerPivot.ps1)
. (Join-Path $scriptPath ConfigurePowerPivot.ps1)

# -------------------------------------

# Imports

# -------------------------------------

$global:script = $myInvocation.MyCommand.Definition

$scriptPath = Split-Path -parent $global:script

. (Join-Path $scriptpath RestartAndResumeFunctions.ps1)

. (Join-Path $scriptpath DisableIEESC.ps1)

. (Join-Path $scriptPath Set-Restart-AndResume.ps1)

. (Join-Path $scriptPath SetupActiveDirectory.ps1)

. (Join-Path $scriptPath ConfigurePasswordPolicy.ps1)

. (Join-Path $scriptPath InstallSystemCenterEndpointProtection.ps1)

. (Join-Path $scriptPath CreateServiceAccount.ps1)

. (Join-Path $scriptPath InstallSQLServer.ps1)

. (Join-Path $scriptPath InstallSharePoint.ps1)

. (Join-Path $scriptPath InstallPowerPivot.ps1)

. (Join-Path $scriptPath ConfigurePowerPivot.ps1)

This part join-paths to make sure we have all the items we need; the script uses restart and resume functions as an include, these functions enable auto restart and resume of the tasks (available in RestartAndResumeFunctions.ps1). The other scripts included here are the scripts that actually do the work of installing and configuring services.

Part 3: Parameter passing

$global:DoAllTasks = $DoAllTasks
$global:AutoReboot = $AutoReboot
Set-Location $scriptPath

#get the passed parameters
$Myparameters = $myinvocation.BoundParameters
#remove step from the list
$Myparameters.Remove("Step")
#build parameter string
$global:line = ""
foreach ($key in $Myparameters.keys)
{
    $value = (get-variable $key).Value 
    #is this a switch
    if($value -eq $true) {
        $global:line+= " -"+$key
    }
    else
    {
        $global:line+=" -"+$key+" "+$value
    }
}

$global:DoAllTasks = $DoAllTasks

$global:AutoReboot = $AutoReboot

Set-Location $scriptPath

#get the passed parameters

$Myparameters = $myinvocation.BoundParameters

#remove step from the list

$Myparameters.Remove("Step")

#build parameter string

$global:line = ""

foreach ($key in $Myparameters.keys)

{

$value = (get-variable $key).Value

#is this a switch

if($value -eq $true) {

$global:line+= " -"+$key

}

else

{

$global:line+=" -"+$key+" "+$value

}

This part is used to pass parameters between the master script and downstream scripts, even after auto reboot.

Part 4: Setting global variables

#Set the hostname
$global:HostName = hostname
$global:HostNameFull = $HostName
$global:HostNameFull += ".cloudapp.net"
$global:httpHostName = "http://"
$global:httpHostName += $HostName
#Set current user name
$global:currentUserName = [System.Security.Principal.WindowsIdentity]::GetCurrent().Name;
#Path to SQL ISO
$global:pathToSQLISO = ".\Resources\SQLServer2014DeveloperEdition\en_sql_server_2014_developer_edition_x64_dvd_3940406.iso"
$global:pathToSQLISO = Resolve-Path $global:pathToSQLISO
#Path to SHarePoint ISO
$global:pathToSharePointISO = ".\Resources\SharePoint2013\en_sharepoint_server_2013_with_sp1_x64_dvd_3823428.iso"
$global:pathToSharePointISO = Resolve-Path $global:pathToSharePointISO
#Path to SharePoint Prerequisites
$global:SharePoint2013Path = ".\Resources\SharePoint2013"
$global:SharePoint2013Path = Resolve-Path $global:SharePoint2013Path
#Domain Vars
#$global:path = "CN=Managed Service Accounts,"
$global:path = "CN=Users,"
$global:root = [ADSI]''
$global:dn = $global:root.distinguishedName
$global:path += $global:dn
$global:domainpart = (gwmi Win32_NTDomain).DomainName
#SPFarm Account Name
$global:spAccount = "SPFarm"

#Set the hostname

$global:HostName = hostname

$global:HostNameFull = $HostName

$global:HostNameFull += ".cloudapp.net"

$global:httpHostName = "http://"

$global:httpHostName += $HostName

#Set current user name

$global:currentUserName = [System.Security.Principal.WindowsIdentity]::GetCurrent().Name;

#Path to SQL ISO

$global:pathToSQLISO = ".\Resources\SQLServer2014DeveloperEdition\en_sql_server_2014_developer_edition_x64_dvd_3940406.iso"

$global:pathToSQLISO = Resolve-Path $global:pathToSQLISO

#Path to SHarePoint ISO

$global:pathToSharePointISO = ".\Resources\SharePoint2013\en_sharepoint_server_2013_with_sp1_x64_dvd_3823428.iso"

$global:pathToSharePointISO = Resolve-Path $global:pathToSharePointISO

#Path to SharePoint Prerequisites

$global:SharePoint2013Path = ".\Resources\SharePoint2013"

$global:SharePoint2013Path = Resolve-Path $global:SharePoint2013Path

#Domain Vars

#$global:path = "CN=Managed Service Accounts,"

$global:path = "CN=Users,"

$global:root = [ADSI]''

$global:dn = $global:root.distinguishedName

$global:path += $global:dn

$global:domainpart = (gwmi Win32_NTDomain).DomainName

#SPFarm Account Name

$global:spAccount = "SPFarm"

Here some items are set up, such as the hostname of the machine, the current user name, the paths to ISO files for SharePoint and SQL. Also, the account name for the SharePoint farm account is specified here.

Part 5: the actual program

#ACTUAL PROGRAM

#STEP 1 - Disable IE ESC
if ($DisableIEESC -or ($DoAllTasks -and (Should-Run-Step "1"))) {
    DisableIEESC
}
#Step 2 - Setup AD
if ($SetupActiveDirectory -or ($DoAllTasks -and (Should-Run-Step "2"))) {
    SetupActiveDirectory -DomainName $DomainName
}
#Step 3 - Configure Password Policy
if ($ConfigurePasswordPolicy -or ($DoAllTasks -and (Should-Run-Step "3"))) {
    ConfigurePasswordPolicy -DomainName $DomainName
}
#Step 4 - Install System Center Endpoint Protection
if($InstallSystemCenterEndpointProtection -or ($DoAllTasks -and (Should-Run-Step "4"))) {
    InstallSystemCenterEndpointProtection
}
#Step 5 - Install SQL Server
if($InstallSQLServer -or ($DoAllTasks -and (Should-Run-Step "5"))) {
    InstallSQLServer -Password $Password
}
#Step 6- Install SharePoint
if($InstallSharePoint -or ($DoAllTasks -and (Should-Run-Step "6"))) {
    InstallSharePoint
}
#Step 7- Install PowerPivot
if($InstallPowerPivot -or ($DoAllTasks -and (Should-Run-Step "7"))) {
    InstallPowerPivot -Password $Password
}
#Step 8 - Configure PowerPivot
if($ConfigurePowerPivot -or ($DoAllTasks -and (Should-Run-Step "8"))) {
    ConfigurePowerPivot -passphrase $passphrase -Password $Password
}
#Step 9 - Configure PowerPivot Part 2
if($ConfigurePowerPivotPart2 -or ($DoAllTasks -and (Should-Run-Step "9"))) {
    ConfigurePowerPivotPart2 -passphrase $passphrase -Password $Password
}

#ACTUAL PROGRAM

#STEP 1 - Disable IE ESC

if ($DisableIEESC -or ($DoAllTasks -and (Should-Run-Step "1"))) {

DisableIEESC

}

#Step 2 - Setup AD

if ($SetupActiveDirectory -or ($DoAllTasks -and (Should-Run-Step "2"))) {

SetupActiveDirectory -DomainName $DomainName

}

#Step 3 - Configure Password Policy

if ($ConfigurePasswordPolicy -or ($DoAllTasks -and (Should-Run-Step "3"))) {

ConfigurePasswordPolicy -DomainName $DomainName

}

#Step 4 - Install System Center Endpoint Protection

if($InstallSystemCenterEndpointProtection -or ($DoAllTasks -and (Should-Run-Step "4"))) {

InstallSystemCenterEndpointProtection

}

#Step 5 - Install SQL Server

if($InstallSQLServer -or ($DoAllTasks -and (Should-Run-Step "5"))) {

InstallSQLServer -Password $Password

}

#Step 6- Install SharePoint

if($InstallSharePoint -or ($DoAllTasks -and (Should-Run-Step "6"))) {

InstallSharePoint

}

#Step 7- Install PowerPivot

if($InstallPowerPivot -or ($DoAllTasks -and (Should-Run-Step "7"))) {

InstallPowerPivot -Password $Password

}

#Step 8 - Configure PowerPivot

if($ConfigurePowerPivot -or ($DoAllTasks -and (Should-Run-Step "8"))) {

ConfigurePowerPivot -passphrase $passphrase -Password $Password

}

#Step 9 - Configure PowerPivot Part 2

if($ConfigurePowerPivotPart2 -or ($DoAllTasks -and (Should-Run-Step "9"))) {

ConfigurePowerPivotPart2 -passphrase $passphrase -Password $Password

}

This part of the script calls the right downstream execution script with the right parameters.

Up next: the script that disables Internet Explorer Enhanced Security Configuration.

Automatically building a Microsoft BI machine using PowerShell – preparation: logging infrastructure (post #4)

on November 10, 2015

by Jeroen ter Heerdt 2 Comments

This post is #4 in the series to automatically build a Microsoft BI machine using PowerShell – see the start of series.

In this series so far:

Start of series – introduction and layout of subjects
Post #2 – Preparation: install files using Azure disk
Post #3 – Preparation: install files using Azure File Service

Our final step in preparation is setting up a logging infrastructure. I found a very simple to use function online, see the code below:

Function Write-Log {
    [cmdletbinding()]

    Param(
    [Parameter(Position=0)]
    [ValidateNotNullOrEmpty()]
    [string]$Message
    )
    
    #Pass on the message to Write-Verbose if -Verbose was detected
    Write-Verbose $Message
    
    Write-Output "$(Get-Date) $Message" | Out-File -FilePath $global:LogFile -Append
    

} #end function

Function Write-Log {

[cmdletbinding()]

Param(

[Parameter(Position=0)]

[ValidateNotNullOrEmpty()]

[string]$Message

)

#Pass on the message to Write-Verbose if -Verbose was detected

Write-Verbose $Message

Write-Output "$(Get-Date) $Message" | Out-File -FilePath $global:LogFile -Append

} #end function

Including this function in the script enables any step to write to a log by passing a $Message to this function.

Next post will be our master script.

Speaking at Experts Live 2015 on BI and Big Data

on November 3, 2015

Uncategorized

by Jeroen ter Heerdt 0 Comments

Just a quick post: Inviting all to visit Experts Live 2015, 19th of November in Ede, the Netherlands. I will be speaking on BI and Big Data.
See http://www.expertslive.nl/ for more info on the event.
Hope to see you there! EXPERTSLIVE.5011_email-signature_spreker_630x180

Reporting on your Azure subscription made easy using Power BI

on October 27, 2015

by Jeroen ter Heerdt 2 Comments

Just released, an Azure Enterprise Subscription content pack for Power BI. Reporting on the usage of your Azure subscription does not get any easier!

Check it out: https://powerbi.microsoft.com/en-us/documentation/powerbi-content-pack-azure-enterprise/

Ultimate list of silly pie charts

on October 20, 2015

Just For Laughs

by Jeroen ter Heerdt 0 Comments

Regular readers know I hate pie charts so I just had to share this: The ultimate list of silly pie charts. Enjoy!

Power BI licensing explained: Free vs. Pro – when do you need Pro?

on September 29, 2015

by Jeroen ter Heerdt 0 Comments

The pricing table on the Power BI website does a good job at explaining when a free account is acceptable and when a pro account is required. However, it does not explain all nor is really clear (in my opinion). So, after some digging I came up with this: an step-wise wizard that helps you determine if you can use a free account for Power BI or if you need pro (below); simply answer a series of Yes/No questions and you will know if you can use free or really need pro. Please note that this is no official communication and by no means I am responsible for any errors. Use this at your own risk.

Enjoy!

Using custom .NET activities in Azure Data Factory

on September 22, 2015

by Jeroen ter Heerdt 5 Comments

Azure Data Factory provides a great number of data processing activities out of the box (for example running Hive or Pig scripts on Hadoop / HDInsight).

In many case though, you just need to run an activity that you already have built or know how to build in .NET. So, how would you go about that? Would you need to convert all those items to Hive scripts?

Actually, no. Enter Custom .NET activities. Using this you can run a .NET library on Azure Batch or HDInsight (whatever you like) and make it part of your Data Factory pipeline. Regardless of whether you use Batch or HDInsight you can just run your .NET code on it. I prefer using Batch since it provides more auto-scaling options, is cheaper and makes more sense to me in general; I mean, why run .NET code on a HDInsight service that runs Hive and Pig? It feels weird. However, if you already have HDInsight running and prefer to minimize the number of components to manage, choosing HDInsight might make more sense than using Batch.

So, how would you do this? First of all, you would need a custom activity. For this you will need to .NET class library and need to extend IDotNetActivity interface. Please refer to https://azure.microsoft.com/en-us/documentation/articles/data-factory-use-custom-activities/ for details. Trust me, it is not hard; I have done it.

Next, once you have a zip file as indicated on the page above, make sure to upload it to a Azure Blob store you can use later. The pipeline will need to know what assembly to load from where later on.

You will need to create an Azure Batch account and pool if you use that. If you decide to use HDInsight either let ADF spin one up on demand or make sure you have your HDInsight cluster ready.

You will need to create input and output tables in Azure Data Factory, as well as linked services to Storage and Batch or HDInsight. Your pipeline will look a bit like this:

{
"name": "ADFTutorialPipelineCustom",
"properties": {
"description": "Use custom activity",
"activities": [
{
"Name": "MyDotNetActivity",
"Type": "DotNetActivity",
"Inputs": [
 {
"Name": "EmpTableFromBlob"
 }
],
"Outputs": [
 {
"Name": "OutputTableForCustom"
 }
 ],
"LinkedServiceName": "AzureBatchLinkedService1",
"typeProperties": {
"AssemblyName": "AzureDataFactoryCustomActivity.dll",
"EntryPoint": "AzureDataFactoryCustomActivityNS.AzureDataFactoryCustomActivity",
"PackageLinkedService": "AzureStorageLinkedService1",
"PackageFile": "adfcustomactivity/customactivitycontainer/AzureDataFactoryCustomActivity.zip",
"extendedProperties": {
"SliceStart": "$$Text.Format('{0:yyyyMMddHH-mm}', Time.AddMinutes(SliceStart, 0))"
 }
 },
"Policy": {
"Concurrency": 1,
"ExecutionPriorityOrder": "OldestFirst",
"Retry": 3,
"Timeout": "00:30:00",
"Delay": "00:00:00"
 }
 }
 ],
"start": "2015-09-07T14:00:00Z",
"end": "2015-09-07T18:00:00Z",
"isPaused": false
 }
}

{

"name": "ADFTutorialPipelineCustom",

"properties": {

"description": "Use custom activity",

"activities": [

{

"Name": "MyDotNetActivity",

"Type": "DotNetActivity",

"Inputs": [

{

"Name": "EmpTableFromBlob"

}

"Outputs": [

{

"Name": "OutputTableForCustom"

}

"LinkedServiceName": "AzureBatchLinkedService1",

"typeProperties": {

"AssemblyName": "AzureDataFactoryCustomActivity.dll",

"EntryPoint": "AzureDataFactoryCustomActivityNS.AzureDataFactoryCustomActivity",

"PackageLinkedService": "AzureStorageLinkedService1",

"PackageFile": "adfcustomactivity/customactivitycontainer/AzureDataFactoryCustomActivity.zip",

"extendedProperties": {

"SliceStart": "$$Text.Format('{0:yyyyMMddHH-mm}', Time.AddMinutes(SliceStart, 0))"

}

"Policy": {

"Concurrency": 1,

"ExecutionPriorityOrder": "OldestFirst",

"Retry": 3,

"Timeout": "00:30:00",

"Delay": "00:00:00"

}

"start": "2015-09-07T14:00:00Z",

"end": "2015-09-07T18:00:00Z",

"isPaused": false

}

Switching from Batch to HDInsight means to changing the LinkedServiceName for the activity to point to your HDInsight or HDInsight on demand cluster.

Tables are passed to the .NET activity using a connection string, so essentially if you have both input and output tables defined as blob storage items, your custom assembly will get a connection string to the blob storage items, read the input files, do its processing and write the output files before passing on the control to ADF.

Using this framework the sky is the limit: anything you can run in .NET can now be part of your ADF processing pipeline…pretty cool!

Power BI and Cortana integration explored

Like this:

Automatically building a Microsoft BI machine using PowerShell – Active Directory Setup (post #7)

Like this:

Automatically building a Microsoft BI machine using PowerShell – Disabling Internet Explorer Enhanced Security Configuration (post #6)

Like this:

Automatically building a Microsoft BI machine using PowerShell – master script (post #5)

Like this:

Automatically building a Microsoft BI machine using PowerShell – preparation: logging infrastructure (post #4)

Like this:

Speaking at Experts Live 2015 on BI and Big Data

Like this:

Reporting on your Azure subscription made easy using Power BI

Like this:

Ultimate list of silly pie charts

Like this:

Power BI licensing explained: Free vs. Pro – when do you need Pro?

Like this:

Using custom .NET activities in Azure Data Factory

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this: