This document describes the datamodel used for the analysis of End-User Performance (EUP) data in Microsoft Power BI. The datamodel gives the possibility to analyze EUP data in different ways. The datamodel is a tabular datamodel with multiple related tables, calculated columns, and measures. The datamodel is customizable, so sources and columns can be added to adjust the datamodel as necessary.
Datamodel
The figure below visualizes the datamodel used in Power BI. Most tables are loaded from the SCOM data warehouse. Only the table ‘Targets’ is filled from within Power BI.
Tables
Two types of tables are used inside the datamodel. There are fact tables like ‘EUP History’, ‘Simulations Realtime’, and ‘Steps Realtime’. These tables contain information on simulation measures. The other tables, like ‘Date’, ‘Time’, and ‘Targets’, contain descriptive information about the facts. The summary below contains details about each table in the datamodel:
EUP History: Historical data is stored by SCOM in the data warehouse and aggregated per day and hour. This table reflects these aggregations. Availability and performance data from each simulation, and simulation step, can be found in this table. This tables is useful in many situations where there is a need to analyze historical data. Examples are SLA reports, trend analysis, diagnostics, and descriptive analysis.
Simulations Realtime: Raw data is stored in the data warehouse for 10 days and used to calculate the aggregated data. This table contains the raw data for each simulation. For historical analysis the time period is too short, but the data is useful when analyzing ad-hoc and current questions, as well as anomalies that occurred the last few days.
Steps Realtime: This table is the equivalent of the ‘Simulations Realtime’ table for simulation step data.
Date: This table contains properties of a date like the day of the week, the year, month, etc.
Time: This table contains time properties, with the granularity of a minute.
Targets: This table is used to define SLA agreements with suppliers and / or customers. The service level objects can be defined on a simulation level for availability and performance.
Relations
The descriptive tables (‘Date‘, ‘Time‘ and ‘Targets‘) are related to the fact tables (‘EUP History ‘, ‘Simulations Realtime‘ and ‘Steps Realtime‘). The relationships add the possibility to filter the data by date, time, and SLA.
Data
Below, the table columns are listed for each descriptive table with the contained information:
Date
Value
DateValue
Each date occurs once in this table
DayOfWeek
Day within the week
DayOfMonth
Day within the month
WeekOfYear
Week within the year
MonthNr
Number of the month
Quarter
Quarter of the year
Year
Year
Month
Month in text
Time
Value
TimeValue
Time on minute level, each possible hour/minute within a day
Hour
Hour of the TimeValue
Minute
Minute of the TimeValue
Targets
Value
Simulation
Name of the simulation
SlaAvailability
Percentage of availability which needs to be achieved
SlaPerformance
Number of seconds a simulation is allowed to take
For more information about the fact tables see this article.
Parameters
Go to ‘Home’ and choose ‘Edit Queries’
Within the list that appears click ‘Edit Parameters’
Edit the parameters in the presented dialog
Click ‘Ok’
The table below describes the available parameters which can be adjusted:
Parameters
Value
Realtime Hours
The last x-hours of raw data. This parameter takes effect on tables: ‘Simulations Realtime’ and ‘Steps Realtime’
History Months
The last x-months of aggregated data. This parameter takes effect on table: ‘EUP History’
History Granularity
Daily: History data grouped by day. Hourly: Historic data grouped by hour for each day.
Servername DWH
Full name of the SQL instance
Database DWH
Database name of the SCOM data warehouse. Default name is OperationsManagerDW
