Reporting

Introduction

The Panel platform reporting module allows tabular reports to be generated and displayed in the browser or downloaded as a file. You can generate reports to include up to 200,000 objects, and this number can be increased within the configuration settings as your system resources allow for it.

Two key features of the reporting interface are file download and point-in-time reports. Reports may be downloaded as Excel, Attribute Value Pair, Tab Delimited, or JSON files. The point-in-time report feature allows reports to be constructed as if they were being viewed a particular timestamp in the past.

Reports can be used to query, aggregate, join, and group any data collected by Identity Panel.

Report Example

Viewing Reports

Reports are viewed using the Reports tab in Identity Panel. When you first navigate to the reports tab it displays "No Report Selected". Choose a report from the dropdown and optional enter a date in the Point-in-Time text box. Then press the "Build" button.

Building a point-in-time report generates the report as if it had been viewed on the date selected. Point-in-time reports may take longer to generate, since Identity Panel must query more of the data from the database and convert it based on timestamps before it can apply query filters.

Report Controls

After a report is built you may download it as an Excel, Delimited, JSON, or XML file. Simply choose the desired format and press the download button.

Report Download

When you view a report the summary includes the name of the report, the timestamp the report was generated (since reports will be cached for a configurable amount of time), and the total number of rows in the report.

The reporting interface supports the usual table controls, including the "Select Display Fields" link to hide and show columns, and a paging interface. You can also click on any header name to re-sort in ascending or descending order.

Settings

Reports are created in the Reports section under Settings. Reports must have a Name, and may optionally have tags to group them by type. Use the Name Search and Tags Filter text boxes to restrict which reports are visible.

Report Search

Use upload button to upload a JSON file containing one or more reports.

Use the download button to download all reports that are currently visible. Reports may also be duplicated and downloaded individually.

The Save button will commit any outstanding changes to all unsaved reports.

Report Header

In the report header you must provide a Name, and optional tags. If you select they "My Reports" checkbox, the report will be attached to your user profile, and only yourself and Admins will be able to view it.

Report Header

Report Details

When you start a report or expand an existing one, the first settings Report Roles and Refresh Rate. If you select one or more roles, then users in those roles will be able to view the report, but only if they also have access to the reporting interface in Identity Panel.

Expiration

All reports are required to have a Refresh Rate. This is a cache expiration setting that specifies how long to re-serve a cached copy of a report. This greatly improves performance, since building a report can be an expensive operation and reports will get retrieved multiple times (either by multiple users or due to paging and sorting). Typical expirations would be 12 or 24 hours for a report that changes infrequently, or 15 minutes for a report that requires the most current data.

Report Pipeline

The details of report settings follow a pipeline model. The options and settings for these sections will be explored in more detail below.

  • Data Sets
    • Query Data Sets – The first step is to collect all the data that will be needed for the report. With query data sets you build out the database queries that will retrieve the information used by the report.
    • Report Data Sets – This is analogous to query data sets, but instead of just querying Identity Panel Data, you choose another report to be a data source for the current report.
  • Join Relationships – With join relationships you connect the data together. This is like the JOIN clause of a SQL query. In most reports the final data will be projected from a join relationship.
  • Report Fields – After the data sets are joined together you must project fields out. Field projections use the Identity Panel rule engine to select and transform data from the objects in the report.
  • Transformations – The final step of the report pipeline is transformations. Transformations can be used to excluded data that's not relevant with filter rules, and they can also be used to perform a variety of aggregation operations.

The final report setting is to choose a default sort order. This is the sort order that will be used in report downloads.

Report Sort

Query Data Sets

With query data sets you build out the database queries that will retrieve the information used by the report. You can think of them as being analogous to the FROM portion of a SQL query.

Query data sets are processed in the order defined. You should make your queries as specific as possible to reduce the amount of data that must be processed later in the report. By default, there is a limit of 200,000 objects which may be retrieved by all queries for a single report. This setting may be modified in config.json.

The first part of a report query is a name, which must be unique for the report, and the data type. Identity Panel uses the data type to determine what data collection to query, and to enhance Rule Engine help.

Query Type

Next you create query filters. Use the help popup to select a field from the object, and the operator dropdown and value rule to check the result. Values for the query are generated using the rule engine, so string literals should be enclosed in quotes. An object must match every clause to be returned.

You can optionally provide a sub-query. This gives a new list of clauses. The sub-query gives you the option to use a NOT or an OR operator.

Sub Query

Finally, you may use a Map Rule to transform a single object into a list of objects. This is useful when you want to retrieve part of an object that is intrinsically a list, and use each item in the list for a report row.

Map Rule

One good example for this, is using a map rule to retrieve the error instances from an MA synchronization history record. When referencing mapped objects later in the report, remember that each result from the Map rule will be converted into an object that has a Parent and Child property. In this example the Parent is the MA history record, and the Child is the error instance.

Report Data Sets

Often you want to create a report that is a refinement, aggregation, or augmentation of data in an already existing report. In these cases it may be most effective to simply select the other report as one of your data sets.

Report Data Set

To use a report data set, simply give it a short name, and select the desired input report.

Join Relationships

Join relationships are optional if you only have a single data set (although you may choose to join a data set to itself). If you have multiple data sets you must create enough joins to create a graph across all the data sets. It is also possible to re-use the same set in more than one relationship, such as when joining object records to both sides of a multi-value reference attribute.

Report Join Strip

Choosing and defining join relationships is one of the most powerful aspects of the reporting engine, and it is also one of the most challenging.

A join relationship starts with a unique name, and selecting the data sets for the left and right sides of the relationship. Later when projecting fields, you will choose a join relationship by name, and select either the left or right object to pull a value from.

Next you create a join rule for the left side, and one for the right side. The join rules should be designed so that both sides produce the same value (when converted to a string), when the objects should be joined. The reporting engine uses these rules to perform a hash join on values that are equal.

Report Join Rule

Note: in the above example, The History data set uses a Map Rule to return a list of errors, where Child.DN is the object that triggered the error. This is being joined to the DN attribute from the connector space data set.

Finally, you may optionally select whether to allow missing join data. If the checkbox is selected, then the join relationship will return a NULL for one side for objects that it can't find a relationship for. Note that this is equivalent to performing an OUTER join in SQL. You can also use a filter rule in the Transformations section to ONLY show rows where a join wasn't found.

Report Fields

Report Fields are how you get data out of your data sets and join relationships into your report. You can think of them as being analagous to the SELECT portion of a SQL query.

Report Fields

When creating report fields you should have one strip for each column in the report. You need to project both the data that's required by the report, and the data that's required for any filtering/grouping/transforming required.

  • Name – A report field must have a name. You can type a new name, or choose an existing name using the drop-down list.
  • Join – Next choose the join relationship to use as the data source for the field.
  • Side – Select either the or right side of the relationship. The dropdown will help you remember which is which by displaying the type of the object in the data set.
  • Value – Rule to extract and format the data from the object. In the above example, rule is taking the MA Run Record from the left side of the join, and using the ReverseSpecial function to lookup the name that accompanies the MA Guid in the RecordOf property.

The Join and Side fields may be left blank if there is only a single data set in the report.

Transformations

Transformations may be used for one or more of the following tasks:

  • Filtering – Use the rule engine to process report rows, and eliminate rows that should not be included in the report.
  • Grouping – Convert row records using a rule, and group rows that have the same rule value together. When using grouping, you can use aggregate functions to transform individual columns, as well as append new aggregate columns.
  • Field Transform – Use the rule engine to reformat values.

You may use as many discrete transformation steps and transformation types as needed within a single report.

Filter Transform

In this example a rule is used to show only rows where the DN contains the value "New York". Because there is no group by rule, it is unnecessary to have field transformations.

Filter Transform

Grouping Transform

In this example, a grouping rule is used. The DateTimeFormat function is used to pull the Month portion of the TimeStamp column. Every row with the same month value will get grouped together. Note that in this case, if we had more than a year's worth of data, we would probably also want to include the year as part of the grouping output.

DateTimeFormat(Data.Timestamp, "MM") + Data.Department

Grouping Transform

Because this transform has a grouping rule, we must include a field transformation for every field which we want to have present in the final report. Note that the number of rows in the final report will equal the number of unique grouping buckets.

Field Transform

For a field transformation we select the column to use from the previous projection, and optionally, provide a new name for the column.

If we have a grouping rule we must then provide an Aggregation Operator. The aggregation operator decides how to produce a single value from the list of rows in a grouping bucket.

The Transformation Rule indicates how to convert the value. If no format changes are necessary, then the Transformation Rule may be left blank.

Aggregation Operators

  • None – This is the default value for when grouping is not used
  • First – Chooses the first row in the grouping set.
  • Last – Chooses the last row in the grouping set
  • Any – Chooses an arbitrary row in the grouping set
    • Note: unless you are creating a transformation by feeding a sorted report as the data set for another report with no join relationships, First, Last, and Any are essentially equivalent, since query set and join data is not sorted.
  • Sum – Performs numeric summation of the column by group. This should be used with a transformation rule that returns a numeric value.
  • Mean – Takes the arithmetic mean of the group. This should be used with a transformation rule that returns a numeric value.
  • Min/Max – Take the minimum or maximum value from the group. This should be used with a transformation rule that returns a numeric value.
  • Count – Gives the number of rows in each group.
  • Map – Gives a list containing the full row data from the bucket. Use the various List functions to filter and condense the row data down to a single result.

Schedule Steps

Send Report

Sends an email with a report file download attached.

History

Returns a History Record with RecordOf set to the name of the report, and Argument set to the Recipient.

Concurrency

The send report step may run simultaneously with any step including itself, provided another step hasn't exclusively locked the Panel Service.

Settings

  • Report: Required, name of the report to send. Must be pre-defined in the Report settings.
  • File Type: Required, type of report file to download.
  • Recipient: Required, email address to send the report to.
  • Subject: customize subject header of email.
  • Message: customize email message.
  • Send as HTML: Wether the email message body should marked as HTML formatted.

Download Report

Downloads a report file to the specified directory.

History

Returns a History Record with RecordOf set to the name of the report, and Argument set to the Directory.

Concurrency

The download report step may run simultaneously with any step including itself, provided another step hasn't exclusively locked the Panel Service.

Settings

  • Report: Required, name of the report to send. Must be pre-defined in the Report settings.
  • Directory: Required, file system directory to download the report to. Directory is relative to the context of the Panel Service making the request.
  • File Type: Required, type of report file to download.

Workflow Steps

Send Email Workflow

Sends an email with a report file download attached.

Settings

  • Report: Required, name of the report to send. Must be pre-defined in the Report settings.
  • File Type: Required, type of report file to download.
  • Recipient: Required, email address to send the report to.
  • Subject: customize subject header of email.
  • Message: customize email message.
  • Send as HTML: Wether the email message body should marked as HTML formatted.

Download Report Workflow

Downloads a report file to the specified directory.

Settings

  • Report: Required, name of the report to send. Must be pre-defined in the Report settings.
  • Directory: Required, file system directory to download the report to. Directory is relative to the context of the Panel Service making the request.
  • File Type: Required, type of report file to download.

Performance

Of all components of the Panel platform, the reporting module is most sensitive to hardware performance. This occurs for several reasons, but essentially the inherent flexibility of the reporting engine makes it impractical to create covering indices for all reports, and it is possible to generate very large reports with lots of processing required.

The following performance considerations are important if Panel platform is tracking more than 20,000 identities per silo. The performance of generating a report depends on the following factors:

  • Does the server have enough RAM to fit the entire object data collection in memory? If server sizing recommendations have been followed, the server should be able to fit the entire working set in memory.
  • Are the report data query fields covered by indices? The report will generate faster if the report data is filtered on an indexed column such as Identity Silo.
  • Whether the Filters use a simple comparison (e.g. "=" or ">") or a more complex rule (e.g "in" or "Regex")

If your organization has a regularly generated report, and enough identities that performance is an issue you can contact SoftwareIDM to obtain assistance creating additional custom indices to support your requirements.

Copyright © SoftwareIDM

Table of Contents