Информационная панель (dashboard) в СЭД Directum

Создание информационной панели для мониторинга ключевых параметров СЭД на основе системы отчетов Microsoft SQL Server Reporting Services

На сегодняшний день трендом в области визуализации цифровых данных являются так называемые информационные панели (dashboard), позволяющие отслеживать одновременно ряд ключевых показателей работы объекта. Большинство подобных панелей представляют собой верхний срез систем аналитики, характеризующий результаты работы наблюдаемого объекта, дающий необходимую информацию для принятия решений. Удобство применения информационных панелей во многом определяется их свойствами:

• Визуальное представление данных;
• Представление ключевых показателей;
• Удобство восприятия.

Применимость таких решений практически ко всем системам, позволяющим в автоматизированном режиме группировать и анализировать данные, а также удобство использования в процессе выработки решений предопределяет их растущую популярность. Не составляют исключение и системы электронного документооборота (далее – СЭД), которые также могут быть объектом мониторинга. К СЭД, помимо прочего, применимы традиционные показатели:

1. Производительность серверной части СЭД;
2. Производительность клиентской части СЭД;
3. Показатели эффективности использования СЭД пользователями.

Ввиду того, что сервером базы данных для СЭД Directum является Microsoft SQL Server, в качестве системы отчетов для создания информационной панели удобно использовать Microsoft SQL Server Reporting Services (далее - SSRS). Для этого потребуются следующие компоненты:

1. Microsoft SQL Server с установленным компонентом Reporting Services (https://www.microsoft.com/ru-ru/evalcenter/evaluate-sql-server-2014);
2. Построитель отчетов Microsoft Reporting Builder (https://www.microsoft.com/ru-RU/download/details.aspx?id=42301).

Настройка SSRS и подключение построителя отчетов не имеет нюансов и выполняется в соответствии с инструкцией компании-производителя программного обеспечения в библиотеке MSDN: https://msdn.microsoft.com/ru-ru/library/bb630430(v=sql.120).aspx

 

Для подготовки информационной панели сервера СЭД может быть предложен набор следующих показателей:

• Длительность сессий пользователей;
• Состояние индексов таблиц базы данных СЭД;
• Состояние заданий резервного копирования базы данных СЭД;
• Оценка блокировок в базе данных СЭД;
• Средняя длительность операций чтения и записи для баз данных сервера.

Для сбора необходимых сведений используем запросы T-SQL к серверу баз данных СЭД:

Длительность сессий пользователей

WITH t1 (
	Login_Name
	,MessageBox
	)
AS (
	SELECT Login_Name
		,CASE 
			WHEN DATEDIFF(HOUR, MIN(Login_Time), GETDATE()) <= 1
				THEN 'INFO (less 1 hour)'
			WHEN DATEDIFF(HOUR, MIN(Login_Time), GETDATE()) = 2
				THEN 'WARNING (between 1 and 2 hours)'
			WHEN DATEDIFF(HOUR, MIN(Login_Time), GETDATE()) >= 3
				THEN 'ALERT (more 3 hours)'
			END AS MessageBox
	FROM sys.dm_exec_sessions
	WHERE database_id > 0
		AND DB_NAME(database_id) IN ('DIRECTUM')
		AND Login_name NOT IN (
			'sa'
			,'ISBuilderSystem'
			,'Conductor'
			)
	GROUP BY Login_Name
	)
SELECT MessageBox
	,COUNT(MessageBox) AS CountMB
FROM t1
GROUP BY MessageBox

Состояние индексов таблиц базы данных СЭД

SELECT TOP 15 OBJECT_NAME(s.[object_id]) AS TableName
	,i.NAME AS IndexName
	,ROUND(avg_fragmentation_in_percent, 2) AS FragmentationPercent
	,SUM(pr.Rows) AS Row_count
FROM sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, NULL) s
INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]
	AND s.index_id = i.index_id
LEFT JOIN sys.partition_schemes AS p ON i.data_space_id = p.data_space_id
LEFT JOIN sys.objects o ON s.[object_id] = o.[object_id]
LEFT JOIN sys.schemas AS sh ON sh.[schema_id] = o.[schema_id]
JOIN sys.partitions pr ON i.object_id = pr.object_id
	AND i.index_id = pr.index_id
WHERE s.database_id = DB_ID()
	AND i.NAME IS NOT NULL
	AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
	AND page_count > 100
	AND avg_fragmentation_in_percent > 3
	AND DB_NAME() = 'DIRECTUM'
GROUP BY OBJECT_NAME(s.[object_id])
	,i.NAME
	,avg_fragmentation_in_percent
ORDER BY avg_fragmentation_in_percent DESC

Состояние выполнения заданий резервного копирования на сервере баз данных СЭД

SELECT d.NAME AS DBName
	,MAX(b.backup_finish_date) AS LastBackupCompleted
	,DATEDIFF(DAY, MAX(b.backup_finish_date), GETDATE()) AS DurationBackup
	,CASE 
		WHEN DATEDIFF(DAY, MAX(b.backup_finish_date), GETDATE()) <= 1
			THEN 'OK'
		WHEN DATEDIFF(DAY, MAX(b.backup_finish_date), GETDATE()) = 2
			THEN 'WARNING'
		WHEN DATEDIFF(DAY, MAX(b.backup_finish_date), GETDATE()) >= 3
			THEN 'ALERT'
		END AS MessageBox
FROM sys.databases d
LEFT OUTER JOIN msdb..backupset b ON b.database_name = d.NAME
	AND b.[type] = 'D'
WHERE d.NAME NOT IN (
		'master'
		,'model'
		,'msdb'
		,'tempdb'
		,'tempbd'
		)
GROUP BY d.NAME
ORDER BY d.NAME

Статистика ожиданий в базе данных СЭД

WITH [Waits]
AS (
	SELECT [wait_type]
		,[wait_time_ms] / 1000.0 AS [WaitS]
		,([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS]
		,[signal_wait_time_ms] / 1000.0 AS [SignalS]
		,[waiting_tasks_count] AS [WaitCount]
		,100.0 * [wait_time_ms] / SUM([wait_time_ms]) OVER () AS [Percentage]
		,ROW_NUMBER() OVER (
			ORDER BY [wait_time_ms] DESC
			) AS [RowNum]
	FROM sys.dm_os_wait_stats
	WHERE [wait_type] NOT IN (
			N'BROKER_EVENTHANDLER'
			,N'BROKER_RECEIVE_WAITFOR'
			,N'BROKER_TASK_STOP'
			,N'BROKER_TO_FLUSH'
			,N'BROKER_TRANSMITTER'
			,N'CHECKPOINT_QUEUE'
			,N'CHKPT'
			,N'CLR_AUTO_EVENT'
			,N'CLR_MANUAL_EVENT'
			,N'CLR_SEMAPHORE'
			,N'DBMIRROR_DBM_EVENT'
			,N'DBMIRROR_EVENTS_QUEUE'
			,N'DBMIRROR_WORKER_QUEUE'
			,N'DBMIRRORING_CMD'
			,N'DIRTY_PAGE_POLL'
			,N'DISPATCHER_QUEUE_SEMAPHORE'
			,N'EXECSYNC'
			,N'FSAGENT'
			,N'FT_IFTS_SCHEDULER_IDLE_WAIT'
			,N'FT_IFTSHC_MUTEX'
			,N'HADR_CLUSAPI_CALL'
			,N'HADR_FILESTREAM_IOMGR_IOCOMPLETION'
			,N'HADR_LOGCAPTURE_WAIT'
			,N'HADR_NOTIFICATION_DEQUEUE'
			,N'HADR_TIMER_TASK'
			,N'HADR_WORK_QUEUE'
			,N'KSOURCE_WAKEUP'
			,N'LAZYWRITER_SLEEP'
			,N'LOGMGR_QUEUE'
			,N'ONDEMAND_TASK_QUEUE'
			,N'PWAIT_ALL_COMPONENTS_INITIALIZED'
			,N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP'
			,N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP'
			,N'REQUEST_FOR_DEADLOCK_SEARCH'
			,N'RESOURCE_QUEUE'
			,N'SERVER_IDLE_CHECK'
			,N'SLEEP_BPOOL_FLUSH'
			,N'SLEEP_DBSTARTUP'
			,N'SLEEP_DCOMSTARTUP'
			,N'SLEEP_MASTERDBREADY'
			,N'SLEEP_MASTERMDREADY'
			,N'SLEEP_MASTERUPGRADED'
			,N'SLEEP_MSDBSTARTUP'
			,N'SLEEP_SYSTEMTASK'
			,N'SLEEP_TASK'
			,N'SLEEP_TEMPDBSTARTUP'
			,N'SNI_HTTP_ACCEPT'
			,N'SP_SERVER_DIAGNOSTICS_SLEEP'
			,N'SQLTRACE_BUFFER_FLUSH'
			,N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'
			,N'SQLTRACE_WAIT_ENTRIES'
			,N'WAIT_FOR_RESULTS'
			,N'WAITFOR'
			,N'WAITFOR_TASKSHUTDOWN'
			,N'WAIT_XTP_HOST_WAIT'
			,N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG'
			,N'WAIT_XTP_CKPT_CLOSE'
			,N'XE_DISPATCHER_JOIN'
			,N'XE_DISPATCHER_WAIT'
			,N'XE_TIMER_EVENT'
			,N'BACKUPBUFFER'
			,N'BACKUPIO'
			)
	)
SELECT TOP 5 [W1].[wait_type] AS [WaitType]
	,CAST([W1].[WaitS] AS DECIMAL(16, 2)) AS [Wait_S]
	,CAST([W1].[ResourceS] AS DECIMAL(16, 2)) AS [Resource_S]
	,CAST([W1].[SignalS] AS DECIMAL(16, 2)) AS [Signal_S]
	,[W1].[WaitCount] AS [WaitCount]
	,CAST([W1].[Percentage] AS DECIMAL(5, 2)) AS [Percentage]
	,CAST(([W1].[WaitS] / [W1].[WaitCount]) AS DECIMAL(16, 4)) AS [AvgWait_S]
	,CAST(([W1].[ResourceS] / [W1].[WaitCount]) AS DECIMAL(16, 4)) AS [AvgRes_S]
	,CAST(([W1].[SignalS] / [W1].[WaitCount]) AS DECIMAL(16, 4)) AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2] ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum]
	,[W1].[wait_type]
	,[W1].[WaitS]
	,[W1].[ResourceS]
	,[W1].[SignalS]
	,[W1].[WaitCount]
	,[W1].[Percentage]
HAVING SUM([W2].[Percentage]) - [W1].[Percentage] < 95

 

 

 

 

 

Средняя длительность операций чтения и записи для баз данных сервера

SELECT cast(db_name(a.database_id) AS VARCHAR) AS Database_Name
	,b.physical_name
	,a.size_on_disk_bytes
	,a.io_stall_read_ms / a.num_of_reads 'Средняя задержка одной операции чтения'
	,a.io_stall_write_ms / a.num_of_writes 'Средняя задержка одной операции записи'
FROM sys.dm_io_virtual_file_stats(NULL, NULL) a
INNER JOIN sys.master_files b ON a.database_id = b.database_id
	AND a.file_id = b.file_id
WHERE num_of_writes > 0
	AND num_of_reads > 0
ORDER BY Database_Name
	,a.io_stall DESC

 

 

 

 

 

 

 

Таким образом, можем получить следующий вариант информационной панели для серверной части СЭД:

 

 

 

 

 

 

 

 

 

 

 

N.B.: Отмечаем, что представленные запросы во многом являются типовыми и не претендуя на уникальность являются в данном контексте лишь демонстрацией возможностей инструментария SSRS применительно к СЭД Directum.

 

Для вывода показателей клиентского рабочего места СЭД может быть использован следующий набор данных:

• Количество объектов СЭД в папках;
• Соотношение количества просмотренных и непрочитанных заданий;
• Соотношение количества выполненных заданий и заданий в работе;
• Выполнение заданий с заданными сроками;
• Автоматическая выдача рекомендаций по настройке свойств папок СЭД.

Количество объектов в папках

SELECT SBFolder.NAME AS FolderName
	,COUNT(SBFolder.NAME) AS CountObject
	,SBFolder.ShowUnread
FROM SBLinks
INNER JOIN SBFolder ON SBLinks.SourceID = SBFolder.XRecID
INNER JOIN MBAnalit ON SBFolder.Author = MBAnalit.Analit
WHERE MBAnalit.Dop = @User
	AND SBFolder.FolderType IN (
		'P'
		,'I'
		,'O'
		,'F'
		)
GROUP BY SBFolder.NAME
	,SBFolder.ShowUnread

Просмотры заданий

SELECT CASE 
		WHEN SBTaskJob.Readed = 'Y'
			THEN 'Readed'
		WHEN SBTaskJob.Readed = 'N'
			THEN 'Not readed'
		END AS STATE
	,COUNT(Readed) AS IsReaded
FROM SBTaskJob
INNER JOIN MBAnalit ON SBTaskJob.Executor = MBAnalit.Analit
WHERE MBAnalit.Dop = @User
GROUP BY Readed

Задачи со сроками исполнения

WITH t1 (CountFailedNow)
AS (
	SELECT COUNT(STATE) AS CountFailedNow
	FROM SBTaskJob
	INNER JOIN MBAnalit ON SBTaskJob.Executor = MBAnalit.Analit
	WHERE MBAnalit.Dop = @User
		AND Kind != 'N'
		AND FinalDate <= GETDATE()
		AND STATE = 'W'
	)
	,t2 (CountTask)
AS (
	SELECT COUNT(STATE) AS CountTask
	FROM SBTaskJob
	INNER JOIN MBAnalit ON SBTaskJob.Executor = MBAnalit.Analit
	WHERE MBAnalit.Dop = @User
		AND Kind != 'N'
		AND FinalDate IS NOT NULL
		AND STATE != 'W'
		AND FinalDate <= EndDate
	)
	,t3 (CompleteFailedFullTime)
AS (
	SELECT COUNT(STATE) AS CompleteFailedFullTime
	FROM SBTaskJob
	INNER JOIN MBAnalit ON SBTaskJob.Executor = MBAnalit.Analit
	WHERE MBAnalit.Dop = @User
		AND Kind != 'N'
		AND FinalDate > EndDate
		AND STATE != 'W'
	)
SELECT 'Execute' AS CName
	,CountTask
	,CompleteFailedFullTime
	,CountFailedNow
FROM t1
	,t2
	,t3

Исполнение заданий

SELECT CASE 
		WHEN SBTaskJob.STATE = 'W'
			THEN 'Work'
		WHEN SBTaskJob.STATE = 'D'
			THEN 'Complete'
		WHEN SBTaskJob.STATE = 'B'
			THEN 'Stop'
		END AS STATE
	,COUNT(STATE) AS CountState
FROM SBTaskJob
INNER JOIN MBAnalit ON SBTaskJob.Executor = MBAnalit.Analit
WHERE MBAnalit.Dop = @User
	AND Kind != 'N'
GROUP BY STATE

При построении элементов отчета, применимых к отдельному пользователю, нами был использован параметр User, имеющий динамическое значение по умолчанию. Данный параметр задает имя пользователя, для которого строится отчет и его содержанием является значение логина пользователя, который обращается к серверу SSRS (User!UserID). Это же значение, в нашем случае, является и логином пользователя в СЭД. Наиболее удобно добавлять ссылку на подобный отчет в качестве обложки папки в СЭД:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Пользователь, запустивший отчет, видит только данные, отфильтрованные для своей учетной записи.

Для создания элемента отчета с автоматическим анализом объектов СЭД нами были использованы свойства этих элементов, позволяющие задавать их значение на основе заданных выражений:

Выражение ячейки Tablix для генерирования рекомендации по настройке свойств

IIf(Fields!ShowUnread.Value="Y", "Отображение этой папки в списке замедляет работы системы. Для ускорения работы СЭД DIRECTUM рекомендуем отключить вывод количества непрочитанных сообщений", "OK")

Выражение ячейки Tablix для генерирования рекомендации по настройке содержания

IIf(Fields!CountObject.Value>100, "Открытие этой папки значительно снижает производительность системы. Для ускорения работы СЭД DIRECTUM необходимо уменьшить количество ссылок до максимально допустимого (до 100 ссылок)", IIf(Fields!CountObject.Value>80, "Открытие этой папки несколько замедляет работу системы. Для ускорения работы СЭД DIRECTUM необходимо уменьшить количество ссылок до рекомендуемого (до 80 ссылок)", "OK"))

Наблюдение за показателями производительности системы будет полезно технической поддержке и администраторам СЭД, а эффективность использования СЭД может оказаться полезной как при анализе и оптимизации бизнес-процессов, так и в случае оценки качества работы сотрудников:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Таким образом, количество и качество выводимых показателей, а также компоновка данных и как следствие – эффективность созданной информационной панели ограничивается возможностями сервера базы данных СЭД и потребностями пользователей.

 

Подробную информацию о перечисленных компонентах SSRS можно найти в библиотеке MSDN:

• Об службах MS SSRS: https://msdn.microsoft.com/ru-ru/library/ms159106(v=sql.120).aspx
• О работе MS Reporting Builder: https://msdn.microsoft.com/en-us/library/dd239338.aspx