Автор: Mosha Pasumansky
Перевод: Александр Орловский
Дата публикации оригинала: 2007-05-23
Источник: Блог Mosha Pasumansky

Вопрос в заголовке этого сообщения очень популярен. Существует множество ситуаций, когда желательно выбрать элемент иерархии измерения времени (Time dimension), который соотносится с текущей датой. Иногда желательно установить элемент по умолчанию, соотносящийся с текущей датой. Это особенно актуально для случая, когда измерение времени содержит неагрегируемые атрибуты, такие как «Год» (т.к. не существует элемента «Все годы»). По умолчанию Analysis Services устанавливает элементом по умолчанию одно из значений атрибута Год, но какое - не определено. Таким образом, вместо определения его статическим элементом (например, [Time].[Year].[2005]), кто-то может захотеть указать на текущий год. Другой распространённый сценарий связан с использованием свойства KPICurrentTime, которое часто устанавливается в значение текущего дня. Или, возможно, существуют вычисления в кубе, требующие ссылки на текущую дату.

Обычно, когда задают такой вопрос, типичый ответ содержит вызов VBA-функции Now() или Date() комбинированной с хитрым форматированием (которое обычно требует дополнительных функций,таких как Format, CStr, CDate, Month, Year, Quarter, Day и т.п.) для построения строки, выглядящей как полное или даже уникальное имя элемента и последующего преобразования MDX-функцией StrToMember. Не смотря на то, что эти решения обычно работают, я от них не в восторге. Ручное построение уникального имени элемента противоречит духу MDX, так как уникальные имена зависят от провайдера и не имеют чётко заданного формата. Даже построение полного имени опасно, особенно в решениях, свободно использующих амперсанды (&) в качестве префикса ключа элемента - стопроцентно незадокументированное поведение. Наконец, многим причинам мне действительно не нравится функция StrToMember. За хаос, которое она вносит в действия оптимизатора, за непредсказуемое кэширование, за очень динамическое связывание путём повторного разбора её параметров.

Альтернатива, которою я предлагаю взамен попыток построения имени элемента в специальном формате – просмотр элементов для поиска текущей даты. Продемонстрируем это при помощи примера из Adventure Works. Мы выполним следующие шаги:

  1. Получить текущую дату с использованием функции VBA!Date().
  2. Так как данная статья была написана в 2007 году, а измерение времени базы Adventure Works заполнено только до 2004, мы перейдем на 4 года назад при помощи функции DateAdd, в 2003 год.
  3. Пройдем по всем дням и поищем один, для которого свойство MemberValue такое же как у текущей даты (4 года назад). В правильно спроектированном измерении времени значение элемента Date (Дата) будет типа DateTime.
  4. Если измерение времени правильно спроектировано, в нем не должно быть больше одного кортежа. Таким образом, возьмем первый элемент набора, который и будет желаемым элементом или NULL, если набор пуст.

MDX-выражение, выполняющее это выглядит следующим образом:

Filter([Date].[Calendar].[Date], [Date].[Calendar].MemberValue = vba!dateadd("yyyy", -4, vba![Date]())).Item(0)

Или его использование в MDX-запросе для получения результата:

select {} on 0
,Filter([Date].[Calendar].[Date], [Date].[Calendar].MemberValue = vba!dateadd("yyyy", -4, vba![Date]())) on 1
from [Adventure Works]

В измерении времени немного элементов. Даже если мы храним в кубе 10 лет, в измерении будет не более 3660 дней. Функция Filter для такого маленького количества элементов срабатывает мгновенно. Однако, мы должны заметить, что для каждого дня происходит вызов VBA-функции, которое кажется избыточным, т.к. текущая дата постоянна. Более того, это немного опасно. Если мы запустим запрос вечером в 23:59, результат выполнения функции VBA!Date может измениться в процессе выполнения запроса. Для предотвращения этого запрос можно переписать так:

with member Measures.Today as vba!dateadd("yyyy", -4, vba![Date]())
select {} on 0
,Filter([Date].[Calendar].[Date], [Date].[Calendar].MemberValue = ([Date].[Calendar].[All Periods],Today)) on 1
from [Adventure Works]

Это известный трюк - сдвинуть координату на постоянный элемент (например, All Periods) для выполнения функции Filter для получения такой же координаты - ([Date]).[Calendar].[All Periods], Today) - для каждой итерации. В этом подходе надежда в том, что выражение будет вычислено только один раз и затем закешируется. Более подходящий вариант сделать это - написать:

with member Measures.Today as vba!dateadd("yyyy", -4, vba![Date]())
select {} on 0
,Filter([Date].[Calendar].[Date], [Date].[Calendar].MemberValue = Root(Today)) on 1
from [Adventure Works]

Здесь, используя Root(Today) мы сдвигаем координаты всех измерений и атрибутов к константе. Даже если у нас больше осей координат в запросе или другие координаты сдвигающие вычисления - не имеет значения, VBA!Now() будет вызвана только один раз.

Похожий трюк может быть также выполнен и в MDX-скрипте. Он полагается на тот факт, что именованные наборы (named sets) статичны и вычисляются только один раз. MDX-скрипт может содержать следующие строки:

CREATE HIDDEN TodayDate = vba!dateadd("yyyy", -4, vba![Date]());
CREATE SET Today AS Filter([Date].[Calendar].[Date], [Date].[Calendar].MemberValue = ([Date].[Calendar].[All Periods],TodayDate));

Впоследствии, где бы мы не хотели сослаться на текущую дату, мы можем использовать Today.Item(0) или даже более короткую нотацию Today(0).

Подвох здесь в том, что вычисление MDX-скрипта кэшируется, так что, если нет никакого обновления куба, именованный набор Today не будет меняться с течением времени и устареет. Но, пока новые данные загружаются в куб ежедневно, всё будет хорошо, поскольку любой процессинг будет запускать повторное вычисление скрипта MDX.

Еще одним решением для ситуации, где приложение не может зависеть от определённого MDX-скрипта, будет использование хранимой процедуры. Хотя она и не будет столь же производительной, как предыдущий вариант, она может быть более универсальной.

Ниже представлен код хранимой процедуры, возвращающей текущий день.

public Member GetToday(Level lvl)
{
// Get today's date from the system
System.DateTime today = System.DateTime.Today;
System.DateTime fouryearsago = today.AddYears(-4);
// The only way to get set out of the level. Direct cast won't work
Expression lvlexp = new Expression(lvl.UniqueName);
Set lvlset = (Set)lvlexp.CalculateMdxObject(null);
// Build the string in the form
// [Date].[Calendar].[Date].MemberValue = CDate("5/21/2007")
Expression exp = new Expression(
lvl.ParentHierarchy.UniqueName
+ ".MemberValue = CDate(\""
+ fouryearsago.GetDateTimeFormats('d')[0]
+ "\")");
Set filterset = MDX.Filter(lvlset, exp);
// Iterate only one step - this is better then checking
// the count and indexing the 0's item
foreach (Tuple t in filterset.Tuples)
return t.Members[0];
// If today's date wasn't found - return NULL member
// Since Member object doesn't have ctor - this is the only way
Expression nullmbr = new Expression("NULL");
return (Member)nullmbr.CalculateMdxObject(null);
}

Из-за некоторых ограничений объекта AdomdServer в приведенном коде выше коде чрезмерно используется объект Expression. Всё было бы проще, если бы объект Member содержал свойство MemberValue, потому что тогда не потребовалось бы динамического построения выражений, и поребовалось бы снова и снова пересчитывать CDate. Простой цикл по lvl.GetMmbers(), сравнивающий значение «сегодня» со значением Member.MemberValue справился бы с этим. Увы, не в текущей версии. Типичный вызов для подобной процедуры выглядел бы так:

select {} on 0
,ASSP.ASStoredProcs.Util.GetToday([Date].[Calendar].[Date]) on 1
from [Adventure Works]

Процедура требует передачи уровня в качестве своего аргумента, но это может быть улучшено. Версия ниже автоматически ищет уровень дня в кубе.

public Member GetToday()
{
CubeDef cb = Context.CurrentCube;
Dimension timedim = null;
foreach (Dimension dim in cb.Dimensions)
{
if (dim.DimensionType == DimensionTypeEnum.Time)
{
timedim = dim;
break;
}
}
if (null == timedim)
throw new System.ArgumentException("No Time dimension in the cube");
foreach (Hierarchy h in timedim.Hierarchies)
{
foreach (Level lvl in h.Levels)
{
if (lvl.LevelType == LevelTypeEnum.TimeDays)
return GetToday(lvl);
}
}
throw new System.ArgumentException("No Day level in the Time dimension");
}

Эта процедура будет работать при простом вызове:

select
{} on 0
,ASSP.ASStoredProcs.Util.GetToday() on 1
from [Adventure Works]

Однако, в кубе, аналогичном Adventure Works, имеющем несколько измерений времени, процедура вернёт элемент из случайно выбранного измерения. Также есть небольшое несоответствие между типами атрибутов AMO и типами уровней ADOMD.NET, таким образом атрибут, отмеченный как Date в AMO в ADOMD.NET будет транслирован в Regular. А в кубе Adventure Works есть небольшая ошибка, уровень, получивший тип Day (День) является именем дня, хотя должен быть отмечен как DayOfWeek (День недели).

Таким образом, мы увидели несколько разных способов определения текущей даты при помощи MDX. Но ни один из них не является идеальным. Все они ссылаются на недетерминированные функции VBA, такие как Now или Date, которые очень плохо кэшируются. Итак, лучшее решение, которое также является простейшим с точки зрения MDX - иметь специально выделенный процесс, ежедневно обновляющий MDX-скрипт следующей строкой:

CREATE SET Today as {[Date].[Calendar].[Date].[May 21, 2003]};

При этом имя текущей даты жёстко прописывается и меняется каждый день. Это решение будет иметь наилучшую производительность, но потребует небольших дополнительных затрат на управление кубом.


Для удобства отслеживания новых публикаций рекомендуем подписаться на рассылку или на канал RSS.

Читайте также: