Довольно часто (особенно в финансовых организациях, осуществляющих международную деятельность) возникает задача представления денежных сумм в эквивалентах различных валют. "Конвертировать" суммы в другие валюты можно следующими способами:
- - все показатели рассчитывать в моновалюте, а пересчет в валюты других стран отдать на откуп пользователям/клиентскому приложению;
- - все необходимые суммы рассчитывать и хранить в реляционном источнике как в основной/национальной валюте, так и в наиболее используемых валютных эквивалентах (например, USD, EUR), что не универсально и требует дополнительного дискового пространства;
- - в реляционном источнике все суммы хранятся в базовой валюте, а пересчет в валюту эквивалента осуществлять "на лету" при формировании отчетов.
Рассмотрим вариант реализации последнего способа в SQL Server Analysis Services (SSAS). Прежде всего, на стороне реляционного источника нам потребуется справочник валют, на базе которого создадим измерение валют. Классификатор валют можно найти и скачать здесь. Для лучшего понимания ниже приведем фрагмент запроса к реляционному источнику:
-- пример фрагмента запроса к Oracle источнику: SELECT currency -- международный буквенный код валюты по ISO 4217 ,currency_name -- официальное наименование валюты ,currency_name_en -- международное наименование валюты ,currency_code -- цифровой код валюты по ISO 4217 ,country_code -- код страны -- порядок сортировки валют в списке (наиболее исользуемые вверху списка) ,CAST(DECODE(currency, 'USD', '0', 'RUB', '1', 'EUR', '2', '9' ) || currency AS VARCHAR2(4)) AS currency_order ,CAST(DECODE(currency, 'USD', '1033', 'RUB', '1049', 'BYR', '1059', 'EUR', '2067', 'GBP', '2057', 'CAD', '4105', 'LTL', '1063', 'LVL', '1062', 'BGN', '1026', 'UAH', '1058', 'PLN', '1045', 'TRY', '1055', 'ARS', '11274', 'AUD', '3081', 'VEB', '16394', 'BRL', '1046', 'DEM', '1031', 'FRF', '1036', 'MXN', '2058', 'SAR', '1025', 'JPY', '1041', 'CNY', '2052', '1049' ) AS VARCHAR2(5)) AS id_locale -- Microsoft Windows locale identifier (LCID) ,CAST(DECODE(currency, 'USD', '\$', 'RUB', '\р\.', 'BYR', '\р\.', 'EUR', '\€', 'CAD', '\$', 'LTL', '\L\t', 'LVL', '\L\s', 'BGN', '\л\в', 'UAH', '\г\р\н\.', 'TRY', '\T\L', 'AUD', '\$', '' ) AS VARCHAR2(8)) AS currency_sign -- знак валюты ,CAST(DECODE(currency, 'RUB', '\т\.', 'BYR', '\т\.', 'UAH', '\т\.', '\K' ) AS VARCHAR2(8)) AS thousand_abbreviation -- аббревиатура тысяч денежных единиц ,CAST(DECODE(currency, 'RUB', '\м\л\н\.', 'BYR', '\м\л\н\.', 'UAH', '\м\л\н\.', '\M' ) AS VARCHAR2(8)) AS million_abbreviation -- аббревиатура миллионов денежных единиц FROM ...
На основании данного источника в проекте SSAS, как показано на рисунке ниже, создадим неагрегируемое измерение "Валюты", уникальные элементы которого будут упорядочены по вспомогательному полю currency_order:
Далее нам потребуется таблица курсов валют (публикуемых ЦБ как ежедневно, так и ежемесячно) на каждую отчетную дату, на основании которой создадим скрытую физическую меру "Курс_валюты", применив к ней агрегирующую функцию LastChild:
Затем подготовим наши денежные меры, аналогично применив к ним агрегирующую функцию LastChild, и скроем меры от пользователей:
В дизайнере куба на вкладке "Dimesion usage" ("Использование измерений") подключим измерения "Отчетные даты", "Валюты" к мерным группам так, как показано на рисунке ниже:
Наконец, в MDX-скрипте пропишем следующие инструкции:
-- устанавливаем валюту по-умолчанию: ALTER CUBE CURRENTCUBE UPDATE DIMENSION [_Валюты конверсии], DEFAULT_MEMBER ='[_Валюты конверсии].[Валюта].[USD]' ; -- в контексте выбранной валюты для всех денежных мер уставливаем языковую локализацию: SCOPE ([_Валюты конверсии].[Валюта].MEMBERS) ; LANGUAGE(THIS)=[_Валюты конверсии].[Валюта].PROPERTIES("id_locale") ; END SCOPE ; -- для каждой физической скрытой денежной меры создаем вычисляемую меру - пересчет в валютный эквивалент по курсу; -- эти вычисляемые меры выставляем доступными для пользователей: CREATE MEMBER CURRENTCUBE.[MEASURES].[Остаток ОД] AS [MEASURES].[Остаток_ОД] / [MEASURES].[Курс_валюты] ,ASSOCIATED_MEASURE_GROUP = 'Показатели' ,DISPLAY_FOLDER = 'Остатки\Балансовые' ,FORMAT_STRING = [MEASURES].[selected_format_string] -- форматы представлений мер ,FORE_COLOR = [MEASURES].[foreColor] -- определяются отдельными служебными скрытыми мерами, ,BACK_COLOR = [MEASURES].[backColor] -- вычисляемыми в скрипте выше, и это тема отдельной статьи ,VISIBLE = 1; ...... Развернем проект на сервере OLAP, спроцессим куб, подключимся к кубу из клиентского приложения и проверим, что всё работает так, как нам нужно:
Надеюсь, этот пример Вы примените в своей практике :)