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 the 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

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]