Automation

While the Security Metrics Toolkit is designed for measurements to be manually updated in the Excel Template, the architecture of the toolkit allows for automating measurements as the program matures.

Data Format

Metric measurements must be in the same format as the Log sheet. Metric ID, Value, and Measurement Date are required fields, while Notes is optional. Below is an example of the format:

Metric ID	Value	Measurement Date	Notes
M-01	0.8	1/1/2025	Sample notes
M-01	0.85	2/1/2025	Sample notes
M-02	2300	1/15/2025	Sample notes

Metric Inventory Sheet

A metric must have both a record in the Metric Inventory as well as the Log. Ensure any Metric ID you are automating log measurements for has a corresponding record in the Metric Inventory Sheet of the Excel Template.

Common Data Connectors

Data can be ingested into PowerBI from a variety of datasources. For the full list, see the Microsoft Power Query Connector Documentation. Below are some of the most common:

• Web APIs

  • Connector: Web

• Databases

  • SQL Server

  • Oracle

  • MySQL

  • PostgreSQL

  • Google BigQuery

  • Snowflake

  • Amazon Athena

• Azure

  • Azure Blob Storage

  • Azure Table Storage

  • Azure Cosmos DB

  • Azure Datalake Storage Gen2

  • Azure Databricks

• OData

• ODBC

• Micrososft Fabric

• Flat files

  • CSV

  • JSON

  • XML

  • Parquet

Ingesting Data

It's recommended to build the above data format in the source system wherever possible, such as a database or data lake.

To ingest new data into the PowerBI template, click "Get Data" and select the data connector you wish the use.

Extract, Transform, & Load (ETL)

After connecting to your data source, click "transform" to use Power Query to format the data. Ensure the column names are properly named and you have the required columns.

1) If the table did not automatically set your headers, do so by clicking "Use First Row as Headers".

2) If the table did not automatically set the Measurement Date to type Date, do so by right-clicking on the column > "Change Type" > "Date".

3) If the table did not automatically set the Value to type Decimal, do so by right-clicking on the column > "Change Type" > "Decimal Number".

4) Set the name of the query.

We can now see we have 3 data queries and a parameter.

You can ingest measurement log data from multiple systems in different queries. In order to integrate the new queries into the data model, we need to append the data into the Log table.

5) Click on the "Log" query, and then click "Append Queries" > "Append Queries". Do not append the data as a new query.

Select the tables to append to the Log table, depending on how many queries you are pulling in.

6) Click on "Close and Apply"