Skip to content

Power BI – Active Directory data integration

Introduction

This documentation explains how to integrate Active Directory data in a Power BI report. It explains also how to link this data with another data source.

To illustrate the method, we will integrate Active Directory Computer data and link it with SCCM Server information. 

We will only use 2 different data sources in this example.

You can find the list of available data sources and their caracteristics in the following link:

Power BI data sources – Power BI | Microsoft Docs

You can see in the Microsoft documentation that the Active Directory connector does not allow to use the direct query mode. We have use the import data mode.

Furthermore, when linking several data sources of different technologies , it is recommended to avoid the direct query mode, your report will certainly behave with bad performances.

Questions about your own setup? A dbi services consultant can look at your case.

Talk to an expert (opens in a new tab)

Also, do not forget to choose the right Power BI Desktop client application depending where you want to publish your report. Power BI Desktop for publishing on Power BI Azure service and Power BI Desktop Report Server (Power BI Desktop RS) for publishing on Power BI Report Server.

Active Directory data integration

  • Open Power BI Desktop 
  • Click the Transform data ribbon to open the Power Query Editor
  • Create a new parameter
    • Note: I always recommend to use parameter for your data source, it will ease your work, either to make a template or to change the connection in one place when you use the same data source in several queries
  • Click the Manage Parameter ribbon and select New Parameter
  • Create a new parameter by capturing a name, description, its type and define an initial value and click OK to validate.
  • Click the New Source ribbon and select the More option to open the connector list
  • Search for the Active Directory connector and select it and click the connect button
  • In the Active Directory setting window select the domain by selecting the parameter you created previously and click the OK button

At this stage you are connected to the Active Directory service. You have to know where to find the data you need. For our example we will use the computer information and specifically the name of the servers and their descriptions.

  • Search for the Computer object in the Active Directory data structure, check its checkbox  and click the OK button
  • You notice that a new query is appearing on the left pane with the original object name
  • You can change the name of the query in the Query Settings pane on the right side of the window
  • You can also notice that in the APPLIED STEP information pane you have already 2 steps. The first for the source selection and the second for the object selection
  • Now to get the data we need, we select 2 columns. Select the column Computer than while pressing the CTRL key select the Top column.
  • Right click on one of the column you selected and select Remove other columns. This will avoid to have unnecessary information in your report later
  • Expand the Computer column to get to the data
  • Unselect the (Select All Columns) to only select the information you need
  • Select the checkbox of the cn info and click OK
  • Do the same by expanding the Top column and selecting the Description column
  • The description information being a list datatype, you need to expand a second time this information to get to the data
  • Expand the top.description column and the select  the option Extract Values…
  • In the window Extract the value from list  I select None in the dropdown list as the description contains only 1 element in our case

We have now our Computer information we need

  • Notice all the steps in the Apply Steps pane on the right
  • By clicking on the gear icon you can see the details of the action done in the step

SCCM Server information integration

The data source is this time a SQL Server Database. We will apply the same method to get to the data.

  • Create first the parameter for the server connection 
  • Create a second parameter for the database name
  • Defined a new source by selecting SQL Server 
  • Choose the parameter your created previously for the server and database name
  • Select an initial object to create the query with the SQL Server connector, but we will change it immediately and replace it by a custom query and click OK
  • In the query settings change the name
  • Select the source in the steps and click the gear icon
  • In the SQL statement textbox write the query needed for the server selection on the SCCM database
  • Click OK
  • You can now remove the Navigation step used for the object selection, it is no ore needed as we used a SQL Statement instead
  • We are done with the data source selection and import, so click the Close & Apply ribbon to integrate your data in the Power BI report
  • The data will be loaded in the report
  • In our case we have some error while loading the AD Computer info, due to the empty description on some servers

Link the data sources 

Now that we have the data sources tables ready we can linked them

  • Select the Model designer mode on the left of the screen
  • Select the column computer.cn of the table AD_ComputerInfo, drag and drop it on the column Server_Name of the table ServerInfo. Automatically a 1:1 relationship will be determine

It is good for the user experience to group the information of the same type at the same location. Thus we can integrate the server description column with the other server information available

  • Select the Data designer mode on the left of the screen
  • Select the Column tools in the menu
  • Select the New column ribbon
  • Write the DAX formula to get the related information from the other table and validate with the Enter key

To avoid to confuse the report user with uneeded information, your can hide the AD_ComputerInfo table 

  • go to the Model designer mode
  • Click on the eye icon to have it strikethrough

Design your report

Now you can create you report and use the data you have integrated

  • go to the Report designer mode
  • Select the table visualization object
  • In the Values of the table visualization object drag and drop the Server_Name and Server_Description columns

Conclusion

This is of course only an introduction of what can be done with the Active Directory data integration into Power BI.

There are plenty of possibilities to follow your operation and Active Directory data is part of it. 

The integration itself is pretty simple, the complexity you will face is to find the right information in the AD data structure.

Let’s work together

Ready to operate at
the next level?

Tell us about your challenge. We’ll bring the depth, the expertise, and the Swiss precision.

Christophe Cosme

Christophe Cosme

With more than 30 years of experience in IT architecture, development, and implementation, Christophe Cosme specializes in Microsoft SQL Server administration and development, Business Intelligence with the Microsoft BI suite and cloud services, Power BI Azure service and Report Server, and Azure/AWS cloud architecture and engineering. His background also covers project management and business analysis in global organizations. Prior to dbi services, he was Solution Lead Business Intelligence at Hoffmann-La Roche in Basel and Senior Manager Head of Business Intelligence at Swiss International Air Lines. Christophe holds a Master's degree in Engineering, Automation and IT from ENSISA.View Author posts

25 posts since November 2018

Most read in Business Intelligence

The Business Intelligence posts our readers open most: the all-time favourites, and what is trending right now.

Leave a Comment

Your email address will not be published. Required fields are marked *