Совет
Если при попытке повторить за лектором и нажать "new database diagram" на меню "database diagram" возникает ошибка :
Невозможно выполнить в качестве участника базы данных, поскольку участник "dbo" не существует, этот тип участника не может проходить олицетворение, или отсутствует разрешение.
В этом случае в базе данных необходимо назначить владельцем пользователя sa (это было продемонстрировано ранее). Нажмите правую кнопку мыши на базе, выберите пункт Свойства, закладка Файл, в окне владельца введите sa. После этого диаграмма будет создана успешно
Постановка задачи
Нужно собрать первое измерение — Персонал — для аналитического куба, чтобы далее анализировать продажи.
Какие атрибуты нужны
По персоналу требуются:
- группа отдела;
- отдел;
- ФИО продавца;
- дата рождения;
- пол.
Исходные таблицы и их связи
Для измерения нужны четыре таблицы:
- Person (Персона);
- Employee (Сотрудник);
- Department (Департамент, отдел);
- Employee Department History (История принадлежности сотрудника к департаментам).
Связи:
- Person и Employee связаны по Business Entity ID (идентификатор бизнес-сущности) один к одному;
- Employee и Employee Department History связаны по Business Entity ID;
- Department и Employee Department History связаны по Department ID (идентификатор департамента).
Таблица Person
В Person хранится информация обо всех людях в системе: и о сотрудниках, и о покупателях. Здесь есть:
- Business Entity ID — первичный ключ;
- First Name (имя);
- Middle Name (второе имя; условно — отчество);
- Last Name (фамилия).
Таблица Employee
В Employee хранятся те же Business Entity ID, но уже как идентификаторы сотрудников компании. Дополнительно здесь есть:
- Birth Date (дата рождения);
- Gender (пол);
- другие кадровые поля: семейное положение, должность, дата найма, часы отпуска и болезни, организационный уровень.
Для измерения нужны только Birth Date и Gender. Остальные поля отсекаются.
Таблица Department
В Department есть:
- Department ID;
- Name (название отдела);
- Group Name (название группы отдела).
Именно Name и Group Name нужны для измерения.
Таблица Employee Department History
Эта таблица показывает историю перемещений сотрудника по департаментам. В ней есть:
- Business Entity ID;
- Department ID;
- Start Date (дата начала работы в департаменте);
- End Date (дата окончания работы в департаменте).
История нужна, потому что на момент продажи сотрудник мог числиться не в текущем департаменте, а в другом. Поэтому нужно определить, в каком департаменте он работал именно на момент покупки.
Если End Date пустой (NULL), сотрудник всё ещё работает в этом департаменте. Если он переходит в другой отдел, у старой записи появляется дата окончания, а у новой — дата начала, при этом End Date может быть не указан.
Зачем нужна денормализация
Четыре исходные таблицы нормализованы. После соединения и выбора нужных полей получается денормализованное отношение. Для схемы «звезда» это нормально. Денормализация ускоряет операции селекции, потому что не нужно каждый раз соединять четыре таблицы.
Сборка запроса в конструкторе
Можно написать SQL (Structured Query Language, язык структурированных запросов) вручную, но здесь используется конструктор запросов. В него добавляются таблицы, а затем отмечаются нужные поля. Конструктор сам формирует соединения и SQL-код.
Нужные поля:
- из Person: Business Entity ID, First Name, Middle Name, Last Name;
- из Employee: Birth Date, Gender;
- из Department: Department ID, Name, Group Name;
- из Employee Department History: Start Date, End Date.
Проблема NULL в End Date
В End Date для текущего департамента стоит NULL. С NULL сложно работать: нужны постоянные проверки. Поэтому NULL заменяется на заведомо большую дату, например на 3000 год. Тогда понятно: если указан 3000 год, сотрудник ещё работает в этом департаменте.
Проблема отсутствующего Middle Name
Middle Name может быть NULL. Если просто склеивать Last Name + Middle Name + First Name, то при NULL результат тоже станет NULL. Нужно проверять значение. Если Middle Name отсутствует, его нужно заменить пустой строкой или пробелом так, чтобы не появился двойной пробел. Если Middle Name есть, он включается в ФИО.
Формирование полного имени
Требуется одно поле — ФИО продавца. Оно собирается из Last Name, Middle Name и First Name. При отсутствии Middle Name лишний пробел не добавляется.
Итоговый запрос и порядок полей
В результате получается запрос со следующими полями:
- Business Entity ID;
- Full Name (полное имя);
- Gender;
- Birth Date;
- Department ID;
- Department Name;
- Group Name;
- Start Date;
- End Date.
Соединения:
Department.DepartmentID = EmployeeDepartmentHistory.DepartmentID;EmployeeDepartmentHistory.BusinessEntityID = Employee.BusinessEntityID;Employee.BusinessEntityID = Person.BusinessEntityID.
Сохранение измерения
Запрос сохраняется. На его основе создаётся измерение Персонал. Это первое кастомное измерение из трёх; измерение времени обычно уже существует.
Краткие итоги
Формирование аналитического измерения начинается не с базы данных, а с вопроса, какие атрибуты действительно нужны для анализа. Транзакционная система хранит данные в нормализованном виде, чтобы избежать противоречий и дублирования. Аналитическая модель, наоборот, может использовать денормализованные структуры: они быстрее читаются и удобнее для построения кубов. Поэтому сначала определяются требования к измерению, затем выбираются таблицы, поля и связи, а лишняя информация отсекается.
Ключевая сложность — время. Атрибуты сотрудника, например департамент, могли меняться. Если использовать только текущее значение, анализ продаж даст искажённую картину. История департаментов позволяет восстановить состояние на момент продажи. Это делает измерение корректным для исторического анализа, а не только для отчётов «на сегодня».
Отдельного внимания требуют пустые значения. NULL в дате окончания работы означает, что сотрудник всё ещё находится в департаменте. Такой NULL неудобен для запросов, поэтому его заменяют условной датой, заведомо большей любой реальной. Аналогично обрабатывается отсутствующее второе имя: без проверки склейка ФИО может стать NULL или дать лишний пробел. Эти, казалось бы, технические детали напрямую влияют на качество данных в измерении.
Практический смысл подхода в том, что одно измерение собирается один раз и затем многократно используется в анализе. Конструктор запросов снижает риск ошибок в соединениях, но логику всё равно должен задавать аналитик. Сохранённый запрос становится источником для измерения, а значит, его нужно поддерживать и понимать, какие поля и почему в него попали. В результате создаётся не просто набор данных, а управляемый элемент аналитической модели, пригодный для дальнейшего анализа продаж.
Нужно собрать измерение «Персонал» для аналитического куба. Для него требуются: группа отдела, отдел, ФИО продавца, дата рождения, пол.
Данные лежат в четырёх таблицах:
- Person — все люди: сотрудники и покупатели. Здесь есть Business Entity ID, First Name, Middle Name, Last Name.
- Employee — сотрудники компании. Здесь есть Birth Date, Gender и другие кадровые поля.
- Department — отделы. Здесь есть Department ID, Name, Group Name.
- Employee Department History — история перемещений сотрудника по департаментам. Здесь есть Business Entity ID, Department ID, Start Date, End Date.
Связи:
- Person и Employee — по Business Entity ID;
- Employee и Employee Department History — по Business Entity ID;
- Department и Employee Department History — по Department ID.
История нужна, потому что сотрудник мог работать в другом департаменте на момент продажи. Текущий департамент не всегда совпадает с историческим.
Исходные таблицы нормализованы. Для аналитики их соединяют и получают денормализованное отношение. Это допустимо для схемы «звезда» и ускоряет селекцию, потому что не нужно каждый раз соединять четыре таблицы.
Запрос можно собрать в конструкторе запросов. Он сам создаёт соединения и SQL-код. Нужно отметить поля:
- из Person: Business Entity ID, First Name, Middle Name, Last Name;
- из Employee: Birth Date, Gender;
- из Department: Department ID, Name, Group Name;
- из Employee Department History: Start Date, End Date.
Есть две проблемы с NULL.
- В End Date для текущего департамента стоит NULL. Его заменяют заведомо большой датой, например 3000 годом. Тогда 3000 год означает, что сотрудник ещё работает в этом департаменте.
- Middle Name может отсутствовать. Если просто склеивать Last Name, Middle Name и First Name, результат может стать NULL. Нужно проверять: если Middle Name есть — включать его; если нет — не добавлять его, но следить, чтобы не появился двойной пробел.
ФИО собирается в одно поле Full Name. Порядок полей в итоговом запросе:
- Business Entity ID;
- Full Name;
- Gender;
- Birth Date;
- Department ID;
- Department Name;
- Group Name;
- Start Date;
- End Date.
Итоговые соединения:
Department.DepartmentID = EmployeeDepartmentHistory.DepartmentID;EmployeeDepartmentHistory.BusinessEntityID = Employee.BusinessEntityID;Employee.BusinessEntityID = Person.BusinessEntityID.
Запрос сохраняется. На его основе создаётся измерение «Персонал». Это первое кастомное измерение из трёх; измерение времени обычно уже готово.
1. Для аналитического измерения отбираются только атрибуты, нужные для анализа, а не все поля базы.
2. Измерение «Персонал» требует группу отдела, отдел, ФИО, дату рождения и пол.
3. Нужные данные распределены по таблицам Person, Employee, Department и Employee Department History.
4. Person хранит ФИО всех людей, а Employee — дополнительные сведения о сотрудниках.
5. Department содержит название отдела и название группы отдела.
6. Employee Department History позволяет определить департамент сотрудника на момент продажи.
7. Person и Employee связаны по Business Entity ID один к одному.
8. Department и Employee Department History связаны по Department ID.
9. Employee и Employee Department History связаны по Business Entity ID.
10. После соединения нормализованных таблиц получается денормализованное отношение, что допустимо для «звезды».
11. NULL в End Date означает, что сотрудник ещё работает в департаменте; его заменяют большой датой.
12. При склейке ФИО нужно отдельно обрабатывать отсутствующий Middle Name, чтобы избежать NULL и двойного пробела.
1. Почему для измерения отбираются не все поля из таблиц?
2. Какие атрибуты требуются для измерения «Персонал»?
3. В какой таблице хранятся фамилия, имя и второе имя человека?
4. Почему дата рождения и пол берутся из Employee, а не из Person?
5. Какие поля нужны из таблицы Department?
6. Зачем в измерении используется Employee Department History?
7. Что означает пустое значение в End Date?
8. Как обрабатывается NULL в End Date и зачем?
9. Почему нельзя просто склеить Last Name, Middle Name и First Name без проверки?
10. Как избежать двойного пробела при отсутствующем Middle Name?
11. По каким полям соединяются Person, Employee, Department и Employee Department History?
12. Что происходит с запросом после его сборки в конструкторе?
