Даты в PowerPivot
Этот раздел содержит рекомендации по импорту данных, содержащих даты, и по работе с датами в связях и сводных таблицах.
Рекомендации по импорту данных о дате
Если импортируются данные со сведениями о дате и времени, особенно из нескольких источников, такие данные часто содержат даты в разных форматах или с разным уровнем гранулярности.
Например, в Excel есть две функции, возвращающие текущую дату и время: функция TODAY возвращает ту же дату, что и функция NOW, однако при этом возвращаемое ею время всегда равно полудню, тогда как функция NOW возвращает точное время.
Проблема в том, что дополнительные сведения о времени могут приводить к несовпадению значений. При попытке просуммировать значения в сводной таблице может оказаться, что значения не группируются нужным способом.
Помимо данных с разными уровнями точности, источники данных могут содержать даты в разных текстовых форматах, даты в разных региональных форматах и даты в разные финансовые годы.
Чтобы объединить разные типы дат для работы в одной модели, после импорта данных сделайте следующее:
Используйте формулы DAX в вычисляемых столбцах для усечения значений или для создания согласованного набора значений даты и времени.
Создайте главную таблицу дат и времени, с помощью которой можно будет создать связи между столбцами дат.
Более подробные сведения приведены в следующих разделах.
Определите требования к датам.
Прежде чем изменять какие-либо значения даты, подумайте, в каких видах анализа планируется использовать эти данные, и ответьте на следующие вопросы:
На каком уровне или уровнях гранулярности подсчитываются или группируются числовые факты: дни, часы, недели или кварталы?
Какие уровни гранулярности будут использоваться для группировки дат: недели, финансовые кварталы и т. п.?
Отсутствуют ли какие-то даты? Могут ли даты или другие значения отсутствовать или вместо них нужно вставлять значения и даты-заполнители? Если значения отсутствуют, должен ли использоваться ноль или другое выбранное значение вместо неизвестных значений?
Используйте формулы для приведения дат к согласованному формату.
Если импортированные данные содержат даты в разных форматах, можно оставить столбцы как есть и с помощью формул DAX создать вычисляемые столбцы, представляющие даты в правильном формате и на нужном уровне гранулярности.
Примеры см. в следующих разделах:
Если потребуется, используйте функции DAX для извлечения таких значений, как день, год, месяц.
С помощью функций DAX составьте значения в формате даты и времени.
Если значения даты не отформатированы как даты или имеют разный формат, можно использовать функции даты и времени языка DAX для построения допустимых дат.
- Функция FORMAT позволяет работать с пользовательским числовым форматом или форматом даты и времени.
Полный список функций даты и времени см. в разделе Справочник по функциям DAX.
Выполните усечение периодов, если необходимо использовать дни, недели и месяцы.
Дни — это наименьшая единица времени, с которой могут работать интеллектуальные функции логики операций со временем в языке DAX. Поэтому, если не нужно работать со значениями времени, следует уменьшить гранулярность данных до дней как наименьшей единицы.
Решить проблемы, связанные с излишней точностью значений времени, можно несколькими способами.
Усеките время из значений даты и времени или сделайте так, чтобы во всех значениях даты и времени использовалось одно и то же значение времени по умолчанию.
Если требуется работать со временем (с часами, минутами и секундами), создайте с помощью вычисляемых столбцов отдельное поле или несколько полей, представляющих приращения времени. Это позволит анализировать значения времени отдельно от дат.
Тип данных date/time, используемый в PowerPivot, — это тип данных SQL Server, который по умолчанию создает значение времени для каждой даты.
Отфильтруйте данные при импорте, чтобы удалить недопустимые данные.
Если внешние данные содержат недопустимые значения, можно отфильтровать недопустимые данные во время импорта. Дополнительные сведения см. в разделах:
Добавление данных с помощью мастера импорта таблиц (учебник)
Изменение строк, импортированных из источника данных
Рекомендации по работе с датами в сводных таблицах
В этом разделе содержатся рекомендации по работе с датами в сводных таблицах и с формулами, в которых используются интеллектуальные функции логики операций со временем языка DAX.
Старайтесь не использовать целочисленные суррогатные ключи в связях.
Если внешние данные импортируются из реляционного источника данных, очень часто столбцы даты и времени представлены суррогатным ключом — целочисленным столбцом, представляющим уникальную дату. Однако в книге PowerPivot следует избегать создания связей с использованием целочисленных ключей даты и времени. Вместо этого в качестве ключей необходимо использовать столбцы, содержащие уникальные значения с типом данных date.
Хотя использование суррогатных ключей считается целесообразным в традиционных хранилищах данных, нежелательно использовать целочисленные ключи в сводных таблицах, поскольку они могут усложнить группирование значений по различным временным периодам.
Создайте главную таблицу дат.
Если каждая таблица данных в книге содержит столбец со значениями даты и времени и таблицы соединяются по этим столбцам, очень возможно, что многие значения не совпадут. Например, в таблице «Sales» могут быть данные только за вторую половину 2008 г., а в таблице «Suppliers» — даты с 2006 по 2008 г.
Вместо того чтобы соединять разные таблицы данных по большому количеству независимых столбцов с разными значениями даты и времени, можно добиться лучшего результата, создав главную таблицу, в которой будут храниться только сведения о дате. Затем с помощью связей можно связать эту таблицу с таблицами данных и таким образом получить возможность работать с согласованным набором дат.
.gif "Примечание") Примечание |
|---|
При создании главной таблицы дат можно пометить ее как таблицу данных, что позволит использовать дополнительные фильтры дат, которые в противном случае будут недоступны. Дополнительные сведения см. в разделе Диалоговое окно «Пометить как таблицу дат». |
В книге образцов DAX есть пример главной таблицы даты и времени, соединенной с другими таблицами с помощью связей.
Помимо уникальных значений даты и времени для каждой используемой даты, главная таблица содержит иерархии, которые используются для группировки данных в сводных таблицах, как показано в следующей таблице:
DayNumberOfWeek |
WeekNumberOfYear |
CalendarQuarter |
FiscalQuarter |
DayNameOfWeek |
WeekNumberOfMonth |
CalendarSemester |
FiscalSemester |
DayNumberOfMonth |
MonthName |
CalendarYear |
FiscalYear |
DayNumberOfYear |
MonthNumberOfYear |
|
|
При необходимости создайте копии столбцов данных
Понятие главной таблицы дат должно быть известно пользователю, если он работал с традиционными базами данных служб Analysis Services, где для представления и группировки дат используется измерение дат.
Отличие PowerPivot в том, что каждый уникальный столбец в таблице PowerPivot может участвовать только в одной связи между любыми двумя таблицами. Таким образом, если одна таблица содержит несколько столбцов, которые должны быть связаны с ключом даты, необходимо создать копию ключевого столбца даты и связь.
Например, таблица «Orders» содержит следующие столбцы с датами: SalesDate, TransactionDate и ShippingDate. Все эти столбцы нужно связать с ключевым столбцом даты в главной таблице дат, но в PowerPivot это запрещено, потому что каждая связь должна обеспечивать уникальный и однозначный путь по значениям. Вместо этого нужно перенести дополнительные столбцы дат в отдельные таблицы и связать столбец даты в каждой из этих таблиц с ключом даты в главной таблице дат. Например, решено оставить столбец SalesDate в таблице Orders, но создать новую таблицу для транзакций и отдельную таблицу для сведений об отгрузке. С помощью вычисляемого столбца можно создать копии столбцов ShippingDate и TransactionDate, чтобы гарантировать их синхронизацию.
В книгах образцов DAX есть пример создания копий столбцов данных и эффективной работы с ними. Дополнительные сведения о том, где можно получить образцы, см. в разделе Получение образца данных для PowerPivot.
См. также
Основные понятия
Общие сведения по выражениям анализа данных (DAX)