Create a gist now

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Populate PowerBI with Employee Naming and Org MetaData for Visualization Reports
# 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
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment