Populate PowerBI with Employee Naming and Org MetaData for Visualization Reports

MVPersonMetadata2PowerBI.ps1

# Install-Module PowerBIPS -RequiredVersion 1.2.0.9 -Force
Import-Module PowerBIPS -RequiredVersion 1.2.0.9
Import-Module LithnetMiisAutomation

$credentials = Get-Credential
$clientID = "4036df76-4de6-43cb-afe6-1234567890"

$authtoken = Get-PBIAuthToken -ClientId $clientID -Credential $credentials
# MV Users
$OrgMVResults_raw = Get-MVObject -ObjectType person    
$OrgMVResults = $OrgMVResults_raw | Where-Object {($_.Attributes.employeeActive.Values.ValueBoolean -eq $True)}
$OrgMVResults.Count

$EmpPBIOrdDataLoad = @()
foreach ($OrgUser in $OrgMVResults){
    # Build PS Object for injection to PBI
    if ($OrgUser.Attributes.accountName.Values.valueString){$EmpAccount = $OrgUser.Attributes.accountName.Values.valueString}else{$EmpAccount = $null}
    if ($OrgUser.Attributes.birthDate.Values.valueString){[dateTime]$EmpBirthDate = [dateTime]$OrgUser.Attributes.birthDate.Values.valueString}else{[dateTime]$EmpBirthDate = "1900-01-01T00:00:00.000"}
    if ($OrgUser.Attributes.employeeEndDate.Values.valueString){[dateTime]$EmpEndDate = [dateTime]$OrgUser.Attributes.employeeEndDate.Values.valueString}else{[dateTime]$EmpEndDate = "2100-01-01T00:00:00.000"}
    if ($OrgUser.Attributes.employeeStartDate.Values.valueString){[dateTime]$EmpStartDate = [dateTime]$OrgUser.Attributes.employeeStartDate.Values.valueString}else{$EmpStartDate = "1980-01-01T00:00:00.000"}
    if ($OrgUser.Attributes.employeeActive.Values.valueBoolean){[boolean]$EmpActive = $OrgUser.Attributes.employeeActive.Values.valueBoolean}else{[boolean]$EmpActive = $false}
    if ($OrgUser.Attributes.ExchangeMailboxEnabled.Values.valueBoolean){[boolean]$EmpMailbox = $OrgUser.Attributes.ExchangeMailboxEnabled.Values.valueBoolean}else{[boolean]$EmpMailbox = $false}
    if ($OrgUser.Attributes.lyncEnabled.Values.valueBoolean){[boolean]$EmpLync = $OrgUser.Attributes.lyncEnabled.Values.valueBoolean}else{[boolean]$EmpLync = $false}
    if ($OrgUser.Attributes.city.Values.valueString){$EmpCity = $OrgUser.Attributes.city.Values.valueString}else{$EmpCity = $null}
    if ($OrgUser.Attributes.displayName.Values.valueString){$EmpDisplay = $OrgUser.Attributes.displayName.Values.valueString}else{$EmpDisplay = $null}
    if ($OrgUser.Attributes.company.Values.valueString){$EmpCompany = $OrgUser.Attributes.company.Values.valueString}else{$EmpCompany = $null}
    if ($OrgUser.Attributes.department.Values.valueString){$EmpDept = $OrgUser.Attributes.department.Values.valueString}else{$EmpDept = $null}
    if ($OrgUser.Attributes.division.Values.valueString){$EmpDiv = $OrgUser.Attributes.division.Values.valueString}else{$EmpDiv = $null}
    if ($OrgUser.Attributes.employeeType.Values.valueString){$EmpType = $OrgUser.Attributes.employeeType.Values.valueString}else{$EmpType = $null}
    if ($OrgUser.Attributes.officeLocation.Values.valueString){$EmpOffice = $OrgUser.Attributes.officeLocation.Values.valueString}else{$EmpOffice = $null}
    if ($OrgUser.Attributes.postalCode.Values.valueString){$EmpPCode = $OrgUser.Attributes.postalCode.Values.valueString}else{$EmpPCode = $null}
    if ($OrgUser.Attributes.st.Values.valueString){$EmpState = $OrgUser.Attributes.st.Values.valueString}else{$EmpState = $null}
    if ($OrgUser.Attributes.street.Values.valueString){$EmpStreet = $OrgUser.Attributes.street.Values.valueString}else{$EmpStreet = $null}
    if ($OrgUser.Attributes.timezone.Values.valueString){$EmpTZ = $OrgUser.Attributes.timezone.Values.valueString}else{$EmpTZ = $null}
    if ($OrgUser.Attributes.title.Values.valueString){$EmpTitle = $OrgUser.Attributes.title.Values.valueString}else{$EmpTitle = $null}

    #Manager 
    foreach ($ref in $orgUser.DNAttributes){
        if ($ref.Name -eq 'manager'){
            $MgrDN = [array]$ref.Values.Item(0).DN
            $Manager = $MgrDN | Get-MVObject
            if ($Manager){
                $EmpMgrAccountName = $Manager.Attributes.accountName.Values.value
                $EmpMgrDisplayName = $Manager.Attributes.displayName.Values.value
            }else {
                $EmpMgrAccountName = $null
                $EmpMgrDisplayName = $null 
            }
        }
    }
 
    $EmpRecord = New-Object PSObject -prop @{        
        accountName = $EmpAccount
        displayName = $EmpDisplay
        birthDate = $EmpBirthDate
        city = $EmpCity
        company = $EmpCompany    
        department = $EmpDept
        division = $EmpDiv
        employeeType = $EmpType
        employeeActive = $EmpActive
        exchangeMailbox = $EmpMailbox
        lyncEnabled = $EmpLync
        managerID = $EmpMgrAccountName
        managerName = $EmpMgrDisplayName
        officeLocation = $EmpOffice
        postalCode = $EmpPCode
        state = $EmpState
        street = $EmpStreet
        timezone = $EmpTZ
        title = $EmpTitle
        employeeStartDate = $EmpStartDate
        employeeEndDate = $EmpEndDate
    }
    
    $EmpPBIOrdDataLoad += $EmpRecord
}

$EmpPBIOrdDataLoad.Count

if($EmpPBIOrdDataLoad.Count -gt 0){ 
    # Write Out the User data to PowerBI
    $DataSet = Get-PBIDataSet -authToken $authToken -Name "EmployeePolyArchyData" 
    # Clear Table 
    Clear-PBITableRows -authToken $authtoken -dataSetId $DataSet.id -tableName EmployeeOrgInfoFull -Verbose    
    $EmpPBIOrdDataLoad | Out-PowerBI -AuthToken $authToken -dataSetName $DataSet.name -tableName EmployeeOrgInfoFull -verbose
}