Use Visier Data in Power BI

Create reports in Power BI by fetching data from Visier.

Overview

If you want to use your Visier data in Power BI Desktop, you can use Visier's Power BI plugin to fetch aggregate and table data from Visier to create reports in Power BI. Visier users with the appropriate data security access can get data out of Visier to use in Power BI.

Prerequisites

• Have a Visier tenant that contains data.
• Have a Viser user account. You can log in with your username and password or single sign-on (SSO) if your organization uses SSO.
• Have access to data in Visier. You can only bring Visier data into Power BI if your Visier user account is assigned data security access to that data. For more information about data security in Visier, see Data Security for a Permission.
• Retrieve a Visier API key. For more information, see Generate an API Key.
• Do one of:
  • Download the VisierDataConnectorSigned.pqx file and register the certificate thumbprint. The PQX file allows you to connect to Visier from Power BI by registering Visier as a trusted third-party connector. To register the certificate, use this thumbprint: 8A3D8EF28E77A58913F13D531820E74C71D41952. For more information, see Trusted third-party connectors. If you're able to edit your registry, we recommend this method.

  • Download the VisierDataConnector.mez file. The MEZ file allows you to connect to Visier from Power BI without editing your registry.

    Note: For more information about the latest PQX and MEZ files, see File history.

Connect Visier data to Power BI

After completing the prerequisites, you're ready to connect your data to Power BI. You must first move the PQX or MEZ file into your Power BI application folder. If using the MEZ file, there is an additional step to allow Power BI to open the extension.

Step One: Move the Visier data connector file into Power BI

1. On your local device, find your Power BI application folder; for example, YourHome\Documents\Power BI Desktop.
2. In the Power BI Desktop folder, open the Custom Connectors folder.
3. In the Custom Connectors folder, paste the VisierDataConnectorSigned.pqx or VisierDataConnector.mez file.

Step Two: Allow Power BI to open the VisierDataConnector extension

1. On your local device, launch Power BI Desktop.
2. In Power BI, click File > Options and settings > Options > Security.
3. In Security, under Data Extensions, select (Not Recommended) Allow any extension to load without validation or warning.

   Note: Visier is not yet certified with Power BI. If you do not select this option, you cannot load Visier data into Power BI using the MEZ file.

4. To apply your change, click OK.
5. Restart Power BI Desktop.

Step Three: Authorize Visier access in Power BI

1. On your local device, launch Power BI Desktop.
2. In Power BI, click File > Get data > Get data to get started.
3. In the Get Data dialog, in the search bar, type Visier and then click VisierDataConnector (Beta).
4. Click Connect.
5. In the Visier Data Connector dialog, provide the following credentials:
   • Tenant vanity name: The vanity name in your Visier URL; for example, if your Visier URL is jupiter.visier.com, your tenant vanity name is jupiter.
   • Service access API key: Your Visier solution's API key; for example, YRggWt8z047rJPjyjxVZa. To get your API key, see Generate an API Key.
6. Optional: Select a login method:
   • OAuth 2.0 is the default.
   • If your organization uses single sign-on (SSO) to log into Visier and you have access to a user account that bypasses SSO, select the Non-SSO login option. For more information about setting up Bypass Users, see Set Up Single Sign-On.
   • If you are publishing the report with Visier data to the Power BI Service and scheduling data refreshes, select the Direct Username-Password option. This enables credential caching so you don't have to re-authenticate for data refresh.
7. After providing your credentials, click OK.
8. Click Sign In.
9. On the Visier sign in page, provide your user credentials (username and password or SSO) and then click Sign In.
10. Click Connect.

    Result: If successful, Power BI launches the Navigator dialog. In the Navigator dialog, you can select data from your Visier solution.

Create reports in Power BI

Now that your Visier data is accessible in Power BI, you can create reports in Power BI powered by Visier data. This section describes how to access Visier data using Power BI's Navigator and some of the settings you can use to create meaningful data visualizations. This article doesn't describe every action you can take to visualize data in Power BI. For more information about using Power BI, see Create reports and dashboards in Power BI - documentation.

Select a metric or list of records

The Navigator dialog contains a list of every analytic object that your Visier user can access; for example, Employee and Employee Exit. If you expand an object, you can then select its Metrics or Records, or form a query, as described next.

• Metrics: All metrics associated with the object. For example, if you select Employee, you can then select a metric like Headcount or Absence Ratio.
• Records: A list of members associated with the object. For example, if you select Employee, you can then select snapshot.
• JSON Aggregate query: A single-line JSON query to retrieve aggregate data for the analytic object. For more information about JSON aggregate queries, see JSON query code samples.
• JSON Snapshot query: A single-line JSON query to retrieve snapshot data for the analytic object. For more information about JSON snapshot queries, see JSON query code samples.
• Multi-Metric query: More than one metric from which to retrieve data for the analytic object.

Set the analysis context

After you select a metric or snapshot, you can then set the analysis context for your visualization.

If you selected a metric or multi-metric query, you can set the following options.

• Metrics: If you selected Multi-Metric query, select one or more metrics from the list.
• Time period count: The number of time periods to retrieve, such as 6 months.
• Time period type (optional): The type of time period to measure data, such as MONTH or QUARTER. If undefined, the default is YEAR.
• Date type: Select a method to specify the time instant to query from.
  • Update automatically (Complete & Non-complete periods): If Time direction is BACKWARD, use the start of the most recent period. If Time direction is FORWARD, use the start of the earliest period.
  • Update automatically (Complete periods): If Time direction is BACKWARD, use the start of the most recent complete period. If Time direction is FORWARD, use the start of the earliest complete period.
  • Use specified value in Date field: Use the date from which to retrieve data as defined in the Date field.
• Date (optional): The date from which to retrieve data. Required if Date type is Use specified value in Date field. Otherwise, leave empty.
• Time direction (optional): The direction to return data, such as BACKWARD. If undefined, the default is BACKWARD.
• Trailing period count (optional): The number of trailing time periods to retrieve, such as 6 months. Trailing period is only relevant for time-based metrics such as rates. If undefined but the trailing period type is defined, the default is 1.
• Trailing period type (optional): The type of trailing period to measure data, such as MONTH or QUARTER. Trailing period is only relevant for time-based metrics such as rates. If undefined but the trailing period count is defined, the default is MONTH.
• Filter by (optional): The attribute to filter the data by.
• Group by (optional): The attribute to group the data by.

If you selected a snapshot, you can set the following options.

• Properties: The attribute to retrieve data for.
• Sort by (optional): The attribute to sort the data by.
• Sort order (optional): Whether to sort results in ascending or descending order. If undefined, the default is SORT_ASCENDING.
• Date type: Select a method to specify the time instant to query from.
  • Update automatically (Complete & Non-complete periods): If Time direction is BACKWARD, use the start of the most recent period. If Time direction is FORWARD, use the start of the earliest period.
  • Update automatically (Complete periods): If Time direction is BACKWARD, use the start of the most recent complete period. If Time direction is FORWARD, use the start of the earliest complete period.
  • Use specified value in Date field: Use the date from which to retrieve data as defined in the Date field.
• Date (optional): The date from which to retrieve data. Required if Date type is Use specified value in Date field. Otherwise, leave empty.
• Time period type (optional): The type of time period to measure data, such as MONTH or QUARTER. If undefined, the default is YEAR.
• Time period count: The number of time periods to retrieve, such as 6 months.
• Time direction (optional): The direction to return data, such as BACKWARD. If undefined, the default is BACKWARD.

If you selected a JSON query, paste a single-line JSON query. For more information, see JSON query code samples.

When finished, click Apply. Power BI generates a data preview based on the defined analysis context. If the data preview looks good to you, click Load.

Adjust the data settings

Now that you have data in Power BI, you can configure the data settings to change the representation of data. For example, you can rename the metric or select the Date Hierarchy type. The following steps are a few options you can do to adjust the data settings. For more information about the Data pane, see Work with Data view in Power BI Desktop.

1. In the Data pane, expand the metric or snapshot.
2. Optional: Expand DateInRange > Date Hierarchy and then select the Date Hierarchy type, such as MONTH.
3. Optional: If you selected a metric, beside Measures, click the More options button > Rename. You can then type a new name for the selected metric.
4. Optional: If you defined any filters or group bys, rename them by clicking the More options button > Rename.

Build a visualization

With the data settings configured, you can now build your visualization with the data. The following steps are a few options you can do to build your visualization. For more information about visuals, see Visualizations in Power BI reports.

1. In the Visualizations pane, select a visual type, such as a pie chart.
2. In the Data pane, drag the group by (if available) to the Visualizations pane under Y-axis.
3. In the Data pane, drag the metric to the Visualizations pane under X-axis.
4. To hide zeros from the visual, do the following:
   1. In the Data pane, drag the metric to the Filters pane.
   2. Expand the metric.
   3. Change Show items when the value to Is greater than and 0.
   4. When finished, click Apply filter.

Save or publish your work

When finished building your report, you can save it to your local device or publish it to the web server. Either of these options retain the data at the time that you built the report. For more information, see Export reports from Power BI to PDF or Publish to web from Power BI.

Connect to Power BI Service

After creating a report in Power BI Desktop, you can optionally publish the report to Power BI Service. If you publish the report to Power BI Service, you can manually refresh your report's data or schedule data refreshes on a daily or weekly basis.

To connect to Power BI Service:

1. Install an on-premises data gateway. We recommend a standard gateway, but if your user doesn't have admin access, you can install a personal mode gateway. For more information, see Install an on-premises data gateway.

2. After the gateway is installed, set up the gateway cluster to use Visier's custom connector, as shown next. The gateway's service user must have access to your Power BI Custom Connector's folder. For more information, see Use custom data connectors with an on-premises data gateway.

   

   

3. In your report in Power BI Service, navigate to Semantic models and schedule daily or weekly data refresh, using the established connection as shown next. Ensure, the Direct Username-Password option is selected as the Authentication method.

   

   

4. Optional: To manually refresh your data in Power BI Service, click Refresh > Refresh now.

   

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
{
  "query": {
    "source": {
      "metrics": {
        "columns": [
          {
            "id": "wfaStructureSpanOfControlMeasure",
            "columnName": "Average Direct Span of Control"
          },
          {
            "id": "employeeCount",
            "columnName": "Headcount"
          }
        ]
      }
    },
    "axes": [
      {
        "dimensionLevelSelection": {
          "dimension": {
            "name": "Employee.Absence_Hours",
            "qualifyingPath": "Employee"
          },
          "levelIds": [
            "Employee.Absence_Hours_level"
          ]
        }
      }
    ],
    "filters": [
      {
        "selectionConcept": {
          "name": "ableToRemoteWork",
          "qualifyingPath": "Employee"
        }
      }
    ],
    "timeIntervals": {
      "fromDateTime": "2023-01-01T00:00:00.000",
      "intervalPeriodType": "MONTH",
      "intervalPeriodCount": 1,
      "intervalCount": 3
    }
  }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
{
    "source": {
        "analyticObject": "Employee_Exit"
    },
    "columns": [
        {
        "columnName": "Employee age",
        "columnDefinition": {
              "property": {
                "name": "Employee.Age",
                "qualifyingPath": "Employee"
              }
            }
        },
        {
        "columnName": "Employee ID",
        "columnDefinition": {
              "property": {
                "name": "Employee.EmployeeID",
                "qualifyingPath": "Employee"
              }
            }
        },
        {
            "columnName": "Effective Date",
            "columnDefinition": {
              "effectiveDateProperty": {}
            }
        }
    ],
    "sortOptions": [
        {"columnIndex": 2, "sortDirection": "SORT_DESCENDING"}
    ],
    "timeIntervals": {
      "fromInstant": "1672531200000",
      "intervalPeriodType": "MONTH",
      "intervalPeriodCount": 1,
      "intervalCount": 3
    }
}