Дополнительные таблицы. Календарь

Измерение даты и времени -- ключевая и обязательная сущность любого хранилища данных или аналитической системы. В данной статье будет приведен пример создания типовой таблицы календаря с гранулярностью до уровня дня. Для создания данной таблицы  нам понадобится таблица dbo.Numbers из предыдущей статьи. Полный код и результаты работы можно посмотреть здесь.

Создадим таблицу dbo.DimDate. В качестве кластерного ключа таблицы будем использовать суррогатный целочисленный ключ, представленный в формате YYYYMMDD. Ниже представлен скрипт создания таблицы календаря, а также скрипт создания дополнительной таблицы dbo.Numbers.

Запросы к Active Directory

SQL Server позволяет производить выборки данных из множества источников данных при помощи многочисленных доступных драйверов. В том числе MS SQL Server позволяет выполнять запросы к контроллеру домена. В моем случае к Active Directory. Это очень полезная функция, которая может быть использована для множества задач. Я в основном использую ее для динамической настройки прав доступа к отчетным системам. По хорошему, настройка прав должна производиться с использованием групп пользователей домена. В таком случае, при добавлении нового пользователя в группу домена, для него автоматически будут применены соответствующие права. Но не все системы позволяют настроить роли с учетом групп пользователей контроллера домена. Например, я столкнулся с такой проблемой в MS SQL Server SSAS Multidimensional. Чтобы решить данную проблему, необходимо получить список пользователей необходимой группы контроллера домена и автоматически добавить каждого пользователя в нужную нам роль.

Настройка Linked Server

Параметр	Значение
Provider	OLE DB provider for Microsoft Directory Services
Product Name	ADSI
Data Source	afsdatasource
Security	Be made using the login's current security context

Настройка связного сервера к MS AD не представляет из себя ничего трудного. Достаточно указать следующие свойства:

Запросы к AD

Запросы к AD могу писаться в двух форматах:

• SQL
• LDAP

SQL-формат:

SELECT * FROM OPENQUERY(AD,
'SELECT name, ADsPath, title  
FROM ''LDAP://DC=domain_name'' 
WHERE objectCategory = ''User''' 
) 

LDAP-формат:

SELECT * FROM OPENQUERY(AD,'<LDAP://DC=domain_name>;(&(objectcategory=user));name, ADsPath, title ')

Но существует ограничение на получение данных из AD -- 900 строк с данными. Данное ограничение можно обойти настроив соответствующим образом контроллер доменов.

Далее я приведу примеры запросов, которые я использую:

Дополнительные таблицы. Numbers

Для многих задач полезно иметь под рукой дополнительные таблицы. Одной из таких таблиц является таблица с последовательным набором чисел Numbers. Ее можно использовать как для создания тестовых таблиц с данными, так и для прикладных задач. Особенно полезны для работы со строками, для их итеративной обработки.

Существует множество способов создания такой таблицы:

• master..spt_values
• row_number() с любой таблицей, например sys.all_objects
• CTE
• Рекурсивный CTE
• Цикл

Загружаем OpenStreetMap в MS SQL Server

На днях подкинули задачку по геокодированию объектов, а именно:

• По строке адреса определить соответствующие ей географические координаты. Строка с адресом не нормализованна и потенциально может содержать ошибки.
• Обратная задача. По геокординатам точки предоставить список всех имеющихся в базе объектов в эпсилон окрестности от этой точки.

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

• Яндекс.Карты Геокодирование (25 000 запросов/сутки)
• Google Geocoding(2 500 запросов/сутки)
• Nominatim OpenStreetMap (политика использования 1 запрос/секунда)

Каждый сервис реализует собственные алгоритмы и предоставляет определенный уровень достоверности результатов. Однако, предполагается, что объектов, геокодирование которых необходимо производить, будет больше, чем количество допустимых запросов и обработку необходимо производить в пакетном режиме.

В качестве возможного варианта решения, я решил попробовать реализовать собственный поиск, используя для этого открытую базу данных Open Street Map (OSM). В качестве инструмента для первоначальной реализации я выбрал MS SQL Server 2012. В последнем поддерживаются пространственные типы данных, а также имеется возможность создания пространственного индекса.

SQL Server. Storage Engine. Страницы данных

В данной статье я хочу описать внутренние механизмы движка MS SQL Server по хранению данных и механику данного процесса. Это очень полезно для изучения особенностей работы SQL Server и для собственной проверки гипотез и утверждений о том, как работает SQL Server. Как говорится, доверяй, но проверяй.

Структура таблицы

Рассмотрим структуру таблицы в SQL Server.
Каждая таблица или индекс хранит свою информацию в партициях (partition, до 15.000 для sql server 2012). Каждая партиция может содержать один объект  B-дерева (b-tree, если быть точным в виде B+ дерева) или кучу(heap), отсюда, данные структуры называют общим именем hobt (heap or b-tree). Между партицией и hobt-структурой существует отношение один к одному. В зависимости от типа данных колонок таблицы или индекса, hobt может состоять из трех наборов страниц (allocation unit), каждая из которых образует цепочку страниц (iam chain):

• IN_ROW_DATA (практически все типы данных, кроме описанных ниже)
• ROW_OVERFLOW_DATA (типы переменной длины, вместе с которыми запись не помещается на страницу)
• LOB_DATA ( nvarchar(max), filestream, xml, varbinary)

Шпаргалка по типам данных

Обзор типов и их размерностей

DATATYPE	MIN	MAX	STORAGE	NOTES
Bit	0	1	1 to 8 bit columns in the same table requires a total of 1 byte, 9 to 16 bits = 2 bytes, etc...
Decimal	-10^38+1	10^38–1	Precision 1-9 = 5 bytes, precision 10-19 = 9 bytes, precision 20-28 = 13 bytes, precision 29-38 = 17 bytes	The Decimal and the Numeric data type is exactly the same. Precision is the total number of digits. Scale is the number of decimals. For both the minimum is 1 and the maximum is 38.
Numeric	same as Decimal	same as Decimal	same as Decimal
Money	От -922 337 203 685 477,5808 до	922 337 203 685 477,5807	8 bytes
Smallmoney	От -214 748,3648	214 748,3647	4 bytes
Float(n)	-1.79E + 308	1.79E + 308	4 bytes when precision is less than 25 8 bytes when precision is 25 through 53	Precision is specified from 1 to 53.
Real	-3.40E + 38	3.40E + 38	4 bytes	Precision is fixed to 7 digits.
Datetime	1753-01-01 00:00:00.000	9999-12-31 23:59:59.997	8 bytes	If you are running SQL Server 2008 or later and need milliseconds precision, use datetime2(3) instead to save 1 byte.
Smalldatetime	1900-01-01 00:00	2079-06-06 23:59
Date	0001-01-01	9999-12-31	3 bytes
Time(n)	00:00:00.0000000	23:59:59.9999999	Presicion 0-2 = 3 bytes Presicion 3-4 = 4 bytes  Presicion 5-7 = 5 bytes	Specifying the precision is possible. TIME(3) will have milliseconds precision. TIME(7) is the highest and the default precision. Casting values to a lower precision will round the value.
Datetime2(n)	0001-01-01 00:00:00.0000000	9999-12-31 23:59:59.9999999	Presicion 1-2 = 6 bytes precision 3-4 = 7 bytes precision 5-7 = 8 bytes	Combines the date datatype and the time datatype into one. The precision logic is the same as for the time datatype.
Datetimeoffset(n)	0001-01-01 00:00:00.0000000 -14:00	9999-12-31 23:59:59.9999999 +14:00	Presicion 1-2 = 8 bytes precision 3-4 = 9 bytes precision 5-7 = 10 bytes	Is a datetime2 datatype with the UTC offset appended

  

Собственные типы данных -- домены 

Allocation_Units

Datetime vs Datetime2

http://sqlhints.com/tag/datetime-vs-datetime2/

http://stackoverflow.com/questions/1334143/sql-server-datetime2-vs-datetime

Decimal vs Numeric

Money vs Decimal vs Float


Nvarchar(max) cautions
http://stackoverflow.com/questions/12639948/sql-nvarchar-and-varchar-limits

Nvarchar(max) vs Nvarchar(255)
http://stackoverflow.com/questions/148398/are-there-any-disadvantages-to-always-using-nvarcharmax
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4d9c6504-496e-45ba-a7a3-ed5bed731fcc/varcharmax-vs-varchar255?forum=sqlgetstarted


Implicit convertions

Работа с данными

http://msdn.microsoft.com/en-us/library/ff848728.aspx

Функции по просмотру метаданных типов и колонок

SELECT DATALENGTH(@variable)

SELECT

    SQL_VARIANT_PROPERTY(@variable, 'BaseType'),

    SQL_VARIANT_PROPERTY(@variable, 'TotalBytes'),

    SQL_VARIANT_PROPERTY(@variable, 'MaxLength')

;with cte
(
select * from t
)
select * from cte

SSIS. Полезные ссылки

Блоги:

1. Matt Manson
2. Jamie Thomson
3. Davide Mauri
4. Joost van Rossum
5. Константин Хомяков

Компоненты:

1. Samples http://sqlsrvintegrationsrv.codeplex.com/
2. Tools http://ssisctc.codeplex.com/

Best practices:

1. SQL CAT BI Guide 
2. Top 10 SQL Server Integration Services Best Practices
3. http://www.mssqltips.com/sqlservertip/1840/sql-server-integration-services-ssis-best-practices/
4. https://www.youtube.com/watch?v=QA1_o_ngFlY

Книги:

• Cleanse and Match Master Data by Using EIM

Видео:

• How to (not) torment your fellow SSIS developer?

Оптимизация

• http://nexxtjump.com/2012/06/12/ssis-performance-tuningmonitoring-data-collection/

Туториалы:

1. SSIS tutorial: Creating Simple ETL Package 
2. SSIS Tutorial with Videos from Kate and Emil 
3. PASS Summit 2012 SSIS sessions http://blogs.msdn.com/b/mattm/archive/2012/09/19/ssis-sessions-at-the-2012-pass-summit.aspx
4. Integration Services Video
5. Stairway to integration servcies
6. http://microsoft-ssis.blogspot.ru/