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:

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:

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.

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.

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.

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.

Data Storage Units Report

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

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

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

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

Assets Schema

Pipeline Assets Schema

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 'OrchestrateLoadDW' 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-11-14