Skip to content

Sidra DW Reports

Sidra DW provides several reports in Power BI to control the data intake performance. Information related to the pipelines and activities executions can be analysed.

The reports allow us to achieve an easier interpretation of the information collected and consolidated from different data sources like the core and log databases.

Power BI Reports

A description of each report available in the Power BI Service and its navigation are described below:

Data Intake Report:

DW Data Intake Report

This report displays, for the selected time period in the slicer, different graphs and indicator cards regarding Data Intake volumes:

  • Total volume of data stored in MB
  • Percentage of validation errors
  • Number of active Data Storage Units, Providers, Entities and applications
  • Total ingested Assets
  • Total number of rows on the Assets

The last date when all the values in the report were updated is also displayed.

Pipeline Executions Report:

DW Pipeline executions report

This report shows all information related with the pipelines executions and their performance during the selected time period:

  • Total amount of pipeline executions
  • Percentage of failed executions
  • Total duration of the pipelines (seconds)
  • Total duration of the activities (seconds)

In addition to these indicators, there is a pipeline executions table, which provides information about the total number of executions, total number of executions failed and duration in seconds for each pipeline.

There is also a line chart visualization depicting the evolution of the pipeline executions and the total pipeline duration compared to the average duration during the period in seconds.

Selecting one pipeline from the list and clicking on the 'See Pipeline Executions Detail' button, a drill through is possible in any specific pipeline, in order to see more detailed information about the pipeline executions.

Detailed Pipeline Executions Report

This Detailed Pipeline Executions view gives information about the number of executions of the pipeline by date and the duration of the pipeline executions compared to the activities duration by date.

This dashboard contains also a Pipeline Run List with each execution of the pipeline, date and time execution, duration and any error message in case of failure.

DW Pipeline executions detail report

Selecting one of the pipeline run executions from the list and clicking on the 'See Run Detail' button another report is opened with deeper information for that selected execution.

Detailed information about a particular pipeline execution

This report shows all the activities and Assets related with a specific execution.

DW Pipeline execution detail report

Providers report

This report presents information regarding the Providers. For the selected period of time, the following information is displayed:

  • Total stored volume in MB
  • Percentage of validation errors
  • Number of active Entities
  • Total number of Assets
  • Total number of rows on the Assets

On the left-hand side the number of Entities by Provider is displayed. In the middle there are three charts representing the number of assets, the stored volume and the total rows compared to the validation errors by date. The table on the right presents the volume and percentage of errors for each Entity.

DW Providers Report

More specific aspects of a particular Entity can be obtained selecting one of the Entities on the list.

The Entity detail report conveys the information for the selected Entity. Information about the Assets and Attributes related with the Entity can be checked.

DW Entity Detail Report

Data Storage Units Report

This report shows in a map the location of the Data Storage Units.

DW Data Storage Units Report

Stored Volume Analysis Report

This last report consists on a decomposition tree. This tree allows to analyse the stored volume of data by different hierarchical categories.

In the image below the stored volume is broken down by region, Data Storage Unit, Provider, Entity and Asset.

DW Stored Volume Analysis Report

DW Data Model

All Power BI reports, and possible new ones if wanted or needed, are created based on the data model presented below.

All dimensions and fact tables and their relationships are shown.

DW Operational Data Model

To see it a little bit more clearly, the following pictures show the relations between each of the fact tables and the different dimensions.

A simple star schema was created between the fact tables and the dimension tables.

Pipeline Executions Schema

DW Operational Data Model Pipeline Execution

For pipeline executions, we have in this model the relevant tables: FactPipelineExecution and DimError. The first one contains facts data about the executions of the pipelines, and the second one is a dimensions table with the possible errors in the system.

Both tables are loaded by a Load process from the core Logs table, more specifically, the tables Util.Logs and Util.DataFactoryPipelineRun.

You can find more information about these source tables to generate this Power BI model here>

  • Util.Log table is described here.
  • DataFactoryPipelineRun and DataFactoryActivityRun are described here.

Activity Executions Schema

DW Operational Data Model Activity

Assets Schema

DW Operational Data Model Assets

Pipeline Assets Schema

DW Operational Data Model Pipeline Assets

Dimensions and Measures

In this section all the attributes available in the different dimensions and the measures that could be used to create reports in the Power BI service are described.

  • Dimensions:

Brief description of all the dimensions and attributes available in Power BI:

Dimension Attribute Description
Activity Activity Activity name
Activity Status Activity Status Activity status
Activity Status Activity Status Value Amount of activities in each different status.
App Application Application name.
App Description Brief description of the application.
App Owner Owner of the application.
App Resource Group Resource Group of the application.
App Suscription Subscription of the application.
Asset Asset Asset name.
Asset Asset Date Date of ingestion of the Asset.
Asset Status Asset Status Asset status.
Attribute Attribute Name Attribute name.
Attribute IsMetadata Whether the Attribute is metadata or not.
Data Factory Data Factory Data Factory name.
Data Factory Resource Group Resource group name.
Data Storage Unit Data Storage Unit Data storage unit name.
Data Storage Unit Latitude Data storage unit latitude.
Data Storage Unit Location Data storage unit location.
Data Storage Unit Longitude Data storage unit longitude.
Data Storage Unit Region Data storage unit location.
Date Date Date.
Date Month Name Month name.
Date Month Short Name Month short name.
Date Quarter Quarter.
Date Year Year.
Date Year Month Year and month.
Date Year Month Short Name Year and month short name.
Date Year Quarter Year and quarter.
Entity Entity Name of the Entity.
Entity Table Name Table name of the Entity.
Errors Failure Type Type of failure.
Errors Message Description of the error message.
Errors Target Error target.
Last Updated Log Last Data Updated Last date data was updated in the DW.
Pipeline Last Deployed Last date the pipeline was deployed.
Pipeline Pipeline Pipeline name.
Pipeline Selected Pipeline Returns the actual pipeline selected in the report (use for drillthrough purposes).
Pipeline RunId RunId Unique identifier of each pipeline execution.
Pipeline RunId Selected RunId Returns the current selected pipeline execution unique identifier (use for drillthrough purpuses).
Pipeline Status IsFailed Whether the pipeline has failed or not.
Pipeline Status Pipeline Status Pipeline status.
Pipeline Status Pipeline Status Label Pipeline status label.
Pipeline Status Pipeline Status Value Pipeline status value.
Provider Provider Provider name.
Provider Selected Provider Returns the actual selected Provider.
Time Hour Hour.
Time Time Hour, minutes and seconds.
Time Intelligence Time Calc Time intelligence calcultations.
  • Measures:

Measures are grouped in different groups inside Power BI. For each group a table with the name and description of each measure is presented.

Activity Execution measures group:

Measure Description
Activity Duration (Seconds) Activity duration in seconds.
Activity Executions Number of executions of the activity.
Activity Last Execution Last date the activity has been executed.
Activity Last Execution Duration Duration of the activity in its last execution.

Assets measures group:

Measure Description
%Validation Errors Percentage of validation errors with respect to the total rows.
Active Apps Number of applications with Assets within the selected period.
Active Data Storage Units Number of active data storage units with Assets within the selected period.
Active Entities Number of active Entities with Assets within the selected period.
Active Providers Number of active Providers with Assets within the selected period.
Last Date Last date when the Asset has been ingested.
Stored Volume (MB) Stored volume of the Asset in MB.
Total Assets Total number of Assets.
Total Providers Total number of Providers.
Total Rows Total number of rows of the Asset.
Validation Errors Total number of validation error for the Asset.

Attribute Popularity measures group:

Measure Description
Num Times Number of times the Attribute has been populated.
Total Attributes Total number of Attributes.

Pipeline Assets measures group:

Measure Description
Num Assets Number of Assets related with the pipeline.

Pipeline Execution measures group:

Measure Description
Average Pipeline Duration Average pipeline duration.
Last PipelineRunId Unique identifier of the last execution of the pipeline.
Pipeline Duration (Seconds) Pipeline duration in seconds.
Pipeline Executions Number of pipeline executions.
Pipeline Last Execution Last execution date of the pipeline.
Pipeline Last Execution Duration Duration of the last execution of the pipeline.
Total Pipelines Total number of pipelines.

Datawarehouse - DW Database

In order to create the data model presented before, information regarding the data ingestion process is collected from different sources and consolidated in a data warehouse. In a first stage data mainly from Core and Log databases are extracted and loaded in several tables in a schema called Staging inside a database called DW. The table below contains the list of external (when possible) tables created and their data sources.

  • (External) Staging tables and data sources:
Table Name Description Source database Source table
[Staging].[App] Applications information. Core Apps.App
[Staging].[Asset] Assets information. Core DataIngestion.Asset
[Staging].[AssetStatus] All possible states of an Asset. Core DataIngestion.AssetStatus
[Staging].[Attribute] Attributes information. Core DataIngestion.Attribute
[Staging].[AttributePopularity] Attributes popularity information. Core DataIngestion.AttributePopularity
[Staging].[DataFactoryActivityRun] Activities execution information. Log Util.DataFactoryActivityRun
[Staging].[DataFactoryPipelineRun] Pipelines executions information. Log Util.DataFactoryPipelineRun
[Staging].[DSU] Data Storage Units information. Core DataIngestion.Datalake
[Staging].[Entity] Entities information. Core DataIngestion.Entity
[Staging].[Pipeline] Pipelines information. Core DataIngestion.Pipeline
[Staging].[PipelineExecution] Relation between Assets and Pipelines information. Core DataIngestion.PipelineExecution
[Staging].[Provider] Providers information. Core DataIngestion.Provider

Based on these staging tables a process of transformation and load is executed. This process is accomplished by executing the following stored procedures. These store procedures load all the dimension and fact tables in the DW schema. The 'OrchestratorLoadDW' stored procedure is the one in charge of executing the rest in the correct order:

[DW].[Load_Date]

[DW].[Load_Hour]

[DW].[MergeDimActivity]

[DW].[MergeDimActivityStatus]

[DW].[MergeDimApp]

[DW].[MergeDimAsset]

[DW].[MergeDimAssetStatus]

[DW].[MergeDimAttribute]

[DW].[MergeDimDataFactory]

[DW].[MergeDimDataStorageUnit]

[DW].[MergeDimEntity]

[DW].[MergeDimError]

[DW].[MergeDimLocation]

[DW].[MergeDimPipeline]

[DW].[MergeDimPipelineStatus]

[DW].[MergeDimProvider]

[DW].[MergeFactActivityExecution]

[DW].[MergeFactAssets]

[DW].[MergeFactAttributesPopularity]

[DW].[MergeFactPipelineAssets]

[DW].[MergeFactPipelineExecution]

[DW].[OrchestrateLoadDW]

There is another schema called 'Log'. Tables and stored procedures belonging to this schema provide a way to register and control the load process into the DW database. The schema contains these two stored procedures:

[Log].[LoadProcessLog] [Log].[LoadProcessTasksLog]

Next a metadata description of all the tables, dimension tables, fact tables and control tables, created and loaded in the DW and Log schemas, is presented:

DW and Log schema tables

Dimension tables and fact tables in DW database:

  • DimActivity:
Schema Table name Column Name Data_type Length Precision
DW DimActivity IdActivity int 4 10
DW DimActivity Activity varchar 75 0
  • DimActivityStatus:
Schema Table name Column Name Data_type Length Precision
DW DimActivityStatus IdActivityStatus smallint 2 5
DW DimActivityStatus ActivityStatus varchar 30 0
  • DimApp:
Schema Table name Column Name Data_type Length Precision
DW DimApp IdApp smallint 2 5
DW DimApp AppName varchar 80 0
DW DimApp Description varchar 1024 0
DW DimApp Owner varchar 150 0
DW DimApp SubscriptionName varchar 90 0
DW DimApp ResourceGroupName varchar 90 0
DW DimApp LastUpdated datetime2 6 19
DW DimApp LastDeployed datetime2 6 19
DW DimApp ManagementDatabase varchar 150 0
DW DimApp ManagementDatabaseServer varchar 150 0
DW DimApp IsDeleted bit 1 1
  • DimAsset:
Schema Table name Column Name Data_type Length Precision
DW DimAsset IdAsset int 4 10
DW DimAsset AssetName varchar 100 0
DW DimAsset AssetDate date 3 10
DW DimAsset LastUpdated datetime2 6 19
DW DimAsset IsDeleted bit 1 1

- DimAssetStatus:

Schema Table name Column Name Data_type Length Precision
DW DimAssetStatus IdAssetStatus smallint 2 5
DW DimAssetStatus AssetStatus varchar 50 0
  • DimAttribute:
Schema Table name Column Name Data_type Length Precision
DW DimAttribute IdAttribute int 4 10
DW DimAttribute IsDeleted bit 1 1
DW DimAttribute AttributeName varchar 150 0
DW DimAttribute IsMetadata bit 1 1
  • DimDataFactory:
Schema Table name Column Name Data_type Length Precision
DW DimDataFactory IdDataFactory smallint 2 5
DW DimDataFactory DataFactory varchar 50 0
DW DimDataFactory ResourceGroup varchar 50 0
  • DimDataStorageUnit:
Schema Table name Column Name Data_type Length Precision
DW DimDataStorageUnit IdDataStorageUnit smallint 2 5
DW DimDataStorageUnit DataStorageUnit varchar 50 0
DW DimDataStorageUnit ResourceGroupName varchar 50 0
DW DimDataStorageUnit ClusterName varchar 50 0
DW DimDataStorageUnit IdLocation smallint 2 5
DW DimDataStorageUnit IsDeleted bit 1 1
  • DimDate:
Schema Table name Column Name Data_type Length Precision
DW DimDate IdDate int 4 10
DW DimDate Date date 3 10
DW DimDate MonthOfYear tinyint 1 3
DW DimDate MonthOfYearName varchar 10 0
DW DimDate QuarterOfYear tinyint 1 3
DW DimDate QuarterName varchar 2 0
DW DimDate Year smallint 2 5
DW DimDate YearName varchar 4 0
DW DimDate YearMonth int 4 10
DW DimDate YearMonthName varchar 20 0
DW DimDate YearQuarter smallint 2 5
DW DimDate YearQuarterName varchar 10 0
DW DimDate Week tinyint 1 3
DW DimDate WeekName varchar 2 0
DW DimDate DayOfWeek tinyint 1 3
DW DimDate DayOfWeekName varchar 10 0
DW DimDate DayOfYear smallint 2 5
DW DimDate DayOfYearName varchar 3 0
DW DimDate DayOfMonth tinyint 1 3
DW DimDate DayOfMonthName varchar 2 0
DW DimDate CommencingWeekYearStartDateId int 4 10
DW DimDate CommencingWeekYearEndDateId int 4 10
DW DimDate IsWeekend bit 1 1
  • DimEntity:
Schema Table name Column Name Data_type Length Precision
DW DimEntity IdEntity int 4 10
DW DimEntity IdProvider int 4 10
DW DimEntity TableName varchar 128 0
DW DimEntity IsDeleted bit 1 1
DW DimEntity Entity varchar 128 0
  • DimError:
Schema Table name Column Name Data_type Length Precision
DW DimError IdError int 4 10
DW DimError ErrorCode varchar 50 0
DW DimError Message varchar 4000 0
DW DimError FailureType varchar 50 0
DW DimError Target varchar 50 0
  • DimHour:
Schema Table name Column Name Data_type Length Precision
DW DimHour IdHour int 4 10
DW DimHour Time datetime2 6 19
DW DimHour Hour tinyint 1 3
DW DimHour Minute tinyint 1 3
DW DimHour Second tinyint 1 3
DW DimHour HourName char 2 0
DW DimHour MinuteName char 2 0
DW DimHour SecondName char 2 0
DW DimHour HourMinute char 5 0
DW DimHour HourMinuteSecond char 8 0
  • DimLocation:
Schema Table name Column Name Data_type Length Precision
DW DimLocation IdLocation smallint 2 5
DW DimLocation Region nvarchar 100 0
DW DimLocation RegionId nvarchar 100 0
DW DimLocation Location nvarchar 100 0
DW DimLocation Longitude decimal 9 18
DW DimLocation Latitude decimal 9 18
  • DimPipeline:
Schema Table name Column Name Data_type Length Precision
DW DimPipeline IdPipeline int 4 10
DW DimPipeline Pipeline varchar 80 0
DW DimPipeline IsRemoved bit 1 1
DW DimPipeline LastUpdated datetime2 6 19
DW DimPipeline LastDeployed datetime2 6 19
  • DimPipelineStatus:
Schema Table name Column Name Data_type Length Precision
DW DimPipelineStatus IdPipelineStatus smallint 2 5
DW DimPipelineStatus PipelineStatus varchar 30 0
  • DimProvider:
Schema Table name Column Name Data_type Length Precision
DW DimProvider IdProvider smallint 2 5
DW DimProvider ProviderName varchar 30 0
DW DimProvider IsDeleted bit 1 1
DW DimProvider IdDataStorageUnit smallint 2 5
  • FactActivityExecution:
Schema Table name Column Name Data_type Length Precision
DW FactActivityExecution PipelineRunId uniqueidentifier 16 0
DW FactActivityExecution ActivityRunId uniqueidentifier 16 0
DW FactActivityExecution IdPipeline int 4 10
DW FactActivityExecution IdActivity int 4 10
DW FactActivityExecution IdActivityStatus smallint 2 5
DW FactActivityExecution IdDate int 4 10
DW FactActivityExecution IdHour int 4 10
DW FactActivityExecution IdApp smallint 2 5
DW FactActivityExecution ActivityDuration int 4 10
DW FactActivityExecution IsDeleted bit 1 1
DW FactActivityExecution IdError int 4 10
DW FactActivityExecution IdDataStorageUnit smallint 2 5
DW FactActivityExecution IdDataFactory smallint 2 5
DW FactActivityExecution IdLocation smallint 2 5
  • FactAssets:
Schema Table name Column Name Data_type Length Precision
DW FactAssets IdAsset bigint 8 19
DW FactAssets IdProvider smallint 2 5
DW FactAssets IdDataStorageUnit smallint 2 5
DW FactAssets IdEntity int 4 10
DW FactAssets IdDateAsset int 4 10
DW FactAssets IdAssetStatus smallint 2 5
DW FactAssets IdDate int 4 10
DW FactAssets IdHour int 4 10
DW FactAssets IdApp smallint 2 5
DW FactAssets IdLocation smallint 2 5
DW FactAssets LastUpdated datetime2 6 19
DW FactAssets Entities bigint 8 19
DW FactAssets ByteSize bigint 8 19
DW FactAssets ValidationErrors bigint 8 19
DW FactAssets IsDeleted bit 1 1
  • FactAttributePopularity:
Schema Table name Column Name Data_type Length Precision
DW FactAttributesPopularity IdQuery bigint 8 19
DW FactAttributesPopularity IdAttribute int 4 10
DW FactAttributesPopularity IdEntity int 4 10
DW FactAttributesPopularity IdProvider smallint 2 5
DW FactAttributesPopularity IdDate int 4 10
DW FactAttributesPopularity IdHour int 4 10
DW FactAttributesPopularity IdDataStorageUnit smallint 2 5
DW FactAttributesPopularity IdApp smallint 2 5
  • FactPipelineAssets:
Schema Table name Column Name Data_type Length Precision
DW FactPipelineAssets PipelineRunId uniqueidentifier 16 0
DW FactPipelineAssets IdAsset bigint 8 19
DW FactPipelineAssets IdProvider smallint 2 5
DW FactPipelineAssets IdDataStorageUnit smallint 2 5
DW FactPipelineAssets IdEntity int 4 10
DW FactPipelineAssets IdPipeline int 4 10
DW FactPipelineAssets IdPipelineStatus smallint 2 5
DW FactPipelineAssets IdDate int 4 10
DW FactPipelineAssets IdHour int 4 10
DW FactPipelineAssets IdApp smallint 2 5
DW FactPipelineAssets ExecutionTime datetime2 6 19
DW FactPipelineAssets IsDeleted bit 1 1
DW FactPipelineAssets IdDataFactory smallint 2 5
DW FactPipelineAssets IdLocation smallint 2 5
  • FactPipelineExecution:
Schema Table name Column Name Data_type Length Precision
DW FactPipelineExecution PipelineRunId uniqueidentifier 16 0
DW FactPipelineExecution IdPipeline int 4 10
DW FactPipelineExecution IdPipelineStatus smallint 2 5
DW FactPipelineExecution IdDate int 4 10
DW FactPipelineExecution IdHour int 4 10
DW FactPipelineExecution IdApp smallint 2 5
DW FactPipelineExecution PipelineDuration int 4 10
DW FactPipelineExecution IsDeleted bit 1 1
DW FactPipelineExecution IdError int 4 10
DW FactPipelineExecution IdDataStorageUnit smallint 2 5
DW FactPipelineExecution IdDataFactory smallint 2 5
DW FactPipelineExecution IdLocation smallint 2 5
  • LoadProcess:
Schema Table name Column Name Data_type Length Precision
Log LoadProcess LoadId int 4 10
Log LoadProcess DWLoadStartDate datetime2 8 27
Log LoadProcess DWLoadEndDate datetime2 8 27
Log LoadProcess LoadStatusId smallint 2 5
Log LoadProcess ErrorMessage nvarchar 400 0
  • LoadProcessTask:
Schema Table name Column Name Data_type Length Precision
Log LoadProcessTask LoadTaskId int 4 10
Log LoadProcessTask DestinationTableName nvarchar 400 0
Log LoadProcessTask EndDate datetime2 8 27
Log LoadProcessTask Error bit 1 1
Log LoadProcessTask ErrorMessage nvarchar 8000 0
Log LoadProcessTask LoadId int 4 10
Log LoadProcessTask RowsInserted int 4 10
Log LoadProcessTask RowsUpdated int 4 10
Log LoadProcessTask StartDate datetime2 8 27
  • LoadStatus:
Schema Table name Column Name Data_type Length Precision
Log LoadStatus LoadStatusId smallint 2 5
Log LoadStatus StatusName nvarchar 100 0

Finally for each dimension and fact table a view is created in the PowerBI schema so it is used as the datasources in the Power BI file.


Sidra Ideas Portal


Last update: 2022-07-14
Back to top